Excel Macro Class: WorksheetFunction Object

The WorksheetFunction object is a VBA container for all Microsoft Excel built-in worksheet functions. It is the result of using the WorksheetFunction property of the Application object.

How to refer to the WorksheetFunction object

Application.WorksheetFunction.a_given_function

or simply

WorksheetFunction.a_given_function

or also

Application.a_given_function

where, a_given_function is usually a VBA method (corresponding to a particular worksheet function in Excel)

The result of the function is generally associated with a variable of the relevant data type (Integer, Single, String, etc).

MyResult = Application.WorksheetFunction.a_given_function

Count and numerical worksheet functions

The following are some of the most common numerical methods of the WorksheetFunction object:

WorksheetFunction.CountA(Range("A1:D4")) 'number of cells with some content

WorksheetFunction.CountIf(Range("A1:D4"), "Yes") 'number of cells which value is “Yes”

WorksheetFunction.Sum(Range("A1:D4")) 'sum of values in that range

WorksheetFunction.Average(Range("A1:D4")) 'average of value in that rang

WorksheetFunction.Min(Range("A1:D4")) 'minimum value in that range

WorksheetFunction.Max(Range("A1:D4")) 'maximum value in that range

The same can be achieved with the Subtotal function, having the advantage to allow calculations ignoring hidden cells. This is very useful when applying filters.

WorksheetFunction.Subtotal(9, Range("A1:D4")) 'sum of values in that range

WorksheetFunction.Subtotal(109, Range("A1:D4")) 'sum of values in that range (ignoring hidden cells)

Subtotal function number and name

Function number (includes hidden values)	Function number (ignores hidden values)	Function
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Lookup worksheet functions

The Excel LOOKUP, VLOOKUP, and HLOOKUP functions have their equivalent method of the WorksheetFunction object in VBA and accept the same attributes. For example, a VLOOKUP to look up the country of a given user in the table below would be as follows:

Dim UserName As String, UserCountry As String

UserName = "Lisa"

UserCountry = WorksheetFunction.VLookup(UserName, Range("A1:D6"), 4, 0)

'returns "Sweden"

Similarly, the Excel MATCH and INDEX functions have their equivalent methods and accept the same arguments in VBA. It is important to remember that both functions work with a 1D array (or range); please check the Microsoft documentation to learn about these functions if you are not familiar with them (this blog focuses only on Excel VBA and assumes users already have a good command of Excel formulas and functions).

Dim UserName As String, UserCountry As String, UserRow As Integer

UserName = "Oscar"

UserRow = WorksheetFunction.Match(UserName, Range("A1:A6")) 'returns 5

UserCountry = WorksheetFunction.Index(Range("A1:A6"), UserRow) 'returns "Spain"

The Excel SEARCH function can also be accessed through the Search method of the VBA Worksheetfunction object. It accepts three arguments and is used to get the position of a substring within a string.

SubStrPosition = WorksheetFunction.Search(substring, string, start_position)

LetterPosition = WorksheetFunction.Search(“M”, ”Excel Macro Class”, 1) 'equals 7

Other worksheet functions

There are as many VBA WorksheetFunction methods as Excel built-in worksheet functions (see below the full list of WorksheetFunction methods available in VBA). Furthermore, some of these methods have their equivalent (and often more convenient) VBA function. A VBA function is a stand-alone command used in the VBA code. Let’s see a couple of examples to clarify that.

The Replace method of the WorksheetFunction object has an equivalent Replace function that does the exact same thing, but in slightly a different way. The Replace method accepts four required attributes: the string (or cell where it resides) that contains characters to be replaced, the start position of the character to be replaced, the number of characters to be replaced, and the string to replace those.

oldVersion = "Microsoft Excel 2013"

newVersion = WorksheetFunction.Replace(oldVersion, 17, 4, "2016")

In this example, the variable newVersion becomes “Microsoft Excel 2016” by replacing 4 characters in the oldVersion variable starting in position 17 (“2013”).

The same can be achieved with the Replace function in a simpler manner. In this case, it just needs the source string, the substring to be replaced, and the new substring to take its place.

newVersion = Replace(oldVersion, "2013", "2016")

Another example is the InStr VBA function used to find a substring within a string, in quite a similar way the WorksheetFunction.Search method explained earlier does.

LetterPosition = InStr("Excel Macro Class", "M", 1) 'returns 7

Hopefully that clarified the difference or similarity between the WorksheetFunction methods and VBA functions. Find the most commonly used VBA functions in this other post HERE.

Finally, depending on the situation, we may also want to insert the function or formula directly in the worksheet using the Formula property of the Range object as follows:

Worksheets("Sheet1").Range("A1:B3").Formula = "=SUM(A1:A10)"

List of all WorksheetFunction methods available in Excel VBA in alphabetical order