Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning an array from the INDEX function | Excel Worksheet Functions | |||
Returning an array from a function | Excel Programming | |||
[Newbie question] Returning an array from a function | Excel Programming | |||
function returning array | Excel Programming | |||
returning an array from a custom function | Excel Programming |