excel c# multithreading

Multithreading VBA using C#.NET

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

Multithreading in VBA in Excel is possible! No wait… that can’t be true right? Almost everyone knows that VBA runs in a single thread. So am I kidding? Not quite! Although this may seems like a workaround there is actually a way to multithread your VBA algorithms. There is only 1 condition… you must be willing to rewrite it to .NET :). Yeah, now you know the catch. But either way this is not as hard as it seems and can prove really useful. You are probably also curious how efficient the native VBA code is vs. called C# dll methods. Below you will find an answer to both questions – how to introduce multithreading to your VBA code and how efficient will multithreaded C# methods be against single threaded VBA algorithms.

Multithreading VBA: Sorting an array of random values using Quicksort

Quicksort is one of the most popular sorting algorithms. It uses the “Divide and Conquer” strategy in order to sort a array of values, partitioning the array of value for sorting and applying the QuickSort algoirhtm to each partition recursively. Quicksort is therefore a natural and simple example for multithreading.

Below you can find a simple example of executing a native VBA QuickSort procedure and both a C# QuickSort sequential and parallel method.

Executing C# QuickSort in VBA

Declare Function CreateTestClass Lib "C:\[PATH TO DLL]\TestLib.dll" () As Object
Sub TestQuickSort()
  '---Variables
  Dim i As Long, ar() As Long, testClass As Object, StartTime as Date, EndTime As Date, stp As Long
  Set testClass = CreateTestClass() 'Load DLL for the first time to reduce 1st execution overhead

  ReDim ar(0 To 100000)
  '---VBA sort---
  Call Randomize(100)
  Call RandArray(ar)
  StartTime = Timer
  Call QuickSort(ar)
  EndTime = Timer
  qTime = Format((EndTime - StartTime), "0.00")
  
  '---C# sequential sort---
  Call Randomize(100)
  Call RandArray(ar)
  StartTime = Timer
  Set testClass = CreateTestClass()
  Call testClass.QuickSortSequential(ar)
  EndTime = Timer
  qTimeC = Format((EndTime - StartTime), "0.00")
  
  
  '---C# parallel sort---
  Call Randomize(100)
  Call RandArray(ar)
  StartTime = Timer
  Set testClass = CreateTestClass()
  Call testClass.QuickSortParallel(ar)
  EndTime = Timer
  pqTimeC = Format((EndTime - StartTime), "0.00")

  '---Print---
  Call MsgBox("VBA time: " & qTime & ", C# sequential time: " & qTimeC & ", C# parallel time: " & pqTimeC)
End Sub

Simple right? If you read my post on this is probably nothing new to you. However, as you can see the only setback is really the requirement to reference the C# dll. You can of course add it to your libraries and reference it in other VBA projects using “Tools->References”.

How are the C# methods implemented?

C# TestClass implementation (Unmanaged Exports)

using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace TestLib
{
    [ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
    public class TestClass
    {
        
        [return: MarshalAs(UnmanagedType.SafeArray)]
        public int[] QuickSortParallel([MarshalAs(UnmanagedType.SafeArray)]ref int[] ar)
        {
            ParallelSort.QuicksortParallel<int>(ar);
            return ar;
        }

        [return: MarshalAs(UnmanagedType.SafeArray)]
        public int[] QuickSortSequential([MarshalAs(UnmanagedType.SafeArray)]ref int[] ar)
        {
            ParallelSort.QuicksortSequential<int>(ar);
            return ar;
        }
    }

    static class UnmanagedExports
    {
        [DllExport]
        [return: MarshalAs(UnmanagedType.IDispatch)]
        static Object CreateTestClass()
        {
            return new TestClass();
        }
    }
}

To learn how to build C# dlls which can be referenced from VBA projects see my blog post here: EXCEL: Using C# in Excel VBA

Below you can find also the popular C# QuickSort implementation (from Google Summer of code 2008):

C# QuickSort implementation

public class ParallelSort 
    { 
        #region Public Static Methods 

        public static void QuicksortSequential<T>(T [] arr) where T : IComparable<T> 
        { 
            QuicksortSequential(arr, 0, arr.Length - 1); 
        } 

        public static void QuicksortParallel<T>(T[] arr) where T : IComparable<T> 
        { 
            QuicksortParallel(arr, 0, arr.Length - 1); 
        } 

        #endregion 

        #region Private Static Methods 

        private static void QuicksortSequential<T>(T[] arr, int left, int right)  
            where T : IComparable<T> 
        { 
            if (right > left) 
            { 
                int pivot = Partition(arr, left, right); 
                QuicksortSequential(arr, left, pivot - 1); 
                QuicksortSequential(arr, pivot + 1, right); 
            } 
        } 

        private static void QuicksortParallel<T>(T[] arr, int left, int right)  
            where T : IComparable<T> 
        { 
            const int SEQUENTIAL_THRESHOLD = 2048; 
            if (right > left) 
            { 
                if (right - left < SEQUENTIAL_THRESHOLD) 
                { 
                    QuicksortSequential(arr, left, right); 
                } 
                else 
                { 
                    int pivot = Partition(arr, left, right); 
                    Parallel.Invoke(new Action[] { delegate {QuicksortParallel(arr, left, pivot - 1);}, 
                                                   delegate {QuicksortParallel(arr, pivot + 1, right);} 
                    }); 
                } 
            } 
        } 

        private static void Swap<T>(T[] arr, int i, int j) 
        { 
            T tmp = arr[i]; 
            arr[i] = arr[j]; 
            arr[j] = tmp; 
        } 

        private static int Partition<T>(T[] arr, int low, int high)  
            where T : IComparable<T> 
        { 
            int pivotPos = (high + low) / 2; 
            T pivot = arr[pivotPos]; 
            Swap(arr, low, pivotPos); 

            int left = low; 
            for (int i = low + 1; i <= high; i++) 
            { 
                if (arr[i].CompareTo(pivot) < 0) 
                { 
                    left++; 
                    Swap(arr, i, left); 
                } 
            } 

            Swap(arr, low, left); 
            return left; 
        } 

        #endregion 
    }

As you will notice the above parallel QuickSort implementation utilizes the C# Parallel.Invoke method which allows you to easily leverage multithreading in C# without the hassle of having to manage thread joining etc. The Parallel C# class facilitates the following methods which make multithreading fairly easy:

  • Parallel.For – for iterating over integers
  • Parallel.ForEach – for iterating over any array of objects
  • Parallel.Invoke – basically invokes any number of actions (threads)

Using the For and ForEach linq methods are very straightforward making it very easy to mulithread exisiting singlethreaded algorithms. See a simple Parallel for example for building a multiplication table below:

Parallel.For(0, maxJ, i =>
        {
            for (int j = 0; j < maxJ; j++)
            {
                result[i, j] = i*j;
            }
        }); 

If you would like to see the VBA implementation of QuickSort see below. This implementation was based on this algorithm: Link

VBA QuickSort implementation

Private Sub QuickSort(ByRef values As Variant, Optional ByVal Left As Long, Optional ByVal Right As Long)
  Dim i As Long
  Dim j As Long
  Dim K As Long
  Dim Item1 As Variant
  Dim Item2 As Variant

  On Error GoTo Catch
  If IsMissing(Left) Or Left = 0 Then Left = LBound(values)
  If IsMissing(Right) Or Right = 0 Then Right = UBound(values)
  i = Left
  j = Right

  Item1 = values((Left + Right)  2)
  Do While i < j
    Do While values(i) < Item1 And i < Right
      i = i + 1
    Loop
    Do While values(j) > Item1 And j > Left
      j = j - 1
    Loop
    If i < j Then
      Call Swap(values, i, j)
    End If
    If i <= j Then
      i = i + 1
      j = j - 1
    End If
  Loop
  If j > Left Then Call QuickSort(values, Left, j)
  If i < Right Then Call QuickSort(values, i, Right)
    Exit Sub
Catch:
  MsgBox Err.Description, vbCritical
End Sub
Private Sub Swap(ByRef values As Variant, ByVal i As Long, ByVal j As Long)
  Dim Temp1 As Double
  Dim Temp2 As Double
  Temp1 = values(i)
  values(i) = values(j)
  values(j) = Temp1
End Sub

QuickSort Performance: Native VBA vs. C# sequential vs. C# multithreading

Now let’s analyze the performance of the various algorithms. The benefits of multithreading will be clearly visible when we’ll compare arrays of several sizes and sort them using the various algorithms:

Disclaimer: The following performance metrics are not precise as they simply measure the time that elapsed during code execution and do not reflect the actual processor time. What is more the QuickSort implementations are also slightly different which might also somewhat influence performance. The statistics can nevertheless be used to show the relative performance between VBA and C# (referenced from within VBA projects).

QuickSort: Native VBA vs. C# sequential vs. C# multithreading
QuickSort: Native VBA vs. C# sequential vs. C# multithreading

While it takes almost 15 seconds to sort 2 mln elements with the VBA singlethreaded QuickSort, the C# singlethreaded algorithm requires only slightly above 1 second and the multithreaded algorithm required approx. half a second! For me it was a real surprise that even without multithreading the C# algorithm was sooo much faster than the native VBA algorithm (over 10-12x). In case of the multithreaded algorithm there are of course several performance parameters to consider like the “SEQUENTIAL_THRESHOLD” parameter.

Conclusions: Multithreading in VBA possible, but not in native VBA

As you can see multithreading in VBA is possible if you export your VBA algorithms to C# and reference them from inside your VBA projects. As shown above you can expect significantly improved performance even in singlethreaded algorithms and leverage additional performance boosts thanks to multithreading!. I know that this may seem like a little unsatisfactory – as it’s not actually native VBA multithreading. On the other hand Excel is not a tool designed and appropriate for implementing processor intensive algorithms. What is more, even if VBA multithreading were possible, I hope you noticed that either way even the singlethreaded imported C# method was still much faster (10-12x) than the one in VBA.

You may rarely experience such situations in which your VBA algorithms will take more than a couple of seconds to execute. However, for those of you working in analytics – hopefully this will be a useful tool.

Let me know what you think!

Be sure to checkout a comparison of all available approaches to VBA multithreading:
EXCEL: Multithreading VBA – VBA vs. VBscript vs. C#.NET.

Here you can download the Excel xlsm file and the C# Project:

Related Posts

16 thoughts on “Multithreading VBA using C#.NET”

    1. Native in the sense that it is not utilizing external libraries and applications like the COM/dll libraries or at least only those available be default in any Excel installation. That’s my understanding – maybe I should have made it clear at the beginning..

  1. You seem to first try to reduce the overhead of creating an instance with: Set testClass = CreateTestClass(), and then you are calling it again in the ‘—C# sequential sort— part after the timer has been already instantiated?

    1. My assumption is that there might be some overhead with loading the dll into memory as, according to my knowledge, just the “CreateTestClass” declaration will not actually load the COM/dll into memory. Therefore I am executing the method for the first time at the very beginning and thus reducing the overhead in case of measuring the TestClass methods’ performance (this is critical when executing QuickSort for 100k elements).

  2. So basically multithreading in VBA is NOT possible but you can pack your C# code into a COM library and use cool tricks to improve the overall speed of calculations.

    Pretty cool, good effort I will try all the code tomorrow :)

    1. In some sense Yes and No right? :D De iure it is not possible in native VBA (that’s why I am using this term). De facto it is possible by using COM/dll written in C#.NET. You are certainly right that this is great for packing code to improve overall speed of calculations. If I would push further I could even write a COM/dll written in C/C++ and use methods written in assembler… however multithreading would then again be a great challenge therefore better to stay with C#.NET and the Parallel class.

      Actually I am writing a post about an alternative approach to multithreading in VBA which will not require the use of COM/dlls but will leverage the Windows Shell – so stay tuned.

  3. Hey, I have had a few minutes to run this test in VBA this morning.

    First thing I did was improve your current VBA code – eliminated Variant types, improved the QuickSort() a bit created the RandArray function to populate the array (which seemed to be missing from your code).

    To get the dll to work in VBA you have to set the target platform to x86 in the Solution Properties in VS.

    Well, my average result for VBA QuickSort on 2 million records (ran 10K times) is actually not THAT bad comparing to .NET -> 3.514248046875

    Go try that yourself if you want:

    Option Explicit

    Sub Main()

    Dim average As Double

    Dim i As Long
    For i = 0 To 10 ' increase if needed
    average = (average + TestQuickSort) / 2
    Next

    Debug.Print average

    End Sub

    Function TestQuickSort()

    Dim i As Long, ar() As Long, StartTime As Date, EndTime As Date
    Dim qTime As Double

    ReDim ar(0 To 1999999)
    Call RandArray(ar)
    StartTime = Timer
    Call QuickSort(ar, LBound(ar), UBound(ar))
    EndTime = Timer
    qTime = EndTime - StartTime

    TestQuickSort = qTime
    End Function

    Public Sub QuickSort(vArray() As Long, inLow As Long, inHi As Long)

    Dim pivot As Long
    Dim tmpSwap As Long
    Dim tmpLow As Long
    Dim tmpHi As Long

    tmpLow = inLow
    tmpHi = inHi

    pivot = vArray((inLow + inHi) \ 2)

    While (tmpLow <= tmpHi)

    While (vArray(tmpLow) < pivot And tmpLow < inHi)
    tmpLow = tmpLow + 1
    Wend

    While (pivot inLow)
    tmpHi = tmpHi - 1
    Wend

    If (tmpLow <= tmpHi) Then
    tmpSwap = vArray(tmpLow)
    vArray(tmpLow) = vArray(tmpHi)
    vArray(tmpHi) = tmpSwap
    tmpLow = tmpLow + 1
    tmpHi = tmpHi - 1
    End If

    Wend

    If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
    If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi

    End Sub

    Private Sub RandArray(ByRef values As Variant)
    Dim i As Long
    For i = LBound(values) To UBound(values)
    values(i) = Int((100000 - 0 + 1) * Rnd + 0)
    Next
    End Sub

    1. Thanks Mike for optimizing the VBA code! Seems like C#.NET is still a lot faster though. I didn’t want to spend too much time on tinkering the statistics that’s why I added the “Disclaimer” but I greatly appreciate your effort. The key call outs are that C#.NET is still much faster and allows multithreading compared to “native” VBA. Did you see my article on VBA VBscript multithreading vs. VBA C#.NET multithreading? I am planning to also compare some rough stats and do a VBscript vs. C#.NET showdown.

      Thanks for pointing out the x86 settings – forgot to mention that. Pending edit.

    1. Thanks this is machine-specific so can’t update post. But still based on your metrics seems like for 2mln elements you get a 4x speed improvement with C#.NET and 16x speed improvement with multithreaded C#.NET. Still a significant boost factor! :)

      1. Oh yes that’s so true. I sometimes too make COM dlls in C# when performance is critical in a project that still needs to be hosted in Excel. You can get away with a lot of things, I specially love the idea of passing a 2D array to .NET, converting it to .NET Array class and then you can use all the “cool” stuff without writing out all the algorithms ( reverse, you can do linq on collections, deduplicate, etc ).

        .NET is a lot faster than VBA but bare in mind that VBA was designed like 20 years ago :P

        1. Yeah passing arrays allows you to leverage all the awesome Linq queries (loving C#.NET…). I work around this in VBA/Excel using OLE DB SQL queries but it’s not as easy.

          Considering VBA vs. .NET I am not complaining that VBA should be just as fast. I was not expecting it even to be just as fast as Excel as, to be frank, I consider Excel not a tool for creating complex IT solutions but prototypes at best. For me the possibility of referencing external COM dlls is totally satisfactory as this still allows me to leverage any functionality I need in Excel VBA. VBA is still pretty cool and even more respect if it was designed like 20 years ago :D.

Leave a Reply