0

In a question regarding VBA: Passing multiple values to Instr John Coleman posted a 'Contains' function that accepts multiple search strings that are then fed into Instr to check, this was great for me and works well. However, it required writing a list of the search strings at design time (as far as I can tell), and I wanted to be able to create dynamic lists of search strings gathered from various places on the speadsheet. So in his function I would call it like this;

If Contains(StringToSearch, "Alan", "Betty", "Arthur", True)

and I wanted to be able to call it more like this;

If Contains(StringToSearch, Range("B1:B3"), True)

I then tried to see if I could assemble my entries dynamically and then pass the result to his function but could not do that like this using 2 different methods;

Dim varClasses() As Variant          (Method 1)
Dim strClasses() As String           (Method 2)
For i = 0 To n
  strClasses(i) = Range("C5").Offset(0, i).Value '(Method 1)
  varClasses(i) = Range("C5").Offset(0, i).Value '(Method 2)
Next i
If Contains(sEntry, strClasses, True) Then       '(Method 1)
If Contains(sEntry, varClasses, True) Then       '(Method 2)
  MsgBox "Found"
End If

So I want to be able to create a list of search terms that will be accepted by the function to do the multiple word searches. Thanks for reading.

AHeyne
  • 3,377
  • 2
  • 11
  • 16
Ramses505
  • 3
  • 2
  • On top of the methods listed in the duplicate question, if your main use case is passing an existing array, just remove the `ParamArray` keyword from the function definition. And when you do need to pass a design-time array to such function, pass `Array("Alan", "Betty", "Arthur")`. – GSerg Jan 11 '21 at 12:31
  • Thanks I have read and will re read. My point was I don't want to pass a hard coded set of search terms - I want to form them up dynamically and then pass that to the function - IE not like Array("Alan", "Betty", "Arthur") but like Array = Range("B1:B3). – Ramses505 Jan 11 '21 at 13:41
  • Then just remove `ParamArray`. – GSerg Jan 11 '21 at 13:47
  • You would benefit by reading up on Scripting.Dictionary object to collect your strings and the the .Items method of the Scripting.Dictionary to provide a list of strings to your function. – freeflow Jan 11 '21 at 15:01

0 Answers0