Manage & Reference Macros externally, instead of embedded in the spreadsheet
Posted on 2011-11-01
We have an engineer at our company that likes to create a bunch of spreadsheets and write macros for just about anything he can think of (he apparently has alot of free time at home). We can't verify or validate the macros are non-harmful and want to be able to control the use of macros in Excel in general.
I've been digging around trying to find a way to remove all macros from an Excel document and then have the document reference the macros from an external source (network path), basically having the document access and use the macros in the external macro file (.bas?). The only thing I could come up with was creating an Add-In, which I did as a test and that works fine. But we don't want to have to make users activate an add-in manually and we couldn't find a way through Group Policy to get Excel to auto-install the add-in. The only thing we could find was using a macro to do the add-in install, which defeats the whole purpose of removing macros from the document itself.
Basically, we want to change our Macro security to High and manage the macros in an external file (like one master macro file or several macro files). Is there any way to do this? We're hoping that, by doing this, we can curb the mass macro creation that this engineer is doing and give us more control over what macros are being used.