Effective VBA

I love VBA. VBA, if you've never heard the term before, stands for Visual Basic for Applications.

What that means, simply is that it's a programming language - a way to automate processes in Microsoft Office, and make your existing processes go faster.

Before I go any further, though, in talking about when to use VBA, I want to talk about when not to use VBA:

1. To make a program act like another. For example, if I'm using Excel VBA to automatically add a number to a row, or to require data in a field, or to aggregate data from a form, I might as well use Access.
2. To replicate existing functionality of a program. For example, if Excel already has a button/command to do Paste Special->Values, then there's no need for me to make a macro to do that. (Oops, too late, already did.) But there's a case where if you go to Tools->Customize in your program (like Excel), then you might find that the functionality you want already exists. In the case of Paste Special, Values, the button did exist; I just had to go to Tools->Customize, and look under "Edit," and there it was.

Now, having said both of those things, let me offer a disclaimer: As far as #1 goes, I've given people advice on the Excel Community forum at Microsoft's web site on how to automatically generate a new invoice number every time a person opens a workbook template. In many cases, Access can do it better, but there's no one at the office who can support Access, and/or there's a license issue. OK, that makes VBA the best choice. Fair enough.

I digress. When to use VBA is also two part:

1. To make one step out of six or seven steps. For instance if I want to do three or four steps over and over again, I would record those steps in a macro. As anyone who has ever taken Excel Level 3 with me will tell you, I usually have people record the steps of giving a cell a blue background, white text, center aligned, Arial font, size 9. (I sometimes mix it up how many of those steps I have people do.) In this case, all you do is just have the program (in this case, Excel) follow you through the steps, so that next time, all you'll have to do to replicate them is to run the macro.
2. To build a better mousetrap. This is where you not only record the steps, but then add some conditional logic and/or control constructs to it. The conditional logic part is to say, for example, "if the workbook is blank, don't do anything to it," while a control construct might be, "execute this code once for every worksheet in the book" kind of thing.

Frankly, I do most of my VBA in Excel, because it's what I use the most. I've done VBA in Word, but I'm still having a hell of a time learning the object model. The object model means how the entities are related; in Excel, for example, the Workbook is an object, which contains Worksheets, and those contain cells, which can be selected. Word isn't that consistent.

One can do an extensive amount of VBA in Access. I have barely scratched the surface of its power. Remember, macros in Access are different than macros in Excel. A macro in Access means assembling steps using the Macro editor. To do VBA in Access means creating what's called a module. Either way, you can't have Access record the steps. In Excel, Word, or PowerPoint, you can have the program record your steps and play them back later.

Back to the example I brought up earlier about automating the process of changing the background color, font, and stuff like that. I had my class the other day go into a certain file called "OfficeSupplies" (which can be downloaded from the New Horizons website, by clicking this link, which is to a Zip file. What I have the class do is start with the "Australian" worksheet, and then record the steps of various formatting, like currency formatting the dollars, bolding the title, and stuff like that. The macro that I have the folks create initally looks like this:

Sub formatWorksheetLongWay()
' formatWorksheetLongWay Macro
' Macro recorded 6/20/2005 by Geoff Lilley
Range("A1:E1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection.Interior
.ColorIndex = 5
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("A3:A7,B3:E3").Select
Range("B3").Activate
Selection.Font.Bold = True
Range("B4:E9").Select
Selection.Style = "Currency"
Range("B9:E9").Select
Selection.Font.Bold = True
End Sub

This code suffers from two problems. Number one, I have to manually apply the formatting to each individual worksheet, in turn. Two, it's ridiculously bloated, which is going to slow down my processing time long-term.

I re-wrote the code to be more efficient; this code loops through all the books, and then does all the same formatting to each one, except "Chart1."

Sub ColorRegions()
'variable to figure out what sheet number we're on
Dim SheetNum As Byte
'variable to figure out how many sheets are in the book
Dim SheetsCount As Byte
'move the chart worksheet to the end
Sheets("Chart1").Select
Sheets("Chart1").Move After:=Sheets(ActiveWorkbook.Sheets.Count)
'start with the first sheet
SheetNum = 1
'do this stuff to every sheet but "Chart1" by looping through the sheets
SheetsCount = ActiveWorkbook.Sheets.Count
Do Until SheetNum = SheetsCount
'select each sheet in turn, starting with the first
ActiveWorkbook.Sheets(SheetNum).Select
'take the title, make it bold and centered, white text, blue background
Range("A1:E1").Select
With Selection
.Interior.ColorIndex = 5
.HorizontalAlignment = xlCenter
.MergeCells = True
.Font.Name = "Arial"
.Font.Size = 14
.Font.ColorIndex = 2
.Font.Bold = True
End With
'bold the titles on the left and top
Range("A3:A7", "B3:E3").Select
Selection.Font.Bold = True
Range("B4:E7", "B9:E9").Select
'currency format the numeric cells, including the totals
Selection.Style = "Currency"
'go to the next sheet
SheetNum = SheetNum + 1
Loop
End Sub

In essence, I just cut down the code to the necessary pieces, and then wrapped the whole thing in a "Do" loop. The loop just says, "do this stuff once to each sheet in the book, until you get to the end."

This is an example of VBA for quick and dirty fixes. I highly recommend it as a solution. If you live in the Sacramento area, and you want to learn more about Excel and macros, call the New Horizons main line at (916) 641-8500. As it stands right now, we have to generate enough interest from people to get a full VBA class together. If we get the interest, either me or one of my colleagues can teach it. But we need the demand first. So let people know it can be done!