Sharing Modules

[sconly]

Is it possible to have a centralized module that you can share in multiple workbooks, and have any changes made to that central module pulled throught to the wrokbooks its used in.

For example, a module that can be used to do numerous worksheet formatting routines.

Or will I have to insert the module into each workbook and make the changes to each module in each workbook its used in?

Thanks!

[mdmackillop]

You can create an Add-In (XLA) file to contain the code. Whilst the add-in is open, any workbook can call on the code within it. This can be stored on a server, or copied to each user's computer. The former makes it easier to maintain.

[sconly]

Thanks for that.

So if I make any changes to it will I have to re-apply the add-in to each workbook?

[mdmackillop]

No. The add-in is normally loaded when Excel starts and will apply to all workbooks.
Conversely, you could have code in each workbook of a certain type which will install the add-in and unload it upon closing, if it is of a specialist nature.

[sconly]

An add-in is what i'll make.

Thanks. Nice one!!

By-the-way, what would this code you talk of look like (install/unload add-in)?

[mdmackillop]

For such as these, simply record a macro. That gives me


Sub Macro2()
' Macro2 Macro
' Macro recorded 19/05/2010
AddIns("Solver Add-in").Installed = True
AddIns("Solver Add-in").Installed = False
End Sub



Add the relevant bit to an Open/Close event

[Code]
Private Sub Workbook_Open()
AddIns("Solver Add-in").Installed = True
End Sub

[/VCode]

[sconly]

Brill!!!

Thanks for your help and advice mdmackillop!