Making an array of objects

[WhiteRabbit]

I have a Data sheet filled with resources. Each resource has a name, location, and title. I made a class called Resource [see below]. I'm trying to iterate through the Data sheet and store all the resource information into objects, which are then stored in an array.

So there's a Name column, Title, and Location. I get the number of rows in the document, and for each row i look for the name, the title, and location of the resource, make a new object, and store it in the array. Then I go to the next row, until there's no more data.

Here's what I have so far. I'd really appreciate any advice. Obviously this code isn't functional yet.

Here's the class:

Private pName As String
Private pCity As String
Private pTitle As String


''''''''''''''''''''''
' Name property
''''''''''''''''''''''
Public Property Get Name() As String
    Name = pName
End Property
Public Property Let Name(Value As String)
    pName = Value
End Property


''''''''''''''''''''''
' City property
''''''''''''''''''''''
Public Property Get City() As String
    City = pCity
End Property
Public Property Let City(Value As String)
    pCity = Value
End Property


''''''''''''''''''''''
' Title property
''''''''''''''''''''''
Public Property Get Title() As String
    Title = pTitle
End Property
Public Property Let Title(Value As String)
    pTitle = Value
End Property

Here's what I have so far of the Sub.

Sub ArrayPractice()


Dim r As Integer
Dim i As Integer
Dim a As Integer
Dim numberOfRows As Integer
Dim names() As String


r = 2 'row that i start looping from
a = 0




numberOfRows = ActiveSheet.UsedRange.Rows.Count


Dim resourceArray(numberOfRows) As Resource


For i As Integer = 0 to numberOfRows 'Getting an error here.
resourceArray(i) = New Resource
' Make the new Resource object here??


Next i








ReDim names(0) 'redimension the array to size zero


Do Until Cells(r, 1).Value = ""
names(i) = Cells(r, 1).Value


i = i + 1 'increment index of the array
ReDim Preserve names(i) 'size is equal to the next index.


r = r + 1
Loop


''''print the array!''''


For i = 0 To UBound(names) - 1 'ubound is equal to one number higher






Cells(r, 2).Value = names(i)
r = r + 1


Next i








End Sub

 

[Andrew Poulsom]

Maybe this will help you:

Classes In VBA

 

[WhiteRabbit]

Maybe this will help you:

Classes In VBA

I actually went through that tutorial. It was helpful but, I'm not sure that storing in a collection is the way I want to go.

 

[Andrew Poulsom]

What other way is there?

 

[WhiteRabbit]

What other way is there?

Okay, here's what I got so far. It selects the cell on the By Market worksheet but my collection may be blank because nothing is printing. I'm not getting any errors so, is there anything wrong with my code?

Sub ArrayPractice()


Dim r As Integer
Dim i As Integer
Dim a As Integer
Dim numberOfRows As Integer
Dim names() As String
Dim resourceCollect As Collection


Dim Emp As Resource
Dim Count As Long


Set resourceCollect = New Collection


a = Worksheets("DATA").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
r = 2 'row that i start looping from
i = 0


For Each Emp In resourceCollect


For Count = 0 To a
Emp.Name = Cells(r, 1).Value
Emp.Title = Cells(r, 2).Value
Emp.City = Cells(r, 3).Value
r = r + 1
Next Count
Next Emp


''''print the array!''''


Sheets.Add.Name = "By Market"
Sheets.Add.Name = "By Resource Level"
Sheets.Add.Name = "By Resource Manager"






Sheets("By Market").Select
Range("C36").Select
r = 36
For Each Emp In resourceCollect
If Emp.City = "Dallas" Then
Cells(r, 3).Select
Debug.Print Emp.Name
r = r - 1
End If
Next Emp


Range("D36:D36").Select
r = 36
For Each Emp In resourceCollect
If Emp.City = "Denver" Then
Cells(r, 4).Select
Debug.Print Emp.Name
r = r - 1
End If
Next Emp


Range("E36:E36").Select
r = 36
For Each Emp In resourceCollect
If Emp.City = "Houston" Then
Cells(r, 5).Select
Debug.Print Emp.Name
r = r - 1
End If
Next Emp


Range("F36:F36").Select
r = 36
For Each Emp In resourceCollect
If Emp.City = "Kansas City (Missouri)" Then
Cells(r, 6).Select
Debug.Print Emp.Name
r = r - 1
End If
Next Emp


End Sub

 

[Andrew Poulsom]

You're not adding anything to the collection.

 

[WhiteRabbit]

You're not adding anything to the collection.

Yeah I noticed that. But, I still put in

For Count = 0 To a
Emp.Name = Cells(r, 1).Value
Emp.Title = Cells(r, 2).Value
Emp.City = Cells(r, 3).Value
[COLOR=#ff0000]resourceCollect.Add Emp[/COLOR]
r = r + 1
Next Count
Next Emp

But nothing is printing.

 

[Andrew Poulsom]

Why don't you adapt the code in the link I posted. Or this one?

Daily Dose of Excel » Blog Archive » Custom Collection Class