Entering VBA code

Before you can do anything meaningful, you must have some VBA code in a Code window. And the 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:

♦ The old-fashioned way: Enter the code the old-fashioned way: Type it from your keyboard.

♦ 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 are working in.

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 really necessary, but it makes the code easier to read, so it's a good habit to acquire.

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 will become apparent in Chapters 9 and 10.

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.

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 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-5 shows how this looks in a VBA module.

Figure 7-5: Your first VBA procedure.

While you enter the code,you might 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:

♦ Click the Run Sub/UserForm button on the Standard toolbar.

If you entered the code correctly, the procedure will execute, and you can respond to a simple dialog box (see Figure 7-6) that displays the user name, 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-6: The result of running the procedure in Figure 7-5.

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

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:

1. Activate a worksheet in the workbook (any worksheet will do).

2. Choose the Tools ^ Macro ^ Record New Macro command.

Excel displays its Record Macro dialog box.

3. Just click OK to accept the defaults.

Excel automatically inserts a new VBA module into the project. From this point on, Excel converts your actions into VBA code. While recording, Excel displays the word Recording in the status bar and also displays a miniature floating toolbar that contains two toolbar buttons (Stop Recording and Relative Reference).

4. Choose the File ^ Page Setup command. Excel displays its Page Setup dialog box.

5. Select the Landscape option and click OK to close the dialog box.

6. Click the Stop Recording button on the miniature toolbar (or choose Tools ^ Macro ^ Stop Recording).

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. Click the Modules node to expand it. Then click the Modulel item to display the code window. (Hint: If the project already had a Modulel, the new macro will be in Module2.) The code generated by this single command is shown in Listing 7-1. Remember that code lines preceded by an apostrophe are comments, and they are not executed.

Listing 7-1: Macro for Changing Page Setup to Landscape Orientation

Sub Macro1()

' Macrol Macro

' Macro recorded by John Walkenbach

With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = ""

End With

ActiveSheet.PageSetup.PrintArea = ""

With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = ""

.LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(l) .BottomMargin = Application.InchesToPoints(l) .HeaderMargin = Application.InchesToPoints(0.5)

.FooterMargin = Application.InchesToPoints(0.5)

.PrintHeadings = False

.PrintGridlines = False

.PrintComments = xlPrintNoComments

.PrintQuality = 600

.CenterHorizontally = False

.CenterVertically = False

.Orientation = xlLandscape

.Draft = False

.PaperSize = xlPaperLetter

.FirstPageNumber = xlAutomatic

.Order = xlDownThenOver

.BlackAndWhite = False

.PrintErrors = xlPrintErrorsDisplayed 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 when I tried something like this.) Although you changed only one simple setting in the Page Setup dialog box, Excel generates code that reproduces all the settings in the dialog box.

This brings up an important concept. Often, the code produced when you record a macro is overkill. For example, if you want your macro only to switch to landscape mode, 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 not necessary. 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. Variable xlLandscape has a value of 2, and xlPortrait has a value of 1. The following macro works the same as the preceding

Macrol.

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.

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.

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

COPYING VBA CODE

So far, I've covered entering code directly 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.

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

0 0

Post a comment