CHAPTER 1 n THE MACRO RECORDER AND CODE MODULES 27
Standard modules are inserted into your project by choosing Insert ?¤ Module or by
right-clicking an object in the Project Explorer and choosing Insert ?¤ Module from the popup
menu.
Subprocedures
A subprocedure (also called subroutine) is a procedure that performs a task within a program,
but does not return a value. A subroutine begins with a Sub statement and ends with an End
Sub statement. Any version of the TotalSales code you wrote previously is an example of a
subroutine.
Sub TotalSales3()
'Author: Jim DeMarco
'Date: 6/24/07
'Purpose: Adds total sales for all regions by moving across columms
Dim sFormula As String
Dim i As Integer
sFormula = "=SUM(R[-5]C:R[-1]C)"
For i = 2 To 5
Cells(8, i).Select
ActiveCell.FormulaR1C1 = sFormula
Next i
End Sub
Functions
A function is a procedure that performs a task within a program and returns a value. A function
begins with a Function statement and ends with an End Function statement. Functions
(and subroutines) can receive arguments passed in from calling procedures or passed in
directly.
The following is a function that returns the total for a range passed in to the function as
an argument. We pass in the range reference to make the code flexible enough to reuse on
any range that needs to be totaled.
Function GetSalesTotal(RangeToTotal As Range) As Currency
'Author: Jim DeMarco
'Date: 6/24/07
'Purpose: Returns value of sales total
Dim currReturn As Currency
Dim cell As Range
Dim temp As Currency
For Each cell In RangeToTotal
temp = temp + cell.
Pages:
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54