Contents
Formulasin EXCEL
Microsoft Excel provides several built-in functions that can be used as formulas in Excel worksheets. It shows the function categories available in the Insert Function window, as shown in the following figure:
Using a formula in an Excel worksheet
Just as mathematical formulas help us solve simple and quick mathematical problems, so do Mrs. B.’s. Excel provides formulas for keeping good databases and formulas that can be automatically calculated and saved for easy use and reference.
Example of an Excel formula:
After we enter the SUM formula in the formula bar or in any cell (what you enter in a cell that starts with an equal sign (=) is displayed in the formula bar), if we press Enter, click a check mark in the formula bar, or select another cell, the automatically calculated answer is displayed.
If a row or formula cell is not in edit mode, the checkboxes and crosses in the formula row are disabled.
Using formulas as functions in VBA
Excel VBA provides most of these formulas as built-in functions that can easily be used in our code to achieve the same result. These are called spreadsheet functions.
What is the spreadsheet function?
The MS Excel application object has a method called WorksheetFunction. It has access to most standard built-in functions that are available as formulas in Excel.
Application. can be used immediately to access the functions. However, using Application.worksheetfunction. can help a programmer who can use Intellisense to browse through the parameters that should be used to complete the feature set.
List of worksheet functions – VBA
The following is a list of some commonly used spreadsheet functions.
List of logical functions
no | Function | Description | Syntax | Example | Output |
1 | И | Check that all conditions are met. The possible result of the output can be TRUE or FALSE. | Function of the worktop. And(Logic1,Logic2) | Function of the worktop. И(5>3,4<6) | TRUE |
2 | IF | If the conditions are met, do something (second parameter), otherwise do something else (third parameter). | IF | IF(2<6, 2 min, 6 min) | 2 means less |
3 | IFERROR | It is a combination of IF and ISERROR. | IF(ISERROR( <calculation>), 0, <result of calculation>) | IF(ISERROR(2/0),0,2/0) | 0 |
4 | OR | Verifies that one of the conditions is met. The possible result of the output can be TRUE or FALSE. | Function of the worktop. Si(Logic1,Logic2) | Function of the worktop. If(5>3,14<6) | TRUE |
List of search and reference functions
no | Function | Description | Syntax |
1 | SELECT | Selects a value from a list based on the index number and index number specified in the parameter. | select( , value1, value2) |
2 | HLOOKUP | Looks for a value in the first row of the array and returns it. | Hlookup( ,
, , ) |
3 | INDEX | Returns a value based on the column number and the row number. | INDEX (matrix, , <[col num]>, <[area num]>) |
4 | LOOKUP | Search for values in horizontal (rows) or vertical (columns) direction. | Lookup( <search value>, , ) |
5 | MATCH | Searches for a value in the list and returns its position. | MATCH (,, <[type of match]>) |
6 | TRANSPORT | Changes the alignment of some cells. From vertical to horizontal and vice versa. | Transpose() |
7 | VLOOKUP | Searches for and returns a value in the first column of the array. | Vlookup( ,
, , ) |
Date and time functions
A valid date here means that IsDate() returns the boolean value TRUE.
no | Function | Description | Syntax |
1 | DATE | Returns the date from a combination of year, month and day. | Date (year, month, day) |
2 | DATEVAL | Converts the date stored as text to a valid date (in an acceptable data format). | date value(<text-date> ) |
3 | DAY | Returns the day as a number (1-31) from the current date. | Tag() |
4 | DAY360 | Returns the difference (in days) between two valid dates in a 360-day year. | DAYS360( <starting date> , , |
5 | EDATE | Returns the current date, n months from the start date. | DATE(, ) |
6 | EOMONTH | Returns the last day of the month, n months from the current date. | Month |
7 | CHARLIE | Returns the time as a number (0-23) from the current time. | Time(<serial number>) |
8 | MINUTE | Returns the minute as a number (0-59) from the current time. | Minute() |
9 | MONTHS | Returns the month as a number (1-12) of a valid date. | Month() |
10 | POLICY NETWORK | Returns the number of working days between 2 valid dates. | network days(, , []) |
11 | NETWORK DAYS.INTL | Returns the number of working days between 2 valid dates. By default, weekends are excluded and it is possible to add a list of holidays to be excluded from the count. | netzwerktage.intl( , , [], []) |
12 | NOW | Returns the current date and time as they appear in the system. | now() |
13 | SECOND REPORT | Returns the second as a number (0-59) from the current time. | Second() |
14 | TIME | Returns the time from a valid combination of hours, minutes and seconds. | Time (, , ) |
15 | TIME | Converts time stored in text form to real time. | Time value() |
16 | WEEKLY | Returns the day of the week as an integer (1-7). | Day of the week(, []) |
17 | WEEKLY NUMBER | Returns the week number of the year (1-52). | week number(, []) |
18 | WORK DAY | Returns the date of n working days from the valid date. | Working day (, days, [ ]) |
19 | BIRTHDAY | Returns the year of a valid date. | Year(<serial number>) |
20 | YEARFRAC | Returns the fraction of a year between two valid dates. | yearfrac( , []) |
Technical functions
no | Function | Description | Syntax |
1 | CONVERT | Converts a number or value from one unit to another. E.G.: Cm in inches, ft in centimeters, m in centimeters, kg in g. | convert(, <from unit> , <to unit> ) |
Financial functions
no | Function | Description | Syntax |
1 | FV | Calculates and provides the future value of the investment. | fv(, , [pv], []) |
2 | PV | Calculates and provides the current value of the investment. | fv(, , [], []) |
3 | NPER | Calculates and gives the total number of pay periods for the investment. | nper(<Rate, , [], [type]) |
4 | PMT | Calculates and reimburses the payment amount when making an investment. | pmt(, , [], []) |
5 | TARIFFS | Calculates and returns the interest rate of the investment. | Quotes (, ,, [], [], []) |
6 | CASH VALUE | Calculates and returns the net present value of the investment. | npv(, , [], [], ….., []) |
7 | IRR | Calculates and reports internal profitability for a range of periodic CFs. | irr( , [] ) |
8 | XIRR | Calculates and returns the internal rate of return for a set of non-periodic CFs. | xirr(, , [] ) |
9 | PRICE | Calculates and returns the bond price. | Price (, , , []) |
10 | INTRATE | Returns the interest rate of a fully invested security. | within(, , , []) |
Information functions
no | Function | Description | Syntax |
1 | ISERR | Tests if the cell value is an error, ignores #N/A. Returns a boolean value TRUE/FALSE | iserr( < value > ) |
2 | ISERROR | Checks if the cell value is an error. Returns the boolean value TRUE/FALSE | iserror( <value>) |
3 | ISEVEN | Checks whether the cell value is uniform. Returns the boolean value TRUE/FALSE | iseven( <a number>) |
4 | ISLOGICAL | Checks whether the cell is logical (TRUE or FALSE). Returns the boolean value TRUE/FALSE | islogic( < value > ) |
5 | ISNA | Test if the cell value is #N/A. Returns a boolean value TRUE/FALSE. | isna( < value > ) |
6 | ISNONTEXT | Checks if the cell is text (empty cells are not text). Returns the boolean value TRUE/FALSE | isnontext( < value > ) |
7 | IDENTIFIER | Checks if the mobile number is a number. Returns the boolean value TRUE/FALSE | isnumber( < value > ) |
8 | ISODD | Checks if the value of the cell is odd. Returns the boolean value TRUE/FALSE | isodd( <a number>) |
9 | SOURCE | Checks if the cell is text. Returns the boolean value TRUE/FALSE | istext( < value > ) |
10 | TYPE | Returns the type of the value stored in the cell. | Type( < value > ) |
Mathematical functions
no | Function | Description | Syntax |
1 | ABS | Calculates and returns the absolute value of the number. | abs( <a number>) |
2 | AGGREGAT | It defines and performs calculations for a database or a list. | AGGREGATE(, , [k] > |
3 | COVER | Rounds the number to the nearest specified multiple and returns the value. | Ceiling( <a number> , ) |
4 | COS | Returns the cosine value of the angle. | cos( <a number>) |
5 | REVIEWS | Converts radians to degrees and returns the value. | Degree( <angle>) |
6 | TSUM | provides a summary of the records in the database that meet certain criteria. | dsum( , , ) |
7 | EVEN | Round to the nearest even number and return the value. | even( <a number>) |
8 | EXP | Calculates an exponential value for a given number and returns the value. | exp( <a number> ) |
9 | DONE | Returns the factorial value. | Done() |
10 | DISCUSSION | Round the number to the nearest indicated multiple. | soil ( , ) |
11 | GCD | Returns the greatest common divisor. | gcd( <number 1> , [], [],……[]) |
12 | INT | Round the number to the nearest whole number. | int( < number > ) |
13 | LCM | Returns the least common multiple. | lcm( <number 1> , [], [],……[]) |
14 | LN | Returns the natural logarithm of the number. | LN( <a number> ) |
15 | LOG | Returns the logarithm of the number with the specified base. | log( , []) |
16 | LOG10 | Returns the logarithm of the number in base 10. | log10( <a number> ) |
17 | MROUND | Rounds a number to the specified plural. | mround( < number > , ) |
18 | ODD | Round to the nearest odd number. | odd( <a number > ) |
19 | PI | Meaning of PI. | PI() |
20 | POWER | Calculate the estimated number. | Power( <a number> , ) |
21 | PRODUCT | Multiplies a series of numbers. | product( <number 1> , [], [],……[]). |
22 | QUOTIENT | Returns the integer of the division. | Quotient( <counter> , ) |
23 | RADIANS | Converts the angle to radians. | radians( <angle> ) |
24 | RANDBETWEEN | Calculates a random number between two numbers. | borderbetween( <Bottom> , ) |
25 | ROUND | Rounds the number to the specified number of digits (specified in the parameter). | around( <a number> , ) |
26 | ROUNDDOWN | Round the number down (to zero). | ROUNDDOWN( , ) |
27 | ROUNDUP | Round the number up (away from zero). | ROUNDUP( , ) |
28 | SIN | Returns the value of the sine of the angle. | sin( <a number>) |
29 | SYNOPSIS | Returns a statistical summary of a set of data. | subtotal( <function num>, , [], [],…..[]). |
30 | SYNOPSIS | Add up the numbers and return the sum. | Sum( <number 1> , [], [],……[]) |
31 | SUMIF | The sums of the numbers that meet the criteria. | somif( , , []) |
32 | SUMIFS | Sum of numbers satisfying different criteria. | sumifs(<sum range>, , <criterion 1> , [, ],[, ],….[, ]]) |
33 | SYNOPSIS | multilayer arrays of numbers and folds the resulting array. | sumproduct( <table 1> , [], [], [],….[]) |
34 | TAN | Returns the value of the tangent of the angle. | tan( <a number > ) |
Statistical functions
no | Function | Description | Syntax |
1 | AVERAGE | Average of the given numbers. | Average( <number 1> , [], [],……[]) |
2 | AVERAGE | A set of average numbers that meet the criteria. | averageif( , , []) |
3 | AVERAGEIFS | Average value of numbers that meet different criteria. | averageifs(<average range>, , <criterion 1> , [, ],[, ],….[, ]]) |
4 | CORREL | Calculates and gives the correlation of two series. | correlate( , ) |
5 | COUNT | Counts the number of cells that contain a specified value and returns this number. | count( , [], [], [<value 4>]….[] ) |
6 | COUNTRY | Counts the number of empty cells and returns them. | COUNTA( , [], [], [<value 4>]….[] ) |
7 | COUNTBLANK | Counts the number of empty cells and returns them. | countblank( <range> ) |
8 | COUNTIF | Counts and returns the number of cells that meet the criteria. | counttif( , ) |
9 | CONFIGURATIONS | Counts and returns the number of cells that meet various criteria. | counttifs( , <criterion 1> , [, ], [, ],….[, ]) |
10 | PREDICTION | Predict future y-values from the linear trend line. | Prediction ( , ) |
11 | FREQUENCY | Counts the values that fall within the specified ranges. | frequency(<data table> , ) |
12 | GROW | Calculates Y values based on exponential growth. | Growth( , [], [], [], [const]) |
13 | INTERCEPT | Calculates the intercept of the line with the best match. | interception(, ) |
14 | MORE | Returns the kth largest value. | large(, k) |
15 | LINES | Returns a trendline statistic. | linest( <known ys> , [], [] , [] ) |
16 | MAX | Returns the largest number in the specified row. | max( <number 1> , [], [],……[]) |
17 | MEDIAN | Returns the median value of the specified range. | Median ( <number 1> , [], [],……[]) |
18 | MIN | Returns the smallest number in the specified row. | min( <number 1> , [], [],……[]) |
19 | MODE | Returns the most frequent number in the specified row. | mode( <number 1> , [], [],……[]) |
20 | PERCENTAGE CRANE | Returns the 10th percentile. | Percentile( , ) |
21 | PERCENTAGE CRANE | Returns the k percentile, where k is included. | Percentile. |
22 | PERCENTAGE CRANE | Returns the k percentile, where k is exclusive. | Percentile. |
23 | QUARTIL | Returns the value of the specified quartile. | quartile( <table> , ) |
24 | NEIGHBORHOOD | Returns the value of the specified quartile (inclusive). | quartile. |
25 | OVERVIEW OF THE APARTMENT | Returns the value of the specified quartile (exclusive). | quartile.exc( , ) |
26 | RANK | Returns the rank of the number in the row. | rank(<number> , , [])) |
27 | RANK.AVG. | Returns the rank of the number in the row. (Average). | rank.avg (<a number> , , []) |
28 | RANK.E.M.T. | Returns the rank of the number in the row. (Front row). | rank.eq(<number> , , []) |
29 | SLOP | Calculates and returns the value of the slope of a linear regression. | Slope( < known ys> , ) |
30 | LITTLE | Returns the smallest value in the number sequence. | small( <table>, ) |
31 | STDEV | Calculates and returns the standard deviation. | stdev( <number 1> , [], [],……[]) |
32 | STDEV.P. | Calculates and gives the standard deviation of the entire population. | stdev.p( <number 1> , [], [],……[]) |
33 | STDEV.S. | Calculates and gives the standard deviation of the sample. | stdev.s( <number 1> , [], [],……[]) |
34 | STDEVP | Calculates and gives the standard deviation of the whole population | stdevp( <number 1> , [], [],……[]) |
35 | TREND | Calculates and returns Y values based on the trend line. | trend( , [], [], [], [const]) |
String (or) text functions
no | Function | Description | Syntax |
1 | NUMBER | Deletes all characters that cannot be printed. | pure |
2 | DOLLAR | Adds the currency format and decimals to the number. | dollar( <a number> , []) |
3 | FIND IT | Indicates the position of the search text in the cell. Upper and lower case letters are case sensitive. | find( < search text > , , [] ) |
4 | LEFT | Returns the specified number of characters to the left of the string. | LEFT(, <no characters>)) |
5 | LEN | Counts the number of characters in a string. Fields are also included. | len( <string value> ) |
6 | MID | Extracts text from the middle of a line from a specified position. | mid( <string value> , <start position>, <numeric character>) |
7 | OWN | Converts the text to the desired capital letter. | actual( <line value>) |
8 | REPLACEMENT | Replaces part of the text with other text on the line. | replace( <string value>)), <string to find>, <string to replace> ) |
9 | ANSWER: | Repeat the text several times on one line. | rept( <row value> , <a number of times>)) |
10 | RULES | Returns the specified number of characters to the right of the string. | right( <line value>, <number of characters>)) |
11 | SEARCH | The search is not case sensitive. | search( <search text> , <position to start search>) ) |
12 | SUBSCRIBE | Search and replace text. Search and replace is case sensitive. | replace( <string> , <old text> , <new text> , [ ] ) |
13 | TEXT | Converts the value to a text data type with a specified numeric format. | text( <string value> , ) |
14 | TRIM | Removes all spaces before ( master ) and at the end ( trailing ) of a text/line expression. | trim( < string value > ) |
Examples of using worksheet functions in VBA
Delete the empty rows in the range.
Sub row_del_demo()
Declare variable
Dim range_new
Loop to go through the rows
For i = 1 To 60
‘ Get the range of the rows
Set range_new = Worksheets(Wonders).Rows(i & : & i)
Get the number of empty columns in this row
fill_cols = Application.WorksheetFunction.CountA(range_new)
‘ delete the entire row if all columns are empty
If fill_cols = 0, then
Sheets(Wonders).Rows(i).Delete
End If
Next
Sub-sub final
Use a spreadsheet function to find the sum of integrals in a table.
Under worksh_demo()
Declare variables and objects
Dim a(10) as integer
Dim sum_10 as integer
‘ loop to assign integer values to the 10 elements of the array
For i = 0 To 9
a(i) = i + 10
Debug.Print a(i)
Next
Calculate the sum of all the numbers in the table using worksheet function
sum_10 = Application.WorksheetFunction.Sum(a)
Print sum
Debug.Print sum_10
Sub-sub final
Using the Average and Summary functions in the worksheet
Sub wksfun_Demo()
‘ Declare variables
Dim tot, avg, res, sci, soc, mat, eng, lan
Value assignment
sci = 67
soc = 78
eng = 90
mat = 89
lan = 45
Get sum with worksheet function
to = WorksheetFunction.Sum(sci, soc, eng, lan, mat)
Debug.Print Sum of all scores & Sum of all scores is & total
Get average grades
avg = WorksheetFunction.Average(sci, soc, mat, eng, lan)
Debug.Print Average grades student & avg
Sub-sub final
Spreadsheet function, which uses the data from the Excel spreadsheet as parameter.
Sub wksh_demo()
maximum_value = WorksheetFunction.Max(range(D2:D9))
Debug.Print maximum_value
End Sub
Go away:
528
Supplement
The Worksheetfunction object helps us use the correct format to code our program, rather than using a macro written to code an arbitrary formula. We can use object variables from our procedure or cell values from worksheets/any other Excel workbook as parameters when we use the Worksheet function. The preceding object of application is optional. (Appendix. Worksheet function.)
Post Use any formula: The post VBA worksheet function appeared first on Software Solutions Online.
frequently asked questions
How do you use the worksheet function in VBA?
Blog
Can we use Vlookup in VBA?
vlookup-in-vba-excel
How do you use a variable in an Excel VBA formula?
Forum 86819-vba code…
Related Tags:
using excel formulas in vbaworksheetfunction.sum vbavba worksheetfunction vlookuphow to write excel formula in vba codeapplication.worksheetfunction vbaworksheetfunction.match vba,People also search for,Feedback,using excel formulas in vba,worksheetfunction.sum vba,vba worksheetfunction vlookup,how to write excel formula in vba code,application.worksheetfunction vba,worksheetfunction.match vba,vba worksheetfunction column,excel vba worksheet functions list