Page 27 - Excel for Scientists and Engineers: Numerical Methods

4 EXCEL: NUMERICAL METHODS

Visual Basic Procedures

VBA macros are usually referred to as procedures. They are written or
recorded on a module sheet. A single module sheet can contain many
procedures.

There Are Two Kinds of Macros
There are two different kinds of procedures: Sub procedures, called
command macros in the older XLM macro language, and Function procedures,
called function macros in the XLM macro language and often referred to as
custom functions or user-defined functions.
Although these procedures can use many of the same set of VBA commands,
they are distinctly different. Sub procedures can automate any Excel action. For
example, a Sub procedure might be used to create a report by opening a new
worksheet, copying selected ranges of cells from other worksheets and pasting
them into the new worksheet, formatting the data in the new worksheet,
providing headings, and printing the new worksheet. Sub procedures are usually
"run" by selecting Macro from the Tools menu. They can also be run by means
of an assigned shortcut key, by being called from another procedure, or in
several other ways.
Function procedures augment Excel's library of built-in functions by adding
user-defined functions. A custom or user-defined function is used in a
worksheet in the same way as a built-in function like, for example, Excel's SQRT
function. It is entered in a formula in a worksheet cell, performs a calculation,
and returns a result to the cell in which it is located. For example, a custom
function named FtoC could be used to convert Fahrenheit temperatures to
Celsius.
Custom functions can't incorporate any of VBA's "action" commands. No
experienced user of Excel would try to use the SQRT function in a worksheet
cell to calculate the square root of a number and also open a new workbook and
insert the result there; custom functions are no different.
However, both kinds of macro can incorporate decision-making, branching,
looping, subroutines and many other aspects of programming languages.

The Structure of a Sub Procedure

The structure of a Sub procedure is shown in Figure 1-4. The procedure
begins with the keyword Sub and ends with End Sub. It has a ProcedureName, a
unique identifier that you assign to it. The name should indicate the purpose of
the function. The name can be long, since after you type it once you will
probably not have to type it again. A Sub procedure has the possibility of using
one or more arguments, Argumentl, etc, but for now we will not create Sub