INDEX Function gives #REF! Error on worksheet and Error 2023 in UDF

[narendra]

I am not sure what is causing below error, it seems my mind has stopped thinking ... it's been a long day.

Wrapping below FORMULA in a SUM function gives me #REF error in excel. The formula with SUM is in cell E4.

INDEX(A:C,ROW())

The column reference is omitted as I want it to return all 4 columns.

Ideally, the above part should return Range A4:C4 which is filled with numeric values and the SUM function should return the total of those values, but i get the #REF error.


I am testing above function on a worksheet because the same INDEX formula when passed to a UDF returned #VALUE error.
The above part was passed to the 1st argument in the UDF which is declared As Range.

When I entered the formula, it did not even trigger the breakpoint I set at the very beginning of the UDF code.
I had to remove the As Range declaration from the 1st argument which then triggered the breakpoint. Mouse over the 1st argument showed Error 2023.

Note: The UDF works fine when only one column is selected.

Can someone please let me know what is wrong here?

 

[Domenic]

To return all columns, you'll need to specify the third argument for your INDEX function as 0. In a worksheet formula, you would do the followIng...

=SUM(INDEX(A:C,ROW(),0))

Hope this helps!

 

[narendra]

=SUM(INDEX(A:C,ROW(),0))

Hope this helps!

Fixed it! Thank you Domenic.