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.
Syntax of VBA Constant in Excel
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.
Rules to Naming a Constants in VBA
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.
Declare Constant Values
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.
Module Level Constants:
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
Project Level Constants:
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
Example on Constant variable in Excel VBA
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
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog
1 thought on “VBA Constants in Excel”
Hi, I was a new beginner of VBA codes. I wanted to perform the following:
1) Private Sub CheckBox1_Click()
[K:N].EntireColumn.Hidden = Not CheckBox1
[G:J].EntireColumn.Hidden = CheckBox1
2)Private Sub CheckBox2_Click()
[1:1].EntireRow.Hidden = CheckBox2
[2:2].EntireRow.Hidden = Not CheckBox2
Try the above and its work very well.
But in the same time, I wanted to “Run Sub/User Form F5” to clear some data in selected cells below, if necessary.
3) Sub sbClearCellsOnlyData()
Really appreciate your help and thanking you in advance