Use Any Formula: Worksheetfunction in VBA

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