returning an array from a function
On Jun 10, 3:31 pm, 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
No. Functions return values. And a range variable has to be set to
an actual range. You cant' just populate a range variable with values
without knowing into which cells those values are assigned. Change
your Function to a Subroutine, declare a range variable and you can
populate your range from an array using For - Next, and range.Cells or
range.Offset
SteveM
|