Caller method returns argument that has changed

Mar 20, 2014 at 4:55 AM
I'm using the ExcelDnaUtil.Application.Caller to try and get the value of the cell that contains the macro.

e.g. A1 contains =TestMethod( B2 )

If I refresh A1, it correctly returns the A1 range, however if B2 changes (causing a recalculation), B2 is returned as the caller.

This in a way makes sense, however I'm looking for a way of always returning A1 in this case.

What am I missing?

Thanks in advance!

Paul
Coordinator
Mar 20, 2014 at 8:39 AM
Edited Mar 20, 2014 at 8:40 AM
Hi Paul,

I get that too, testing on Excel 2013! I'm very surprised.
Here's the code I'm testing with:
Imports ExcelDna.Integration

Public Module Test

    Function dnaGetCaller(input As Object) As String
        Dim caller = ExcelDnaUtil.Application.Caller

        Dim xlCaller = XlCall.Excel(XlCall.xlfCaller)


        Return String.Format("Called from {0} (or {1}) with {2}", caller.Address, xlCaller, input)
    End Function

End Module
What version of Excel are you using?

I can suggest you use the C API call (XlCall.Excel(XlCall.xlfCaller)) which seems to work right. You get an ExcelReference back, with 0-based row and column information. You can convert to a Range COM object if you have to (search the Google group for "ReferenceToRange").

What do you want to do with the caller information?

-Govert
Marked as answer by callidex on 3/23/2014 at 5:48 PM
Mar 20, 2014 at 9:38 AM
Hi Govert,

I originally was using it to change the formatting of the calling cell.

I've played with the xlfRefText call but it actually excepts in this case although the cell reference is consistent! :-D


I've boiled down a sample
    {
        //Excel DNA
        Range r = (Range)GNTExtension.GetApp().Caller;
        // r is valid, but could also be an argument, not the cell with the macro
        Debug.Print("{0} {1}",r.Row,r.Column);   // eg   5,5

        // C API
        var callerReference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
        // callerReference is valid  
        Debug.Print("{0}", callerReference);     // shows 4,4  (zero based)

        Range c = (ExcelDnaUtil.Application as Application).get_Range(XlCall.Excel(XlCall.xlfReftext, callerReference, true));
        // throws exception           
    }

The ExcelDna.Integration.XlCallException is thrown every time.

I'll take another look later with less tired eyes..
Coordinator
Mar 20, 2014 at 9:46 AM
I don't think you'll be able to change the formatting of the calling cell directly from the UDF. Excel-DNA has a feature to allow you to schedule a macro that runs after the calculation is complete, where you can then do such things.

An example is in Distribution\Samples\Async\AsyncMacros.dna, at the bottom. The function looks like this:
    [ExcelFunction(IsMacroType=true)] // IsMacroType=true is required only for the xlfGetCell call.
    public static DateTime asyncFormatCaller()
    {
        object caller = XlCall.Excel(XlCall.xlfCaller);
        if (caller is ExcelReference)
        {
            var desiredFormat = "h:mm:ss";

            var oldFormat = (string)XlCall.Excel(XlCall.xlfGetCell, 7 /* number format */, caller);
            if (oldFormat != desiredFormat)
            {
                ExcelAsyncUtil.QueueAsMacro(delegate
                    {
                        // Set the formatting of the function caller
                        using (new ExcelSelectionHelper((ExcelReference)caller))
                        {
                            XlCall.Excel(XlCall.xlcFormatNumber, desiredFormat);
                        }
                    });
            }
        }
        return DateTime.Now;
    }
Mar 20, 2014 at 10:04 AM
Edited Mar 20, 2014 at 10:04 AM
Just started playing with the macro queuing so thats good to know.

Any idea how to trace the exception? Couldn't find any supporting info other than XlCallException

Thanks!
Mar 20, 2014 at 10:10 AM
I found that IsMacroType =true needs to be set, I'd fallen for that before.

That does the trick now!

Thanks for you help
Mar 20, 2014 at 11:18 PM
The more I think about it, the more this can be a 'feature'. Being able to know if the recalculation is because an argument changed rather than being edited may be useful in some cases...