A SunCam online continuing education course

Programming MS Excel in Visual Basic (VBA)

Part 4-Error Handling, Arrays, Dialog Boxes, Charts

by

Kwabena Ofosu, Ph.D., P.E., PTOE

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Abstract

This course is the fourth of a four-part series on computer programming in Excel Visual Basic for
Applications (VBA), tailored to practicing engineers. In this course the topics, error handling,
arrays, dialog boxes, and charts and graphs are presented. Several examples relevant to
engineering are used to illustrate and demonstrate the concepts and methods learned in this class.
A mini-project is used to demonstrate the programming concepts and methods in a situation
encountered by practicing engineers.

Computer Programming in Visual Basic (VBA) – Part 1, Part 2, and Part 3 are not required pre-
requisites to this course. It would however be helpful to understand the basic principles of
computer programming as well as the fundamentals of the Excel VBA language as presented in
Part 1, Part 2 and Part 3 of this series.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 2 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

TABLE OF CONTENTS

Abstract ........................................................................................................................................... 2
List of Tables .................................................................................................................................. 5
1. DIALOGUE BOXES .................................................................................................................. 6
1.1 Input Box .............................................................................................................................. 9
1.2 File and Folder Dialog Boxes ............................................................................................. 19
1.3 msoFileDialogOpen ............................................................................................................ 19
1.4 Customizing the Dialog Box ............................................................................................... 26
1.5 msoFileDialogFolderPicker ............................................................................................... 31
1.6 msoFileDialogFilePicker.................................................................................................... 34
1.7 msoFileDialogSaveAs ......................................................................................................... 37
2. ARRAYS .................................................................................................................................. 41
2.1 Declaring an Array .............................................................................................................. 41
2.2 Array Elements ................................................................................................................... 42
2.3 Matrices............................................................................................................................... 43
2.4 Dynamic Arrays .................................................................................................................. 45
2.5 For Each … Next Statement ............................................................................................... 47
3. ERROR HANDLING ............................................................................................................... 49
3.1 Compiler Errors .................................................................................................................. 49
3.2 Run-time Errors .................................................................................................................. 54
3.3 Logic Errors ........................................................................................................................ 58
3.4 On Error Statements ............................................................................................................ 59
3.5 On Error Goto 0 .................................................................................................................. 59
3.6 On Error Resume Next........................................................................................................ 62
3.7 On Error Goto < label >: ..................................................................................................... 64
3.8 Error Handling Blocks ........................................................................................................ 67
3.9 The Resume Statement ....................................................................................................... 67
3.10 Caution on Error Handlers ................................................................................................ 70
3.11 Seventh VBA Project ........................................................................................................ 70
4. GRAPHS ................................................................................................................................... 93

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 3 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

4.1 Chart Objects ...................................................................................................................... 93

4.2 Creating Graphs .................................................................................................................. 93
5. CONCLUSION ......................................................................................................................... 98
REFERENCES ............................................................................................................................. 99

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 4 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

List of Tables

Table 6: Values for input box Type argument .............................................................................. 10

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 5 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

1. DIALOGUE BOXES

A dialog box is a secondary window that enables an application to retrieve input from the user.
Dialogue boxes are used to ask users questions, provide information to the user, or communicate
feedback to or from the user. A modal dialog box requires the user to complete and close before
continuing. In a modeless dialog box the user may choose not to complete it but the program
execution will proceed.

Generally a dialogue box consists of the following:

Title bar: The title bar identifies the application or feature from which it originated.

Main instruction: This is some question or statement on the dialog box that communicates its
primary purpose. It may also include an icon or symbol.

Content Area: The content area provides further descriptive information regarding the dialog box
and its purpose.

Command Area: This consists of controls such as buttons or a textbox where the user
communicates with the application.

Footnote area: This is an optional element that provides further guidance particularly for less
experienced users.

For example,
Open a new Excel session.
Click on Home to open the Home ribbon.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 6 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Click on the Find & Select icon.

Select Replace

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 7 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The Find and Replace dialog box opens. This dialogue box can be used to search for a letter,
word, phrase, sentence etc., in a document or code window, and replace it (or them) throughout
the document with the entry in the Replace with control. It is required that the user make some
selection or the other in the command area in order to dismiss the dialog box and proceed, even
if no information is supplied in the Find what and Replace with controls. This is therefore modal
dialog box.

In this chapter a selection of VBA dialog boxes will be reviewed.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 8 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

1.1 Input Box

An input box is a special dialog box that requests a value from a user. The value is stored in
some variable which is then called within some procedure as part of some manipulation or
calculation, etc. In its simplest form, the input box is of the following structure.

The user must enter the requested input and click on OK to dismiss the input box and enable the
program to proceed. The user may decide to Cancel in which case the input box is dismissed and
no user input is supplied to the program.

The syntax for the input box is

avariable = Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId,

Type)

where,
Prompt is a required string data type, and is the message displayed in the main area of the dialog
box.

Title is an optional variant data type, and is the title for the input box.

Default is an optional variant data type that specifies a value that will appear in the text box
when the input box opens. If this argument is omitted, the text box will be empty. Note that this
value can be a range object, for example, a range of cells on a spreadsheet.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 9 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Left is an optional variant type that specifies the horizontal position of the top left corner of the
input box in relation to the upper left corner of your computer’s monitor. If omitted, the input
box will be centered horizontally.

Top is an optional variant type that specifies the vertical position of the input box in relation to
the upper left corner of the computer screen. If omitted, the input box will be centered vertically.

HelpFile is an optional variant type that specifies the name of the Help file for the input box. If
the HelpFile and HelpContextID arguments are provided, a Help button will appear on the dialog
box.

Type is an optional variant type that specifies the return data type. If omitted, the input box
returns a text data type. The values the Type argument can realize are as listed in Table 6.

For simple input boxes, the Application class is optional.

Table 1: Values for input box Type argument

Type value Returned data type

0 A formula
1 A number
2 Text (a string)
4 A logical value (True or False)
8 A cell reference, as a Range object
16 An error value, such as #N/A
64 An array of values

Example 1: Simple Input Box:

Open a new Excel session.
Select Developer, and click on Design Mode to activate it.
Click on Insert, Select CommandButton from ActiveX controls to add one to the spreadsheet.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 10 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Trace out the command button onto the spreadsheet.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 11 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Double click on the command button to enter the code window.

Type the following code.

Clicking on the command button will activate an input box. This is an input box with a prompt
and a title only (the first two arguments in the input box syntax). Note that the Application class
has also been omitted. The user entry will be stored in the variable dblInput. The value of the
variable holding the user entry value will then be displayed on a simple message box.

Save the file.

Click on Debug, Select Compile VBAProject.
Correct any errors and recompile, otherwise save and go to the spreadsheet.
Click on Design Mode to deactivate it.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 12 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Click on the command button.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 13 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Follow the prompt and enter a value and click on OK.

Click on OK to dismiss the message box.

Save the file.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 14 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Example 2:Detailed Input Box

Create a login application where an employee must enter their full name, and then their four digit
company ID number to enter the system. A message box will notify the user of a successful
login.

Activate Design Mode.

Add a second command button to the spreadsheet.
Right click on the command button.
Select Properties.
Change the Caption property to CommandButton2.
Close Properties.

Double click on the command button to go to the code window.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 15 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

In this application, an input box will ask the user for their full name. A second input box will ask
the user to enter their four digit ID number. Once the information is confirmed, a message box
notifies the user of a successful login.

Type the following code.

Note that in this case the full blown input box syntax is being used. Therefore the Application
class is appended to the input box call. Note the data types being used to store the user inputs.
Also note the argument values and how they are assigned with the “:=”. Particularly, review the
Type argument values.

Save the file.

Click on Debug, Select Compile VBAProject.
Correct any errors and recompile, otherwise Save and go to the spreadsheet.
Click on Design Mode to deactivate it.
Test the application.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 16 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Click on CommandButton2.
The command button event procedure executes, and the first input box opens.
Notice the change in the appearance of the input box due to the full blown syntax structure being
used.

Enter a name and click OK.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 17 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The next input box pops up.

Enter your four digit ID number.

Click on OK. The message box opens, confirming the user information.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 18 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Click on OK to dismiss the message box.

The application has been successfully tested.

Note that in this example, any name and four digit ID combination will result in a successful
login. To ensure that only valid employees and ID numbers are granted access, the names and ID
numbers of current employees would have to be used to set up a conditional statement. The
variables capturing the user inputs would then be checked against the names and IDs in the
conditional statement’s logic, and if a match is found login, access will be granted, otherwise
access will be denied. Given that two inputs will be checked, a nested conditional statement or a
composite conditional statement, or some combination of both would be applicable.

1.2 File and Folder Dialog Boxes

File and folder dialog boxes enable a user to select a file or folder to open, or a folder to save a
file to, through a dialog box. This is accomplished by using the VBA FileDialog object. There
are a number of choices that can be applied. In each case, a unique value is passed to the
FileDialog object.

1.3 msoFileDialogOpen

This choice of the VBA FileDialog object enables a user to select one or more files to open.

Open a new spreadsheet, and activate the Design mode.

Insert a new ActiveX command button.
Double click on the button to go to the code window.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 19 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Type the following code.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 20 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Note the following highlights of the code.

Declaration of variable as
FileDialog object type

Assignment of FileDialog
call to the variable
Open the dialog box

Conditional statement
such that if user does not
select a file to open (or
selects cancel), a message
box tells the user that no
file was selected.

Save the path to the

selected file
Open the file

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 21 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Save the file.

Click on Debug, Select Compile VBAProject.
Correct any errors and recompile, otherwise save and go to the spreadsheet.
Click on Design Mode to deactivate it
Click on the command button.
The dialog box opens.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 22 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Navigate to a file and select it, and click on the Open button to open the file.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 23 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The selected file opens.

The dialog box is dismissed.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 24 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Reactivate the dialog box

This time click on the Cancel button.
The dialog box is dismissed, and the message box pops up telling the user that a file was not
selected.

Click on the OK button to dismiss the message box.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 25 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

1.4 Customizing the Dialog Box

The appearance of the dialog box can be customized by adjusting properties of the FileDialog
object.

Copy and paste the command button.

Change the Caption of the new command button to CommandButton2 via its Properties.

Double click on the command button to enter the code window.

Type the following code that will adjust the following properties of the dialog box

 The Title.

 The default directory that opens in the dialog box.

 Clear any filters. The filter property enables only a certain type of file to be visible and
selectable based on the filter value applied.
Add a new filter such that only text files in a directory will be visible in the dialog box.

 Change the caption on the Open button.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 26 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 27 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Save the file.

Click on Debug, Select Compile VBAProject.
Correct any errors and recompile, otherwise save and go to the spreadsheet.
Click on Design Mode to deactivate it.
Test the command button.
Click on CommandButton2.

Caption not changed

Note that due to the text file filter, regardless of which folder the user navigates to, only text files
in that folder are visible and can be selected.

Review and confirm the customized features.

Navigate to a text file and open it (or select Cancel)

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 28 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Caption changes to customized

caption once a file is selected

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 29 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The file opens (in Excel)

The test is complete and successful.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 30 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

1.5 msoFileDialogFolderPicker

This choice of the FileDialog object enables a user to select a path to a folder.

Activate the Design mode.

Insert an ActiveX command button.
Double click on the button to go to the code window.
Type the following code.

This code is such that the folder path will be displayed on a message box.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 31 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Save the file.

Click on Debug, select Compile VBAProject.
Correct any errors and recompile, otherwise save and go to the spreadsheet.
Click on Design Mode to deactivate it.
Test the command button.
Click on CommandButton3.
Navigate to a folder.

Note that the contents of the folder are not visible, but only the path to the folder.

As with the previous example, the dialog box may be customized as needed.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 32 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Click OK.
The message box displays the path of the folder that was selected in the dialog box.

Click OK to dismiss the message box.

The test is complete and successful.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 33 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

1.6 msoFileDialogFilePicker

This choice enables a user to select a path to one or more files, but will not open the file(s).

Activate the Design Mode.

Insert an ActiveX command button.
Double click on the command button to go to the code window.
Type the following code.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 34 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Save the file.

Click on Debug, Select Compile VBAProject.
Correct any errors and recompile, otherwise save and go to the spreadsheet.
Click on Design Mode to deactivate it.
Test the command button.
Click on CommandButton4.
Navigate to a file, and select it.

As with the previous examples, the dialog box may be customized as needed.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 35 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The message box displays the path of the file that was selected in the dialog box.

Click OK to dismiss the dialog box.

The test is complete and successful.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 36 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

1.7 msoFileDialogSaveAs

This choice enables a user to select a location to save a file to.

Activate the Design Mode.

Insert an ActiveX command button.
Double click on the command button to go to the code window.
Type the following code.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 37 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Save the file.

Click on Debug, select Compile VBAProject.
Correct any errors and recompile, otherwise save and go to the spreadsheet.
Click on Design Mode to deactivate it.
Test the command button.
Click on CommandButton5.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 38 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Save a copy of the current file.

Navigate to a directory, and enter a name for the new file.
Select an appropriate file suffix in the Save as type drop down

As with the previous examples, the dialog box may have been customized as needed.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 39 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Click on Save.
The dialog box dismisses.
The new file is created and saved with the name and directory location as selected and entered in
the dialog box.

The test is complete and successful.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 40 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

2. ARRAYS

An array is a variable that holds multiple values of the same data type. The individual values are
the elements of the array. Each element has a unique index (or address) to identify it. For
example, consider the test scores of students in a computer programming class. The score of each
student can be stored individually and separately as a variable. Alternately, the scores of all of
the students can be saved collectively as one variable using an array. Each individual test score
would therefore be an element of the array.

In large and complex programs, arrays can dramatically reduce the number of variables and the
amount of code written.

2.1 Declaring an Array

An array is declared in a Dim statement by giving it a variable name and appending the number
of elements it will hold (this is called the dimension list) in parenthesis, and stating the data
type. The syntax for declaring an array is as follows

Dim arryname(n) As datatype

where
n is the highest index, starting from zero. This part of the syntax is the dimension
list.

For example, an array to hold the test scores (in percent) of a class of 25 will be of the form

Dim arrTests(24) As Integer

This array contains 25 elements, namely, element 0, element 1, element 2, … , element 24.

Alternately, the declaration may be of the form

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 41 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Dim arryname(firstindex To lastindex) As datatype

Therefore using this alternate dimension list format, the array for the test scores can be declared
as

Dim arrTests(1 To 25) As Integer

In this case the first element is of index 1, and the last element is of index 25, for a total of 25
elements.

The choice of how to set up the dimension list is entirely up to the programmer.

2.2 Array Elements

An array element is identified or called as follows

arrayname(i)

where
arrayname is the name of the array as declared in the declaration statement, and
i is the index of the element.

The array elements for the arrTests array can be depicted as a list of items as follows

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 42 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Using Dim arrTestScores(24) As Integer

arrTestScores(1) arrTestScores(24)
arrTestScores(0)

[95 98 85 … … … 91]

arrTestScores(1) arrTestScores(25)
arrTestScores(2)

Using Dim arrTestScores(1 To 25) As Integer

This array consists of one row of values and twenty five columns. This is a one-dimensional
array. A one-dimensional array is also called a vector.

2.3 Matrices

A matrix is a multidimensional array. A matrix has more than one row, and more than one
column.

The declaration of a matrix is as follows

Dim arryname(m, n) As datatype

where
. m is the highest row index, whereby the first row is of index 0
n is the highest column index, whereby the first column is of index 0

Alternately, the matrix may be declared as

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 43 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Dim arryname(firstrowindex To lastrowindex, firstcolumnindex To lastcolumnindex) As datatype

For example, consider a 4 x 5 matrix, i.e. 4 rows and 5 columns, called arrMyMatrix, and holds
integer values. The elements of the matrix are as follows

Using Dim arrMyMatrix(3, 4) As Integer

arrMyMatrix (1,3)
arrMyMatrix (0, 0) arrMyMatrix (3, 4)

| 7 9 5 6 0 |
| 6 5 9 0 2 |
| 9 8 6 1 0 |
| 0 0 5 0 2 |

arrMyMatrix (4,1) arrMyMatrix (4, 5)

arrMyMatrix (3, 3)

Using Dim arrMyMatrix(1 To 4, 1 To 5) As Integer

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 44 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

2.4 Dynamic Arrays

In some applications, it is unknown beforehand what size of array shall be needed. One strategy
is to declare an excessively large array and use only those elements that are needed to meet the
objectives of the project. This strategy however, is inefficient and may have serious impacts on
the computer’s memory. Another alternative is to use a dynamic array.

A dynamic array can be sized and resized, at any point in run time. The process to create a
dynamic array involves

1. Declare the array without a dimension list

Dim arryname( ) As datatype

2. At the point in the code where the array needs to be sized/ resized, use the ReDim statement to
size/ resize it with the relevant dimension(s). Note that the dimension(s) may be a value(s) stored
in some variable(s) that is calculated or generated by the program in run time.

ReDim arryname(m, n )

Example: Consider an array that is used to compile the speeds of vehicles passing over a
pneumatic traffic counting device installed on the highway, every 15 minutes. The device counts
each vehicle and logs its speed. The number of vehicles that will pass in the next 15-minute
period is random and unknown, therefore a static array will be inefficient and difficult to use for
this application. A dynamic array can be created as follows.

Dim arrTrafficSpeeds( ) As Integer

Assuming that after a 15-minute period, 275 vehicles were recorded by the counter, the array can
now be resized to accept the speed data for that 15-minute period.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 45 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

ReDim arrTrafficSpeeds(1 To 275 )

Assume that in the subsequent 15-minute period after that, 305 vehicles were recorded by the
counter, the ReDim statement will be called again to resize the array for the current number of
elements for processing as follows

ReDim arrTrafficSpeeds(1 To 305 )

and so on and so forth.

It must be noted that the ReDim Statement clears all pre-existing elements and dimensions such
that the array is now “empty”, before resizing it to the new dimensions. In some situations this
may be undesirable. To resize an array while keeping the pre-existing data, the Preserve
keyword must be added to the ReDim.

Example: Consider that the traffic engineer would like to compile the speeds of the vehicles in a
given hour into an array but the traffic counting device compiles the volumes every 15 minutes.
Consider the four 15-minute traffic volumes in the coming hour will be 200, 300, 200, and 150
vehicles respectively. The hourly volume can be compiled from the 15-minute volumes as
follows

At the beginning of the first quarter,

Dim arrTrafficSpeeds( ) As Integer

At the end of the first quarter,

ReDim arrTrafficSpeeds(1 To 200 )

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 46 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

At the end of the second quarter, resize the array such that the pre-existing data will be preserved
and together with the current period volume will all fit in the array.

ReDim Preserve arrTrafficSpeeds(1 To 500 )

At the end of the third quarter, resize such that pre-existing and current can fit in the array.
ReDim Preserve arrTrafficSpeeds(1 To 700 )

At the end of the fourth quarter, resize such that pre-existing and current can fit in the array.
ReDim Preserve arrTrafficSpeeds(1 To 850 )

This completes the total for the hour.

Consider that now the engineer wants to reduce this new “hourly” array back down to contain
only the first quarter dimensions, elements, and data. This can be done by ReDim but by
reducing the size of the array.

ReDim Preserve arrTrafficSpeeds(1 To 200 )

Only the first quarter elements will remain. All other elements will be eliminated.

2.5 For Each … Next Statement

A For Each … Next loop can be used to loop through each element in an array. For example
consider vector of 20 elements that is multiplied by a scalar value of 5. Ordinarily a For Next
loop can be used follows

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 47 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

For i = 1 To 20

arrMyVector(i) = 5*arrMyVector(i)

Next

Alternately, the For Each loop can be used as follows

For Each i In arrMyVector( )

arrMyVector(i) = 5*arrMyVector(i)

Next

Note the keyword In. Also, the keyword Next is optional.

The For Each loop will enable a fixed number of iterations be performed even though the
programmer may not know the number of iterations needed. For example, if the array is a
dynamic array and its dimensions are continuously changing at various points in the program, it
may be unclear what the current dimensions are, making the use of a For Next loop problematic.
On the other hand, the For Each loop will apply the target operation(s) on all elements in the
array regardless.

The general form of the For Each loop is therefore

For Each indexvariable In nameofarray( )

‘perform some operation on all the array elements

Next

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 48 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

3. ERROR HANDLING

It is common, even among seasoned programmers, that due to some error(s) in the code, the
program may not work as expected, or it may run partially and prematurely terminate, or not run
at all. Identifying errors and addressing them is called debugging. In programming, error
handling refers to techniques and practices used to anticipate errors in advance, and to provide
code to deal with them as they arise.

In general, errors can be grouped into three categories namely, compiler errors, run-time
errors, and logic errors.

3.1 Compiler Errors

A compiler error occurs when a command, instruction, statement or expression cannot be

executed or cannot be converted to machine language by the compiler. Common causes of
compiler errors include errors in the syntax, misspelled keywords, incomplete branching or
looping structures, undeclared variables, and inadmissible use of mathematical operators and
functions among others. For example, consider the following error messages for compiler errors
resulting from a missing Next statement for a For-loop, and an incomplete If statement,
respectively.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 49 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The error causes the program to go into break mode.

Clicking on the OK button sends the programmer to the procedure in which the compiler error
was detected.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 50 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Clicking on the Help button (from the error message box) opens the Excel Help which provides
more detailed information on the nature of the error, possible solutions, as well as links to user
forums and search engine results on the topic.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 51 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Once the offending code has been resolved – in this case removing the comment sign in front of
the last Next, the programmer may click on Run/ Continue to resume from the current cursor
location (which can be adjusted and must be chosen appropriately), or select Reset, in which case
the program will have to be restarted from the beginning.

Run/ Continue Reset

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 52 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Before running a program the code may be checked for compiler errors by clicking on the Debug
menu, and selecting Compile VBAProject. The code is compiled by the compiler (in other
words it is converted to machine language (that the computer understands) without actually
running the program. If the code cannot be compiled for any reason, an error message opens.
From the error message the programmer can select to be taken to the region of the code where
the error occurred, or go through the error message’s help button to research additional
information on the nature of the error, probable causes, and possible solutions.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 53 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

3.2 Run-time Errors

Once a program has been successfully compiled and execution proceeds, it may happen that
some value is processed or some resource is accessed in a manner that is inadmissible to the
programming language. This will cause the program execution to terminate and abort with a run-
time error.

For example, dividing some value or variable by zero will cause a run-time error as the value is
mathematically indeterminate.

Note that in VBA each run-time error has a number identifier and a description in the main
content area of the error message box. A comprehensive list of all the run-time errors built into
the VBA language, and their descriptions can be found on the Microsoft website. (Microsoft,
2014a).

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 54 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The error causes the program to go into break mode.

Clicking on the Debug button sends the programmer to the procedure in which the error
occurred, and highlights the line of code on which the program was aborted.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 55 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Once the offending code has been resolved, the programmer may click on Run/ Continue to
resume from the current cursor location (which can be adjusted and must be chosen
appropriately), or select Reset, in which case the program will have to be restarted from the
beginning manually.

Run/ Continue

Reset

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 56 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Clicking on the Help button (from the error message box) opens the Excel Help which provides
more detailed information on the nature of the error, examples, possible solutions, as well as
hyperlinks and links to user forums and search engine results on the topic.

Clicking on the End button (from the error message box) stops the program execution altogether
and sends the program to design time.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 57 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

3.3 Logic Errors

Logic errors, also called data entry errors, and commonly called bugs, occur when the program
runs “normally” but produces unexpected or undesirable results. In other words upon review, the
programmer knows that the results are incorrect, but from the point of view of the compiler, the
code is fine. Logic errors also exist if the program behaves erratically, for example results are
saved to a spreadsheet that was not the intent of the programmer.

Due to the fact that the program will be running “normally”, there will be no compiler or run-
time errors for logic errors. This makes logic errors difficult to identify. The programmer must
have some domain knowledge on the underlying theories and mathematical models being
implemented in the program. The program must be tested repeatedly and the results thoroughly
scrutinized and verified/ validated.

Some common causes of logic errors include

 omission of relevant code

 calling the wrong variables or functions
 declaring the wrong data type for a variable
 incorrect choice of branching and looping structures
 incorrect variables and/ or logic in conditional statements
 incorrect (loop) variables in loops
 incorrect sequence of instructions
 incorrect array dimensioning

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 58 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

3.4 On Error Statements

During run-time, if an error occurs, the applicable built-in run-time error message box will
appear. Ordinarily, an end user will not know what the error message means or what steps should
be taken to address it. As a result it is considered unacceptable programming practice for built-in
error messages to open up to an end user. It is therefore the programmer’s responsibility to
anticipate potential errors that may occur and add code that will address them in such a manner
that the built-in error messages do not open to an end user. This is the basis of error handling.
Errors that are anticipated and addressed such that the built-in error message box does not appear
to the user are referred to as handled errors, otherwise they are referred to as unhandled
errors.

The On Error statement is the programming feature in VBA for error handling. It has three
forms, namely

 On Error Goto 0
 On Error Resume Next
 On Error Goto <label>:

3.5 On Error Goto 0

In this mode VBA will display the applicable built-in standard run-time error message box and
allow the user to enter the remedial code in break mode or to terminate the VBA program. It is
technically the same as having no error handler. This is the default in VBA

Example: Consider a contractor compiling unit costs of work items on a project for the actual
work done and the actual expenses incurred. The contractor would like to compare these with the
budgeted unit prices used to prepare the bid for the job, in order to evaluate the current financial
position.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 59 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The Actual Unit Cost for each item will be calculated by dividing the Quantity of work done by
the Actual Cost for that item.

A command button with a For loop will be used to iterate down the list. The profit or loss for the
work done for that item is calculated by multiplying the difference of the Budgeted Unit Cost and
the Actual Unit Cost by the Quantity of work done for that item. Note that no pipe work was
done and therefore that item has a quantity of zero which will cause an error when it divides the
actual cost for pipe work.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 60 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The On Error Goto 0 statement is the same as having no error handler at all. On executing the
program, therefore, the standard run-time error Number 6 pops up, and the program goes into
break mode to enable the user correct the code and re-run the program or terminate altogether.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 61 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Obviously, this is not desirable from the point of view of a non-expert end user. The programmer
needs to add code to handle this error, knowing ahead of time that if any work item is not done
and has Quantity of zero, this error will continually occur.

3.6 On Error Resume Next

This error handler causes the program to ignore the built-in error message and resume execution
of the code on the next line. Following the sequence of code execution in a procedure in VBA -
from top to bottom, the error handler code must be placed towards the top of the procedure, or
above the code where the programmer thinks may be prone to some error, such that any code
below it that triggers an error message will then be subject to the pre-existing error handler.

It is important to note however that this error handler simply ignores the error and continues code
execution on the next line and does not resolve the problem generating the error whatsoever.

Example:
Continuing from the contractor’s unit pricing analysis in the previous section, add an On Error
Resume Next handler in anticipation of errors resulting from the Actual Unit Price calculation,
particularly the case where division by zero may occur.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 62 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 63 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

No error message is displayed for the division by zero as it has been handled by the On Error
Resume Next handler. Notice that for the item where this error occurred, no new output values
were calculated. The program simply ignored the error and went to the next line to continue. The
values calculated from the previous item were brought over and displayed per the code for the
offending line item. The error handler did not attempt to address the problem, or adjust the
values displayed for that line item. This may be acceptable in some cases, however in most cases
the programmer may want to not only handle the error, but also attempt to fix the problem or
adjust the relevant output values to reflect the situation that, as in this case, output values were
not calculated for that line item.

3.7 On Error Goto < label >:

In this error handler, once the error is handled, execution “jumps” to after the line listed in the
label. The code between the error location and the label is ignored.

As with the On Error Goto 0, and On Error Goto Next, the error is not fixed, it is simply
bypassed.

Example:
Consider the contractor’s Actual Unit Price calculations for a project as reviewed in the previous
section. Add the line label called ErrHandler. In other words if an error occurs after the On
Error Goto <label>, the program will look for the line labeled ErrHandler and continue

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 64 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

executing the code after it. Note the syntax used in the line label. Code is added such that if or
when an error occurs in the calculations as a result of inadmissible data, the user is alerted with a
message box and any further calculations are stopped. In other words the program is terminated.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 65 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Note that if the handler label was placed inside the For-loop, execution would have continued for
the other entries down the table, and only the line(s) causing the error would be skipped.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 66 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

3.8 Error Handling Blocks

When on On Error statement is executed, the error handler is said to be enabled.

When an error occurs and execution is transferred to another location by way of the On Error
Goto <label>: statement, the code that executes is called an active error handler.

The section of code to which execution is transferred through the On Error Goto <label>:
statement is called an error handling block. The code of the error handling block may be
designed to fix the problem causing the error and resume “normal” execution, or to terminate the
procedure.

3.9 The Resume Statement

The Resume statement causes a program to resume execution at a specific line. The Resume
statement can only be used in an error handling block. Apart from exiting a procedure, the
Resume statement is the only way to exit out of an error handling block. There are three syntactic
forms of the Resume statement, namely,

 Resume
 Resume Next
 Resume <label>

Resume causes execution to resume on the line where the error was detected. The error handling
block associated with the Resume must therefore fix the problem that caused the error to begin
with.

Resume Next will cause execution to continue on the line immediately following the line of code
where the error occurred.

Resume <label> causes code execution to resume at a specified labeled location. Therefore this
format enables some section of code to be skipped if an error occurs.

Example:
Add the following error handling block and Resume statement to the code for the calculation of
Actual Unit Cost in the previous section.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 67 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 68 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Note that when an error occurs in the Actual Unit Cost calculation on any line, execution is
transferred to the line after the label ErrHandler, the error handling block. Here text entries
“input error” and “cannot compute” are assigned to spreadsheet cells which would have
otherwise displayed the Actual Unit Cost and the Profit/Loss respectively. The Resume statement
with label pResumeHere then exits the program out of the error handling block and back into the
For-loop so that the calculations proceed for the next line item in the list, and down the table.

In an error occurs
after this line

Go to here to
continue execution

Once error handling is

completed, Go to
pResumeHere to continue

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 69 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The results are therefore as follows.

3.10 Caution on Error Handlers

Error handling techniques are often used to skip or jump over problematic code, and code which
the programmer can conclude is not meeting expectations, but may be finding difficulty in
debugging. Such uses are considered misuse and abuse of the error handling methods. The
programmer must identify and fix the bugs, and test the code to confirm that it is working as
planned. It is recommended that potential situations that may cause errors should be thoroughly
researched and if possible, accommodated for in the code. If this is not possible, then error
handlers should be used in anticipation of such potential problems.

3.11 Seventh VBA Project

Problem Statement
In this project the project calculator of the Fifth VBA Project will be developed further,
incorporating array methods in the calculations as well as error handling to deal with unforeseen
cases and inadmissible inputs.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 70 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Solution
The framework for the application is as follows.

Review the code and identify all the functions that were created and the function calls that were
embedded in the other procedures. Select and delete all functions and function calls embedded in
other procedures.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 71 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

For instance, the function clcSubTotal1,

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 72 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

‘and its calls within the UnitPrice1 and Quantity1 change procedures.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 73 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Repeat for functions clcSubTotal2, clcSubTotal3, clcTotal.

Save the workbook.

Click on Debug. Select Compile VBAProject.

If there no errors, particularly errors referring to undefined variables and unknown functions,
then all functions have been deleted. If not review the code again and remove any functions and
function calls.

Resave the workbook.

Open the design window.

Rename the Calculate and Print buttons CommandButton5 and CommandButton6 respectively.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 74 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Declare the following global arrays

 A vector to hold the Item values

 A matrix to hold the Unit Prices, the Quantities, and the Subtotals.
 A vector to hold the Total, Contingency, and the Grand Total.

Global variables or in this case global arrays are declared outside of any procedure, but must be
assigned values from within a procedure. Unlike local variables that “die” once a procedure has
executed to completion, global variables are still “alive” as long as the module is active. In this
case the array values shall be printed from the application window “long” after the procedures
that generated their values have run to completion and “died”.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 75 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Double click on the Calculate button to open its click procedure.

Assign the Item values element by element.

Assign Unit Prices, Quantities, and SubTotals values to the matrix, element by element.
The currency conversion function is used to force the text values to currency type before
assigning to the matrix element. For convenience the Quantities are currency type but integer

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 76 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

type or double type may have been used. In that case the Quantities may have been declared in a
separate matrix of the selected data type.
The SubTotals are the product of Unit Price and Quantity.
The Total is the sum of the SubTotals. The Contingency is 15% of the Total in dollars. The
Grand Total is the sum of the Total and the Contingency.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 77 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Continue the CommandButton5 procedure by adding code to display the results in the form
controls.

Save the workbook.

Click on Debug. Select Compile VBAProject.
Correct any errors.
Resave.
If there are no errors, test the application.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 78 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Open the application.

Enter items, and quantities.
Click on Calculate.

The test is a success.

The original version of this application used twelve variables to enable the calculations. This
current version, by taking advantage of arrays uses only three to achieve the same result. This
version, therefore, is more efficient and has less impact on the computer’s resources.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 79 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The next step is to add error handling code. Assume a user inadvertently entered a non-numeric
value in for a quantity. The following will occur.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 80 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Add the following error handler into the command button procedure.

This means that whenever an error of any kind occurs, it will be ignored and the code will
resume execution on the next line.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 81 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Test the application.

The error is handled and the procedure continues to completion yielding a valid result that omits
the offending line from the calculations.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 82 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Alternately, the On Error Goto <label> can be used to alert the user and prematurely stop the
procedure. In this case the On Error Goto <label> will be used in conjunction with a Resume
statement. The Resume label will be placed such that the after the offending line of code sends
the execution to the error handling block for fixing or bypassing the problem, the user is alerted
and the program prematurely terminates execution.

At the top of the procedure enter the On Error Goto <label> call.

The error handling block associated with the line label myHandlerLine shall be inserted at an
appropriate location such that the program will “jump” over any block of code the programmer
thinks may cause an error. In this case the intent is to display a message box and prematurely
stop execution after the bad data is detected. Therefore the error handling block will be placed
towards the bottom of the procedure.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 83 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Add the error handling block. Note the “Exit Sub” line. Without this, the error handler will fire
regardless of whether an error is picked up or not. It is analogous to a wall blocking off the error
handling block, and can only be “jumped” over when an error is picked up and execution is
transferred to after the label line.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 84 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The Resume label is placed such that the totals will be calculated for the “good data” up until an
error is picked up and handled.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 85 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Test the application.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 86 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The error handler activates and displays the message box.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 87 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Dismiss the message box. The calculations for the “good data” are executed and displayed,
whereas that of the bad data is skipped. Upon picking up an error, the message box is displayed
after which the program prematurely terminates, as designed.

The test is a success.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 88 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Double click on the Print button.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 89 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

On clicking this button, the data will be saved to a spreadsheet. Since the global variables are
still “alive”, they can be called to display their values on the spreadsheet as an alternative to
calling the form controls’ values. In terms of the amount of code needed, this is a more efficient
method.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 90 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Save the workbook.

Click on Debug. Select Compile VBAProject.
Correct any errors.
Resave.
If there are no errors, test the application.

Open the application.

Enter items, and quantities.
Click on Calculate.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 91 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Press Print
Confirm the values are correct and saved to the cells specified in the code.

Press Exit to close out of the application.

The test is a success.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 92 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

4. GRAPHS

4.1 Chart Objects

Graphs can be created by using the VBA Chart object. Examples of charts and graphs that can be
created in Excel-VBA include scatterplot, pie chart, bar chart, area graph, and their 3-
dimensional counterparts among others. A comprehensive list of chart types supported by Excel-
VBA can be found on the Microsoft website. (Microsoft, 2014b).

4.2 Creating Graphs

A general procedure for creating graphs involves the following

Step 1: Create the chart object. The syntax is

Charts.Add

Step 2: Specify where the chart shall be displayed. To display on a spreadsheet, for example, the
syntax is of the form

ActiveChart.Location Where : = xlLocationAsObject, Name : = “nameofspreadsheet”

Step 3: By default Excel will plot any pre-existing series of data on the spreadsheet on the chart.
Delete any such series plotted on the chart. A loop can be utilized to remove the data series by
series. The syntax will be of the form

Do Until ActiveChart.SeriesCollection.Count = 0

ActiveChart.SeriesCollection(1).Delete

Loop

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 93 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Step 4: Add new series to the chart and assign data to it. Obtaining the data from a spreadsheet,
for example, will be of the form

With ActiveChart.SeriesCollection.NewSeries

‘give the series a name

.Name = ActiveSheet.Range (rangeofcells)

‘assign “y” values

.Values = ActiveSheet.Range (rangeofcells)

‘assign “x’ values

.XValues = ActiveSheets.Range (rangeofcells)

‘assign other properties e.g. title, gridlines etc

:
:

‘if multiple series of data are being plotted on the graph, add
‘name, y-values, x-values for each series, as needed.
:
:
:

End With

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 94 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

Example:
Consider the following data. For Y2 through Y6, plot each Y versus X to create one graph with
multiple series of data plotted on it.

Add the following code under a command button click procedure

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 95 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 96 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

The code results in the following.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 97 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

5. CONCLUSION

This course has presented a broad overview of fundamental concepts and principles of computer
programming, and presented them in situations encountered by practicing engineers and
scientists. All codes were developed using the Visual Basic for Applications (VBA)
programming language.

In this course the topics, error handling, arrays, dialog boxes, and graphs were presented. Several
examples relevant to engineering were used to illustrate and demonstrate the concepts and
methods learned in this class. A mini-project was used to demonstrate the programming concepts
and methods in a situation encountered by practicing engineers.

This course has enabled participants to identify situations where programming is relevant and
will be of advantage to the professional. Practitioners are strongly encouraged to look out for
situations in their domains of expertise where programming solutions are applicable and will be
of benefit to their work and their organization.

Computer programming requires a careful and meticulous approach, and can only be mastered
and retained by practice and repetition.

Good Luck and Happy Programming.

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 98 of 99

 Programming MS Excel in Visual Basic (VBA)
A SunCam online continuing education course

REFERENCES

Bradley, J. C., & Millspaugh, A. C. (1999). Programming in Visual Basic 6.0. Irwin McGraw-
Hill.

FunctionX Inc. (2013). VBA for Microsoft Office Excel 2007. Retrieved December 21, 2013,
from FunctionX Tutorials: www.functionx.com/

Microsoft. (2013). Excel 2013 developer reference. Retrieved October 15, 2013, from Office
Dev Center: http://msdn.microsoft.com/en-us/library/office/ee861528.aspx

Microsoft. (2014a). Error Trapping with Visual Basic for Applications. Retrieved January 11,
2014, from http://support.microsoft.com/kb/146864

Microsoft. (2014b). XlChartType Enumeration (Excel). Retrieved January 15, 2014, from Office
Dev Center: http://msdn.microsoft.com/en-us/library/office/ff838409.aspx

Wise Owl Business Solutions Ltd. (2013). Using FileDialogs in VBA. Retrieved January 3, 2014,
from Wise Owl: http://www.wiseowl.co.uk/blog/s209/type-filedialog.htm

Images were all drawn/ prepared by K. Ofosu

www.SunCam.com Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE Page 99 of 99