Threading errors when retrieving ActiveWorkbook CustomDocumentProperties

[Richard Lewis]

I'm using CustomDocumentProperties to store user settings from a CustomUI ribbon. It works the first time the addin loads. If I save the workbook and open it an exception is thrown on 

        public bool ContainsKey(string key)
        {
            var props = ws.CustomDocumentProperties;

System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800401A8'

If I unload and load the Add-In it goes away. Not sure how to fix this. 

using System.Runtime.InteropServices;

using ExcelDna.Integration;

using ExcelDna.Integration.CustomUI;

using Microsoft.Office.Core;

using Microsoft.Office.Interop.Excel;

using IRibbonControl = ExcelDna.Integration.CustomUI.IRibbonControl;

using IRibbonUI = ExcelDna.Integration.CustomUI.IRibbonUI;

namespace TradeService

{

    [ComVisible(true)]

    public class MyRibbon : ExcelRibbon

    {

        private static IRibbonUI _ribbonUi;

        private Workbook ws;

        public void Ribbon_Load(IRibbonUI sender)

        {

            var xlApp = ExcelDnaUtil.Application as Application;

            ws = xlApp?.ActiveWorkbook;

            sender.Invalidate();

            _ribbonUi = sender;

        }

        public string GetEditBoxText(IRibbonControl control1)

        {

            return GetDocumentProperty(control1.Id);

        }

        public void EditBoxTextChanged(IRibbonControl control1, string text)

        {

            switch (control1.Id)

            {

                case "envComboBox":

                    var config = new Config();

                    SetDocumentProperty("url", config[text]);

                    _ribbonUi?.InvalidateControl("url");

                    break;

            }

            SetDocumentProperty(control1.Id, text);

        }

        public void SetDocumentProperty(string propertyName, string propertyValue)

        {

            var props = ws.CustomDocumentProperties;

            if (!ContainsKey(propertyName))

            {

                props.Add(propertyName, false, MsoDocProperties.msoPropertyTypeString, propertyValue);

            }

            else

            {

                var property = props[propertyName];

                property.Value = propertyValue;

            }

        }

        public bool ContainsKey(string key)

        {

            var props = ws.CustomDocumentProperties;

            foreach (var property in props)

                if (property.Name == key)

                    return true;

            return false;

        }

        public string GetDocumentProperty(string propertyName,

            MsoDocProperties type = MsoDocProperties.msoPropertyTypeString)

        {

            if (!ContainsKey(propertyName))

                return string.Empty;

            var props = ws.CustomDocumentProperties;

            var value = props[propertyName];

            return value.Value;

        }

    }

}

[Govert van Drimmelen]

Hi Richard,

I'm not sure why you're mentioning "Threading errors" in the title.

The HRESULT 0x800401A8 that you see maps to VBA Error 424 (converting the hex digits 01A8 to decimal).

VBA Run-time error '424' means 'Object Required'.

So you're getting a null reference exception on the line 

    var props = ws.CustomDocumentProperties;

which means that ws is null at this point.

You're setting the ws field in the Ribbon_Load handler, which is called when the add-in is loaded, and you call

    var xlApp = ExcelDnaUtil.Application as Application;

    ws = xlApp?.ActiveWorkbook; 

However, there is no reason to think there is an ActiveWorkbook loaded at this point, as the add-in could be loaded before any workbook is activated.

When you then unload and re-load the add-in, most likely a workbook is already open, and the code sets the ws field to a valid objects.

You will have to reconsider the relationship between the add-in and the workbook in question - perhaps you want to refer to the workbook that is active at the moment when the user presses the button, rather than the workbook which was active when the add-in got loaded.

-Govert