Share VBA Macro Between Files 2

[mrsnrub]

I have a situation where a number of files (Excel and Word) all need a particular macro to run when they are opened. I am trying to decide on the best way to do this.

I could add the code to a .bas file and import into all applicable files, but if the code needs changing I would need to re-import into all the files. It would be great to maintain it in a single place and not have to re-import the module when it changes.

My thoughts at this stage are that potentially creating an add-in to maintain the code centrally would be a good solution. I would then call the functions within the add-in when the files open.

Does anyone have any other suggestions on any better ways to achieve this?

Thanks.

 

[bartrein]

Creating an addin would be the best approach.

Some useful links :

http://www.ozgrid.com/VBA/excel-add-in-create.htm

http://msdn.microsoft.com/en-us/library/aa140990(office.10).aspx

 

[MakeItSo]

How about compiling your cross-app functions in an activex-dll and simply adding a reference to it in your Excel/Word macros?


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[fumei]

Well I certainly would take care to separate Word and Excel.

For Word, it is easy. Put your code into a .DOT file. That file can be anywhere. When you want to use it, load it as a global template. When you are done (if this is applicable), unload it.

Or, if you want it always loaded, put it in the Startup folder. However, Startup of course is local.

Add-ins are the way to go. You can have one file (thus easily managed), on a network drive.

Gerry

 

[HughLerwill]

>For Word, it is easy. Put your code into a .DOT file.

And, although I have never really been there, I believe similar stuff is possible by placing a file, containing the code to be shared, called 'Personal.xls' in the same folder as EXCEL.EXE.

Others more expert than me may elaborate..., or Google for Personal.xls.

 

[Gavona]

In Excel Personal.xls is fine if only one user. Otherwise: In each of your workbooks include code to open the workbook containing the code from a shared network location:

Private Sub Workbook_Open()
Workbooks.Open Filename:="Z:\WorkGK\Macros.xls"
Application.Run "Macros.XLS!AdvancedFilter"
'also close the workbook if not required later
End Sub

Tip: The macro workbook can be hidden and can be read only.


Gavin

 

[mrsnrub]

Thank you all for your responses and suggestions.

In regards to the Activex DLL suggestion, the requirement is not cross-application, sorry. I have the same requirement for both Excel and Word, but they will be separate macros.

I believe I will go with the suggestion to store these macros in hidden networked files and load as required. This will save me the hassles of having to deploy add-ins to the users.

Thanks again.