to perform the changes you’ve undone.
Ready to enter some real, live code? Try the following steps:
1 Create a new workbook in Excel.
2 Press Alt+F11 to open the VBE.
3 Click the new workbook’s name in the Project Explorer.
4 Choose Insert ⇒ Module to insert a VBA module into the project.
5 Type the following code into the module: Sub GuessName() Dim Msg as String Dim Ans As Long Msg = "Is your name " & Application.UserName & "?" Ans = MsgBox(Msg, vbYesNo) If Ans = vbNo Then MsgBox "Oh, never mind." If Ans = vbYes Then MsgBox "I must be clairvoyant!" End Sub
6 Make sure the cursor is located anywhere within the text you typed and press F5 to execute the procedure.
When you enter the code listed in Step 5, you might notice that the VBE makes some adjustments to the text you enter. For example, after you type the Sub statement, the VBE automatically inserts the End Sub statement. And if you omit the space before or after an equal sign, the VBE inserts the space for you. Also, the VBE changes the color and capitalization of some text. This is all perfectly normal. It’s just the VBE’s way of keeping things neat and readable.
If you followed the previous steps, you just created a VBA Sub procedure, also known as a macro. When you press F5, Excel executes the code and follows the instructions. In other words, Excel evaluates each statement and does what you told it to do. You can execute this macro any number of times — although it tends to lose its appeal after a few dozen executions.
This simple macro uses the following concepts:
Defining a Sub procedure (the first line)
Declaring variables (the Dim statements)
Assigning values to variables (Msg and Ans)
Concatenating (joining) a string (using the & operator)
Using a built-in VBA function (MsgBox)
Using built-in VBA constants (vbYesNo, vbNo, and vbYes)
Using an If-Then construct (twice)
Ending a Sub procedure (the last line)
Customizing the VBE
If you’re serious about becoming an Excel programmer, you’ll spend a lot of time with VBA modules on your screen. To help make things as comfortable as possible, the VBE provides quite a few customization options.
When the VBE is active, choose Tools ⇒ Options. The Options dialog box has four tabs: Editor, Editor Format, General, and Docking. Take a moment to explore some of the options found on each tab.
The Editor tab
Figure 2-4 shows the options accessed by clicking the Editor tab of the Options dialog box. Use the option in the Editor tab to control how certain things work in the VBE.
FIGURE 2-4: The Editor tab in the Options dialog box.
The Auto Syntax Check option
The Auto Syntax Check setting determines whether the VBE opens a dialog box if it discovers a syntax error while you’re entering your VBA code. The dialog box tells roughly what the problem is. If you don’t choose this setting, VBE flags syntax errors by displaying them in a different color (red by default) from the rest of the code, and you don’t have to deal with any onscreen dialog boxes.
The Require Variable Declaration option
If the Require Variable Declaration option is set, VBE inserts the following statements at the beginning of each new VBA module you insert:
Option Explicit
Changing this setting affects only new modules, not existing modules. If this statement appears in your module, you must explicitly define each variable you use. Using a Dim statement is one way to declare variables.
If you don't set this option, VBA won't require you to explicitly declare your variables. If you don't declare a variable, VBA will declare it for you the first you use it. That is, VBA will create a spot in memory with that variable's name. That may seem like a time saver, but the first time you mistype a variable, you'll be glad you required variable declaration. Bugs from misspelled variables can be hard to find.
The Auto List Members option
If the Auto List Members option is set, VBE provides some help when you’re entering your VBA code. It displays a list that would logically complete the statement you’re typing. This feature is very useful for saving time while coding.
The Auto Quick Info option
If the Auto Quick Info option is selected, VBE displays information about functions and their arguments as you type. This is similar to the way Excel lists the arguments for a function as you start typing a new formula.
The Auto Data Tips option
If the Auto Data Tips option is set, VBE displays the value of the variable over which your cursor is placed when you’re debugging code. This is turned on by default and often quite useful. There is no reason to turn this option off.
The Auto Indent setting
The Auto Indent setting determines whether VBE automatically indents each new line of code the same as the previous line. Most Excel developers are keen on using indentations in their code, so this option is typically kept on.
