Declare a constant with Const, a name, a type and its value on one line: Const TAX_RATE As Double = 0.18. Use the name in place of the number, as in Range("C2").Value = Range("B2").Value * TAX_RATE. Any line that tries to assign it a new value gives a compile error.
Where you declare it sets its scope. Inside a procedure, it works only there. At the top of a module, above every procedure, it covers that module, and Public Const at the top of a standard module makes it available to every module in the workbook. Private and Public can’t be used inside a procedure.
Write dates between hash signs in month/day/year order, whatever your regional settings: Const START_DATE As Date = #4/1/2026# is 1 April 2026. Text goes in straight double quotes: Const REPORT_NAME As String = "Monthly Sales".
The value must be fixed when you write the code. A function result such as Date, Range("A1").Value or CDec(...) gives “Constant expression required”, so use a variable for those. Other constants and arithmetic are allowed: Const MAX_ROWS As Long = 1000 * 2.
What is a Constant in VBA?
In VBA, a constant is like a storage box with a name that stores a value for you, but the value which you assign to it cannot be changed during the execution of the code. In VBA, there are two different kinds of constants that you can use:
- Intrinsic Constants: Intrinsic constants are those which are built into VBA and Excel (for example, the built-in constant vbOKCancel, which you use in the message box).
- User-Defined Constants: These are the constants which you create yourself by declaring them and assigning a value to them.
Intrinsic constants are ready to use, and you don’t need to declare them. Some of the common ones are vbYes and vbNo (message box buttons), vbNewLine (a new line in text), and xlUp and xlDown (directions in Excel). To see all of them, open the Object Browser in the Visual Basic Editor (F2) and search for the constant’s name.
Declare a Constant in VBA
Use the following steps:
- First of all, use the keyword “Const”.
- Then, specify a name for the constant.
- After that, use the keyword “As” after the name.
- From the “Data Type” list specify the data type for the constant according to the value you want to assign to it.
- Now, enter the equals sign “=”.
- In the end, assign the value you want to store to it.

Above is a constant that stores a birth date. Now, if you think about it, a birth date is something that is supposed to be fixed, and for this kind of value, you can use a constant.
Best Practices for Naming Constants in VBA
You can name a constant the way you want, but there are a few tips and rules which you can consider while naming them.
- Use a descriptive name which shows the purpose of the constant. This is also useful when you are sharing your code with someone else, so that they can understand it easily.
- There is a list of reserved words in VBA which you can’t use as a name for the constant you are declaring (like Sub, Dim, If or Date). (list)
- You also need to avoid using the special characters in the name. You can use the alphanumeric characters and underscores.
- The name of a constant can’t start with a number. So, you need to use a letter as the first character of the name, and the name can be up to 255 characters long. This is a predefined rule.
- If you have a constant for which you want to use a long name with multiple words, you can use an underscore in the name to make it easier to read.
- Some programmers also use a capital letter for each word, if they have multiple words in the name.
- Along with the underscore, you can also follow the practice of using capital letters for constants and small letters for variables to create a separate identity of the constants.
Major Data Types of Constants in VBA
There are four major data types of the constants in VBA.
1. String Constant
In a string constant, you can store a string (the text has to fit in the line where you declare the constant), like a name or a message which you don’t want to change throughout the code.
Const myStringConstant As String = "Finished without any error."
In the above example, you can see we have a constant with a message. I can show this message to tell the user that the code has been completed when a macro finishes the run.
2. Numeric Constants
In the numeric constants, you can store a number which you don’t need to change in the code. There are multiple data types which you can use to store the number:
- Integer: To store a whole number from -32,768 to 32,767.
- Long: For a whole number ranging from -2,147,483,648 to 2,147,483,647.
- Single: A positive or negative number with decimals, accurate to about 7 significant digits.
- Double: A positive or negative number with decimals, accurate to about 15 significant digits.
- Currency: A numeric type to hold currency values, positive or negative, with up to 4 decimals.
- Decimal: To hold a positive or negative number with up to 28 decimals. But VBA doesn’t support Decimal as a declared type for now, so you can’t declare a constant As Decimal (see the last line of the code below).
Const cInteger As Integer = 10 Const cLong As Long = 30000 Const cSingle As Single = 0.1234567 Const cDouble As Double = 0.123456789012345 Const cCurrency As Currency = 1234.5678 ' Const cDecimal As Decimal = ... is not supported in VBA (compile error)
3. Boolean Constant
When you are working with conditions, Boolean constants are helpful. In a Boolean constant, you can save one of two values, TRUE and FALSE. You can’t save the result of a condition that is checked while the code runs (that needs a Boolean variable), but you can use a Boolean constant as a fixed switch. Let’s understand it in this way:
Const CBoolean As Boolean = True
When you declare this constant and assign the value TRUE to it, you can use it anywhere in the code. In VBA, True is not just a word but a state. You can use it to start something or to stop something.
The best part is that when you want to change TRUE into FALSE, you can do it in the line where you have declared the constant, and the values of the constant will change in the entire code. You don’t need to go to each line of the code and change it there individually.
4. Date/Time Constant
In VBA, a date constant can store a fixed date and time to use in the code. The following is an example of declaring a date constant and assigning a date to it.
Sub one() Const myBirthDay As Date = #11/10/1990# MsgBox myBirthDay End Sub
Inside the hash marks, VBA always reads the date as month/day/year, whatever date format your system uses. So, #11/10/1990# is 10 November 1990, and the message box shows it in your system’s date format.
In the above code, we declared the constant myBirthDay and then assigned a date. And when you use the constant myBirthDay anywhere in the code, it returns the date we have assigned.
And if you want to add time along with the date, you can do it like the following.
Sub one() Const myBirthDay As Date = #11/10/1990 03:30:00 AM# MsgBox myBirthDay End Sub
Note – Make sure to enclose the date, or the date and time both, in hash marks (#).
Examples to Use Constants in VBA: Constant in a Loop
In VBA, you can use a constant in a loop code where you need to define a fixed value that you don’t want to change with each loop iteration. In the example below, you can see that in the For Next loop, we have used a constant to specify the count for the loop.
Sub SerialNumbers() Const iCount As Integer = 10 Dim i As Integer For i = 1 To iCount ActiveCell.Value = i ActiveCell.Offset(1, 0).Activate Next i End Sub
In the above example, we have used the iCount constant to store the counter for the loop. When you run this VBA code, it goes ten cells down and adds serial numbers.
Errors that Occur While Using Constants in VBA
Assigning Value
When you declare a constant, you need to assign a value to it in the same line (as you have learned earlier). But when you skip this, VBA shows you an error (Compile Error) to remind you that an equal sign is expected.

Re-Assigning Value
After assigning a value to the constant initially, it’s not possible to re-assign a value to it. And when you try to do that in the code, it shows you a compile error saying, “Assignment to constant not permitted.”

Overwriting a Constant
If you declare a constant at the top of the module and then declare another constant with the same name inside a procedure, the procedure uses the second one. It looks like you changed the value, but you didn’t: the first constant still has its value, and VBA simply uses the local one inside that procedure.

Public Const iCount As Integer = 1 Sub SerialNumbers() Const iCount As Integer = 4 MsgBox iCount End Sub
If you run the above code, the message box will show the value 4 instead of 1. In any other procedure in the project, iCount is still 1. So, avoid using the same name twice, as it makes the code confusing.
Scope of a Constant
A constant has the same scope as variables. When you declare a constant inside a procedure, it has a procedure-level scope, which means you can use it only within that procedure. But you can also declare a constant at the top of a module, before any procedure, with a private or public scope.
A private constant is available to all the procedures in the module where it is declared. To declare a private constant, you need to use the keyword “Private”, just like the following example (a constant at the top of a module without any keyword is private too).
Private Const iName As String = "Puneet"
And in the same way, you need to use the keyword “Public” when you need to declare a constant as public, just like a global variable in VBA.
Public Const iPrice As String = "$3.99"
A public constant is available to all the procedures in all the modules. Declare it in a standard module, as VBA doesn’t allow public constants in a sheet, ThisWorkbook, UserForm or class module.
Advantages of Using Constants over Variables in VBA
Yes, there are a few advantages of using constants in VBA code, instead of variables. Like:
- Improve Readability: When you use a constant which has a fixed value, it helps you and even others to read and understand the code easily. Unlike variables, in a constant, the value does not change again and again, which makes it easier to understand.
- Fewer Errors: With a constant, you have a constant value throughout the code, and even in loops, so you will always be sure that the value used is exactly the same.
- Easy to Maintain: Changing the value of a constant is easier than a variable. You just need to change it at the place where you have declared it instead of changing it at every point where the value is used.
Use Other Constants and Operators in a Constant
The value of a constant can be a literal value, another constant, or a calculation with operators. You can also declare more than one constant in one line by separating them with commas.
Const TAX_RATE As Double = 0.18
Const PRICE As Double = 250
Const PRICE_WITH_TAX As Double = PRICE * (1 + TAX_RATE)
Const FIRST_ROW As Long = 2, LAST_ROW As Long = 100
But you can’t use a variable or a function (not even a built-in one like Chr or Date) in the value of a constant. If you try, VBA shows the compile error “Constant expression required”. For a value that comes from a function, use a variable instead.
If you skip the data type (As Double), VBA gives the constant the type that fits its value best. It still works, but declaring the type makes your code clearer.
Group Related Constants with an Enum
When you have a set of related whole-number constants, like column numbers, you can group them in an Enum at the top of a module. Then you use them with the Enum name, and VBA even shows the list as you type.
Enum ColumnNo
colName = 1
colCity = 2
colSales = 3
End Enum
Sub ShowSales()
MsgBox Cells(2, ColumnNo.colSales).Value
End Sub
Frequently Asked Questions
What is the difference between a constant and a variable in VBA?
A variable’s value can change while the code runs, and a constant’s value is fixed when you declare it. You declare a variable with Dim and assign the value later, and you declare a constant with Const and assign the value in the same line.
Why do I get “Constant expression required”?
The value you assigned to the constant uses a variable or a function. Use only literal values, other constants and operators, or change the constant to a variable.
Can I use a constant as the size of an array?
Yes. A constant works where VBA needs a fixed number, like the size of a fixed array: Dim names(1 To MAX_ITEMS) As String. A variable doesn’t work there; for that you need a dynamic array with ReDim.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.