Use Any Formula: Worksheetfunction in VBA
Contents
Formulas in EXCEL
Microsoft Excel offers several built-in functions that can be used as formulas in Excel worksheets. It displays the categories of functions available in the Insert Function window, as seen in the image below:
Usage of Formula in an Excel Sheet
Just like how mathematical formulas help us tackle easy and quick calculations in math, the formulas offered by Ms. Excel help us maintain good databases with data and formulas that can be auto-calculated and stored for ease of use and reference.
Example of an Excel Formula:
After typing the “SUM” formula in the formula bar or any cell, (what you type in the cell starting with an equal (=) sign will display on the formula bar), if we press the enter key, click on the check mark on the formula bar, or select some other cell, we can see the auto-calculated answer.
When the formula bar or the cell is not in edit mode, the check and cross marks on the formula bar are disabled.
Usage of Formulas as Functions in VBA
Excel VBA offers most of these formulas as built-in functions that can be easily used in our code to achieve the same result. These are called Worksheetfunctions.
What is a WorksheetFunction?
The application object of MS Excel has a method called “WorksheetFunction.” It can give access to most of the standard built-in functions that are available as “Formulas” in Excel.
Application.<function call> can be used straight away to access the functions. However, using Application.worksheetfunction.<function call> can help the programmer with Intellisense that can guide him through the parameters to be used to finish typing the function.
A List of Worksheet Functions – VBA
Here is a list of some worksheet functions that are commonly used.
List of Logical Functions
| S.no | Function | Description | Syntax | Example | Output |
| 1 | AND | Checks whether all conditions are met. Possible output result can be either TRUE or FALSE. | Worksheetfunction.And(logic1,logic2) | Worksheetfunction.And(5>3,4<6) | TRUE |
| 2 | IF | If conditions are met, do something (second parameter), if not, do something else (third parameter) . | IF(<logical test>,<value if true>, <value if false>) | IF(2<6,”2 is less”,”6 is less”) | “2 is less” |
| 3 | IFERROR | This is a combination of IF and ISERROR. | IF(ISERROR( < calculation> ), 0, < calculation result >) | IF(ISERROR(2/0),0,2/0) | 0 |
| 4 | OR | Checks whether any one of the conditions are met. Possible output result can be either TRUE or FALSE. | Worksheetfunction.If(logic1,logic2) | Worksheetfunction.If(5>3,14<6) | TRUE |
List of Lookup and Reference Functions
| S.no | Function | Description | Syntax |
| 1 | CHOOSE | Chooses a value from a list based on its index number and the index number provided in the parameter. | choose( <index num>, value1, value2) |
| 2 | HLOOKUP | Looks up a value in the first row of a table and returns a value. | Hlookup( <lookup value>, <table Array>, <row index num>, <range lookup>) |
| 3 | INDEX | Returns a value based on its column number and row numbers. | INDEX (array, <row num>, <[col num]>, <[area num]>) |
| 4 | LOOKUP | Looks up values either horizontally (in rows) or vertically (in columns) . | Lookup( < lookup value>, <range of cols or table>, <boolean result>) |
| 5 | MATCH | Searches for a value in a list and returns its position. | MATCH (<lookup value>, <lookup array>, <[match type]>) |
| 6 | TRANSPOSE | Changes the orientation of a range of cells. Vertical to horizontal and vice versa. | Transpose(<array or range that needs to be flipped>) |
| 7 | VLOOKUP | Looks up a value in the first column of a table and returns a value. | Vlookup( <lookup value>, <table Array>, <col index num>, <range lookup>) |
Date and Time Functions
A valid date here means IsDate(<valid date>) returns the Boolean value “TRUE”
| S.no | Function | Description | Syntax |
| 1 | DATE | Returns a date from a combination of year, month, and day. | DATE(Year, month, day) |
| 2 | DATEVALUE | Converts a date that is stored as text into a valid date (in acceptable data format). | datevalue(< date text> ) |
| 3 | DAY | Returns the day as a number (1-31) from a valid date. | day( <serial number> ) |
| 4 | DAYS360 | Returns difference (in days) between 2 valid dates in a 360-day year. | DAYS360( < start date> , <end date> , <method> |
| 5 | EDATE | Returns a valid date, “n” months away from a valid start date. | EDATE(<start date>, <months> ) |
| 6 | EOMONTH | Returns the last day of the month, “n” months away from a valid date. | eomonth(<start date>, <months>) |
| 7 | HOUR | Returns the hour as a number (0-23) from a valid time. | hour(< serial number>) |
| 8 | MINUTE | Returns the minute as a number (0-59) from a valid time. | minute(<serial number>) |
| 9 | MONTH | Returns the month as a number (1-12) from a valid date. | month(<serial number>) |
| 10 | NETWORKDAYS | Returns the number of working days between 2 valid dates. | networkdays(<start date>, <end date>, [<holidays>]) |
| 11 | NETWORKDAYS.INTL | Returns the number of working days between 2 valid dates. By default , the weekends are excluded and also provides an option to add a list of holidays that needs to be excluded from the count. | networkdays.intl(<start date> , <end date> , [<weekend>], [<holidays>]) |
| 12 | NOW | Returns the current date and time as in the system. | now() |
| 13 | SECOND | Returns the second as a number (0-59) from a valid time. | second(<serial number>) |
| 14 | TIME | Returns the time from a valid combination of hour, minute, and second. | time(<hour>, <minute>, <second>) |
| 15 | TIMEVALUE | Converts a time stored as text into a valid time. | timevalue(<time text>) |
| 16 | WEEKDAY | Returns the day of the week as an integer (1-7). | weekday(<serial number>, [<return type>]) |
| 17 | WEEKNUM | Returns the week number in a year (1-52). | weeknum(<serial number>, [<return type>]) |
| 18 | WORKDAY | Returns the date n working days from a valid date. | workday(<start date>, days, [ <holidays >]) |
| 19 | YEAR | Returns the year from a valid date. | year(< serial number >) |
| 20 | YEARFRAC | Returns the fraction of a year between 2 valid dates. | yearfrac(<start date , <end date> , [<basis>]) |
Engineering Functions
| S.no | Function | Description | Syntax |
| 1 | CONVERT | Convert a number or value from one unit to another. Eg: Cm to inches, feet to cm, m to cm, kg to g . | convert(<value>, < from unit> , < to unit > ) |
Financial Functions
| S.no | Function | Description | Syntax |
| 1 | FV | Calculates and returns the future value of an investment. | fv(<rate>, <nper>, <payment>, [pv], [<type>]) |
| 2 | PV | Calculates and returns the present value of an investment. | fv(<rate>, <nper>, <payment>, [fv], [<type>]) |
| 3 | NPER | Calculates and returns the total number of payment periods in an investment. | nper(<rate, <payment>, <pv> , [<fv>], [type]) |
| 4 | PMT | Calculates and returns the payment amount in an investment. | pmt(<rate>, <nper>, <pv> , [<fv>], [<type>]) |
| 5 | RATE | Calculates and returns the interest rate for an investment. | rate(<nper>, <payment> ,<pv>, [<fv>], [<type>], [<guess>]) |
| 6 | NPV | Calculates and returns the net present value of an investment. | npv(<rate>, <value 1>, [<value 2>], [<value 3>],….., [<value n>]) |
| 7 | IRR | Calculates and returns the internal rate of return for a set of periodic CFs. | irr( <values>, [<guess>] ) |
| 8 | XIRR | Calculates and returns the internal rate of return for a set of non-periodic CFs. | xirr(<values>, <dates>, [<guess>] ) |
| 9 | PRICE | Calculates and returns the price of a bond. | price(<settlement>, <maturity> , <rate>, <yield> , <redemption>, <frequency>, [<basis>]) |
| 10 | INTRATE | Returns the interest rate of a completely invested security. | intrate(<settlement>, <maturity> , <investment> , <redemption>, <frequency>, [<basis>]) |
Information Functions
| S.no | Function | Description | Syntax |
| 1 | ISERR | Tests if cell value is an error, ignores #N/A. Returns boolean value TRUE/FALSE | iserr( < value > ) |
| 2 | ISERROR | Tests if cell value is an error. Returns a boolean value TRUE/FALSE | iserror( < value> ) |
| 3 | ISEVEN | Tests if cell value is even. Returns a boolean value TRUE/FALSE | iseven( < number> ) |
| 4 | ISLOGICAL | Tests if cell is logical (TRUE or FALSE). Returns a boolean value TRUE/FALSE | islogical( < value > ) |
| 5 | ISNA | Tests if cell value is #N/A. Returns a boolean value TRUE/FALSE | isna( < value > ) |
| 6 | ISNONTEXT | Tests if cell is not text (blank cells are not text). Returns a boolean value TRUE/FALSE | isnontext( < value > ) |
| 7 | ISNUMBER | Tests if cell is a number. Returns a boolean value TRUE/FALSE | isnumber( < value > ) |
| 8 | ISODD | Tests if cell value is odd. Returns a boolean value TRUE/FALSE | isodd( < number> ) |
| 9 | ISTEXT | Tests if cell is text. Returns a boolean value TRUE/FALSE | istext( < value > ) |
| 10 | TYPE | Returns the type of value stored in a cell. | type( < value > ) |
Math Functions
| S.no | Function | Description | Syntax |
| 1 | ABS | Calculates and returns the absolute value of a number. | abs( < number> ) |
| 2 | AGGREGATE | It defines and performs calculations for a database or a list. | AGGREGATE(<function number>, <options>, <array >, [k] > |
| 3 | CEILING | Rounds a number up, to the nearest specified multiple and returns the value. | ceiling( < number > , <significance> ) |
| 4 | COS | Returns the cosine value of an angle. | cos( < number > ) |
| 5 | DEGREES | Converts radians to degrees and returns the value. | degrees( < angle> ) |
| 6 | DSUM | Sums up database records that meet certain criteria. | dsum( <database> , <field> , <criteria> ) |
| 7 | EVEN | Rounds to the nearest even integer and returns the value. | even( < number > ) |
| 8 | EXP | Calculates the exponential value for a given number and returns the value. | exp( < number > ) |
| 9 | FACT | Returns the factorial value. | fact( <number> ) |
| 10 | FLOOR | Rounds a number down to the nearest specified multiple. | floor( <number> , <significance> ) |
| 11 | GCD | Returns the greatest common divisor. | gcd( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 12 | INT | Rounds a number down to the nearest integer. | int( < number > ) |
| 13 | LCM | Returns the least common multiple. | lcm( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 14 | LN | Returns the natural logarithm of a number. | LN( < number > ) |
| 15 | LOG | Returns the logarithm of a number to a specified base. | log( <number> , [<base>]) |
| 16 | LOG10 | Returns the base-10 logarithm of a number. | log10( < number > ) |
| 17 | MROUND | Rounds a number to a specified multiple. | mround( < number > , <multiple> ) |
| 18 | ODD | Rounds to the nearest odd integer. | odd( < number > ) |
| 19 | PI | The value of PI. | PI() |
| 20 | POWER | Calculates a number raised to a power. | power( < number> , <power> ) |
| 21 | PRODUCT | Multiplies an array of numbers. | product( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 22 | QUOTIENT | Returns the integer result of division. | quotient( < numerator> , <denominator> ) |
| 23 | RADIANS | Converts an angle into radians. | radians( < angle > ) |
| 24 | RANDBETWEEN | Calculates a random number between two numbers. | randbetween( < bottom> , <top> ) |
| 25 | ROUND | Rounds a number to a specified number of digits (specified in a parameter). | round( < number> , <num digits> ) |
| 26 | ROUNDDOWN | Rounds a number down (towards zero). | ROUNDDOWN( <number> , <num digits> ) |
| 27 | ROUNDUP | Rounds a number up (away from zero). | ROUNDUP( <number> , <num digits> ) |
| 28 | SIN | Returns the sine value of an angle. | sin( < number > ) |
| 29 | SUBTOTAL | Returns a statistical summary for a series of data. | subtotal( < function num>, <ref 1>, [<ref 2>], [<ref 3>],…..[<ref n>]) |
| 30 | SUM | Adds numbers together and returns the total value. | sum( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 31 | SUMIF | Sums numbers that meet a criteria. | sumif( <range> , <criteria> , [<sum range>]) |
| 32 | SUMIFS | Sums numbers that meet multiple criteria. | sumifs(< sum range>, <criteria range 1> , < criteria 1> , [<criteria range 2>, <criteria 2>],[<criteria range 3>, <criteria 3>],….[<criteria range n>, <criteria n>]) |
| 33 | SUMPRODUCT | Multiplies arrays of numbers and adds up the resultant array. | sumproduct( < array 1> , [<array 2>], [<array 3>], [<array 4>],….[<array 4>]) |
| 34 | TAN | Returns the tangent value of an angle. | tan( < number > ) |
Statistical Functions
| S.no | Function | Description | Syntax |
| 1 | AVERAGE | Averages given numbers. | average( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 2 | AVERAGEIF | Averages given numbers that meet a criteria. | averageif( <range> , <criteria> , [<average range>]) |
| 3 | AVERAGEIFS | Averages given numbers that meet multiple criteria. | averageifs(< average range>, <criteria range 1> , < criteria 1> , [<criteria range 2>, <criteria 2>],[<criteria range 3>, <criteria 3>],….[<criteria range n>, <criteria n>]) |
| 4 | CORREL | Calculates and returns the correlation of two series. | correl( <array 1> , <array 2> ) |
| 5 | COUNT | Counts and returns the number of cells that contain a specific value. | count( <value 1> , [<value 2>], [<value 3>], [< value 4>]….[<value n>] ) |
| 6 | COUNTA | Counts and returns the number of cells that are non-blank. | COUNTA( <value 1> , [<value 2>], [<value 3>], [< value 4>]….[<value n>] ) |
| 7 | COUNTBLANK | Counts and returns the number of cells that are blank. | countblank( < range > ) |
| 8 | COUNTIF | Counts and returns the number of cells that meet a criteria. | countif( <range> , <criteria> ) |
| 9 | COUNTIFS | Counts and returns the number of cells that meet multiple criteria. | countifs( <criteria range 1> , < criteria 1> , [<criteria range 2>, <criteria 2>],[<criteria range 3>, <criteria 3>],….[<criteria range n>, <criteria n>]) |
| 10 | FORECAST | Predict future y-values from a linear trend line. | forecast( <x> , <known_ys>, <known_xs> ) |
| 11 | FREQUENCY | Counts values that fall within specified ranges. | frequency(< data array> , <bins array> ) |
| 12 | GROWTH | Calculates Y values based on exponential growth. | growth( <knowns ys> , [<known xs>], [<new xs>], [const]) |
| 13 | INTERCEPT | Calculates the Y intercept for the “best-fit” line. | intercept(<known ys>, <known xs>) |
| 14 | LARGE | Returns the kth largest value. | large( <array> , k) |
| 15 | LINEST | Returns statistics about a trendline. | linest( < known ys> , [<known xs>], [<const>] , [<stats>] ) |
| 16 | MAX | Returns the largest number from the given series. | max( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 17 | MEDIAN | Returns the median number from the given series. | median( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 18 | MIN | Returns the smallest number from the given series. | min( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 19 | MODE | Returns the most common number in the given series. | mode( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 20 | PERCENTILE | Returns the kth percentile. | percentile( <array>, <k>) |
| 21 | PERCENTILE.INC | Returns the kth percentile where k is inclusive. | percentile.inc( <array> , <k> ) |
| 22 | PERCENTILE.EXC | Returns the kth percentile where k is exclusive. | percentile.exc( <array> , <k> ) |
| 23 | QUARTILE | Returns the specified quartile value. | quartile( < array> , <quart> ) |
| 24 | QUARTILE.INC | Returns the specified quartile value (Inclusive) . | quartile.inc( <array> , <quart> ) |
| 25 | QUARTILE.EXC | Returns the specified quartile value (Exclusive). | quartile.exc( <array> , <quart> ) |
| 26 | RANK | Returns the rank of a number within a series. | rank(< number> , <ref> , [<order>]) |
| 27 | RANK.AVG | Returns the rank of a number within a series. (Averages). | rank.avg (< number> , <ref> , [<order>]) |
| 28 | RANK.EQ | Returns the rank of a number within a series. (Top Rank). | rank.eq (< number> , <ref> , [<order>]) |
| 29 | SLOPE | Calculates and returns the value of slope from linear regression. | slope( < known ys> , <known xs>) |
| 30 | SMALL | Returns the smallest value in a series of numbers. | small( < array>, <k>) |
| 31 | STDEV | Calculates and returns the standard deviation. | stdev( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 32 | STDEV.P | Calculates and returns the Standard deviation of an entire population. | stdev.p( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 33 | STDEV.S | Calculates and returns the Standard deviation of a sample. | stdev.s( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 34 | STDEVP | Calculates and returns the Standard deviation of an entire population | stdevp( < number 1> , [<number 2>], [<number 3>],……[<number n>]) |
| 35 | TREND | Calculates and returns Y values based on a trendline. | trend( <knowns ys> , [<known xs>], [<new xs>], [const]) |
String (or) Text Functions
| S.no | Function | Description | Syntax |
| 1 | CLEAN | Removes all characters that are not printable. | clean( <text> ) |
| 2 | DOLLAR | Adds currency format and decimals to a number. | dollar( < number> , [<decimals>]) |
| 3 | FIND | Locates position of lookup text within a cell. Case-sensitive. | find( < search text > , <search source text>, [<start pos>] ) |
| 4 | LEFT | Returns the specified number of characters from the left side of a string | LEFT(<text>, < no of characters> ) |
| 5 | LEN | Counts number of characters in a string. Spaces are also included. | len( < string value> ) |
| 6 | MID | Extracts text from the middle of a string from the mentioned position. | mid( < string value> , < start position>, < num characters>) |
| 7 | PROPER | Converts text to proper case. | proper( < string value>) |
| 8 | REPLACE | Replaces a portion of text , with another text in a string. | replace( < string value>), < string to find>, < string to replace> ) |
| 9 | REPT | Repeats text a number of times in a string. | rept( < string value> , < number of times>) |
| 10 | RIGHT | Returns the specified number of characters from the right side of a string | right( < string value>, < number of characters> ) |
| 11 | SEARCH | Locates position of text within a string.This search is not Case-sensitive. | search( < search text> , < position to start searching> ) |
| 12 | SUBSTITUTE | Finds and replaces text. This search and replace is Case-sensitive. | substitute( < string> , < old text> , < new text> , [ <instance num>] ) |
| 13 | TEXT | Converts a value into text data type with a specific number format. | text( < string value> , <format text > ) |
| 14 | TRIM | Removes all the spaces in front of ( leading) and at the end of ( trailing) the text/ string expression. | trim( < string value > ) |
Examples of Using Worksheetfunctions in VBA
Deletion of Rows That are Blank in a Range
Sub row_del_demo()' declare variableDim range_new' loop to iterate through the rowsFor i = 1 To 60 ' get the range of rows Set range_new = Worksheets("Wonders").Rows(i &amp;amp;amp;amp; ":" &amp;amp;amp;amp; i) ' find the number of blank columns in that row fill_cols = Application.WorksheetFunction.CountA(range_new) ' delete the entire row if all columns in it are blank If fill_cols = 0 Then Sheets("Wonders").Rows(i).Delete End IfNextEnd Sub
Using Worksheet Function to Find the Sum of Integers in an Array
Sub worksh_demo()' declare variables and objectsDim a(10) As IntegerDim sum_10 As Integer' loop to assign integer values to all the 10 array elementsFor i = 0 To 9a(i) = i + 10Debug.Print a(i)Next' find the sum of all numbers in the array using the worksheetfunctionsum_10 = Application.WorksheetFunction.Sum(a)' Print the sumDebug.Print sum_10End Sub
Using Average and Sum Worksheet Functions
Sub wksfun_Demo() ' declare variables Dim tot, avg, res, sci, soc, mat, eng, lan ' assigning values sci = 67 soc = 78 eng = 90 mat = 89 lan = 45 ' find the total using a worksheet function tot = WorksheetFunction.Sum(sci, soc, eng, lan, mat) Debug.Print "The total of all marks is " &amp;amp;amp;amp; tot 'find the average marks avg = WorksheetFunction.Average(sci, soc, mat, eng, lan) Debug.Print "The average marks of the student is " &amp;amp;amp;amp; avg End Sub
Worksheet Function That Uses the Data From Excel Sheet as Parameter
Sub wksh_demo()maximum_value = WorksheetFunction.Max(range("D2:D9"))Debug.Print maximum_valueEnd SubOutput :
528
Conclusion
Worksheetfunction object helps us use proper format at our convenience to code our program instead of using the macro recorded to code any formula. We can either use object variables from our procedure or cell values from worksheets / any other Excel workbooks as parameters while we use the Worksheetfunction. The application object in front of it is optional. (Application.Worksheetfunction.)
Tagged with: Excel, excel formulas, Formulas, Microsoft, Microsoft Excel, VBA, VBA For Excel, Worksheet, worksheetfunction, Worksheets