Working With Variables

You will read about Variables a lot while learning VBA and we use them to a very large extent in most of the codes we write. It’s better to know about them before starting real coding. So, let’s see.

Variable is essentially a storage location where you can store data. As the name suggests, this value can be changed during the macro execution. You can literally define any number of variables you need to work in your project but each variable will have to be differently named.

Naming a Variable

There are some rules to follow while naming a variable. Let’s see what they are below.

• Name length must be within 255 characters
• Space, Period (dot/full stop), exclamation (!), @, &, $, # cannot be used in the name
• Should not begin with a number
• Reserved names cannot be used
• Names are not case sensitive
• You cannot duplicate names in the same procedure

Remember our Named Range post? You can relate these rules to them. Few examples of valid names can be: MyName, This_Year, Year1, NewVal etc. Invalid name examples are: My.Name (dot not allowed), This Year (space), 1Year (number at the start), New&Val (& used).

Declare a Variable

If you decide to use a variable in your procedure, you will have to declare that to VBA. This is usually done with a key word Dim.  For example: if I have to use a variable named MyVal, I enter the following line in my code.

Dim MyVal

If you place this line after Sub statement, this variable can be used anywhere in the same macro until End Sub. If you place this at the start of the module code before any Sub statement, you can use this variable throughout all the macros written in that module.

Variables occupy memory to work with. Though VBA is capable of deciding the nature of the variable when it encounters the actual usage of variable in a line of code, it is usually useful to define the data type for which this variable is used. For example: you can define if you are going to use the variable to store a text string or a date or another object etc. Defining this would help VBA to reserve exact memory required and often it saves from risk of storing an unintended value. To define the data type, you will have to enter below line.

Dim MyVal As XX

You have multiple options to use in place of XX. See below to know what those are.

So if you want your variable named MyVal to store a number that is between 0 and 255, you declare it as in below line.

Dim MyVal as Byte

If you or your procedure tries to give a value to variable that is outside of 0 and 255, you will get a run time error. Look at the below picture, I declared variable as byte and tried to set its value to 2556 and executed the code. This is the error I got.

As said earlier, declaring the variable in advance is not mandatory. In the code in above picture, I can straight away enter MyVal = 2556 without declaring the variable. However this causes few problems. If you do not declare the variable and data type, VBA reserves more memory than required. One more problem is that you cannot capture correct data type in the variable. In above example: your intention is not to store any number outside 0 and 255, but if you don’t declare the data type, VBA happily accepts the number 2556. One more problem is related to misspelling. VBA might take any misspelt words as a different variable and your project may produce undesired results. If you want to mandate VBA to ask for declaration of every variable, read about Option Explicit below.

Option Explicit

This is to make sure you declare each variable before using it in the module. Enter Option Explicit at the top of module, before starting of any code with Sub. You can also click on Tools > Options > Editor tab > check or clear Require Variable Declaration.

Declaring Multiple Variables

Many times, you might have to use more than one variable in your module. You can declare all of them in 2 ways as shown below.

Method 1

Dim MyVal As Byte

Dim MyName As String

Dim Cl As Range

Dim SelOpt As Boolean

Method 2

Dim MyVal As Byte, Dim MyName As String, Dim Cl As Range, Dim SelOpt As Boolean

Finishing Touch

Once you declare variables, you can start using them to store values so that they can used somewhere else in your code. For example, copy the below code in to a VBE window and press F5.

Sub Test()
Dim UserName As String 'Declaring variable
UserName = Application.UserName 'Passing value to variable
MsgBox ("You are signed in as " & UserName) 'Using variable in another action
End Sub

Running this code on my system will show me a dialogue box as show below.