Confirming limits of IsThreadSafe = true

[Brian Pierce]

First things first: I've said it before, but ExcelDNA if absolutely amazing. Honestly, some of the most productivity-enhancing software I've ever used.

I have found one bit of weird behavior, which I suspect is either an Excel issue or (more likely) a result of user error. Throwing it out there in case anybody can confirm one way or the other. In short, Excel is breaking in subtle way if a have a noop threadsafe method that receives a range reference. Here is the totality of the code:

using ExcelDna.Integration;

namespace BreakingExcel {

    public static class Class1 {

        [ExcelFunction(Name = "DO_NOTHING", Description = "Does nothing", Category = "Some category", HelpTopic = "Some help topic", IsExceptionSafe = true, IsThreadSafe = true)]

        public static object DoNothing([ExcelArgument(Name = "range", AllowReference = true)] object range) { return "Hi"; }

    }

}

If I have a spreadsheet that calls this method more than 4 times (32-bit Excel 2016 running on x64 Windows 10 with 4 physical and 8 logical cores) then Excel will suddenly stop showing certain dialog boxes and other varied strange behavior. I've attached a spreadsheet that does this, and there is a bit more flavor in the comments. Interestingly, if I only call the method 4 times or less the bad behaviors aren't observed -- not sure if it's just a coincidence given the number of cores. Perhaps it's known that having reference params, even if not dereferenced, only works when called by the main UI thread?

The same thing happens if I call XlCall.Excel(XlCall.xlfCaller) in a method marked as threadsafe: the call always seems to succeed fine, but Excel is put in this slighly off state. Tangentially related: if I remove the threadsafe designation, I am able to safely call GetValue() on a reference even if the method is not flagged as a macro type. In that case Excel seems to continue to behave without any issues at all. Is that expected to be safe? The attached spreadsheet also has those cases -- here are the methods:

        [ExcelFunction(Name = "CALLER_ROW", Description = "Returns row of caller", Category = "Some category", HelpTopic = "Some help topic", IsExceptionSafe = true, IsThreadSafe = true)]

        public static object CallerRow() { return (XlCall.Excel(XlCall.xlfCaller) as ExcelReference)?.RowFirst ?? 0; }

        [ExcelFunction(Name = "RANGE_VAL", Description = "Returns value of range", Category = "Some category", HelpTopic = "Some help topic", IsExceptionSafe = true, IsThreadSafe = false)]

        public static object RangeVal([ExcelArgument(Name = "range", AllowReference = true)] object range) { return (range as ExcelReference)?.GetValue(); }

What's strange is that I've been doing this for years, and never noticed any such issues until recently. Perhaps some Excel update changed the way it responds or something, or perhaps I simply never noticed it since the effect is a little subtle. I did confirm that the problem does not exist in Excel 2010, so something different about Excel 2016. In any event, I want to stick with whatever is the safest/recommended way to use it.

Thanks!

Brian

[Govert van Drimmelen]

Hi Brian,

 

Thank you for the detailed write-up and steps to reproduce this issue.

 

I’ve had a look, and I can reproduce the behaviour your describe with my Excel version 16.0.12607.20000 (32-bit) and Excel-DNA v 1.0.

Have you found that the behaviour, once broken, ever recovers for the Excel session?

 

The main source of information about thread-safe functions in .xll add-ins is this page:

https://docs.microsoft.com/en-us/office/client-developer/excel/multithreaded-recalculation-in-excel

 

My expectations according to this were:

• I expect DO_NOTHING (IsThreadSafe + AllowReference) to be fine.
• I expect CALLER_ROW (IsThreadSafe + AllowReference + xlfCaller) to be fine.
• I expect RANGE_VAL modified to thread-safe (IsThreadSafe + AllowReference + GetValue()/xlCoerce) to be problematic and fail if the reference is uncalced. (Excel-DNA internally calls xlCoerce to implement GetValue(), and this will fail (throw an exception from GetValue()) if the reference has not been calculated yet.)

 

I can recreate the problem you report with each of the three functions (with the thread-safe version of RANGE_VAL).

 

I should add an extra piece of information about Excel-DNA that might not be obvious. When converting a passed in reference to  an ExcelReference structure, there might be extra calls by Excel-DNA to determine the correct sheet for the reference (depending on whether a local “SReference” or a global reference is passed). This means there are internal calls to xlfCaller, xlSheetNm and xlSheetId for an AllowReference function. While the Microsoft document says xlSheetNm and xlSheetId are thread-safe, the status of xlfCaller is not made explicit, though I would expect it to be fine for thread-safe functions.

 

Then I tried some initial tests:

1. I’ve modified Excel-DNA to skip all the extra calls xlfCaller / xlSheetNm / xlShetId calls.

Then the problem does not occur (of course now the reference passed in to the function is broken).

 

2. Next, I took out the xlfCaller call, but still do xlSheetNm and xlSheetId.

(The xlfCaller call is only there to work around an Excel bug where for IsMacroType functions xlSheetNm would incorrectly report the active sheet instead of the current sheet.)

The problem does occur in this case.

 

3. Next, I check if we can get rid of all the magic behind AllowReference:

 

        [ExcelFunction(IsThreadSafe = true)]

        public static object GetSheetName()

        {

            var emptyRef = new ExcelReference(0, 0, 0, 0, IntPtr.Zero);

            return XlCall.Excel(XlCall.xlSheetNm, emptyRef);

        }

 

This reproduces the issue too, though no AllowReference is involved anymore.

The documentation clearly indicates that xlSheetNm is thread-safe, so this should be fine.

 

So either there is a bug in Excel, or something in the way Excel-DNA handling the memory in this case.

 

I would like to see if the problem can be recreated with a native code C add-in.

If the problem still occurs in this simpler case, then we can report to Microsoft.

 

I’m not sure when I’ll get a chance to look at it more, but that’s a start.

 

-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/6dd0125d-4e72-4407-a836-70a1d260dbc8%40googlegroups.com.

[Brian Pierce]

Hi Govert,

Thanks for taking a look so quickly. I've never seen Excel recover from that state, so I'm fairly confident it doesn't. I do get the uncalced error on the first call to GetValue(), but that seems fine since it doesn't have any lasting impact.

I am suspicious that it could be an Excel bug since it definitely does not occur in Excel 2007 (I incorrectly said 2010 in the post, but just double-checked and my other installed version is 12.0.6787.5000 SP3 32-bit ...my 2016 version is 16.0.12430.20112 32-bit).

A far as I can tell it does not cause any major failures beyond those small side-effects, and simple enough to avoid now that I know it's there. If nobody else has noticed this before, then I suspect it does not lead to any major issues -- just those semi-annoying little side effects, which really aren't show-stoppers.

Brian

[FastExcel]

A quick test on Excel 2019 C2R and a C++ Do_Nothing using XLL Plus and an argument defined as type U does not show any problems - but it does not call xlSheetNm

If I change it to return the sheetname using the XLLPlus method GetSheetName it no longer shows the cannot change part of an array message

#pragma region DO_Nothing support code
IMPLEMENT_XLLFN4(DO_Nothing, DO_Nothing_4, DO_Nothing_12, "RR", "UU$", L"DO_No"
    L"thing", 0, L"Arg1", 0, L"14", 0, L"", 0, L"\0", 0, 0, L"{DO_Nothing,,,{}"
    L",14,1,33,U,{{0,{Arg1,Reference,0,,,,,,}}},{},3,,0,0,,,,0,0}", 1, 0, 0)
CXlOper* DO_Nothing_Impl(CXlOper&, const CXlOper*);
extern "C" __declspec(dllexport)
LPXLOPER12 DO_Nothing_12(LPXLOPER12 Arg1)
{
    XLL_FIX_STATE;
    CXlOper xloResult, Arg1__port(Arg1);
    try {
        CXlStructuredExceptionHandler _seh_;
        xloResult.HandleResult(DO_Nothing_Impl(xloResult, &Arg1__port));
    }
    catch(const CXlRuntimeException& ex) {
        CXllApp::Instance()->DisplayException(xloResult, ex);
    }
    XLP_CATCH_CLR_EXCEPTIONS_TO(xloResult)
    return xloResult.Ret12();
}
extern "C" __declspec(dllexport)
LPXLOPER4 DO_Nothing_4(LPXLOPER4 Arg1)
{
    XLL_FIX_STATE;
    CXlOper xloResult, Arg1__port(Arg1);
    try {
        CXlStructuredExceptionHandler _seh_;
        xloResult.HandleResult(DO_Nothing_Impl(xloResult, &Arg1__port));
    }
    catch(const CXlRuntimeException& ex) {
        CXllApp::Instance()->DisplayException(xloResult, ex);
    }
    XLP_CATCH_CLR_EXCEPTIONS_TO(xloResult)
    return xloResult.Ret4();
}

#pragma endregion

CXlOper* DO_Nothing_Impl(CXlOper& xloResult, const CXlOper* Arg1)
{
    // End of generated code
//}}XLP_SRC
    // TODO - set the value of xloResult, or return another value
    //          using CXlOper::RetXXX() or throw a CXlRuntimeException.
    xloResult = Arg1->GetSheetName();
    return xloResult.Ret();
}

[Govert van Drimmelen]

Thanks Charles.

 

Yes, I think that the problem is exhibit with the AllowReference / “U” type in Excel-DNA is a red herring.

 

It looks like calling either xlSheetId or xlSheetNm inside a thread-safe function causes the problem

Both of these are explicitly called out as thread-safe in the official documentation.

 

-Govert

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of FastExcel

Sent: 18 February 2020 18:15
To: Excel-DNA <exce...@googlegroups.com>

--

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/94bccddd-d7b6-40f6-9ed2-205053aa54a2%40googlegroups.com.

[FastExcel]

I will see if I can get MSoft to do anything about it ... but I don't know if its a bug in the documentation or the XLL SDK ... and I doubt it would be high up the list of priorities!

[Govert van Drimmelen]

Thank you Charles – that would be a big help!

 

I don’t have an easy way to check older Excel versions to see when this might have gone wrong.

We also don’t really know what other misbehaviour this state exhibits – maybe Brian can add what he’s found.

The array-change dialog not showing seems like an indication of the internal state of Excel being really weird.

 

xlSheetNm / xlSheetId are certainly functions that are useful from thread-safe functions.

I also see that xlfRtd is thread-safe in current versions, so Microsoft has made some improvements in that general area.

 

-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/94aaeeef-826e-4c3f-958e-09e27b450c42%40googlegroups.com.

[Brian Pierce]

Thanks Charles and Govert for looking into this one.

The weirdness I can confirm is what I noted in my original post: that some dialog boxes stop showing up, and that cut/copy mode becomes unstable. Other dialog boxes I've noticed that don't open include the warning when trying to insert a row/column through an array formula, reporting that the end of a spellcheck is completed, and even asking whether you want to open a new instance when pressing Alt down and launching a new Excel instance (it automatically just launches in a new process in that case without asking). I think there are others I'm forgetting offhand, but thankfully the most critical dialog boxes like "Do you want to save..." are still shown.

I also suspect that it *could* be related to an issue seen by 2 of my users whereby Excel gets stuck in a state of repeatedly re-saving itself. These were both users who had experienced the dialog weirdness, and also both were doing DB queries from Excel. I've never been able to recreate that, though, so really don't know if it's related. Based on the symptoms that do exist, I suspect there are other little things that are off.

In any event, I've made the required tweaks on my end to avoid the issue ...in some cases removing the reference indicator on params, and in others removing the thread safe flag. All quick to do and has fully eliminated the issue.

Brian

On Wednesday, February 19, 2020 at 12:14:47 PM UTC, Govert van Drimmelen wrote:

Thank you Charles – that would be a big help!

 

I don’t have an easy way to check older Excel versions to see when this might have gone wrong.

We also don’t really know what other misbehaviour this state exhibits – maybe Brian can add what he’s found.

The array-change dialog not showing seems like an indication of the internal state of Excel being really weird.

 

xlSheetNm / xlSheetId are certainly functions that are useful from thread-safe functions.

I also see that xlfRtd is thread-safe in current versions, so Microsoft has made some improvements in that general area.

 

-Govert

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of FastExcel
Sent: 19 February 2020 14:04
To: Excel-DNA <exce...@googlegroups.com>
Subject: Re: [ExcelDna] Re: Confirming limits of IsThreadSafe = true

 

I will see if I can get MSoft to do anything about it ... but I don't know if its a bug in the documentation or the XLL SDK ... and I doubt it would be high up the list of priorities!

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