VBA Constants in Excel

VBA Constants

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.

  1. Constant Name must be less than 256 characters length
  2. Constant first letter in name must be a character
  3. VBA keywords or reserved words cannot use as a constant Name
  4. We can’t use reserved words as a constant name.
  5. Constant name doesn’t allow period(.)
  6. We cannot use special characters like @,#,$.%,&,!, etc. when declaring a constant name.
  7. 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”

  1. 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
    End Sub
    2)Private Sub CheckBox2_Click()
    [1:1].EntireRow.Hidden = CheckBox2
    [2:2].EntireRow.Hidden = Not CheckBox2
    End Sub
    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()
    Range(“D21”).ClearContents
    Range(“D23”).ClearContents
    Range(“D26:D28”).ClearContents
    End Sub

    Really appreciate your help and thanking you in advance

Leave a Comment