VBA Constant

This tutorial will demonstrate the use of VBA Constants.

A constant is similar to a variable and is declared in a similar way.  There is, however, a major difference between them!

What is a Constant

A constant is a value that we declare in our code and consequently it is reserved in our computer’s memory and stored. We have to name our constant and it’s good practice to declare the data type of our constant. When we declare the data type, we are telling the program what type of data needs to be stored by our constant .

We will use the constant in our code, and the program will also access our constant. Unlike a variable, where the actual value can change while the code is running, a constant value never changes.

Data Types used by Constants

Constants use the same data type as Variables.   The most common data types for Constants are as follows:

• String – this is used to store text values.
• Boolean – this is used to store TRUE or FALSE values.
• Integer – this is used to store whole number values.
• Double – this is used to store numbers with decimals.
• Date – this is used to store dates.

To see a complete list of all data types used by Variables and Constants in VBA, click here.

In VBA, we have to use a Const statement in order to declare a Constant.  We can declare constants in a similar way to declaring Variables – at Procedure Level, at Module Level and at a Global Level.

Declaring a Constant within a Procedure

To declare a Constant at Procedure level, we declare it inside the procedure.

Sub CompanyDetails()
   Const strCompany As String = "ABC Suppliers"
   Const strAddress As String = "213 Oak Lane, Highgate"
   MsgBox strCompany & vbCrLf & strAddress
End Sub

When we run the code, the message box will return the constant values.

Because the Constant is declared at Procedure level, we  can declare a Constant with the same name in a different Procedure.

If we run the second Procedure, the Constant value stored in that Procedure is returned.

Declaring a Constant within a Module

If we want a Constant value to be available to all Procedures within a Module, we need to declare the constant at Module level.

This will make the same constant available to multiple procedures WITHIN that module only.

Should you use the Constant in a different module, an error will occur.

Declaring Constants at a Global Level

You can declare Constants at a Global Level which would then mean you can use them in all the Modules contained in your entire VBA Project.

To declare a Constant as a Global Constant, we need to put the word PUBLIC in front of the declaration statement.

For example:

Public Const strCompany as string = "ABC Suppliers"

This will allow the Constant to be used in all the modules regardless of where is is declared.

NOTE: you can ONLY declare a public constant at a Module level, you CANNOT declare a public constant within a procedure.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!