Central VBA code accessed and run by multiple files/users

[pvans267]

Good morning,

I hope someone can help me with this. I have been developing a macro enabled workbook that essentially is a template for a number of users across the organisation to use.

Essentially, the users will each be provided a version of this workbook that they will then use to enter data, and run the macros (which do Data validation checks and basic formatting) before they send their data back to me.

However, the code for the procedures and functions that are used within the document may (almost definitely will) be updated and modified by me during the project (but will always remain standard across all the different user versions) - and therefore I would currently need to resend new versions to each of the users and then also ensure that the document they return is the new version with the latest version of the VBA code.

Is there a way in which I am able to have a central "VBA code" workbook that stores the Functions and then have each User's individual workbook simply call this central VBA code and effectively run it - applying any formatting/calculations - within the users document?

(I really hope that makes sense, I have tried to be as descriptive as possible. Below is a very basic Pseudo Code example of what I want to achieve)

User doc:
Sub CallCentralVBA
Workbooks.Open "CentralVBA.xlsm"
Call ApplyFormattingAndValidation <-- This is a sub housed in the Cental Doc, but all formatting is applied within User doc
End Sub

Central Doc:
Public Function ApplyFormattingAndValidation
DoStuff
End Function

 

[Jaafar Tribak]

Not sure if this would work but if the central vba code workbook is located on a shared drive that the users can access then you could make this central workbook ReadOnly and add some code to each user's individual workbook (in their respective Workbook Open event) that opens the central workbook .

Once the read only copy of the central workbook is loaded on each user's excel instance, they should be able to call the generic functions using the Application.Run method or something else.

 

[jkpieterse]

This basically is what add-ins are for. See:
http://jkp-ads.com/Articles/buildexceladdin.asp
http://jkp-ads.com/Articles/UpdateAnAddin.asp
http://jkp-ads.com/Articles/FixLinks2UDF.asp

 

[pvans267]

Hi,

Thank you so much for your replies.

I have actually managed to find what appears to be a very useful solution. Essentially, the user docs will import the macros from a central location in their .bas format and then run them.

I can therefore ensure that their versions are all up to date. I found the answer here: https://stackoverflow.com/questions/17616366/import-and-run-macro-from-text-file

Seems like a really nifty solution

Thank you for your help and speedy replies though!

Kind regards,

Paul

 

[Jaafar Tribak]

Hi,

Thank you so much for your replies.

I have actually managed to find what appears to be a very useful solution. Essentially, the user docs will import the macros from a central location in their .bas format and then run them.

I can therefore ensure that their versions are all up to date. I found the answer here: https://stackoverflow.com/questions/17616366/import-and-run-macro-from-text-file

Seems like a really nifty solution

Thank you for your help and speedy replies though!

Kind regards,

Paul

Bear in mind that the Trust Center Settings on each user's excel must be set .