## Use any type: Worksheet Functions in VBA

*pass through*Lakshmi Ramakrishna

*exists*Excel

content

## Formula u EXCEL-u

Microsoft Excel has a lot built infunctions that can be used as typesin an Excel worksheet. It shows the function categories available in the Insert Function window, as shown in the figure below:

## Use formulas in Excel worksheets

Just like math formulas help us in simple and quick math calculations, the formulas provided by Ms. Excel help us maintain a good database and formulas that can be automatically calculated and saved for easy use and reference.

### An example Excel formula:

after the introduction"I AM" formula.In the formula bar or in any cell (whatever you type in a cell that starts with an equal sign (=) will appear in the formula bar), if we press Enter, click the check mark in the formula bar, or select another cell, we can see**Automatic calculation**answer.

Checkmarks and crosses in the formula bar are disabled when the formula bar or cell is not in edit mode.

## Use formulas as functions in VBA

Excel VBA provides most of these types as built-in functions that can easily be used in our code to achieve the same results. these are called**Worksheet function.**

## What are worksheet functions?

The MS Excel application object has a method called "WorksheetFunction". It provides access to most of the standard built-in functions available as "forms" in Excel.

** application.**It can be used immediately to access these functions. However, using

**It can help the developer to use Intellisense to prompt the developer to input the parameters to use to complete the function input.**

`Application.worksheet function.`

## List of Worksheet Functions - VBA

Listed below are some commonly used worksheet functions.

### List of logical functions

serial number | Mode | describe | pension | example | Exit |

1 | I | Make sure all requirements are met. Possible output results can be TRUE or FALSE. | Worksheet function. And (logical1, logical2) | Worksheet function. And (5>3,4<6) | real |

2 | I | If the condition is met, do something (second parameter), if not, do something else (third parameter). | IF( | IF(2<6;"2 less","6 less") | "2 now" |

3 | error | This is a combination of IF and ISERROR. | IF(ISERROR( | if(error(2/0),0,2/0) | 0 |

4 | the | Check if any of the conditions are met. Possible output results can be TRUE or FALSE. | Worksheet function. if (logical1, logical2) | Worksheet function.If(5>3,14<6) | real |

### Lists of finder and reference functions

serial number | Mode | describe | pension |

1 | choose | Selects a value from a list based on index number and the index number specified in the argument. | select ( |

2 | Ask around | Finds the value in the first row of the array and returns the value. | hlookup( |

3 | index | Returns a value based on the column and row number. | INDEX (string, |

4 | look at | Find values horizontally (by row) or vertically (by column). | Search ( |

5 | answer | Searches for a value in a list and returns its position. | RACE ( |

6 | transfer | Change the orientation of a range of cells. Vertical to horizontal and vice versa. | Transport ( |

7 | VLOOKUP | Looks up the value in the first column of the array and returns the value. | Vlookup( |

### date and time functions

Valid date here means** IsDate(**RETURNBoolean value"real"

serial number | Mode | describe | pension |

1 | datum | Returns a date from a combination of year, month, and day. | DATE (year, month, day) |

2 | date value | Converts a date stored as text to a valid date (in an acceptable data format). | date value ( |

3 | the | Returns the date from ValidDate as a number (1-31). | dana ( |

4 | 360 dana | Returns the difference in days between 2 valid dates in a 360-day year. | DAYS360( |

5 | processing | Returns the valid date, "n" months from the valid start date. | EDATE( |

6 | Menstruation | Returns the last day of the month, 'n' months from the current date. | month ( |

7 | Sat | Returns the time (0-23) from the current time. | Sati ( |

8 | minute, thin | Returns the minutes since the current time as a number (0-59). | minute, thin ( |

9 | month | Returns the month from the current date as a number (1-12). | month ( |

10 | cyber and | Returns the number of business days between 2 valid dates. | network days ( |

11 | NETWORKDAYS.INTL | Returns the number of business days between 2 valid dates. Weekends are excluded by default and there is an option to add a list of holidays that should be excluded from the count. | networkdays.intl( |

12 | Now | Returns the current system date and time. | Now() |

13 | but | Returns the seconds (0-59) of the current time. | Others ( |

14 | year | Returns the time from a valid combination of hours, minutes, and seconds. | time ( |

15 | time value | Convert time stored as text to valid time. | time value ( |

16 | working day | Displays the day of the week as an integer (1-7). | working day ( |

17 | week number | Returns the week number (1-52) of the year. | week ( |

18 | working day | Returns a date n business days from the current date. | Day of the week ( |

19 | Year | Returns the year since the current date. | year ( |

20 | YearFRAC | Returns the fraction of a year between 2 valid dates. | yearfrac( |

### Mechanical operation

serial number | Mode | describe | pension |

1 | to change | Converts a number or value from one unit to another. For example: centimeters to inches, feet to centimeters, meters to centimeters, kilograms to grams. | convert () |

### financial function

serial number | Mode | describe | pension |

1 | FV | Calculates and returns the future value of an investment. | fv( |

2 | Photovoltaics | Calculates and returns the present value of an investment. | fv( |

3 | National Economic Institute | Calculates and returns the total number of pay periods for an investment. | For ( |

4 | photomultiplier tube | Calculates and returns the payment amount to the investment. | pmt( |

5 | speed up | It calculates and returns the interest rate of the investment. | price( |

6 | net present value | Calculates and returns the net present value of an investment. | npv( |

7 | Internal rate of return | Calculates and returns the internal rate of return for a set of CF journals. | irr( |

8 | XIRR | Calculates and returns the internal rate of return for a set of aperiodic CFs. | whistling |

9 | price | Calculates and returns the value of the bond. | price( |

10 | antagonist | Returns the interest rate of the fully invested security. | inputs (, [ |

### information function

serial number | Mode | describe | pension |

1 | error | Checks for an error in the cell value, ignoring #N/A. Returns boolean TRUE/FALSE | iserr( |

2 | error | Checks if the cell value is false. Returns a Boolean TRUE/FALSE value | error( |

3 | even | Checks if the cell value is even. Returns a Boolean TRUE/FALSE value | seven ( |

4 | unreasonable | Checks if the cell is logical (TRUE or FALSE). Returns a Boolean TRUE/FALSE value | logically ( |

5 | ISNA | Checks if the cell value is #N/A. Returns a Boolean TRUE/FALSE value | also |

6 | don't send message | Checks if a cell is text (empty cells are not text). Returns a Boolean TRUE/FALSE value | not text( |

7 | serial number | Checks if the cell is a number. Returns a Boolean TRUE/FALSE value | is a number ( |

8 | uproot | Checks if the cell value is odd. Returns a Boolean TRUE/FALSE value | clogging ( |

9 | text | Checks if a cell is text. Returns a Boolean TRUE/FALSE value | istext( |

10 | suggestion | Returns the type of the value stored in the cell. | items ( |

### mathematical function

serial number | Mode | describe | pension |

1 | ABS | Computes and returns the absolute value of a number. | absolute value ( |

2 | In total | Defines and performs calculations on databases or lists. | TOTAL( |

3 | sharpen | Rounds a number to the nearest specified multiple and returns that value. | strop ( |

4 | COS | Returns the cosine of an angle. | cosine ( |

5 | Bachelor of Science | Converts radians to degrees and returns a value. | grades ( |

6 | DSUM | Summarizes database records that meet certain criteria. | dsum( |

7 | even | Rounds to the nearest even number and returns a value. | parents ( |

8 | Experience | Computes the exponential value of the given number and returns that value. | exp( |

9 | fact | Returns the factorial value. | fact ( |

10 | tlo | Rounds the number down to the nearest specified multiple. | by |

11 | GCD | Returns the greatest common divisor. | gcd( |

12 | information Office | Rounds a number down to the nearest whole number. | whole number ( |

13 | LCD unit | Returns the least common multiple. | lcm( |

14 | logical node | Returns the natural logarithm of a number. | LN( |

15 | log | Returns the logarithm of a number to the specified base. | log( |

16 | minutes 10 | Returns the logarithm of a base 10 number. | log10( |

17 | krug | Rounds the number to the specified multiple. | mround( |

18 | Strange | Round to the nearest odd number. | Nepal ( |

19 | pi | The price of Fr. | PI() |

20 | power | Calculates the power of a number. | power( |

21 | product | Multiply a series of numbers. | product( |

22 | business | Returns the integer result of the division. | quotient ( |

23 | radian | Convert degrees to radians. | rays ( |

24 | between the edges | Computes a random number between two numbers. | edge between ( |

25 | krug | Rounds the number to the specified number of digits (specified in the argument). | krug ( |

26 | on | Rounds a number down (towards zero). | ROUND DOWN ( |

27 | busy if | Rounds a number up (from zero). | BUSY IF( |

28 | crime | Returns the sine of the angle. | sin ( |

29 | Subtotal | Returns a statistical summary of a data series. | SUBTOTAL ( |

30 | I | Adds the numbers and returns the total. | sum ( |

31 | Sumif | Sums the numbers that match the condition. | sumif( |

32 | SUMIFS | Adds numbers that meet multiple conditions. | soumifi ( |

33 | sum of products | Multiplies sequences of numbers and adds the resulting sequences. | cumulative product ( |

34 | tanned skin | Returns the tangent of an angle. | tan( |

### statistical function

serial number | Mode | describe | pension |

1 | Average | Average of given numbers. | average( |

2 | Average AN | Calculates the average of the given numbers that meet the criteria. | projectif( |

3 | average value | Calculates the average of a given number that satisfies multiple conditions. | project code ( |

4 | fellow | Computes and returns the correlation of two strings. | correlation( |

5 | count | Counts and returns the number of cells that contain a specified value. | number( |

6 | count | Counts and returns the number of cells that are not empty. | COUNTA( |

7 | they count empty | Counts and returns the number of empty cells. | measurement gap ( |

8 | COUNTIF | Counts and returns the number of cells that satisfy a condition. | countif( |

9 | count | Counts and returns the number of cells that meet multiple criteria. | countifs ( |

10 | forecast | Predicting future y values from a linear trend line. | forecast( |

11 | frequency | Calculates a value within a specified range. | frequency( |

12 | to find | Calculates Y values based on exponential growth. | increase ( |

13 | restrain | Calculates the Y-intercept of the "best fit" line. | wiretapping ( |

14 | large | Returns the kth largest value. | a large( |

15 | line | Returns trendline statistics. | line ( |

16 | upper limit | Returns the largest number in the given array. | Maximum( |

17 | the median | Returns the median of the given array. | median ( |

18 | minimum price | Returns the smallest number in the given array. | min( |

19 | model | Returns the most common number in the given sequence. | mode ( |

20 | rate | Returns the kth percentile. | percentile ( |

21 | rate | Displays the kth percentile, up to and including k. | percentil.inc( |

22 | Percentage.EXC | Returns the kth percentile, where k is unique. | percentil.exc( |

23 | quadrant | Returns the specified quartile value. | quadrant ( |

24 | quarter of the company | Returns the specified quartile value (inclusive). | kvartil.inc( |

25 | quadrant.EXC | Returns the specified quartile value (exclusive). | quartile.exc( |

26 | knocked | Returns the rank of the numbers in the sequence. | knocked ( |

27 | Ranking M.S | Returns the rank of the numbers in the sequence. (Average value). | ranking.prosj. ( |

28 | level equalizer | Returns the rank of the numbers in the sequence. (wickedness). | rank.eq ( |

29 | prejudice | Calculates and returns the value of the slope of a linear regression. | prejudice( |

30 | Yes | Returns the smallest value in an array of numbers. | Yes ( |

31 | standard deviation | Calculates and returns the standard deviation. | stdev( |

32 | standard development process | Calculates and returns the standard deviation for the entire population. | stdev.p( |

33 | standard development tools | Calculates and returns the sample standard deviation. | stdev.s( |

34 | Vice President of Standards Development | Calculates and returns the standard deviation for the entire population | stdevp( |

35 | trend | Calculates and returns the Y value from the trend line. | trend( |

### String (or) text functions

serial number | Mode | describe | pension |

1 | clean | Remove any non-printable characters. | CLEAN ( |

2 | Dollar | Add currency and decimal formatting to numbers. | USD( |

3 | I am looking | Find the location of the search text in the cell. case sensitive. | Finding ( |

4 | left | Returns the specified number of characters from the left side of the string | LEFT( |

5 | Lun | Counts the number of characters in a string. Spaces are also included. | len( |

6 | the middle | Extracts the text from the middle of the string at the specified position. | in the middle ( |

7 | suitable | Convert text to fix uppercase and lowercase. | corresponding ( |

8 | replace | Replaces a piece of text with other text in a string. | replace ( |

9 | answer | Repeat text several times in a row. | rept( |

10 | rectify | Returns the specified number of characters from the right side of the string | correctly ( |

11 | looking for | Place the text inside the table. This search is not case sensitive. | Search ( |

12 | replace | Find and replace text. This search and replace is case sensitive. | replace ( |

13 | text | Converts a value to a text data type with a specified number format. | text( |

14 | circumcise | Removes all whitespace before (at the start) and at the end (at the end) of a text/string expression. | arrangement ( |

## Examples of using worksheet functions in VBA

### Delete empty rows in a range

Sub row_del_demo()' declare variableDim range_new' Loop through rowsFor i = 1 To 60 ' Get the range of rows Define range_new = Worksheets("Wonders"). Rows(i & ":" & i) ' Find Count the number of empty columns in the range fill_cols = Application.WorksheetFunction.CountA(range_new) ' If all columns in it are empty, delete the entire row If fill_cols = 0 Then Sheets ("Wonders"). Rows(i). Delete End IfNextEnd Sub

### Add integers to a sequence using worksheet functions

Subworksh_demo()' declare variables and objectsDim a(10) As IntegerDim sum_10 As Integer Loop to assign integer values to all 10 array elements For i = 0 Do 9a(i) = i + 10 Debug. Print a(i) Next ' Use sheetfunctionsum_10 = Application.WorksheetFunction.Sum(a)' Print sumDebug.Print sum_10End Subnumerical sum to complex

### Use the worksheet average and sum functions

Sub wksfun_Demo() ' Declare variables Dim tot, avg, res, sci, soc, mat, eng, lan ' Assign values sci = 67 soc = 78 eng = 90 mat = 89 lan = 45 ' Use the worksheet function to find the total sum tot = WorksheetFunction .Sum(sci, soc, eng, lan, mat) Debug.Print "The sum of all scores is " & tot 'Average score m.g = WorksheetFunction.Average(sci, soc, mat, eng, lan ) Debugging. Print "Student's average is" & final sub average

## Worksheet functions that use Excel worksheet data as arguments

Sub wksh_demo()maximum_value = WorksheetFunction.Max(range("D2:D9"))Debug.Print maximum_valueEnd Sub

Exit:

528

## in conclusion

The worksheet function object helps us write programs using the appropriate format when it suits us instead of using inline macros to write formulas. When using worksheet functions, we can use object variables in a procedure or cell values in a worksheet/any other Excel workbook as parameters. The preceding application object is optional. (**Application worksheet function.**)

**Marked as:** Excel,Excel formula,officially,Microsoft,Microsoft Excel,VBA,VBA for Excel,work sheet,worksheet function,work sheet