Lately I have been working hard on a completely new project – compiling VBA to VB.NET (VBA Compiler). If you’ve had your way around my blog a little bit you might notice I am little obsessed with VBA code performance and VBA multithreading :). Well… I always had a soft spot for .NET in my career as a developer – although nowadays it’s only a late night hobby when the kids are sleeping, and management consulting during the day. Anyways… I always thought it way a real shame there was no easy (at least not for an amateur) way to compile VBA code into VB.NET. Especially since both VB and VBA are almost identical in their syntax (no duh)!
Why you may ask? Well, VBA is a slow language due to the fact that its code is interpreted instead of being compiled and executed. No surprises there – Microsoft didn’t expect anyone to write complex algorithms and applications in a Workbook. But often many prototypes start in Excel and VBA! Or simply they start as small stopgaps and then grow bigger and more complex. Feel free to see for yourself just how slow VBA is compared to VBscript and .NET.
If you read my article where I compare various approaches to achieving multithreading in VBA you must already be aware of the vast performance opportunities in exporting your VBA code to .NET (usually a 10x effiency gain) even when multithreading is not involved. However, the task to export VBA code is not easily accessible to the average VBA developer. Hence the need for a tool that would allow you to effortlessly compile and export your VBA code to VB.NET (dll). This is the goal that drove me to creating the VBA Compiler Add-In. Enough with the intro – let’s jump right in!
The VBA Compiler Add-In
The Add-In (still in development) allows you to select any number of VBA Functions or Subs to be exported and compiled into a COM-visible VB.NET DLL (don’t worry about the details if you are not a techie).
Why Compile VBA to VB.NET?
Well, for a load of reasons – let me mention just a few top of mind below:
- Performance – in most scenarios .NET (there are exceptions) is much more efficient. Although the resulting DLL will still run sequentially you may as well be seeing 10-20x faster execution
- Code is more secure – out of the box not so much (google .NET decompilers), but with Obfuscation (many free available solutions out there) you can easily achieve much higher levels of code security that with any Excel Workbook
The VBA Compiler does not obfuscate your code. You need to obfuscate it using any of the available .NET obfuscators. I recommend ConfuserEx as it has a simple GUI
- Portability – migrating your Excel VBA solution to another platform (PHP, ASP.NET etc.)? Want to make the implementation as easy as possible? Well the VBA Compiler will create a COM visible DLL that can easily be used directly from PHP or from most other platforms
Exporting your VBA code to a COM visible DLL is preferable over using Excel Interop. Be aware that Excel Interop is not a server supported solution
Honestly – in some situations it’s better to rewrite VBA from scratch. There are still performance opportunities in multithreading, refactoring or using native .NET capabilities (Linq) not available by simply compiling a piece of VBA code.
VBA Compiler Installation
To install the VBA Compiler Add-In simply:
- Be sure to first install Microsoft .NET Framework 4.5 and Windows SDK
- Unzip the VBA Compiler.zip file
- Run Setup.exe
- Open Excel Options and select Trust Access to the VBA Project Object Model:
Compile VBA code to VB.NET
Currently both VBA Functions and Subs can be compiled to VB.NET. VBA has a similar syntax to VB.NET hence most VBA Functions should compile without issues to VB.NET DLLs. Follow the process below:
- Step 1: Select Functions and Subs – select any number of VBA Functions or Subs you wish to compile to a VB.NET DLL
- Step 2: Select Platform – this is a VERY important step! Make sure which version of Excel you are using x86 (32 bit) or x64 (64 bit) and select the correct value. This value defaults to your Windows platform (which does not necessarily need to be your Excel platform)
- Step 3: Select .NET version – select 4.5 to be safe for now. You can try using other versions
- Step 4: Extract DLL – compile the VBA Functions to a VB.NET DLL. Make sure to fix any compilation errors
Compile VBA Examples
Let us start with a simple Hello World! example to show how to use the AddIn. We want to export the following function to a VB.NET DLL:
Public Function MergeStrings(str1 As String, str2 As String) As String MergeStrings = str1 & str2 End Function
Simple right? If you have the AddIn installed simply proceed as follows:
- Go to the VBA Compiler Ribbon Tab
- Click Step 1: ... and select the MergeStrings function from the list
- Click Step 2: ... to select your Excel platform (32 or 64 bits)
- If you have .NET 4.5 installed proceed if not click Step 3: ... and select your designated .NET version
- Click Step 4: Extract DLL
Notice that now your VBA Project is extended with an additional Module:
Public Declare Function CreateRunClass Lib "FunctionsDLL_x86.dll" () As Object Public Declare Function FreeLibrary Lib "kernel32" (ByVal hLibModule As Long) As Long Public Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName$) As Long Function LoadDLL() As Long LoadDLL = LoadLibrary(ThisWorkbook.Path & "\" & "FunctionsDLL_x86.dll") End Function Sub FreeDLL(hModule As Long) Do Until FreeLibrary(hModule) = 0 Loop End Sub
What is really important is the CreateRunClass procedure which creates an object we can use to execute our exported function:
Sub Test() Dim testClass As Object Set testClass = CreateRunClass() Debug.Print testClass.MergeStrings("Hello ", "World!") End Sub
What do you get?
Fantastic! The whole exercise took me only a moment. Be sure to read through to learn more.
VBA Compiler best practice
The VBA Compiler Add-In produces a .NET DLL which is loaded dynamically via the LoadDLL procedure. The procedures needs to be run only once hence it is suggested to Load the DLL during the Workbook Open event. A good practice is to also unload the DLL – for this the compiler generates the FreeDLL procedure. It requires that you provide the module handle. Below a code snippet I recommend placing in your Workbook module covering the above needs:
Dim hModule As Long Private Sub Workbook_Open() 'Load the DLL into memory hModule = LoadDLL End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Unload the DLL from memory FreeDLL hModule End Sub
Download and License
Feel free to download the latest version below:
VBA Compiler32 bit version:
VBA Compiler 64 bit version:
This Excel Add-In is currently available under the: MIT License.
VBA Compiler Startup Kit
Struggling with VBA Compiler? Be sure to checkout my
VBA Compiler Startup Kit!
The VBA Compiler Startup Kit contains various code examples that will let you get the most out of your projects. How to use VB.NET multithreading to increase performance? How to share object and arrays with your DLL functions? All these questions are answered with examples you can compile using the AddIn.
FAQNeed better performance (e.g. multithreading)? Or simply prefer someone else do the work for you? Feel free to reach out to me for small VBA jobs.
This section will list the Frequently Asked Questions on the VBA Compiler Add-In.
- Why does the VBA Compiler throw errors for Excel object such as Range, Cells, Worksheets, Workbooks, Applications?
Range, Cells, Worksheets, Workbooks and the Excel.Application objects are all associated with Excel specifically and often your current running Excel process. The VBA Compiler on the other hand aims to compile VBA code that is compatible with VB.NET. The above mentioned objects are associated purely with Excel and are not available in VB.NET.
Potentially VB.NET does support the Excel Interop COM library – however this library requires Excel to be installed and acts only as an intermediary by creating a dedicated Excel process which is both inefficient, error-prone and not supported for Server-side solutions.
The right way to go around this issue is to separate VBA code that can be compiled by the VBA Compiler with VBA code that references Excel objects e.g. Ranges can be passed as VBA Arrays to compiled Subs/Functions.
Another potential way of going around this problem, if you intend to run your DLL directly from Excel, is to acquire access to the current running Excel process using the GetObject function.
- Does the VBA Compiler run on Excel 64bit?
Yes. Simply download and install the 64bit version for Excel 64bit