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


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


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:

■ 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?


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 Developer Code Record Macro. Excel displays its Record Macro dialog box.

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

4. Choose Page Layout

Page Setup

Orientation Landscape.

5. 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 Modulel item to display the code window. (If the project already had a Modulel, 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 Macrol() ' Macrol 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- I discuss the macro recorder in more detail later in this chapter. See the

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


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.



0 0

Post a comment