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.