Value
Next cell
CHAPTER 1 n THE MACRO RECORDER AND CODE MODULES 28
currReturn = temp
GetSalesTotal = currReturn
End Function
To use the function, we can create a subroutine or function to call it. The following adds
a label and inserts the total next to it on the worksheet:
Sub AddSalesTotal()
'Author: Jim DeMarco
'Date: 6/24/07
'Purpose: Places value of sales total on worksheet
With Range("A10")
.Value = "Grand Total"
.Font.Bold = True
End With
Range("C10").Value = GetSalesTotal(range("B8:E8"))
End Sub
Type Statements
Type statements are used at module level to define a user-defined data type containing one or
more elements. In the following example, we define Employee as a data type and then use it in
a subroutine, setting values and displaying them.
Type Employee
ID As Long
Name As String
Title As String
Phone As String
End Type
Sub SetEmployee()
Dim empMyEmployee As Employee
empMyEmployee.ID = 123456
empMyEmployee.Name = "John Doe"
MsgBox empMyEmployee.ID & " " & empMyEmployee.Name
End Sub
Class Modules
If you??™ve done any amount of VBA or VB coding, you have more than likely used objects in
your code. Any time you??™ve gone out to a database and retrieved records using ADO, you may
have declared and instantiated a variable like this:
CHAPTER 1 n THE MACRO RECORDER AND CODE MODULES 29
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Some of the examples you??™ve seen thus far have also used some of Excel??™s built-in objects,
like the Selection object, which has a Font property, or the Range object, which has many
properties and methods you can use in your code.
Pages:
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55