Evaluate Method

It is possible to access any of the Excel worksheet functions by using the Evaluate method.

Evaluate[ISBLANK("B4")] 

The following line however will generate an error ??

Application.WorksheetFunction.IsBlank 

The Evaluate Method can be used to calculate worksheet formulas.
The normal syntax is:
Evaluate("=formula")
There is also a shorthand format, which uses Square brackets in place of the double quotes.
[=formula]
The formula can by any valid formula with or without an equal sign.
The following four lines are all equivalent

Application.Evaluate("=SUM(10,20,30)") 
Evaluate("SUM(10,20,30)")
[=SUM(10,20,30)]
[SUM(10,20,30)]

These formulas can contain worksheet functions even those available from the WorksheetFunction object or they can be worksheet array formulas.


The advantage of using the Evaluate method is that the string can be created using variables

Dim sCellAddress As String 
sCellAddress = "B4"
Evaluate("=ISBLANK(" & sCellAddress & ")")

In the above example the ISBLANK function is not available from the WorksheetFunction object as VBA has an equivalent function ISEMPTY.


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext