Understanding VBE components

Figure 2-1 shows the VBE program with some of the key parts identified. VBE contains several windows and is highly customizable, so chances are your window won’t look exactly like what you see in the figure. You can hide windows, rearrange windows, dock windows, and so on.

Adding a new VBA module

When you record a macro, Excel automatically inserts a VBA module to hold the recorded code. The workbook that holds the module for the recorded macro depends on where you chose to store the recorded macro, just before you started recording.

In general, a VBA module can hold three types of code:

• Declarations: One or more information statements that you provide to VBA. For example, you can declare the data type for variables you plan to use or set some other module-wide options.
• Sub procedures: A set of programming instructions that performs some action. All recorded macros are Sub procedures.
• Function procedures: A set of programming instructions that returns a single value (similar in concept to a worksheet function, such as Sum).

A single VBA module can store any number of Sub procedures, Function procedures, and declarations. How you organize a VBA module is up to you. Some people prefer to keep all their VBA code for an application in a single VBA module; others like to split up the code into several modules. It’s a personal choice, like arranging furniture.

Follow these steps to manually add a new VBA module to a project:

1. In the project window, select the project’s name.
2. Choose Insert  ⇒  Module.

Or you can

1. Right-click the project’s name.
2. Choose Insert  ⇒  Module from the shortcut menu.

The new module is added to a Modules folder in the project window (see Figure 2-3). Any modules you create in a given workbook are placed in this Modules folder.

Removing a VBA module

You may want to remove a code module that is no longer needed. To do so, follow these steps:

1. In the project window, select the module’s name.
2. Choose File  ⇒  Remove xxx, where xxx is the module name.

Or

1. Right-click the module’s name.
2. Choose Remove xxx from the shortcut menu.

You can remove VBA modules, but there is no way to remove the other code modules, those for Sheet objects or ThisWorkbook.

Minimizing and maximizing windows

Code windows are much like workbook windows in Excel. You can minimize, maximize, resize, hide, and rearrange them, and more. Most people maximize the code window that they’re working on so that they can see more code and reduce distractions.

To maximize a code window, click the maximize button in its title bar (right next to the X) or double-click the title bar. To restore a code window to its original size, click the restore button (the icon that looks like a box) located next to the X.

Sometimes, you may want to have two or more code windows visible. For example, you may want to compare the code in two modules or copy code from one module to another. You can arrange the windows manually, or use the Window  ⇒  Tile Horizontally or Window  ⇒  Tile Vertically command to arrange them automatically.

You can quickly switch among code windows by pressing Ctrl+Tab. If you repeat that key combination, you keep cycling through all open code windows. Pressing Ctrl+Shift+Tab cycles through the windows in reverse order.

Minimizing a code window gets it out of the way. You can also click the close button (the X) in a code window’s title bar to close the window completely. (Closing a window just hides it; you won't lose anything). To open it again, just double-click the appropriate object in the project window. Working with these code windows sounds more difficult than it really is.

Getting VBA code into a module

Before you can do anything meaningful, you must have some VBA code in the VBA module. You can get VBA code into a VBA module in three ways:

• Use the Excel macro recorder to record your actions and convert them to VBA code.
• Enter the code directly.
• Copy the code from one module and paste it into another.

You have discovered the excellent method for creating code by using the Excel Macro recorder. However, not all tasks can be translated to VBA by recording a macro. You often have to enter your code directly into the module, either by typing the code by or copying and pasting code you've found elsewhere.

Entering and editing text in a VBA module works as you might expect. You can select, copy, cut, paste, and do other things to the text.

A single line of VBA code can be as long as you like. However, you may want to use the line-continuation character to break up lengthy lines of code. To continue a single line of code (also known as a statement) from one line to the next, end the first line with a space followed by an underscore (_). Then continue the statement on the next line. Here’s an example of a single statement split into three lines:

Selection.Sort Key1:=Range("A1"), _

Order1:=xlAscending, Header:=xlGuess, _

Orientation:=xlTopToBottom

This statement would perform the same way if it were entered in a single line (with no line-continuation characters).

VBE has multiple levels of undo and redo. If you deleted a statement that you shouldn’t have, use the Undo button on the toolbar (or press Ctrl+Z) until the statement appears again. After undoing, you can use the Redo button 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 activate VBE.
3. Click the new workbook’s name in the project window.
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 then press F5 to execute the procedure.

F5 is a shortcut for the Run  ⇒  Run Sub/UserForm command.

When you enter the code listed in step 5, you might notice that VBE makes some adjustments to the text you enter. For example, after you type the Sub statement, VBE automatically inserts the End Sub statement. And if you omit the space before or after an equal sign, VBE inserts the space for you. Also, VBE changes the color and capitalization of some text. These changes are 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)

As mentioned, you can copy and paste code into a VBA module. For example, a Sub or Function procedure that you write for one project might also be useful in another project. Instead of wasting time reentering the code, you can activate the module and use the normal copy-and-paste procedures (Ctrl+C to copy and Ctrl+V to paste). After pasting the code into a VBA module, you can modify the code as necessary.

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 VBE.

The Editor Format tab

Figure 2-5 shows the Editor Format tab of the Options dialog box. With this tab, you can customize the way VBE looks.

The General tab

Figure 2-6 shows the options available on the General tab in the Options dialog box. In almost every case, the default settings are just fine.

The most important setting on the General tab is Error Trapping. If you are just starting your Excel macro-writing career, it’s best to leave Error Trapping set to Break on Unhandled Errors. This setting ensures that Excel can identify errors as you type your code.

The Docking tab

Figure 2-7 shows the Docking tab. Its options determine how the various windows in VBE behave. When a window is docked, it is fixed in place along one edge of the VBE program window. Docking makes it much easier to identify and locate a particular window. If you turn off all docking, you have a big, confusing mess of windows. Generally, the default settings work fine.