VBA Mini-Series – Multi-Threading and Parallel Processing

Introduction

When I was but a toddler – young, impressionable, and back then filled with less existential dread – my father once told me “son, parallel processing in VBA, it’s the next big thing”. It’s probable I dreamed this. Who knows, perhaps I even made it up. But it stuck with me.

Well, fast forward a few decades, and here I am clumsily writing a VBA mini-series, washed up by article 4 and already writing away from actual VBA, because this article isn’t really about VBA. It’s about introducing some more advanced concepts that other languages get for free, and diving into what it would take to give VBA the opportunity to use those concepts too.

One of the demands currently on insurance companies is a pressure to adapt to bigger datasets. A lot of models are being migrated away from Excel to open-source languages (such as R recently, and Python more recently) which are just better options for modelling. But Excel is, broadly, the most comfortable piece of software used in our industry. You’d need to travel long and far to find an actuarial student who hasn’t at least used it briefly. The transition away from its warm embrace has to be for proper benefits, right? Languages like R and Python are capable of processing at a much faster speed than Excel, and one of the reasons for this is their ability to use multi-threading and parallel processing.

In this article, we take a fairly light-touch look at these concepts. We’ll see what they are in general and look at how they might apply in the context of R – one of our key actuarial languages. We’ll then see how well it fits VBA (spoiler alert: it doesn’t). If you’re interested, read on!

A quick look at CPUs/GPUs

It will help motivate the rest of this article if we consider the make-up of a computer. A computer has a Core Processing Unit (CPU), which acts as its brain. It processes tasks that we ask of it, whatever their complexity – from opening a folder to running the latest video game. A CPU is traditionally made up of some number of cores (for example, the machine I’m working on has four), each one increasing the CPU’s ability to multi-task and handle complex workloads effectively.

To illustrate this, imagine you own a kitchen. There are four chefs, with each chef representing a core. Here are some features of how they might work:

1. Independent tasks: each chef can prepare a different dish at the same time. For example, one chef makes a salad, one chef bakes a cake, one prepares a fish dish and another grills meat. This represents multi-tasking, or handling multiple simultaneous tasks.
2. Collaborative tasks: instead of each chef preparing a dish of their own, perhaps there is an order for the same dish several times. For example, they all focus on making a huge cake. One prepares frosting and decorative items, one prepares the sponge batter, one prepares the garnish and the other prepares a jam filling. This is parallel processing, the division of one big task among the cores simultaneously.
3. Efficiency: with either of the above, the kitchen can prepare a lot more food using all four chefs than using one chef doing it all themselves. Similarly, a four-core CPU can (all else being equal) process multiple instructions faster than a single-core CPU.

Now, each core has a number of threads in it. In our kitchen example, let’s now imagine that this kitchen is on Mars. The Martian chefs each have multiple hands – certainly more hands than we have here on Earth. Each hand of a chef now represents each thread of a core:

1. One chef, one thread: a single chef uses one hand to chop some vegetables. This is simple enough – one core running one thread.
2. One chef, multiple threads: the same chef has finished chopping vegetables, and uses one hand to taste soup, one hand to season it, and another hand to stir a pot that might boil over. This is like a core running multiple threads – it’s still one chef, but it splits its attention to work on multiple small tasks that are largely related.
3. Multiple chefs, multiple threads: one chef makes a salad, using one hand to prepare vinaigrette, another to chop vegetables, and one more to toss the salad bowl. Other chefs do similar with their own dishes, splitting their preparation into smaller related tasks and using one hand for each.

Threads allow the CPU to maximise its efficiency by juggling multiple tasks at the same time. Even with only four chefs (cores), the kitchen can handle many more tasks by splitting the workload out between hands (threads). Many threads make light work as they say!

These threads, as you might have guessed, give their name to multi-threading, where the task that is the sole responsibility of the core gets split into multiple smaller sub-tasks that each of the threads can handle. There is some level of concurrency going on here, because threads won’t just stand idly by waiting for a pot of water to boil.

Incidentally, it’s a worthwhile moment to segue into GPUs. Graphical Processing Units (GPUs) are another part of a computer’s architecture. They are specialised processors, originally designed to accelerate rendering for graphics and images. The CPU is not suited to this role, primarily because images/graphics contain thousands of pixels that need to be loaded up – usually in a short span of time – to display an image to the user. CPUs are designed for general-purpose computing and handling a broad range of tasks.

Before GPUs became central to AI and high-performance computing, they were primarily used for:

• Rendering 3D graphics in video games (which is why gaming laptops typically require very strong specifications).
• Image and video processing, such as Adobe Photoshop or movie editing software.
• CAD and engineering simulations.

While CPUs possess a handful of cores, designed to sequentially but efficiently handle a few general tasks, GPUs are designed with hundreds to thousands of smaller cores, each equipped with the ability to work on a different task at the same time. These cores are not as powerful as CPU cores, but the large number of them makes the rendering of thousands of pixels at once possible. This means that, while CPUs are optimised for the purpose of sequential processing, GPUs are optimized for parallel processing – the handling of thousands of small tasks at once.

This optimisation has some unexpected benefits. High-end CPUs are a suitable choice for logic-heavy tasks, like running an operating system (OS) or apps, where the majority of programming is sequential and relies on resolving flow. However, GPUs are the better choice for repetitive/iterative calculations (such as matrix algebra). It just so happens that the mathematics underlying neural networks is relatively repetitive, and relies on the performance of billions of matrix multiplications which are highly parallelisable. This is why GPUs have become the workhorse of AI models in recent times, explaining the spike in share price of Nvidia, who readers of my generation may remember primarily from shopping for a laptop that could play open-world games.

Extending our laboured kitchen metaphor, suppose now that there are two types of kitchen:

• A fine dining kitchen (CPU):
  • Uses a few highly-skilled chefs (CPU cores).
  • Each chef can perform multiple tasks at once (using multiple threads).
  •  Chefs focus on preparing complex, gourmet dishes that required detailed sequential instructions.
• A fast food kitchen (GPU):
  • Uses hundreds of line cooks (GPU cores) who aren’t as talented at cooking.
  • Each cook does a small, simple task, such as flipping a burger or pouring a drink.
  • Instead of working on a single, complex dish that requires many individually executed elements, they mass-produce food in parallel.

AI requires the production of thousands of identical sandwiches, e.g. when processing images or training a neural network. The fine dining CPU kitchen would struggle, because it only has a few chefs, and they would have to make each sandwich one at a time. The fast food kitchen could utilise its hundreds of line cooks to assemble 1000 sandwiches in very little time.

Because GPUs can do so much, they’re capable of working with large datasets simultaneously. While a CPU may take weeks or months to train an AI, a GPU could do it in days or hours.

What does this have to do with multi-threading and parallel processing?

Well, multi-threading is now essentially what it says on the tin. It’s the ability for a core to split a task into smaller steps that each of its threads can take on. Its purpose is to improve computational efficiency. Multi-threading simulates concurrency without true parallelism: these threads are doing different tasks, but they share the same resources (memory, cache, etc). They execute tasks independently, but they still share CPU time. This means that only one thread runs at a time per core, but the CPU optimises itself to rapidly switch between them.

This has the effect of making it appear as if multiple tasks are running simultaneously, but in reality they are rapidly being progressed sequentially by the processor. A web browser is an example of this. When loading a webpage, a browser might have separate threads handling rendering, network requests and user input. Because browsers rely on return signals from the network, some threads might be awaiting a response while others can continue running.

On the other hand, parallel processing is true simultaneous execution. It occurs when multiple tasks actually execute at the same time, by using multiple CPU cores or even multiple processors. Each core has its own allocation of resources, and works on a task independently. In practice, of course, most modern processors have multiple cores (although not thousands), so are capable of both multi-threading and parallel processing to an extent. This means that the two terms are largely used interchangeably, and the difference between them is largely academic.

From the perspective of programming, unless we are actually looking to train a neural network using millions of rows of data, the focus will be “can it split tasks” rather than the nature of that split. Therefore, for the rest of this article, we will no longer treat the two as separate concepts and instead focus on the practical applications.

Multi-threading in R

R is one of the most used actuarial programming languages, so it is a worthwhile comparison point for actuaries looking to implement multi-threading in VBA. The first thing to know is that, by default, R is single-threaded. Therefore, to achieve some level of multi-threading, R has packages which allow for this functionality. Examples of these packages are:

• future: The future package provides some support for multi-threading by allowing tasks to run in the background asynchronously (i.e. without blocking the main R session).

In the example above (run in RStudio’s console), the first two lines set up the asynchronous session. The call to the future package sets the value of x to be 42 AFTER waiting two seconds. Then, while this two seconds goes on, the next line gets run, before the value of x is set up. Without the asynchronous call, line 3 would take two seconds, then line 4 and 5 would run after. In my example, line 4 ran immediately after line 3, then two seconds elapsed, then line 5 ran.

Doing this enables users to make calls which require a response (for example, loading a large dataset from a database) and to perform further tasks while waiting for this load to finish. In this case, the time saved is the time spent running any intermediate steps during the load, as the alternative would be for them to happen before or after the load time rather than during.

• parallel: the parallel package is included in base R and allows you to run calculations on multiple cores simultaneously. It provides key functions such as parLapply() which are set up to split the work between multiple cores.

In this case, we set up all bar one core to split the calculation of working out the square number of 1:10. This is, in practice, not even a slightly difficult piece of code to run on one core, so the time savings are trivial (and the work to set up the cores may even mean it takes MORE time). But amend that 1:10 to 1:10000, or even more, and it’s clear to see how much runtime can be saved for the larger calculations. In my case, the saving was approximately 78% in time for 1:10000.

The use of parallel is best for computationally expensive tasks, such as Monte Carlo simulations or ML model training.

• foreach and DoParallel: these are further libraries (we will not illustrate with examples below) that can help an R programmer parallelise their code. The design of foreach is to replace for loops in R code with foreach() and see automatic parallelisation. DoParallel provides a %dopar% operator that forces operations to parallelise.

How about in VBA?

This is where we get to the crux of the matter. We’ve now seen what multi-threading is and what it looks like in a commonly used actuarial programming language like R. But this mini-series is about VBA, and the reality is that sometimes you may have a very good reason why something should be done in Excel and VBA.

To cut to the chase, there is no native multi-threading functionality in VBA. VBA is built as a single-thread language, and none of the base VBA syntax allows the user to split out their work among multiple cores/threads. There is SOME example of asynchronicity in native VBA, particularly:

• The vbModeless setting on userforms: this setting allows other code to run in the background while userforms are live.
• The DoEvents command: this line forces VBA to cede control to the OS to execute any events in the events queue. These generally include Excel calculation updates, which gives rise to the typical use cases of live updating a chart or forcing a screen refresh. The former would normally wait until the macro has stopped executing before forcing a refresh, and the former refreshes at small iterations, but at large iterations opts not to (this is why, for anyone familiar with ScreenUpdating, when running a large number of iterations, you will sometimes see Excel switch to Not Responding) – in this instance, DoEvents forces the screen to update regularly.

Both of these act as examples of specifically built functionalities within the Excel/VBA ecosystem that ‘fudge’ asynchronicity a little bit to allow code execution to be conducted somewhere else. But they are not examples of multi-threading.

In VBA, there are some workarounds that can be used to make multi-threading work, but they vary in complexity, and none of them are considered ‘typical’ VBA. There will always be situations where a user feels it is necessary to leverage multi-threading / parallel processing, but also needs to be tied into running their code using VBA. The three examples below can help with that, but it is recommended that, if possible, the user should just switch to a language that is compatible with multi-threading.

Example 1 – Instances of Excel

The first, and most obvious way, to make multi-threading work in VBA is to split your code over multiple instances of Excel. Most people reading should be familiar with opening a new instance of Excel (right click the task icon to bring up a menu, then Alt+click on the menu). New instances of Excel use a different core to the original, meaning that it’s possible to run VBA code from both instances in parallel.

This means it’s possible to split the processing of, say, 10,000 rows of data into 2 sets of 5,000. Since the two instances can run in parallel, it takes the same time as running 5,000 rows instead of 10,000. The difficulty for this workaround is that setting up a second instance to run the second 5,000 rows is time-consuming. For other tasks which need to be parallelized, it’s not possible to have anything in one instance which depends on results from another because there is no shared memory/cache between cores. This limits the usefulness significantly.

Practically speaking, it’s possible to use a separate Excel controller workbook to automate the creation of additional instances and partition of work. For example, a function which takes a StartRow and EndRow input which loads and analyses data from StartRow to EndRow can be the basis of the partition, with the creation of a new Excel.Application object setting up the instance.

Just note there is a limit to the number of instances Excel can handle, and there comes a point where the additional processor strain causes runtime degradation.

Example 2 – Windows API Threading

For those comfortable and familiar with using Windows API functions (essentially a suite of functions that Windows includes with its OS that can be used by VBA), there is an API function from the kernel32 library called CreateThread. This function launches a new execution thread outside of VBA’s single-threaded ecosystem, and you can feed it an argument that tells it which function you want to run multi-threaded.

This is a nifty solution in the sense that you get the additional functionality of base VBA and there is no need for awkward wrangling of functions to make the split work (as in Example 1). However, Windows API functions always come with a warning, because they are well-known for being volatile. Files containing them (that actually run them) have been known to lose data and cause memory leaks. It’s a risk-reward balance.

Example 3 – Use Another Language

While VBA lacks a lot of multi-threading functionality itself, it’s always possible to port your code into another language which DOES support multi-threading and call it using VBA. The Wscript.Shell object allows you to run any executable script (essentially via the command line). Therefore, it’s possible to borrow the power of multi-threading (via R or Python, for example) by calling the command prompt to run any scripts. The obvious objection to this is that it’s barely VBA at all:

This piece of code can run a simple R script, but that’s the limit of VBA’s involvement in completing the task. The actual code to perform whatever task you want, which may include multi-threading, is in Rscript.exe. This solution simply doesn’t meet the brief of keeping all code self-contained within VBA.

Example 4 – Use Another Language (but different!)

As a final note, VBA is now very much an outdated language. After VBA7, which was last updated 15 years ago, Microsoft continued to develop the language in the form of VB.NET when it released the .NET Framework, a development platform that is maintained by Microsoft. This platform supports functionality far beyond what typical VBA could do, including app development, cloud-based web services, AI and Machine Learning.

The key point here is that VBA has some natural synergies with .NET languages because of its move to .NET as Visual Basic. Languages under the Microsoft .NET umbrella can be packaged into libraries of code, much like R or Python might have packages installed. These are called COM Add-Ins in Office terms (Component Object Model, COM, is Microsoft’s technology that allows different applications to communicate and use each other’s functionality). Using COM Add-Ins, a user can choose a Microsoft .NET language, build a library of functionality (known as a Dynamic Link Library or DLL), and attach it to be called from a VBA environment as a COM Add-In. Imagine it like packages in R – you give VBA a link and install the add-in. Then you can use the functions from it, with one crucial advantage: you get the speed and functionality of the add-in language rather than VBA.

In the case of VBA, a suitable choice of language is C#, which supports multi-threading, in particular with the Parallel class. By using this class in building your functionality, you can create hyper-efficient code that runs orders of magnitude faster than typical VBA.

The downside is obvious: complexity – very few developers understand well how COM Add-Ins work, even fewer in our industry have working C# experience, and, even if both of the above downsides can be mitigated, the reality is that the VBA model loses a lot of portability when it depends on external libraries.

Summary

The message isn’t a particularly optimistic one – VBA is capable of mimicking or borrowing some of the aspects of multi-threading languages, but it’s not always simple and usually requires an understanding of fairly advanced VBA. The below table showcases the methods we identified, together with some pros and cons:

Overall, hopefully this article gives you a sense of the theoretical foundation behind multi-threading. We have learned that it’s very possible in R, and, even though it wasn’t covered here, Python is no exception. For VBA, though, even the best methods are a ‘fudge’ of true functionality, because VBA is built to be single-threaded. There are a number of valid workarounds, but they all require a good amount of confidence in your VBA code, as well as in theoretical underpinnings.

In most situations, I would recommend just choosing a more compatible language. After all, only the most inconvenient runtimes need that multi-threading functionality to speed up appropriately. However, if you cannot part from VBA (I know the feeling), and need your code sped up, it’s possible to wrangle a few tricks that could be exponentially powerful!

John Nicholls

February 2025