What's the proper way to edit an addin?

[scottbass]

This is related to http://www.excelforum.com/excel-prog...ml#post3571485

I open my end user workbook, which has a reference to my add-in. So, when I open the VBE, I see both the add-in and the workbook.

I debug the code in the xlam project, save everything, close Excel, and reopen the end user workbook.

All my edits in the xlam are gone. Not exactly user-friendly

[MickG]

After you edit the addin, and with the code window still open Click "File" then from the dropdown, Click "Save Add1.xla", or whatever its called

[D_N_L]

Hi,

I have a few people using an add-in I have created. They have all added it from a location i have specified so that i know they are all suing the same one that i am using. All i do is this:

1. Open a blank excel doc
2. Alt+F11
3. Find the add in module code > insert password
4. Change code and then save and quit
5. Everyone's code is updated!

I've made a few changes to my add-in and it's worked great. Of course you need to be doubly sure of the code you are tweaking as you are directly overwriting your original code.

[scottbass]

After you edit the addin, and with the code window still open Click "File" then from the dropdown, Click "Save Add1.xla", or whatever its called

This doesn't work on my system (Win7 x64, Excel 2007 x86):

* open the workbook
* the add-in is opened as well due to the reference
* I see the add-in in the VBE (as well as another add-in I've installed)
* I edited the class module in the add-in
* File -> Save Midas.xlam, where Midas.xlam is the add-in physical file name
* Close Excel
* re-open the workbook
* The changes are not there in the class module

If it matters, the add-in is installed on a network location. I originally saved the XLSX to XLAM, specifying the network location. When I added the add-in to Excel (Excel options --> Add-Ins --> Browse), I chose No to copy the add-in to my local add-ins location.

Also, after I File -> Save Midas.xlam, I checked the timestamp on the physical file. The timestamp on ~Midas.xlam shows the time the add-in was opened. The timestamp on Midas.xlam shows the time I first created it, by saving Midas.xlsb as an add-in. Neither file's timestamp updates when I File -> Save the add-in.

[shg]

You have to select the VBA project for the add-in in the Project Explorer window. Then you can do file > save, or just press the VBE Save icon.

[scottbass]

You have to select the VBA project for the add-in in the Project Explorer window. Then you can do file > save, or just press the VBE Save icon.

I've added a MS Word doc with screenshots.

[shg]

You sure there aren't two copies of the add-in? Suggest you do a file search.

[Izandol]

I do not trust save button in VB Editor so I always have a simple ThisWorkbook.Save routine in the add-in.

[scottbass]

I do not trust save button in VB Editor so I always have a simple ThisWorkbook.Save routine in the add-in.

Thanks Izandol, this solved the issue.

In the ThisWorkbook module in the add-in:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    SaveAddin
End Sub

In a standard module in the add-in:

Option Private Module
Option Explicit

' Workaround an Excel bug when saving an add-in
Sub SaveAddin()
    ThisWorkbook.Save
End Sub

I now can make any changes in the add-in, and when I close the end-user workbook referencing the add-in, it saves the add-in changes.

If I comment out the call to SaveAddin, and just click the Save button from the VBE (when the add-in project is selected), I lose the changes when I restart Excel.

This will save the add-in code when the end users close the workbook (bad?), but ensures that I never forget to save changes in the code (good).

The only thing I worry about is 1) once it's production, several users will be accessing the add-in concurrently, and 2) once the add-in code is frozen, I intend to make it readonly, and don't want an error dialog when it's trying to save the add-in code.

@shg: There were no other copies of the add-in on the file system. Perhaps it's an Excel bug fixed in a later release than 2007?

[scottbass]

There were issues with calling SaveAddin from Workbook_BeforeClose when the add-in was read only. I moved the call to Workbook_BeforeSave. I'll just have to remember not to forget to save add-in code changes.

[shg]

Wow -- I have never seen that problem.

[Izandol]

I only run this manually. An add-in should not require to save itself normally.