Use the TRANSPOSE() function to transpose a range as an input to the UDF

Dear Sir,

I'm very curious about using output from the TRANSPOSE() function as an input to the UDF function. For the first UDF below, it will work if you specify it as: =MaxDrawDown(TRANSPOSE(A11:G11)) , confirmed Ctrl+Shift+Enter. But for the second UDF, it will be an #VALUE! error if specified as =DivGrowth(TRANSPOSE(A1:A11)), confirmed with Ctrl+Shift+Enter.

Can I ask why the TRANSPOSE() worked for the first UDF, but not the second?

Please Login or Register  to view this content.

DivGrowth will fail immediately because, I assume, TRANSPOSE(A1:A11) doesn't return a range.

Hi alice,

I'm wondering about returning arrays from a function and found some hints at:
http://www.cpearson.com/excel/return...ysfromvba.aspx

But more fundamentally he talks about problems with UDF at:
http://www.cpearson.com/excel/Writin...ionsInVBA.aspx

I hope the above two links will help you figure out your own question.

The first UDF takes a variant as an argument and as far as I can see from the code it's a variant array.

PS If you step through the DivGrowth formula on the worksheet using Formulas>Evaluate Formula you'll see what TRANSPOSE returns.

No.

DivGrowth expects a range as an argument, TRANSPOSE(A1:A11) doesn't return a range it returns an array.

If you try calling DivGrowth in code like this you'll get an 'Object required' error.

Please Login or Register  to view this content.