A Clear View of VBA Programming
by Alan German
Excel uses Visual Basic for Applications (VBA) as its
macro programming language. This is an incredibly
powerful system but isn't all that intuitive nor
exceptionally easy to learn. My technique has been to
create a macro using the built-in macro recorder and then
use Google to search for specific information to help
tweak the resulting product. There is a wealth of forum
postings, on-line tutorials, and YouTube videos on almost
everything one might need to know about VBA. The trick is
to find the specific details on any given issue. What is
really required is a comprehensive textbook.
My first foray into the written word on VBA (Looking a
gift horse in the mouth?, Ottawa PC News, April 2015)
wasn't all that successful. The book I located had some
basic information on using the programming language, but
certainly didn't cover everything that I needed to know.
My next approach was to scan the
catalogue of the Ottawa Public Library for relevant
material. It turns out that lots of people in Ottawa are
seeking the same information. There is a fairly long
waiting list for almost all of the available books on VBA
programming. However, one relatively old text - Master
Visually Excel 2003 VBA Programming by Julia Kelly - was
available in short order and I actually found this to be
a really good resource.
I guess the good news is that many VBA commands and
techniques are still in place after more than ten years
(and any new items must be things that I don't need!)
Readers may have seen other books in the
"visually" series but, if not, as you might
guess from the title, the book is packed with how-to
screenshots. These, together with the basic text, and
many supplementary notes and tips, provide a simple guide
to many of VBA's features.
The book starts with a section on using the macro
recorder, followed by details of how to use the Visual
Basic Editor. Some of the techniques described, such as
stepping through a macro in order to monitor its
progress, were familiar to me, while some of the tips
were on things I had heard about but never used.
I found one such item - the use of a watch point and the
Watch Window - to be very useful in debugging a
particular macro. The code was looping through thousands
of lines of data and checking if specific files existed
on a web server. The problem was that, intermittently,
the server would return "an invalid or unrecognized
response". Because of the lengthy looping process, I
certainly didn't want to step through the entire macro to
check my error recovery routine. My solution was to
establish a dummy variable (x=0) and set this variable to
one (x=1) at the point in the code where the error was
trapped. Then, I simply opened the watch window and added
a watch point such that the macro would break if x=1.
This allowed me to run the macro and step through the
error handler code if and when an error occurred.
One important aspect of the book is a whole section
devoted to ranges - with details of how to refer to
cells, rows, columns and ranges. Interestingly, while
there is a specific page indicating how to enter values
into a range, I couldn't find an equivalent description
of how to read a value from a data cell or a set of
values from a range [Hint: x =
The "Making a Macro Smart" section of the book
includes details on the use of conditions (e.g. If...
Then... Else), conditional loops (e.g. For... Next, Do
While... Loop, Select Case) and error handling. Other
sections in the book cover methods for handling
workbooks, including how to refer to, add, rename, or
hide/unhide worksheets; creating charts and pivot tables;
and the use of forms and controls.
Some individual topics that may interest certain users
include: writing functions, obtaining user input, message
boxes, assigning a macro to a command button,
automatically running a macro when a workbook opens or
closes, and stopping screen flicker (e.g. when a macro
switches between worksheets.) Another item is one feature
that I have always thought lacking in Excel (but, in my
view, the fix isn't worth the effort!) The problem occurs
when running a macro that will take considerable time,
such as cycling through hundreds of lines of data and
processing various elements. It would be useful to pop up
a message telling the user to "Please wait..."
Using a message box would seem to be the obvious
solution, but there is basically no control on the timing
of this feature. In particular, once the user
acknowledges the message (by pressing the Enter key), the
message box is removed as the macro continues its
One alternative is to display a custom message on the
status line, which is persistent, but this location is
fairly unobtrusive. The suggestion in the book is to
create a custom user form and use the form's show/hide
functionality to display and then remove the message as
required. The problem with this approach, as I see it, is
that developing and displaying the user form are both
unnecessarily complex. So, for now at least, I intend to
stick with message boxes at the start and end of the data
processing, and place a message on the status line!
Kelly's book dates back to 2003, but still seems relevant
for the topic. The text covers VBA's features in
considerable detail, with many images showing exactly how
things should be done. Wiley Publishing no longer have a
listing for the title. On the library's web site, the
book currently has an "All copies in use" flag.
So, if you need a good how-to on VBA programming, place
your library reservation for Master Visually Excel 2003
VBA Programming, sit back, and wait patiently.
Master Visually Excel 2003 VBA Programming
Wiley Publishing, Inc.
OPL Call No 005.54.E96K
Originally published: November, 2015
top of page
The opinions expressed in these reviews
do not necessarily represent the views of the
Ottawa PC Users' Group or its members.