how to output multiple results of a single function by running the function only once

I have a function that looks something like this:

Please Login or Register  to view this content.

Then to run the function and get an output, I would do this: x = my_function(1, a, b, c) to get output_1, y = my_function(2, a, b, c) to get output_2, and so on. This, however, involves running the function every time I need to calculate one of the 3 outputs. I there a way to output ALL function results at ounce as a vector/array somehow, but by running the function only once instead of 3 separate times?

The code is massive, and I need to be able to run it as efficiently as possible, so each millisecond matters. The resulting vector/array does not need to be transferred to the spreadsheet, but will only be used within the VB code.

Please Login or Register  to view this content.

I have several UDF's (intended to be called from a spreadsheet cell) where I output arrays (similar to the built in LINEST() and FREQUENCY() functions). The strategy I use:

1) Dimension function as a variant data type (it will become a variant containing an array).
2) Dimension an array inside of the function to store the results
3) At the end, assign the array to the function name
4) Note that a 1D VBA array will be returned to Excel as a horizontal array. If you need a vertical array output to Excel, you can either use the TRANSPOSE() function or, if you want the function to control the orientation of the output, use a 2D array for step 2.

Please Login or Register  to view this content.

As noted, my experience is almost exclusively with "array function udf's to be called from and return results to the spreadsheet", so take that into account, if your primary interest is in calling the function from another procedure. In those cases, a loop like TMS suggested may be more appropriate, or you may find other ways to transfer arrays from one procedure to another.

What I would do is use the original UDF but make the var_number argument optional.
If specified, it returns the same value as the current function. If the var_number argument is omitted then it will return an array

Please Login or Register  to view this content.

TMS, thank you, though that looks like that still involves running the function 3 separate times to fill all slots of the vA array. Once for each slot.

MrShorty, is this what you mean:

Please Login or Register  to view this content.

I think I got my example up, if you haven't seen it, yet. It looks like you understand the basic idea (though I am not sure that the syntax of my_function=(output_1,output_2,...) works in VBA. I don't recall it working that way).

As far as the issue with transpose, I usually do that outside of the function. I program the function to return a horizontal array. If I want a vertical array, I nest the function inside of the TRANSPOSE() function =TRANSPOSE(my_function(a,b,c,d))

MrShorty - ok, got it. Thank you. mikerickson, I've never seen -1 used to output a function result - that makes it more versatile. Thank you.

I go this to work after some testing, so I'll go with it:

Please Login or Register  to view this content.