vba multithreading tool

Excel VBA Multithreading Tool

The VBA Multithreading Tool, from AnalystCave.com, is a free and open source tool (VBA) for enhancing your VBA Project with Mulithreading capabilities. It was designed to make VBA multithreading quick an easy and to minimize the complexity of your VBA code. VBA is natively singlethreaded hence will utilize only a single logical processor core. Why waist your processors underutilized capacity and significantly speed up VBA code execution time?
vba multithreading tool
Until now I have posted quite a few posts on the topic of multithreading in VBA. Let’s admit – there is no such thing as “native” VBA Multithreading. This is the closest I got to “native” VBA Multithreading. However, I considered one of other of my similar posts on this topic as a good enough alternative to achieving multithreading to VBA – by using VBscript and VBA worker threads.

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 Tool based on the concepts of the C# Parallel Class.

Example 1: VBA 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 Tool 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 Tool 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?

Multithreading For loops

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

See also  VBA MsgBox vs InputBox vs UserForm

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?

Async procedure invoke

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 Tool: 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 Tool will become a useful for you. The Tool makes multithreading almost just as easy as in any other languages that allow for multithreading natively.

Download

You can download the Tool (complete source code) here:
https://analystcave.com/product/excel-vba-multithreading-tool/
If you prefer to download this as an add-in (the warning won’t appear) please follow this link: here.

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. Hi
    I had a similar issue. I started several processes – instances of Excel. Each of the processes processed its own part of the data and placed the results in the “parent” instance. The parent stream could be edited. If I entered the formula editing mode in one of the cells, similar errors occurred.
    As a variant, I would recommend inserting a processing call in the Worksheet_Change procedure through a call to the Application.OnTime method. It looks like calling events in the Worksheet is blocking other processes. Therefore, it is better to do processing not in the event thread, and then call the procedure on a timer

  2. Thanks so much for this info and the download.
    I have cut my processing time of wave audio files analysis using 4 threads by best 25%. Note in this application best to sort the wave files by size largest first.

    • Glad to hear this helped you! I know 25% might not seem like a lot – so I would suggest consider you migrate your VBA code to a VB.NET application and apply multithreading there. You will definitely see a much higher performance boost!

  3. I carry out a real task of assessing the investment potential of 53 regions in 12 streams. Uses 80% of CPU time and 13 GB of RAM. I turn off antivirus and OneDrive. AMD 5800H, 16 Gb RAM.

  4. how many downloads so far and what is success ratio?

    I will download and try the tool for my processing intensive vba code.

  5. Hello Tom,

    Can I get the source code of the multi tread tool. On the link you specify there is no download link (anymore)

  6. Hi ! i have to make a for next …

    2 to 5000..

    im trying to insert your code in my project..

    where do i have to write… for i = 2 to 5000 ???
    since you replace my
    for i = 2 to 5000 row
    with…

    For i = seqFrom To seqTo
    x = seqFrom / seqTo

    ty
    ps

  7. Hi Tom,
    Great work! Thank you for that jewel!

    I was wondering, why did you have to include that Do Until … Loop in VBScript file worker file?
    In my extensive testing, I never experienced that semaphore(s) didn’t change to 1.
    Was it some kind of safeguard in the case of older (i.e. slower) computers?

    I tinkered a bit around your code and polished it to suit my needs 🙂
    And, I noticed that ParallelFor() was not slicing the For Loop range correctly.
    Your starting range in sequence n+1 is the same as the ending range in the previous sequence.
    It should be +1 greater.

    So, here is my contribution. Please check it out.

    seqFrom + CLng(CDbl(seqTo – seqFrom + 1) * (thread – 1) / NoOfThreads), _
    seqFrom + CLng(CDbl(seqTo – seqFrom + 1) * thread / NoOfThreads) – 1)

    Kind regards,
    Igor

  8. Good day, when I go to the download page for the multithreading tool, I don’t see any links to get the code…
    I cannot download the .xla add-in for security reasons.

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.