VbaCompiler

How to protect Excel workbook template

 

The DoneEx VBA compiler for Excel allows you to create copy-protected Excel workbooks and add-ins, as well as protect Excel template in .xltm format.

 

What is a Workbook Template (.xltm)?

An Excel workbook template is a pre-designed file that provides a ready-made framework for creating new spreadsheets. It includes preset formatting, formulas, VBA code and often sample data, enabling you to concentrate on inputting your own information without having to design the layout or structure from scratch. You cannot save changes into workbook template but instead you can save it into the new workbook with all data, formatting and algorithms of the Excel template.

 

Why You Need to Protect Your Excel Templates

If you’re distributing templates to customers or colleagues, protecting them is crucial. Without protection, your VBA code and content can be easily accessed, edited, duplicated or used without your permission. Using DoneEx VbaCompiler for Excel ensures your workbook template:

  • Is compiled into binary VBA code, which is impossible to restore
  • Binds to a user’s computer hardware – aka ‘Hardware locking’
  • Requires activation with registration key for use
  • Prevents unauthorized usage

 

How Excel Template VBA Copy Protection Works

VbaCompiler for Excel provides VBA copy protection by compiling your VBA code into a binary format and embedding it in the workbook as a dynamic link library (DLL).

>It also uses hardware-based locking and registration key verification to ensure the workbook template and workbooks saved from it runs only on authorized devices.

 

How to create protected workbook template in brief

There are only 2 steps to create a copy protected workbook template:

  1. Compile workbook with VbaCompiler for Excel using specific option settings.
  2. Open compiled workbook and save it as Excel workbook template (.xltm file format).

 

Step-by-Step Guide to Protect Workbook Template

Step 1: Select mandatory compilation options as the following:

  • Single file result (Embedded DLL)
  • Target Excel bitness as ‘Both 32 and 64’. To ensure that when you protect your workbook template, it will work with both Excel 32-bit and Excel 64-bit.
  • Copy protection options as ‘Copy protection with regkey’ and ‘Hardware locking’.
  • Regkey alternative path. The place where registration key will be located on customer computer. The workbook template does not specify its current location on disk in the VBA code, so the ThisWorkbook.Path property returns an empty string. In order for a secure Excel template to find the registry key on client computers, an alternate path to the registry key must be present in the compilation settings.

Excel workbook template copy protection options settings

The compiler must have these options set so that the workbook template works correctly.

You may add some other compilation options that are available on the compilation form. However, The compiler must have all of the options listed in the ‘Step 2’ description set. This ensures that you get a functional copy protected workbook template.

When the compilation of your workbook finishes successfully, proceed to step 2.

Step 2:  

  • Open your compiled workbook in MS Excel.
    Because of the “Hardware Lock” feature, the workbook will ask for a registration key when opened. Therefore, you need to generate a registration key for your computer and place it in the workbook location before opening it.

    >This registration key is only required to complete saving of the workbook to an Excel workbook template and does not affect the behavior of the final template file.
  • Select File >> Save As >> Select saving format as “Excel Macro-Enabled Tempate (*.xltm)”

Protected Workbook Save-As Excel template

 

Click ‘Save’ button to save your workbook as Template

 

Compiled workbook is saved as protected Excel template

 

 

Workbook Template Usage on Customer side

After you deliver your workbook template to your customer, the customer will get the following request upon opening it:

 

Registration key request on a customer's computer the first start

Customer sends you his computer id by email or any other communication software (as messenger).

 With this computer id you will generate registration key which unlock the workbook template on customer computer only.

 With this computer id you will generate registration key which unlock the workbook template on customer computer only.

You need to deliver customer’s registration key by email or other communication program.

When customer get registration key he should copy it into location you have pointed in ‘Regkey alternative path’ option.

After registration key is copied, the customer can open your workbook template and modify it but cannot save changes into the workbook template. This is because the template format doesn’t allow for that.  So, the customer needs to save changes as a separate new workbook in format which may contain VBA code like *.xls, *.xlsm or *.xlsb.

Note: All security measures for the newly created workbook will be the same as for the compiled workbook template. It will be protected from copying with the same security level, conditions and the same registration key that you provided for the workbook template.

In case of a newly created workbook, the registration key may be located in the ‘Regkey alternative path’ disk location as well as in the same folder as where this workbook is located.