Output from UDF matrixfunction sometimes a row, sometimes a column,why?

[szegersnl]

Hi,

I've created a UDF which I call through a matrix function on the excel
sheet which is located in a column.

Up till now functions allways returned a column vector containing the
values assigned.

Now however if I assign a 1 dimensional variable the UDF now returns a
row vector, but I have no idea what I do different.

How does VBA.Excel decide what type to return and how can I control
it.

 

[Joel]

Since you didn't post any code it is hard to tell you what you did different.
The results is based on the number of items that get returned. It can be a
single item, a single row, a single column, or an array. It dependends on
the function you use and the order of the functions.

What I do recommendc is to debug the problem yourself. This is what you
should do. Thre is an evaluate function tool in excel use it. click on the
cell with the formula and then go to the following menu:

Tools - Formula Auditing - Evaluate Formula

 

[szegersnl]

Maybe it's still possible to help without copying the code here (Not
that I don't want to, I just think in this case it's not relevant as I
want to know how a feature works). What I basically want to know is
how VBA.Excel decides what type (column or row) to return and how I
can control it.

 

[Joel]

You can't control what is returned. It is the results of the size of the
data and the function used. If you do a cross product of a 3 x 4 matrix with
a 4 x 3 matics you will get 3 x 3 matrics. You can't change the size of the
results.

 

[Charles Williams]

I always make a UDF return either a scalar value or a 1-based 2 dimensional
array even if one or more of the dimensions is 1 (Array(1 to 5,1 to 1) for a
5 row 1 column array or Array(1 to 1,1 to 5) for a 1 row 5 column array).

(I always use 1-base for consistency with the way Excel retrieves data from
a range into a variant)

Actually returning an 1 x 1 array containing Empty is better than returning
a single Empty value (Count and Counta etc then get the right answer), so
maybe a UDF should never return a scalar value!


Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[szegersnl]

I always make a UDF return either a scalar value or a 1-based 2 dimensional
array even if one or more of the dimensions is 1 (Array(1 to 5,1 to 1) for a
5 row 1 column array or Array(1 to 1,1 to 5) for a 1 row 5 column array).

(I always use 1-base for consistency with the way Excel retrieves data from
a range into a variant)

Actually returning an  1 x 1 array containing Empty is better than returning
a single Empty value (Count and Counta etc then get the right answer), so
maybe a UDF should never return a scalar value!

Charles
___________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com






- Tekst uit oorspronkelijk bericht weergeven -

@ Charles Williams
Yes, this is the work-around I found working reasonbly well. I found a
function Application.Transpose to transpose when needed, I just need a
way to find out if it will return a row or a column vector.

@Rest thanks for your input. If any more ideas come up I would greatly
appreciate them.

 

[Dana DeLouis]

I just need a way to find out if it

will return a row or a column vector.

Hi. Consider a custom function that uses the second argument to both
the Ubound and Lbound function.

Dana DeLouis