Array question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karsten Jung - 23 Mar 2006 16:22 GMT

Hello together.

How can I create an assoc array in excel via vba?

Should be something like this:

myArray("cats") = 1
myArray("dogs") = 2

I have the "Collection" but it's very poor. I cannot reset the value
there, e.g.

Dim c as New Collect
c.Add "1", "dogs"

c.Items("dogs") = c.Items("dogs") + 1 'Will NOT work, but I have the
change this values.

Can anybody help me?

Thanks a lot!

Reply to this Message

paul.robinson@it-tallaght.ie - 23 Mar 2006 16:36 GMT

Hi
This should work

temp = c.item("dogs")+1
c.remove "dogs"
c.add temp, "dogs"

Adding an existing element (with the same key) to a collection
generates an error, so you must remove it first.

regards
Paul

Reply to this Message

Karsten Jung - 23 Mar 2006 16:38 GMT

Thanks,

but what happends, when "dogs" is not set?

Maybe I read 3 cats and then the first dog, I musst set it to 1

Reply to this Message

Karsten Jung - 23 Mar 2006 16:40 GMT

I have to check whether dog ist "in" the collection or not, but how to
do?

Reply to this Message

paul.robinson@it-tallaght.ie - 24 Mar 2006 09:39 GMT

Hi
Use the fact that adding the key, if it is already there, creates an
error

On Error Resume Next
'maybe want a loop here?
err.clear
c.add 1, "dogs"
if error.number<>0 then
temp = c.item("dogs")+1
c.remove "dogs"
c.add temp, "dogs"
end if
'next
on error goto 0

Above is untested. I don't THINK the first add will actually do the add
if an error is generated...
Err.clear is required to catch the error each time. Err also exists as
long as the application is open, so it is always a good idea to clear
it if using error.number<>0

regards
Paul

Reply to this Message