Mr. Script

Nowhere Near Theory

Mr. Script dissects a VBA application that automates processes so you can side-step any pitfalls along the way.

• By Chris Brooke
• 09/01/2005

There is a magical place many of us have heard about, but few—if any—have actually seen. It's a wondrous land full of beauty, simplicity, and above all, productivity. It is the place called "Theory." Everything works in Theory. Java applications are "write once, run anywhere." Windows never needs to be rebooted. And all of our administrative systems integrate with each other seamlessly. Since first hearing of the existence of Theory, I have longed to go there, where everything "just works."

Unfortunately, my address (and, I'm guessing, yours) is smack-dab in the middle of the planet that Theory is furthest from. On this antithetical plane of existence, we must deal with interoperability issues in heterogeneous environments, disparate functionality and capability just within different versions of Windows, and the occasional blue screen of death. As such, we in the real world are often forced to create solutions to problems that simply don't exist in Theory.

Over the next few months, we're going to take a look a how scripting can be used to tie several different systems together. As a case study, we're going to dissect a VBA application that automates retrieving information from a Web-based order system, downloading software, burning the software to a CD, interfacing with the shipping provider to schedule pickup, and e-mailing the customer. Now, in Theory, all of this would work fine right off the bat; but because we live in the real world, we're going to discuss how to side-step the various pitfalls you won't come across in Theory.

VBA?
Yes, the example we're using is written as VBA code in an Excel spreadsheet. Not to worry; the VBA code we write will be so close to pure VBScript you'll hardly notice a difference. And, of course, I'll explain any differences as we go along. Besides, the more scripting environments you're familiar with, the better prepared you are to choose the right one for the task at hand. One of the prime benefits to using Excel is that it doesn't require any additional tools. Most (if not all) of you probably have Excel already installed on your computer.

Note: For those of you who don't have Excel available, don't worry--you'll still be able to follow along. Just create a HyperText Application (HTA) instead. (If you need a refresher, check out the series of columns on HTAs I did, starting back in January.) You just won't get all the cool benefits of working in the VBA Editor.

The VBA app we'll be using has many parts, each with many functions. We're going to tackle the steps based on common functionality. Each topic we cover will be self-contained enough for you to take what you've learned and apply it to other tasks. Before we're done, though, I'll tie them all together with a nice bow.

Figure 1. You can create a form in Excel from which to launch your scripts. (Click image to view larger version.)

Getting Started
Let's look at how we utilize VBA in Excel. First, we're going to design a form that will be our main interface to these scripts. I'm using Excel 2003, although some of the code was originally written in Excel 2000. When I switched to Excel 2003, no changes were required--it "just worked" (Hey, maybe I am in Theory!) As long as you're using Office 2000 or later, the steps should be the same. For earlier versions of Office, I make no guarantees. If you encounter any real problems, switch to an HTA.

The first step is to open Excel with a blank workbook. From the Tools menu, select "Macro" -> "Visual Basic Editor". When the editor opens, right-click "ThisWorkbook" and select "Insert" -> "UserForm" (see Figure 1).

Resize the form (either by dragging the corner or by specifying the width and height in the Form properties window) to roughly 400 pixels wide by 300 pixels high. Change the form's name in the Property window to frmMain. Then use the Toolbox to arrange a Label, three CommandButtons and a TextBox, as shown in Figure 2.

Figure 2. You will be creating a text box and three command buttons. (Click image to view larger version.)

Next, click on each button in turn and change the caption to "Run Script x " (Substitute 1, 2, or 3 for x ). I've renamed the buttons to btnRun1, btnRun2, and btnRun3 respectively, in order to keep to basic Hungarian Notation as much as possible. I've renamed TextBox1 to txtResult as well. I've also used a 14pt font on each button, as well as the text box--mostly just so the screenshots are clear. You can keep the default font size or change it as I have by simply clicking the ellipsis (...) next to the Font property of each button.

Finally, you need to add code to the "Click" event of each button so that this app will actually do something. To do this, right-click on each button and select "View Code". The Visual Basic editor will automatically create a Sub... End Sub for the event (yet another time-saving benefit of using VBA). Just enter the code you want to execute:

Private Sub btnRun1_Click()
txtResult = "You Clicked Button 1"
End Sub

Private Sub btnRun2_Click()
txtResult = "You Clicked Button 2"
End Sub

Private Sub btnRun3_Click()
txtResult = "You Clicked Button 3"
End Sub

Figure 3. A VBA Form can provide a rich user interface for your scripts.

Figure 3 shows our new Excel "application" in action. Not all that impressive, I know, but bear with me. Just like with HTAs, we can now code these button-click events to do … well … anything. In fact, next month we'll start exploring the first of many tasks we're going to automate with our newest creation. Until then, take some time to get familiar with VBA. Use the buttons on our form to launch some of your common admin scripts. Dig in!

Before I go, though, I'll bet some of you are wondering how to run this app, hmm? It's true, you can create forms all day in Excel, but they won't do anything until you load them. In order to do that, you need to create a Macro. From the Tools Menu, select "Macro" -> "Macros." In the Macro window, under Macro Name, enter "Auto_Open" and click create (see Figure 4 ).

Figure 4. You have to create a Macro in Excel to load your form.

Once again, the VBA Editor will open and you simply enter the code to execute:

Sub Auto_Open()
frmMain.Show
End Sub

Now your form will load automatically when you open the spreadsheet.

One final word of warning about Macros: You need to have your Excel security settings set to Medium or lower. At medium security, Excel will prompt you to enable Macros. From the "Tools" menu, click "Options," select the "Security" tab, and click the "Macro Security" button. Select "Medium" and you're ready to go!