Manage & Reference Macros externally, instead of embedded in the spreadsheet

Posted on 2011-11-01
Last Modified: 2013-11-05
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.
Question by:1SmokinJoe
    LVL 7

    Accepted Solution

    You can do this relatively simply by going down the authorised XLAs route, but it requires you to firstly create (or have a developer create) the add-in/s you want, and then to deploy them. The following links may be of help in getting you started down this route:

    Give me a shout if you would like me to go into this in more detail.

    The other option would be to go down the route of having fully-fledged dictator applications, but that's a fairly hefty route to go down as they require extensive development.

    Author Comment


    Thanks for the links.  We may try the group policy method, using a batch file.  I didn't think of trying that, but it makes sense.  We'll do some experimentation/testing to see how it all works forus.
    LVL 7

    Expert Comment

    by:philip m o'brien
    No problem, if you find you need further help then just let me know.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now