Working with Code Windows

When you become proficient with VBA, you'll be spending lots of time working in code windows. Each object in a project has an associated code window. To summarize, these objects can be

The workbook itself ( ThisWorkbook in the Project Explorer window)
A worksheet or chart sheet in a workbook (for example, Sheet1 or Chart1 in the Project Explorer window)
A VBA module
A class module (a special type of module that lets you create new object classes)
A UserForm

Minimizing and maximizing windows

Depending on how many workbooks and add-ins are open , the VBE can have lots of code windows, and things can get a bit confusing. Code windows are much like worksheet windows in Excel. You can minimize them, maximize them, hide them, rearrange them, and so on. Most people find it most efficient to maximize the Code window that they're working in. Doing so enables you to see more code and keeps you from getting distracted. To maximize a Code window, click the maximize button in its title bar or just double-click its title bar. To restore a Code window (make it nonmaximized), click the Restore button in its title bar.

Sometimes, you might want to have two or more Code windows visible. For example, you might want to compare the code in two modules or perhaps copy code from one module to another. To view two or more Code windows at once, make sure the active code window isn't maximized. Then drag and resize the windows that you want to view.

Minimizing a code window gets it out of the way. You can also click the Close button in a Code window's title bar to close the window completely. To open it again, just double-click the appropriate object in the Project Explorer window.

The VBE doesn't let you close a workbook. You must reactivate Excel and close it from there. You can, however, use the Immediate window to close a workbook or an add-in. Just activate the Immediate window, type a VBA statement like the one that follows , and press Enter:

 Workbooks("myaddin.xlam").Close

As you'll see, this statement executes the Close method of the Workbook object, which closes a workbook. In this case, the workbook happens to be an add-in.

Storing VBA code

In general, a code window can hold four types of code:

Sub procedures: A procedure is a set of instructions that performs some action.
Function procedures: A function is a set of instructions that returns a single value or an array (similar in concept to a worksheet function, such as SUM).
Property procedures: These are special procedures used in class modules.
Declarations: A declaration is information about a variable that you provide to VBA. For example, you can declare the data type for variables you plan to use.

A single VBA module can store any number of Sub procedures, Function procedures, and declarations. How you organize a VBA module is completely 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 different modules.

Note

Although you have lots of flexibility regarding where to store your VBA code, there are some restrictions. Event handler procedures must be located in the Code window for the object that responds to the event. For example, if you write a procedure that executes when the workbook is opened, that procedure must be located in the Code window for the ThisWorkbook object, and the procedure must have a special name . This concept will become clearer when I discuss events (Chapter 19) and UserForms (Part IV).

Entering VBA code

Before you can do anything meaningful, you must have some VBA code in a Code window. This VBA code must be within a procedure. A procedure consists of VBA statements. For now, I focus on one type of Code window: a VBA module.

You can add code to a VBA module in three ways:

Enter the code manually: Use your keyboard to type your code.
Use the macro-recorder feature: Use Excel's macro-recorder feature to record your actions and convert them into VBA code.
Copy and paste: Copy the code from another module and paste it into the module that you're working in.

Pause for a Terminology Break

Throughout this book, I use the terms routine, procedure, and macro. Programming people typically use the word procedure to describe an automated task. In Excel, a procedure is also known as a macro. Technically, a procedure can be a Sub procedure or a Function procedure, both of which are sometimes called routines. I use all these terms pretty much interchangeably. There is, however, an important difference between Sub procedures and Function procedures. This distinction becomes apparent in Chapters 9 and 10.

ENTERING CODE MANUALLY

Sometimes, the most direct route is the best one. Entering code directly involves well, entering the code directly. In other words, you type the code by using your keyboard. You can use the Tab key to indent the lines that logically belong together - for example, the conditional statements between an If and an End If statement. This isn't necessary, but it makes the code easier to read, so it's a good habit to acquire.

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 might 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; 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.

Tip

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.

Try this: Insert a VBA module into a project and then enter the following procedure into the Code window of the module:

 Sub SayHello()     Msg = "Is your name " & Application.UserName & "?"     Ans = MsgBox(Msg, vbYesNo)     If Ans = vbNo Then         MsgBox "Oh, never mind."     Else         MsgBox "I must be clairvoyant!"     End If End Sub

Figure 7-4 shows how this looks in a VBA module.

Figure 7-4: Your first VBA procedure.

Note

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. This is all perfectly normal, and you'll appreciate it later.

To execute the SayHello procedure, make sure that the cursor is located anywhere within the text that you typed. Then do any of the following:

Press F5.
Choose Run Run Sub/UserForm.
Click the Run Sub/UserForm button on the Standard toolbar.

If you entered the code correctly, the procedure executes, and you can respond to a simple dialog box (see Figure 7-5) that displays the username, as listed in Excel's Options dialog box. Notice that Excel is activated when the macro executes. At this point, it's not important that you understand how the code works; that becomes clear later in this chapter and in subsequent chapters.

Figure 7-5: The result of running the procedure in Figure 7-4.

Note	Most of the time, you'll be executing your macros from Excel. However, it's often more efficient to test your macro by running it directly from the VBE.

What you did was write a VBA Sub procedure (also known as a macro ). When you issued the command to execute the macro, the VBE quickly compiled the code and executed it. In other words, each instruction was evaluated, and Excel simply did what it was told to do. You can execute this macro any number of times, although it tends to lose its appeal after a while.

For the record, this simple procedure uses the following concepts (all of which I cover later in the book):

Declaring a procedure (the first line)
Assigning a value to variables ( Msg and Ans )
Concatenating strings (using the & operator)
Using a built-in VBA function ( MsgBox )
Using built-in VBA constants ( vbYesNo and vbNo )
Using an If-Then-Else construct
Ending a procedure (the last line)

Not bad for a first effort, eh?

USING THE MACRO RECORDER

Another way to get code into a VBA module is to record your actions by using the Excel macro recorder.

No matter how hard you try, there is absolutely no way to record the SayHello procedure shown previously. As you'll see, recording macros is very useful, but it has some limitations.

In fact, when you record a macro, you almost always need to make some adjustments or enter some code manually.

This next example shows how to record a macro that simply changes the page setup to landscape orientation. If you want to try this, start with a blank workbook and follow these steps:

Activate a worksheet in the workbook (any worksheet will do).
Choose Developer Code Record Macro.

Excel displays its Record Macro dialog box.
Click OK to accept the default setting for the macro.

Excel automatically inserts a new VBA module into the workbook's VBA project. From this point on, Excel converts your actions into VBA code. Notice that Excel's status bar displays a blue square. You can click that control to stop recording.
Choose Page Layout Page Setup Orientation Landscape.
Select Developer Code Stop Recording or click the blue square in the status bar.

Excel stops recording your actions.

To take a look at the macro, activate the VBE (pressing Alt+F11 is the easiest way) and locate the project in the Project Explorer window. Double-click the Modules node to expand it. Then double-click the Module1 item to display the code window. (If the project already had a Module1 , the new macro will be in Module2 .) The code generated by this single command follows. Remember that code lines preceded by an apostrophe are comments and are not executed.

 Sub Macro1() ' ' Macro1 Macro ' '     With ActiveSheet.PageSetup         .PrintTitleRows = ""         .PrintTitleColumns = ""     End With     ActiveSheet.PageSetup.PrintArea = ""     With ActiveSheet.PageSetup         .LeftHeader = ""         .CenterHeader = ""         .RightHeader = ""         .LeftFooter = ""         .CenterFooter = ""         .RightFooter = ""         .LeftMargin = Application.InchesToPoints(0.7)         .RightMargin = Application.InchesToPoints(0.7)         .TopMargin = Application.InchesToPoints(0.75)         .BottomMargin = Application.InchesToPoints(0.75)         .HeaderMargin = Application.InchesToPoints(0.3)         .FooterMargin = Application.InchesToPoints(0.3)         .PrintHeadings = False         .PrintGridlines = False         .PrintComments = xlPrintNoComments         .PrintQuality = 600         .CenterHorizontally = False         .CenterVertically = False         .Orientation = xlLandscape         .Draft = False         .PaperSize = xlPaperLetter         .FirstPageNumber = xlAutomatic         .Order = xlDownThenOver         .BlackAndWhite = False         .Zoom = 100         .PrintErrors = xlPrintErrorsDisplayed         .OddAndEvenPagesHeaderFooter = False         .DifferentFirstPageHeaderFooter = False         .ScaleWithDocHeaderFooter = True         .AlignMarginsHeaderFooter = True         .EvenPage.LeftHeader.Text = ""         .EvenPage.CenterHeader.Text = ""         .EvenPage.RightHeader.Text = ""         .EvenPage.LeftFooter.Text = ""         .EvenPage.CenterFooter.Text = ""         .EvenPage.RightFooter.Text = ""         .FirstPage.LeftHeader.Text = ""         .FirstPage.CenterHeader.Text = ""         .FirstPage.RightHeader.Text = ""         .FirstPage.LeftFooter.Text = ""         .FirstPage.CenterFooter.Text = ""         .FirstPage.RightFooter.Text = ""     End With End Sub

You might be surprised by the amount of code generated by this single command. (I know I was the first time I tried something like this.) Although you changed only one simple setting in the Page Setup tab, Excel generates code that affects dozens of print settings.

This brings up an important concept. The Excel macro recorder is not the most efficient way to generate VBA code. More often than not, the code produced when you record a macro is overkill. Consider the recorded macro that switches to landscape mode. Practically every statement in that macro is extraneous. You can simplify this macro considerably by deleting the extraneous code. This makes the macro easier to read, and the macro also runs faster because it doesn't do things that are unnecessary. In fact, this macro can be simplified to the following:

 Sub Macro1()     With ActiveSheet.PageSetup         .Orientation = xlLandscape     End With End Sub

I deleted all the code except for the line that sets the Orientation property. Actually, this macro can be simplified even more because the With-End With construct isn't necessary when you're changing only one property:

 Sub Macro1()     ActiveSheet.PageSetup.Orientation = xlLandscape End Sub

In this example, the macro changes the Orientation property of the PageSetup object on the active sheet. By the way, xlLandscape is a built-in constant that's provided to make things easier for you. The variable xlLandscape has a value of 2 , and xlPortrait has a value of 1 . The following macro works the same as the preceding Macro1 .

 Sub Macro1a()     ActiveSheet.PageSetup.Orientation = 2 End Sub

Most would agree that it's easier to remember the name of the constant than the arbitrary numbers . You can use the Help system to learn the relevant constants for a particular command.

You could have entered this procedure directly into a VBA module. To do so, you would have to know which objects, properties, and methods to use. Obviously, it's much faster to record the macro, and this example has a built-in bonus: You also learned that the PageSetup object has an Orientation property.

Note

A point that I make clear throughout this book is that recording your actions is perhaps the best way to learn VBA. When in doubt, try recording. Although the result might not be exactly what you want, chances are that it will steer you in the right direction. You can use the Help system to check out the objects, properties, and methods that appear in the recorded code.

CROSS-REFERENCE

I discuss the macro recorder in more detail later in this chapter. See the section, "The Macro Recorder."

Unfortunately, some Excel actions simply can't be recorded. For example, turn on the macro recorder and record your actions while you insert a Shape and apply formatting to it.

You'll find that the recorded macro is completely empty. Why? Because Microsoft didn't deem enabling this type of action important enough to delay the release of Office 2007.

COPYING

VBA CODE So far, I've covered typing code directly into a module and recording your actions to generate VBA code. The final method of getting code into a VBA module is to copy it from another module. For example, you may have written a procedure for one project that would also be useful in your current project. Rather than re-enter the code, you can simply open the workbook, activate the module, and use the normal Clipboard copy-and-paste procedures to copy it into your current VBA module. After you've finished pasting, you can modify the code as necessary.

Tip	As I note previously in this chapter, you can also import to a file an entire module that has been exported.