Getting the "calling workbook" for a function proves tricky

[Jens Theisen]

I've implemented Excel functions that should return values depending on the workbook in which they are evaluating.

That, however, turned out to be quite problematic to get right, as ExcelDna gives us only the application, not the calling worksheet.

There is one worksheet that is special (I believe it's the one that has user focus), which we can get with:

(ExcelDnaUtil.Application as Microsoft.Office.Interop.Excel.Application).ActiveWorkbook

But that is not always the one that the respective function is evaluating in.

To see that, try this function:

        [ExcelFunction(IsVolatile = true)]

        public static string TestDna(string name)

        {

            var excel = ExcelDnaUtil.Application as Microsoft.Office.Interop.Excel.Application;

            return excel.ActiveWorkbook.GetHashCode().ToString();

        }

Open up Excel, get a second Excel workbook and put the two windows side by side. Evaluate the formula in each.

You will see that each time the formula is evaluated in one workbook, the value of the other changes as well - to the wrong value.

If one defines the function as non-volatile the situation seems better, but then I have a different problem: I do need to refresh the cells when underlying data sources change, and I can only change all workbooks at once, with a call to Application.CalculateFullRebuild. Doing that will again have some workbooks have the wrong value for ActiveWorkbook.

So what to do?

I think ideally, ExcelDna should tell what context a function is evaluated in, if that is possible.

[Govert van Drimmelen]

Calling

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

In your function will give you an ExcelReference object for the calling cell (or cells).

You can use the SheetId in there to build a new ExcelReference that covers another part of the same sheet.

I think you can only read other parts of the sheet if your function is marked as IsMacroType=true, since you are messing up Excel's dependency tracking.

It's always better to pass the dependencies of a function in as function arguments, since that allows Excel's calculation engine to work properly.

-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 post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Jens Theisen]

How do I get from that to the workbook object?

I don't need other cells, I need the CustomXMLParts property of the respective workbook.

[Govert van Drimmelen]

You should be able to get the sheet name like this:

    ExcelReference callerRef = XlCall.Excel(XlCall.xlfCaller) as ExcelReference; 

    string callerSheetName = (string)XlCall.Excel(XlCall.xlSheetNm, callerRef); 

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Jens Theisen [jens.t...@gmail.com]
Sent: 20 October 2016 03:16 PM
To: Excel-DNA
Subject: Re: [ExcelDna] Getting the "calling workbook" for a function proves tricky

How do I get from that to the workbook object?

I don't need other cells, I need the CustomXMLParts property of the respective workbook.

--

[Jens Theisen]

But I need the workbook, not the sheet. Also, the names are probably not unique, especially not that of the sheets.

Still thanks for your tips!

[Sergey Zhilyakov]

The following property returns calling workbook:

excel.Caller.Worksheet.Parent

четверг, 20 октября 2016 г. пользователь Jens Theisen написал:

But I need the workbook, not the sheet. Also, the names are probably not unique, especially not that of the sheets.

Still thanks for your tips!

--

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+unsubscribe@googlegroups.com.

To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

--
С уважением,

Сергей

[Govert van Drimmelen]

You get the Workbook name as part of the xlSheetNm result.

Try this function:

using ExcelDna.Integration;

using static ExcelDna.Integration.XlCall;

public static class TestFunctions

{

    public static string dnaGetCaller()

    {

        ExcelReference callerRef = Excel(xlfCaller) as ExcelReference;

        if (callerRef == null)

            return "!!! UNKNOWN CALLER";

        string callerName = (string)Excel(xlSheetNm, callerRef);

        return callerName;

    }

}

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Jens Theisen [jens.t...@gmail.com]

Sent: 20 October 2016 04:25 PM

To: Excel-DNA
Subject: Re: [ExcelDna] Getting the "calling workbook" for a function proves tricky

But I need the workbook, not the sheet. Also, the names are probably not unique, especially not that of the sheets.

Still thanks for your tips!

--

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.

[Jens Theisen]

Unfortunately, that's also just the same as excel.ActiveWorkbook.

(I presume you mean excel.Caller.Worksheets.Parent.)

[Jens Theisen]

Ok, I think you're right that this is indeed a solution - the worksheet name is part of the sheet name.

It relies also on the fact that Excel apparently enforces all worksheets to have different names - perhaps that's part of the reason why that is.

So thank you for your help!

[Jens Theisen]

For the record, here a proper function to get the workbook:

        static Workbook GetCallingWorkbook()

        {

            var excel = ExcelDnaUtil.Application as Microsoft.Office.Interop.Excel.Application;

            if (excel == null) throw new Exception("No application.");

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

            if (cref == null) throw new Exception("No calling excel reference.");

            var cname = XlCall.Excel(XlCall.xlSheetNm, cref) as string;

            if (cname == null) throw new Exception("No sheet name found.");

            var match = Regex.Match(cname, @"\[([^\]]+)\](.+)");

            if (!match.Success) throw new Exception($"Sheet name '{cname}' does name match format '[book]sheet'.");

            var bookName = match.Groups[1].Value;

            var sheetName = match.Groups[2].Value;

            var workbooks = excel.Workbooks.Cast<Workbook>();

            var workbook = workbooks.FirstOrDefault(wb => wb.Name == bookName);

            if (workbook == null) throw new Exception(

                $"Workbook {bookName} could not be found, current workbooks are [{String.Join(", ", (from wb in workbooks select wb.Name))}].");

            return workbook;

        }

[Xavier Aymé - Mauchand]

This method works like a charm, many thanks Jens, you saved me a lot of work ! :-)