Microsoft Office Tutorials and References
In Depth Information
Executing Sub Procedures
Another way to call a procedure in a different workbook is to use the Run method of the
Application object. This technique doesn’t require that you establish a reference, but the
workbook that contains the procedure must be open. The following statement executes the
Consolidate procedure located in a workbook named budget macros.xlsm :
Application.Run “’budget macros.xlsm’!Consolidate”
Why call other procedures?
If you’re new to programming, you may wonder why anyone would ever want to call a
procedure from another procedure. You may ask, “Why not just put the code from the called
procedure into the calling procedure and keep things simple?”
One reason is to clarify your code. The simpler your code, the easier it is to maintain and modify.
Smaller routines are easier to decipher and then debug. Examine the accompanying procedure,
which does nothing but call other procedures. This procedure is very easy to follow.
Calling other procedures also eliminates redundancy. Suppose that you need to perform an
operation at ten different places in your routine. Rather than enter the code ten times, you can
write a procedure to perform the operation and then simply call the procedure ten times. Also, if
you need to make a change, you make it only one time rather that ten times.
Also, you may have a series of general-purpose procedures that you use frequently. If you store
these in a separate module, you can import the module to your current project and then call
these procedures as needed — which is much easier than copying and pasting the code into
your new procedures.
Creating several small procedures rather than a single large one is often considered good
programming practice. A modular approach not only makes your job easier but also makes life
easier for the people who wind up working with your code.