ExcelArgument(AllowReference:=True)

[Rich Russell]

I have numerous UDFs with parameters which read in either a numeric or a range.   The parameters are defined as an Object then code internal to the UDF deciphers if its a numeric or array.

Users have developed sizable workbooks utilizing these UDFs, so sizable such that they have difficulty tracking errors in the UDFs.  I'm considering inserting the AllowRefence=True for the parameters so I can grab the cell address to display to users when an error is encountered but that means changing a lot of code which deciphers these parameters since they will be a reference and not an array.  Is there a quick way to convert the reference back to an array so I can use existing code?

Thanks,  Rich

[Govert van Drimmelen]

Hi Rich,

 

It might be less disruptive to get the UDF caller in error cases.

    var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

// caller might be null in some cases

 

You can the calling sheet info:

    var sheetName = XlCall.Excel(XlCall.xlSheetNm, caller); 

 

And you can get the (0-based) row and column information from the ExcelReference object itself.

 

If you still want to go for AllowReference=true, then you can convert from a reference to a value by calling ExcelReference.GetValue().

This will give you one of the supported input types: string, double, bool, object[,], ExcelError, ExcelEmpty, ExcelMissing.

From there to get to a string, a double or a DateTime is then up to you.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/360c466a-454a-4603-95d1-9fcfd1afd19en%40googlegroups.com.

[Rich Russell]

Thanks Govert - works perfectly!   U da man!!!!