This post is by guest blogger Yoav Eze, his bio can be fond at the bottom of the post. Thanks Yoav!
It’s always nice to get content from other Excel developers, so if you have an interesting story, please feel free to share it here!!! Get in touch: rossmcleanatgoogledotcom
That now makes a list of 2 guest bloggers on MIE in 5 years, Sam and Yoav, I’m hoping to grow that list!!!
Now, over to Yoav.
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.
.
Worksheet Function
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 this function won’t work:
Application.WorksheetFunction.Sum(A1:A10)
But this will work prefectly:
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
.
will return the sum of the range A1 to B3
.
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, so for this sheet:
.
..
The output will be:
a1
b0
b1
c0
d0
f0
b2
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. Can you think of anywhere you could use these features right now?
About the author
Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces
PDF to Excel conversion software.
