This tutorial explains how to declare & use VBA variables. Also learn about option explicit, variable scope, static and constant variables:

In a computer, all the data values are stored in a memory or computer storage. To access these values, you need to specify a name associated with that value, which is called a Variable in programming languages.

In this tutorial, we will learn how to declare and define a variable along with the various types of variable scopes and also explore Static and Constant variables in detail.

=> Check Here To See A-Z Of VBA Training Tutorials

VBA Variables

Variables are like placeholders for the values in computer storage. Variables can hold different varieties of values and those values can change during the execution of the code.

Every variable will have a name that is used to access the value. VBA has certain constraints with respect to name selection.

Enlisted below is the list of constraints:

• The name of your variable cannot be more than 255 characters.
• A variable name cannot start with a number.
• You cannot start with special characters such as! @, &, ., # or cannot have spaces.
• Keywords are not considered variable names.

The compiler will throw an error if you violate these constraints

Example:

Valid Variable Names: varName, Result12, First_Number

Invalid Variable Names: 123abc, #number, Sub, abc.123
First Number (Space is not allowed between words)

Declaring A VBA Variable

We need to use the Dim keyword to declare a variable. There are 2 sections while declaring a variable. 1^st is the variable name and 2^nd is the type of value to be stored in the variable which is called Data type.

Syntax: Dim VariableName As DataType

Example:

1. Dim MyAge As Integer
2. Dim My_Percentage As Double
3. Dim Full_Name As String

Here you are explicitly telling the compiler that my variable should only store these types of data and VBA will make sure to throw a compiler Type mismatch error when a value defined for the variable doesn’t match the data type.

Recommended reading =>> How to handle errors in VBA

Example:

Sub example1()
Dim Total_Marks As Integer
Total_Marks = “$23”
MsgBox Total_Marks
End Sub

But if you are unsure of the data type of the variable, then VBA allows you to skip the datatype. The below-mentioned syntax is also valid.

Dim VariableName

When you don’t declare the data type of the variable, then VBA treats it as a Variant data type, and it is bound to accept any type of data integer like an integer, string, workbook, etc. In the same program, the variant variables can accept string values, integer values, and any other type as well.

Sub VarientVariable()
Dim Total_Marks
Total_Marks = "$23"
MsgBox Total_Marks
Total_Marks = 544.33
MsgBox Total_Marks
Total_Marks = 522
MsgBox Total_Marks
End Sub

As the Total_Marks is a variant variable, unlike the previous code, the compiler won’t throw any error as shown below.

Note: Remember that VBA allows you to use a variable without actually declaring it.

The below example code also gives a proper result. In this case, VBA automatically treats the variable as a Variant data type. This is called an Implicit Declaration.

Sub VariantVariable()
Total_Marks = 444
MsgBox Total_Marks
End Sub

Advantages Of Declaring A Variable

1. Easy to Debug: It is a good practice to declare a variable before using it. It gives us an earlier understanding of the errors like type mismatch and typo errors, thereby reducing the risk of deviating from the logic of the program.
2. Better Readability: As a team, there will be many people looking at the code, and declaring a variable will help everyone with the type of values the variable is expected to take and also help the users to enter appropriate input values while executing the program.
3. Storage Space: If you don’t declare a variable, then VBA treats it as a Variant data type that takes the largest space in memory (16 bytes to 22 bytes) when compared to other data types. For example, if you are using a variable of type Byte that just takes 1 byte and if you don’t declare then VBA will end up reserving a space of 16 bytes, thereby wasting the memory.

Option Explicit

To ensure that all the variables are explicitly declared, we have to use the Option Explicit statement before using them. It should be used at the beginning of any procedure in that module.

Let’s see an example that does not use the Option Explicit Statement.

Sub example3()
Dim First_name As String
First_name = " abs"
MsgBox Firt_Name
End Sub

Execute the code (Press F5 or run button in the toolbar) and you will end up getting a blank msg box.

Even though you have declared the variable, you ended up in a blank result box because of the typo in the Msgbox Firt_Name. Option Explicit will help us in eliminating such errors.

Let’s redo the same code using Option Explicit. Enter Option Explicit before the procedure starts.

Option Explicit
Sub VarientVariable()
Dim First_name As String
First_name = " abs"
MsgBox Firt_Name
End Sub

Now if you execute the code (Press F5 or Run button in the toolbar) and you will end up getting a compiler error.

You can make VBA add Option Explicit automatically.

In the VB Editor, Go to Tools -> Options -> Select “Require Variable Declaration”.

Now Option Explicit will be added automatically every time when you insert a new module or record a new macro.

Note: Option Explicit will not be added to the existing VBA code. You need to add it manually if required.

Assigning A Value To a Variable

Values can be assigned to variables using equal to symbol (=). If you referring to a cell in Excel, then you need to use the Range functions.

Refer to the code below.

Sub varValue()
Dim var1 As Integer
var1 = 10
Range("C1:C14").Value = var1
MsgBox var1
End Sub

In the above example, var1 is a variable that stores the value 10 directly by using (=) and the Range function will help to enter the var1 value which is 10 in Excel cells C1 to C14.

Scope Of A Variable

Every variable in a program will have a defined scope. This scope tells the program the areas in which the variable is visible i.e. the module or function that can or cannot use the variable.

VBA has 3 types of Scope defined for the variable.

1. Procedure-level scope
2. Private module-level scope
3. Public module-level scope

Procedure-level Scope

The Variables defined in a procedure i.e. in the Sub or Function will only be able to use them. These are not visible to any other procedure.

Example:

Option Explicit

Sub ProcScope()
Dim msg As String
vartext = "Varible is visible only inside this Sub"
MsgBox vartext
End Sub

Sub VarNotVisible()
MsgBox vartext
End Sub

When you execute the above code, for the first Sub the msgbox will return a proper result, but for the second Sub, a Compiler error will be thrown. Because the vertex variable is visible only for the 1^st sub and not for the 2^nd. As we have used Option Explicit, we will get a compiler error for not defining variables in the 2^nd sub.

Private Module-Level Scope

These variables are available to all the procedures in the defined module. By default, the variables declared with the Dim are scoped as private. But it is recommended to add private for better readability.

Let’s consider the same example but we will declare the variable outside the procedure.

Option Explicit
Private vartext As String ‘ Dim vartext As String can also be used

Sub PrivateScope()
vartext = "Varible is visible to all procedures"
MsgBox vartext, ,”Result from Sub1”
End Sub

Sub VarIsVisible()
MsgBox vartext, ,”Result from Sub2”
End Sub

After execution, you will see that both procedure will return the proper results without any error. For a clear understanding, we have added the right title in the msgbox.

Public Module-Level Scope

These types of variables are visible to all the procedures and in all modules of a project. Insert 2 modules. (Right-click on a sheet and Insert -> modules).

Enter the below code in one module.

Option Explicit

Public vartext As String

Sub PrivateScope()
vartext = "Varible is visible to all modules"
MsgBoxvartext, , "Result from Sub1"
End Sub

Enter the below code in the 2nd module.

Option Explicit

Sub VarIsVisible()
MsgBoxvartext, , "Result from Sub2"
End Sub

Results are shown below

Let’s consider a practical example and understand the scope better.

Attached is the reference file for the above code.

Static Variable

Usually, the value of the variable is only preserved from the procedure call to the procedure ends. It doesn’t preserve the value once the execution of the procedure ends.

For example, in the above code, the value of firstNo and secondNo is removed as soon as the procedure ends, next time you execute the code, firstNo and secondNo gets the same value as defined in the code.

But Static variables are those that will retain the value of the variable even after the execution. A static variable is allowed only inside a sub or a function.

Consider the below code with a non-static variable.

Option Explicit

Sub staticVariable()
Dim count As Integer
count = count + 1
Debug.Print count
End Sub

Every time you hit F5, you will get only 1 as a result.

Now let’s declare the variable as static.

Option Explicit

Sub staticVariable()
Static count As Integer
count = count + 1
Debug.Print count
End Sub

Every time you execute the code (F5) The result will have an increased value for counts like 1,2,3 etc as the Static variable has retained its value.

Note: Static variable will retain the value until you hit the Reset button on the toolbar or close the workbook.

Constant Variable

As the name indicates, the value of these variables cannot be changed during the execution of the program. Once a constant is declared, it cannot be modified or assigned a new value.

Declaration of a constant can be done within a procedure or at the module level (above all procedures).

Option Explicit

Sub constantVariable()
Const SpeedLimitOfcar As String = "90kmph"
Dim myCarSpeed As String

myCarSpeed = "70kmph"
If myCarSpeed > SpeedLimitOfcar Then
MsgBox "overspeed: Reduce the speed"
Else
MsgBox "Within the limit: Always drive below : " & SpeedLimitOfcar
End If
End Sub

If you attempt to change the constant value, then a compile error will be thrown.

Option Explicit

Sub ChangeConstantVariable()
Const SpeedLimitOfcar As Integer = 90
SpeedLimitOfcar = SpeedLimitOfcar + 10
MsgBox SpeedLimitOfcar
End Sub

FAQs

Q #1) How to set a public variable in VBA?

Answer: Public variables are declared before the start of a procedure. The public keyword must be used while declaring a variable.

Public Total_Marks As Integer

Q #2) What type of variables cannot be declared outside the procedure?

Answer: Static Variables cannot be declared outside the procedure in VBA and you will get a compile error.

Q #3) How to declare a variable in VBA?

Answer: Dim keyword is used to declare a variable. Dim stands for Dimension.

Dim variablename As Datatype

Q #4) How to assign a variable value to a cell in VBA?

Answer: You can use the Range function.

var1 = 10
Range(“C1:C14”).Value = var1

Q #5) What are Variables in VBA?

Answer: Variables are like placeholders for the values in computer storage. Variables can hold different varieties of values and those values can change during the execution of the code. Every variable should have a name, using the variable name, the computer will fetch the value assigned for that variable.

Q #6) Do you have to declare a Variable in VBA?

Answer: The declaration is optional in VBA. If you don’t declare a variable and directly use it in the procedure, then it is called an Implicit Declaration. To avoid errors in the code and for better readability, it is recommended to declare a variable explicitly.

Q #7) How do I declare multiple Variables in VBA?

Answer: You can declare multiple variables in one declaration statement. You just need to specify the variable name separated by commas in one As clause.

Dim FirstNo, SecondNo As Integer

You can also specify different variables in one declaration statement. Each variable will take the data type specified in the As clause after its variable name part.

Dim a, b As Single, c, d As Double, e As Integer, f As String

Q #8) When would there be no difference between dim and private in VBA?

Answer: If you are declaring a private module-level scope, then there is no difference between declaring a variable as dim or private. By default, variables declared with the Dim are scoped as private. But it is recommended to add private for better readability.

Q #9) What is a Module-level variable?

Answer: Module-level variables can be private or public. Private variables are available for all the procedures in that module and are not visible to any other module. Public variables are visible to all the procedures in all the modules in a project.

Conclusion

A variable is crucial in any programming language. In this tutorial, we have seen what are variables, and how to declare and use them in the program. We also explored the Option Explicit method, which forces the implementation of the explicit declaration.

Different types of variable scope that help the users to determine which parts of the code the variable can be used were discussed. We learned the usage of Static variables that helps to retain the value of the variable and Constant variables that help to maintain the value of the variable unchanged.

=> Visit Here To Learn VBA From Scratch