How can I use a for each loop on an array?

Question

I have an array of Strings:

Dim sArray(4) as String

I am going through each String in the array:

for each element in sarray
  do_something(element)
next element

do_something takes a string as a parameter

I am getting an error passing the element as a String:

ByRef Argument Mismatch

Should I be converting the element to a String or something?

Answer 1

Element needs to be a variant, so you can't declare it as a string. Your function should accept a variant if it is a string though as long as you pass it ByVal.

Public Sub example()
    Dim sArray(4) As string
    Dim element As variant

    For Each element In sArray
        do_something element
    Next element
End Sub


Sub do_something(ByVal e As String)
    
End Sub

The other option is to convert the variant to a string before passing it.

  do_something CStr(element)

Answer 2

A for each loop structure is more designed around the collection object. A For..Each loop requires a variant type or object. Since your "element" variable is being typed as a variant your "do_something" function will need to accept a variant type, or you can modify your loop to something like this:

Public Sub Example()

    Dim sArray(4) As String
    Dim i As Long

    For i = LBound(sArray) To UBound(sArray)
        do_something sArray(i)
    Next i

End Sub

Answer 3

I use the counter variable like Fink suggests. If you want For Each and to pass ByRef (which can be more efficient for long strings) you have to cast your element as a string using CStr

Sub Example()

    Dim vItm As Variant
    Dim aStrings(1 To 4) As String

    aStrings(1) = "one": aStrings(2) = "two": aStrings(3) = "three": aStrings(4) = "four"

    For Each vItm In aStrings
        do_something CStr(vItm)
    Next vItm

End Sub

Function do_something(ByRef sInput As String)

    Debug.Print sInput

End Function

Answer 4

what about this simple inArray function:

Function isInArray(ByRef stringToBeFound As String, ByRef arr As Variant) As Boolean
For Each element In arr
    If element = stringToBeFound Then
        isInArray = True
        Exit Function
    End If
Next element
End Function

Answer 5

If alternatives are acceptable for this case, I would rather suggest UBound :

For i = 1 to UBound(nameofthearray)
   your code here
next i