Microsoft Office Tutorials and References
In Depth Information
Working with Code Windows
Entering and editing text in a VBA module works just as you would expect. You can select text,
copy it or cut it, and then paste it to another location.
A single instruction in VBA can be as long as you need it to be. For readability’s sake, however,
you may want to break a lengthy instruction into two or more lines. To do so, end the line with a
space followed by an underscore character and then press Enter and continue the instruction on
the following line. The following code, for example, is a single VBA statement split over four lines:
MsgBox “Can’t find “ & UCase(SHORTCUTMENUFILE) _
& vbCrLf & vbCrLf & “The file should be located in “ _
& ThisWorkbook.Path & vbCrLf & vbCrLf _
& “You may need to reinstall BudgetMan”, vbCritical, APPNAME
Notice that I indented the last three lines of this statement. Doing so is optional, but it helps
clarify the fact that these four lines are, in fact, a single statement.
Like Excel, the VBE has multiple levels of Undo and Redo. Therefore, if you find that
you deleted an instruction that you shouldn’t have, you can click the Undo button (or
press Ctrl+Z) repeatedly until the instruction comes back. After undoing, you can click
the Redo button (or press Ctrl+Y) to redo changes that were previously undone. This
feature can be a lifesaver, so I recommend that you play around with it until you
understand how it works.
To get a feel for entering a VBA procedure, try this: Insert a VBA module into a project and then
enter the following procedure into the Code window of the module:
Msg = “Is your name “ & Application.UserName & “?”
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then
MsgBox “Oh, never mind.”
MsgBox “I must be clairvoyant!”
Figure 7-4 shows how this code looks in a VBA module.
While you enter the code, notice that the VBE makes some adjustments to the text that
you enter. For example, if you omit the space before or after an equal sign ( = ), VBE
inserts the space for you. Also, the color of some of the text is changed. These
adjustments are all perfectly normal, and you’ll appreciate them later.