Introduction to Excel VBA

Introduction to Excel VBA

1.1 The Concept of Excel VBA

VBA is the acronym for Visual Basic for Applications. It is an integration of the

Microsoft's event-driven programming language Visual Basic with Microsoft Office

applications such as Microsoft Excel, Microsoft Word, Microsoft PowerPoint and

more. By running Visual Basic IDE within the Microsoft Office applications, we can

build customized solutions and programs to enhance the capabilities of those

applications.

Among the Visual Basic for applications, Microsoft Excel VBA is the most popular.

There are many reasons why we should learn VBA for Microsoft Excel, among them

is you can learn the fundamentals of Visual Basic programming within the MS Excel

environment, without having to purchase a copy of Microsoft Visual Basic software.

Another reason is by learning Excel VBA; you can build custom made functions to

complement the built-in formulas and functions of Microsoft Excel. Although MS

Excel has many built-in formulas and functions, they are not enough for certain

complex calculations and applications. For example, it is very dificult to calculate

monthly payment for a loan taken using Excel's built-in formulas, but it is relatively

easier to write VBA code for such calculation. This book is written in such a way that

you can learn VBA for MS Excel at your own pace.

You can write Excel VBA code in every version of Microsoft Office, including MS

Office 97, MS Office2000, MS Office2002, MS Office2003, MS Office XP , MS Office

2007 and MS Offce 2010. By using VBA, you can build some very powerful tools in

2

MS Excel, including financial and scientific applications that can perform financial

calculations and programs that can perform statistical analyses.

1.2 The Visual Basic Editor in MS Excel

There are two ways which you can start VBA programming in MS Excel. The first is

to place a command button on the spreadsheet and start programming by clicking the

command button to launch the Visual Basic Editor. The second way is to launch the

Visual Basic Editor by clicking on the Tools menu then select Macro from the dropdown

menu and choose Visual Basic Editor. Lets start with the command button first.

In order to place a command button on the MS Excel spreadsheet, you click the View

item on the MS Excel menu bar and then click on toolbars and finally select the

Control Toolbox after which the control toolbox bar will appear, as shown in Figure

1.1. ,then click on the command button and draw it on the spreadsheet, as shown in

Figure 1.2.

Figure 1.1: Displaying Control Toolbox in MS Excel.

3

Figure 1.2: The Command Button in Design Mode

Now you select the command button and make sure the design button on the far left

of the control toolbox is depressed. Next, click on the command button to launch the

Visual Basic Editor. Enter the statements as shown in figure 1.3. Let’s write out the

code here:

Example 1.1

Private Sub CommandButton1_Click ()

Range (“A1:A10).Value=”Visual Basic “

Range (“C11”).Value=Range (“A11”).Value +Range (“B11”).Value

End Sub

4

The first statement will fill up cell A1 to cell A10 with the phrase "Visual Basic" while

the second statement add the values in cell A11 and cell B11 and then display the

sum in cell C11. To run the program, you need to exit the Visual Basic Editor by

clicking the Excel button on the far left corner of the tool bar. When you are in the MS

Excel environment, you can exit the design mode by clicking the design button, then

click on the command button.

Figure 1.3: The Visual Basic Editor IDE in MS Excel

Running the above VBA will give you the output as shown in Figure 1.4

5

Figure 1.4:

1.3 The Excel VBA Code

Writing Excel VBA code is almost exactly the same as writing code in Visual Basic,

which means you have to use syntaxes similar to Visual Basic. However, there are

codes specially designed for use in MS Excel, such as the use of the object or

function called Range. It is the function that specifies the value of a cell or a range of

cells in MS Excel spreadsheet. The format of using Range is as follows:

Range(“cell Name”).Value=K or Range(“Range of Cells”).Value=K

Where Value is the property of Range and K can be a numeric value or a string

6

Example 1.2

Private Sub CommandButton1_Click ()

Range (“A1”).Value= “VBA”

End Sub

The above example will enter the text “VBA” into cell A1 of the MS Excel spreadsheet

when the user presses the command button. You can also use Range without the

Value property, as shown in Example 1.3:

Example 1.3

Private Sub CommandButton1_Click ()

Range ("A1") = 100

End Sub

In the above example, clicking the command button with enter the value of 100 into

cell A1 of the MS Excel spreadsheet. The follow example demonstrates how to input

values into a range of cells:

Example 1.4

Private Sub CommandButton1_Click ()

Range ("A1:A10") = 100

End Sub

7

Chapter 2

Working with Variables in Excel VBA

2.1 The Concept of Variables

Variables are like mail boxes in the post office. The contents of the variables change

every now and then, just like the mail boxes. In Excel VBA, variables are areas

allocated by the computer memory to hold data. Like the mail boxes, 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 Table 2.1

Table 2.1: Examples of valid and invalid variable names

Valid Name Invalid Name

My_Car My.Car

this year 1NewBoy

Long_Name_Can_beUSE He&HisFather *& is not acceptable

Group88 Student ID * Space not allowed

8

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 in Table 2.2.

Table 2.2: Numeric Data Types

Type Storage Range of Values

Byte 1 byte 0 to 255

Integer 2 bytes -32,768 to 32,767

Long 4 bytes -2,147,483,648 to 2,147,483,648

Single 4 bytes

-3.402823E+38 to -1.401298E-45 for negative values

1.401298E-45 to 3.402823E+38 for positive values.

Double 8 bytes

-1.79769313486232e+308 to -4.94065645841247E-324 for

negative values

4.94065645841247E-324 to 1.79769313486232e+308 for

positive values.

Currency 8 bytes -922,337,203,685,477.5808 to 922,337,203,685,477.5807

Decimal 12 bytes

+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use

+/- 7.9228162514264337593543950335 (28 decimal places).

9

ii) Non-numeric Data Types

Nonnumeric data types are summarized in Table 2.3

Table 2.3: Nonnumeric Data Types

Data Type Storage Range

String(fixed length) Length of string 1 to 65,400 characters

String(variable length) Length + 10 bytes 0 to 2 billion characters

Date 8 bytes January 1, 100 to December 31, 9999

Boolean 2 bytes True or False

Object 4 bytes Any embedded object

Variant(numeric) 16 bytes Any value as large as Double

Variant(text) Length+22 bytes Same as variable-length string

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=8 and label=”Welcome”.

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 variableName as DataType

Example 2.1

Dim password As String

Dim yourName As String

Dim firstnum As Integer

Dim secondnum As Integer

Dim total As Integer

Dim BirthDay As Date

10

You may also combine them into one line, separating each variable with a comma, as

follows:

Dim password As String, yourName As String, firstnum 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 Example 2.1 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 yourName can hold no more than 10 Characters.

Example 2.2

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

currency.

Private Sub CommandButton1_Click()

Dim YourName As String, BirthDay As Date, Income As Currency

YourName = "Alex"

BirthDay = "1 April 1980"

Income = 1000

Range("A1") = YourName

Range("A2") = BirthDay

Range("A3") = Income

End Sub

11

Figure 2.1: Output screen for Example 2.2

2.2 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.

12

Example 2.3

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

being tracked.

Option Explicit

Private Sub CommandButton1_Click()

Dim YourName As String, password As String

YourName = "John"

password = 12345

Cells(1, 2) = YourNam

Cells(1, 3) = password

End Sub

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

Figure 2.2: Error message due to typo error

13

2.3 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

2.4 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

Table 2.3.

14

Table 2.3: Arithmetic Operators

Operator Mathematical function Example

^ Exponential 2^4=16

* Multiplication 4*3=12

/ Division 12/4=3

Mod

Modulus (return the remainder from an

integer division)

15 Mod 4=3

\ Integer Division (discards the decimal places) 19\4=4

+ or & String concatenation

"Visual"&"Basic"="Visual

Basic"

Example 2.4

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