What's the correct way to share macros in secure environments?

[rhelm]

I'm a fairly new Excel user but I've been actively creating formula's for my work mates. I would like to also share with them some saved Macro's that I keep in the 'Personal.xlsb'.

My company has pretty strict security policies, so I'm hesitant to start asking anyone to change their Trust Center macro settings.

So I'm wondering if there is a recommended easy/safe way to make these available for my colleagues? For example, if they're Trust Center settings are locked for Macro's, could they still import one my exported .bas files and use it without having to change their files to .xlsm?

Regards,
rhelm

 

[jkpieterse]

- Copy the relevant modules to a new Excel file (you can simply drag them in the project explorer window in the VBA Editor).
- Then save-as the new Excel file, first as xlsm, after than as an add-in (xlam).
- Place the add-in in a location your colleagues have access to.
- Tell them to open the Add-ins dialog and browse to your add-in.
- Let them click No if asked to copy the add-in to their local add-ins folder!
- Before telling anyone: Right-click the add-in file in the shared location, choose Properties and set it to read-only. By doing so you can simply replace the add-in file when an update is there (don't forget to set it to read-only again!) and notify your users to reboot their Excel.

 

[rhelm]

Interesting....I'll definitely give it a try. Thanks, jkpieterse!

rhelm

 

[rhelm]

jkpieterese,
Could I trouble you to clarify this section: "after than as an add-in (xlam)."
- Then save-as the new Excel file, first as xlsm, after than as an add-in (xlam).

Not sure I'm following that part. Save as a xlsm and then re-save as an xlam??

Regards,
rhelm

 

[jkpieterse]

Further reading: https://jkp-ads.com/Articles/buildexceladdin.asp

 

[rhelm]

Finally got it....using your steps. Thanks again!

 

[jkpieterse]

I always prefer to edit the xlsm file rather than the xlam. When I'm done updating and testing I save-as the xlsm (to a new file) and then save-as the xlsm to xlam.