Page 119 - Microsoft Office Excel 2003 Programming Inside Out

Creating Sub and Function Procedures

Tip Use the keyboard to enter the declarations section
You can also enter the declarations section of a code module by moving the insertion point
to the left of the first line of code in the module, pressing Enter, and moving back up to the
newly blank first line. When you do, the value in the Procedure list box changes to Declara
tions and you can begin typing the declaration. You can also start typing the declaration in
the first position of the first line of the module and press Enter. When the Visual Basic
Editor recognizes you put a declaration on the first line, it will create a Declarations section
and put in a line marking the end of the section.

Inside Out

The Good News Is That You Didn’t Break It…

There might be times when you want to change the scope of a procedure from Public to
Private, perhaps after you’ve finished testing the procedure and you don’t need to run it
from the Macro dialog box any more. Another situation where you might want to make a pro
cedure private is if you attached the macro to a toolbar button or menu item and, while you
still want to allow your colleagues to run the macro from the custom button or menu item,
you don’t want just anyone who knows how to use the Customize dialog box to assign the
macro to another button or, worse, edit it in the Visual Basic Editor. If someone is deter-
mined to get into the code and you don’t have the workbook password-protected, you can’t
really stop them, but you can make it more difficult by taking the procedure Private. And, as
the title of this Inside Out sidebar implies, making a Public procedure Private doesn’t break
the link between the toolbar button, menu item, or object to which you assigned the macro.
Clicking the macro trigger, whatever it is, will still run the macro.

Running a Sub Procedure from Within Another Procedure

After you have created a Sub procedure, you will want to run it. As mentioned before, you can
run the procedure by linking the macro to a trigger, but you can also run the macro by calling
it from within another macro. For example, if you want to run an existing procedure, such as
Chapter 5
NOW, which returns the current date and time, you can do so.
Sub ShowTheTime
MsgBox (NOW())
End Sub
The same mechanism works for running a Sub procedure.
To call a Sub procedure from another procedure, type the name of the procedure and include
values for any required arguments. The Call statement is not required, but if you use it, you
must enclose any arguments in parentheses.

93
Part 2: Visual Basic for Applications