Tag Archives: multithreading

Native VBA Multithreading

Native VBA Multithreading? There is only 1 but…

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

First I am sorry for making another post on VBA multihreading. Having written so many posts already it seems I am ranting on an on about this topic. I must subdue, however, and write shortly on one new way of almost Native Multithreading VBA using native kernel32 functions. If you’ve been around my blog you are probably aware of my posts on VBA Multithreading like Multithreading VBA – VBA vs. VBscript vs. C#.NET and heard of the VBA Multithreading Tool which allows you to multithread your Excel VBA projects. Today, however, I wanted to share another alternative to the previous multithreading options I presented previously.

By importing functions from the kernel32 library you can achieve almost seamless VBA multithreading… although with limited stability.
For more reliable multithreading in VBA look at my VBA Multithreading Tool.

Creating threads this way is much easier as there is no need of creating separate Excel processes or using VBscript. There is, as you might expect however, one serious downfall… As according to my experience you can only create 1 additional stable thread this way. That gives you 2 threads in total if we count the native Excel VBA running process. Creating more threads will, most probably sooner or later, cause Excel to crash for no good reason. This method is worth knowing to easily accelerate 2-fold your VBA code. Although, I do hope that Microsoft will some day fix these issues or introduce native VBA multithreading.

The CreateThread VBA example

So let’s jump right into the source code and demonstrate with a simple example. First I will start with the imports from the kernel32 library.

'Import declarations from kernel32
Private Const INFINITE = &HFFFFFFFF
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function CreateThread Lib "kernel32" (ByVal LpThreadAttributes As Long, _
    ByVal DwStackSize As Long, _
    ByVal LpStartAddress As Long, _
    ByVal LpParameter As Long, _
    ByVal dwCreationFlags As Long, _
    ByRef LpThreadld As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal HANDLE As Long) As Long

Now let’s demonstrate a simple thread procedure and run it concurrently within the native VBA Excel process.

'Example thread
Public Sub Thread()
   Debug.Print "Hello there I am a separate Thread!"
End Sub

'Starting point
Public Sub Main()
    Dim threadId As Long
    threadId = CreateThread(nil, 0, AddressOf Thread, nil, 0, nil)
    Debug.Print "Hello I am the main thread!"
    Call WaitForSingleObject(threadId, INFINITE)
    CloseHandle threadId
End Sub

The good news is that the additional thread can read and write to shared VBA global variables. The bad news is that you can’t write to the Worksheets as this will violate the main process and cause Excel to crash (as many other things). Also as mentioned several times above only roughly 1 additional thread will prove stable enough. Any additional thread create will sooner than later cause Excel to die on you.

Conclusions

Simple enough right? I must confess that at first I had my hopes really high when discovering this approach. I though it was only a matter of working out via trial and error how to use this approach to work with the additional threads in a stable manner. It turned out however that there is a limit that you simply can’t work around. As mentioned above I noticed that:

  • Provided you wait and close the thread, 1 additional thread should be fairly stable to prevent Excel from freezing and/or crashing.
  • For me this changes thing only slightly as the VBA Multithreading Tool has only a setback of a couple of seconds to create the additional Excel processes.
  • There is no real sense of benchmarking this example against the previous options for multithreading as this approach is only stable enough to create 1 additional thread and secondly we will see only a couple of seconds in favor of the currenly presented approach when comparing 2 threads.

If you are happy with 2 threads in Excel VBA go for the source code from the example above. Otherwise, stick to the VBA Multithreading Tool and look out for any future post from me ;).

multithreaded IE automation

Multithreaded browser automation (VBA Web Scraping)

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

Web Scraping is very useful for getting the information you need directly off websites. Sometimes however simple browser automation is not enough in terms of performance. Having created both the IE and Parallel classes I decided to jump an opportunity of creating a simple example of how mulithreaded browser automation can be achieved. Daniel Ferry achieved the same here. However, he used VBscript and a lot of coding was required to manage the swarm, as he called it. What I wanted to show you is rather an example how you can combine the Parallel class and the IE class to achieve the same functionality but in a much more concise and easy to manage way. So let’s jump straight in.

What does it do?

The browser automation procedure queries the Google page and copies the first resulting link text to the Excel workbook. In the mulithreaded example a “swarm” of 4 threads (or more if needed) is maintained to carry out simultaneous Google queries. This way the overall execution time is significantly reduced as more IE browser objects are created when some of them are waiting for a callback.

A multithreaded browser automation example
A multithreaded browser automation example

Multithreaded browser automation: Video

Instead of going into the details I encourage you to watch this short video example of a single and mulithreaded IE automation example:

Download

Feel free to download the workbook here:

Next steps

Check out the deterministic IE automation class here:
EXCEL: Simple class for using IE automation in VBA

Check out the Parallel class mulithreading tool here:
EXCEL: VBA Multithreading Tool

vba multithreading tool

Excel VBA Multithreading Tool

1 Star2 Stars3 Stars4 Stars5 Stars (12 votes, average: 4.42 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

Native VBA Multithreading

Multithreading VBA – VBA worker threads via VBscript

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

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
       Next
    Next
    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
        DoEvents
        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
    Loop
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

Performance

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

Download

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

Native VBA Multithreading

Multithreading VBA – VBA vs. VBscript vs. C#.NET

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


I you were looking for an end-to-end analysis of the topic of multithreading VBA. You have come to the right place! So lately I have been really wearing out the topic of multithreading in Excel in VBA. Hopefully you have already read through my three posts on the various methods of achieving multithreading capabilities in VBA:

In all three posts I showed that, although “native” VBA does not feature multithreading, you can in fact achieve multithreading by using either external COM/dlls (e.g. in C#.NET), running VBscripts via the Windows Shell command or run additional VBA worker threads via VBscript. All three approaches have their ups and downs. Now just to clarify: What do I mean by “native”? I mean that VBA as a programming language does not natively support creating new threads, joining threads etc.

Pro’s and Con’s of all multithreading VBA approaches

Let us summarize them in the table below:

Multithreading approach Pro’s Con’s
Using a C#.NET COM/dll
  • Best performance – even singlethreaded C#.NET COM/dlls will significantly reduce the execution time of your algorithms
  • Easy parallelism – C# provides the “Parallel” class which allows you to easily create and manage threads and run for loop in multiple threads
  • Requires learning C#
  • Requires referencing an external dll – must make sure it is not lost when sharing the Excel file
  • Requires a separate programming environment (e.g. Visual Studio) to create, modify and compile
VBscript worker threads
  • Fairly easy to implement
  • VBscript is almost identical with VBA language
  • Worst performance – as VBscript is much slower than native VBA in most cases multithreading your algorithms using multiple VBscript worker threads will extend execution time instead of reducing it. I would recommend using VBscript worker threads only if the threads are to leverage other system libraries/resources
VBA worker threads (via VBscript)
  • Good performance – you should see a proportional reduction of execution time, at some point, when using this approach (2 cores ~= 2x faster)
  • Most complicated – VBA worker threads run via VBscript
  • More room for errors
  • Overhead – multithreading makes sense when VBA algorithm takes at least 5 sec or more depending on the amount of cores/processors available. This is due to the overhead resulting from invoking separate VBA worker threads in separate Excel processes via VBscript

Performance: Native VBA vs. VBA workers vs. VBscript workers vs. C#.NET

I would like to warn you that these are only example metrics run on a specific environment (on a 2-core 4 threaded Intel processor (i5-4300U)). I didn’t focus too much of my time on drafting very precise metrics as these statistics are simply meant to give you the general idea of the relative performance of all approaches.

Comparison of different approaches to multithreading VBA
Comparison of different approaches to multithreading VBA

As you can see the VBscript parallelism approach provides very lousy performance and it gets worse the more operations the algorithm has. I know what you are thinking now: well then want is the sense of using VBscript multithreading if the performance is awful in the provided example? Well there are examples when VBscript threading makes sense see Daniel’s example here. In most common examples you should, however, disregard the VBscript approach therefore I removed it from the chart and focused on the remaining methods:

Comparison of different approaches to multithreading VBA (excluding VBscript worker threads)
Comparison of different approaches to multithreading VBA (excluding VBscript worker threads)

As expected C#.NET rules the stats! While it took at least 26 sec to compute 900mln operations in “native” VBA, C#.NET managed to do this in less than 2 sec. On the other hand the VBA parallel workers managed to reduce the computation time to about 10 sec which is less than half the “native” VBA execution time. Still not bad – would have been much better if my CPU had more cores. Notice that the break-even point between the “native” VBA algorithm and the VBA parallel workers methods was at about 100mln operations – taking approx. 3 seconds. It seems therefore that there is a couple of seconds of overhead in case of the latter approach.

Source code and example

If you want to download the whole source code containing all these example approaches see the link at the bottom of this post. To gather these performance statistics I made a single Excel workbook that allows you to test all multithreading VBA approaches and verify which is the best fit for your algorithm / use case.

Screenshot from the Excel Workbook consolidating the findings (download link at the bottom)
Screenshot from the Excel Workbook consolidating the findings (download link at the bottom)

VBA – No multithreading

Below you can find the a “native” VBA example of a simple division algorithm. This is the baseline for the other algorithms.

VBA – no multithreading (the baseline)

Sub VBASinglethread()
    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 = 1 To divTabSize
       For j = 1 To divTabSize
          x = CDbl(i) / j
       Next
    Next
    endTimeS = Timer
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Range("J2").Value = elapsed
    DoEvents
End Sub

 

C#.NET- Single and multithreaded

Below you can find 2 example algorithms in C#.NET. One is singlethreaded and the other one leverages the C# Parallel class to easily achieve multithreading.

C#.NET COM/dll code

using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
namespace TestLib
{
    [ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
    public class TestClass
    {
        
        [return: MarshalAs(UnmanagedType.SafeArray)]
        public int ParallelMethod(int divTabSize)
        {   
            Parallel.For(0, divTabSize, i =>
            {
                double x;
                for (int j = 1; j < divTabSize; j++)
                {
                    x = i / j;   
                }
            });
            return 0;
        }

        [return: MarshalAs(UnmanagedType.SafeArray)]
        public int SequentialMethod(int divTabSize)
        {
            double x;
            for (int i = 1; i < divTabSize; i++)
            {
                for (int j = 1; j < divTabSize; j++)
                {
                    x = i / j;
                }
            }
            return 0;
        }
    }

    static class UnmanagedExports
    {
        [DllExport]
        [return: MarshalAs(UnmanagedType.IDispatch)]
        static Object CreateTestClass()
        {
            return new TestClass();
        }
    }
}

 

C#.NET COM/dll code – VBA example

Declare Function CreateTestClass Lib "C:Users[PATH TO DLL]TestLib.dll" () As Object
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub CshartSequential()
    Dim startTimeS As Date, endTimeS As Date
    startTimeS = Timer
    Dim testClass As Object
    Set testClass = CreateTestClass()
    Call testClass.SequentialMethod(divTabSize)
    endTimeS = Timer
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Range("K2").Value = elapsed
    DoEvents
End Sub
Sub CshartParallel()
    Dim startTimeS As Date, endTimeS As Date
    startTimeS = Timer
    Dim testClass As Object
    Set testClass = CreateTestClass()
    Call testClass.ParallelMethod(divTabSize)
    endTimeS = Timer
    elapsed = "" & Format(endTimeS - startTimeS, "0.00")
    Range("L2").Value = elapsed
    DoEvents
End Sub

 

VBscript multithreading

Below the source code for multithreading using VBscript worker threads. Interesting but usually of little use due to terrible performance. See Daniel Ferry’s good example of when it is worth leveraging.

VBscript worker threads

Sub VBscriptParallel(maxThreads As Long)
    Dim thread As Long, threads As Long
    For threads = 1 To maxThreads
        ClearThreadTime
        'A simple division algorithm (a multiplication table would quickly cause overflow)
        startTime = Timer
        For thread = 1 To threads
            CreateVBScriptThread thread, threads, divTabSize
        Next thread
        CheckIfFinished threads
    Next threads
End Sub
Sub CreateVBScriptThread(threadNr As Long, maxThreads As Long, divTabSize As Long)
    Dim s As String, sFileName As String, wsh As Object
    '---Copy parameters to VBscript---
    s = s & "dim i, j, oXL, x, startTime, endTime" & vbCrLf
    s = s & "startTime = Timer" & vbCrLf
    '---Compute partition of the division table---
    s = s & "For i = " & CInt(CDbl(divTabSize) * (threadNr - 1) / maxThreads + 1) & " To " & CInt(CDbl(divTabSize) * threadNr / maxThreads) & vbCrLf
    s = s & "   For j = 1 to " & divTabSize & vbCrLf
    s = s & "      x = CDbl(i) / j" & vbCrLf
    s = s & "   Next" & vbCrLf
    s = s & "Next" & vbCrLf
    '---Save the threadNr back to your Excel file! - this is the incredible part really
    s = s & "Set oXL = GetObject(, ""Excel.Application"")" & vbCrLf
    s = s & "endTime = Timer" & vbCrLf
    s = s & "oXL.workbooks(""" & ThisWorkbook.Name & """).sheets(""Status"").Range(""" & "A" & (threadNr + 1) & """) = (endTime - startTime)"
    '---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
'---------Verify if finished----------
Sub CheckIfFinished(maxThreads As Long)
    Dim endTime As Double, elapsed As String
    Do Until False
        DoEvents
        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("G1").Offset(maxThreads).Value = maxThreads
                Range("H1").Offset(maxThreads).Value = elapsed
                Exit Sub
            End If
        End If
        Sleep 50
    Loop
End Sub

 

VBA multithreading (via VBscript)

Below the code to a good alternative to the C#.NET COM/dll approach. It uses VBscripts just for creating separate VBA worker threads. This approach can provide satisfactory results although not as incredible as C#.NET.

VBA worker threads (via VBscript)

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

 

Summary

Hopefully I have exhausted the topic of multithreading in VBA. I am certain there are other methods of multithreading in VBA although I would not think that it would be worth taking them into consideration. Now for the ultimate conclusions. You can leverage several approaches to VBA multithreading although each has it’s drawback and advantages. I would not eliminate any of the presented approaches as I think this depends on the algorithm you are designing / programming. I personally would almost always go with C#.NET as I am comfortable with C# and when making complex algorithms it is probably better to create them from the very beginning in a language designed to support multithreading. I understand, however, that some of my readers would prefer staying with VBA and for those I would recommend the VBA worker threads – a little more complex but still very efficient.

What do you think? What are your experiences? Did you ever had to consider multithreading in VBA?

Download

Here you can download the whole project source codes and the complete set of files:

Next steps

Check out the VBA Mulithreading Tool (the easiest way to add multithreading to Excel macro):
EXCEL: VBA Multithreading Tool
Check out the VBA Mulithreading Add-In to add multithreading to all Excel Workbooks:
EXCEL: VBA Multithreading AddIn

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