returning an array from a function
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 |
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 |
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 |
returning an array from a function
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 "qpg" wrote in message ... 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 |
All times are GMT +1. The time now is 01:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com