Array question

[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!

 

[paul.robinson]

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

 

[Karsten Jung]

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

 

[Karsten Jung]

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

 

[paul.robinson]

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