73

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?

TylerH
  • 20,799
  • 66
  • 75
  • 101
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
  • sounds like your do_something signature has byref specified but it should be byval instead? – Phil C Nov 19 '10 at 18:36

5 Answers5

126

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)
BigBen
  • 46,229
  • 7
  • 24
  • 40
Bobsickle
  • 1,689
  • 1
  • 12
  • 15
  • This will do nothing since sArray is empty?!?! – Black Nov 10 '16 at 09:16
  • 2
    @EdwardBlack - He only gave code snippets that are relevant to the discussion. Presumably additional code between the dimensioning of sArray and the For Each loop would actually define it. But how it is defined is not important for the question. – Paul Sinclair Jan 24 '17 at 20:43
44

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
Fink
  • 3,356
  • 19
  • 26
  • +One,use of LBound and UBound is clear!! Though another alternative is using For Each block.. – Learner Mar 11 '16 at 09:02
  • The `lbound` and `ubound` is useful if you need the index i in the loop and can be another use case compared to `foreach`.. – Timo Aug 26 '20 at 14:34
8

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
Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
5

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
Sebastian Viereck
  • 5,455
  • 53
  • 53
2

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

For i = 1 to UBound(nameofthearray)
   your code here
next i
user2922434
  • 57
  • 4