Excel VBA: share procedures / macros with users

Luis Diaz
Luis Diaz used Ask the Experts™
on
Hello experts,

I have multiple vba procedures which I would like to share with final users so they can re-use it when they open excel application. I was wondering what is the best way to automatically share with them those procedures. I was thinking to add all those procedures into an add-in file and then share the file however I don’t know if it is the best way.

Thank you very much for your feedback.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Using an add-in is probably the best way, though you might need to rewrite some of the code to ensure it targets the correct workbook.
Luis DiazIT consultant

Author

Commented:
All the procedures are related to activesheet so I will not have issue with this. Experts: is it possible to share a script example that allows to automatically add procedures in excel application?
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
All the procedures are related to activesheet so I will not have issue with this.
Highway to hell !!
What if the user click on another worksheet ?
What if the user click on another workbook ?

10 bucks your code will fail.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Roy CoxGroup Finance Manager

Commented:
When writing code for an addin you cannot code a workbook name or a worksheet name when writing the code. Therefore, it is necessary to use ActiveWorkBook or ActiveSheet. You can add a check asking the user if the correct sheet is activated or  if the there is a naming convention for the sheets to use, e.g. "Timesheet April", then something like

If Left(ActiveSheet.Name, 9) = "TimeSheet" Then
''code here
Else: MsgBox "Are you sure the correct sheet is activated?"
End If

Open in new window

Luis DiazIT consultant

Author

Commented:
Thank you very much for your help.

the question  here is to find a way to: share procedures with other users and add those procedures in excel tool bar. Is there way to do this automatically through vba?
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Make a wokbook holding your VBA functions or an access database, and reference it.
Group Finance Manager
Commented:
You need to create an Excel addin. Read this article about how to do this and post back with any questions.

Creating An Add-In From An Excel Macro.

There's an article and template to help you make your own Ribbon here

My First Addin
Luis DiazIT consultant

Author

Commented:
Roy, great links! Thank you very much for sharing!
Roy CoxGroup Finance Manager

Commented:
Pleased to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial