Secure Excel Workbook (with VBA) for reuse with various data

Question

I created an Excel workbook with lots of VBA code for the client. The client will provide me with the data. I will import this data into the loaded VBA template, save it as xlsm and deliver it to the client. I get paid for a Workbook, so I don’t have to try to copy new data into an existing workbook and reuse it.

How can I somehow prevent the client from reusing the workbook by simply entering new data on the main worksheet, and then saving it as a new workbook and getting the free VBA code. (Alternatively, they can copy the file into the windows, and then enter the new data into the copied version.) I need to detect a significant change in the data from the original imported data.

The data on the main sheet is quite static (perhaps even completely static for many well-known columns). I am thinking about random selective analysis of some cell data during import (maybe 10 random cells or the number of rows, etc.), and somewhere this data is stored. If, say, 50% of the cells change data, can I just turn off (or short-circuit) the public entry points in the code ... or something else?

I would like to provide some flexibility on the client side, but prevent abuse.

• Is there a better way than my general idea above?
• Where can I store this data (it should be part of the sheet, but not changed by the client). Perhaps a hidden sheet with password-locked cells?
• Is there any acceptable way to do this that I don't know about?

Answer 1

Maybe time is running out in your code

So thanks for this question. Thank you for choosing generosity. This is a very interesting question, given your desire to monetize VBA code as a VBA programmer, I generally accept that I cannot monetize VBA code. It’s good that you insist, and I will try to answer.

First, let me join a chorus of answers that say VBA is easy to crack. Password protection may be compromised. I would join the chorus of respondents who say that you should choose a compiled language as a ship for your code. Why not try C # or VB.NET hosted in the Visual Studio Tools for Office (VSTO) .NET assembly?

VSTO will associate the compiled assembly with the book. This mechanism is worth knowing, because if you insist on VBA, we can use the same mechanism (see below). Each book has a CustomDocumentProperties collection in which you can set custom properties (this means that the document is not a table, since the same can be found in Word, so the document is generalized).

Excel will look at the CustomDocumentProperties collection of the workbook and look for "_AssemblyName" and "_AssemblyLocation"; if _AssemblyName is an asterisk, then he knows that he needs to load the .NET / VSTO assembly, _AssemblyLocation provides a search for the downloaded file (you will have to delve into it, I forgot the details). Link

Anyway, I was reminded of the VSTO CustomDocumentProperties mechanism, because if you insist on using VBA, I suggest storing the value in the CustomDocumentProperties collection, which will help you expire the functionality of your code. Note. Do not use BuiltInDocumentProperties ("Creation Date") as it is easily identifiable; use a codeword instead, such as "BlackHawk". Here is a 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 

Thus, you can set the CustomDocumentProperty "BlackHawk" at the initial time the book was created, and then allow the client to use the code for 24 hours or even 48 hours (be careful with weekends, create Friday work until Tuesday), and then the code may refuse to work and instead, leave a message saying that paying LimaNightHawk more money!

PS Good luck with your income model.

PPS I read your profile, thanks for your military service.

Answer 2

Whatever you do, it will be possible to crack it (VBA code is easy to crack). But:

• have a contract, so ... what is not legal for them to do this
• you can put part of the code on an FTP server and physically control what is running

There are a lot of ideas, but

Answer 3

Compile the excel file into exe. Google for this.

Answer 4

Concern 1 seems to be the main reuse of the file. You can create a sub in the ThisWorkbook module to destroy the code located in the other modules in case saving is selected.

Concern 2, it seems someone will crack your password protection. A similar tactic can be used, for example, to use the "open developer window" as your event instead of saving as.

I experimented with save events to log user records with great success using ThisWorkbook. I'm not sure how / if I could detect if the developer tab is open.

Answer 5

Here is what I did. Perhaps there is a completely better approach, or there are tweeks to the bottom that will make it better:

• In the menu VBA Tools> VBAProject Properties> Protection (tab), I blocked the project for viewing with a password.

• I created a new "License" sheet. This sheet is hidden from the user. If you hide the sheet through code as follows:

Me.Visible = xlSheetVeryHidden

then the sheet cannot be hidden by the user (to display it, you must run the vba code).

3. On initial import, I have an example:
   • Number of rows imported
   • x the number of randomly selected cells * from the columns that I know will not / should not change. (There are columns that are allowed to change freely.)

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

4. When the workbook is open, the Workbook_Open event occurs, and then a quick comparison of the current number of lines and the current address values ​​stored on the License sheet is performed. Then I do a percentage calculation: if the rows are greater than x%, or the number of changed values ​​is greater than y%, then I

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

There is also a way to unlock the sheets if necessary.

Answer 6

This may be a too simple answer, but sometimes we cannot think of the simplest solutions:

Why don't you just provide the client with only a copy of the result? You can run your data through your macro-enabled workbook, copy the output sheet to a new workbook, and then break all the links so that your book does not have any formulas, updates or links.

Answer 7

I would create another book containing code, and then refer to the wb clients from this.