Excel VBA Evaluate Function – How to use this Secret VBA Function - KING OF EXCEL

Saturday, December 21, 2019

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

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

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

Evaluate Function Examples

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.
#evba #etipfree
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment