VBA Looping through a dynamically generated string array (type mismatch)

Question

I am using this function to generate a string array and it is working correctly to output a string array:

Dim tmp As String
Dim arr() As String

If Not Selection Is Nothing Then
   For Each cell In Selection
      If (cell <> "") And (InStr(tmp, cell) = 0) Then
        tmp = tmp & cell & "|"
      End If
   Next cell
End If

If Len(tmp) > 0 Then tmp = Left(tmp, Len(tmp) - 1)

Debug.Print tmp
arr = Split(tmp, "|")

The problem I am having is I am then trying to use the generated array to loop through the set of strings I am searching for in a spreadsheet. I need to dynamically generate this array. The only way I know how to loop through the array is using a variant and have written the code like this:

'Create IRAF array
Dim test_col As Range
Set test_col = Range(CStr(testSearch.Offset(1, 0).Address), Range(CStr(testSearch.Address)).End(xlDown))
test_col.Select
test_arr = create_array(test_col)
Debug.Print test_arr

'Loop for the Test Cycles
Dim iGen As Variant
Dim iCurrent As Variant
Dim i As Integer
iGen = test_arr
For Each iCurrent In iGen
    Set Search = Cells.Find(What:=iCurrent, _
        After:=Cells(1, 1), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)
    If Not Search Is Nothing Then...

I realize the issue I am having is that the array is being returned from the function as an array of strings and then I am trying to set a Variant type equal to the string array. Is there some way to convert the string array to a variant or otherwise create a loop for the dynamically generated string array? The problem I am trying to work around is that I need to dynamically generate the array of strings since the string search criteria could change on a file by file basis.

Tell us again why you need to declare as Variant?

MacroMarc
– MacroMarc

2017-06-26 19:09:55 +00:00
Commented Jun 26, 2017 at 19:09 — MacroMarc
– MacroMarc, Commented Jun 26, 2017 at 19:09

Brian M Stafford · Accepted Answer · 2017-06-26 19:04:06Z

1

It sounds like your main challenge is how to loop through your string array. The following code shows how to do this:

   For i = LBound(test_arr) To UBound(test_arr)
      'your logic here
      'you would refer to an element like this test_arr(i)
   Next

edited Jun 26, 2017 at 19:04

answered Jun 26, 2017 at 18:58

Brian M Stafford

8,9432 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

M. Stag Over a year ago

I realized I wasn't returning the array due to not setting the function var name to the array. No issues now, thanks for this syntax though, will be helpful in the future!

Collectives™ on Stack Overflow

VBA Looping through a dynamically generated string array (type mismatch)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related