Any way to test if item in an array is in an ArrayList?

skarden · #1 08-29-2023, 05:19 PM

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

gmaxey · #2 08-30-2023, 06:14 AM

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

skarden · #3 08-30-2023, 06:54 AM

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.

Guessed · #4 08-31-2023, 11:44 PM

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

I'm thinking if you are using this for a listbox then you already have an array to work with so the code can be a lot more direct than fiddling with dictionary and arraylist

vivka · #5 09-01-2023, 01:35 AM

Another interesting solution! Greg and Andrew, thank you (although I'm not skarden and that was not my question)!

gmaxey · #6 09-01-2023, 06:16 AM

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

Guessed · #7 09-03-2023, 10:35 PM

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

FWIW, you could go to town and add another option for case sensitivity.

gmaxey · #8 09-04-2023, 07:58 AM

Andrew,
Yes, good improvements. Thanks.

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