Protection Strategies in Excel

Why protect your Excel Worksheets? How to do it right? How to employ a protection strategy in a tight business application based on Excel? Can you protect your IP vested in your VBA code? Read this before you deliver your next Excel project to your customer

All Protection Options Available for You

Before we explore the business motivation and possible strategies for protecting your work, let’s recap all of the protection options we have in Excel:

• Password-protect your Workbook file. This option will require a password you set, when the Workbook is opened. The Workbook will not even open without the matching password.
• Workbook Protection. This allows the protection of the Workbook structure, such as hiding, renaming, moving or deleting Worksheets. This protection does not affect the Worksheet cells (data / format). A password can be set when setting the Workbook protection.
• Worksheet Protection. This protects the Worksheet contents you chose to protect. Typically, this is used to prevent over-writing the Worksheet cells (excluding specific cells you explicitly allow to be changed). Think about inadvertently destroying your formulas in cells. You can protect other elements, such as adding/deleting columns and rows, formatting cells and even hiding the formulas from the eye. A password can be set when setting the Worksheet protection.
• Protect VBA Code. This option prevents others from viewing the VBA code you added to your Workbook. A password can be set when setting the VBA Code protection.

Different Needs – Different Strategies

Let’s explore different protection strategies from the business need point of view. Contrary to what you might expect, I’ll start off with the most demanding situation and work my way down the stream.

You are selling an Excel Product

If you package a tight Excel product and sell it as a standard package to any paying customer, you would probably want to:

1. Protect your IP: users should not be able to see your VBA code at all.
2. Protect your IP.2: prevent users from tempering, adversely, with your Workbook, trying to re-engineer and crack its protection bolts.
3. Protect your IP.3: prevent the copying of your product to other users or to multiple uses beyond what your licenses agreement allows.
4. Protect your product and reputation: users should not be able to manipulate the solution beyond what you designed it for. They should not leverage the (usually flexible) built-in Excel capabilities, such as adding rows, copying cells, moving Worksheets, adding calculations, changing the format of cells, etc.

I presume you get the importance of protecting your IP intuitively. I do want to emphasize, though, the last point: protecting your product. It may seem to be an aesthetic, or convenience, or ease of use issue. It is much more than that. This is about your product robustness. This is about your reputation.

Typically, software products, especially built with Excel, are highly complex under the hood. You have dependencies between multiple players: VBA algorithms, range names, columns’ width, cells’ formatting, etc. Breaking your product’s integrity will leave the user with bad experience and your reputation will be stained. Yes, this will happen subconsciously, even if he knows it broke after he crossed the line. You want to keep the integrity of your product intact!

Excel Product Protection Strategy

In order to cover the above interest, I’d recommend the following possible methods:

• Protect your VBA code with a strong steel safe. For that, you will need a 3^rd party protection tool. The Excel built-in password protection is very weak and can be broken with little effort. The challenge here is to find a 3^rd party solution that is top-secured, while not compromising your application’s functionality. To date, I tested a few of them, most rendered my applications unusable. I do stretch the limits, so these solutions may still work for you. The only solution I found to be excellent (unbreakable as far as I could verify) without breaking my code, is the Unviewable+ solution, found here.
• Password-protect your Workbook and Worksheets. Block any action unless it is part of your planned user experience and usability scheme. Most users will not bother to break the password anyway, and the main purpose is not to protect your IP, but rather secure the user experience, the integrity of the product and your reputation. In some cases, I combine this protection approach with other usability and experience features, such as hiding the Excel Ribbon all together, expanding the application to full screen, color-coding the available cells for update from the rest, and the like.
• Consider silently logging unwanted activity. The idea here is to send HTTP requests to a web-service you set up, upon unwanted events raised in your application. Example of such events could be: unhiding a hidden Worksheet, Unprotecting the Settings Worksheet (where your password resides hidden), changing fundamental attributes of the customer, such as his company name or address, etc. The posted record will include a code of the event identified, customer details, possibly the host machine name / IP address and timestamp. Do be aware that you may need to update your privacy policy and let your customers know that security measures are implemented to prevent breaching the license agreement terms. To-date, I only employed this measure in one product. I’m not a big fan of it, but if you like control – you’d probably like the next point.
• Consider killing your application upon a serious breach. This is a stronger version of the previous measure. Instead (or in addition) of sending you a log record on a breach attempt, you may automatically shut down Excel, or even delete your application file unless the customer calls you. Personally, I never applied such measures, but in some circumstances, you may find it suitable for you.

I do want to say that not all products deserve such a strict protection approach. My Excel Date Picker, for example, is not protected at all – it is delivered with the VBA code open for adaptation and learning.

You are Delivering an Excel Project

The main difference between a product and a project, as far as our protection discussion is concerned, is that a product reaches many users you may not even know, while a project is delivered to a specific customer with which you have on-going relationships. (I elaborated on other differences between a project ad a product in my last week’s Blog post here).

Generally speaking, the protection bells are very moderate with a project. I rarely protect my VBA code, for example, even though it includes a library of hundreds of VBA functions I devised over the years that are a highly valuable IP of mine (by the way, I do offer a subset of this function library for free here).

If I do want to protect my VBA code, I clarify in the agreement that in the case that I cannot guarantee my support and maintenance services to the customer, I will release the code for him. This way I’m protected for as long as I care to maintain my business software career, and the customer get’s a fair degree of freedom and security.

On the other hand, I never compromise on user experience and solution integrity. In that regard, password-protection of the Workbook and all Worksheets are a must. The experience and integrity of the application are tightly controlled.

Needless to say, I do not log any activities on the application. The only standard HTTP communication to my external web-services and repositories outside of application-specific Internet communication needs, is the query to the customer name and product name to be displayed in the standard Splash screen, as in this example:

No data is sent outside of the customer environment to me.

When a Project is a Product

Occasionally, I’m asked to design and develop a product for another entrepreneur. Such was the case with the Excel Rose Diagram. On one hand, it is a project for a specific customer. On the other hand, it will be a product that will reach many customers I have no control of.

The setting is even more challenging, as my customer requires access to my VBA code, not to be locked-in by me.

This situation is delicate and not guaranteed to always be resolved.

These days, I’m working on such a project for an Australian customer. The product is a pre-sales opportunity tracking workbench towards a streamlined closure by the end of the quarter.

In this case, the customer foresees the Excel solution as a first phase (sort of a prototype) before developing a cloud-based solution, therefore he was OK with me protecting the VBA code from view. Not always that’s the case, and I need to decide what am I willing to compromise, and what not.

You are Sharing an Excel Workbook with Colleagues

In this situation, you may be working for a company (on a paid salary) and share Excel Workbooks with other colleagues. These Workbooks may sometimes contain sensitive data you don’t want others to see.

There are already good measures of protection, by the confidentiality agreement all employees are bound to. Still, it is usually corporate policy to have every employee be exposed to what he needs to see for his line of responsibility, and not more.

A fair measure here would be to Password-protect the Excel file for opening. Sometimes that will be just enough.