Solved

Microsoft Excel, protecting formulas and vba code

Posted on 2014-09-25
4
527 Views
Last Modified: 2014-10-07
I have a MS Excel 2010 .xlsm Workbook that I need to make sure to protect\hide the formulas and the vba code, well beyond what Microsoft offers in forms of protection.

The big sticking point up to now has been that this workbook uses Frontline System's Analytical Solver and that has proved to be incompatible with all the compile and/or encryption, 3rd party tools I've tested to protect the formulas and vba code.  I've tried LockXLS, DoneEx, XLSPadlock, and SpreadsheetSentry.

All of these have caused issues for Frontline Analytical Solver where it cannot interpret the formulas correctly after they have been compiled and/or encrypted.

I also need to user to be able to enter values in certain input fields and also save copies of the .xlsm file.

I'm looking a total rewrite in the face, so I wanted to check here for any other ideas.  A rewrite of this from Excel VBA to .Net would be a huge effort.


Thanks,
Jon
0
Comment
Question by:JBM9966
4 Comments
 
LVL 10

Accepted Solution

by:
Ray earned 200 total points
ID: 40344983
If you're that serious about protecting what's 'under the hood', then sadly a programmatic method is the only way I know about.  You can hide things from most users fairly easily, but to truly lock it down 100% without losing functionality...I have never seen it implemented.  Sorry.

Maybe other experts could weigh in on some kind of 'bullet proof' protection?
0
 

Author Comment

by:JBM9966
ID: 40345074
Thanks, I've been reading up a bit on Excel 365 Online and SharePoint 2013, it sounds like you can secure workbooks in a more enterprise type manner, but I'm not sure it would allow me to protect\hide formulas and vba, while also allowing the user to enter into some input cells on the workbook.

Jon
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 200 total points
ID: 40345126
Excel password protection is not bombproof; there are plenty of crackers and tips online for hacking.

That said, you can do a couple of things to protect from regular users.
1) With Sheet protection, limit entry and cell selection to unlocked cells only:
sheet protectionThis will prevent users from clicking on cells which likely contain formulas and therefore, won't be able to see or edit them.
2) With VBA, protect your project (VBA Menu: Tools, VBAProject Properties)
VBA protectionIt probably goes without saying, but use a different password for this than for sheet protection!

-Glenn
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 100 total points
ID: 40345907
FYI, the web apps won't run VBA, which sounds like it would be an issue.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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.

757 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

21 Experts available now in Live!

Get 1:1 Help Now