What is a Constant in VBA?
In VBA, a constant is a storage box that is itself stored in your system and it can store a value in it 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 the VBA language itself (for example, the built-in constant vbOKCancel which you use in the message box),
- User-Defined Constants – These are user-defined constants which you can create by assigning a value to it.
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 to “=” sign
- In the end, assign the value you want to store to it.
Above is a constant that stores a birthdate. Now if you think, 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 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 can declaring. (list)
- You also need to avoid using the special characters in the name. You can use the alphanumeric characters and underscores.
- Name of a constant can’t be start with a number. So, you need to use alphabet in the first letter of the name. This is a pre-define rule.
- If you have constant for which you want to use a long name with multiple words, you can use underscore in the name to make it easier to read.
- Some programmers also use capital letters for the each word, if they have multiple words in the name.
- Along with the underscore, you can also use a practice to use 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 (up to approximately 65,400 in length), like, a name, message which you don’t want to change throughout of 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 run this message, to tell the user that code has been completed, when a macro finishes the run.
2. Numeric Constants
In the numeric constants, you can store number which you don’t need to change in the code. There multiple data types which you can use to store the number:
- Integer – To store a number between -32,768 to 32,767.
- Long – For a number ranging between -2,147,483,648 to 2,147,483,647.
- Single – A number with up to 7 decimals in positive or negative.
- Double – A number with up to 15 decimals in positive or negative.
- Currency – A numeric type to hold the currency in positive and negative up to 4 decimals.
- Decimal – To hold a positive or negative number up to the 28 decimals.
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 = 0.12345678901234567890123456783. 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 save the result from a condition in Boolean constant. Or let’s understand it in this way:
Dim CBoolean As Boolean = TrueWhen 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 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
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 enclosed date or date and time both in the 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 below example, you can see that in the For Next loop we have use 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 loops to ten cells downwards and add serial numbers.
Errors Occur While using Constants in VBA
Assigning Value
When you declare a variable, you need to assign a value to it in the same line (as you have learned earlier). But when you skip to do this, VBA shows you an error (Compile Error) to remind you to 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 Contant
If you declare a constant and assign a value to it, and then you declare a constant with the same name, you can still change its value. Yes, that true, and against what we have learned earlier.
Public Const iCount As Integer = 1
Sub SerialNumbers()
Const iCount As Integer = 4
MsgBox iCount
End SubIf you run the above code, the message box will show the value 4 instead of 1.
Scope of a Constant
Constant has the same scope as variables. When you declare a constant, it has a procedure-level scope, which means you can use it anywhere within the procedure. But you can declare a constant using a private or public scope.
A private constant is available only to the procedure where it is declared as a constant. To declare a private constant, you need to use the keywords “Private”, just like the following example.
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”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 other 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.
- Less Errors – With a constant, you have a constant value through out the code, and even in loops, so you will always be sure that the value used is exactly same.
- Easy to Maintain – Changing 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 every point where value is used.