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
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: