London

81 Chancery Lane
London
WC2A 1DD
01235 821 160
View map

Edinburgh

1 Lochrin Square
92 Fountainbridge
Edinburgh
EH3 9QA
01235 821 160
View map

Dublin Office

24A Baggot Street, Upper
Dublin 4
Ireland
D 04 N5 28
01235 821 160
View map

Administrative Office

BH Office
Church Street
Ardington
Wantage
Oxfordshire
OX12 8QA
01235 821 160
View map

Send us a message

CLOSE X
Contact Us
12.11.2020

Password Encryption in VBA

Programming languages such as R and Python may be making waves in the realm of insurance, but ask any actuary, and they’ll tell you that Excel still reigns supreme. This makes VBA (widely reviled in programmer communities and no longer updated by Microsoft) wildly useful – but knowledge of it is quite rare.

This strong reliance on spreadsheet software, as well as the sensitive data and processes often adapted into Excel, makes it reasonable to assume strong password protection is commonplace. However, practices in offices vary. In this article I will lay out some ideas on how to implement some standard Excel protection rules, and will propose a specific VBA solution to this problem.

Here are some types of generic protection often used in spreadsheets:

Generally, all these protections have one thing in common: a password.

Usually, this comes in the form of a shared password that all members of a team know. These passwords stay in circulation for a few months, and eventually everyone who uses a spreadsheet knows how to unlock it. This isn’t just the developers; it could be testers, users, managers…

The Problem

Is this a problem? On the surface, it isn’t so bad. For one thing, Excel’s protection is easily breakable by anyone with an internet connection and a spare lunch break. Equally, protection should be designed to meet its need. There are two reasons concern might be minimal:

As such, the real reason we use protection in spreadsheets tends to be restriction of access rather than to protect against crime. Consider the following examples, drawn from personal client experience:

Both of the above examples are fairly trivial to fix, but identifying the breach after it has happened leads to potential issues with customers. Obviously, some basic version control processes and a stricter handling of passwords would have prevented both happening. However, theory and practice aren’t always best friends, and invariably little concessions happen.

To address this issue, we are proposing a finer handling of spreadsheet access. In particular, we will borrow the ideas of account creation and encryption from any online portal and adapt it to Excel via some VBA code. The result should be:

Before we get started though we will need to introduce an element of cryptography!

Encryption – Hashing

Inheriting CodeWhat happens when you create an account online? You’re asked to provide a username (which could be an e-mail address) and a password. This password has to meet certain security requirements, mainly to ensure potential hackers cannot simply guess yours by knowing a little bit about you. Then, a few hours later, you want to log into the app on your tablet device. Almost unbelievably, you’ve already forgotten your password. Luckily, there’s a handy “forgotten password?” button on the same page, taunting you even as it offers its assistance.

A password reset e-mail is sent to you, which you then click on. You provide no information; you just need to click on the link in the e-mail and then you get to decide a new password. You swear at the screen. Why can’t they just tell you your current password? Now you have to invent a whole new one, which you do begrudgingly… only to discover your new password was actually your old one!

The process described above might be so vivid in its description because the author of this article has gone through it several times this month.

There are two reasons the password reset e-mail cannot provide a current password:

To see how this works as a process:

To illustrate the power of hashing, we have used SHA-256 encryption on the password above:

Before: Password 1

After: 19513FDC9DA4FB72A4A05EB66917548D3C90FF94D5419E1F2363EEA89DFEE1DD

Much more secure than the initial password!

What makes this better is that it’s impossible to reverse engineer a password algorithmically, if it has been hashed by a good algorithm. Therefore, there is only one place where the series of keys required to access the account is stored: in the account holder’s head!

Salt Values

As an additional form of security, consider the scenario where two users share the same password. In theory, it is possible for user A to guess the password for the account of user B, or accidentally access it, if the two have similar usernames.

To prevent this from causing trouble, a salt value is used. A salt value is essentially a random string, which is prepended to the original password. This entire string is then fed through the hashing algorithm to lead to two different results. To see the effect of even a simple salt, see the table below:

Password

Salt  

Pre-Hash

Hash

Password1

A

APassword1

6098F531662ED964162675212061C8821D4EB3EAC603DC27DDDA72B6B2B5534

Password1

B

BPassword1

C23E4DA4E2CC2DD195627F1DDF8BC6E723347D4E6F8417CE78649A23645C

Notice that even a single character as salt is enough to create wildly different hash values. Salt values are usually unique to the username, not the password, meaning each user will have a randomly generated salt value (that even they don’t know), which then goes into their hashing algorithm process.

Generally, salt values will be longer than the above.

How does this work with spreadsheets?

The idea of using a bespoke hashing algorithm and appending a salt value to a username may seem something like overkill for a spreadsheet. However;

Meeting these desires will require a fairly robust piece of code that is:

The Solution

To account for the versatility requirement, we would propose an Excel add-in which is accessed by some Workbook_Open code in the relevant spreadsheet. Using an Excel add-in means a single piece of written code is sufficient for all spreadsheets using it. This also allows for quick updating when changes are needed.

In order to simulate the experience of “logging in”, a VBA userform should be created with a Username / Password combination required from the user. The userform could autofill a username based on the current user running the code.

When a password is entered, the code goes into a database (this could be an SQL server database, a Microsoft Access database, or simply within a workbook somewhere) and looks up the username. It pulls back the salt value (which, recall, is unique to the user). This is then prepended to the password entered and run through the hashing algorithm of choice.

The resulting string of characters is validated against the password stored against the username. One of two things can occur:

  1. The password will be valid. The code will pull through an access level (e.g 1 = Developer, 2 = Tester…). This will determine further code to run to “prepare” the spreadsheet for the user.
  2. The password will be invalid. The code will spit out a generic invalid password message and the user is invited to try again. Crucially, a failed log-in means no access to the spreadsheet.

With some fairly standard tweaks and preparation, this piece of code can be attached to any spreadsheet (which should be structured with some consistency to allow the access level rules to be managed by the add-in code), and the user validated fully for access.

An Example

We have developed a very simple model which performs the required steps. It is meant to be illustrative of encryption and the process of validation, so a number of changes to the solution outlined above have been made to accommodate this:

  1. Instead of a bespoke database (which may be inaccessible to most users), the username / salt / password / access_level combination is stored within the spreadsheet itself, for easy perusal.
  2. Instead of an add-in, the spreadsheet has been developed as a macro-enabled workbook, purely to help illustrate the effect of different access levels.
  3. Instead of only a log-in functionality, a Register functionality has been added so anyone in possession of this workbook can themselves add their details and see the effect of different access levels. To be clear, this solution implemented in a real-world context would NOT offer registration; the decision was made to do so here to illustrate the effect.
  4. A practical implementation of this theory would probably involve some additional work on the part of the user – particularly in setting up a series of rules on which worksheets to hide / show depending on access level.

Below is a short walkthrough of the development of this model.

First, we need to look at the user interface. A specially-made VBA userform is needed to allow the user to select whether this is an attempt to log in or create a new account. This has been designed in the standard fashion, with three userforms. One will be the selection screen:

One will be the registration screen; in practice, someone with admin privileges would perform this step for every user:

The final one will be the sign-in screen:

Now, the process of creating a new user or validating an existing one sounds like it belongs in a nice class module wrapper; in particular, to make the code easily portable and intuitive when used. We go about writing the code for that; the bones of the class module are examinable in the file, but we expose the following to the rest of the workbook:

The code will work like this:

CreateUser

This requires a username, password and role. All three properties will be set, and then CreateUser will:

ValidateUser

This requires only a username / password combination. It will look up the salt, perform the hashing and check it against the HashedPassword column above. The access level will be retrieved, and this will determine what the user sees.

Each type of user role will have a different treatment!

Final Thoughts

With some very simple code and a couple of hours, it is possible to design strong protection to validate Excel users against accidental changes / errors. The example model above is designed to be played around with by any interested readers, so it required the simplifying assumptions made above to make it viable as a single, simple workbook. Additionally, a more professional product would:

The full example model is available from APR. Please contact John.Nicholls@aprllp.com for a copy of the code and further commentary on its adaptability to client offices.

Hopefully this article provides good ground for an initial understanding of password encryption, and some information over how it can be done in Excel, ubiquitous as it is in the industry. For those interested in the concept but who wish to see a product developed not for illustration but for use, please get in touch to discuss.

John Nicholls

November 2020