How I learnt the fundamentals of programming through Microsoft Office VBA – part 1

As I have “IT professional” in my tagline, it’s probably time to write something IT-related. Fear not, this is about as simple as you can get and still call it “IT”.  Intended audience: this is basic programming for those who use Microsoft Office but know nothing about programming. If that’s not you, feel free to skip on to another post.

I never sought to become an IT professional. I trained in science and information management; but along the way I also learned a bit of Basic, and Visual Basic, and HTML and Javascript and CSS, and became an expert at programming macros in Microsoft Office. At every job, the technical tasks landed on my desk; every backup routine, every database adjustment, every hardware failure. My story is hardly unique. It seems amusing that in a world swimming with ICT graduates who can’t find work, many organisations end up giving the IT responsibility to someone who never asked for it or trained for it.

One of the skillsets I developed, both because it was useful and because there was no other way to achieve what I wanted to do, was programming in Microsoft Office VBA. What started as simple recorded macros for efficiency grew into more complex routines, and then grew again, adding complexity and flexibility along the way. We’re not talking about recording little macros here, oh no. A representative program which combines operations in Microsoft Excel, Word and PowerPoint comes to about about 5000 lines of code. VBA is a startlingly simple scripting language to control the Office suite, but in order to build complex tools that are capable of dealing with multiple sets of data and high variability, it is necessary to build a lot of complex error checking and conditional mechanisms.

I recently came across a definition of six fundamental concepts of programming. Whilst it’s certainly possible to build a simple program without using all of these concepts, particularly in Excel where the most common kind of “macro” is probably a simple formatting of a cell, these methods extend the possibilities and functionality that VBA can offer. Although I now program mostly in ASP.NET and PHP, I still do the occasional bit of VBA, because Microsoft Office with VBA automation can be a hundred times more efficient than doing everything by hand. But the fundamental methods and approaches I taught myself by trial and error working in VBA are universal and vital for success, in whatever language you are working. Once you have the fundamentals in place, everything else, regardless of the language you’re working in, is syntax.

Algorithms and Errors

The first and most critical part of programming, of course, is breaking the tasks down into the actual flow of information, input, actions and outputs are going to be required. Real programmers do this either using flowcharts or pseudocode – instead of writing the programming code, you start by writing the steps that the program will go through to fulfil the required actions. Any complex VBA program can benefit from a bit of pseudocode. The pseudocode gets written inside the VBA editor, and the programmer can use it to break the program into manageable chunks of programming. And the best ancillary benefit? When the code is complete, the pseudocode forms the basis of your code comment descriptions so someone else has a hope of understanding what the hell the program is supposed to be doing!

In VBA, as many other languages, code comments are marked with an apostrophe. Your comments can be as detailed or brief as you wish, but they should describe all the most pertinent points and actions.

'Find the cell that has the client name and capture the data

'Create a new sheet

'Paste the client name data

Error catching in VBA is slightly less sophisticated than in most real programming languages. When building VBA code, I try to avoid errors ever happening by anticipating and specifically coding for every possible combination of input and conditions, but the more complicated a program is, the less this is likely to be foolproof. Error handling code should be included even if you don’t anticipate there ever being errors.

Error handling in VBA basically entails two approaches. The most usual method is to write a bit of code to run whenever an error occurs. You specifically set the program into a state that knows what to do when an error occurs.

Function tryme()

On error goto errhandler

...Code here for the program where an error might occur

On error goto 0


...Code here which deals with the error - e.g. alert the user to the error, the line it occurred on, and the person to contact for a fix.

End Function

The “goto 0” line above turns off the error checking routine; any errors after this point are not handled (if there is no other error handling routine set).

The other method of handling errors is to simply make sure they are ignored:

On Error Resume Next

This statement tells the program to jump to the next step and keep running. It’s generally a bad idea because having a program continue to run after it has failed to do something expected can have all sorts of outcomes.

Variables and Conditionals

A variable is a codeword that is used to hold a value or a reference to something. In Excel, a variable might hold a reference to a cell, or it might hold a number or a piece of text. Variables allow you to perform actions in memory without actually altering the underlying data – until you’re ready to. For instance, the following code specifies a variable, captures a value, alters the value and writes the new value into the next cell.

Dim myNumber As Integer
myNumber = Range("A1").Value
myNumber = myNumber + 1
Range("A2").Value = myNumber

In Excel, getting stuff into your variables generally entails either referencing specific cells on a worksheet (either by cell location – A1 – or in relation to the cell which is currently “active”) or by asking for user input. In the above code, we’re specifically referencing cells A1 and A2.

Most programming languages treat their variables like Ikea flatpacks. You have to list the exact components before you begin assembly, and each component has to be exactly the right size and format. If your screw is half a centimetre too short, the desk just isn’t going to come together. VBA, on the other hand, is not so fussy. In VBA variables are more like ingredients in a recipe; if you want to swap a potato for a pumpkin or use a teaspoon of salt rather than a tablespoon, go right ahead. It’s not going to fundamentally break the program. This flexibility is both a blessing and a curse.

Even though VBA doesn’t require you to specify your variables up front or to declare what they should hold, good practice says that you should create your variables at the start of a program (“Dim myNumber”) and specify what kind of content they will hold (“As Integer”). VBA is very forgiving and will always try to guess what you’re trying to achieve, but mixing variable types can lead to problems, and specifying what each variable should be will give you much more descriptive errors. In the code above, if cell A1 holds the text “Hello world!”, then trying to set the variable myNumber will give rise to an error.

Any programming is likely to also require conditionals. The more complicated the code, the more conditionals are going to be required. Conditionals check a value or a condition, and do one thing for one set of conditions, and something else for another set of conditions. In VBA, there are two main kinds of conditionals: the If – Then structure, and the Select Case structure.

If/Then is exactly what it sounds like. VBA allows you to go a little more complex by including “ElseIf” and “Else”. The basic structure is as follows:

If myValue = "1" then something
elseif myValue = "2"  or myValue = "3" then something else
else a different thing
end if

This code is processed in a linear fashion, one step after another, and once one condition is met, the others are ignored.

The Select Case form is similar but different.

Select case myValue
Case "1" something
Case "2", "3" something else
Case else a different thing
End select

Again, each case is mutually exclusive, and each is processed in order.

Select Case and If/Then/Else are virtually identical in how they operate, and the programmer can feel free to use whichever one results in cleaner code.

To finish this post, let’s put all these concepts together into a very basic program.

Sub HelloWorld()
'Set up my variables
Dim myText1 As String
Dim myText2 As String
Dim myCell As Range
'Set error catching
On Error GoTo CatchErrors:
myText1 = "Hello"
myText2 = "World"
Set myCell = Range("A1")
If myCell.Value = "" Then
myCell.Value = myText1 & " " & myText2 & "!"
myCell.Value = myCell.Value + " World!"
End If

Exit Sub
'Error handling routine
MsgBox ("You encountered an error!")
End Sub

From here on, things start getting more technical. Part 2 of this article will discuss loops and arrays. Part 3 will talk about functions, subroutines and objects, as well as interactions between different packages in MS Office. That’s where the fun stuff really happens!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s