Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Information from Functions
Hello --
I'm looking for ideas of how to get multiple values from a function. Ideally, I'm looking for a function to return an array, but this hasn't seemed to work. I'd appreciate suggestions/ideas an/or solutions. At the moment, I'm considering returning a string containing comma-separated values, then processing this string. Is there a better solution? Thanks, Austin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Information from Functions
On Thu, 20 Nov 2003 10:36:54 -0800, Austin Amaya wrote:
Hello -- I'm looking for ideas of how to get multiple values from a function. Ideally, I'm looking for a function to return an array, but this hasn't seemed to work. I'd appreciate suggestions/ideas an/or solutions. At the moment, I'm considering returning a string containing comma-separated values, then processing this string. Is there a better solution? Thanks, Austin If the return values are all of the same type, you can declare an empty array and assign the return value to that. (This works in Excel 2000. No clue about other versions.) Sub foo() Dim x As Integer Dim y() As Byte x = 12345 y = bar(x) For n = 0 To UBound(yz) MsgBox y(n) Next End Sub Function bar(a As Integer) As Byte() 'returns an array Dim b(1) As Byte b(0) = Abs(a \ 256) b(1) = Abs(a Mod 256) bar = b End Function Alternately, you can pass the variables that will hold the return values by reference and skip arrays, so you can return multiple data types. (Note that the function has become a sub, since it doesn't need to return a value.) Sub foo() Dim x As Integer, y As Byte, z As String x = 12345 bar x, y, z MsgBox y & " " & z End Sub Sub bar(ByRef a As Integer, ByRef b As Byte, ByRef c As String) b = Abs(a \ 256) c = CStr(a Mod 256) End Sub -- auric "underscore" "underscore" "at" hotmail "dot" com ***** Be a yardstick of quality. Some people aren't used to an environment where excellence is expected. -- Steve Jobs |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Information from Functions
Many many thanks! Works like a charm.
-Austin -----Original Message----- On Thu, 20 Nov 2003 10:36:54 -0800, Austin Amaya wrote: Hello -- I'm looking for ideas of how to get multiple values from a function. Ideally, I'm looking for a function to return an array, but this hasn't seemed to work. I'd appreciate suggestions/ideas an/or solutions. At the moment, I'm considering returning a string containing comma-separated values, then processing this string. Is there a better solution? Thanks, Austin If the return values are all of the same type, you can declare an empty array and assign the return value to that. (This works in Excel 2000. No clue about other versions.) Sub foo() Dim x As Integer Dim y() As Byte x = 12345 y = bar(x) For n = 0 To UBound(yz) MsgBox y(n) Next End Sub Function bar(a As Integer) As Byte() 'returns an array Dim b(1) As Byte b(0) = Abs(a \ 256) b(1) = Abs(a Mod 256) bar = b End Function Alternately, you can pass the variables that will hold the return values by reference and skip arrays, so you can return multiple data types. (Note that the function has become a sub, since it doesn't need to return a value.) Sub foo() Dim x As Integer, y As Byte, z As String x = 12345 bar x, y, z MsgBox y & " " & z End Sub Sub bar(ByRef a As Integer, ByRef b As Byte, ByRef c As String) b = Abs(a \ 256) c = CStr(a Mod 256) End Sub -- auric "underscore" "underscore" "at" hotmail "dot" com ***** Be a yardstick of quality. Some people aren't used to an environment where excellence is expected. -- Steve Jobs . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return a value based on 2 or more columns of information | Excel Worksheet Functions | |||
information functions | Excel Worksheet Functions | |||
Return Side and Header information from table? | Excel Worksheet Functions | |||
Functions for manipulating and filtering address information | Excel Discussion (Misc queries) | |||
Information retrieval functions | Excel Worksheet Functions |