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!

Best Way to Make Forms in Office

I get this question a lot. What's the best way to make a form to fill out?

I have several different answers to this question, each with their own relative merits and drawbacks. The guiding question is always, "What do you want to do with the form when the person is done filling it out?"

1. Create the form in Word. If you want to go this route, there are two tools that will serve you well - the Forms toolbar, and tables. Put the label for the data (like the word "name" or the word "address" in the left column of the row, and the form field where you want the person to put the name, or address, or phone number, in the right column of the row.
   
   Use this method if you want to create a quick and dirty form that will be printed out and mailed to you, or emailed to you.
   
   Don't use this method if you're concerned about maximum number of characters in a field, and/or you're worried about populating this information into a central data location later.
2. Create the form in Excel. If you want to go this route, the two tools you'll find most useful are validation and protection. Use Data->Validation to restrict what kind of values a person can put into a cell, like minimum and maximum numeric values, and in-cell drop-down lists, using Data->Validation, with the "allow" property being set to "list."
   
   Use this method if your primary concern is getting valid data within a given threshold. Compared to Word, still pretty quick and dirty, and still perfectly serviceable for printout and/or email.
   
   Don't use this method if you have to populate the data into a central data location later. You can do it, but it's clunky and inelegant. But better than Word in this regard.
3. Create an Access database. This will require first creating the table(s) to hold the data, then the forms to enter the data. Probably, you'll want to implement some kind of security, so that your form users don't see your back-end functionality.
   
   Use this method if you're only dealing with internal clients (people in your office, or people who work for your company in other locations who can access the same server(s) you can.) This method is the best of the Office programs for validating data to ensure its compliance with your standards, and has the best built-in method for making sure that required data gets filled in.
   
   Don't use this method if you have to make your form available to people outside your company.

Other solutions to the problem that I've heard batted around, going outside the traditional confines of Office, also come with ad and disadvantages:

1. InfoPath. This is a new product for Office 2003. It is a very elegant, easy-to-use forms designer.
   
   Use this method if you have a SharePoint website for your company where people can go and fill out your form on the team and/or company SharePoint site. You can also use this method if you already have an Access database created, and you want to make the InfoPath form talk to the Access database.
   
   Don't use this method if you have to publish your form to external parties; their likelihood of having the tools needed is pretty low.
2. Acrobat forms. Acrobat 6 and 7 both have pretty robust, usable tools to create radio buttons, check boxes, and drop-down menus. If you know your way around Acrobat JavaScript, you can extend the functionality of the forms quite a bit. With 7, there's a separate Form Designer product (comes with the Professional version of Acrobat, I believe) that makes form design a pretty painless process.
   
   Use this method if you have to publish your form for use by the broader general public, and you want to give that general public the chance to fill in the data in a form. You can then either have them postal mail it to you, or submit it to a database.
   
   Don't use this method unless you have a respectable knowledge of Acrobat and of basic web design; the way to make certain functionality work in Acrobat forms requires a little of each.
3. Web forms. You can put a form on a website that a person can fill out, right there on your website, and have that form data go to a database, get emailed to you, or any combination thereof.
   
   Use this method to make your form most widely available to the general public. This method requires you either are or know someone who can do both web programming and database design. This method is very effective at allowing you to aggregate data into a database. To make it work, it requires that you have a web site that can support a web language, like ASP, PHP, JSP, CGI, or ColdFusion. (There are many others; those are just the first few I can think of.)
   
   Don't use this method if you don't need to aggregate your data into a database; it's kind of overkill, and comes with a certain human-hour cost. Don't use this method if you don't have access to competent web design, programming, and database design resources, or don't have the time to learn them.

Personally, given my choice, I'll usually use a web form that uses ColdFusion to talk to a database, and fill that database with the form values. Then, again, using ColdFusion, I can write code to email me a notification that a new form submission has occurred, and the person who submitted it can get a "thank you" email. I say ColdFusion because that's what I was trained in, not necessarily because it's better than ASP, PHP, JSP, or CGI.

More than anything, look to see what others have done, including cases of what you do and don't like. Hopefully, that'll help guide your process better.

Why doesn't everyone use templates?

This is something I've never understood. Why don't more people use templates in their office?

Templates are a way to standardize what comes in and what goes out. Templates are a way to make sure we're all on the same sheet of music, when creating such standard documents as purchase orders, cover letters, confirmation letters, presentations about our company, and expense reports.

I've heard people say that standardization can suppress creativity. OK, fair enough. But the way I see it, this is not the kind of stuff that we want to spend our creative resources on; this is the kind of stuff where the compelling interest to project a unified front is far more important than the compelling interest to be creative.

Save creativity for new ideas about how to manage resources, how to promote and position products, how to sell and market services.

Here's a couple of ways to think about how to use templates in the various Office applications:

1. In Word, templates are a really effective way to store styles and macros. So if you create styles that reflect the company look and feel, you can check the box that says "add to template," and then the template becomes a container for those styles - the two go together. As far as macros go, if the automation provided by the aforementioned macros is specific to the template, why not?
2. In Excel, create templates, like an expense report, where the expense types are fully spelled out and known quantities. Protect the cells that have the formulas (total miles travelled; reimbursable rate times miles travelled). Use Data->Validation to establish upper and lower thresholds for values put into those cells. Use Data->Validation->List to ensure that a person has to choose their name from a drop-down that's in another set of cells.
3. In PowerPoint, create a template with your company logo and colors. Put standard company fonts into the Slide Master and/or Title Master, so that everyone begins with the same look and feel.
4. In Outlook, you can use templates to create a standard message, like a confirmation of purchase, or a request for payment.

In all four cases, create the look you want in the file. That means everything - margins, headers, footers, backgrounds, fonts, colors, the whole bit.

Also, in all four cases, File->Save As. Give the file a meaningful name ("Expense Report" or "Confirmation Message," for example.)

Make sure to go to the bottom, where it says, "Save As Type." Change the type to "template." In Word, it's "Document Template;" in Excel and Outlook, it's just "Template;" in PowerPoint, it's "Design Template."

Once you click on the option for the program in question, you will find yourself taken to a whole new folder, called just "templates." That folder, by default, is in "C:\Documents and Settings\user\Application Data\Microsoft\Templates." This is normal; don't be alarmed. Go ahead and let the program save the template there. Hit "Save," and then close the template file.

Pause. How can you modify your templates directory so that other people can share your templates? Easy. First, open Word. Go to Tools, Options, File Locations. What I generally recommend to people is to change the setting called "Workgroup Templates." In my case, I changed my "Workgroup Templates" on my own computer to a folder called "NHCLC Templates," with NHCLC standing for New Horizons Computer Learning Center, where I work.

For the first three programs mentioned, the way you access the template to use it is to go to your "File" menu, and choose "New." I'll describe Outlook's method shortly. If you use Office 2000 or previous, you'll get a window that says "Templates." Yours will be listed under the tab called "General."

If I had modified my "Workgroup Templates" setting in Word, then any templates I save in the "NHCLC Templates" folder will also show up in the tab called "General."

To get the templates that I created to show up in a separate tab, what I would do is put sub-folders under "NHCLC Templates," like "Reports," and "Personnel." If I do that, then the names of the folders show up as tabs in the "Templates" window. That would be a sound idea if you have a fairly extensive array of templates.

If you want to ensure that everyone in your office uses your templates, the best thing to do is get buy-in at the top levels. If you do, then you can get your administrator to copy the registry key that tells people where their workgroup templates are located. The key, by the way, is called "SharedTemplates," and it's under "My Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Common\General."

Remember, the number "10" in my case refers to the fact that I run Office XP. For 2003, it's "11," and for 2000, it's going to be "9," and so forth. But, if in doubt, you can do a registry key search for "SharedTemplates," and you're good to go.

Once you've found the one you want, just double-click on it. Remember, the best part of all is that you're not overwriting the template when you use this method. You're starting a new document based on it. It's a very elegant, scalable solution.

Back to Outlook. The way you create and use an Outlook template is slightly different. Before you start composing your template message, go to Tools->Options->Mail Format, and make sure that "Use Microsoft Word to edit e-mail messages" is turned off. Personally, I think this so-called "feature" sucks, but that's another story.

Once you've hit "OK" on that change, start a new mail message. Put in that message any information that will be constant for every time you send it. I might create a message addressed to my training group, subject "Timesheets are Due," with the message text being, "Timesheets are due this Friday." That's keeping it nice and generic; it's not date-specific.

Once that's in place, in the message, go to "File," and choose "Save As." Again, as above, save the file as a template.

Close the message. When it asks "Do you want to save changes?" you can say no.

To use the template, you go to your "File" menu, and click on "Choose Form" (towards the bottom.) At the top, where it says "Look In," choose "User Templates in File System." That's where you'll find yours. Click "Open," and you're good to go.

Templates, my friends, will save you infinite amounts of doing and re-doing, and making sure everyone has the most current version of this or that. Make templates not just a policy at your office. Make them a religion. Amen!

Word Tables

I hate Word tables.

I have never had much luck getting Word tables to cooperate with me. Either they fall completely off the page, or the column width gets all jacked up.

I have, however, found a few useful workarounds for this problem:

1) Convert the table to text. Get anywhere in the table, and click on the Table menu. Choose Convert, and then "Table to Text." That will often take care of the problem; you can use tabs to get the text looking how you want. Once you've done that, reverse the steps: Table->Convert->Text to Table. Make sure you use "Tabs" as your delimeter. That might work.
2) Instead of two tables on the same page with different column widths, experiment with splitting cells. On a row where you need more cells, right-click in one of the cells, and choose "Split Cells." You get prompted whether you want to create multiple columns, rows, or both.
3) Use #2 in conjunction with merged cells. Same deal; highlight the two or more cells, right-click, and choose "merge."
4) When all else fails, highlight the table, cut it, and paste the damn thing into Excel. Get it just right there. Then paste back into Word.

None of those solutions are perfect by any means. But the splitting and merging cells is the best I've found so far. Sure beats setting tab stops for stuff like phone lists, resumes, and any other document where structured data is at a premium.

Excel versus Access

OK. Let me begin by saying that I am like all the other 3,999,999 Office users out there, in thinking that Excel is about as cool as cool gets. Excel is friendly, intuitive, "come on in, the water's fine." Excel is familiar.

Access, by contrast, seems cold, dark, distant, and mysterious, "don't go in there, you might get hurt..." kind of a sense. Access doesn't understand Excel's Undo. Access feels foreboding.

Access talks in strange terms, like "referential integrity" and "bound column," while Excel uses friendly, familiar terms like "column" and "row."

But here's the secret, friends: they're both useful. Both have their place and their good.

Excel is useful for two reasons: number one, because it's relatively easy to get in there and start working. Reason number two, because it's great for financial modeling.

Access is also useful for two reasons: number one, because it handles immensely complex, interrelated data with relative ease. Number two, because it make your finished product look like a stand-alone application, with much of the dark underside of functionality hidden from the user.

If I had to keep a list of customers, with names, phone numbers, and addresses, I'd just use Excel.

If I had to interrelate those customers, their orders, the products ordered, the shippers who sent the orders, and the employees who took the orders, I'd go with Access.

I just wanted to give a shout out of warning to those who try to avoid having to use Access by doing complex, processor-intensive VLOOOKUP, INDEX, MATCH, and OFFSET functions to get a result. The biggest problem you'll run into is that you're needlessly replicating the functionality that Access gives you right out of the box.

Let it.