Excel VBA Evaluate Function – How to use this Secret VBA Function

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;

Evaluate(Name)

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.
Compare this;

Worksheets("Sheet1").Range("A2").Value * Worksheets("Sheet2").Range("A2").Value

To this;

Evaluate("Sheet1!A2*Sheet2!A2")

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;

[Sum(A:A)]

Evaluate is Not Limited to Just Formulas

Instead of using the Range object to select a range, you could use the Evaluate shorthand.

So this;

Range("A2").Select

Could be this;

[A2].Select

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.

More Excel VBA Tutorials

• 6 ways to find the last row in a list
• 5 examples of why and how to use the Immediate Window
• Automatically refresh a PivotTable with VBA
• Export all worksheets to PDF