by Alan German

Microsoft 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 = Range("A1").Value]

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

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.

Originally published: November, 2015

top of page