Spreadsheets with something extra

February 23, 2003

0 2 minutes read

Have you ever created a spreadsheet only to open it a couple of months later to discover you couldn’t remember how to update it or what data it needed? Or, if you prepared the spreadsheet for others in your organization, did they keep calling you for instructions on how to use it?

You can eliminate those problems and, as a by-product, ensure the accuracy of spreadsheets by enhancing them with easy-to-create graphic messages and input boxes that can provide reminders, explain various functions of the data tables, help users find and enter the right data and even refuse to let them proceed unless they follow a prescribed procedure. In short, those boxes serve as automated stand-ins for the spreadsheet creator, who can design them to appear whenever a user opens the spreadsheet. Though you have to write message and input boxes in the Visual Basic for Applications (VBA) language, which is built into Microsoft Excel, you will be happy to know it is easy to learn.

GET THE MESSAGE
Let’s begin by creating a message box. The message may be a greeting to the user, instruction about using the spreadsheet or a reminder to complete a task. The message syntax, in its simplest form, is: MsgBox “prompt”, where the words between the quote marks are the message.

To make a message box, open Excel and press Alt+F11 to launch the Visual Basic Editor. Another way to start the editor is to click on Tools, Macro and then select Visual Basic Editor. Once it begins, click on Insert, then Module to open a VBA screen; that’s the place where you will write the VBA code, which is technically called the subroutine or subprocedure.

All subroutines must start with the word Sub and finish with End Sub. After typing Sub, give the routine a name followed by open and closed parentheses. As a practical matter, the name you assign should describe your routine so it will be easy to identify.

A subroutine for a welcoming message box, called Hello, would look like this:

Sub Hello ()
MsgBox “Hello!!”
End Sub

Exhibit 1

Exhibit 1above shows a sample of such a message box and the code that produced it. After users read the message box and press the OK button, it disappears from the screen.