#1
|
|||
|
|||
Any way to test if item in an array is in an ArrayList?
I'm trying to see if I can come up with a way to tell if an item exists in a dictionary without having to iterate through it with a For Each type of loop. My idea was to put the items into an ArrayList and then use ArrayList.Contains or ArrayList.IndexOf to see if it was in there. I can prove that I've put the items into the ArrayList as an array, but I can't figure out the syntax to then test if it is in there using either ArrayList.Contains or ArrayList.IndexOf of.
Below is an example of using .Contains. You'll see the Debug.Print proves it is in there in an array, but I can't figure out how to get .Contains to find it or .IndexOf to find it. So am I correct (sadly) that there is now such way? Sub dicItemExists_Test() On Error GoTo lblError Dim i As Long Dim arList As Object Dim dicTest As Object Set arList = CreateObject("System.Collections.Arraylist") Set dicTest = CreateObject("Scripting.Dictionary") dicTest.Add "Key2", "Item 2" dicTest.Add "Key3", "Item 3" dicTest.Add "Key1", "Item 1" arList.Add dicTest.Items Debug.Print "arList.Item(0)(0) = " & arList.Item(0)(0) 'This proves "Item 2" is in there If arList.Contains("Item 2") Then MsgBox "Item 1 was found", vbInformation + vbOKOnly, "ArrayList test" Else MsgBox "Item 1 was not found", vbInformation + vbOKOnly, "ArrayList test" End If lblExit: Set dicTest = Nothing Set arList = Nothing Exit Sub lblError: Debug.Print "Got error: " & Err.Number & ", " & Err.Description GoTo lblExit End Sub |
#2
|
|||
|
|||
Code:
Sub dicItemExists_Test() Dim i As Long Dim arList As Object Dim dicTest As Object On Error GoTo lblError 'arList.contains will tell you if an item exists in the list (not if in an arrary that is part of the list). Set arList = CreateObject("System.Collections.Arraylist") arList.Add "Apples" arList.Add "Peaches" arList.Add "Pears" MsgBox arList.Contains("Pears") 'dicTest.Exists will tell you if a "Key" exists in a dictionary Set dicTest = CreateObject("Scripting.Dictionary") dicTest.Add "Key2", "AAA" dicTest.Add "Key3", "BBB" dicTest.Add "Key1", "CCC" MsgBox dicTest.Exists("Key1") 'So, you could use" Set dicTest = CreateObject("Scripting.Dictionary") dicTest.Add "AAA", "AAAA" dicTest.Add "BBB", "BBB" dicTest.Add "CCC", "CCC" MsgBox dicTest.Exists("CCC") 'AFAIK, the only way to check if a "value" exists is to loop as you are trying to avoid. lblExit: Set dicTest = Nothing Set arList = Nothing Exit Sub lblError: Debug.Print "Got error: " & Err.Number & ", " & Err.Description GoTo lblExit End Sub |
#3
|
|||
|
|||
Hey Greg:
I posted it here instead of asking you directly as I didn't want to bother you. But it's GREAT to see you are still so active. Hope all is well with you. You just confirmed what I was afraid was true. What I'm actually trying to do is create a better BuildingBlock replacement where the document name is the "Template" name and in it I have a bunch of Content Controls where I'm using the CC's Tag to store a "Category" and BB's name like this: Category~Name. (Just BTW, I'm using the CC's title to indicate if this CC holds the BB's Description, Insert Method or Text). Since there could be many entries for each Category when I parse the Category out I then store it into a dictionary (dicCats) with the Category as the Key, first using an If test of: If Not dicCats.Exists "[Parsed Category]" Then dicCats.Add "[Parsed Category"], "1" 'Also, BTW, the value is actually meaningless to me End if When I go to populate my UserForm ListBox for the Topic's Categories I can just dump in the dicCats Keys. |
#4
|
||||
|
||||
I would do this by converting the ArrayList to an Array and then use Join with a delimiter to get all the contents into one string. Then use InStr to find out if the element exists. For example
Code:
Sub dicItemExists_Test() Dim i As Long, dicTest As Object Dim arList As Object, arr As Variant, str As Variant, sFind As String Set dicTest = CreateObject("Scripting.Dictionary") Set arList = CreateObject("System.Collections.Arraylist") dicTest.Add "Key2", "Item 2" dicTest.Add "Key3", "Item 3" dicTest.Add "Key1", "Item 1" arList.Add dicTest.Items arr = arList.ToArray str = "|" & Join(arr(0), "|") & "|" sFind = "|" & "Item 4" & "|" Debug.Print InStr(str, sFind) > 0 sFind = "|" & "Item 3" & "|" Debug.Print InStr(str, sFind) > 0 End Sub
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#5
|
|||
|
|||
Another interesting solution! Greg and Andrew, thank you (although I'm not skarden and that was not my question)!
|
#6
|
|||
|
|||
Andrew, sweet!
That solution should be booked as a function! Here is my stab: Code:
Sub Test() Dim oDic As Object Set oDic = CreateObject("Scripting.Dictionary") oDic.Add "1", "Apples" oDic.Add "2", "Blue Berries" oDic.Add "3", "Cherries" 'Where the "Exists" method tells us if a key is present in a dictionary ... MsgBox oDic.Exists("1") '... the following function tells us if a value exists MsgBox fcnDicValueExists(oDic, "Cherries") lbl_Exit: Set oDic = Nothing Exit Sub End Sub Function fcnDicValueExists(oDicPassed, varValue) As Boolean 'Created from suggestions offered by Andrew Lockton in the Word VBA Forum. Dim oArrList As Object, varMembers, varString 'Convert dictionary list members to an ArrayList Set oArrList = CreateObject("System.Collections.Arraylist") oArrList.Add oDicPassed.Items 'Convert ArrayList to array varMembers = oArrList.ToArray 'Join array element members to a delimited string varString = Join(varMembers(0), "|") & "|" 'Test if value found in string fcnDicValueExists = InStr(varString, varValue & "|") > 0 lbl_Exit: Exit Function End Function |
#7
|
||||
|
||||
My solution is based off a post I once saw on another forum (probably Woody's or Eileen's lounge) although I didn't have the good manners or time to go and find it to give kudos to whoever it was that first thought to join an array.
Making functions like this gets me all pedantic on a never ending loop of 'what ifs'. I agree it would work as a function but I would add a couple of alterations to make it slightly more useful in edge situations. I think the separator would need to be an optional component (in case '|' is used in the dictionary values). Also, I think you need the varString (and InStr find) to start and finish with the separator so you don't get false hits eg "Blue Berries|Fig|" would say that "Berries|" is a hit although I would consider it a miss. This includes those modifications to Greg's function to deal with those minor issues Code:
Function fcnDicValueExists(oDicPassed, varValue, Optional sSep As String = "|") As Boolean 'Function by Greg Maxey 'Created from suggestions offered by Andrew Lockton in the Word VBA Forum. Dim oArrList As Object, varMembers, varString 'Convert dictionary list members to an ArrayList Set oArrList = CreateObject("System.Collections.Arraylist") oArrList.Add oDicPassed.Items varMembers = oArrList.ToArray 'Convert ArrayList to array varString = sSep & Join(varMembers(0), sSep) & sSep 'Join array element members to a delimited string fcnDicValueExists = InStr(varString, sSep & varValue & sSep) > 0 'Test if value found in string lbl_Exit: Exit Function End Function
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#8
|
|||
|
|||
Andrew,
Yes, good improvements. Thanks. |
Tags |
arraylist contains, dictionary, indexof |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Test, just a test | Thomasordef | Office | 0 | 07-25-2019 10:43 AM |
Student's t-test (T.TEST): how to get p value? | seanspotatobusiness | Excel | 1 | 07-20-2018 05:46 AM |
How do I make a checkbox or dropdown item reference data in that specific section of the data array? | dhare | Excel Programming | 2 | 02-24-2016 12:36 PM |
Copying data related to one item to worksheet with many instances of the same item | nmp13 | Excel | 3 | 02-06-2016 02:13 AM |
Convert String Array to Integer Array from a User Input? | tinfanide | Excel Programming | 4 | 12-26-2012 08:56 PM |