11

I've put together a simple Excel database that performs a few macro functions and I need to distribute this database to a few people - but they cannot see how the macro function actually works (stupid rules I have to follow!). What is the best way to achieve this?

I've done a bit of research and I found two ways:

  • Password protect the VBA project; but this is apparently very easy to break using readily available tools online (it would be in the best interest to the people I'm sending this to find out how the macros and functions work; so I'm almost 100% sure they will try to get into it.. hence a password protection seems inadequate.

  • Move to a fully compiled language like C++; my skills are very limited to VBA on Excel and Access so this being the ideal solution; isn't a solution for me :(

Are there any other ways? I thought of having a 'master excel document' with all the macros in that and then send 'children' databases to the end users and have the 'children' databases connect to the 'master' - is something like this possible? By hosting the master online or even sending the end users the master but making it completely inaccessible unless accessed by the 'children' databases?

Chronix3
  • 601
  • 2
  • 9
  • 21
  • 3
    Hire a lawyer to craft and provide guidance on enforcement of an EULA. A smart enough person can open *any* box they are given - just takes time and effort. (Obfuscators and the like just increase time/effort with the intent that it will grossly exceed the benefit ..) – user2246674 May 26 '13 at 07:39
  • Check on VSTO to create Add-Ins and then use ClickOnce to deploy them – Siddharth Rout May 26 '13 at 18:00
  • 1
    Check out http://hivelink.io, it allows you to create a user interface spreadsheet without calculations, and easily define the inputs/outputs to connect them with your database with authentication etc. I wrote an extensive post here: http://stackoverflow.com/questions/16363621/protecting-code-in-an-excel-workbook/28968867#28968867 – stuzor Mar 10 '15 at 17:23

3 Answers3

9

You can create Automation Add In.

An Automation Add In provides several advantages

  • Execution Speed : An Automation Add In written in VB6 is compiled to native machine code which runs much faster than the interpreted VBA languange.

  • Security : Unlike an XLA add in, you never distribute the source code to the end users. If your code has proprietary information or intellectual property value, that remains safely protected on your own computer. It is never distributed to the user. Your code can never be compromised.

http://www.cpearson.com/excel/automationaddins.aspx

Community
  • 1
  • 1
Santosh
  • 12,175
  • 4
  • 41
  • 72
  • 7
    vb6 is obsolete. I would recommend VSTO instead :) – Siddharth Rout May 26 '13 at 17:59
  • What about compatibility issues? I'm going through the documentation for VSTO now but if you had a quick answer that would be awesome. - Does VSTO addins support older versions of Office? - Does it support the MAC version of Office/Open Office? – Chronix3 May 27 '13 at 01:35
  • 1
    @Chronix3 I suggested Automation Add in In bcoz the syntax is similar to VBA and can be done faster. Give it a try. Though VB6 is obsolete but it still works. – Santosh May 27 '13 at 03:33
  • 2
    VB6 libraries don't work for anyone using 64-bit Excel, but otherwise they can be good and you get to use VB the way you are familiar with in Excel. VSTO is good but annoying because the users need admin access on their machine to install your addin. I wrote a more extensive post here: http://stackoverflow.com/questions/16363621/protecting-code-in-an-excel-workbook/28968867#28968867 – stuzor Mar 10 '15 at 17:27
2

There aren’t too many ways to protect your Excel VBA code reliably.

You can try to use passwords or VBA obfuscators, but all of that protection is limited. Passwords are easy to break, and obfuscated VBA code can still be traced back and recovered.

The other answer to this question mentions using VB6, but that limits your code to 32bit Excel. Also VB6 can be decompiled by VB decompiler.

Converting your code to a compiled language is indeed the best way to protect your VBA code, but be warned that in this case not all code is created equal.

Converting your code to VBA.NET is a flawed solution because the compiled code of .NET assembly can be converted back into the original source code.

Converting your VBA code to C or C++ is the most effective form of protection, but this takes a lot of experience and effort since C/C++ and VBA are very different languages.

I would suggest you have a look at a tool called VBA Compiler for Excel (https://vbacompiler.com/how-to-compile/). It compiles your Excel VBA code into a DLL file with a click. You do not need any knowledge of C or C++ languages to use it.

NikoDeem
  • 51
  • 3
0

If you're still looking for a good way to protect your Excel VBA code from copying or tampering, then you should consider the Excel VBA compiler of XLS Padlock.

Because XLS Padlock offers an integrated VBA compiler that can compile your VBA code into binary code, making it completely secure and inaccessible to others. it's really binary code and not simple obfuscation.

You open the VBA compiler of XLS Padlock, move some vital parts of your existing VBA code into it (subs or functions) and it compiles live. You can then invoke this compiled code directly from your normal VBA project using dedicated functions. It's like having several modules, except that some modules are compiled into bytecode and thus secure. No need to learn a new language C or C++ or VB, nor COM add-ins.

Plus, XLS Padlock can wrap your Excel workbook into a secure shell application, which you can then distribute and even sell to your customers. It does not create DLL files that must then be distributed among your workbook files (imagine if your other non-tech users lose the DLL or don't know what it is), the software compiles spreadsheets + compiled code into a single EXE file. It is even possible to add licensing features, online activation if you want to sell your workbooks or control who uses them, while keepking your VBA code secret.

GuiDesign
  • 56
  • 3
  • Unfortunately XLS padlock does not appear to work with office 365 excel, unless you download a standalone excel module from excel 365 to your hard drive, and managing both paths and not causing conflict interrupts is not easy, and it is not easy to download generally from the cloud server properly, the instructions are not comprehensive on the office website. – Con Fluentsy May 06 '23 at 23:54