Tag Archives: compiler

VBA Compiler

VBA Compiler Add-In (to VB.NET)

1 Star2 Stars3 Stars4 Stars5 Stars (11 votes, average: 4.64 out of 5)
Loading...

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).
Compiling VBA to VB.NET DLL

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:

  1. Be sure to first install Microsoft .NET Framework 4.5 and Windows SDK
  2. Unzip the VBA Compiler.zip file
  3. Run Setup.exe
  4. Open Excel Options and select Trust Access to the VBA Project Object Model:
    Trust Access to the VBA Project Object Model
    Trust Access to the VBA Project Object Model

The Add-In should now be available in Excel as a new Ribbon Tab group called VBA Compiler
vba compiler ribbon
The Add-In looks as follows:

VBA Compiler Add-In for Excel
VBA Compiler Add-In for Excel

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:

  1. Step 1: Select Functions and Subs – select any number of VBA Functions or Subs you wish to compile to a VB.NET DLL
  2. 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)
  3. Step 3: Select .NET version – select 4.5 to be safe for now. You can try using other versions
  4. Step 4: Extract DLL – compile the VBA Functions to a VB.NET DLL. Make sure to fix any compilation errors

Compile VBA Examples

Hello World!

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?

Hello World!

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

VBA Compiler Startup KitStruggling 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.

FAQ

Need 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