Microsoft Excel, protecting formulas and vba code

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
JBM9966Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayData AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JBM9966Author Commented:
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
Glenn RayExcel VBA DeveloperCommented:
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
Rory ArchibaldCommented:
FYI, the web apps won't run VBA, which sounds like it would be an issue.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.