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:

Screenshot of inserting a function in Excel.

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:

Example of a sum, 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.

Example of an auto-calculated answer in Excel.

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.noFunctionDescriptionSyntaxExampleOutput
1ANDChecks 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
2IFIf 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”
3IFERRORThis is a combination of IF and ISERROR.IF(ISERROR( < calculation> ), 0, < calculation result >)IF(ISERROR(2/0),0,2/0)0
4ORChecks 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.noFunctionDescriptionSyntax
1CHOOSEChooses a value from a list based on its index number and the index number provided in the parameter.choose( <index num>, value1, value2)
2HLOOKUPLooks up a value in the first row of a table and returns a value.Hlookup( <lookup value>, <table Array>, <row index num>, <range lookup>)
3INDEXReturns a value based on its column number and row numbers.INDEX (array, <row num>, <[col num]>, <[area num]>)
4LOOKUPLooks up values either horizontally (in rows) or vertically (in columns) .Lookup( < lookup value>, <range of cols or table>, <boolean result>)
5MATCHSearches for a value in a list and returns its position.MATCH (<lookup value>, <lookup array>, <[match type]>)
6TRANSPOSEChanges the orientation of a range of cells. Vertical to horizontal and vice versa.Transpose(<array or range that needs to be flipped>)
7VLOOKUPLooks 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.noFunctionDescriptionSyntax
1DATEReturns a date from a combination of year, month, and day.DATE(Year, month, day)
2DATEVALUEConverts a date that is stored as text into a valid date (in acceptable data format).datevalue(< date text> )
3DAYReturns the day as a number (1-31) from a valid date.day( <serial number> ) 
4DAYS360Returns difference (in days) between 2 valid dates in a 360-day year.DAYS360( < start date> , <end date> , <method>
5EDATEReturns a valid date, “n” months away from a valid start date.EDATE(<start date>, <months> )
6EOMONTHReturns the last day of the month, “n” months away from a valid date.eomonth(<start date>, <months>)
7HOURReturns the hour as a number (0-23) from a valid time.hour(< serial number>)
8MINUTEReturns the minute as a number (0-59) from a valid time.minute(<serial number>)
9MONTHReturns the month as a number (1-12) from a valid date.month(<serial number>)
10NETWORKDAYSReturns the number of working days between 2 valid dates.networkdays(<start date>, <end date>, [<holidays>])
11NETWORKDAYS.INTLReturns 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>])
12NOWReturns the current date and time as in the system.now()
13SECONDReturns the second as a number (0-59) from a valid time.second(<serial number>)
14TIMEReturns the time from a valid combination of hour, minute, and second.time(<hour>, <minute>, <second>)
15TIMEVALUEConverts a time stored as text into a valid time.timevalue(<time text>)
16WEEKDAYReturns the day of the week as an integer (1-7).weekday(<serial number>, [<return type>])
17WEEKNUMReturns the week number in a year (1-52).weeknum(<serial number>, [<return type>])
18WORKDAYReturns the date n working days from a valid date.workday(<start date>, days, [ <holidays >])
19YEARReturns the year from a valid date.year(< serial number >)
20YEARFRACReturns the fraction of a year between 2 valid dates.yearfrac(<start date , <end date> , [<basis>])

Engineering Functions

S.noFunctionDescriptionSyntax
1CONVERTConvert 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.noFunctionDescriptionSyntax
1FVCalculates and returns the future value of an investment.fv(<rate>, <nper>, <payment>, [pv], [<type>])
2PVCalculates and returns the present value of an investment.fv(<rate>, <nper>, <payment>, [fv], [<type>])
3NPERCalculates and returns the total number of payment periods in an investment.nper(<rate, <payment>, <pv> , [<fv>], [type])
4PMTCalculates and returns the payment amount in an investment.pmt(<rate>, <nper>, <pv> , [<fv>], [<type>])
5RATECalculates and returns the interest rate for an investment.rate(<nper>, <payment> ,<pv>, [<fv>], [<type>], [<guess>])
6NPVCalculates and returns the net present value of an investment.npv(<rate>, <value 1>, [<value 2>], [<value 3>],….., [<value n>])
7IRRCalculates and returns the internal rate of return for a set of periodic CFs.irr( <values>, [<guess>] )
8XIRRCalculates and returns the internal rate of return for a set of non-periodic CFs.xirr(<values>, <dates>, [<guess>] )
9PRICECalculates and returns the price of a bond.price(<settlement>, <maturity> , <rate>, <yield> , <redemption>, <frequency>, [<basis>])
10INTRATEReturns the interest rate of a completely invested security.intrate(<settlement>, <maturity> , <investment> , <redemption>, <frequency>, [<basis>])

Information Functions

S.noFunctionDescriptionSyntax
1ISERRTests if cell value is an error, ignores #N/A. Returns boolean value TRUE/FALSEiserr( < value > )
2ISERRORTests if cell value is an error. Returns a boolean value TRUE/FALSEiserror( < value> )
3ISEVENTests if cell value is even. Returns a boolean value TRUE/FALSEiseven( < number> )
4ISLOGICALTests if cell is logical (TRUE or FALSE). Returns a boolean value TRUE/FALSEislogical( < value > )
5ISNATests if cell value is #N/A. Returns a boolean value TRUE/FALSEisna( < value > )
6ISNONTEXTTests if cell is not text (blank cells are not text). Returns a boolean value TRUE/FALSEisnontext( < value > )
7ISNUMBERTests if cell is a number. Returns a boolean value TRUE/FALSEisnumber( < value > )
8ISODDTests if cell value is odd. Returns a boolean value TRUE/FALSEisodd( < number> )
9ISTEXTTests if cell is text. Returns a boolean value TRUE/FALSEistext( < value > )
10TYPEReturns the type of value stored in a cell.type( < value > )

Math Functions

S.noFunctionDescriptionSyntax
1ABSCalculates and returns the absolute value of a number.abs( < number> )
2AGGREGATEIt defines and performs calculations for a database or a list.AGGREGATE(<function number>, <options>, <array >, [k] >
3CEILINGRounds a number up, to the nearest specified multiple and returns the value.ceiling( < number > , <significance> )
4COSReturns the cosine value of an angle.cos( < number > )
5DEGREESConverts radians to degrees and returns the value.degrees( < angle> )
6DSUMSums up database records that meet certain criteria.dsum( <database> , <field> , <criteria> )
7EVENRounds to the nearest even integer and returns the value.even( < number > )
8EXPCalculates the exponential value for a given number and returns the value.exp( < number > )
9FACTReturns the factorial value.fact( <number> )
10FLOORRounds a number down to the nearest specified multiple.floor( <number> , <significance> )
11GCDReturns the greatest common divisor.gcd( < number 1> , [<number 2>], [<number 3>],……[<number n>])
12INTRounds a number down to the nearest integer.int( < number > )
13LCMReturns the least common multiple.lcm( < number 1> , [<number 2>], [<number 3>],……[<number n>])
14LNReturns the natural logarithm of a number.LN( < number > )
15LOGReturns the logarithm of a number to a specified base.log( <number> , [<base>])
16LOG10Returns the base-10 logarithm of a number.log10( < number > )
17MROUNDRounds a number to a specified multiple.mround( < number > , <multiple> )
18ODDRounds to the nearest odd integer.odd( < number > )
19PIThe value of PI.PI()
20POWERCalculates a number raised to a power.power( < number> , <power> )
21PRODUCTMultiplies an array of numbers.product( < number 1> , [<number 2>], [<number 3>],……[<number n>])
22QUOTIENTReturns the integer result of division.quotient( < numerator> , <denominator> )
23RADIANSConverts an angle into radians.radians( < angle > )
24RANDBETWEENCalculates a random number between two numbers.randbetween( < bottom> , <top> )
25ROUNDRounds a number to a specified number of digits (specified in a parameter).round( < number> , <num digits> )
26ROUNDDOWNRounds a number down (towards zero).ROUNDDOWN( <number> , <num digits> )
27ROUNDUPRounds a number up (away from zero).ROUNDUP( <number> , <num digits> )
28SINReturns the sine value of an angle.sin( < number > )
29SUBTOTALReturns a statistical summary for a series of data.subtotal( < function num>, <ref 1>, [<ref 2>], [<ref 3>],…..[<ref n>])
30SUMAdds numbers together and returns the total value.sum( < number 1> , [<number 2>], [<number 3>],……[<number n>])
31SUMIFSums numbers that meet a criteria.sumif( <range> , <criteria> , [<sum range>])
32SUMIFSSums 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>])
33SUMPRODUCTMultiplies arrays of numbers and adds up the resultant array.sumproduct( < array 1> , [<array 2>], [<array 3>], [<array 4>],….[<array 4>])
34TANReturns the tangent value of an angle.tan( < number > )

Statistical Functions

S.noFunctionDescriptionSyntax
1AVERAGEAverages given numbers.average( < number 1> , [<number 2>], [<number 3>],……[<number n>])
2AVERAGEIFAverages given numbers that meet a criteria.averageif( <range> , <criteria> , [<average range>])
3AVERAGEIFSAverages 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>])
4CORRELCalculates and returns the correlation of two series.correl( <array 1> , <array 2> )
5COUNTCounts and returns the number of cells that contain a specific value.count( <value 1> , [<value 2>], [<value 3>], [< value 4>]….[<value n>] )
6COUNTACounts and returns the number of cells that are non-blank.COUNTA( <value 1> , [<value 2>], [<value 3>], [< value 4>]….[<value n>] )
7COUNTBLANKCounts and returns the number of cells that are blank.countblank( < range > )
8COUNTIFCounts and returns the number of cells that meet a criteria.countif( <range> , <criteria> )
9COUNTIFSCounts 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>])
10FORECASTPredict future y-values from a linear trend line.forecast( <x> , <known_ys>, <known_xs> )
11FREQUENCYCounts values that fall within specified ranges.frequency(< data array> , <bins array> )
12GROWTHCalculates Y values based on exponential growth.growth( <knowns ys> , [<known xs>], [<new xs>], [const])
13INTERCEPTCalculates the Y intercept for the “best-fit” line.intercept(<known ys>, <known xs>)
14LARGEReturns the kth largest value.large( <array> , k)
15LINESTReturns statistics about a trendline.linest( < known ys> , [<known xs>], [<const>] , [<stats>] )
16MAXReturns the largest number from the given series.max( < number 1> , [<number 2>], [<number 3>],……[<number n>])
17MEDIANReturns the median number from the given series.median( < number 1> , [<number 2>], [<number 3>],……[<number n>])
18MINReturns the smallest number from the given series.min( < number 1> , [<number 2>], [<number 3>],……[<number n>])
19MODEReturns the most common number in the given series.mode( < number 1> , [<number 2>], [<number 3>],……[<number n>])
20PERCENTILEReturns the kth percentile.percentile( <array>, <k>)
21PERCENTILE.INCReturns the kth percentile where k is inclusive.percentile.inc( <array> , <k> )
22PERCENTILE.EXCReturns the kth percentile where k is exclusive.percentile.exc( <array> , <k> )
23QUARTILEReturns the specified quartile value.quartile( < array> , <quart> )
24QUARTILE.INCReturns the specified quartile value (Inclusive) .quartile.inc( <array> , <quart> )
25QUARTILE.EXCReturns the specified quartile value (Exclusive).quartile.exc( <array> , <quart> )
26RANKReturns the rank of a number within a series.rank(< number> , <ref> , [<order>])
27RANK.AVGReturns the rank of a number within a series. (Averages).rank.avg (< number> , <ref> , [<order>])
28RANK.EQReturns the rank of a number within a series. (Top Rank).rank.eq (< number> , <ref> , [<order>])
29SLOPECalculates and returns the value of slope from linear regression.slope( < known ys> , <known xs>)
30SMALLReturns the smallest value in a series of numbers.small( < array>, <k>)
31STDEVCalculates and returns the standard deviation.stdev( < number 1> , [<number 2>], [<number 3>],……[<number n>])
32STDEV.PCalculates and returns the Standard deviation of an entire population.stdev.p( < number 1> , [<number 2>], [<number 3>],……[<number n>])
33STDEV.SCalculates and returns the Standard deviation of a sample.stdev.s( < number 1> , [<number 2>], [<number 3>],……[<number n>])
34STDEVPCalculates and returns the Standard deviation of an entire populationstdevp( < number 1> , [<number 2>], [<number 3>],……[<number n>])
35TRENDCalculates and returns Y values based on a trendline.trend( <knowns ys> , [<known xs>], [<new xs>], [const])

String (or) Text Functions

S.noFunctionDescriptionSyntax
1CLEANRemoves all  characters that are not printable.clean( <text> )
2DOLLARAdds currency format and decimals to a number.dollar( < number> , [<decimals>])
3FINDLocates position of lookup text within a cell. Case-sensitive.find( < search text > , <search source text>, [<start pos>] )
4LEFTReturns the specified number of characters from the left side of a stringLEFT(<text>, < no of characters> )
5LENCounts number of characters in a string. Spaces are also included.len( < string value> )
6MIDExtracts text from the middle of a string from the mentioned position.mid( < string value> , < start position>, < num characters>)
7PROPERConverts text to proper case.proper( < string value>)
8REPLACEReplaces a portion of text , with another text in a string.replace( < string value>), < string to find>, < string to replace> )
9REPTRepeats text a number of times in a string.rept( < string value> , < number of times>)
10RIGHTReturns the specified number of characters from the right side of a stringright( < string value>, < number of characters> )
11SEARCHLocates position of text within a string.This search is not Case-sensitive.search( < search text> , < position to start searching> )
12SUBSTITUTEFinds and replaces text. This search and replace is Case-sensitive.substitute( < string> , < old text> , < new text> , [ <instance num>] )
13TEXTConverts a value into text data type with a specific number format.text( < string value> , <format text > )
14TRIMRemoves 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 variable
Dim range_new

' loop to iterate through the rows
For i = 1 To 60
    ' get the range of rows
    Set range_new = Worksheets("Wonders").Rows(i &amp;amp;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 If
Next

End Sub
Example of deleting rows that are blank in a range in Excel

Using Worksheet Function to Find the Sum of Integers in an Array

Sub worksh_demo()

' declare variables and objects
Dim a(10) As Integer
Dim sum_10 As Integer

' loop to assign integer values to all the 10 array elements
For i = 0 To 9
a(i) = i + 10
Debug.Print a(i)
Next

' find the sum of all numbers in the array using the worksheetfunction
sum_10 = Application.WorksheetFunction.Sum(a)

' Print the sum
Debug.Print sum_10

End 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;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;amp; avg
    
End Sub
Example of Using Worksheet Function to Find the Sum of Integers in an Array in Excel

Worksheet Function That Uses the Data From Excel Sheet as Parameter

Screenshot of data in an Excel sheet
Sub wksh_demo()
maximum_value = WorksheetFunction.Max(range("D2:D9"))
Debug.Print maximum_value
End Sub

Output :

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.)

Leave a Reply

Your email address will not be published. Required fields are marked *