how to verify if an item exists in a array using excel vba?

Hi,

I need a code to verify if an item exists in an array. Then if exists do nothing, if doesn't exists then add to the array. Any idea?
LVL 1
joyacv2Asked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Collection.Count is the number of items in the collection.

Besides the above which is the normal way of doing things there is also 'Before' and 'After' parameters that can be used if you like to insert values at a particular place in the collection.


This code would return "Value 2" followed by "Value 1" because the second Add tells VBA to insert the item before "Key 1"

Dim MyCollection As New Collection

On Error Resume Next
MyCollection.Add "value 1", "Key 1"
MyCollection.Add "value 2", "Key 2", "Key 1" 
If Err.Number = 457 Then
    MsgBox "Key already in collevtion"
End If

MsgBox MyCollection(1)
MsgBox MyCollection(2)

Open in new window


To insert after you do

MyCollection.Add "value 2", "Key 2", "Key 1" ,, "key value to insert after"
0
 
Martin LissOlder than dirtCommented:
You can use a collection instead, Keys in a collection must be unique and you get error 457 if you try to add a duplicate. So you wouldn't do it this way but here's an example.


Dim MyCollection As New Collection

On Error Resume Next
MyCollection.Add "value 1", "Key 1"
MyCollection.Add "value 2", "Key 1"
If Err.Number = 457 Then
    MsgBox "Key already in collevtion"
End If

Open in new window

MyCollection.Count will be 1 at the end of the code.
0
 
joyacv2Author Commented:
Hi,

How a collection works, how i refer to an item later? How I know the dimension of the collection? How is different from an array? Thank You
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Martin LissOlder than dirtCommented:
Collections start at index 1 so this would return "Value 1" using the above code.

MsgBox MyCollection(1)
0
 
joyacv2Author Commented:
But how is differs from an array?

My problem is that all my codes are in arrays
0
 
joyacv2Author Commented:
In the code why value 2 gives an error if it's not equal to value 1?
0
 
joyacv2Author Commented:
EXCELLENT, THANK YOU FOR YOUR RESPONSE
0
 
Martin LissOlder than dirtCommented:
In the code why value 2 gives an error if it's not equal to value 1?
Because it checks the keys and not the values, so if you want to check for duplicate values make the key the same as the value. In other words

Dim MyCollection As New Collection

On Error Resume Next
MyCollection.Add "value 99", "value 99"
MyCollection.Add "value 1", "value 1"
MyCollection.Add "value 1", "value 1"
If Err.Number = 457 Then
    MsgBox "Key already in collection"
End If

Open in new window


In case it's not clear, the generalized code is

MyCollection.Add <some value>,<some key that must be a string>
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
 
Martin LissOlder than dirtCommented:
BTW one more example. You don't have to use a key so this is perfectly valid and you wind up with 2 items in the collection.

Dim MyCollection As New Collection

On Error Resume Next

MyCollection.Add "value 1"
MyCollection.Add "value 1"

Open in new window

0
 
joyacv2Author Commented:
PERFECT, SO USING THE SAME SOLVE MY PROBLEM!
0
 
Martin LissOlder than dirtCommented:
There's also a more advanced type of collection called a Dictionary but I don't have much experience with them.
0
 
joyacv2Author Commented:
for me, collection is new, i am working right with it, and works perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.