Boost your Macro Power with This Excel Formula Trick

Excel has a powerful set of formulas, and Visual Basic is a capable language, but have you ever wondered if you can use the Excel formulas within your VBA macros?

There is a way! In fact there are at least two ways to go about this, each option is good for a different scenario.

WorksheetFunction

If you want to use a standard function within your VBA code, and you don’t need to allow the sheet’s user to change it in anyway (i.e., it’s in the code and shouldn’t be changed), you can use the Excel’s WorksheetFunction object. This object contains all Excel’s functions as member functions, so you can use them directly.

For example:

Application.WorksheetFunction.Sum(TheRange)

This will return the sum of the cells in the range defined by ‘TheRange’.

When a range is required within a worksheetfunction function you will need to use a VBA object.

So while this function won’t work:

Application.WorksheetFunction.Sum(A1:A10)

But this will work perfectly:

Application.WorksheetFunction.Sum(Range("A1:A10")

Additionally, when you type the period after WorksheetFunction in the VBA editor, Excel’s intellisense will display the list of all the functions as they are the members of that object. You can also use the editor’s Object Browser (available via the F2 key or View -> Object Browser) to view those functions, including the arguments they get and the return values types.

Evaluate

Here’s where it gets really cool…

Another way to go is to use the Excel’s worksheet or application object’s Evaluate function. This function takes a string and evaluates it, returning either the value it refers to (if it’s a formula) or the range it refers to (if it is a cell or range reference in A1 style).

You can also use square brackets – [] – instead of evaluate.

Examples:

Me.Evaluate("A1");

will return a reference to cell A1

Evaluate("Sum(A1:B3)");

will return the sum of the range A1 to B3

[A1].Font.Bold = True;

will set font of the A1 cells of the current worksheet to be bold

You can combine loops and formulas this way:

For I = 2 To 8
    Debug.Print Evaluate("A"&I), Evaluate("countif(A1:A"&(I-1) & ",A"&I & ")")
Next I

This will print for each cell in range A1:A100 how many times the cell value has appeared before in the range, like so:

a 1
b 0
b 1
c 0
d 0
f 0
b 2

You can make this code even cooler by using a formula to count how many values there are in column A, like so:

For I = 2 To [counta(A:A)]
    Debug.Print Evaluate("A"&I), Evaluate("countif(A1:A"&(I-1) & ",A"&I & ")")
Next I

Note that I used [counta(A:A)] to count how many cells have values in column A directly from within Visual Basic.

Summary

So there you have it! Using those notations, you can combine the flexibility and power of VBA with the ease of use and functionality of Excel Formulas.

P.S.

Don’t forget to check out our PDF To Excel Converter. It can save you a lot of precious time you now spend on retyping PDF data.