Array of objects

[nals14]

Hi,
I have a data entry form in excel. One sheet has a list of items. Each item has several subcomponents. I have created a class for each item. Each class is composed of 3 ints and a String value.

I have an array of this class. Lets call this class as Itemclass. Ideally I should use a list. I have no idea as to how to do that.

Basically what I would like to do is the following :-
Dim x as Itemclass
x = Itemclassarray(i):help

What should I include in my class to be able to perform regular assignments as shown above? In C++ we need a copy constructor / overload the operator. What should be done in VBA. If I can find out how to get this done then I can implement a link list too.

Can anyone help me here?

[mikerickson]

I think NEW in the declaration statement is the syntax you're looking for.
Dim componentArray(1 To 10) As New Itemclass

For i = 1 To 10
componentArray(i).subComponent1 = "widgit " & i
Next i

[nals14]

Thanks for your reply. In my post I did not make it clear enough.

I just want to know if it possible to assign 1 object to another object of the same class.

I have a dyanmically growing list of items. Users can modify, delete, sort this list. I didn't know how to implement the linked list. So I have an array.

I have an array of items that I would like to sort. I use bubble sort. So when I swapping 2 items from the array I do something like this.

Dim temp as itemclass

Set temp = itemarray(i)
Set itemarray(i+1) = itemarray(i)
Set itemarray(i) = temp

I get a compiler error. When I added items to the itemarray I did use the 'new' operator.

To overcome the problem I have a function :
CopyItem( ByRef source as Itemclass, ByRef dest as ItemClass )

source.var1 = dest.var1
source.var2 = dest.var2....

In my sorting routine I call this function instead of the Set functions :-
CopyItem(temp, itemarray(i) ) and so on.

So is this the only way to do it?

Is there a better way to maintain a dynamically changing list of items(classes) in VBA?

Thanks. Really appreciate your time and effort.

[mikerickson]

I'm not sure how you are assigning your new class's properties, but this test routine swaps two indices in the itemArray (class itemClass). Any one of the declarations for temp work.


Sub test()
Dim itemArray(1 To 3) As New itemClass
Dim i As Integer, j As Integer

Rem set test values
For i = 1 To 3
itemArray(i).arg1 = i & "one"
itemArray(i).arg2 = i & "two"
Next i
i = 1: j = 2


Dim temp As itemClass
Rem Dim temp As New itemClass
Rem Dim temp As Variant
Rem Dim temp As Object

Set temp = itemArray(i)
Set itemArray(i) = itemArray(j)
Set itemArray(j) = temp


Rem output result
Dim outStr As String
outStr = ""
For i = 1 To 3
outStr = outStr & vbCrLf & itemArray(i).arg1 & "-" & itemArray(i).arg2
Next i
MsgBox outStr
End Sub

[Bob Phillips]

If I am understanding correc tly, why not just keep a separate list of links, rather than assigning objects as properties of other objects of the same tyape.

And/or, what about a collection class.

[nals14]

Let me try that out. Thanks for the suggestion. When I did that the last time I remember getting an error. Will let you know what that is.


Thanks.:hi:


I'm not sure how you are assigning your new class's properties, but this test routine swaps two indices in the itemArray (class itemClass). Any one of the declarations for temp work.


Sub test()
Dim itemArray(1 To 3) As New itemClass
Dim i As Integer, j As Integer

Rem set test values
For i = 1 To 3
itemArray(i).arg1 = i & "one"
itemArray(i).arg2 = i & "two"
Next i
i = 1: j = 2


Dim temp As itemClass
Rem Dim temp As New itemClass
Rem Dim temp As Variant
Rem Dim temp As Object

Set temp = itemArray(i)
Set itemArray(i) = itemArray(j)
Set itemArray(j) = temp


Rem output result
Dim outStr As String
outStr = ""
For i = 1 To 3
outStr = outStr & vbCrLf & itemArray(i).arg1 & "-" & itemArray(i).arg2
Next i
MsgBox outStr
End Sub

[nals14]

Hi,
I am pretty new to VBA and just now found out that it has a collection class:doh: .

Will start googling now. If there are any articles you know of could you please post the links here please.

Thanks:hi:


If I am understanding correc tly, why not just keep a separate list of links, rather than assigning objects as properties of other objects of the same tyape.

And/or, what about a collection class.

[Bob Phillips]

It doesn't have a collection class, you craete a collection class that collects all of the instances of your object type, and you assign properties just like a normal collection.