I need to write a UDF to process a single cell on a large number of worksheets. I would like to mimic the syntax of the SUM() function:
=SUM(Sheet1:Sheet347!B9)
I don't know how to declare the argument in the Object Model. Its not a Range since it spans more than one worksheet. I have three workarounds, none of which are satisfactory:
1. The first workaround is to use something like:
=MyUDF(B9)
In the body of the UDF, I capture the Address of the input range and then loop over Sheet1 thru Sheet347, grabbing each B9 and then processing the set of values. This is simple, but it lacks volatility.
2. The second workaround is to use a column of "helper" cells
=MyUDF(Z1:Z347) in which
Z1 contains =Sheet1!B9
Z2 contains =Sheet2!B9
etc. I don't want a "helper" column
3. The third workaround is to try to code all the arguments in the UDF call:
=MyUDF(Sheet1!B9, Sheet2!B9, Sheet3!B9.......................) this causes my fingers to cramp up.
Can anyone point me to the part of the Object Model that covers this input to =SUM() or the proper syntax for a UDF??
