Category Archives: C#.NET

save lync conversations

Automatically save Lync conversations (when feature is blocked)

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Honestly, I cannot seem to explain why do some corporations block the Save IM conversations in my email Conversation History folder. What is more funny is that blocking this feature (at least in Ms Lync 2013) will not prevent you from saving your conversations manually (hit CTRL + S).

Automatic conversation saving feature - disabled by some corporations
Automatic conversation saving feature – disabled by some corporations

Unfortunately there is no way to somehow manually enable this option and so the only way around this for me was developing my own MS Lync conversation logger.

The Lync Logger – save Lync conversations

Save Lync conversations: LoggerThe Lync Logger is simple app that requires MS Lync 2013 (not tested with 2010) and at least .NET 4.5 installed. It runs in the background as a notification icon listening for MS Lync IM conversations. Once a conversation concludes the entire contents as saved as an MS Excel file where each row represents a single message of the conversation.

All files are saved in the C:UsersUSERNAMEAppDataRoamingLyncLogger folder which can be opened at any time when you right-click on the LyncLogger notification icon and select “Open log folder”.

Installation

Download the ZIP from here:


Unpack the contents to any preferred location. Next create a link to the “LyncLogger.exe” file in your Windows Start – “Startup” folder. That is it!

The app is licensed under a standard GNU Public Licence.

Features

Once you run the LyncLogger it will appear as an icon in your taskbar notification area:
Save lync conversations: Notification
The app will run logging all your conversation automatically.

By right-clicking on the icon you will see the following options:

  • Open log folder – will open the C:\Users\USERNAME\AppData\Roaming\LyncLogger containing all the logs;
  • How to Search – a short explanation of how to turn on file indexing in the folder above to easily search through the contents of all you conversations
  • Exit – closes the app
Native VBA Multithreading

Multithreading VBA – VBA vs. VBscript vs. C#.NET

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


I you were looking for an end-to-end analysis of the topic of multithreading VBA. You have come to the right place! So lately I have been really wearing out the topic of multithreading in Excel in VBA. Hopefully you have already read through my three posts on the various methods of achieving multithreading capabilities in VBA:

In all three posts I showed that, although “native” VBA does not feature multithreading, you can in fact achieve multithreading by using either external COM/dlls (e.g. in C#.NET), running VBscripts via the Windows Shell command or run additional VBA worker threads via VBscript. All three approaches have their ups and downs. Now just to clarify: What do I mean by “native”? I mean that VBA as a programming language does not natively support creating new threads, joining threads etc.

Pro’s and Con’s of all multithreading VBA approaches

Let us summarize them in the table below:

Multithreading approach Pro’s Con’s
Using a C#.NET COM/dll
  • Best performance – even singlethreaded C#.NET COM/dlls will significantly reduce the execution time of your algorithms
  • Easy parallelism – C# provides the “Parallel” class which allows you to easily create and manage threads and run for loop in multiple threads
  • Requires learning C#
  • Requires referencing an external dll – must make sure it is not lost when sharing the Excel file
  • Requires a separate programming environment (e.g. Visual Studio) to create, modify and compile
VBscript worker threads
  • Fairly easy to implement
  • VBscript is almost identical with VBA language
  • Worst performance – as VBscript is much slower than native VBA in most cases multithreading your algorithms using multiple VBscript worker threads will extend execution time instead of reducing it. I would recommend using VBscript worker threads only if the threads are to leverage other system libraries/resources
VBA worker threads (via VBscript)
  • Good performance – you should see a proportional reduction of execution time, at some point, when using this approach (2 cores ~= 2x faster)
  • Most complicated – VBA worker threads run via VBscript
  • More room for errors
  • Overhead – multithreading makes sense when VBA algorithm takes at least 5 sec or more depending on the amount of cores/processors available. This is due to the overhead resulting from invoking separate VBA worker threads in separate Excel processes via VBscript

Performance: Native VBA vs. VBA workers vs. VBscript workers vs. C#.NET

I would like to warn you that these are only example metrics run on a specific environment (on a 2-core 4 threaded Intel processor (i5-4300U)). I didn’t focus too much of my time on drafting very precise metrics as these statistics are simply meant to give you the general idea of the relative performance of all approaches.

Comparison of different approaches to multithreading VBA
Comparison of different approaches to multithreading VBA

As you can see the VBscript parallelism approach provides very lousy performance and it gets worse the more operations the algorithm has. I know what you are thinking now: well then want is the sense of using VBscript multithreading if the performance is awful in the provided example? Well there are examples when VBscript threading makes sense see Daniel’s example here. In most common examples you should, however, disregard the VBscript approach therefore I removed it from the chart and focused on the remaining methods:

Comparison of different approaches to multithreading VBA (excluding VBscript worker threads)
Comparison of different approaches to multithreading VBA (excluding VBscript worker threads)

As expected C#.NET rules the stats! While it took at least 26 sec to compute 900mln operations in “native” VBA, C#.NET managed to do this in less than 2 sec. On the other hand the VBA parallel workers managed to reduce the computation time to about 10 sec which is less than half the “native” VBA execution time. Still not bad – would have been much better if my CPU had more cores. Notice that the break-even point between the “native” VBA algorithm and the VBA parallel workers methods was at about 100mln operations – taking approx. 3 seconds. It seems therefore that there is a couple of seconds of overhead in case of the latter approach.

Source code and example

If you want to download the whole source code containing all these example approaches see the link at the bottom of this post. To gather these performance statistics I made a single Excel workbook that allows you to test all multithreading VBA approaches and verify which is the best fit for your algorithm / use case.

Screenshot from the Excel Workbook consolidating the findings (download link at the bottom)
Screenshot from the Excel Workbook consolidating the findings (download link at the bottom)

VBA – No multithreading

Below you can find the a “native” VBA example of a simple division algorithm. This is the baseline for the other algorithms.

VBA – no multithreading (the baseline)

Sub VBASinglethread()
    Dim i As Long, j As Long, x As Double, startTimeS As Date, endTimeS As Date
    startTimeS = Timer
    '---Compute partition of the division table---
    For i = 1 To divTabSize
       For j = 1 To divTabSize
          x = CDbl(i) / j
       Next
    Next
    endTimeS = Timer
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Range("J2").Value = elapsed
    DoEvents
End Sub

 

C#.NET- Single and multithreaded

Below you can find 2 example algorithms in C#.NET. One is singlethreaded and the other one leverages the C# Parallel class to easily achieve multithreading.

C#.NET COM/dll code

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 ParallelMethod(int divTabSize)
        {   
            Parallel.For(0, divTabSize, i =>
            {
                double x;
                for (int j = 1; j < divTabSize; j++)
                {
                    x = i / j;   
                }
            });
            return 0;
        }

        [return: MarshalAs(UnmanagedType.SafeArray)]
        public int SequentialMethod(int divTabSize)
        {
            double x;
            for (int i = 1; i < divTabSize; i++)
            {
                for (int j = 1; j < divTabSize; j++)
                {
                    x = i / j;
                }
            }
            return 0;
        }
    }

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

 

C#.NET COM/dll code – VBA example

Declare Function CreateTestClass Lib "C:Users[PATH TO DLL]TestLib.dll" () As Object
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub CshartSequential()
    Dim startTimeS As Date, endTimeS As Date
    startTimeS = Timer
    Dim testClass As Object
    Set testClass = CreateTestClass()
    Call testClass.SequentialMethod(divTabSize)
    endTimeS = Timer
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Range("K2").Value = elapsed
    DoEvents
End Sub
Sub CshartParallel()
    Dim startTimeS As Date, endTimeS As Date
    startTimeS = Timer
    Dim testClass As Object
    Set testClass = CreateTestClass()
    Call testClass.ParallelMethod(divTabSize)
    endTimeS = Timer
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Range("L2").Value = elapsed
    DoEvents
End Sub

 

VBscript multithreading

Below the source code for multithreading using VBscript worker threads. Interesting but usually of little use due to terrible performance. See Daniel Ferry’s good example of when it is worth leveraging.

VBscript worker threads

Sub VBscriptParallel(maxThreads As Long)
    Dim thread As Long, threads As Long
    For threads = 1 To maxThreads
        ClearThreadTime
        'A simple division algorithm (a multiplication table would quickly cause overflow)
        startTime = Timer
        For thread = 1 To threads
            CreateVBScriptThread thread, threads, divTabSize
        Next thread
        CheckIfFinished threads
    Next threads
End Sub
Sub CreateVBScriptThread(threadNr As Long, maxThreads As Long, divTabSize As Long)
    Dim s As String, sFileName As String, wsh As Object
    '---Copy parameters to VBscript---
    s = s & "dim i, j, oXL, x, startTime, endTime" & vbCrLf
    s = s & "startTime = Timer" & vbCrLf
    '---Compute partition of the division table---
    s = s & "For i = " & CInt(CDbl(divTabSize) * (threadNr - 1) / maxThreads + 1) & " To " & CInt(CDbl(divTabSize) * threadNr / maxThreads) & vbCrLf
    s = s & "   For j = 1 to " & divTabSize & vbCrLf
    s = s & "      x = CDbl(i) / j" & vbCrLf
    s = s & "   Next" & vbCrLf
    s = s & "Next" & vbCrLf
    '---Save the threadNr back to your Excel file! - this is the incredible part really
    s = s & "Set oXL = GetObject(, ""Excel.Application"")" & vbCrLf
    s = s & "endTime = Timer" & vbCrLf
    s = s & "oXL.workbooks(""" & ThisWorkbook.Name & """).sheets(""Status"").Range(""" & "A" & (threadNr + 1) & """) = (endTime - startTime)"
    '---Save the VBscript file---
    sFileName = ActiveWorkbook.Path & "Thread_" & threadNr & ".vbs"
    Open sFileName For Output As #1
    Print #1, s
    Close #1
    '---Execute the VBscript file asynchronously---
    Set wsh = VBA.CreateObject("WScript.Shell")
    wsh.Run """" & sFileName & """"
    Set wsh = Nothing
End Sub
'---------Verify if finished----------
Sub CheckIfFinished(maxThreads As Long)
    Dim endTime As Double, elapsed As String
    Do Until False
        DoEvents
        If Range("A2").Value <> "" Then
            If Range("A2:A" & Range("A1").End(xlDown).Row).Count = maxThreads Then
                endTime = Timer
                elapsed = "" & Format(endTime - startTime, "0.00")
                Range("G1").Offset(maxThreads).Value = maxThreads
                Range("H1").Offset(maxThreads).Value = elapsed
                Exit Sub
            End If
        End If
        Sleep 50
    Loop
End Sub

 

VBA multithreading (via VBscript)

Below the code to a good alternative to the C#.NET COM/dll approach. It uses VBscripts just for creating separate VBA worker threads. This approach can provide satisfactory results although not as incredible as C#.NET.

VBA worker threads (via VBscript)

Sub VBAMultithread(maxThreads As Long)
    Dim thread As Long, threads As Long
    For threads = 1 To maxThreads
        ClearThreadTime
        startTime = Timer
        For thread = 1 To threads
            CreateVBAThread thread, threads, divTabSize
        Next thread
        CheckIfFinishedVBA threads
    Next threads
End Sub
Sub CreateVBAThread(threadNr As Long, maxThreads As Long, divTabSize As Long)
    Dim s As String, sFileName As String, wsh As Object, threadFileName As String
    '---Save a copy of the Excel workbook---
    threadFileName = ActiveWorkbook.Path & "Thread_" & maxThreads & "_" & threadNr & ".xls"
    Call ActiveWorkbook.SaveCopyAs(threadFileName)
    '---Save the VBscript---
    s = "Set objExcel = CreateObject(""Excel.Application"")" & vbCrLf
    s = s & "Set objWorkbook = objExcel.Workbooks.Open(""" & threadFileName & """)" & vbCrLf
    s = s & "objExcel.Application.Visible = False" & vbCrLf
    s = s & "objExcel.Application.Run ""Thread_" & maxThreads & "_" & threadNr & ".xls!RunVBAMultithread"" ," & threadNr & "," & maxThreads & "," & divTabSize & ",""" & ActiveWorkbook.Name & """" & vbCrLf
    s = s & "objExcel.ActiveWorkbook.Close" & vbCrLf
    s = s & "objExcel.Application.Quit"
    '---Save the VBscript file---
    sFileName = ActiveWorkbook.Path & "Thread_" & threadNr & ".vbs"
    Open sFileName For Output As #1
    Print #1, s
    Close #1
    '---Execute the VBscript file asynchronously---
    Set wsh = VBA.CreateObject("WScript.Shell")
    wsh.Run """" & sFileName & """"
    Set wsh = Nothing
End Sub
Public Sub RunVBAMultithread(threadNr As Long, maxThreads As Long, divTabSize As Long, workbookName As String)
    'A simple division algorithm (a multiplication table would quickly cause overflow)
    Dim i As Long, j As Long, x As Double, startTimeS As Date, endTimeS As Date
    startTimeS = Timer
    '---Compute partition of the division table---
    For i = CInt(CDbl(divTabSize) * (threadNr - 1) / maxThreads + 1) To CInt(CDbl(divTabSize) * threadNr / maxThreads)
       For j = 1 To divTabSize
          x = CDbl(i) / j
       Next
    Next
    endTimeS = Timer
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Dim oXL
    Set oXL = GetObject(, "Excel.Application")
    oXL.Workbooks(workbookName).Sheets("Status").Range("A" & (threadNr + 1)) = elapsed
    Set oXL = Nothing
End Sub
'---------Join threads - wait until all finish and save elapsed time to worksheet----------
Sub CheckIfFinishedVBA(maxThreads As Long)
    Dim endTime As Double, elapsed As String
    Do Until False
        DoEvents
        If Range("A2").Value <> "" Then
            If Range("A2:A" & Range("A1").End(xlDown).Row).Count = maxThreads Then
                endTime = Timer
                elapsed = "" & Format(endTime - startTime, "0.00")
                Range("I1").Offset(maxThreads).Value = elapsed
                Exit Sub
            End If
        End If
        Sleep 50
    Loop
End Sub

 

Summary

Hopefully I have exhausted the topic of multithreading in VBA. I am certain there are other methods of multithreading in VBA although I would not think that it would be worth taking them into consideration. Now for the ultimate conclusions. You can leverage several approaches to VBA multithreading although each has it’s drawback and advantages. I would not eliminate any of the presented approaches as I think this depends on the algorithm you are designing / programming. I personally would almost always go with C#.NET as I am comfortable with C# and when making complex algorithms it is probably better to create them from the very beginning in a language designed to support multithreading. I understand, however, that some of my readers would prefer staying with VBA and for those I would recommend the VBA worker threads – a little more complex but still very efficient.

What do you think? What are your experiences? Did you ever had to consider multithreading in VBA?

Download

Here you can download the whole project source codes and the complete set of files:

Next steps

Check out the VBA Mulithreading Tool (the easiest way to add multithreading to Excel macro):
EXCEL: VBA Multithreading Tool
Check out the VBA Mulithreading Add-In to add multithreading to all Excel Workbooks:
EXCEL: VBA Multithreading AddIn

Want to learn how to add multithreading using C#.NET?
EXCEL: Multithreading VBA using C#.NET
Want to learn how to add multithreading using VBscript?
EXCEL: Multithreading VBA using VBscript
Want to learn how to add multithreading using VBA via VBscript?
EXCEL: Multithreading using VBA via VBscript

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:

c# excel

Using C# in Excel VBA

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

Today’s topic concerns using C# from within VBA code(Excel C#). Ever had a C#.NET library you wanted to use in Excel VBA (Excel C#)? Well, as a matter of fact it is possible using the Visual Studio Nuget Unmanaged Exports package.

Now why on earth would you want to use C# in Excel by using dll libaries? Well there are a bunch of reasons to consider. Top of mind:

  • Utilizing many available existing C#.NET libraries in Excel VBA
  • Multi-threading – Excel VBA is limited to 1 thread whereas C#.NET can leverage multithreading
  • Use drivers and connections not available Excel VBA (connect to NoSQL databases and custom systems)
  • Protect your code and algorithms compiling them to dll libraries – VBA project protection is not a safe method of protecting your code!
  • Ease of creating and managing complicated solutions with the support of VS tools (debugging, performance analysis, testing)
  • Portability – make sure you algorithms runs exactly the same in VBA, your C#.NET project and your ASP.NET application

Before you follow this tutorial be sure to check-out my VBA Compiler Add-In for Excel which allows you to easily export and compile your VBA Functions to VB.NET DLL!

This tutorial will show you how to create a COM-visible DLL in C#.NET in just 5 easy steps:

Create New Visual Studio Project

  • Create a new VS project
  • Select Class library from the C# group
  • Go to Project Properties and make your library to be COM-visible
New Class Library project
New Class Library project

Show Package Manager Console

To install the Unmanaged Exports template you will need to use the Nuget Package Manager Console. Enable it as shown below:

Show Package Manager Console
Show Package Manager Console

Install the Unmanaged Exports template to the project

  • Open the Package Manager Console
  • Paste Install-Package UnmanagedExports into the console and hit enter
Install the UnmanagedExports template
Install the UnmanagedExports template

Create a class with some example code

  • Create a sample “Sample.cs” class
  • Insert the code below to the “Sample.cs” file
  • Build the Project
using RGiesecke.DllExport;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;

namespace TestLib
{
    [ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
    public class TestClass
    {
        public string Text
        {
            [return: MarshalAs(UnmanagedType.BStr)]
            get;
            [param: MarshalAs(UnmanagedType.BStr)]
            set;
        }

        public int Numbers
        {
            [return: MarshalAs(UnmanagedType.SysInt)]
            get;
            [param: MarshalAs(UnmanagedType.SysInt)]
            set;
        }

        [return: MarshalAs(UnmanagedType.SysInt)]
        public int GetRandomNumber()
        {
            Random x = new Random();
            return x.Next(100);
        }
    }

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

Use the C# class in Excel VBA (Excel C#)

Just insert the following code into your Excel VBA project and you are good!

Declare Function CreateTestClass Lib "C:\[Path to the DLL]\TestLib.dll" () As Object

Sub TestTheTestClass()
  Dim testClass As Object
  Set testClass = CreateTestClass()'Creates an instance of TestClass
  Debug.Print testClass.GetRandomNumber'Executes the method
  testClass.text = "Some text"'Set the value of the Text property
  testClass.Number = 23'Set the value of the Number property
  Debug.Print testClass.text
  Debug.Print testClass.Number
End Sub

Notice that C# types have to be converted to VBA types using the MarshalAs tag. Read more about the different Umanaged Types in C# here: Link.
Below is a list of some typical types you might want to leverage:

C# Type Unmanaged Type VBA Type Comment
String UnmanagedType.BStr String
int UnmanagedType.SysInt Integer
bool UnmanagedType.VariantBool Boolean
Class UnmanagedType.IDispatch Object Return a class object to VBA
Array e.g. int[] ar UnmanagedType.SafeArray Array e.g. Dim ar() As Long If you are using an array as an argument be sure to use the C# “ref” get by reference option e.g. ref int[] ar

Excel C# troubleshooting

In case you are experiencing issues with interfacing with the DLL please check the following:

  • Did you mark the DLL to be COM-visible in your Project Properties in Visual Studio
  • Have you switched the Platform Target in the class library properties to “x86”
  • Is the filename of the DLL in your VBA code correct
  • Are there any typos in e.g. the class name, methods etc.