Page 121 - Microsoft Office Excel 2003 Programming Inside Out

Creating Sub and Function Procedures

When you create a new code module, the Visual Basic Editor gives it the name Module1,
Module2, and so on. You should strongly consider renaming the modules in your projects to
make their contents more obvious.
To rename a module, follow these steps:
1 Open the workbook to which the module is attached, and click Tools, Macro,
Visual Basic Editor.
2 In the Project window, click the name of the module you want to rename.
3 In the Properties window, select the existing module name next to the Name property,
type the new module name, and then press Enter.

Caution You can run into trouble if you have procedures with the same name in two or
more workbooks. You can’t have two procedures with the same name in a module, but
because you can use dot notation to specify which module’s procedure you want, you can
avoid any problems. That said, if you have several procedures with the same name in dif
ferent modules and you attempt to call one of them without specifying the module that con
tains the procedure (for example, CurrencyModule.Krona), the Visual Basic Editor will
display an Ambiguous name detected error and halt the execution of the code that
attempted to call the procedure.

If you know that the procedure you want to run is available in a module attached to another
workbook and for some reason don’t want to copy the code to your current workbook,
you can call the procedure from the other workbook in one of two ways: by using the
Application.Run method and specifying the name of the workbook, or by creating a reference
to the workbook that contains the procedure.
Using the Application.Run method to run a procedure in another workbook requires you to
name only the other workbook and the procedure you want to run. You name the workbook
and procedure using a syntax that looks a lot like the reference you create when you link from
a worksheet cell to a cell in another workbook.

Chapter 5

If you wanted to call the ConvertToKrona procedure from the workbook ExchangeRates.xls,
you would use the following statement:

Application.Run "'ExchangeRates.xls'!ConvertToKrona"

95
Part 2: Visual Basic for Applications