We??™ve added a couple of variables to our declarations:
Dim iMgrID As Integer
Dim sMgrName As String
These will hold the ID for our search and the name for our display.
We??™re setting our xlSheet variable to refer to Sheet3:
Set xlSheet = Sheets("Sheet3")
And we??™re pointing back to Sheet2 to get our selected manager information:
Sheets("Sheet2").Activate
We??™ve added calls to two helper functions, GetMgrID and GetMgrName. These functions refer
to the active sheet, so this line of code is important. We could optionally have made explicit
references to Sheet2 in our functions or passed in the worksheet as an argument to the functions.
Add these functions to Module1.
Function GetMgrID() As Integer
Dim iReturn As Integer
Dim rngMgrID As Range
Set rngMgrID = Cells(ActiveCell.Row, 1)
iReturn = rngMgrID.Value
Set rngMgrID = Nothing
GetMgrID = iReturn
End Function
CHAPTER 2 n DATA IN, DATA OUT 79
Function GetMgrName() As String
Dim sReturn As String
Dim iRow As Integer
iRow = ActiveCell.Row
sReturn = Cells(iRow, 2).Value & " " & Cells(iRow, 3).Value
GetMgrName = sReturn
End Function
These functions illustrate two methods for referring to cells on Sheet2. GetMgrID uses a
variable of type Range to refer to the cell in the current row and column 1. GetMgrName uses
direct references to the cells by using the Cells object.
Let??™s test the code. On Sheet2, put your cursor in any column on a row containing manager
information, as in Figure 2-25.
Figure 2-25.
Pages:
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104