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

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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