Excel 2007 shadow copy ?!

Question

Hello,

my question is about Excel addins and vba code.

This is the situation:

There are 2 Excel files. One is just a normal Excel sheet, one is a .xla addin which is stored on a network drive. In the .xla file I have the Vba code. In the normal Excel sheet is a reference to the .xla file so that the sheet can use the macros from the .xla file. This works fine so far, but sometimes there themes to be a bug. It looks likes the sheet uses some kind of "shadow copy" of the .xla file. When the bug occurs and I change code in the Vba editor or I set a breakpoint, it is both ignored during running the macro. It feels like there is a old copy of the .xla file, which is not the macro i see in the editor, which is invisible and which is somehow stored in the normal Excel file.

I think this problem occurs since I installed MS Office 2007

I hope someone can help me.

Thanks and Bye Johannes

Answer 1

I wouldn't be surprised if Excel is storing a version of your add-in locally. When making a change to your xla, I would be sure to re-add your add-in to ensure the latest version is installed.

EDIT:

You could try doing something like this:

Private Sub Workbook_Open()

    Application.DisplayAlerts = False

    AddIns("Your Library Name").Installed = False 'To remove current link
    AddIns.Add Filename = "\\Your Server Path\Excel_Library3.xla"
    AddIns("Your Library Name").Installed = True

    Application.DisplayAlerts = True

End Sub

That would make sure the latest addin is loaded from your network location when they open the excel sheet.