An Excel VBA syntax error means the Visual Basic for Applications editor cannot parse part of your code as valid VBA. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, use Debug > Compile VBAProject to check the project, and correct the highlighted statement—often a missing quote, parenthesis, or required VBA keyword.
What a VBA syntax error means
VBA code has to follow rules for how statements, keywords, names, and punctuation are written. When VBA encounters a statement it cannot interpret, it reports a compile-time error before that code can run. The message may say “Syntax error,” or it may be more specific, such as “Expected: End If” or “Expected: expression.” The wording is a clue, but the nearby code usually provides the real explanation.
The Visual Basic Editor (VBE) commonly highlights the problem when you try to run a macro or compile the project. The highlighted line is not always where the mistake began. For example, a missing closing quotation mark on one line can make VBA treat the next line as part of the unfinished string. If a line looks correct, inspect the statement immediately above it and any block that has not been closed.
A syntax error is different from a runtime error. A macro with valid syntax can still fail while running—for example, if it refers to a worksheet that does not exist. A logic error is different again: the macro runs, but produces the wrong result. Identifying which kind of problem you have helps you choose the right debugging step.
How to find the line that needs attention
On Windows, open the workbook in desktop Excel and press Alt+F11 to open the VBE. If the Developer tab is visible, you can also select Developer > Visual Basic. In the VBE, choose Debug > Compile VBAProject. If the project contains an error, the editor stops and points to code that needs correction. Compile after each fix; a single correction can reveal a second, separate error.
If the Developer tab is missing, enable it in Excel through File > Options > Customize Ribbon, then select the Developer check box. On a Mac, open the editor from Tools > Macro > Visual Basic Editor or Developer > Visual Basic if that tab is available. Option+F11 is the common Mac shortcut; some keyboards require Fn as well. Excel for the web does not run or edit VBA macros, so use the desktop app for these steps.
- Read the full error message before dismissing it; “Expected” messages often name a missing keyword or expression.
- Check the highlighted line, the line just above it, and any open If, For, or With block nearby.
- Fix one issue at a time, then run Debug > Compile VBAProject again.
- If the editor highlights an unfamiliar statement, compare it with a small, known-good example or check the VBA language reference.
Compilation checks the project’s code for issues that prevent it from compiling; it does not prove that a macro will work correctly with every workbook or data set. After compilation succeeds, test the macro on a copy of the workbook and inspect the results.
Common causes and their corrections
Most beginner syntax errors come from small omissions or from using a structure in the wrong form. Check for these frequent causes:
- Unclosed text: Text values need matching straight double quotation marks. A curly typographic quote copied from a document is not the same character as the normal VBA quote.
- Missing parentheses: Function calls and grouped expressions need balanced opening and closing parentheses.
- Incomplete blocks: An If block needs End If; a For loop needs Next; and a procedure needs End Sub or End Function.
- Misspelled keywords or names: A misspelled VBA keyword can cause a syntax error. A misspelled worksheet or variable name may instead cause another compile error or a runtime error, depending on the code.
- Incorrect line continuation: To continue a statement on the next line, put a space followed by an underscore at the end of the first line.
- Invalid assignment or argument format: Make sure each statement follows the syntax expected for that operation and that arguments are separated correctly.
For example, this procedure has a complete If block and matching quotation marks:
Sub CheckValue()
Dim amount As Double ' Store the value being checked
amount = 25
If amount > 0 Then
MsgBox "The value is positive"
End If
End Sub
If End If is omitted, VBA cannot determine where the conditional block ends. If the text in MsgBox is missing its closing quote, the editor may point to that line or to a later one. When repairing a block, check both its opening statement and its required closing statement.
Examples of small VBA syntax mistakes
Comparing an incorrect statement with a corrected one can make the rule easier to see. The snippets below are standalone procedures; the comments identify the point being illustrated. In the first example, the assignment operator is required to store a value in a variable:
Sub SetCount()
Dim itemCount As Long ' Declare a whole-number variable
itemCount = 5 ' Assign the value
MsgBox itemCount
End Sub
In the next example, the condition has a comparison operator and the If block is closed. A common mistake is to leave out Then or End If:
Sub CheckCount()
Dim itemCount As Long ' Declare a whole-number variable
itemCount = 5
If itemCount > 0 Then
MsgBox "There are items"
End If
End Sub
String literals also need careful quoting. To include quotation marks inside a VBA string, double the quotation mark characters inside the text:
Sub ShowQuotedText()
Dim messageText As String ' Store the message
messageText = "She said ""Done"""
MsgBox messageText
End Sub
For a longer statement, a space and underscore mark a continued line. The underscore must be the final character on that line; do not put a comment after it:
Sub ShowLongMessage()
Dim messageText As String ' Store a message across two lines
messageText = "This is a longer message " & _
"continued on the next line."
MsgBox messageText
End Sub
Syntax errors versus other VBA errors
Not every message in the editor means the syntax is wrong. A compile error is a broader category: VBA cannot compile the project, but the specific reason may be something other than punctuation or statement structure. For instance, adding Option Explicit at the top of a module requires variables to be declared; an undeclared variable then produces a “Variable not defined” compile error. Declaring variables clearly can prevent misspellings from going unnoticed.
A runtime error appears after a macro starts. A valid statement that tries to use a worksheet name that is absent from the workbook may trigger an error during execution. A logic error may produce no message at all: the macro completes but changes the wrong cells. To investigate those cases, step through code with the VBE’s debugging tools, inspect variable values, and confirm that the workbook layout matches what the macro expects.
There are also issues that are not VBA code errors. Macro security settings may block a macro from running, and Excel for the web does not execute VBA. Those situations call for using an appropriate desktop version and checking the workbook’s security context—not rewriting valid code as if it had a syntax mistake.
A practical repair and testing workflow
When the cause is not obvious, work systematically rather than changing several lines at once. First save a separate copy of the workbook. Then follow these steps:
- Open the relevant module in the VBE and read the complete error message.
- Inspect the highlighted statement and nearby lines for unmatched quotes, parentheses, or block endings.
- Check spelling and punctuation, including straight quotes and any line-continuation underscore.
- Use Debug > Compile VBAProject again, and repeat until the project compiles without an error.
- Run the macro on the copy and verify its results on a small, representative set of data.
Saving a backup matters because macros can change or delete workbook content. Macro actions cannot be undone with Ctrl+Z, so do not treat the Undo command as a recovery plan. Keep an untouched copy until you have confirmed that the macro behaves as intended.
How to prevent syntax errors
Readable code is easier to check than a long procedure with tightly packed statements. Use one statement per line, indent the contents of If and loop blocks, and choose descriptive variable names. Put Option Explicit at the top of each module so that undeclared variables are caught during compilation rather than silently becoming new variables. Declare each variable with an appropriate type, such as Long for whole numbers or String for text.
When adding a new feature, make a small change and compile before moving on. If code is copied from a web page or formatted document, replace typographic quotes with straight quotation marks and check for characters that were changed during copying. For a difficult error, temporarily reduce the procedure to a small example, then add the original statements back in stages. This makes it easier to identify which change introduced the problem.
Also check the kind of module and procedure you are editing. A procedure must be placed in an appropriate code module, and event procedures have naming and placement requirements. If a macro is intended to respond to a worksheet event, putting code in a standard module rather than that worksheet’s code module may prevent the event from working, even though the code itself can compile.
Frequently Asked Questions
Why does VBA highlight the line after the actual mistake?
A missing quote, parenthesis, or block ending can leave VBA unable to interpret what follows. The editor may only recognize that the code is invalid when it reaches a later line. Check the highlighted line and the statements immediately before it.
How do I compile VBA code in Excel?
In the desktop VBE, choose Debug > Compile VBAProject. On Windows, open the editor with Alt+F11 or Developer > Visual Basic. Compilation can locate code problems, but you should still test the macro because a successful compile does not guarantee correct results.
Is a syntax error the same as a compile error?
Not exactly. A syntax error is one reason code may fail to compile. Other compile errors include an undeclared variable when Option Explicit is enabled. Runtime errors occur while valid code is running, and logic errors are incorrect results without necessarily producing an error message.
Can Excel for the web fix or run a VBA macro?
No. Excel for the web does not run or edit VBA macros. Open the workbook in the desktop Excel application to work in the Visual Basic Editor and run VBA code.
Can I undo a macro after it runs?
Do not rely on Ctrl+Z to reverse a macro; VBA actions cannot be undone that way. Save a separate workbook copy before running unfamiliar or data-changing code, and test on a copy first.
Related
- Excel Shortcut to Select Cells to the End of a Column
- Excel Shortcut to Select the Active Cell’s Entire Column
- Excel Shortcut to Select to the Bottom of a Column
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- TMG Macro Recorder? Excel’s Built-In Option Explained“TMG macro recorder” is not the name of a standard feature in Microsoft Excel. If you mean recording…
- VBA Error Handling Tutorial: Errors, Handlers, and CleanupVBA error handling lets a macro respond to a runtime problem instead of stopping unexpectedly. Start with On…
- VBA Delete Whole Row: Safe Methods and ExamplesTo delete an entire worksheet row with VBA, use Rows(5).EntireRow.Delete, replacing 5 with the row number you want…
- Excel VBA Arrays vs Collections vs DictionariesIn Excel VBA, use an array for a compact, indexed set of values, a Collection for an ordered…
- VBA Global Dictionary: Scope, Setup, and Code ExamplesVBA has no built-in global dictionary type, but in Excel for Microsoft 365 on Windows you can create…
- Excel Number to Words: Formula, VBA, and How to Use ItExcel has no built-in English worksheet formula that converts an ordinary number into words. In Microsoft 365 for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.