Best way to result more than 1 result?

[Heather Mills]

I need to write a function that returns 3 results. One is the actual
result. The other two are intermediate results that will be passed
back on the next call.

I'm not sure how to return more than one result and I'm also not sure
what the invocation code in the calling cell would do with them.

Is there a way to have the results go into adjacent cells? If the
function is called from B5, can it return one result and put the
intermediate results in C5 and D5?

If so, can someone post sample code snippets for doing something like
that?

Thanks a bunch

 

[joeu2004]

I need to write a function that returns 3 results.
One is the actual result. The other two are intermediate
results that will be passed back on the next call. [....]
Is there a way to have the results go into adjacent cells?
If the function is called from B5, can it return one result
and put the intermediate results in C5 and D5?

If so, can someone post sample code snippets for doing
something like that?

I declare the UDF to be type Variant, and I use Array() to return the
3 results. See examples below. Use myRow() for B55. Use myCol()
for B5:B7.

These work like LOGEXT:

1. Select one cell and enter a non-array formula (press just Enter as
usual), and you get only the first element of the array result,
presumably the primary result.

2. Select two or three cells (in the correct direction) and enter an
array formula (press ctrl+shift+Enter), and you get the first two or
all three elements of the array result, the primary and intermediate
results.

Examples....

Function myRow()
'return __row__ array
myRow = Array(1, 2, 3)
End Function

Function myCol()
'return __column__ array
myCol = WorksheetFunction.Transpose(Array(1, 2, 3))
End Function

 

[joeu2004]

These work like LOGEXT:

Obvious typo.... LOGEST.

 

[joeu2004]

I declare the UDF to be type Variant, and I use Array()
to return the 3 results.  See examples below.  Use myRow()
for B55.  Use myCol() for B5:B7.

Alternative implementations, avoids Worksheet.Transpose. Difference
depends on what your Option Base is.

-----

Option Base 1

Function myCol()
'return __column__ array
Dim myArray(1 To 3, 1)
myArray(1, 1) = 1: myArray(2, 1) = 2: myArray(3, 1) = 3
myCol = myArray
End Function

Function myRow()
'return __row__ array
Dim myArray(1, 1 To 3)
myArray(1, 1) = 1: myArray(1, 2) = 2: myArray(1, 3) = 3
myRow = myArray
End Function

-----

Option Base 0

Function myCol()
'return __column__ array
Dim myArray(0 To 2, 0)
myArray(0, 0) = 1: myArray(1, 0) = 2: myArray(2, 0) = 3
myCol = myArray
End Function

Function myRow()
'return __row__ array
Dim myArray(0, 0 To 2)
myArray(0, 0) = 1: myArray(0, 1) = 2: myArray(0, 2) = 3
myRow = myArray
End Function

 

[joeu2004]

Alternative implementations, avoids Worksheet.Transpose.
 Difference depends on what your Option Base is.

Sorry about the incessant postings. Not thinking clearly.

myRow() can be simplified. Also note that myArray can be typed. But
as a Variant, each element can assigned a different type, e.g. number,
text and boolean.

------

Option Base 1

Function myRow()
'return __row__ array
Dim myArray(1 To 3)
myArray(1) = 1: myArray(2) = 2: myArray(3) = 3
myRow = myArray
End Function

-----

Option Base 0

Function myRow()
'return __row__ array
Dim myArray(0 To 2)
myArray(0) = 1: myArray(1) = 2: myArray(2) = 3
myRow = myArray
End Function

 

[Heather Mills]

I need to write a function that returns 3 results.
One is the actual result. The other two are intermediate
results that will be passed back on the next call. [....]
Is there a way to have the results go into adjacent cells?
If the function is called from B5, can it return one result
and put the intermediate results in C5 and D5?

If so, can someone post sample code snippets for doing
something like that?

I declare the UDF to be type Variant, and I use Array() to return the
3 results. See examples below. Use myRow() for B55. Use myCol()
for B5:B7.

These work like LOGEXT:

1. Select one cell and enter a non-array formula (press just Enter as
usual), and you get only the first element of the array result,
presumably the primary result.

2. Select two or three cells (in the correct direction) and enter an
array formula (press ctrl+shift+Enter), and you get the first two or
all three elements of the array result, the primary and intermediate
results.

Examples....

Function myRow()
'return __row__ array
myRow = Array(1, 2, 3)
End Function

Function myCol()
'return __column__ array
myCol = WorksheetFunction.Transpose(Array(1, 2, 3))
End Function

I got it to work. At first I thought your two list items were step 1
and step 2. I see now that they are 2 different ways of entering an
expression. #1 = non-array, #2 = array.

Thanks