Some call it the most powerful command in Excel VBA, while others struggle with simple VBA macros oblivious of its existence – today we with explore the VBA Evaluate function or more precisely the VBA Application.Evaluate function. Evalute is a great if you want to easily reuse your Excel formulas in a context of a VBA macro.
We will start with the basics and then move on to more elaborate examples and uses of the VBA Evaluate function.
VBA Evaluate
The Excel VBA Evaluate function converts a Microsoft Excel Name to an object or a value.
Syntax
The syntax for the Evaluate function in VBA is:
Evaluate( Name )
Name
A formula or the Name of the object you want to evaluate. The length of the Name must be less than or equal to 255 characters.
Examples
Below are examples of how to use the VBA Evaluate function.
Example 1: Simple formula evaluation
You can evaluate simple mathematical formulas:
Evaluate("1+1")
'Result: 2
Example 2: Using Excel cells in formula evaluation
The real benefits of Evaluate come when you want to replicate features that you are used to using in Excel spreadsheets. Below as simply multiplication of 2 cells from the Activesheet.
Evaluate("A1*B2") 'Activeworksheet, A1 = 10, B2 = 2
'Result: 20
Evaluate("'Sheet1'!A1*'Sheet1'!A1B2") '"Sheet1", A1 = 10, B2 = 2
'Result: 20
Watch out for the first example above! As it might evaluate differently depending on the Activeworksheet used!
Example 3: Using functions in formula evaluation
This example is going to be a bit more complex for several reasons I will explain after the example.
Consider the table on the right I will use to run a simple VLOOKUP function. Let us pull up the value associated with the lookup name “Andrew”.
Evaluate("VLOOKUP(""Andrew"",A1:B5,2,FALSE)")
'Result: 2
Now there are several things you need to learn from the example above:
- Escaping strings enclosed within double apostrophes with another set of apostrophes “” – otherwise expect errors
- Use a comma (,) to separate all function arguments REGARDLESS of your default system list separator as VBA assumes the comma by default
- You don’t need an equals sign before your formula. The Excel VBA Evaluate functions assumes this by default
Now that we know the basics of the Excel VBA Evaluate function lets us explores some useful tips.
Use Evaluate brackets instead of Range
Did you know you can encapsulate Excel formulas in VBA in brackets to achieve the same results are embracing them within the Evaluate function? See example on the right. The Evaluate function can be easily swapped with square brackets to achieve same resutl.
You can easily replace a VBA Range with square brackets as well:
'This is the same...
Range("A1").Value
'...as this
[A1].Value
'This is the same...
Range("A1:A2").Select
'...as this
[A1:A2].Select
You can also seriously shorten your VBA code:
'Long version
Application.Sum(Range("A1:A2"))
'Short version
[SUM(A1:A2)]
Array definition using VBA Evaluate
You can also define elements in a VBA Array in just a single line without needing to resort to the VBA Substring function like so:
Dim arr() As Variant
arr = [{10,30,40}]
Debug.Print arr(1)
'Result: 10
Summary
The VBA Evaluate function is a very powerful tool if you want to easily reference cells in your workbook in your VBA Macros. There are however many pitfalls in using this function as well as its square brackets equivalent. Here are some:
- Prone to errors – typical error includes forgetting that without preceding cells with the worksheet name the statement with always reference the Activesheet
- Slightly slower – if you intend to run your code multiple times you might see a slight performance disadvantage. Usually you won’t notice this at all
In summary I personally think it make sense to know the VBA Application.Evaluate function as sometimes quick and dirty is all you need. However, I would avoid using it in complex VBA macros as it is easy to make a mistake, this comes especially as I am not a fan of embedding static cell references e.g. Range(“A1”) vs Cells(1,1). What is your take?
