PDA

View Full Version : [SOLVED] Sharing Modules



sconly
05-19-2010, 04:37 AM
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
05-19-2010, 07:05 AM
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
05-19-2010, 07:12 AM
Thanks for that.

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

mdmackillop
05-19-2010, 07:17 AM
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
05-19-2010, 07:23 AM
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
05-19-2010, 07:30 AM
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
05-19-2010, 07:47 AM
Brill!!!

Thanks for your help and advice mdmackillop!