Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

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?
0
joyacv2
Asked:
joyacv2
  • 7
  • 6
1 Solution
 
Martin LissRetired ProgrammerCommented:
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
 
Martin LissRetired ProgrammerCommented:
Collections start at index 1 so this would return "Value 1" using the above code.

MsgBox MyCollection(1)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
Martin LissRetired ProgrammerCommented:
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
 
joyacv2Author Commented:
EXCELLENT, THANK YOU FOR YOUR RESPONSE
0
 
Martin LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
 
Martin LissRetired ProgrammerCommented:
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 LissRetired ProgrammerCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now