Excel VBA Multithreading AddIn

This AddIn is an alternative to the VBA Multithreading Tool.Go check out the the tool if you prefer not to install an Add-In.The VBA Multithreading AddIn allows you to multithread any VBA macro.

Having compared at least 3 methods for achieving multithreading in VBA (EXCEL: Multithreading VBA – VBA vs. VBscript vs. C#.NET) I came to the conclusion that the VBA worker threads can be a decent alternative to C#.NET COM/dlls. Having said that, I decided that it might make sense to make it easier to leverage these multithreading capabilities in VBA. That’s why I created the VBA Multithreading AddIn based on the concepts of the C# Parallel Class.

Example 1: Multithreading For loops

For loops are very common cases for multithreading – as, usually, the result of each iteration is independent to the remaining. I would like to show you how you can use the VBA Multithreading AddIn to run a loop in separate parallel threads. Let’s say we have a simple procedure as shown below. This procedure simply divides two numbers in a loop. Each iteration is independent of the other and there is no order in which we need to calculate each iteration. Notice the arguments of this procedure, as they will be required for multithreading later on.

Sub RunForVBA(workbookName As String, seqFrom As Long, seqTo As Long)
    For i = seqFrom To seqTo
        x = seqFrom / seqTo
    Next i
End Sub

We can now execute this code like this in a standard single threaded VBA manner:

Sub RunForVBASingleThread()
    RunForVBA ActiveWorkbook.Name, 1, 1000
End Sub

Now let’s use the VBA Multithreading VBA AddIn to do the same thing within 4 separate threads.

Sub RunForVBAMultiThread()
    'Declare the Parallel class object
    Dim parallelClass As Parallel 
    'Initialize the Parallel class object
    Set parallelClass = New_Parallel 
    'Declare how many threads are to be created
    parallelClass.SetThreads 4 
    'Call 'Run the for loop in 4 threads synchronously
    Call parallelClass.ParallelFor("RunForVBA", 1, 1000) 
End Sub

4 lines of code to make this procedure multithreaded :). Easy right? As I mentioned earlier the ‘RunForVBA’ procedure has to have the 3 arguments for the ‘Parallel’ class to be able to run it in separate threads.

How does Multithreading For loops work?


The ParallelFor procedure creates as many separate Excel threads as required and blocks the Master Excel Workbook’s execution until all threads have finished and closed. Each thread is in fact a copy of the Master Excel Workbook therefore all VBA function or procedures which you will be multithreading will have access to exactly the same data as the Master Excel Workbook.

VBA Multithreading AddIn: For loop multithreading procedures

Considering For loops the following funtions and procedures are available:

  • SetThreads(threads As Long) – set the amount of parallel threads to be used
  • GetThreads() – get the amount of parallel threads to be used
  • ParallelFor(macroName As String, seqFrom As Long, seqTo As Long) – execute the macro macroName over a sequence of seqFrom to seqTo. The macro macroName should have the following arguments (workbookName As String, seqFrom As Long, seqTo As Long)

Example 2: Asynchronously invoking VBA procedures

VBA synchronously blocks the execution of any called functions or procedures (except for COM calls – but they are not native VBA). This is because it runs in a single thread. By leveraging the VBA Multithreading AddIn we can however asynchronously call functions and procedures to run them in the background while we proceed with the execution of other macro code. When we are ready to wait for the async background thread to finish with can call AsyncThreadJoin to Join macro execution on this thread. Alternatively we can also call as many asynchronous threads as we like and not bother waiting for them.

To exemplify this functionality let’s again consider a similar procedure as before. This time the arguments are not important – although it is usually recommended to pass the master Excel Workbook name to an async thread if we want to somehow save the outputs of the threads to the master Excel Workbook.

Sub RunAsyncVBA(workbookName As String, seqFrom As Long, seqTo As Long)
    For i = seqFrom To seqTo
        x = seqFrom / seqTo
    Next i
End Sub

But now instead of running it in parallel threads let’s invoke it asynchronously and continue executing the macro. We will wait for the procedure to end by the end of the procedure.

Sub RunForVBAAndWait()
    Dim parallelClass As Parallel
    Set parallelClass  = New Parallel
    'The line below will not block macro execution
    Call parallelClass.ParallelAsyncInvoke("RunAsyncVBA", 1, 1000) 
    'Do other operations here
    '....
    if parallelClass.IsAsyncRunning then ... 'Check if Async thread is still running
    '....
    'Now let's wait until the thread has finished
    parallelClass.AsyncThreadJoin 
End Sub

How does asynchronous VBA procedure invoking work?


The ParallelAsyncInvoke procedure is non-blocking therefore allows you to execute other macros within the Master Excel Workbook and choose whether you want to, as some point, wait for it to finish execution or not.

VBA Multithreading AddIn: Asynchronous VBA invoking procedures

The following funtions and procedures are available for async invoking:

  • ParallelAsyncInvoke(macroName As String, Optional arg1, … [up to arg 6]) – execute asynchronously the macro macroName with the following arguments arg1, arg2 etc.
  • AsyncThreadJoin – wait for the VBA worker thread to finish

Example 3: Saving data to the master Excel Workbook

All examples above assumed that the code was executed in separate threads without any means of the threads’ outputs being saved to the master Excel Workbook. You have probably noticed that this is actually crucial as otherwise you will not benefit from calculations happening in the void without being able to access their results. I would like to show you how easily you can save your results from the separate threads to the master Excel Workbook while you are keeping the workbook open and even making changes to it!

Again let’s consider the similar procedure as shown above. This time, however, notice the changes:

Sub RunForVBA(workbookName As String, seqFrom As Long, seqTo As Long)
    For i = seqFrom To seqTo
        x = seqFrom / seqTo
    Next i
    'Save the last division operation 'x' to cell 'A1' on worksheet 'Sheet1' in the master Excel Workbook.
    ParallelMethods.SetRangeToMaster(workbookName, "Sheet1","A1", x)  
    'Do the same but this time the procedure will reflect the changes made to the thread workbook
    'automatically to the master Excel Workbook based on the provided Range
    Dim tempRange as Range
    Set tempRange = Range("A1")
    tempRange.Value = x
    ParallelMethods.SaveRangeToMaster(workbookName,  tempRange)  
End Sub

Again quite simple. The threads will save their outputs immediately to the Excel master Workbook – as long as it is open and in use.

Summary

I hope the VBA Multithreading AddIn will become a useful tool for you. The Addin makes multithreading almost just as easy as in any other languages that allow for multithreading natively.

Download

You can download the AddIn (complete source code) here:
Download

Installation

Follow the general instructions here: Installing AddIns in Excel

Once completed be sure to initalize the Parallel VBA Class like this:
Create the Parallel class e.g.

Set parallelClass = New_Parallel

That’s it!

Next steps

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

14 Comments

  1. I have install xla in excel.But I don’t know how to use it.
    Following is my excel commandbutton1_click code.
    Could you give me some suggestion?
    Private Sub CommandButton1_Click()

    Set sh_sheet1 = ActiveWorkbook.Worksheets(3)
    Set sh_team = ActiveWorkbook.Worksheets(4)
    Set sh_PO = ActiveWorkbook.Worksheets(5)
    Set sh_DPRLINE = ActiveWorkbook.Worksheets(6)
    Set sh_POLINE = ActiveWorkbook.Worksheets(7)

    Set sh_sheet1 = ActiveWorkbook.Worksheets(3)
    Set sh_team = ActiveWorkbook.Worksheets(4)
    Set sh_PO = ActiveWorkbook.Worksheets(5)
    Set sh_DPRLINE = ActiveWorkbook.Worksheets(6)
    Set sh_POLINE = ActiveWorkbook.Worksheets(7)

    For i = 2 To sh_sheet1.UsedRange.Rows.Count

    x_hetongliushuihao = sh_sheet1.Cells(i, 2).Value
    x_wuzi = sh_sheet1.Cells(i, 5).Value
    x_bumen = sh_sheet1.Cells(i, 6).Value
    x_chejian = sh_sheet1.Cells(i, 7).Value
    x_gongban = sh_sheet1.Cells(i, 8).Value
    x_shuliang = sh_sheet1.Cells(i, 9).Value

    For j = 2 To sh_PO.UsedRange.Rows.Count
    If sh_PO.Cells(j, 82).Value = x_hetongliushuihao Then
    y_PONUM = sh_PO.Cells(j, 1).Value
    Exit For
    End If
    Next

    For k = 2 To sh_POLINE.UsedRange.Rows.Count

    If sh_POLINE.Cells(k, 1).Value = y_PONUM And sh_POLINE.Cells(k, 2).Value = x_wuzi Then
    POLINEID = sh_POLINE.Cells(k, 58).Value
    Exit For
    End If
    Next

    For l = 2 To sh_DPRLINE.UsedRange.Rows.Count
    If sh_DPRLINE.Cells(l, 32).Value = POLINEID Then

    If sh_DPRLINE.Cells(l, 28).Value = x_bumen And sh_DPRLINE.Cells(l, 29).Value = x_chejian And sh_DPRLINE.Cells(l, 30).Value = x_gongban Then
    sh_sheet1.Cells(i, 10).Value = "Pass"
    Exit For
    Else
    sh_sheet1.Cells(i, 10).Value = "Fail"

    End If

    End If
    Next

    Next
    End Sub

  2. Thanks !!  Used the tool version (not the add-in) and it greatly speeds up my VBA macro.

    Basically my macro generates millions of random numbers… to help me pick lottery numbers haha.  I have managed by using your multithreading tool to reduce each run from 30 seconds to about 5 seconds.

    The interesting thing is that even though my CPU has 64 logical cores (Ryzen ThreadRipper 3970x), it never seems to fully utilize anything beyond 4 cores.  And I get best results by setting the number of threads to 4.  Actually from 3 to 5 or 6 it doesn’t make a big difference, and it slows down again beyond 8 threads or so.

    I also tried setting it to 32 and results were disastrous.  Again only about 3 or 4 cores get close to 100% utilization but that’s it.

    Cheers and thanks again for this helpful tool.

    • I know the reply is old. But this is a really interesting insight. I think this is due to cost of initialization – the creation of separate Excel processes, each with its own memory. Then the joining needs to happen when all the processes finish. Unfortunately this approach is not lightweight.

  3. By the way, the ParallelMethods.GetForThreadNr function turned out to be essential for my macro. Without it I’d be overwriting everything on the main sheet from these spawned threads.

    Interestingly this function isn’t documented on this page at all. I happened to stmble on it while rewriting my macro but it turned out to be immensely useful.

  4. Hi, recently downloaded the VBA Multithreading addin, trying to understand the test methods, and running into Runtime error, which leads to bugs (i think) in the ForThread & AsyncThread methods, where the vbs script is attempting to be opened but has not yet been saved. Am I missing something here?
    Sample code line:
    ‘Save the VBscript file
    sFileName = ActiveWorkbook.Path & “\” & parallelKey & “_” & thread & “.vbs”
    Open sFileName For Output As #1

  5. Hi Tom! I tried VBA Multithreading tool (just tryed to run built-in example macro) but there was an error appeared with the file “7055.vbs”:
    Windows Script Host “the call was rejected 80010001”
    What am i doing wrong?

  6. VBA Multithreading tool download link is absent – only description there.
    Tried the AddIn, linked the addin, set the reference in the vba editor.

    I can see the add-in xla open.
    Dim parallelClass As Parallel – passes
    Set parallelClass = New Parallel – Fails.

    The error tells “Compile error: invalid use of New keyword”.

    Why is that?
    Office 2019, windows 11 x64.

  7. For the following line of code, I receive a compile error: “Expected: =”. What is this line supposed to be set equal to?

    Also, it seems like ParallelMethods.GetForThreadNr can be used to obtain information from one of the threads, but I’m not skilled enough to know how to use this call. Can someone please provide a simple example of how to extract data from each thread and place it into the master workbook?

    • The line of code referred to in the first paragraph of my original question is:
      ParallelMethods.SetRangeToMaster(workbookName, “Sheet1”, “A1”, x)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.