Evaluate Method

You can also use the following abbreviations although they are not encouraged.
This method can be used to generate references to Range objects and also for calculating worksheet formulas.
The normal syntax is as follows:

Application.Evaluate("Expression") 

There are also several shortcut formats to this method as well.

Evaluate("Expression") 

You can also remove the quotes and surround the expression with square brackets.

[Expression] 

Calculating Formulas

The Expression can be any valid worksheet calculation, with or without the equal sign on the left.
The worksheet calculations can include worksheet functions that are not made available to VBA using the WorksheetFunction object.
The worksheet calculations can also be array formulas.
For example the ISBLANK worksheet function is not accessible using WorksheetFunction object because VBA has the equivalent IsEmpty() function.
The following two lines of code are equivalent.

Call MsgBox(Evaluate("=ISBLANK(A1)") 
MsgBox [ISBLANK(A1)]

The advantage of using the first technique is that the worksheet formula string can be generated using code making it more flexible.


Manipulating a Range

The following two lines of code select the range of cells "A1:D4".

Range("A1:D4").Select 
[A1:D4].Select

Application.Evaluate("A4").Value = "some text" 
Evaluate("A4").Value = "some text"
A4.Value = "some text"
[A4].Value = "some text"

This expression could it fact be simplified even more since the default property for a Range object is Value.
Using the default properties is definitley not recommended.

[A4] = "some text" 

You can also use this technique to quickly refer to cells using Named Ranges.

[Named_Range].Select 
[Named_Range] = "some text"

Quick Arrays

The Evaluate method can also be used to quickly obtain an array of numbers.

Dim arValues As Variant 
arValues = Application.Evaluate("Row(1:50)") = {1,2,3, .. 50}

Similarly you can use this technique to quickly insert a sequence of numbers into a range of cells.

Range("B1:B50").Value = Application.Evaluate("Row(1:50)") 

Other Named Objects

Evaluate can also be used with other named objects, such as drawing elements.
It can also be used with Named Ranges.



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