excel - 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)