[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-11-01
Medium Priority
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
  • 2

Accepted Solution

philip m o'brien earned 2000 total points
ID: 37062487
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

ID: 37063335

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.

Expert Comment

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

834 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