VBA Constants in Excel has a meaningful name and assigned or set a value to it. Once constant value is declared, we can’t changed or assigned to a new value throughout the script execution. We use Const keyword to declare a constant value. We can declare a constant variable any where in the module. It can be declared at Module level or procedure level or class module. We can also use private or public while declaring constant variable.
Here is the syntax of VBA Constant syntax in Excel.
Const constant_name As constant_type = constant_value
Where Constant_Name: It represents a constant name which has meaningful.
Constant_Type: It represents type of data.
Constant_Value: It represents a constant value. Once value declared, the value can’t be changed or assigned throughout execution.
We must follow the following rules while declaring a constant variable.
- Constant Name must be less than 256 characters length
- Constant first letter in name must be a character
- VBA keywords or reserved words cannot use as a constant Name
- We can’t use reserved words as a constant name.
- Constant name doesn’t allow period(.)
- We cannot use special characters like @,#,$.%,&,!, etc. when declaring a constant name.
- We can declare multiple constants in one statement.
We can declare constants at module level or project level. We can also use Private and Public keywords along with constants. Constants can accept any type of data.
Let us see how we can declare a constant at module level. In the below example the constant iCnt can be accessed from any procedure in the current module. We can’t access procedures which are in other modules. Module level constants are private by default.
Const iCnt As Integer = 222 'or Private Const iCnt As Integer = 222
Let us see how we can declare a constant at project level.In the below example the constant iCnt can be accessed from any procedure in any module in the project. Project level constants are public by default.
Public Const iCnt As Integer = 222
Let us see the example on constant variables in VBA. Constants can accept any type of data.
Sub VBAF1_Constant_Declaration() 'Constant Variable Declaration Const iCnt As Integer = 444 Const dDate As Date = #1/1/2019# Const sMonth As String = "March" Const cSalary As Currency = "40000" End Sub
You can refer the following link for the step by step instructions.
Click on the following links of the useful resources. These helps to learn and gain more knowledge.