Excel VBA UDF Array function returns the same value for all cels -

i'm trying make excel vba udf function return array. of function either returns #value! error if define argument double array or of cells in array have same value if define argument variant though checked in locals window in vbe , return array variable contains different values.

here code:

function varscenariostest(byref dblrealrates() double/variant) double() 'dblrealrates() defined either double or variant dim intcount integer dim dbltemp() double  intcount = lbound(dblrealrates) (ubound(dblrealrates) - 1)   redim preserve dbltemp(1 intcount)   dbltemp(intcount) = dblrealrates(intcount + 1) - dblrealrates(intcount) next intcount  varscenariostest = dbltemp  end function  function range2dblarray(byref rngrange range) double()  dim dbltemp() double dim intcount integer  intcount = 1 rngrange.count   redim preserve dbltemp(1 intcount)   dbltemp(intcount) = rngrange.cells(intcount) next intcount  range2dblarray = dbltemp  end function 

i call function in spreadsheet in following way: function call in excel dblrealrates() double/variant

column e contains input function, column f values should returned function , column g function itself. if define dblrealrates() variable type double, formula returns #value! error. if define variant, returned values in spreadsheet same. if pause execution of code @ end function line , in locals window, can see values in array different, not equal 0.000016287 output in spreadsheet suggests:function array values in locals window

can please advise why formula returns error when dblrealrates() variable defined double, , why return identical values if it's defined variant?

a one-dimensional array treated being shaped 1 row x columns, why values coming out same. (the entire result column assigned value of first column returned.)

the following code works situation (but undoubtedly give issues if change things work on rows instead of columns):

function varscenariostest(byref dblrealrates() variant) variant()     dim intcount integer     dim dbltemp() double      intcount = lbound(dblrealrates) (ubound(dblrealrates) - 1)         redim preserve dbltemp(1 intcount)         dbltemp(intcount) = dblrealrates(intcount + 1, 1) - dblrealrates(intcount, 1)     next intcount      varscenariostest = application.transpose(dbltemp) end function  function range2dblarray(byref rngrange range) variant()     dim dbltemp() double     dim intcount integer      intcount = 1 rngrange.count         redim preserve dbltemp(1 intcount)         dbltemp(intcount) = rngrange.cells(intcount)     next intcount      range2dblarray = application.transpose(dbltemp) end function 

---

alternatively, doing transpose of results of range2dblarray in excel formula itself

{=varscenariostest(transpose(range2dblarray(e2:e21)))} 

you can leave original range2dblarray untouched , modify varscenariostest:

function varscenariostest(byref dblrealrates() variant) variant()     dim intcount integer     dim dbltemp() double      intcount = lbound(dblrealrates) (ubound(dblrealrates) - 1)         redim preserve dbltemp(1 intcount)         dbltemp(intcount) = dblrealrates(intcount + 1, 1) - dblrealrates(intcount, 1)     next intcount      varscenariostest = application.transpose(dbltemp) end function  function range2dblarray(byref rngrange range) double()     dim dbltemp() double     dim intcount integer      intcount = 1 rngrange.count         redim preserve dbltemp(1 intcount)         dbltemp(intcount) = rngrange.cells(intcount)     next intcount      range2dblarray = dbltemp end function 

---

and doing transpose of results of varscenariostest in excel formula itself

{=transpose(varscenariostest(transpose(range2dblarray(e2:e21))))} 

you can allow varscenariostest return one-dimensional array (but still need pass dblrealrates function two-dimensional variant array):

function varscenariostest(byref dblrealrates() variant) double()     dim intcount integer     dim dbltemp() double      intcount = lbound(dblrealrates) (ubound(dblrealrates) - 1)         redim preserve dbltemp(1 intcount)         dbltemp(intcount) = dblrealrates(intcount + 1, 1) - dblrealrates(intcount, 1)     next intcount      varscenariostest = dbltemp end function  function range2dblarray(byref rngrange range) double()     dim dbltemp() double     dim intcount integer      intcount = 1 rngrange.count         redim preserve dbltemp(1 intcount)         dbltemp(intcount) = rngrange.cells(intcount)     next intcount      range2dblarray = dbltemp end function