VBA Evaluate in Excel — Turn a Formula String into a Value

TL;DR — Evaluate("SUM(A1:A10)") hands a formula as text to Excel's calculation engine and returns the result. The [ ] shorthand — [A1], [SUM(A1:A10)] — is literally Evaluate in disguise. The difference that matters: brackets are fixed text, so you cannot drop a variable into them; Evaluate(aString) takes a string you build at runtime. A bad formula comes back as an Error variant, not a crash, so test it with IsError before you use it. Reach for it to run a formula you assembled in code — otherwise a normal cell formula or WorksheetFunction is clearer.

Sub EvaluateAFormula()
    Dim result As Variant
    result = Evaluate("SUM(A1:A10)")      ' Excel computes it, same as a cell would
    If IsError(result) Then
        MsgBox "That formula did not compute"
    Else
        MsgBox "Total: " & result
    End If
End Sub

The other two tools in this family run code by name — Application.Run runs a macro, CallByName runs a member. Evaluate completes the set by running a formula: you hand Excel a worksheet expression as text and it computes the answer with the very same engine your cells use. That is the one idea behind all three — a string becomes an executed action at runtime — and Evaluate is the most Excel-flavoured version of it, because the string you pass is just a formula, the language you already know from the grid. The same warning carries over: because the formula is text, nothing checks it until it runs, so only pass Evaluate an expression you built or validated yourself.

What you'll learn

Where Evaluate sits next to Application.Run and CallByName
The square-bracket shorthand [ ] and why it is the same method
The critical difference: brackets are fixed text, Evaluate takes a runtime string
Reading the Error variant a bad formula returns, with IsError
Qualifying the call to the right sheet so references resolve where you mean
When to use Evaluate versus WorksheetFunction or a cell formula

The mental model: borrowing the worksheet's calculation engine

Everything in Excel that computes a value — every =SUM(...), every =VLOOKUP(...) — runs through one calculation engine. Evaluate is a door into that engine from VBA. You pass a formula the way you would type it into a cell (with or without the leading =), and Excel evaluates it and gives you the result as a Variant:

d = Evaluate("TODAY()")                    ' a date
n = Evaluate("2*PI()*10")                  ' a number
r = Evaluate("VLOOKUP(""Ada"", A:B, 2, 0)") ' a lookup, quotes doubled for the string

The mental shift is that you are not writing VBA logic here — you are writing an Excel formula and asking Excel to run it. Anything valid in a cell is valid inside Evaluate, including functions that have no VBA equivalent.

The square-bracket shorthand is Evaluate in disguise

This surprises almost everyone: the [ ] notation you have seen in other people's macros is not a special operator. It is a shorthand for Evaluate. These two lines are identical:

x = [SUM(A1:A10)]          ' shorthand
x = Evaluate("SUM(A1:A10)") ' what it actually does

[A1] is Evaluate("A1"), [Sales] (a named range) is Evaluate("Sales"), and [1+2] is Evaluate("1+2") which returns 3. Seeing the brackets as Evaluate is the aha that ties this page to the rest of the family: even the tidy shorthand is string-driven indirection under the surface.

The difference that matters: brackets are fixed, Evaluate takes a variable

Here is the one rule that decides which form to use, and the most common point of confusion. The text inside [ ] is literal — you cannot concatenate a variable into it, because the brackets are not a string:

Dim col As String: col = "B"
' WRONG - you cannot build a bracket expression from a variable:
' total = [SUM(col & "1:" & col & "10")]     ' does not do what you hope

' RIGHT - build the string, then Evaluate it:
total = Evaluate("SUM(" & col & "1:" & col & "10)")   ' SUM(B1:B10)

So the choice is mechanical: use [ ] when the whole formula is a constant you type once — it is shorter and reads well. Use Evaluate(...) the moment any part of the formula is built at runtime from variables, cell values, or user input. That is the entire practical difference between the two forms, and picking the bracket form for a dynamic formula is why people conclude "the brackets are broken" when they are simply the wrong tool for a variable.

Reading the result: a bad formula returns an Error variant

Evaluate does not raise a run-time error when the formula is wrong — it hands back an Error variant, the VBA form of #NAME?, #REF!, #DIV/0! and friends. If you use the result without checking, the error propagates silently:

Dim result As Variant
result = Evaluate("SUM(Nonexistent)")     ' returns an Error variant, does not crash
If IsError(result) Then
    MsgBox "Formula error - check the expression"
Else
    ' safe to use result
End If

Always guard a runtime-built expression with IsError before you trust the value. This is the opposite behaviour from WorksheetFunction, which raises a catchable run-time error on failure — a difference worth knowing when you choose between them.

Qualify the call so references resolve where you mean

An unqualified Evaluate (and every [ ] shorthand) resolves its references against the active sheet. If the active sheet is not the one you meant, Evaluate("A1") reads the wrong cell — a subtle bug when a macro runs from a different sheet than the developer tested on. Qualify the call to pin it down:

' Resolves A1:A10 on Sheet1 no matter which sheet is active:
total = Worksheets("Sheet1").Evaluate("SUM(A1:A10)")

Worksheet.Evaluate and Workbook.Evaluate let you say where the formula runs. Relying on the active sheet is fine for a quick one-off; for anything that ships, qualify the call so it does not depend on what the user happened to click last.

Evaluate vs WorksheetFunction vs a cell formula

Three ways to get a computed value, three jobs. A cell formula is right when the result belongs on the sheet and should recalculate — put =SUM(A1:A10) in a cell. WorksheetFunction is right when you want a specific function from VBA with IntelliSense and a clean error you can catch: WorksheetFunction.Sum(Range("A1:A10")). Evaluate is right when the expression itself is dynamic — you built the formula string at runtime — or when you need a worksheet function that WorksheetFunction does not expose. If the formula is a constant and the function exists on WorksheetFunction, prefer WorksheetFunction; it is clearer and its errors are easier to handle.

How ExcelMaster helps

The traps here are quiet: a bracket form that silently ignores your variable, an Error variant used as if it were a number, an unqualified reference that reads the wrong sheet. None of them announce themselves.

ExcelMaster lets you describe the calculation in plain words — "sum this column, whichever column the report picked, and flag it if the formula fails" — and it writes the Evaluate code that builds the string safely, checks it with IsError, and qualifies the sheet. You keep the workbook and the code, and you skip the silent wrong answer.

Frequently asked questions

What does Evaluate do in VBA?

Evaluate("expression") passes an Excel worksheet formula, written as text, to Excel's calculation engine and returns the computed result as a Variant. Anything valid in a cell works inside it. Use it to run a formula you assembled at runtime, or to reach a worksheet function that WorksheetFunction does not expose.

What do the square brackets mean in VBA?

The [ ] notation is shorthand for Evaluate: [A1] is Evaluate("A1") and [SUM(A1:A10)] is Evaluate("SUM(A1:A10)"). It is shorter for constant expressions, but the text inside is literal — you cannot concatenate a variable into brackets. When any part of the formula is dynamic, use the full Evaluate(aString) form instead.

Why can't I put a variable inside the square brackets?

Because [ ] is not a string — it is fixed text handed straight to Evaluate, so [col & "1"] does not build a reference from col. Build the formula as a string and pass it to Evaluate: Evaluate("SUM(" & col & "1:" & col & "10)"). That is the whole reason the full form exists alongside the shorthand.

How do I check if Evaluate returned an error?

Evaluate returns an Error variant for a bad formula rather than raising a run-time error, so test the result with IsError(result) before using it. This differs from WorksheetFunction, which raises a catchable error instead — choose based on whether you would rather test a value or handle an exception.

Should I use Evaluate or WorksheetFunction?

Use WorksheetFunction when the function exists there and the formula is fixed — it gives IntelliSense and a catchable error. Use Evaluate when the expression is built at runtime, or when you need a worksheet function WorksheetFunction does not expose. For a value that should live and recalculate on the sheet, use a normal cell formula instead of either.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-27.