Introduction to VBA

If there’s a truism to modern life it’s this: if something is good for you then it’s probably no fun at all. No fun at all.

Need an example? Take cod liver oil (please). According to the Weston A. Price Foundation, “There is hardly a disease in the books that does not respond well to treatment that includes cod liver oil, and not just infectious diseases but also chronic modern diseases like heart disease and cancer …. 'If you want to prevent learning disabilities in your children,' said David Horrobin, distinguished medical and biochemical researcher, 'feed them cod liver oil.'"

That’s all well and good; after all, nobody wants to get heart disease or cancer. But just how much fun is cod liver oil? Let’s do what everyone else does these days and let Wikipedia answer the question for us, “Depending on the quality of the oil, the flavor and aroma range from a mild sardine-like flavor, to an intense and obnoxious odor of rotten fish and rancid oil.”

‘nuff said.

Full disclosure. We must admit that neither of the authors of this article has ever tried cod liver oil. On the other hand, cod liver oil is a “pale, yellow oil derived from the liver of the cod fish” and, as noted above, can “have the intense and obnoxious odor of rotten fish and rancid oil.” Do you even need to taste it to know that it’s not much fun?

Or take exercising. Everyone knows that regular exercise prolongs your life … provided that it doesn’t kill you, of course. What do we mean by that? Well, in a study of people who walked a moderate 14 miles a week, it was discovered that 21% had to stop exercising for a week or more due to injury. In another study, conducted over a three-month period, 49% of the aerobic dancers surveyed reported an exercise-related injury, and fully 25% of those dancers had to partly or completely quit the program due to injury.

Hmmm, injuring yourself so badly you can no longer walk or participate in your aerobics class? Well, we suppose that could be fun. Still ….

And what about Visual Basic for Applications (VBA)? As you may or may not know, VBA is a scripting/development language built into most of the Microsoft Office applications. Even if you didn’t know that, there’s still a good chance you’ve used VBA. For example, suppose at some point in your word processing life you selected some text and then used Microsoft Office Word’s macro recording capability to keep track of the following activities:

·         Color the selected text red.

·         Change the font to Arial.

·         Change the font size to 24 point.

·         Set the font style to strikethrough.

Note      Yes, that does make for some odd formatting, doesn’t it? But, then again, beauty is in the eye of the beholder.

So what happens the next time you have a burning desire to format text in 24-point, red, Arial, strikethrough? You got it: instead of manually going through all those steps you can just run your macro.

Of course we didn’t really need to tell you that, did we? You probably already knew what the macro recorder does. However, what you might not have known is  that, under the covers, Word was recording your macro as a set of VBA commands. In fact, assuming we named our macro Test, Word recorded the macro as  a set of VBA commands similar to these (we simplified the commands a little bit; unlike the authors of this article, the macro recorder sometimes does way more work than it needs to):

Sub Test()

    Selection.Font.Color = wdColorRed

    Selection.Font.Name = "Arial"

    Selection.Font.Size = 24

    Selection.Font.StrikeThrough = True

End Sub

 

Instead of using the macro recorder you could have typed in these commands. (Where could you have typed them in? In the Visual Basic Editor. Where and what is that? We’ll explain that a little later.) Either way, you’d end up with text formatted as 24-point, red, Arial, strikethrough.

But that’s a good point: if the macro recorder provides the same net effect as typing in all these VBA commands, well, why would you type in all these VBA commands? Does this mean that VBA is just a big waste of time?

To be honest, in some cases it might be. After all, if all you want to do is format selected text then writing a VBA subroutine might be overkill. But suppose that – for some unknown reason – you wanted to format this text only if the company name Fabrikam or the company name Contoso was selected. That’s a difficult action to record; in fact, as near as we can tell, it’s an impossible action to record. But it’s easy enough to add some intelligence to our VBA subroutine. Here’s a modified chunk of code that formats the text only if you’ve selected the name Fabrikam or the name Contoso:

Sub Test()

    If Selection.Text = "Fabrikam" or Selection.Text = "Contoso” Then

        Selection.Font.Color = wdColorRed

        Selection.Font.Name = "Arial"

        Selection.Font.Size = 24

        Selection.Font.StrikeThrough = True

    End If

End Sub

 

Could we get even fancier here? You bet we could: we could change the lowercase letters to uppercase; we could color the text red only if the text is currently colored blue; we could double the size of the font, regardless of its current size; and so on. We’re not sure how practical some of those actions might be, but that’s not really the point. The point is that VBA lets you do a lot of things that you simply can’t do through the user interface.

In other words, VBA is good for you. Very good for you.

Of course, we know what you’re thinking. You’re thinking, “Wait a second. The theme of this article is that things that are good for you are no fun. That must mean that, as useful as VBA might be, it must be really hard to learn, and to use. In other words, VBA must be no fun at all.”

See? We told you we knew what you were thinking.

Note      Per Microsoft’s legal department, we would like to announce that we don’t know what you’re thinking. Contrary to the long-standing urban legend, Microsoft’s software does not read your mind and transmit your thoughts back to company headquarters. As long as we’re debunking urban legends, we should also note that Microsoft and AOL will not pay you $5 each time you forward a specified email to another person; Microsoft support did not wait 10 years to return a customer’s call; and the Windows Update service does not run on a Linux computer.

It actually runs on a Nintendo Wii.

Note      Per Microsoft’s legal department: no, it doesn’t.

Well, the truth is, VBA really is easy and can even be fun (as those of you joining us for OfficePalooza will discover). So even though some of you are concerned that, while VBA might be very cool and very powerful, it might also be a bit too hard, and it might not be all that much fun, with any luck OfficePalooza should squelch any of those concerns. As for VBA being too hard, well, let’s show you just how “hard” VBA truly is.

Suppose you have a simple two-column Microsoft Office Excel spreadsheet. In column 1, you type a person’s name; in column 2, you type some sort of value (sales figure, test score – it doesn’t matter). Periodically, you find yourself stopping to select all that data and then sort it by the person’s name. That’s fine. It’s a bit of a hassle, but what else are you going to do: have Excel automatically sort your spreadsheet each time you enter a new person and a new value?

Funny you should mention that:

Sub Worksheet_Change(ByVal ObjTarget As Range)

    If ObjTarget.Column = 2 Then

        Set objRange = Application.Range("A1")

        Set objRange2 = ActiveSheet.UsedRange

        objRange2.Sort objRange, xlAscending, , , , , , xlYes

    End If

End Sub

 

Believe it or not, that’s all the code required to create a worksheet that will automatically sort itself each time you type a new value (or change an existing value) in column B. And that’s kind of cool. It might (or might not) be something you’ve been just dying to have but, again, it shows how you can easily add a feature to a Microsoft Office product.

And no, this isn’t some kind of a scam; that really is all the code you need to create a self-sorting worksheet. This would only be a scam if we told you that the former Defense Minister for the country of Nigeria taught us how to create a self-sorting worksheet.

Note      Actually, and by remarkable coincidence, the former Defense Minister for the country of Nigeria did teach us how to create a self-sorting worksheet. But really, this isn’t a scam: you can create a self-sorting spreadsheet just by writing a few simple lines of code. The fact that Microsoft pays us for writing articles that are supposed to be about technical issues but continually veer off into crazy tangents about Nigerian defense ministers? Now that’s a scam!

Maybe it would help if we took a moment to explain how the code actually works. As you can see, we’ve used the Sub keyword to declare a subroutine named Worksheet_Change:

Sub Worksheet_Change(ByVal ObjTarget As Range)

What’s that? You say that “Worksheet_Change” is a silly name for a subroutine? We beg to differ; as it turns out, Worksheet_Change is the absolute perfect name for this subroutine. (Actually, it’s the only name for this subroutine.) One of the cool things about programming in VBA is the fact that you can do event-driven programming. If you’ve written a system administration script in VBScript or Windows PowerShell (as many of you no doubt have), then you know that system administration scripting is typically a straightforward approach: once you start the script that script (in general) executes line 1, then executes line 2, then executes line 3. This process continues until all the lines in the script have been executed, at which time the process automatically comes to a halt.

That’s not really the way that scripts work in VBA, however. Instead, “scripts” tend to be individual subroutines, subroutines that sit idly in the background and wait for a specific event to trigger them. (As you might expect, that’s a somewhat rough approximation of how the process works, but it’s good enough for now.) Often-times this event is the clicking of a button or the selection of a menu item; that is, somebody invokes an item (a button, a list box, a menu choice) that you created specifically for that purpose.

However, many of the objects in Microsoft Office support events all their own. We didn’t choose the name “Worksheet_Change” simply because that was Jean’s nickname in high school. Instead, we chose the name because “Worksheet_Change” is the name of an event supported by the Worksheet object in Excel. As the name implies, this is an event that gets fired off any time a change is made to a given worksheet. (Or, to be more precise, any time a cell on that worksheet is changed in some way.)

Note      So how did we know that Worksheet_Change was one of these supported events? Well, after all, we are highly-skilled, highly-trained professionals who have spent a lifetime preparing to write this article.

Also, we knew about the Microsoft Excel Object Model Reference located at http://msdn.microsoft.com/en-us/library/bb149081.aspx.

In VBA, you can “subscribe” to events (like a change being made on a worksheet) simply by creating a subroutine that has the same name as the event in question. If we want our code to respond to an event named Worksheet_Change then all we have to do is create a subroutine named Worksheet_Change.

Which, come to think of it, is exactly what we did. What if we wanted to subscribe to the event that gets fired each time a worksheet gets recalculated? Then we would have named our subroutine Worksheet_Calculate, like so:

Sub Worksheet_Calculate()

Good question: what about that (ByVal ObjTarget As Range) business? Well, what we’re doing here is passing a variable named ObjTarget to the subroutine. But this isn’t just any variable; instead, it’s a variable that represents an Excel Range object. That’s why we tacked on As Range. If we wanted to pass the subroutine some other type of variable (such as a string variable) then we would have used syntax similar to this: (ByVal ObjTarget As String). As for the ByVal, that simply tells VBA that we want to pass this information “by value;” in effect, that means that we’re passing the subroutine a copy of the variable rather than the actual variable itself.

And why do we do that? Well, we don’t want to get sidetracked too much, so we’ll simply say that this ensures that the value of the underlying Range object will not get changed when the subroutine executes.

Oh, right: and exactly what range are we passing here? That’s easy: it’s the address of the cell that we just changed. (Remember, this event gets fired only when the worksheet is changed.)

Well, that was pretty easy, wasn’t it? And we have good news for you: it doesn’t get much more difficult than that, either. Each time the Worksheet_Change event gets fired the first thing we do is check to see if a value in column 2 has been changed; that’s what this line of code is for:

If ObjTarget.Column = 2 Then

What’s the point of that? Well, we have a two-column spreadsheet: a name gets entered in column 1, and then a value gets entered in column 2. We don’t want the spreadsheet to sort itself if we enter a name in column 1; we only want the spreadsheet to sort itself after a value has been entered in column 2.

Note      We should note that we’re assuming that a name has already been entered in the corresponding cell in column 1. We didn’t bother doing so, but we could have easily added a line of code that verifies that there are values in both columns before we start sorting stuff.

If we have changed a cell in column 2 we then execute these two lines of code:

Set objRange = Application.Range("A1")

Set objRange2 = ActiveSheet.UsedRange

 

In the first line, we’re creating a range object that encompasses cell A1. Why? Well, we want to sort our worksheet on column A. In VBA, the way you indicate the column to sort on is to create a range object consisting of a single cell in that column. Needless to say, if that’s what VBA wants us to do, well, then that’s what we’re going to do.

Note      Should you always do everything that VBA tells you to? In a word: yes. Jean and Greg have devoted their lives to doing everything that VBA tells them to, and look how they turned out.

OK, bad example. But go ahead and create a range object that represents the column that you want to sort on.

After indicating the column we want to sort on, we then used the UsedRange property to select the entire worksheet.

Note      Actually, and to be a little more specific, the UsedRange property selects all the cells on the worksheet that contain data, plus all the cells in between those cells. For example, if you have data in cell A1 and data in cell D9, the UsedRange would include cells A1 and D9, and all the cells in between those two.

Last, but surely not least, we use this line of code to sort the cells in the UsedRange:

objRange2.Sort objRange, xlAscending, , , , , , xlYes

We won’t bother explaining the Sort method in any detail in this article; for that information, take a peek at the Excel VBA Language Reference on MSDN (http://msdn.microsoft.com/en-us/library/bb238871.aspx). For now we’ll simply note that we’re sorting the cells in the UsedRange (objRange2), and passing the Sort method the following parameters:

·         objRange, an object reference to column A, the column we want to sort on. (And yes, we could sort on as many as three different columns, just like in Excel itself.)

·         xlAscending, an Excel constant that causes the data to be sorted in ascending order. (Good guess: the constant xlDescending would cause the data to be sorted on descending order. What are you, psychic or something?)

·         xlYes, another Excel constant, this one indicating that our data has a header row.

Note      Now that you mention it, yes, xlNo would indicate that our data doesn’t have a header row. Man, this is downright spooky!

 So what do you do with all this code? Well, start Excel, and start a new workbook. Right-click the tab for Sheet1 and then click View Code; that will open up the Visual Basic Editor and enable you to add a subroutine for use on Sheet1. (At least that’s what you do in Office 2007. If that doesn’t work in your version of Office, try pressing Alt+F11.) Copy the code from this article and paste it (exactly as-is) into the Visual Basic Editor. Close the editor, then type the header Name in cell A1 and Value in cell B1.

OK. Take a deep breath to recover from your excitement, then type Pilar Ackerman in cell A2 and 50 in cell B2. Now here comes the fun part: type Ken Myer in cell A3. Then type 60 in cell B3, press ENTER, then step back and watch what happens.

We told you it was fun.

Which means, of course, that we were wrong: turns out that it’s possible for something to be good for you and to be a lot of fun. Sorry for any misunderstanding that might have occurred.

Note      Is this the first time anyone from Microsoft has ever been wrong about anything? Yes.

Oh, and just to prove that VBA can be fun, here's an article from TechNet Magazine that shows you how to write a simple little word game in Microsoft Excel.

Note      And just to prove that VBA is also good for you, that same article will show you how to do things like modify the user interface and get Excel to respond to mouse clicks in a particular set of cells.