Solved

Microsoft Excel, protecting formulas and vba code

Posted on 2014-09-25
4
559 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

777 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