Microsoft Office Tutorials and References
In Depth Information
Working with Code Windows
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 in this exercise 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
h Declaring a procedure (the first line)
h Assigning a value to variables ( Msg and Ans )
h Concatenating strings (using the & operator)
h Using a built-in VBA function ( MsgBox )
h Using built-in VBA constants ( vbYesNo and vbNo )
h Using an If-Then-Else construct
h 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
No matter how hard you try, there is absolutely no way to record the SayHello procedure
shown in the previous section. 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 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 these, start with a blank workbook:
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.