VBA and Excel for Engineers and Scientists – Part 2

Help Me Help You!

Learning VBA is something that is going to take much more time and effort than simply reading this article. The way I learned was simply trial and error and being forced to find a solution to a coding question because I needed to implement it in my application! But really the only way to improve is to struggle with it and to try and read up on the things that don’t make sense. To that end, there are a couple of resources that you might want to know about.

First, the Excel/VBA help files. If you use your help menu within Excel, you get the help for the Excel program, not VBA. So it’s useful if you need help with pivot tables and charts but it won’t help much with the VBA. If you launch help from within the VBE, then you’re looking at the VBA help. Of course, you might need to install it from your MS Office CD if it doesn’t seem to come up. I suggest you get used to poking around in the help documentation…It will likely pay off later.

Another resource is the object browser which you launch in the VBE by pressing the F2 key. The usefulness of this little interface will become more apparent after I discuss objects in the third part of this series. But one nice thing is you can search for objects by keyword and if you wish, constrain your searching to the VBA object model, or the Excel object model or both. Then, when you find what you’re looking for, clicking on the help button takes you to the appropriate help page in the Excel/VBA help documentation.

There are lots of good VBA resources out there; here are a couple of my favorites. Of course, you’re welcome to Google your own.

• John Walkenbach, Mr. Spreadsheet (author of numerous Excel books)
• John Peltier’s Excel Charting site, great charting information
• Daily Dose of Excel blog

Debugging

I don’t know what percentage of my coding time I spend debugging, but it’s probably at least 50%. Here are a few techniques you can employ to help you in the debugging process.

Message Box

Slightly crude, but it does serve its purpose. Message Boxes are useful for giving the user some feedback about your application, but they can also be used to print out some debugging information to look at. An example is shown below. You can make longer output by adding the vbCrLf character to your string.

Listing 12: Message Box example

MsgBox "Your Value is '" & strValue &"'."

Debug.Print

This method is very simple and doesn’t require much effort. See the examples below.

Listing 13: Debug.Print example

Debug.Print "Result = " & strResult Debug.Print "Starting Subroutine SecretStash"

Each Debug.Print statement will appear on its own line in the "Immediate” window in the VBE. If you don’t see the immediate window, use the View menu. You can use the Debug.Print statement as often as you want, but the immediate window does have some finite amount of data it will hold (I don’t know what it is off hand). After it fills up, it will start overwriting back at the beginning. I don’t think this is a big problem for most people, just a heads up.

You will need to periodically clean out that window just to make it readable and to avoid confusion. To do that, just select all of the text and hit the delete key. I’m not sure if it’s true or not, but my observation is that using the Debug.Print command seems to slow down my code a little bit, even when I don’t have the VBE open. So if you notice the same thing, I suggest after you debug your application, comment out the Debug.Print lines.

Breakpoints and Stepping Through

A breakpoint is a setting on a line of code that tells VBA to pause execution immediately before that line is executed (via Chuck Pearson’s Debugging page). To set a breakpoint, with your cursor on the line, press the F9 key or click on the gray margin on the left hand side of the code module. You can toggle the breakpoint off the same way. Refer to Figure 2 below for an example.

Figure 2: Debugging with breakpoints

Once you have entered break mode, the current line about to be executed will be highlighted yellow. Press the F8 key or the "Step into” button to execute line by line. Once you’re done with the break mode, pless the play button to resume running the macro until the end of the code or until another breakpoint is encountered.