Returning Multiple Arguments from an Excel VBA Function

[aussie_craig]

Hi Again.
I had such a good response last time I thought I would ask you gurus
another one.

I have written some VBA code to manipulate some strings in a
spreadsheet.
It works well but is getting quite long, and I am beginning to reuse
parts of it. So I thought I would break one part into a function.

I would like to pass 1 string argument to my function, perform some
fairly intensive operations on the string and return with 2 string
arguments.
I think its possible to return with two argumnets - but the syntax has
thrown me.

I think it has something to do with dimensioning the Function name as a
Variant or a 2 element array ?

Here's a rough example along the lines of what I want:

Sub Blah()

MyString = "123456"
AddressSplit (MyString)

End Sub

Function AddressSplit (TargetString)
AddressSplit(1) = "Test1"
AddressSplit(2) = "Test2"

End Function


How do I return the 2 arguments to the Main Subroutine so I can use
them ?
Thanks in Advance for any help.

 

[Robin Hammond]

Craig,

It's probably more sensible to use a sub. e.g.

Sub Test()
Dim strFirstName As String
Dim strLastName As String
SplitExample "Robin Hammond", strFirstName, strLastName
MsgBox "First Name: " & strFirstName
MsgBox "Last Name: " & strLastName
End Sub

Sub SplitExample(ByVal strInput As String, ByRef strFirstName As String, _
ByRef strLastName As String)
strFirstName = Left(strInput, InStr(strInput, " ") - 1)
strLastName = Mid(strInput, InStr(strInput, " ") + 1)
End Sub

Robin Hammond
www.enhanceddatasystems.com

 

[NickHK]

Craig,
You can return an array from a function:

Private Sub CommandButton1_Click()
MsgBox AddressSplit ("qwerty")(0)
End Sub

Private Function AddressSplit (argStr As String) As String()
Dim RetArr(1) As String

RetArr(0) = Left(argStr, 3)
RetArr(1) = Mid(argStr, 4)
AddressSplit = RetArr
End Function