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

Ong Chin Hooi says

Woo! that was good make more of these, please.

Thanks.

No Thanks says

“Inverted commas”? Seriously?? Last I heard, those were called “quotation marks”, “double quotes”, or just “quotes”. Aside from that, it’s a good (though a bit shallow) article.

Alan Murray says

Yes, they go by all of those names and some are specific on region.