In Excel VBA, Expected: expression is a compile error: VBA reached a place where it needs a value or calculation, but found nothing valid to evaluate. Look at the highlighted line for a missing value, operator operand, function argument, or closing parenthesis; the actual mistake can be just before the highlighted spot.
What the error means
An expression is code that evaluates to a value. A number such as 12, a variable such as sales, a comparison such as sales > 0, or a calculation such as price * quantity can all be expressions. VBA needs expressions in assignments, conditions, function arguments, and many other statements.
The compiler reports this message when the sequence of tokens does not make a complete expression where one is required. For example, an equals sign needs something on its right side, and a plus sign needs another value after it. This is a syntax problem, not a result of the code running and producing the wrong answer. VBA must be able to compile the project before it can execute the affected procedure.
The wording is useful, but it does not identify the exact cause by itself. It does not necessarily mean that you forgot to write a function, nor does it tell you which variable or calculation you intended. Treat it as a prompt to inspect the highlighted location and the few tokens immediately before it.
How VBA expressions fit into code
In an assignment, the expression on the right supplies the value placed into the variable on the left. In an If statement, the condition is an expression VBA can evaluate as True or False. A function call can also be an expression when it returns a value. These examples are valid VBA:
Option Explicit
Private Function NetAmount(ByVal price As Double, ByVal discount As Double) As Double
' Return the price after applying the discount.
NetAmount = price * (1 - discount)
End Function
Private Sub CheckAmount()
Dim result As Double
' The function call returns a value for the assignment.
result = NetAmount(100, 0.1)
End Sub
Here, price * (1 - discount) is an expression, as is the call to NetAmount when it is used on the right side of an assignment. The procedure also illustrates an important VBA detail: a Function returns its value by assigning that value to the function’s own name. Declaring a function does not, by itself, guarantee that every later statement using it is syntactically valid.
Common causes and how to recognize them
- A dangling operator: An expression ends with
+,-,*,/, or another operator, but the next value is missing. A line break does not automatically finish a VBA statement in a way that makes an incomplete calculation valid. - An incomplete assignment: A statement contains an equals sign but no usable right-hand expression, as in
count =. Check whether a value or calculation was deleted while editing. - An empty or incomplete argument: A call such as
SomeFunction(, 5)has an empty first argument. Check commas as well as the values around them; a trailing or doubled comma can leave VBA waiting for an expression. - Unbalanced parentheses: A missing closing parenthesis, or a misplaced one, can make the parser interpret the next token in the wrong context. Count opening and closing parentheses in the statement, especially in nested calls.
- A partially written condition: An
Ifcondition such asIf amount > Thenis missing the value to compare with. Conditions need a complete comparison or another valid Boolean expression. - Worksheet syntax copied into VBA: A worksheet formula may use a leading equals sign in a cell, while VBA statements have different syntax and context. Pasting a formula directly into a procedure can leave VBA expecting a valid VBA expression or statement.
One deliberately incomplete example shows the dangling-operator problem. It is meant to illustrate faulty code and will not compile as written:
Option Explicit
Private Sub CalculateTotal()
Dim total As Double
Dim price As Double
price = 12
total = price +
End Sub
The plus sign tells VBA another value must follow. A corrected version gives the expression a second operand:
Option Explicit
Private Sub CalculateTotal()
Dim total As Double
Dim price As Double
Dim quantity As Long
price = 12
quantity = 3
' Multiply price by quantity to calculate the total.
total = price * quantity
End Sub
This correction produces 36 from a price of 12 and a quantity of 3. The particular intended calculation depends on the task; the key repair is to provide a complete expression, not simply to remove the operator that VBA flagged.
Find the real source of the compile error
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then use Debug > Compile VBAProject. The editor stops at a line it cannot compile. On a Mac, open the editor with Option+F11; on some keyboards you may also need Fn. The available editor menus and shortcut behavior can vary by Mac setup, but compilation is performed in the VBA editor, not in Excel for the web.
Do not assume the highlighted character is the original mistake. When VBA encounters a missing operand, it may only become certain that the statement is broken when it reaches the end of the line or the next token. Read the entire statement, then inspect the previous line too. If the procedure has several errors, fix the first reported one and compile again; one syntax repair can reveal or eliminate later messages.
- Read the complete highlighted statement and identify what kind of value it expects at that position.
- Check for a missing right-hand value, function argument, comparison value, or parenthesis.
- Look for accidental extra commas, operators, or line breaks introduced during editing.
- Make one small correction, then run Debug > Compile VBAProject again.
- If the line still looks correct, inspect how the preceding statement ends and whether a continuation was written as intended.
Compilation checks syntax; it does not prove that the calculation is logically correct. Once the project compiles, a procedure might still fail at runtime because a worksheet is missing, a value is outside the expected range, or an object has not been set. Those are different problems and usually produce different error messages.
Functions, calls, and return values
A frequent misconception is that every error mentioning an expression means the Function declaration is wrong. A declaration describes the function’s name, parameters, and return type. For instance, Private Function NetAmount(ByVal price As Double) As Double declares a function that accepts a number and returns a number. The body still has to contain valid statements, and each place that calls the function must pass arguments in a valid form.
In VBA, a function call used to obtain a result can appear in an expression, such as result = NetAmount(100, 0.1). A Sub procedure performs an action rather than returning a value, so it is not interchangeable with a function in an assignment. When editing a call, check that the number and order of arguments match the procedure’s parameters, and that commas and parentheses are balanced. Also verify that a name is spelled correctly; a misspelled name often causes a different compile message, but it can be part of a larger malformed statement.
If you have written a Function and expect it to return a value, assign the result to the function name inside the body. A function can compile without an explicit assignment in every branch, but it may return the default value for its type when no branch assigns a result. That is a logic issue rather than the usual cause of Expected: expression.
Worksheet formulas are not VBA statements
A formula entered into a worksheet cell and code written in the VBA editor use related ideas but different syntax rules. For example, a cell formula may begin with =SUM(B2:B10). In VBA, you would usually call the worksheet function through the Excel object model, or calculate the total with VBA code. The leading equals sign from the cell formula is not a general prefix for a VBA assignment.
For example, the following procedure calls Excel’s worksheet SUM function. It assumes a worksheet named Sheet1 and values in cells B2:B10:
Option Explicit
Private Sub ReadSheetTotal()
Dim ws As Worksheet
Dim total As Double
' Refer to the named worksheet explicitly.
Set ws = ThisWorkbook.Worksheets("Sheet1")
total = Application.WorksheetFunction.Sum(ws.Range("B2:B10"))
End Sub
If the cells contain 2, 3, and 5 with the remaining cells blank, the result assigned to total is 10. This example also makes the worksheet reference explicit, rather than relying on whichever sheet happens to be active. If you intended to enter a formula into a cell rather than calculate a value in VBA, use Excel’s worksheet interface or assign a formula to a Range property using the appropriate VBA syntax.
Safe ways to repair and test code
Make a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. A compile check itself does not run the macro, but testing a corrected procedure can change cells, formatting, files, or other workbook content. Keep a backup until the result is confirmed.
For a calculation, use a small, known example and check the expected result by hand. For code that edits a worksheet, test on a copied sheet or a disposable workbook first. Avoid changing several unrelated lines at once: small edits make it easier to identify which change fixed the syntax and reduce the chance of introducing a new problem.
Excel for the web can open macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Use the desktop Excel application and its VBA editor to work with this error. Excel for Mac supports VBA in its desktop application, although certain Windows-specific features and editor shortcuts may differ; a workbook intended for both platforms should be checked for platform-specific code as well as syntax.
Frequently Asked Questions
Does Expected: expression mean I forgot to declare a variable?
A missing declaration is more commonly reported as a variable-not-defined compile error when Option Explicit is enabled. The expression message points to an incomplete or invalid value in the current syntax, though correcting one issue may expose another.
Why does the editor highlight the line after the mistake?
The parser can sometimes continue past the original omission before it can tell that the statement is impossible to complete. Check the highlighted line and the preceding statement, especially for a trailing operator, comma, or missing parenthesis.
Can an Excel cell formula cause this VBA error?
Not simply by existing in a worksheet. The error appears when VBA code is being compiled. It can occur if worksheet formula text was copied into VBA without adapting it to VBA syntax or to the relevant Excel object model method.
Is this a runtime error?
No. It is a compile error, so VBA cannot compile the affected code as written. A runtime error occurs later, after valid code starts running, and usually depends on the values, workbook state, or operations encountered during execution.
Can I fix or run VBA in Excel for the web?
No. Excel for the web does not provide the VBA editor or run VBA macros. Open the workbook in the desktop version of Excel for Windows or Mac to inspect, compile, and run VBA code.
Related
- Excel Text to Columns: Split at the First Delimiter
- Excel Text to Columns: Use a Line Break Delimiter
- Excel Text to Columns: Split at the Last Space
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Not Working? Find and Fix Common CausesIf an Excel macro is not working, first confirm that the workbook is saved in a macro-enabled format,…
- Excel VBA Month Function: Syntax, Examples, and TipsIn Excel VBA, the Month function returns the month number from a date: an integer from 1 for…
- Excel VBA Loop Syntax: For, Do, and Each ExplainedExcel VBA loops repeat a block of code: use For...Next for a known number of repetitions, For Each...Next…
- VBA Code to Sort Data in Excel: Examples and TipsTo sort Excel data with VBA, define the complete data range, add the column or columns to sort…
- Excel VBA Not Working? Diagnose Common Macro ProblemsIf Excel VBA is not working, first check that you are using the desktop version of Excel, the…
- VBA Range Properties: Values, Formatting, and NavigationIn Excel VBA, a Range property either returns information about cells or another range object, or changes a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.