Excel addins troubleshooting guide

Introduction

So you have deployed your awesome Excel addin in production and all is working fine for some weeks : you are enjoying your success…
But one day the phone rings and the guy at the other side is not really happy; no, he is completely upset and you could almost smell his breath when he screams.
But why? Because your awesome addin has stopped working on his workstation without notice.

Sometimes the root-cause is obvious: you have delivered a new version, but in many cases you will hardly ever know the root-cause of this annoying situation: Windows update, Office update, quick and dirty moving/installation of the addin from a workstation to another one, bad alignment of Uranus and Jupiter…

A wealth of errors can happen, then it’s hard to have them all in mind, especially when you are in a hurry and with stress you start to get mixed up. So to be as efficient as possible you’d better have a checklist and this is precisely what this article will be, enumerating all the errors I have encountered describing usual causes and solutions.

So let’s troubleshoot!

The magic solution that works almost 50% of the time

If the error message does not inspire you or Excel crashes without notice when you call an external API from VBA (this often happens when you are using a third-party API that can evolve independently of the worksheets that use it) and you want or need to move fast you can try this as a first shot:

  1. go into the VBA editor (ALT-F11 is a handy shortcut)
  2. in the top menu choose “Tools” then “References” :
    VBA Tools References

    VBA Tools References

    you should see this popup :vba_references_popupThe reference can point to an XLA file or a TLB file (which is the interface of your addin used by VBA).

  3. locate your addin reference (if selected it should be at the top), uncheck it and click “OK
  4. reopen this popup, locate you addin (you can type its first letter to directly go to the first addin whose name starts with this letter) but this time recheck it
  5. restart the operation that was broken and cross your fingers, it may be OK now
  6. still sucks? follow the rest of this guide depending on your error

I’ve seen the unreference/rereference trick working for :

  • Error 430 “class does not support automation or does not support expected interface” :
    VBA Error 430

    VBA Error 430

    which is typically the consequence of an update of your addin API like changing the signature of a method.

  • Out of memory” errors :

    VBA Out of memory error

    VBA Out of memory error

  • VBA/addin interface mismatch: from VBA you call method “A” of your addin but you end up in method “B“. I’ve seen this happen a few times, at least when the two methods have the same signature and are close in the API, otherwise you should have an explicit error.
  • and a bunch of other situations…

If your Excel application is made of normal spreadsheets (XLS, XLSM…) and XLAs do not forget to update the references for all the files referencing your addin, not only the spreadsheets.

Excel is crashing before you can try the magic solution

Sometimes you’re really not in luck and while you feel that a simple “unreference/rereference” could do the job Excel does not give you the opportunity to try it as it crashes before you can access the VBA editor.

This issue can occur if the workbook is referencing the absolute path to the TLB on your development environment and you deploy the workbook as is in your production environment where the TLB will probably be in another place (typically a sub-directory of “Program Files”).
It can also occurs if you have multiple versions of your addin on the workstation.
It can often happens on your development workstation if:

  • you develop with multiple workspaces (one for each of your SVN/Git branches) and there is a mix between the different TLBs and addins : you can try a full cleanup (including Windows registry) and rebuild from the workspace you’re currently working on
  • you use a task manager or integration server like Jenkins (ex Hudson) which builds your addin and register it into the system in your back : you can temporarily disable the task that breaks your setup

If the workbook is broken in production, you can use two solutions :

  1. Start Excel in safe-mode, either by starting it from the “Run” popup (shortcut is Windows-R) with the “/safemode” option :
    Run Excel in safe-mode

    Run Excel in safe-mode

    or by pressing continuously the CTRL key while starting it which should trigger the following popup :

    Excel start safemode

    Excel start safemode

    Once Excel is started in safe-mode load the broken workbook. You should be able to access the “References” popup and do the unreference/rereference trick.

  2. You can temporarily move or rename the TLB file and starts your Excel application as usual. Excel should not crash anymore and starts as usual, only complaining about the missing TLB. You can now move/rename the TLB as it was before, proceed to the “unreference/rereference” trick and then save the workbook to ensure the correct reference is kept. All should be fine at the next startup.

“Error in loading DLL” when referencing a TLB

If, when referencing a TLB, you see this popup :

VBA Error in loading DLL

VBA Error in loading DLL

this is probably the sign that Excel cannot access the TLB file, the root cause can be :

  • a simple installation issue : the TLB not being where it is expected (you can check the expected path in the Windows registry using RegEdit); so check the correct installation of your application and reinstall it if necessary.
  • user rights issue with the TLB, and more generally all the installation items : this can happen if you have installed your application with a user that is not the one currently trying to use the application (e.g. a remote user).
    You can then either:

    • proceed to a dedicated reinstallation for the current user,
    • change the rights of the installation items to allow him full access,
    • or add him to a user-group that has the required rights,

    whichever you find easier and cleaner in your situation.

“Error in loading DLL” when compiling the VBA project

If this error occurs when the VBA code is compiled, either automatically when accessing a function/sub that uses the addin API or manually when clicking the Debug/Compile menu item then check if you have recently changed the settings of a dependency (e.g. of your .Net (C#, VB.Net…) project) that contains COM types you expose through your addin API.

I’ve seen this issue once in a C# project with the ADODB dll when the “Embed Interop Types” option was set to “True” in the reference properties. With only this settings and “Copy Local” to “False” a method of the addin that returned an ADODB connection was broken in Excel 2003 on Windows XP whereas all was working fine on Excel 2010 on Windows 7. I’ve not dived into the details but it seems the “Embed Interop Types” feature is not retro-compatible or may need some additional setup on the workstation, which in both case made it unusable.

So, setting “Embed Interop Types” to “False” and reverting “Copy Local” to “True” solved the issue caused by Visual Studio 2010 because it activates these settings by default with .Net 4.0 projects (the project had recently been migrated from .Net 3.5 to .Net 4.0)!

ADOBD Reference Properties

ADOBD Reference Properties

As often VS wanted to help but broke something :)

The addin does not want to load

If, depending on the way you load your addin, you have the following symptoms:

  • you use the Excel interface: you can see the addin in the “COM Addins” list and you can check it:
    COM addin checked

    COM addin checked

    but when you reopen the “COM Addins” popup your addin is not checked anymore.

  • you use VBA code: when you try to set the Connect property of the addin:
    Dim boomAddin As COMAddIn
    ...
    boomAddin.Connect = True
    

    you get a “Run-time error ’287′: Application-defined or object-defined error“:

    VBA connect disabled addin error popup

    VBA connect disabled addin error popup

In both cases it’s the sign Excel cannot or does not want to load the addin.

This can happen if your addin throws an exception when loaded, ie in the “OnConnection” method; in this case you should see a warning at the bottom of your addin description, in the “Load Behavior” field:

Excel COM addin error

Excel COM addin error

You can reproduce this behavior with a simple code:

public void OnConnection(object application, Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array custom)
{
    applicationObject = application;
    addInInstance = addInInst;

    throw new Exception("Boom!");
}

But if your addin causes more serious damages like an Excel crash, what you can experiment with such a code (note that the crash can happen in another method that OnConnection with the same sanction):

public void OnConnection(object application, Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array custom)
{
    applicationObject = application;
    addInInstance = addInInst;

    System.Diagnostics.Process.GetCurrentProcess().Kill();
}

Excel won’t trust your addin anymore and may put in quarantine ALL the addins loaded at the time of the crash!
In this case, if you are lucky, at the next startup, Excel should warn you of the issue with this message:

Excel addin serious problem

Excel addin serious problem

You can choose to give your addin a second chance by answering “No” or you can answer “Yes” so the addin will stay disabled and you won’t be able to load it into Excel until you notify Excel the addin should not be kept in quarantine any longer.

But if you are in doubt and think your addin may have been disabled how to check ?
You have at least two solutions :

  •  using Excel 2010 : go to the “File” ribbon tab then “Options” :
    Excel2010 File Options

    Excel2010 File Options

    You should see the “Excel Options” popup in which you now select “Add-Ins” :

    Excel 2010 Options Add-Ins

    Excel 2010 Options Add-Ins

    From here scroll down to the bottom of the list where the disabled items are listed.
    Here is an example of a too zealous Excel that has disabled all the COM addins (and they were a lot!) :

    Excel2010 Disabled addins

    Excel2010 Disabled addins

    To show the culprit of all this mess use the “Manage” drop-down at the bottom of the window and choose “Disabled Items” :

    Excel 2010 Go To Disabled

    Excel 2010 Go To Disabled

    then click the “Go…” button to show the bad guy :

    Excel 2010 Culprit Addin

    Excel 2010 Culprit Addin

    If you forgive your addin then select it and click “Enable” then “Close” :

    Excel 2010 Addin Reenable

    Excel 2010 Addin Reenable

    You should now be able to reload it and all the other addins disabled due to the crash via the “COM Add-Ins” popup.

  • using the Windows registry : Excel stores the disabled items list into the registry under the keys “…\Software\Microsoft\Office\14.0\Excel\Resiliency\DisabledItems“, e.g. “HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Resiliency\DisabledItems“. If an addin has been disabled you’ll find a binary key holding its name :
    RegEdit DisabledItems Key

    RegEdit DisabledItems Key

    We can read the identity of our explosive addin : “boomaddin”.
    If you want to reenable it you can either:

    • delete the numerical entry (here “5793E01E”)
    • delete the whole “DisabledItems” key

    Note that this change will be effective only for the next Excel instances, so you’ll have to restart your application to be able to load your addin as usual.

NullReferenceException thrown by the .Net (C#, VB.Net…) addin code

Object reference not set to an instance of an object” like errors :

VBA Object reference error popup

VBA Object reference error popup

generally happen when your .Net addin has not been loaded by Excel, so initialization code has not been executed (typically the part that captures the Excel application instance loading the addin).
Therefore your addin code may be naively accessing not initialized references.

Indeed you’ll typically use code like this one :

public void OnConnection(object application, Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array custom)
{
    // keep the application object for future use
    applicationObject = application;
    addInInstance = addInInst;

    // other initialization code
}

public void DoSomething()
{
    // oops! if the addin is not loaded this code will throw a NullReferenceException
    (applicationObject as Application).ActiveCell.Value2 = "Something";
}

If the addin is not loaded the “OnConnection” method will not be called, then the “applicationObject” reference will not be initialized with the running Excel instance.

It could also happen that something is broken inside the “OnConnection” method itself preventing it from running to its end, letting the addin half-initialized (which is far more dangerous than not initialized at all as the resulting errors and behavior will be more surprising and entertaining ;) ).
So you should always keep track of your addin state and notify of any inconsistency : using a simple boolean flag set to “true” at the end of initialization (typically at the end of the “OnConnection” method) should be sufficient in most cases.

So refer to point 5) to check the status of your addin.

Generic run-time errors

Sometimes you get this kind of anonymous error popup :

Excel Automation error

Excel Automation error

To make sense of this cryptic and apparently useless message you should lookup your error-code/HRESULT (here 80131524) in this list.

In this case the error was COR_E_DLLNOTFOUND due to a missing native DLL used through DllImport by the C# addin (itself called by a VBA macro).

Deployment of native DLLs often causes issues that you can troubleshoot using Dependency Walker (see my article about it).

All other unknown mystic errors

If you have any error not referenced here, for which you don’t find a solution by googling it and whose root-cause is impenetrable then, in desperation, you can try to remove any trace of your addin in your system :

  • remove all the files and directories that contain the assets of your application like XLS/XLSX/XLSM spreadsheets (whose references could have been corrupted), DLLs and TLBs
  • cleanup the registry by hand by looking for all the references to your addin : DLLs and TLBs keys should be enough but you can also remove all the COM registration information of your types.

Then reinstall the addin and cross your fingers…

It should be relatively uncommon to need this : in the last year I’ve never had to go this far thanks to my better understanding of Excel addins and this kind of checklist.

Conclusion

If this article has helped you troubleshoot an issue I’d like to read your story.

If you have any remarks or suggestions about this article or if you know other errors with their standard fixes please share by letting a comment, I’ll do my best to update this article with your feedback.

29 thoughts on “Excel addins troubleshooting guide

  1. Thanks for teh excellent article. I am facing the following problem….not very different from one you have described above:

    I had an Excel Add-in which has been working fine for the last 2 years. An update was due and i made the change son my machine where the addin was also installed. At the end of the development i tried installing it on the machine, and it woudl not succeed. The error message i kept getting was that the xlam file could not be accessed and there could be several reasons for that…and then the three standard reasons (-The file name or path does not exist, -The file is
    being used by another program, -The workbook you are trying to save has the
    same as a currently open book.)

    I tried your magic solution, but the add-in is not installed, so cannot get it in the references and cannot install it because it does not allow. i can open teh xlam file and see the code. everything looks fine and i am able to install and uninstall in F8 mode.

    The only thing different from teh previous Version is that in this i have added a form and on the form used MSCAL.OCX, which is not part of MSO 2010, and was used from MSO 2003 and registered in system32 directory. Any idea how that might effect?

    Would appreciate your help on this.

    Thanks,
    Rajeev

    • Hi Rajeev,

      how exactly are you installing the addin on the new workstation?
      Could you provide some screenshots of the error(s)?

      Thanks.

  2. Hi,

    I have a similar problem, the plugin has been working fine, but since I need to re-install it every time I debug it, some of the items are not visible, after I installed it again. to enable those hidden items, I have to restore the entire system, of which I’m out of earlier restore points that can revert the system back to previous session with working plugin. I tried solving it the way its stated above, but still does the same thing.

    Thanks in advance,
    Jay.

    • Hi Jay,

      are you using the “setup project” provided by Visual Studio?
      If yes you’d better not use it, it’s a big source of troubles when deploying the addin to other workstations, and even on your own workstation sometimes, as you may experimenting.
      What are the “items”: classes, methods, both…?
      Have you tried to “rebuild” your addin project, Visual Studio should register the newly created debug version of your addin.

      Regards

  3. Ignore my above post.

    Hi,

    am having problem with deleting an excel add-in from the Add-in’s list after uninstallation.

    Name of the add-in (oracle hyperion smart view_11.1.1.3) for excel 2010

    After uninstallation of the add-in,

    If I launch the Excel it gives me “C:\Oracle\Smartview\HsTBar.xla not found. if you are using it from recently added list, make sure it is available, not modified, not renamed”

    I tried,

    1) Deleted all the HKCU related to the add-in

    2)I know this key should be deleted. But, there is no entry for this add-in this key,” HKLM\Software\Microsoft\Office\Excel\option\open”

    3) some articles say, “raise ticket withOracle” is it really that bad.

    4) a script which unchecks the option in excel after uninstallation. but, can’t run it as a system (I need to run it as a system since we use SCCM2012 for deployments enterprise wide).

    It’s a nightmare, any suggestions appreciated. Thanks!

    Regards,
    Shiva

      • Hi

        Thanks for the quick response! Forgot to mention above “your article is amazing”.

        No, first thing once I get to work. What if it is referenced?

        Will their be any reg keys to that should be taken care of upon uninstallation?

        regards,
        Shiva

        • Thanks for the kind words. :)

          If it’s referenced there then just uncheck it.

          Moreover watch out for the binary format of your Office applications: if you are on a 64bit system and use a 32bit Office (a really common setup) then some keys are in the Wow6432Node registry hives.

          To be sure your registry is clean make a full registry scan/search so that you won’t miss any key related to the addin.

          • Yep. will see if the “uncheck” in the developer tab remove’s any of the reg keys in the Wow6432Node. Thanks!

  4. Hi,

    you were right! although Add-in windows installer removes all the keys and main Add-in from the ribbon except for the HKCU keys (none of the HKCU are related to the developer tab add-in entry). There is an entry for this particular file HsTbar.xla in the developer tab “Add-in’s”. When click browse in the add-in list, excel says “cannot find add-in “C:\Oracle\SmartView\bin\HsTbar.xla”. Delete from the list?” pop-up.

    I need to delete/uncheck it on the fly when uninstalling. is there any particular reg key in HKCU or HKLM I should be looking into. Appreciate your inputs!

    Regards,
    Shiva

    • Hi Shiva,

      in your registry check the entries under:
      HKEY_CURRENT_USER\Software\Microsoft\Office\<your_excel_version>\Excel\Add-in Manager

    • Even on a machine where you have the message “C:\Oracle\Smartview\HsTBar.xla not found. if you are using it from recently added list, make sure it is available, not modified, not renamed”?

      If you scan the registry from the root searching for “HsTBar” you should find the key.

  5. This script would uncheck the add-in from the add-in’s list. But, couldn’t run it as a system.

    My last attempt would be to create an AutoIT .exe, atleast it avoids the pop-up for the users when they launch the excel.

    Dim WShell,crAddin
    on error resume next
    Set WShell = WScript.CreateObject(“Excel.Application”)
    for i = 1 to WShell.Addins.Count
    Set crAddin = WShell.Addins.item(i)
    If crAddin.Name = “HsTbar.xla” Then
    crAddin.Installed = False
    End If
    Next
    WShell.Quit
    Set WShell = Nothing

    • Hum, really strange…
      I guess you have administrator rights on the workstation?
      Moreover have you double-checked the Tools/References menu in the VBA editor?

      • Yes sir.

        After hours of reading blogs about this issue. two things,

        1) Microsoft’s KB211956 says that the using older version of this app on newer version of Excel is a reason for notification pop-up’s

        2) some say, simply, raise a ticket with Oracle :D

        • Fact, am bad at VB scripts. Atleast am trying to get a script where I could run it in System Context. But, I couldn’t find any.

          • Would it be possible that one of your XlStart file is referencing the xla?
            Excel is not making up this reference, it should be either in the registry or in an Excel file. :)

  6. No, definitely Excel is not making up. Am wondering where Excel stores the reg keys for the Add-in list (when checked) other than where it usually stores entries.

    • The registry is the only place I know for storing the addins list that appears in the Developer tab under Add-Ins.
      And the file itself stores the addins list that appears in the VBE editor in Tools/References.
      If your addin is not referenced from any of these places and that Excel still complains I’d be curious to know from where it’s fetching it…

      • Hi,

        Hapy new year!

        I had to move on with other priorities putting this issue aside.

        Coming to this agian, We use Appsense in our environment which virtualizes the “User state” all of the user data stored in “For ex: my documents, desktop” will be carried with the profile.

        Excel is trying to load the plug-in “HsTbar.xla” from that particular location on the sever “mydocuments” folder. So, I diabled the service and tested it.

        Now, I can see all the “options” key data Values “open” “open1″ etc.

        however, if it’s loading from %localappdata% excel folder we should able to see this add-in in there. But, surprisingly, there are no .xla files anywhere in the XLStart or %localappdata% folders.

        Freaking IT :D

        • Hi Shiva,

          Happy new year too! :)

          If you have such exotic behavior I fear you’ll have to make a full scan of the workstation to find the HsTbar.xla file Excel is using.

          Moreover if you want to be sure there is no cheat you can (at your own risk ;) ) unplug the network cable of the workstation and check how Excel reacts.

          Whichever the issue, share your feedback on it.
          Hopefully it will help someone else. :)

  7. Hi,

    Finally, This issue has been resolved.

    As it appears, our Appsense version 8.6 was causing this.

    Turning off that service or Uninstalling it or upgrading to next version solves this issue.

    Now, without Appsense ver 8.6, the “open” string’s were created in “options” key and we are deleting that HKCU key through a VBscript upon uinstall.

    Earlier, it didnt even create that key inorder for the script to work.
    Thanks for your follow up and suggestions. Appreciate it.

    Regards,
    Shiva

Leave a Reply

Your email address will not be published. Required fields are marked *


6 × four =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>