vba multithreading tool

Excel VBA Multithreading Tool

1 Star2 Stars3 Stars4 Stars5 Stars (10 votes, average: 4.40 out of 5)
Loading...

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
Mulithreading VBA: Mulithread Excel running in parallel

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

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


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

Related Posts

30 thoughts on “Excel VBA Multithreading Tool”

    1. @Skevenger
      If you want your own loop procedure then:

      Sub RunForVBA(workbookName As String, seqFrom As Long, seqTo As Long)
      For i = seqFrom To seqTo
      'Your code here
      Next i
      End Sub

      If you want to make an async thread procedure then:

      Sub RunAsyncVBA(workbookName As String, ...) ' Your params instead of the '...'
      'Your code here
      End Sub

    1. Sure. What the Tool does is it duplicates your current Excel file (as many as threads needed) and executes a certain VBA procedure with the required arguments. The Threads have all the data as the master Workbook so you don’t need to pass Worksheet data to the Threads. In case you want to pass VBA data you can pass it simply as an argument like below:

      ‘arg1-3 are custom arguments. You can add additional arguments
      Sub RunAsyncVBA(workbookName As String, arg1 As Long, arg2 as String, arr as Long)
      ‘Do something
      End Sub

      Sub RunForVBAAndWait()
      Dim parallelClass As Parallel: Set parallelClass = New Parallel
      Dim arr(1 to 3) as Long
      arr(1) = 1: arr(2)=10: arr(3) = 20
      Call parallelClass.ParallelAsyncInvoke("RunAsyncVBA", 132, "Hello there", arr)
      parallelClass.AsyncThreadJoin
      End Sub

      Now in case you want to share data live between the threads, you might need to use the GetObject function to save data to other thread worksheets or dump binary data to a file and then read it by another thread.

      1. Hi ! Very nice tool !

        So how can I share a variable (in my case, dictionary type) which shall be independently updated at each loop ?

        Thanks again for your work.

        jb.

        1. Hi jb,

          not sure, but I don’t think you can share a VBA variable directly between different Workbooks as they are within different Excel processes (normally you can if they are executed within the same process – see here. Try asking on StackOverflow. I would be much interested what the community has to say about this.

          My current idea would simply be:

          1. To consolidate your results after the various processes finish their computations. You would need to save your Dictionary, from each process, into a separate binary file (see my post here). Then the master process would need to download all the Dictionaries from their respective binary files and consolidate them into one final Dictionary.

          2. Save your results ad-hoc to the Master Workbook into the cell(s) of a Worksheet as an encoded string. You would need however to encode your Dictionary to a string and then parse it in the working process as shown below. This would however impact performance so would need to be done rarely.

          Dim arr(1 to 3) as Long, encodedArr as String
          arr(1) = 1: arr(2) = 5: arr(3) = 10
          for each lval in arr
          encodedArr = encodedArr & lval & ";"
          next lval
          'Save the encoded array
          masterWb.Sheet1.Cells(1,1) = encodedArr

          'Decode/parse the array
          arr = Split(encodedArr,";")

  1. Hi, i found this tool extremely usable in may projects! Congrats for your effort on building such a great enhancement.

    I would like to ask you something: i have a group of already built functions, and i would just like to be able to launch them in parallen from different buttons in the front end. So if you see i’m not requiring precisely a swarm of threads running in parallel the same operation over and over, but i definitely would need multi threads to execute these functions in parallel. So is it possible with your tool? thanks in advance for your help!!

      1. Ok based on your response and the one to @skevenger, i have two questios?

        if i need to execute several macros each one in an asynchronous thread, i must do it with:

        Sub RunAsyncVBA(workbookName As String, …) ‘ Your params instead of the ‘…’
        ‘Your code here
        End Sub

        or can i use my own function?

        the next question will be, can i instead of invoking the Master file, invoke an additional file , lets say B and execute a macro from that B file?

        thanks in advance

        1. “or can i use my own function?” – you can change the name of this procedure although its first argument has to be “workbookName As String” as you will need to know the name of the Masterworkbook thread. To make matters simple just assume the macro “RunAsyncVBA” will run normally as any other singlethreaded macro. It can invoke additional files, create new workbooks in the children threads.

          What the VBA Multithreading Tool does is replicate the current workbook and Excel process and call the “RunAsyncVBA” macro in each process separately.

          1. “Alternatively we can also call as many asynchronous threads as we like and not bother waiting for them.” : I tried this but the copies of the excel file and also the vbs script files remain in memory for each thread that i create and if i ommit the AsyncThreadJoin function , they remain in the same location than the original file. So then how do i effectively run several threads without actually care about them anymore??

          2. I tried this but the copies of the excel file and also the vbs script files remain in memory for each thread that i create and if i ommit the AsyncThreadJoin function

            Indeed AsyncThreadJoin is required to wait on the Threads and to do the necessary cleaning of the Thread files. It is to be expected that if you don’t call this procedure these files will remain. However, this will not cause any issues as the Thread files will be overwritten each time.

            So then how do i effectively run several threads without actually care about them anymore??

            You just can. As mentioned above if you don’t want to run AsyncThreadJoin the other thread files will simply remain where they are. If this bothers you then here are some ideas to consider:

            • Include in the Thread procedure an AutoDestruct procedures – which will e.g. run a vbscript which will wait until the Workbook is closed and delete it
            • Instead of saving the Thread files in the current directory, save them to the Temporary folder. See an explanation here. This folder gets cleaned every now and then
  2. Hi, this multithreading tool appears to be exactly what I want. I have a huge for…next loop in VBA performing calculations which I need to speed up. However, I can’t seem to get your examples running. I keep getting a “Windows Script Host” message saying there is an Unknown runtime error at line 1 Char 203 in file 7055_2.vbs.

    I am running 64bit Excel. Can you help? Thanks in advance.

  3. hi

    “invalid use of New keyword”

    the error appears when I want to make in another workbook than the vbamultithreading project although the examples are within the modules of vbamultithreading project.
    (I have made a reference for multithreading in my workbook.)

    what should I do?
    regards

    1. That’s probably the issue with the Parallel class being Private (not accessible from other workbooks). Just go and download the Multithreading AddIn instead and follow the instructions.

      Be aware that this will not work in the AddIn (when creating the class from an external workbook):
      Dim pa as Parallel
      Set pa = New Parallel

      But this will:
      Dim pa as Parallel
      Set pa = New_Parallel

  4. this tool is exactly was a was looking for to Speed up a macro we have created that really takes a long time to parse a lot of data within a for time. Unfortunately I am not able to run your code because I always get an error in the function that creates the semaphores (Error 1004: Invalid Name exactly on the Call instruction). If Change the Name or Definition I get Error 1004: Application-defined or Object-defined error. I have no idea why… It doesn’t matter if I use your sample code or integrate your code into my macro… always the same result. Any help would be welcomed.

    Private Sub CreateSemaphores()
    Dim i As Long
    For i = 1 To cThreads
    Call ActiveWorkbook.Names.Add(“S” & parallelKey & “_” & i, “=0”) ‘<– Fails here
    Next i
    End Sub

    Thanks in advance

    1. After some Research I made it work. Something seems to be not allowed with the Names of the threads. I just changed the start character “S” to “SM” in all the code and works. Code:

      old –> Call ActiveWorkbook.Names.Add(“S” & parallelKey & “_” & i, “=0”)
      new –> Call ActiveWorkbook.Names.Add(“SM” & parallelKey & “_” & i, “=0”)

      I don’t know why, but now I get no more Errors. BTW, i am using Excel 2007 32 bit

      1. Hi Alberto,
        glad it worked and appreciate the feedback. I will definitely have a look into why the error pop-ed up. Expect updates next week!

        Btw. not sure you are aware of my new Tool for speeding up Excel macros the VBA Compiler. It compiles VBA functions (as long as they are compatible with VB.NET) to VB.NET DLLs. Usually you can expect about 10-20x performance improvement.

        1. Thank you very much for your reply. Actually was only related with the Name, I don’t know if this is only an Excel 2007 Problem but after applying the changes everything works. Now I am experiencing some strange timming behaviour… Maybe someone here already knows about it or has an idea about where the origin can be. I integrated your code into my macro and i see in the Task Manager that 4 books are created and that the processor reaches 25% for each one. The first thread delivers ist result in about 8 minutes (what is great because the old macro took 30min) but then the second one delivers the result 7 minutes later, the third one 6 minutes after the second and the last thread again 6 minutes after the third one. In the end the whole macro takes the same time or more than the original Excel single thread code. It Looks like if the threads were executing after each other (taking into account the Timing) but when I look to the Task Manager it really seems that they are running in parallel. I am probably doing something wrong but no idea what…

  5. I have a spreadsheet in which I am creating an asynchronous thread as described in your article however, when I use the SaveRangeToMaster routine to store the calculated range it gives an “subscript out of range error” because the “masterWorkbookName” parameter is empty, which is because the “workbookName” parameter passed to the subroutine called by ParallelAsyncInvoke (using the macroname and two parameters) is also blank. What is the correct way of using this so the work book name is passed in correctly, as neither example shows the work book name being passed in explicitly. Seems like I am missing something here…

  6. Hi Robert,
    sure – please set up a suitable thread on my Forum. I don’t see why SaveRangeToMaster is not working for you so please also attach your Workbook or at least your VBA code

  7. Hi

    I keep getting the error “Cannot run the macro ‘7055_1.xls!RunTestCases’. The macro may not be available in this workbook or all macros may be disabled. ”

    All macros have been enabled in the macro settings and Trust access to the VBA project object model is checked. And it still shows the same error.

  8. Hi

    Thanks for the useful tool.

    However, I think there is a possible problem inside the Function ParallelFor ()

    For thread = 1 To cThreads
    ForThread macroName, thread, _
    seqFrom + CLng(CDbl(seqTo – seqFrom) * (thread – 1) / cThreads), _
    seqFrom + CLng(CDbl(seqTo – seqFrom) * thread / cThreads)
    Next thread

    This part is better to be changed to the following.

    For thread = 1 To cThreads
    If thread <> 1 Then
    ForThread macroName, thread, _
    seqFrom + CLng(CDbl(seqTo – seqFrom) * (thread – 1) / cThreads) + 1, _
    seqFrom + CLng(CDbl(seqTo – seqFrom) * thread / cThreads)
    Else
    ForThread macroName, thread, _
    seqFrom, _
    seqFrom + CLng(CDbl(seqTo – seqFrom) * thread / cThreads)
    End If
    Next thread

     

Leave a Reply