Pass "array of strings" into a function??

R

Robert Crandal

I thought this would be easy to solve, but I am having trouble
finding information on how to define a VBA function (or sub)
that has an "array of strings" as an argument. Here is my
defintion so far:

Sub Sort_Array_of_Strings (ByRef Arr() As String)

' stuff in here

End Sub


Whenever I try passing an array of strings into this subroutine
I get a "type mismatch" type of error message.

Can anyone show me how to correctly pass an array of
strings into a function or sub? How do I define the function as well?

Thank you!
 
R

Rick Rothstein

Can anyone show me how to correctly pass an array of strings
to a function or sub? How do I define the function as well?

Here is an example for you to study (the subroutine names should tell you
what you need to know)...

Sub SubWithArrayArgument(Arr() As String)
MsgBox "UpperBound = " & UBound(Arr) & vbLf & "Last element = " &
Arr(UBound(Arr))
End Sub

Sub CallSubWithArrayArgument()
Dim MyArr() As String
MyArr = Split("one,two,three,four", ",")
SubWithArrayArgument MyArr
End Sub

Just run the CallSubWithArrayArgument subroutine (which could be a macro if
need be). It will create the MyArr array inside itself and then pass it to
the SubWithArrayArgument subroutine which will display the index for the
last element in that passed in array and also the value in that last
element. Obviously, the setup and passing mechanism would be the same for
functions as well. Hope this helps.

Rick Rothstein (MVP - Excel)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top