Secure Excel Workbook (with VBA) from reuse with different data

Question

I have created an Excel Workbook with a lot of VBA code for a customer. The customer will provide me with data. I will import that data into the VBA laden template, Save it as an xlsm, and deliver it to the customer. I get paid by the Workbook so I need to prevent them from trying to copy new data into the existing workbook and reusing it.

How can I somehow prevent the customer from reusing a workbook by just entering in new data on the main Worksheet, then saving as a new Workbook, and getting the use of the VBA code for free. (Alternately they could copy the file in windows then enter new data on the copied version.) I need to detect a significant change in data from the initial imported data.

The data on the main sheet is fairly static (perhaps even totally static on many known columns). I'm thinking about randomly sampling some of the cell data on import (perhaps 10 random cells, or number of rows, etc.), and storing that data somewhere. If, say, 50% of the cells change data, I could just disable (or short-circuit) the public entry points in the code...or something else?

I'd like to allow for some flexibility on the part of the customer, but prevent abuse.

• Is there a better way than my general idea, above?
• Where could I store that data (it should be part of the sheet, but not changeable by the customer). Perhaps a hidden sheet with password locked cells?
• Is there some accepted way of doing this that I'm unaware of?

Answer 1

Perhaps Time-expire the functionality in your code

So thank you for this question. Thank you for setting a bounty. It is a very interesting question given your desire to monetise the VBA code, as a VBA programmer I generally resign myself to not being able to monetise VBA code. It is good that you insist and I will contribute an attempt at an answer.

Firstly, let me join the chorus of answers that say VBA is easily hacked. The password protection can be broken. I would join the chorus of respondents who say you should pick a compiled language as the vessel for your code. Why not try C# or VB.NET housed in a Visual Studio Tools For Office (VSTO) .NET assembly?

VSTO will associate a compiled assembly with a workbook. This mechanism is worth knowing because if you insist on VBA we can use the same mechanism (see later). Each workbook has a CustomDocumentProperties collection where one can set custom properties (it says Document not Spreadsheet because the same can be found in Word so Document is the generalised case).

Excel will look at the workbook's CustomDocumentProperties collection and search for "_AssemblyName" and "_AssemblyLocation"; if _AssemblyName is an asterisk then it knows it needs to load a .NET/VSTO assembly, the _AssemblyLocation provides a lookup to the file to load (you'll have to dig in on this, I forget the details). Reference

Anyway, I'm reminded of VSTO CustomDocumentProperties mechanism because if you insist on using VBA then I suggest storing a value in the CustomDocumentProperties collection that helps you time expire the functionality of your code. Note do not use the BuiltInDocumentProperties("Creation Date") because it is easily identifiable; instead use a codeword, say "BlackHawk". Here is some sample code.

Sub WriteProperty()

    ThisWorkbook.BuiltinDocumentProperties("Creation Date") = CDate("13/10/2016 19:15:22")


    If IsEmpty(CustomDocumentPropertiesItemOERN(ThisWorkbook, "BlackHawk")) Then
        Call ThisWorkbook.CustomDocumentProperties.Add("BlackHawk", False, MsoDocProperties.msoPropertyTypeDate, Now())
    End If


End Sub

Function CustomDocumentPropertiesItemOERN(ByVal wb As Excel.Workbook, ByVal vKey As Variant)
    On Error Resume Next
    CustomDocumentPropertiesItemOERN = wb.CustomDocumentProperties.Item(vKey)
End Function


Sub ReadProperty()

    Debug.Print "ThisWorkbook.BuiltinDocumentProperties(""Creation Date""):=" & ThisWorkbook.BuiltinDocumentProperties("Creation Date")
    Debug.Print "CustomDocumentPropertiesItemOERN(ThisWorkbook, ""BlackHawk""):=" & CustomDocumentPropertiesItemOERN(ThisWorkbook, "BlackHawk")

End Sub

So you could set CustomDocumentProperty "BlackHawk" to the workbook's initial creation time and then allow the client to use the code for 24 hours, or even 48 hours (careful with weekends, create Friday work through to Tuesday) and then afterwards the code can refuse to operate and instead throw a message saying pay LimaNightHawk more money!

P.S. Good luck with your revenue model.

P.P.S. I read your profile, thanks for your military service.

Answer 2

Compile Excel file to EXE. Google for that.

Answer 3

This might be too simple of an answer, but sometimes we fail to think of the simplest solutions:

Why don't you simply provide the customer with only a copy of the output? You could run their data through your macro-enabled workbook, copy the output sheet into a new workbook, and then break all links so that there's no formulas, updating, or ties to your workbook.

Answer 4

I would create another workbook that contains the code and then reference the customers wb from that one.

Answer 5

Whatever you do it will be feasible to crack it (VBA code is easy to crack). However:

• there is the contract so... that's not legal for them to do it
• you can put part of the code on a FTP server and control physically what is being executed

Very nices ideas here though

Answer 6

Here's what I've done. Perhaps there is a entirely better approach, or there are tweeks to the below that will make it better:

1. From the VBA Tools menu > VBAProject Properties > Protection (tab), I Locked the project for viewing with a password.

2. I created a new "License" sheet. This sheet is hidden from the user. If you hide the sheet via code like this:

Me.Visible = xlSheetVeryHidden

then then sheet cannot be un-hidden by the user (it requires running vba code to unhide).

3. On initial import I sample:
   • Number of imported rows
   • x number of randomly selected cells *from the columns I know won't/shouldn't change. (There are columns they are allowed to change freely.)

I store these on the "License" sheet in Address / Value pairs.

4. When the workbook is opened, the Workbook_Open event fires and then does a quick comparison of the current number of rows, and the current values for the addresses stored on the "License" sheet. I then do a percentage calculation: If the rows are more than x% different, or the number of changed values is more that y% then I

Sheets(1).Protect LOCKOUT_PASSWORD
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

There is also a method for unlocking the sheets if necessary.

Answer 7

Concern 1 seems to be basic re-use of the file. You could create a sub in the ThisWorkbook module to destroy code located in other modules in the event that save-as is selected.

Concern 2 seems to be someone hacking your password protection. A similar tactic could be employed such as using "opening the developer window" as your event instead of save as.

I have experimented with save events to log user entries with great success using the ThisWorkbook module. I am not certain how/if one could detect if the developer tab is opened.