returning an array from a function
Perhaps this...
Sub test()
Dim x() As String
x = RangeVal(Selection)
Debug.Print x(0)
Debug.Print x(1)
Debug.Print x(2)
Debug.Print x(3)
End Sub
Function RangeVal(rng As Range) As Variant
Dim AddString As String
Dim addStart As String
Dim addEnd As String
Dim addVals(0 To 3) As String
AddString = rng.Address(, , xlR1C1)
addStart = Left(AddString, InStr(1, AddString, ":") - 1)
addEnd = Right(AddString, InStr(1, AddString, ":") - 1)
addVals(0) = CInt(Replace(Left(addStart, InStr(1, addStart, "C") - 1), "R",
""))
addVals(1) = CInt(Replace(Right(addStart, InStr(1, addStart, "C") - 1), "C",
""))
addVals(2) = CInt(Replace(Left(addEnd, InStr(1, addEnd, "C") - 1), "R", ""))
addVals(3) = CInt(Replace(Right(addEnd, InStr(1, addEnd, "C") - 1), "C", ""))
Debug.Print "start " & addStart
Debug.Print "end " & addEnd
'Debug.Print addVals(0)
'Debug.Print addVals(1)
'Debug.Print addVals(2)
'Debug.Print addVals(3)
RangeVal = addVals()
End Function
--
HTH...
Jim Thomlinson
"qpg" wrote:
I am looking for an example of how to return an array from a function
to the calling subroutine. I want to call the function from the sub
routine and have it return 4 values. Is this possible or do I need
another approach.
This is basically where I am:
Sub test()
Dim x As Integer
x = RangeVal(Selection)
Debug.Print x
End Sub
Function RangeVal(rng As Range) As Variant
Dim AddString As String
Dim addStart As String
Dim addEnd As String
Dim addVals(0 To 3) As Variant
AddString = rng.Address(, , xlR1C1)
addStart = Left(AddString, InStr(1, AddString, ":") - 1)
addEnd = Right(AddString, InStr(1, AddString, ":") - 1)
addVals(0) = CInt(Replace(Left(addStart, InStr(1, addStart, "C") - 1),
"R", ""))
addVals(1) = CInt(Replace(Right(addStart, InStr(1, addStart, "C") -
1), "C", ""))
addVals(2) = CInt(Replace(Left(addEnd, InStr(1, addEnd, "C") - 1),
"R", ""))
addVals(3) = CInt(Replace(Right(addEnd, InStr(1, addEnd, "C") - 1),
"C", ""))
Debug.Print "start " & addStart
Debug.Print "end " & addEnd
'Debug.Print addVals(0)
'Debug.Print addVals(1)
'Debug.Print addVals(2)
'Debug.Print addVals(3)
Set RangeVal = addVals()
End Function
|