How to Effectively Manage Variables, Constants, and Enumerations in VBA for Beginners

Scope

This refers to the part of the code where variables can be used. There are several types of scopes:

• Local Scope: Variables declared in a procedure are only accessible in that procedure.
• Module Scope: Variables declared as Private at the top of the module are accessible to all procedures in the module.
• Global Scope: Variables declared as Public can be accessed from any procedure in a VBA project.

Lifetime

It refers to how long a variable stays in memory during the execution of the program.

• Local variables only exist for the duration of the procedure and will disappear once the procedure is complete.
• The global variable (which is declared as Public) exists for as long as the app is running and remains until the app is closed.

Working with Constants

A constant is a value that is fixed and does not change during the program. Unlike variables whose values can change at any time, constants will always have the same value throughout the program. Constants are very useful for storing fixed and immutable values, such as pi values ($$\pi$$), maximum limits, or fixed messages.

Difference between Variable and Constant

Variable: The value can be changed during the program. Example:

Dim userAge As Integer
userAge = 25 ' Value can be changed
userAge = 30 ' Value can be changed

Constant: The value cannot be changed once it is declared. Example:

Const MAX_USERS As Integer = 100 ' Value cannot be changed

Using constants can improve code readability and reduce the likelihood of errors, as programmers don’t have to worry about accidental value changes.

How to Declare Constants

To declare a constant in VBA, use the keyword Const, followed by the name of the constant, the data type, and the value you want to set. Here is an example of declaring a constant:

Const PI As Double = 3.14159 ' Declares PI constant
Const WELCOME_MESSAGE As String = “Welcome!” ' Declares welcome message constant

Once declared, you can use this constant in your code like a regular variable, but the value cannot be changed:

Built-in and Custom Constants

VBA has many built-in constants that help in code writing. These constants pre-existed and are often used to regulate existing properties, methods, or functions.

1. Constants for MsgBox:

• vbOKOnly: Only displays the “OK” button.
• vbYesNo: Displays “Yes” and “No” buttons.
• vbInformation: Displays the information icon.

Examples of use:

Sub ShowMessage()
     MsgBox “Do you want to continue?”, vbYesNo + vbInformation, “Confirm”
End Sub

2. Constants for Alignment:

• xlCenter: Aligns text in the middle.
• xlLeft: Aligns the text to the left.
• xlRight: Aligns the text to the right.

Examples of use:

Sub SetAlignment()
     Dim ws As Worksheet
     Set ws = ThisWorkbook.Sheets(1)

     Ws. Cells(1, 1). Value = “Welcome”
     Ws. Cells(1, 1). HorizontalAlignment = xlCenter
End Sub

3. Constants for Color:

• vbRed: Red.
• vbGreen: Green.
• vbBlue: Blue.

Examples of use:

Sub ShowColor()
     Dim ws As Worksheet
     Set ws = ThisWorkbook.Sheets(1)

     Ws. Cells(1, 1). Value = “Red Color”
     Ws. Cells(1, 1). Font.Color = vbRed
End Sub