Using PublishObject.Publish() in background thread resets workbook's calculation mode

Question

I'm creating an Excel Add-in that implements IDTExtensibility2 and has one function that publishes a range off the worksheet using Excel's PublishObjects

I'm using Visual Studio 2008 and Excel 2007, the following is my code:

public class AddIn : IDTExtensibility2
{
    private static Application s_Application;

    public string PublishRange(Range rng)
    {
        new Thread(() => DoWork(rng)).Start();
        return "OK";
    }

    private string DoWork(Range rng)
    {
        try
        {
            Worksheet sheet = rng.Parent as Worksheet;
            Workbook book = sheet.Parent as Workbook;

            string name = sheet.Name;
            string address = rng.get_Address(Type.Missing, 
                             Type.Missing, XlReferenceStyle.xlA1, 
                             Type.Missing, Type.Missing);

            book.PublishObjects.Add(
                             XlSourceType.xlSourceRange, @"c:\Temp\Output.htm",
                             name, address, XlHtmlType.xlHtmlStatic, "", "")
                         .Publish(true);
        }
        catch (Exception e)
        {
            return e.Message;
        }
    } 

    public void OnConnection(object application, ext_ConnectMode connectMode, 
                             object addInInst, ref Array custom)
    {
        s_Application = application as Application;
    }

    // implementation of other interface methods is omitted
}

Code above works as expected and I get Output.htm when my workbook is in Automatic formula calculation mode.

However, if workbook is in manual mode (and in debugger I see that s_Application.Calculation is set to xlCalculationManual) then line Publish(true) forces all UDFs on active sheet to recalculate.

In other words, before Publish(true) finishes, I get another call to my PublishRange() method, but this time s_Application.Calculation is set to xlCalculationAutomatic.

I also discovered that functions on other sheets in the same workbook are not affected (don't know why).

Question: Is there a way to avoid this calculation mode reset?

My own function being called is not a problem, I can always add some boolean flag in there, but worksheet would have other functions that must not be invoked by anything I do.

BTW, the problem is not there when DoWork is invoked from main thread, this is specific to running a background thread.

score 0 · Answer 1 · answered Apr 02 '14 at 18:10

UDFs should always have all the cell references that they use passed in as arguments, otherwise Excel does not know when to call them in a recalc (but also note that Excel may well call a UDF more than once in the process of determining the final calculation sequence).
Also not sure if you really want to use Activesheet since this may change.

Here is a VBA version that does not get called twice in a manual calculation.

Function zPublishRange(oRng As Range)
    Dim oPub As PublishObject

    On Error GoTo Fail
    Set oPub = oRng.Parent.Parent.PublishObjects.Add(xlSourceRange, "D:\Output.htm", oRng.Parent.Name, oRng.Address, xlHtmlStatic)
    oPub.Publish True
    zPublishRange = oRng.Parent.Parent.PublishObjects.Count
    Exit Function
Fail:
    zPublishRange = "Fail"
End Function

Thanks, Charles! After reading your answer I figured out that I omitted most important part of the code that actually produces the problem: I do have a separate thread that does actual publishing. Please see my updated post. — user270576, Apr 22 '14 at 17:09

Using PublishObject.Publish() in background thread resets workbook's calculation mode

1 Answers1