VBA Variables - The Corner Stone of VBA Programs

Udemy Editor

VBA stands for Visual Basic for Applications. It is an event driven programming language designed to automate repetitive task in MS Office Suite applications. This language offers ease of use and provides useful and powerful programming features.We walk you through this beginners level course on VBA variables. We assume that you’re familiar with the basics of MS Excel and Visual Basic for Applications. If you are new to this, you can take this beginners course on MS Excel essentials for VBA, including macros.

Variables are an integral part of all programming languages. Variables are the keywords used to store important data values, that you want to use later on. They have to be declared before using them. They can store different values at different times – ie the value can change over time. When you declare a variable the computer sets apart some memory, for storing it. The size of the memory depends on the data type of the variable.

The syntax for declaring a variable in VBA is as follows.

Dim <variable name> As <data type>

Dim indicates that a variable is being declared.
Variable name is the name of the declared variable.
As is used to set the variable type.
Data type is the variable type

Take a look at the given example to help you understand this better.

Example 1: Displaying a variable in a message box

Sub variables()
Dim new_variable As Integer
new_variable = 10
MsgBox new_variable
End Sub

In this VBA program, first new_variable is declared to be a variable of data type integer. In other it can store integer values. In the next statement the variable is assigned value 10. Finally, the message box displays the value of the variable. Learn to write your own VBA programs with this course.

VBA variable data types

Variables can be of different types, depending on the type of data you want to store. The selected type determines the amount of memory storage set apart for the variable, as well as which operations are possible on it. Here is a list of six different types of commonly used VBA variable data types

Numerical Data types

Byte
Integer
Long
Currency
Single
Double

Text data type includes String
Date data type includes Date (date and time)
Boolean data type (True or False)
Object data type (MS Object)
Variant data type can store any kind of data.

Note that if a variable is not declared it is treated as the variant data type.Listed below are a few examples that will help you get a better insight about the above mentioned VBA variable data types.

```
Dim num As byte
num = 100
```

```
Dim new_dec As Single
new_dec = 456.78
```

Dim newtext As String
newtext = "Good Morning"

Dim var_date As Date
var_date = "06.01.2014"

Dim newboolean As Boolean
newBoolean = True

Dim newsheet As Worksheet
Set newsheet = Sheets("Sheet3")
newsheet.Activate

Lifetime and scope of VBA variables

In VBA variables can have different scopes- procedure level, module level and global level. It’s recommended that the variable be defined with the smallest scope as possible. This will simplify the program code in VBA.

Local variable– This is declared inside a procedure. A local variable comes into existence when the procedure is called and goes out of existence when the procedure terminates. It can be referenced only within the procedure.
Member variable– This is declared inside a class, structure or module. However, it cannot be declared in any procedure found inside the latter.
Global Variable– This can be referenced from anywhere in the program. It comes into existence upon the start of the program and goes out of existence when the program terminates. The syntax to declare a global variable in VBA is as follows.

Global <variable name> As <data type>

Note that the keyword Dim is replaced by the keyword Global. Take this course to learn more about the scope of variables in VBA.

Example 2: Get details from the worksheet and display them in a dialog box.

Sub variables()
Dim lastname As String, firstname As String, age As Integer
lastname = Cells(2, 1)
firstname = Cells(2, 2)
age = Cells(2, 3)
MsgBox lastname & " " & firstname & ", " & age & " years old"
End Sub

Here we declare the variables on the same line separated by commas. Then the variable lastname is assigned the value of the cell A2 and the variable firstname is assigned the value of cell B2. While the variable age is assigned the value of the cell C2. The dialog box displays the result by using the & operator to join the different values.

Example 3: Display the row number stored in F5

Sub variables()
Dim lastname As String, firstname As String, age As Integer, rownum As Integer
rownum = Range("F5") + 1
lastname = Cells(rownum, 1)
firstname = Cells(rownum, 2)
age = Cells(rownum, 3)
MsgBox lastname & " " & firstname & ", " & age & " years old"
End Sub

The variable rownum is assigned the value of Cell F5. After this we add the number 1 to the value. This is done as the first row of the table contains the table titles. Hence we get the row number that we want. Finally, in the cells command we pass as parameters the correct row number. This will result in the intialisation of the three variables- lastname, firstname and age. Then we use the dialog box to display the fullname and the age of the concerned person.

Example 4: How to use variables in VBA Excel range

Set Range1 = Worksheets("Worksheet1").Range("A1:D4")

Here the variable Range1 will hold the range A1:D4 on the worksheet named worksheet1.

Range1.Cells(1, 2) = "Good Morning"

Here the string Good Morning is put in cell A2. You can store the location of the range in variables. For example

worksheetName = "Worksheet1"
rangeString= "A1:D4"

If you wish to use the range, you can use the following code.

Worksheets(worksheetName).Range(rangeString)

Go ahead and try out some of these programs on your own. That’s the best way to learn any programming! Feel free to experiment with the code on your own. You can also look up this ultimate VBA course for more examples.