Returning Multiple Arguments from an Excel VBA Function
 

Thanks Nick.
How do I access the 2 returned values in the Main Routine ?

Can I reference them in the Main Routine by AddressSplit(0)
AddressSplit(1) and if so do I need to Dim AddressSplit in a particla
manner in the Main Routine

ie
Private Sub CommandButton1_Click()
MsgBox AddressSplit ("qwerty")(0)
A = AddressSplit(0)
B = AddressSplit(1)
End Sub


NickHK Wrote:
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



"aussie_craig" wrote i
message
...

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 synta
has
thrown me.

I think it has something to do with dimensioning the Function name a
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.


--
aussie_craig

------------------------------------------------------------------------
aussie_craig's Profile:
http://www.excelforum.com/member.php...o&userid=15491
View this thread
http://www.excelforum.com/showthread...hreadid=271071


--
aussie_crai
-----------------------------------------------------------------------
aussie_craig's Profile: http://www.excelforum.com/member.php...fo&userid=1549
View this thread: http://www.excelforum.com/showthread.php?threadid=27107

Returning Multiple Arguments from an Excel VBA Function
 

Craig,
For brevity I wrote the code as such to show the basic. Actually, you may
want to:

Private Sub CommandButton1_Click()
Dim Answers() as string

Answers=AddressSplit ("qwerty")
'Do what you want with Answers(0), Answers(1),,,Answers(n)
'Maybe
MsgBox Answers(0) & ", " Answers(1)
End Sub

Returning an array, rather than using ByRef argument allows for expansion of
the number of return elements. Whether this is important will depend on what
you trying to achieve.

NickHK

"aussie_craig" wrote in message
...

Thanks Nick.
How do I access the 2 returned values in the Main Routine ?

Can I reference them in the Main Routine by AddressSplit(0) &
AddressSplit(1) and if so do I need to Dim AddressSplit in a particlar
manner in the Main Routine

ie
Private Sub CommandButton1_Click()
MsgBox AddressSplit ("qwerty")(0)
A = AddressSplit(0)
B = AddressSplit(1)
End Sub


NickHK Wrote:
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



"aussie_craig" wrote in
message
...

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.


--
aussie_craig

------------------------------------------------------------------------
aussie_craig's Profile:
http://www.excelforum.com/member.php...o&userid=15491
View this thread:
http://www.excelforum.com/showthread...hreadid=271071



--
aussie_craig
------------------------------------------------------------------------
aussie_craig's Profile:
http://www.excelforum.com/member.php...o&userid=15491
View this thread: http://www.excelforum.com/showthread...hreadid=271071