get the range from excelreference problems

May 22, 2013 at 8:36 AM
I have a excelDNA udf that takes as arguments ExcelReference. I need to convert them to Range.

I am using
dynamic app = ExcelDnaUtil.Application;
return app.Range[XlCall.Excel(XlCall.xlfRelref, excelReference, true)];

At first I got the error "Exception of type 'ExcelDna.Integration.XlCallException' was thrown".
I made the function IsMacroType.

But now I get "Exception from HRESULT: 0x800A03EC" because the method XlCall.Excel(XlCall.xlfRelref, excelReference, true) returns ExcelErrorValue.

Is there a valid way to convert excelreference to range?
Coordinator
May 22, 2013 at 10:14 AM
I think you want xlfRefText instead of xlfRelref

You might look also at this post: https://groups.google.com/group/exceldna/browse_thread/thread/b790cb1ee05624b2 or some other on the group if you search for "ReferenceToRange".

-Govert
May 22, 2013 at 12:21 PM
With xlRefRext it worked

Thanks
May 23, 2013 at 4:52 PM
Why does setting IsMacroType=true make the difference between the dynamic excelReference object being processed through XlCall.Excel successfully as a Range object and XlCall.Excel throwing the {"Exception of type 'ExcelDna.Integration.XlCallException' was thrown."} exception? I debugged with and without the IsMacroType parameter, and the internals of the dynamic excelReference object appear to be the same (same set of attributes and values).

I hate to take this on blind faith.

Thank you for any light you can shed on this question.
Coordinator
May 23, 2013 at 5:06 PM
The xlRefText call is a "Macro Sheets Only" API function, which means Excel will not allow you to call it if your function is not marked with IsMacroType=true.

I usually recommend against using the Excel COM Automation interface (including the Range type) from inside a worksheet function, though I don't have a reproducible issue or other good reason for my recommendation.

-Govert
May 23, 2013 at 5:25 PM
OK. The proverbial lightbulb just popped on. Thank you for the quick response to my question. You are awesome!

Now a followup observation and question. The set of UDFs I'm writing are subtotal style functions that will recalculate based on the user filtering/refiltering the rows of data the functions are referenced to through Range objects. In this case, there is no other way I can think of to implement these UDFs without using Range objects. So, I must set IsMacroType=true to be able to process the ranges as Range objects and not simply arrays of values. I need to be able to test the cell.Rows.Hidden attribute to determine if the particular cell value should be included in the subtotal or not.

This leads me to my next question. Setting the IsMacroType=true will cause Excel to treat the UDFs as volatile, correct? So, to prevent the UDFs from being unecessarily called everytime any cell in the worksheet changes, I would assume I ought to set the ExcelFunction attribute parameter IsVolatile to false. If this assumption is correct, do you see any side affects to the combination of IsMacroType=true and IsVolatile=false?
Coordinator
May 23, 2013 at 5:37 PM
I'm not sure whether what you are doing with the hidden rows is safe or whether there is another way.

By default Excel will consider a function that is both IsMacroType=true and has an AllowReference=true parameter as volatile. Setting IsVolatile=false in the ExcelFunction attribute will not override that (it just leaves off the volatile suffix from the function registration string, but Excel make this case volatile anyway).

To prevent your function from being volatile, you need an explicit call to XlCall.Excel(XlCall.xlfVolatile, false) in your function.

-Govert
Coordinator
May 23, 2013 at 5:40 PM
How will Excel know to recalculate your function when you change which rows are hidden?

-Govert
May 23, 2013 at 5:55 PM
I don't know. I just know it works based on having prototyped the functions in Excel VBA and testing, then porting one of them to C#/Excel-DNA and testing. Both cases work.

My guess is when the user sets/resets a data filter, this triggers a filter changed event, which in turn causes Excel to recalculate any formulas that are referencing the filtered data. Or maybe Excel recalculates all formulas in the worksheet when a filter is changed, regardless of what the formulas are referencing? That is another rabbit hole I'll have to go down to better understand what is going on inside Excel's event model.