📌 Constants in VBA

Learn how to create fixed values in VBA, understand constant types, scope, naming conventions, built-in constants, and how constants make your VBA programs easier to maintain.

BEGINNER → INTERMEDIATE

📘 What is a Constant in VBA?

A constant is a value that is defined once and is not intended to change while the VBA program is running.

For example, suppose your application uses a fixed tax rate of 18%. Instead of writing 18% repeatedly throughout your code, you can create a constant.

Simple definition:
A constant is a named value whose value remains fixed during program execution.

💻 Basic Constant Example

The basic syntax for declaring a constant is:

Const TAX_RATE As Double = 0.18

Now you can use the constant anywhere within its available scope.

Dim amount As Double Dim tax As Double amount = 1000 tax = amount * TAX_RATE MsgBox tax

The result will be:

Tax = 180

🧩 Constant Syntax

Const constantName As DataType = value

For example:

Const COMPANY_NAME As String = "ChiragCoder" Const MAX_ROWS As Long = 1000 Const TAX_RATE As Double = 0.18 Const COMPANY_ACTIVE As Boolean = True

Part	Meaning
Const	Keyword used to declare a constant.
constantName	Name given to the constant.
As	Specifies the data type.
DataType	String, Long, Double, Boolean etc.
value	Fixed value assigned to the constant.

⚖️ Constant vs Variable

The main difference is whether the value is expected to change.

Feature	Variable	Constant
Declaration	Dim	Const
Value can change?	Yes	No
Example	Dim taxRate As Double	Const TAX_RATE As Double = 0.18
Typical use	Changing data	Fixed values

Variable Example

Dim taxRate As Double taxRate = 0.18 taxRate = 0.20

A variable can be changed.

Constant Example

Const TAX_RATE As Double = 0.18

You cannot intentionally assign a different value later.

Important:
This will cause an error:

TAX_RATE = 0.20

🎯 Why Should You Use Constants?

Constants are especially useful when the same fixed value is used repeatedly throughout a VBA application.

Without a Constant

total = amount * 0.18 tax = price * 0.18 gst = invoiceAmount * 0.18 reportTax = sales * 0.18

If the tax rate changes, you may need to modify many places.

With a Constant

Const GST_RATE As Double = 0.18 total = amount * GST_RATE tax = price * GST_RATE gst = invoiceAmount * GST_RATE reportTax = sales * GST_RATE

Now the rate is maintained in one place.

Maintainability:
If the value changes in the future, you normally update the constant declaration rather than searching through the entire program for every occurrence of the old value.

🏷️ Naming Constants

Good naming makes VBA code much easier to understand.

A common convention is to use uppercase letters with underscores between words.

Const MAX_ROWS As Long = 1000 Const TAX_RATE As Double = 0.18 Const COMPANY_NAME As String = "ChiragCoder" Const REPORT_FOLDER As String = "C:\Reports"

Constant	Purpose
MAX_ROWS	Maximum number of rows.
TAX_RATE	Tax percentage.
COMPANY_NAME	Company name.
REPORT_FOLDER	Report storage location.

Tip:
Choose a name that explains the purpose of the constant. Avoid names such as X, A1 or VALUE1.

🔢 Constant Data Types

Constants can be declared using different data types.

Const MAX_RECORDS As Long = 5000 Const PI_VALUE As Double = 3.14159265359 Const COMPANY_NAME As String = "ChiragCoder" Const ENABLE_LOG As Boolean = True

Type	Example
Long	Const MAX_ROWS As Long = 1000
Double	Const RATE As Double = 0.18
String	Const COMPANY As String = "ChiragCoder"
Boolean	Const DEBUG_MODE As Boolean = True

📍 Procedure-Level Constant

A constant can be declared inside a Sub or Function.

Sub CalculateTax() Const TAX_RATE As Double = 0.18 Dim amount As Double amount = 5000 MsgBox amount * TAX_RATE End Sub

In this example, the constant is available only inside the procedure where it was declared.

Use this when:
The fixed value is required only by one specific procedure.

📂 Module-Level Constants

If several procedures need the same constant, you can declare it at module level.

Option Explicit Const TAX_RATE As Double = 0.18 Sub CalculateTax() Dim amount As Double amount = 5000 MsgBox amount * TAX_RATE End Sub Sub CalculateDiscount() Dim price As Double price = 10000 MsgBox price * TAX_RATE End Sub

Both procedures can use the same constant.

🌐 Public Constants

A constant can also be declared as Public in a standard module when you want it to be accessible from other modules in the VBA project.

Option Explicit Public Const COMPANY_NAME As String = "ChiragCoder" Public Const TAX_RATE As Double = 0.18

Another module can then use:

Sub ShowCompany() MsgBox COMPANY_NAME End Sub

Use Public constants carefully.
They are useful for application-wide settings, but too many global values can make a large application harder to manage.

🔒 Private Constants

You can explicitly declare a constant as Private.

Private Const MAX_RETRY As Long = 3

This limits the constant to the appropriate module scope.

Private constants are useful when the value is only relevant to the internal logic of a particular module.

⚙️ Built-in VBA Constants

VBA and the Office object libraries already provide many constants that you can use in your programs.

For example:

MsgBox "Do you want to continue?", _ vbYesNo + vbQuestion

Here, vbYesNo and vbQuestion are built-in constants provided by VBA.

Common Examples

Constant	Purpose
vbYes	Represents the Yes response.
vbNo	Represents the No response.
vbOK	Represents the OK response.
vbCancel	Represents the Cancel response.
vbQuestion	Displays a question icon in a message box.
vbInformation	Displays an information icon.

🚀 Practical Example – Invoice Calculation

Suppose you are creating an invoice application and the tax rate is fixed at 18%.

Option Explicit Const GST_RATE As Double = 0.18 Sub CalculateInvoice() Dim amount As Double Dim gst As Double Dim total As Double amount = 10000 gst = amount * GST_RATE total = amount + gst MsgBox "Amount: " & amount & vbCrLf & _ "GST: " & gst & vbCrLf & _ "Total: " & total End Sub

The advantage is that the business rule is clearly visible:

GST_RATE = 18%

If your application later needs a different rate, you can change the constant rather than searching through many lines of calculation code.

🏢 Constants in Real-World VBA Projects

Professional VBA applications often contain many fixed values. Constants can make these values easier to identify and maintain.

Option Explicit Public Const COMPANY_NAME As String = "ChiragCoder" Public Const REPORT_FOLDER As String = "C:\Reports" Public Const MAX_ROWS As Long = 50000 Public Const GST_RATE As Double = 0.18 Public Const DATE_FORMAT As String = "dd-mmm-yyyy"

These values can then be used throughout the application.

Range("A1").Value = COMPANY_NAME Range("B1").Value = GST_RATE Range("C1").NumberFormat = DATE_FORMAT

🪄 What is a "Magic Number"?

A magic number is a fixed value placed directly inside code without explaining what the value represents.

Example

total = amount * 0.18

A developer reading the code may not immediately know what 0.18 represents.

Better Approach

Const GST_RATE As Double = 0.18 total = amount * GST_RATE

Better readability:
The code now clearly communicates the purpose of the value.

📊 Constant vs Excel Cell

An important design decision is whether a fixed value should be stored as a VBA constant or in an Excel worksheet.

Use Constant When	Use Excel Cell When
The value is part of the program logic.	The value should be changed by users.
The value rarely changes.	Business users need to update it.
The value is a technical setting.	The value is a business configuration.
You want the value protected inside code.	You want the value visible in the workbook.

Example

A maximum number of retry attempts could be a VBA constant:

Const MAX_RETRY As Long = 3

But a tax rate that business users regularly change may be better stored in a configuration cell.

❌ Common Mistakes with Constants

1. Trying to change a constant after it has been declared.
2. Using unclear names such as X or VALUE1.
3. Creating too many Public constants unnecessarily.
4. Hard-coding the same value repeatedly instead of creating a meaningful constant.
5. Using a constant when the value actually needs to be changed by the end user.

🏆 Best Practices

• Use meaningful constant names.
• Use uppercase naming for important fixed values.
• Specify an appropriate data type.
• Use constants to eliminate magic numbers.
• Use module-level constants when multiple procedures need them.
• Use Public constants only when application-wide access is required.
• Do not use constants for values that users need to change regularly.

📌 Constants – Quick Summary

Question	Answer
What is a constant?	A named value that is not intended to change during program execution.
Keyword?	Const
Can a constant be changed?	No.
Can constants have data types?	Yes.
Can constants be declared inside procedures?	Yes.
Can constants be shared between modules?	Yes, using appropriate module-level scope such as Public.
Why use constants?	Readability, consistency and easier maintenance.

🎯 Key Takeaway

A constant allows you to give a meaningful name to a fixed value and use that value consistently throughout your VBA code.

Instead of scattering numbers and text throughout your application, define important fixed values once and give them descriptive names.

This becomes especially valuable when you start building larger VBA applications such as invoice systems, inventory systems, reporting tools and UserForm-based applications.

🎉 Level 2 Completed!

You have now covered the major fundamentals of VBA variables and data types:

• 📦 Variables
• 🔤 String
• 🔢 Integer & Long
• 📊 Double
• 📅 Date
• ☑️ Boolean
• 🔄 Variant
• 📌 Constants

Next Step:
Now you are ready to move from Variables & Data Types to the next major VBA concept: Operators & Expressions.

← Previous: Variant Next: Operators & Expressions →