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.

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