Page 184 - Excel Data Analysis

10 537547 Ch09.qxd 3/4/03 12:13 PM Page 170

EXCEL DATA ANALYSIS

CREATE A MACRO USING

THE VISUAL BASIC EDITOR

o create a macro in the Visual Basic Editor, you need should mark subroutines as private if you do not want them
to create a subroutine. You can easily create a accessible as macros. You do so by placing Private before
T subroutine within the Visual Basic Editor that executes the Sub statement, such as Private Sub SampleSub().
a series of VBA commands. Each macro that runs in Excel is Typically, other subroutines within the same module call
actually just a subroutine that contains blocks of VBA code. private subroutines. A subroutine is called using the Call
A single subroutine can call other subroutines and statement — for example, Call SampleSub(). Excel
functions, so you can create a macro that is much more considers any subroutines that do not have the Private
complex than just a simple subroutine. keyword to be public; the use of the Public keyword is
unnecessary because a subroutine with no keyword is the
VBA provides essentially two different types of subroutines: same as one with the Public keyword.
private and public. When you create a macro with the
Macro Recorder, the subroutine it creates is public, meaning VBA does allow a subroutine to be called without the Call
that all procedures, including the Macro dialog box, can statement. However, even though the Call statement is not
access and see it. Conversely, only other procedures within required, you should always use it to remind you that
the same module can access a private subroutine. Excel another procedure is being called. Using the Call
hides all private subroutines from the Macro dialog box, so statement makes your code much more readable because
you cannot activate them with key combinations. You another user can quickly look at the code and see that
another subroutine is being called.

CREATE A MACRO USING THE VISUAL BASIC EDITOR

⁄ In the Project Explorer, Note: The project is the name of the ■ Excel opens a blank Code ˇ Type () after the name of
click the project to which you workbook in which you want to store window. the subroutine.
want to add a new macro. the new macro.
‹ Type Sub. Á Press Enter.
¤ Click Insert ➪ Module.
› Type the name of your
subroutine.

170