Array question

  • Thread starter Thread starter Karsten Jung
  • Start date Start date
K

Karsten Jung

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!
 
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
 
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
 
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
 
Back
Top