The Evaluate function is a hidden secret that few Excel VBA users know. In this blog post, we will explore what it does, and why you will be happy to know it.
The Excel VBA Evaluate Function converts a Microsoft Excel name to an object or a value.
The syntax for the Evaluate function looks like this;
The name can be a formula or the name of an object that you want to evaluate. This name must not exceed 255 characters.
What does this mean? And why is this useful?
Well let’s have a look at some typical uses of the Evaluate Function.
Watch the Video – Excel VBA Evaluate Function
The most common use of Evaluate is to simplify using worksheet formulas in VBA.
In this example, the worksheet function Sum has been used to total column A.
Dim Total as Long Total = Application.Sum(Range("A:A"))
But it can be simplified to this.
Dim Total as Long Total = Evaluate("Sum(A:A)")
When writing the formula in the Evaluate function, you do not need to type the equals sign, and it must be enclosed in inverted commas ” “.
If references to worksheets are required, then the code can be shortened even more impressively.
Worksheets("Sheet1").Range("A2").Value * Worksheets("Sheet2").Range("A2").Value
You can also write the Evaluate function in shorthand by using square brackets instead of the word Evaluate, parenthesis and inverted commas.
For example a formula could be written like this;
Evaluate is Not Limited to Just Formulas
Instead of using the Range object to select a range, you could use the Evaluate shorthand.
Could be this;
The Excel VBA Evaluate function certainly does have some impressive uses and can simplify your code very quickly.
It is worth exploring what else this function is capable and see how it could benefit you.