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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to vba