Making an array of objects

WhiteRabbit

New Member
Joined
Apr 15, 2013
Messages
4
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:
Code:
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.

Code:
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

WhiteRabbit

New Member
Joined
Apr 15, 2013
Messages
4

ADVERTISEMENT

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?

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
 

WhiteRabbit

New Member
Joined
Apr 15, 2013
Messages
4
You're not adding anything to the collection.

Yeah I noticed that. But, I still put in

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top