update collection of arrays

[RB Smissaert]

Is it possible to update the value of an array element if that array is held
by a collection?
It seems not:


Sub test2()

Dim oColl As Collection
Dim arr(1 To 10)

Set oColl = New Collection

arr(1) = 5

oColl.Add arr, "a"

MsgBox oColl(1)(1)

oColl(1)(1) = 6

MsgBox oColl(1)(1)

End Sub


Is there any solution for this?
The whole purpose is to store data with nodes of a treeview control and to
have a robust
linkage between the nodes and the stored data.


RBS

 

[Norman Jones]

Hi Bart,

A collection only has three methods: Add, Remove and Item, so I do not think
that a that a collection element can be changed, irrespective of whether an
array is involved or not.

 

[Jim Cone]

RBS,

You can do something close to what you want with the
Scripting Dictionary object...
'-------------
Sub test3()
'Requires project reference to "MicrosoftScriptingRuntime"
'Note that the order of the Key and Item arguments are the reverse
'of those in a Collection.
Dim oDic As Scripting.Dictionary
Dim arr(1 To 10)
Set oDic = New Scripting.Dictionary

arr(1) = 5
oDic.Add "a", arr()
MsgBox oDic("a")(1)

arr(1) = 6
oDic("a") = arr()
MsgBox oDic("a")(1)

Set oDic = Nothing
End Sub
'------------------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"RB Smissaert" <[email protected]>
wrote in message
Is it possible to update the value of an array element if that array is held
by a collection?
It seems not:

Sub test2()
Dim oColl As Collection
Dim arr(1 To 10)
Set oColl = New Collection
arr(1) = 5
oColl.Add arr, "a"
MsgBox oColl(1)(1)

oColl(1)(1) = 6
MsgBox oColl(1)(1)
End Sub

Is there any solution for this?
The whole purpose is to store data with nodes of a treeview control and to
have a robust
linkage between the nodes and the stored data.

RBS

 

[RB Smissaert]

Jim,

Yes, I had a look at the dictionary object, but what is misses is the option
to add an item after a known other
item and that is what I need as it has to be in the same order as the nodes
in the treeview.

Will look again in putting the data as a 1-D array in node tags. That would
be the most robust linkage with the
nodes and would save me having a routine for clearing data after a node was
deleted.
The other option I can think of now is storing it in a sheet.

RBS

 

[RB Smissaert]

Norman,

Yes, you are right, the arrays just confused the fundamental issue here.

RBS

 

[RB Smissaert]

I think using the node tag looks the simplest option:

Sub AddNodeTag(oNode As Node)

Dim arr(1 To 2, 1 To 35)

With oNode
arr(1, 1) = .KEY
arr(2, 2) = .KEY
.Tag = arr
End With

End Sub



And then when adding a node for example:

Dim arrTag

Set nodX = _
.TreeView1.Nodes.Add(, tvwFirst, LNK, "New Report", 1)

AddNodeTag nodX

arrTag = nodX.Tag

arrTag(2, 10) = "testing"

nodX.Tag = arrTag


The important bit here is to update the tag array values indirectly as doing
the updates directly like this:

nodX.Tag(2, 10) = "testing"

doesn't work. I remember it crashed Excel when I tried this a while ago, but
it doesn't do that now, but the
array values are not updated.


RBS

 

[Dana DeLouis]

...the option to add an item after a known other

item and that is what I need...

Hi. For Collections, one technique is to store the "Key" name as part of
the data. It's not ideal, but can be a work-around.
You will need some error checking with mixed data types in the collection.

Sub Demo()
Dim oColl As Collection
Dim arr(0 To 10)
Dim p As Long

Set oColl = New Collection

arr(0) = "a" ' Index 0 for key...
arr(1) = 5

oColl.Add arr, "a"
oColl.Add 3.14, "Pi"
oColl.Add 1.23, "Sales"

'// Update "a"
arr(1) = 6
For p = 1 To oColl.Count
If oColl(p)(0) = "a" Then
oColl.Remove ("a")
oColl.Add arr, "a", Before:=p
Exit For
End If
Next
End Sub

Another technique is to store the key name and your data array inside
another array...
oColl.Add Array("a", arr), "a"

Hope this helps. :>)

 

[RB Smissaert]

Thanks for the tip.
I think storing the arrays with in the node tag works fine and it can't be
any more robust linkage with the nodes.
Will try this for now.

RBS