Variables

The Concept of Variables


In Excel VBA, variables are areas allocated by the computer memory to hold data. Each variable must be given a name. To name a variable in Excel VBA, you have to follow a set of rules, as follows:

a) Variable Names

The following are the rules when naming the variables in VBA

  •          It must be less than 255 characters
  •          No spacing is allowed
  •          It must not begin with a number
  •          Period is not permitted

Examples of valid and invalid variable names are displayed in below table

Examples of valid and invalid variable names:




b) Declaring Variables

In VBA, we need to declare the variables before using them by assigning names and
data types. There are many VBA data types, which can be grossly divided into two
types, namely the numeric data types and the non-numeric data types.

i) Numeric Data Types

Numeric data types are types of data that consist of numbers, which can be
computed mathematically with various standard arithmetic operators such as Addition,
Subtraction, Multiplication, Division and more. In VBA, the numeric data are divided
into 7 types, which are summarized shown in below table.

Numeric Data Types:





ii) Non-numeric Data Types
Nonnumeric data types are summarized in below table

Non-numeric Data Types:






You can declare the variables implicitly or explicitly. For example, sum=text1.text
means that the variable sum is declared implicitly and ready to receive the input in
textbox1. Other examples of implicit declaration are volume=10 and label=”Dreams”.
On the other hand, for explicit declaration, variables are normally declared in the
general section of the code window using the Dim statements. Here is the syntax:
Dim variable_name as DataType

Example:
Dim Dreams As String
Dim eName As String
Dim fNumber As Integer
Dim sNumber As Integer
Dim sumAll As Integer
Dim bDate As Date

You may also combine them into one line, separating each variable with a comma, as
follows:
Dim Dreams As String, eName As String, fNumber As Integer.

If the data type is not specified, VBE will automatically declare the variable as a
Variant. For string declaration, there are two possible formats, one for the variablelength
string and another for the fixed-length string. For the variable-length string, just
use the same format as mentioned above. However, for the fixed-length string, you
have to use the format as shown below:

Dim VariableName as String * n

Where n defines the number of characters the string can hold. For example, Dim
yourName as String * 10 mean eName can hold no more than 10 Characters.

In this example, we declared three types of variables, namely the string, date and
currency.

Private Sub CommandButton1_Click()
Dim fName As String, bDate As Date, Salary As Currency
fName = "ABC"
bDate = "23 Nov 1981"
Salary = 1000
Range("A1") = fName
Range("A2") = bDate
Range("A3") = Salary
End Sub

The use of Option Explicit

The use of Option Explicit is to help us to track errors in the usage of variable names
within a program code. For example, if we commit a typo, VBE will pop up an error
message “Variable not defined”. Indeed, Option Explicit forces the programmer to
declare all the variables using the Dim keyword. It is a good practice to use Option
Explicit because it will prevent us from using incorrect variable names due to typing
errors, especially when the program gets larger. With the usage of Option Explicit, it
will save us time in debugging our programs.
When Option Explicit is included in the program code, we have to delare all variables
with the Dim keyword. Any variable not declared or wrongly typed will cause the
program to popup the “Variable not defined” error message. We have to correct the
error before the program can continue to run.

This example uses the Option Explicit keyword and it demonstrates how a typo is
being tracked.

Option Explicit
Private Sub CommandButton1_Click()
Dim myName As String, pass As String
myName = "John"
pass = 12345
Cells(1, 2) = myNam
Cells(1, 3) = pass
End Sub

The typo is myNam and the error message ‘variable not defined” is displayed .

Assigning Values to the Variables

After declaring various variables using the Dim statements, we can assign values to
those variables. The general format of an assignment is
Variable=Expression
The variable can be a declared variable or a control property value. The expression
could be a mathematical expression, a number, a string, a Boolean value (true or
false) and more. Here are some examples:

firstNumber=100
secondNumber=firstNumber-99
userName="John Lyan"
userpass.Text = password
Label1.Visible = True
Command1.Visible = false
ThirdNumber = Val(usernum1.Text)
total = firstNumber + secondNumber+ThirdNumber

Performing Arithmetic Operations in Excel VBA
In order to compute input from the user and to generate results in Excel VBA, we can
use various mathematical operators. In Excel VBA, except for + and -, the symbols
for the operators are different from normal mathematical operators, as shown in below table.

Arithmetic Operators
Operator Mathematical function Example






Example:

Option Explicit
Private Sub CommandButton1_Click ()
Dim number1, number2, number3 as Single
Dim total, average as Double
number1=Cells (1, 1).Value
number1=Cells (2, 1).Value
number3= Cells (3, 1).Value
Total=number1+number2+number3
Average=Total/3
Cells (5, 1) =Total
Cells (6, 1) =Average
End Sub

In example 2.4, three variables are declared as single and another two variables are
declared as variant. Variant means the variable can hold any numeric data type. The
program computes the total and average of the three numbers that are entered into
three cells in the Excel spreadsheet.

Example:

Option Explicit
Private Sub CommandButton1_Click()
Dim secondName As String, yourName As String
firstName = Cells(1,1).Value
secondName = Cells(2,1).Value
yourName = firstName + " " + secondName
Cells(3,1) = yourName
End Sub

In the above example, three variables are declared as string. The variable firstName
and the variable secondName will receive their data entered by the user into
Cells(1,1) and cells(2,1) respectively. The variable yourName will be assigned the
data by combining the first two variables. Finally, yourName is displayed on Cells (3,1). Performing addition on strings will result in concatenation of the strings. Names in A1 and A2 are joined up and displayed in A3.