How to save VBA code to DLL library?

Question

For security purpose, I want to save my macros from VBA in Excel workbook to DLL library file I'd be calling from the file later.

This way it is possible to deploy any version of my macros to company's PCs but noone would ever see the real code behind it.

How is this possible? I've read on converting to C++ and creating DLL. Is there any step-by-step solution for this?

Answer 1

You can protect your VBA, code by converting it to a password-protected XLA and obfuscating the code. but the password security is weak. Its easy to make the XLA work with all Excel versions.

An easy alternative is to convert to a compiled VB6 Automation addin. Security is good, its easy to convert, it will handle both UDFs and Command addins. But it won't work with Excel 2010-64 or early versions of Excel like XL 2000. You will need a VB6 compiler, which can be hard to find.

A harder alternative is to convert to .NET. If you use one of the many obfuscators then security is reasonable. Performance will be poor unless you use one of the tools that allow .NET access via the XLL interface, such as Excel DNA or Addin Express. 64-bit capability is also available.

If performance and security is critical you would need to convert to a C++ XLL, but this is hard work when coming from VBA.

Answer 2

a) You can save your macro in a separate file and protect it with password but, exist program for "recover password" that can break such protection.

b) While is possible to do a c++ dll, is smart to do instead using visual basic 6 because the syntax is pretty similar to vba. In vb6 you can create a COM component, register it and later add to you excel sheet.

Answer 3

First off, let me warn you against this in general. We did this once with a corporate app and the time it took to port was approximately the same as the time it took to write the app originally. A step-by-step guide won't exist for this because you're basically rewriting the app from scratch.

Secondly, not everything your Excel VBA can do is necessarily available in COM, or XLL. The events available are different; in the case of XLL there are entirely new structures to use for all of the data types. In COM there are no UDFs; in XLL there are no macro actions. You will no longer be able to attach code to worksheets (only Excel sessions) and the install procedures are much more complex. I would spent a long time researching what your VBA does exactly before embarking down one particular path.

It depends how large your app is, but I would encourage you to invest the money in getting watertight licenses drawn up and doing some basic code obfuscation (as per the original comments) rather than starting from scratch with a different architecture.

Answer 4

You did not mention your target Excel version, but I will give a general view.

1. Make sure your code is appropriate for an AddIn. E.g: instead of ThisWorkBook, use ActiveWorkbook.
2. Also decide if you need to create a menu or use shortcut keys, and modify your code accordingly, and protect it.
3. In VBE, change to IsAddin property to True.
4. "Save As" your file and select Excel add-in. Alternatively, just save it and rename the extension from .xls to .xla
5. Now either inform your users to "open" that .xla, or reference it in your projects, or put it in the autostart folder or....
6. You're done

http://www.ozgrid.com/VBA/excel-add-in-create.htm
http://excel.tips.net/Pages/T002927_Creating_AddIns.html
http://msdn.microsoft.com/en-us/library/aa140936(v=office.10).aspx

Answer 5

Converting VBA code to C++ is a pretty arduous process, since C++ and VBA are very different languages, but such a conversion with compilation to a DLL file really does provide the best VBA code protection out there.

If you want a tool that does exactly that, I would recommend the VBA Compiler for Excel. It converts your VBA code to C and compiles that C code into a DLL file.

It's easier and faster than converting your VBA code to C by hand. VBA Compiler website has a simple 10-step guide on how to compile VBA code into a Windows DLL file https://vbacompiler.com/how-to-compile/ and it doesn’t even require any knowledge of C or C++.