Recently I published the post on using C#.NET to do multithreading in VBA – find it here: EXCEL: Multithreading in VBA using C#.NET. In that post I stated that multithreading is not possible natively in VBA only using external libraries e.g. COM/dll in C#.NET. In result of that post my attention was drawn to an idea I once thought about – using the Windows Shell to execute VBscript. Daniel Ferry’s article looks into it and gives a great example of how you can actually achieve multithreading in VBA using a VBscript swarm without resorting to external libraries although still reaching out of native VBA.

I strongly encourage you to visit Daniel’s article page and download his fantastic example of website scraping using a VBscript swarm vs. singlethreaded VBA IE / MSXML2 scraping. I loved the animation showing how the swarm runs!

However, in this post I wanted to present you with a basic example and provide you with some reusable code that you might use for your own purposes to implement multithreading, w/o resorting to external libraries. Next I will take a look at the increase in performance which I achieved on a simple algorithm due to multithreading.

Multithreading VBA using VBscript example

So let’s start with the example:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim maxThreads As Long, divTabSize As Long, startTime As Double
Public Sub MultithreadedMultiplication()
    '---Clear previous values---
    If Range("A2").Value <> "" Then
        Range("A2:A" & Range("A1").End(xlDown).Row).Value = ""
    End If
    
    Dim thread As Long
    maxThreads = 4
    divTabSize = 10000 'Amount of elements in the division table
    'A simple division algorithm (a multiplication table would quickly cause overflow)
    startTime = Timer
    For thread = 1 To maxThreads
        CreateVBScriptThread thread, maxThreads, divTabSize
    Next thread
    CheckIfFinished 'Wait until all threads finish
End Sub
Public 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 = 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)" & vbCrLf
    '---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()
    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("F1").Offset(maxThreads).Value = maxThreads
                Range("G1").Offset(maxThreads).Value = elapsed
                Exit Sub
            End If
        End If
        Sleep 100
    Loop
End Sub

Yeah, that’s basically it! Just 40-something lines of code for a simple multithreading example in VBA. I would have thought that it would be much more complicated, but apparently as you can see this is not the case.

The example code runs a very simple algorithm which aim is to calculate a division table (just to avoid multiplication overflow). If you use more than one thread the division table will be partitioned so each thread gets an equal slice of the action. Each VBscript will run in a separate thread, calculate the partition and simply return it’s run time directly to our current Excel Workbook. The result will look somewhat like this:

The basic approach is the following:

• Take any algorithm and partition it for each separate thread
• Create as many VBscript files and you need threads
• Execute each one concurrently using the WScript.Shell object

As mentioned each VBscript thread, if required, can save it’s output directly to the current Excel Worbook which is incredible and actually increases the practicality of this example.

Now for comparison I could right the same algorithm in native VBA:

Sub DivisionSingleThread()
    Dim i, j, x, startTimeS, endTimeS
    startTimeS = Timer
    '---Compute partition of the division table---
    For i = 1 To divTabSize
       For j = 1 To divTabSize
          x = i / j
       Next
    Next
    endTimeS = Timer
    Range("A2") = (endTimeS - startTimeS)
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Range("G1").Offset(maxThreads).Value = elapsed
End Sub

Performance analysis

Now what about performance. You would expect multithreading to significantly increase your algorithms performance. I tested the algorithm with a 5000 x 5000 table of elements (5000×5000 divisions) on a 2-core 4 threaded Intel processor (i5-4300U) with the following results:

To be frank I was very surprised to see that the singlethreaded VBA algorithm was actually faster than any VBscript multithreaded example. The VBscript algorithm reached its highest improvement in terms of performance at 2 VBscript threads. Adding additional threads did not seems to significantly improve performance, although I was expecting more or less to improve execution time proportionally until 4 threads are reached. This is probably because my processor is only 2-core and the additional threads are virtual.

To make the 2 algorithms as similar as possible I did not explicitly declare my variable types which Michael from VBA4All rightfully noticed (in my previous post) can help significantly improve VBA performance. If I would modify the VBA algorithm accordingly the execution time would drop to even approx. 1 second! Unfortunately VBscript forbids explicit variable data type declaration which is a serious setback.

Does this mean that multithreading in VBA using VBscript does not make sense? In most cases it won’t. However, there are cases when this could be useful e.g. in Daniel VBscript swarm web scraping example where VBscript utilizes similar objects/system resources as VBA. What is more, although VBscript was much slower on my 2-core laptop, if I had much more cores/processors VBscript might actually make up the distance.

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.

Find the example XLSM file here: