How do I pass a built array back to formula

**sauerj** · 11-11-2009, 10:22 AM

I’m trying to send an array of values INTO a user defined function, do a little math on it, and then send the resulting array back to the caller. The caller in this case is an array formula in an excel cell. I can get it to build an array after the math, but I can’t get it to pass the resulting array back to the formula. The following snippet is a simple version of the code. Here I am building the incoming array in the macro, but same difference at the end. The outgoing Oil_spgr variable never seems to contain the full, final array.

Please Login or Register  to view this content.

**romperstomper** · 11-11-2009, 10:32 AM

Try:

Please Login or Register  to view this content.

**sauerj** · 11-11-2009, 12:32 PM

I gave this a try, but it didn't work. Below is result.
1st column is result of array formula: {= PICalcDat(TO_650,_1ST,_1ET,_1IT,_Avg,1, 0,_PI)}. This shows the array of temperatures feeding into the UDF (Oil_spgr)
2nd column is result of array formula: {= Oil_spgr(PICalcDat(TO_650,_1ST,_1ET,_1IT,_Avg,1, 0,_PI))}. This shows output of the array using this "Oil_spgr = Spgr_Result" approach.
3rd column is result of simplified array formula: {= Oil_spgr2()}. This shows output of this array also using this "Oil_spgr = Spgr_Result" approach.

Note that this "Oil_spgr = Spgr_Result" approach seems to result in all array values equaling the 1st array value.
498 0.77319 0.777303
496 0.77319 0.777303
501 0.77319 0.777303
498 0.77319 0.777303
489 0.77319
494 0.77319
490 0.77319
493 0.77319
508 0.77319
478 0.77319
503 0.77319
490 0.77319
474 0.77319
438 0.77319

**romperstomper** · 11-12-2009, 02:56 AM

Use the Transpose function - either in your worksheet formula, or at the end of the UDF. if you enter your array formula in several cells in one row (not column) now, you should see all the values.