Native VBA Multithreading

Multithreading VBA – VBA worker threads via VBscript

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

Yet again I am approaching the subject of multithreading in VBA. This time I am coming with an interesting alternative to multithreading in VBA compared to the ones I presented previously. So again let me start by repeating that multithreading is possible in VBA but not in “native” VBA. What do I mean by “native”? I mean that VBA as a programming language does not natively support creating new threads, joining threads etc. However, you can achieve multithreading by using external libraries, modules or scripts.

This post is the result of my previous post EXCEL: Multithreading in VBA using VBscript. In that article I focus on an idea of using the Windows Shell to call multiple VBscript worker threads. Although this allowed me to quite easily achieve multithreading VBA, on the the other hand VBscript turned out to be much slower than VBA therefore even multithreaded algorithms would work slower than a single threaded VBA algorithm. As a result of these findings I decided to leverage VBA worker threads (instead of VBscript threads) via Windows Shell (and VBscript). I know this is fairly complicated so let me explain and continue with a simple example.

How multithreading VBA with VBA worker threads works

Multithreading using VBA workers via VBscript
Multithreading using VBA workers via VBscript

As shown above the concept is to replicate as many Excel workbooks as we need threads and then run the macros concurrently via VBscript.

Mulithreading VBA example

This is the code. 60-something lines of code – so not too bad :):

Sub VBAMultithread(maxThreads As Long)
    Dim thread As Long, threads As Long
    For threads = 1 To maxThreads
        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
    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
        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
End Sub

Now what do the various procedures do?:

  • VBAMultithread – the main thread which creates all the worker threads
  • CreateVBAThread – creates a single thread. Copies the current workbook and creates a VBscript which is executed via Windows Shell. This script will run the RunVBAMultithread macro
  • RunVBAMultithread – the worker macro which does the computations: which calculates a partition of the division table and save the elapsed time to the Master Excel Workbook
  • CheckIfFinishedVBA – this macro will execute once all worker threads have been created. It will wait (Sleep and check in loop) until all worker threads have finished in order to evaluate the total execution time of the algorithm


Please see this post on how this method of multithreading in VBA compares to other available approaches:
EXCEL: Multithreading – VBA vs. VBscript vs. C#.NET


You can download the source code here:

More links on Mulithreading VBA

Check out the VBA Mulithreading Tool (the easiest way to add multithreading to Excel macro):
EXCEL: VBA Multithreading Tool

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 see how all multithreading approaches compare?
EXCEL: Multithreading – VBA vs. VBscript vs. C#.NET

Related Posts

8 thoughts on “Multithreading VBA – VBA worker threads via VBscript”

  1. Im wondering about the overall integrity of the workbook when you have multiple workers each writing to their own COM instance. Is there anything special that needs to be done to make sure that all data is properly written to the workbook before the worker is destroyed?

    1. I would suggest you analyze the code of my VBA Multithreading Tool (link) for how I handle joining the various threads (trying to set a named range value representing the thread until I don’t get an error). Basically from my experience you will NOT be able to write to a COM instance when another thread is doing this – you will get an error. Therefore if you are worried about integrity you might take two approaches – either implement a mutex/semaphore of some kind or basically do as I do – make a loop until you don’t get an error from the COM instance when writing.

      I have been using the VBA Multithreading Tool for some time now and I personally haven’t experienced any issues with the files integrity or reliability. My approach is to usually export the final results from my workers threads in one-go to the master file – this way there is less room for the threads to collide.

  2. I was testing out the code as supplied and am getting an error:

    “Cannot run macro… The macro may not be available in this workbook or all macros may be disabled. ”

    I have tried turning off all macro security and making this a trusted location. Are any other changes required to prevent this security error?


    1. Hi Chris,

      reasons may be plenty. However, I think in the code above I missed declaring the Sleep function. Try adding this piece of code:

      #If VBA7 Then
      Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
      Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds as Long)
      #End If

  3. Hi there

    I am currently in the works of making a tool that generates a design of “something” based on multiple worksheets in a workbook. I need to cycle thorugh 5-6 design parameters in small steps one after the other to determine the combination with the least material used, which gives the lowest price of this “something”.

    Currently, this is done in VBA and its getting very cumbersome. I suspect that VBA being single threaded might be what is slowing me down compared to other programming languages.

    How can i make use of your program in my instance? Assume that i

    1) have input data entered in a master excel sheet in multiple worksheet (fx. design parametes, cost parameters

    2) Have multiple worksheets that “determines” the geometry from the inputs

    3) have a sheet with weight calculation

    4) have a sheet withcalculation of the price (weight times cost parametes)
    I currently have my code like this:

    Optimization of geometry ()

    For i = 1 to 100
    For j = 1 to 100
    For k = 1 to 100
    Next (i,j,k)

    If price_trial > lowest_price Then
    Store design()
    End If

    Can i use your program in this instance?

    1. Hi @zarnarkand,

      what you need is my VBA Multithreading Tool. You can use it to optimize the execution time of your program (proportionally based on the amount of cores).

      How can i make use of your program in my instance? Assume that i
      1) have input data entered in a master excel sheet in multiple worksheet (fx. design parametes, cost parameters

      This ain’t a problem as the tool replicates your Workbook and executes the same macro for different parameters.

      2) Have multiple worksheets that “determines” the geometry from the inputs
      3) have a sheet with weight calculation

      Again I see no issues with using the Tool.

      4) have a sheet withcalculation of the price (weight times cost parametes)
      I currently have my code like this:

      The way I see the VBA Multithreading Tool supporting your project is by splitting the first For Loop (For i = 1 to 100) by threads. Each thread will compute a separate portion of the i parameter. After the computations have finished (feel free to use the parallelClass.AsyncThreadJoin procedure) you can either save your results to the master Workbook or save each threads computations in a binary file as shown here and then load them into the master workbook to validate the results of the aggregated computions.

Leave a Reply