returning an array from a function

[qpg]

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

 

[SteveM]

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

 

[Jim Thomlinson]

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

 

[Rick Rothstein \(MVP - VB\)]

You have two problems stopping your code from working...

1) Remove the Set keyword from the last line of your function (Set applies
to objects and an array is not an object)

2) Since you declared your array to be returning a Variant, you cannot
assign its return value to an Integer. Change your Dim statement for the X
variable from Integer to Variant. Next, because you are returning an array,
you can't just "print the function", you have to specify an element within
the array to print. So, print X(0), X(1), etc., not just X.

Rick