If you have some experience with VBA, you already have enjoyed the usefulness of the immediate window – it simply does anything! Anyway, when you try to enter a subroutine, a method or a function, declared in class, you receive an unpleasant mistake, telling you something about a class, which is not defined:
How we can resolve this issue? The answer is quite simple actually – declare the class in the immediate window like this:
|
1 2 3 4 5 6 |
Set wli = New WorkLogItem wli.TaskID = 2 wli.PersonName = "Sally" wli.HoursWorked = 3 set clnwlis = new Collection clnWlis.Add wli |
Then, when you would like to refer to any function or property of the class, you would get an answer (marked with red in the screenshot):
So, last but not least, I will give the code of the example. It is taken from one good example in StackOverflow. It consists of two Class Modules and one Regular Module.
This is how it looks like:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Option Explicit Private pWorkLogItems As Collection Public Property Get WorkLogItems() As Collection Set WorkLogItems = pWorkLogItems End Property Public Property Set WorkLogItems(lWorkLogItem As Collection) Set pWorkLogItems = lWorkLogItem End Property Function GetHoursWorked(strPersonName As String) As Double On Error GoTo Handle_Errors Dim wli As WorkLogItem Dim doubleTotal As Double doubleTotal = 0 For Each wli In WorkLogItems If strPersonName = wli.PersonName Then doubleTotal = doubleTotal + wli.HoursWorked End If Next wli Exit_Here: GetHoursWorked = doubleTotal Exit Function Handle_Errors: 'You will probably want to catch the error that will ' 'occur if WorkLogItems has not been set ' Resume Exit_Here End Function |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Option Explicit Private pTaskID As Long Private pPersonName As String Private pHoursWorked As Double Public Property Get TaskID() As Long TaskID = pTaskID End Property Public Property Let TaskID(lTaskID As Long) pTaskID = lTaskID End Property Public Property Get PersonName() As String PersonName = pPersonName End Property Public Property Let PersonName(lPersonName As String) pPersonName = lPersonName End Property Public Property Get HoursWorked() As Double HoursWorked = pHoursWorked End Property Public Property Let HoursWorked(lHoursWorked As Double) pHoursWorked = lHoursWorked End Property |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
Option Explicit Function PopulateArray() As Collection Dim clnWlis As Collection Dim wli As WorkLogItem 'Put some data in the collection' Set clnWlis = New Collection Set wli = New WorkLogItem wli.TaskID = 1 wli.PersonName = "Fred" wli.HoursWorked = 4.5 clnWlis.Add wli Set wli = New WorkLogItem wli.TaskID = 2 wli.PersonName = "Sally" wli.HoursWorked = 3 clnWlis.Add wli Set wli = New WorkLogItem wli.TaskID = 3 wli.PersonName = "Fred" wli.HoursWorked = 2.5 clnWlis.Add wli Set PopulateArray = clnWlis End Function Sub TestGetHoursWorked() Dim pwl As ProcessWorkLog Dim arrWli() As WorkLogItem Set pwl = New ProcessWorkLog Set pwl.WorkLogItems = PopulateArray() Debug.Print pwl.GetHoursWorked("Fred") End Sub |
That’s all!