Page 52 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 2 FUNDAMENTALS OF PROGRAMMING WITH VBA 29

variable names of the arguments in the calling statement and in the subroutine do
not have to be the same.
There are several advantages to using subroutines: you eliminate the
repetition of code, and you make the programming clearer by adopting a modular
approach. Perhaps most important, a subroutine that is of general usefulness can
be called by several different procedures.
Scoping a Subroutine

A Sub procedure can be Public or Private. Public subroutines can be called
by any subroutine in any module. The default for any Sub procedure is Public.
A Private subroutine can be called only by other subroutines in the same
module. To declare the subroutine Task3 as a private subroutine, use the
statement
Private Sub Task30
A Sub procedure that is declared Private will not appear in the list of
macros that can be run in the Macro dialog box. The name of a Sub procedure
that takes arguments (i.e., a subroutine), will also not appear in the Macro dialog
box; only Sub procedures without arguments, that is, with empty parentheses
following the procedure name, appear in the Macro dialog box.

VBA Code for Command Macros

Command macros (Sub procedures) are "action" macros: they can enter or
modify data on a spreadsheet, create a report, display a dialog box and so on.
The CD that accompanies this book includes some examples of Sub procedures,
so the material in the following sections will be useful in understanding the VBA
code in these procedures.

Objects and Collections of Objects

Some examples of VBA objects are the Workbook object, the Worksheet
object, the Chart object and the Range object. Note that the Range object can
specify a single cell, such as E5 in the preceding example, or a range of cells, for
example, Range("A1:ElOl"). There is no "cell" keyword in VBA to refer to a
single cell; that would be redundant.
You can also refer to collections of objects. A collection is a group of
objects of the same kind. A collection has the plural form of the object's name
(e.g., Worksheets instead of Worksheet). Worksheets refers to all worksheets
in a particular workbook.
To reference a particular worksheet in a collection, you can use either
Worksheets(NameText) or Worksheets(index), For example, you can refer to