In Excel VBA, “Constant expression required” is a compile error: VBA found a value that must be known when the code is compiled, but the expression depends on something evaluated later. Replace that expression with a literal or a valid constant, or use a variable and assign its value at runtime; the right fix depends on where the error appears.
What the error means
VBA distinguishes between constants and values that can change while a program runs. A constant is fixed in advance, so the compiler can determine its value before executing the procedure. A workbook cell, a variable, the current date, or the result of a function is not generally available as a compile-time constant. If one of those is placed in a declaration that requires a constant, VBA stops compilation and reports “Constant expression required.”
This is not an Excel worksheet formula error and it does not mean that a cell contains bad data. It is a VBA language rule. The editor may highlight the line containing the problem, though sometimes the actual cause is in a nearby declaration or directive. The code cannot run normally until the compile error is corrected.
Where VBA requires a constant
The most common location is a Const declaration. A constant’s value must be known at compile time, so a reference to a worksheet, a variable, or a function call cannot initialize it. For example, a fixed worksheet row number is suitable as a constant; the value read from a cell is not.
Other declarations and language features can also need constant values. These include fixed-size array bounds, fixed-length strings in a user-defined type, and numeric values assigned to members of an Enum. Conditional-compilation directives such as #If also make decisions while compiling, not while a macro is running. The exact line and surrounding syntax help identify which rule applies.
For example, this constant declaration is invalid because the worksheet property is evaluated at runtime. It is shown to illustrate the error and will not compile:
Sub ReadSetting()
' Invalid: a worksheet value is not a compile-time constant.
Const firstRow As Long = Worksheets("Data").Range("A1").Value
End Sub
By contrast, a literal or a simple expression made from constants is known in advance. For example, Const firstRow As Long = 2 and Const lastColumn As Long = 3 * 4 use values the compiler can resolve.
How VBA evaluates constants
A constant is declared with the Const keyword and is not assigned a new value later. The expression on the right-hand side must be resolvable at compile time. That allows VBA to use the value in places that need a fixed setting, such as a declaration or a conditional-compilation decision. It does not allow VBA to pause, open a workbook, read a cell, or call a function to find out what the value should be.
These examples illustrate the practical distinction:
- Compile-time value: a numeric or string literal, or a simple calculation using fixed values or other constants.
- Runtime value: a variable, a cell’s contents, a property such as
ThisWorkbook.Name, or a function result such asNoworRGB. - Changing value: anything the procedure needs to read or calculate as the macro runs should normally be stored in a variable, not a constant.
Not every expression that looks simple is a valid constant expression. A function call remains a function call even if its arguments are literal values. Likewise, a value does not become a compile-time constant just because a particular workbook happens to contain the same value each time the macro is run.
How to diagnose the highlighted line
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then use Debug > Compile VBAProject to ask VBA to check the project. The editor usually selects the first compile problem it encounters. Fix that issue and compile again; a project can contain more than one error, and later ones may not be reported until earlier ones are cleared.
On a Mac, open the Visual Basic Editor from Excel’s Developer tab or use Option+F11 where that shortcut is available. Keyboard layouts may require the Fn key as well. The VBA editor’s compile command is still under Debug. Excel for the web does not provide the desktop VBA editor, so use the Windows or Mac desktop app to inspect and compile VBA code.
When VBA highlights a line, look for declarations containing Const, parentheses that specify array bounds, a fixed-length string declaration, an Enum, or a #If directive. Also check preceding lines: a missing line continuation, unmatched parenthesis, or malformed declaration can make the editor flag a location that is not the real source of the problem.
Fix a constant declaration that needs a runtime value
If the value comes from a worksheet or can change, replace Const with a variable and assign the value after the procedure starts. With this layout, the worksheet named Data has the desired first-row number in cell A1:
Sub ReadSetting()
Dim firstRow As Long
' Read the setting when the macro runs.
firstRow = CLng(ThisWorkbook.Worksheets("Data").Range("A1").Value)
MsgBox firstRow
End Sub
This code demonstrates the syntax; make sure the cell contains a number that fits in a Long before relying on the conversion. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z. On a Mac, use Command+Z for ordinary Excel undo, but it does not undo VBA macro actions either.
If the value really is fixed, keep Const and replace the runtime expression with an appropriate literal. Constants can also make code easier to maintain when a setting is reused. For example, a module-level declaration such as Private Const HeaderRow As Long = 1 gives a meaningful name to a fixed row number. If that setting must vary by workbook or user, a variable or a worksheet setting is more appropriate.
Fix array bounds and other declarations
A declaration such as Dim results(1 To itemCount) As String can trigger this error when itemCount is a variable. A fixed-size array needs bounds that VBA can establish when compiling. If the number of items is only known when the procedure runs, declare a dynamic array with empty parentheses and size it with ReDim after calculating the count.
For example, if the number of records is placed in itemCount while the macro runs, the structure should look like this:
Sub MakeArray()
Dim itemCount As Long
Dim results() As String
' Set the count at runtime, then size the dynamic array.
itemCount = 10
ReDim results(1 To itemCount)
End Sub
Here the example creates space for 10 strings. In a real procedure, calculate or read itemCount first and account for the possibility that it is zero; a bound such as 1 To 0 is not a useful array size. If the size is genuinely fixed, use a numeric literal or a valid constant as the bound instead.
Similar reasoning applies to fixed-length strings in user-defined types and fixed array fields in those types: their sizes must be fixed in the declaration. If the data length varies, consider a regular variable-length String, a dynamic array, or another data structure instead of trying to calculate a declaration size from a runtime value.
Enum values and conditional compilation
An Enum names a set of numeric values. Its members are established at compile time, so assigning a member a value fetched from a cell or a variable is not appropriate. Use numeric constant values for the members, then use an ordinary variable to select or store a member while the code runs. If an Enum assignment is where the editor stops, inspect each assigned expression rather than assuming all VBA expressions are permitted there.
Conditional-compilation directives have a related but separate purpose. A directive such as #If controls which code is compiled, so its condition must be based on compile-time information, including applicable compiler constants—not on a worksheet value or a variable created by a running procedure. Do not confuse #Const, which defines a compiler constant for conditional compilation, with the ordinary Const keyword used in VBA code. Neither is a way to read a changing cell value during execution.
Common misconceptions and prevention
- “The cell value never changes.” Even if it is stable in your current file, reading it requires execution. Put the result in a variable at runtime, or use a literal constant only if the setting truly belongs in the code.
- “A function with fixed arguments should count.” VBA still has to call the function. A function such as
RGBorNowis not a compile-time literal. - “Adding a type fixes the expression.” Declaring a constant
As Longclarifies its type, but it does not make a worksheet reference or function call constant. - “This is a calculation or reference error in the worksheet.” The message is a VBA compile error. Check VBA declarations and compile the project; changing cell formatting will not fix a constant-expression problem.
To prevent repeat errors, decide whether each setting is permanent or should be read when the macro runs. Name genuine constants clearly, keep changing values in variables, and compile the project after editing declarations. If copying code from another VBA example, check whether it was written for a different language or environment; VBA’s rules for declarations are not identical to those of every other programming language.
Frequently Asked Questions
What does “Constant expression required” mean in VBA?
It means VBA needs a value it can determine at compile time, but the code supplied depends on a runtime value, function, variable, or other unsupported expression. The fix is usually to use a literal or constant, or move the calculation into the running procedure and store it in a variable.
How do I fix “Constant expression required” for a VBA array?
If the array size is known only at runtime, declare it with empty parentheses, such as Dim items() As String, and size it later with ReDim. If the size is fixed, use a literal or a valid constant for the bounds.
Can I use a cell reference in a VBA Const?
No. A cell reference is evaluated while the macro runs, whereas a Const value must be known when VBA compiles the code. Read the cell into a variable inside a procedure instead.
Is “Constant expression required” a runtime error?
No. It is a compile error, so the affected VBA project cannot compile successfully until the expression is corrected. It is different from an error that occurs after a macro has begun running.
Why does VBA highlight a line that looks correct?
The highlighted location is a starting point, not always the whole explanation. Check the declaration and nearby syntax, then compile again after fixing the first reported problem. A preceding malformed line or an earlier compile issue can affect where the editor points.
Related
- Excel UK Number Format: Dates, Currency and Separators
- Excel Transpose: Turn a List Into a Table
- Excel To-Do List Examples and Simple Templates
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- View Macros in Google Sheets: Find and Read the CodeTo view a macro in Google Sheets, open the spreadsheet in a web browser and choose Extensions >…
- Gorilla Copy and Paste VBA: Excel Methods and ExamplesThere is no standard Excel VBA command or feature called Gorilla; if you mean copying and pasting cells…
- Delete an Excel Macro: Safe Steps for Windows and MacTo delete an Excel macro in Microsoft 365 for Windows, open the workbook, choose Developer > Macros, select…
- Excel VBA: Find the Bottom Row of Data in a SheetTo find the bottom row in a particular Excel column, start at the worksheet’s last row and move…
- VBA Array Example: Declare, Fill, and Loop Through DataA VBA array stores several related values under one variable name. For example, declare a three-item array with…
- Excel Macro Arrays: How VBA Arrays WorkAn Excel macro array is a VBA variable that holds multiple values, so a macro can work with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.