excel vba evaluate

Excel VBA Evaluate – Tips and Tricks to use Application.Evaluate

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Some call it the most powerful command in Excel VBA, while others struggle with simple VBA macros oblivious of its existence – today we with explore the VBA Evaluate function or more precisely the VBA Application.Evaluate function.

We will start with the basics and then move on to more elaborate examples and uses of the VBA Evaluate function.

VBA Evaluate

The Excel VBA Evaluate function converts a Microsoft Excel Name to an object or a value.

Syntax

The syntax for the Evaluate function in VBA is:

Parameters

Name
A formula or the Name of the object you want to evaluate. The length of the Name must be less than or equal to 255 characters.

Examples

Example 1: Simple formula evaluation

Example 2: Using Excel cells in formula evaluation

What out for the first example above! As it might evaluate differently depending on the Activeworksheet!

Example 3: Using functions in formula evaluation

vlookup table
This example is going to be a bit more complex for several reasons I will explain after the example.

Consider the table on the right I will use to run a simple VLOOKUP function. Let us pull up the value associated with the lookup name “Andrew”.

Now there are several things you need to learn from the example above:

  • Escaping strings enclosed within double apostrophes with another set of apostrophes “” – otherwise expect errors
  • Use a comma (,) to separate all function arguments REGARDLESS of your default system list separator as VBA assumes the comma by default
  • You don’t need an equals sign before your formula. The Excel VBA Evaluate functions assumes this by default

Now that we know the basics of the Excel VBA Evaluate function lets us explores some useful tips:

Tip 1: Use brackets instead

Did you know you can encapsulate Excel formulas in VBA in brackets to achieve the same results are embracing them within the Evaluate function? See example on the right. The Evaluate function can be easily swapped with square brackets to achieve same resutl.

Tip 2: Using brackets instead of Range

You can easily replace a VBA Range with square brackets as well:

Combining Tip 1 and 2 you can seriously shorten your VBA code:

Tip 3: Simple array definition

You can also define elements in a VBA Array in just a single line without needing to resort to the VBA Substring function like so:

Summary

The VBA Evaluate function is a very powerful tool if you want to easily reference cells in your workbook in your VBA Macros. There are however many pitfalls in using this function as well as its square brackets equivalent. Here are some:

  • Prone to errors – typical error includes forgetting that without preceding cells with the worksheet name the statement with always reference the Activesheet
  • Slightly slower – if you intend to run your code multiple times you might see a slight performance disadvantage. Usually you won’t notice this at all

In summary I personally think it make sense to know the VBA Application.Evaluate function as sometimes quick and dirty is all you need. However, I would avoid using it in complex VBA macros as it is easy to make a mistake, this comes especially as I am not a fan of embedding static cell references e.g. Range(“A1”) vs Cells(1,1). What is your take?

Related Posts