Page 124 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

Retaining Values Between Procedure Calls
The second optional element in a procedure declaration is whether or not to make the pro­
cedure retain the values generated by previous executions of the procedure. One example of
a procedure where you might want to keep a running total for a value is a procedure that
wrote sales for a day into a worksheet. Sure, you could write the values for a day to a file or an
array and add up the totals, but it’s much simpler to display the running total for a day by
ensuring the procedure remembers the values that came before.
As you probably guessed from the available keywords in the Sub procedure definition state­
ment, the keyword you want to use is Static. As an example, consider the following proce­
dure, which attempts to maintain a running total of a day’s sales using the intTotal variable:

Sub RunningTotal()
intTotal = intTotal + ActiveCell.Value
Range("B10").Value = intTotal
End Sub
The benefit of this procedure, if it were working properly, is that you wouldn’t need to run a
query or even some sort of fancy refreshable summation formula in cell B10 to update the
sales total in your worksheet. But, the procedure as written always generates the same answer:
the value in the active cell. The problem is that the variable intTotal is re-created every time
you run the procedure, so its value is set to 0 every time. How do you fix the procedure so that
it keeps a real running total? By adding the Static keyword in front of the Sub statement, as in
the following listing:

Static Sub RunningTotal()
intTotal = intTotal + ActiveCell.Value
Range("B10").Value = intTotal
End Sub
When you add the Static keyword to the Sub statement, Excel knows to create a durable stor­
age space for each variable and to maintain that space and its contents until you close the
workbook.

Note You can also reset the value of the variables in a static procedure by displaying the
procedure in the Visual Basic Editor and clicking Run, Reset.

Chapter 5
Defining Function Procedures

A Function procedure is similar to a Sub procedure, but a function can also return a value.
A Function procedure can take arguments, such as constants, variables, or expressions that
are passed to it by a calling procedure. As an example, consider the SUM function you most
likely use all the time when you create formulas in an Excel worksheet. The following exam­
ple formula finds the sum of the values in cells C14 to H14, J14 and adds 100:
=SUM(C14:H14, J14, 100)

98
Part 2: Visual Basic for Applications