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