Addins: Usage and Distribution

[James_B]

Does anyone have any helpful hints on how best to use Addins? Basically, I want to have a load of different spreadsheets on a drive somewhere that all run macros from a common AddIn. That way I'll only have to change things in one place when they go wrong. What's the best way of achieving this? Do I need code to install the AddIns in each workbook in order to make sure that updates to the AddIn are effective? And what code should this be? (I tried various pieces but none without problem). Also, the spreadsheets will be used by all sorts of different people, so the AddIn shouldn't be something that needs to be installed manually.

Thanks for any help people can offer, James.

 

[mathias.brandewinder]

Hi James,

I don't have experience with "classic" add-ins, but have written a few using VSTO, so I can talk a bit about add-ins, hoping that it generalizes to other add-ins, and/or that others will complement for classic add-ins.

VSTO add-ins exist in 2 flavors: document customization, and Excel customization. I believe the Excel customization is typical for non-VSTO add-ins. In that case, the Add-In is "added" to the Excel instance that runs on your machine, and present regardless of the document you are using. If you modify the add-in, it will propagate mechanically.

But in your scenario, if you expect users to all read the same documents from a shared drive, by default, the user won't have the add-in installed, until they install it on their local machine. So if you were to change your add-in, every user would have to install the new version.

I have never used it myself, but VSTO allows you to actually publish an add-in in a central location using ClickOnce, and "push" updates to users when there are updates. That being said, first I have never used it myself, and then I have to warn you that VSTO development is not for the faint at heart; if you do not have development experience besides VBA, there is a very heavy upfront cost in learning it.

Hope this helps!

Mathias

 

[RoryA]

If you set references in the workbooks' projects to the add-in projects, then you can use call the add-in code directly. Once saved, the references stay with the workbook (just as any other reference does) so your users shouldn't have to do anything else.

 

[James_B]

Thanks for the replies. Rorya: I need to be able to update the content in the AddIn, and this wasn't happening when I simply added it to the references (though may be I did something wrong). In the end, I just put some code in the workbook's Open and Close events. I can't remember exactly what it was, but it went something like

Public poAddIn as Object
Sub OnOpen....
Set poAddIn = Addins.Add(sNameOfAddin)
poAddIn.Installed = True
End Sub
Sub OnClose
poAddIn.Installed = False
End Sub

Oddly, this worked when I declared poAddIn as an object, but not when I declared it as an Addin. Anyway, I think this is doing what I want. Can anyone see any obvious drawbacks?