Array function only displays first value of array as output

I have written a function to return a vector of the same size as input vectors "YTM", "Spread", "DF", and scalar "RECOVERY". The function seems to work just fine when I debug it, and different values are assigned to the different cells of the output. However, when I use this function in a worksheet I only get the same value in every cell. What am I doing wrong?

here's the code:

Please Login or Register  to view this content.

Btw, never mind the actual calculations in the code - it is not finished... The point is that P is a vector of different values, and after ImpSurvProb = P, the function output take on these P values when I debug it in Visual Basic, but shows only the first value for all vector entries in the worksheet...

The function seems to work just fine when I debug it, and different values are assigned to the different cells of the output. However, when I use this function in a worksheet I only get the same value in every cell.

Which direction is your output vector oriented, vertical or horizontal? When I see this behavior with my UDF's, it is because I have mismatched the orientation of the output vector. IIRC, a one dimensional array in VBA like you have here transfers to Excel as a horizontal array. When you enter the UDF into a vertical array of cells, you get the first entry in each cell.

If this is correct, you could nest your UDF inside of the TRANSPOSE() function when you want to put it into a vertical array =transpose(impsurvprob(arglist))
If you know that you will always (or mostly) want to enter the function into a vertical array, dimension P() as a 2 dimensional "vertical" vector -- Redim P(1 to N,1).

All vectors, both input and output, are vertically oriented. I havent defined this anywhere except for the selection into which i write the function.

if i defined

Please Login or Register  to view this content.

, it actually worked. I get the feeling I could do this more elagantly, and I'm sure I've seen such functions (that also work) with only one dimension defined.

Or?

I'm sure I've seen such functions (that also work) with only one dimension defined.

Possibly. I only know that, in my own array UDF's, VBA/Excel have been pretty insistent that a one dimensional array in VBA returns a horizontal array when returning the result to the spreadsheet. To the point that, many times I will define the array in two dimensions no matter which orientation to help me remember which orientation I intended (dim temp(1,5) for a horizontal array and dim temp(5,1) for a vertical array).

Sometimes I have contemplated using the Application.Caller object to capture whether the function is called from a vertical or horizontal array, but have decided it is too easy to just nest the function inside of Transpose() when I want a different orientation.