Simulating Multithreading in VBA using Excel

This article describes 3 ways of simulating multithreading in VBA using Excel.

One feature that I sorely miss in the VBA’s integrated development environment (IDE) is the ability to do multithreading. I think part of that comes from having worked in my previous avatar with Java and C++.

In the past few days I’ve been thinking about ways in which one can ‘simulate’ multithreading in excel. (If you noticed, I said ‘simulate’ – you can’t actually multithread with vba – not atleast in any way that I know of). Multithreading technically would refer to the parallel processing of two functions independent of each other at the same time. Having said that it can’t be done, one can explore ways in which we can come close to the illusion.

Before you begin, you may want to download this example on simulating multithreading in vba using excel.

The Do….Loop

The first one, and the simplest by far, is to use a master function and let it call two independent functions one after another. If both the functions do not take too much of a time to execute, the user may feel that both of them are executing in parallel.

The code for achieving this would look something like this:

If (condition1 = TRUE) then Call Function 1
If (condition2 = TRUE) then Call Function 2
(…check for some condition that causes the function to Exit Sub …)
(Example 1 in the workbook)

However, if used like this, the loop runs ‘blind’… does not check if any other element is trying to signal it to stop. A way out is to simply insert the DoEvents function in the loop. DoEvents allows the function to yield control to the operating system so that it can check if anything else needs to be done before it resume further executing of the code.

(Tip: Oftentimes you may encounter a situation where the excel hangs while the code is executing. It the code takes more than a few seconds to fully execute, the user has to sit like a dummy looking at the screen! Using the DoEvents function can do your vba code a world of good, though it will speed thing down a bit since code execution would resume only after the operating system has checked if it there are no other important matters waiting to be completed.)

Using Worksheet_Change and Workbook_SheetChange functions in tandem

Both the functions pretty much do the same thing….they execute the moment a cell value changes on a sheet. While the first ones works whenever anything changes in a particular sheet, the second one executes wherever any cell anywhere in the workbook changes value. So if you change a cell value in a sheet, if both the function are activated, both will fire. Importantly, you don’t need to worry about calling the functions inside a loop, they are invoked automatically the moment a cell is updated.

(There are other functions like Activate, SelectionChange, Calculate etc. that can also be used in tandem and are common to both the worksheet and the workbook objects.)

The code for achieving this would look something like this:

If (condition1 = TRUE) then Do Something
(…check for some condition that causes the function to Exit Sub…)
If (condition2 = TRUE) then Do Something else
(…check for some condition that causes the function to Exit Sub …)

Using OLE Objects in VBA

Using the vba OLE Objects to make changes to the worksheet which the user navigates
This method would use a vba object (Image, Label) to check for a user input and process a piece of code. I sometimes tend to use mouse move (See my previous post: How This Bear Market Compares) to execute and display information while the user is browsing through an image or a chart. The implementation is pretty simple.

The code for achieving this would look something like this:

On Image_MouseMove
Do something()
Do somethingelse()
Exit Sub

Now one may argue that is as far away from multithreading as a Horse is from a Dodo :-) but it does add a fancy little touch without the user having to click on something and wait for the code to execute.

All said and done, the above approaches are just us trying to simulate multithreading… practice it can’t be done, not as of now. And though they may have their own shortcomings, they present ways of adding interactivity and real-time responsiveness to your excel sheets.

You can download a sample worksheet containing examples of simulating multithreading in vba using Excel here or click on the button below:


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Daniel Ferry wrote:

    I have written many custom website scrapers for clients in Excel.

    There is no doubt that Excel VBA is single threaded. To improve the performance of my scrapers I developed a technique that resulted in a 300x performance increase.

    After analyzing a particular website, I would code the custom scraping algorithm in VBScript. In meta programming fashion, I would then write the VBA in Excel that when executed would create that same VBScript and write it out to a .vbs file within the Excel workbook’s folder. But the key was that it would write out 300 individual .vbs files. Each of these files were identical except that a unique range address was embedded within each file for the return of the “payload” from the site being scraped. When each “agent” in this swarm was instantiated by the VBA, it would run in the Windows Scripting Host since it was VBScript and the first thing it did was to retrieve an OLE handle to the workbook that created it; it then navigated to the website using MSXML2, scraped the website data record it was assigned, independently used its OLE connection to write the data to it’s assigned range in the workbook, and then finally killed itself.

    Excel would sense that that particular payload had arrived and would then create a new file to replace that particular agent and start the cycle again.

    In this fashion a swarm of 300 agents would work in concert to scrape website records and return them in parallel to one Excel workbook. Surprisingly Excel is not working very hard during this process, as the agents are doing the grunt work.

    Scraping websites is an ideal task to use multi-threaded operations on, because most of the time involved is in waiting for the website server to respond and send the data. When Excel VBA is doing the scraping directly (instead of the swarm), it can only do a couple at a time and it puts an enormous hit on Excel. It cannot do anything useful while waiting, and Excel will max out your CPU in the Windows Task Manager. With the swarm, Excel is very nimble during the scraping operation and the scraping time drops from hours to minutes for thousands of records. And most importantly, the CPU usage drops to a very low percentage.

    This technique is certainly multi-processing, but it is not VBA doing the multi-work. It is a clever exploit of the multi-threaded Windows Scripting Host and the multi-threaded OLE subsystem of Windows. However, the results are spectacular.

    I would be happy to share, if you have any interest.

  2. Davide wrote:

    Hi Daniel,
    I’ve read you comment and i’m very interested to know better this solution. How could you share it with me?

  3. Frankie Heng wrote:

    Daniel: Mighty impressive stuff! That’s a really clever way of overcoming the limitations of Excel VBA. I’d love to know how you establish an OLE handle to the creator workbook and how do you notify Excel that a particular subtask is completed.

    I’ve been trying to exploit all cores in my quad-core CPU with Excel; I think with your technique, I might be able to create some speed.

    Thanks for sharing.

  4. Daniel Ferry wrote:

    @Davide – Ajay asked me to write a guest post about my technique. You can read more about it and download a sample workbook immediately above this comment.

  5. Daniel Ferry wrote:

    @Davide – I meant at the bottom of this page…

  6. Daniel Ferry wrote:

    @Frankie – It’s actually very easy to establish an OLE connection to an open workbook from another application. First, set an object to the Excel Application like this:

    Set oXL = GetObject(, “Excel.Application”)

    Then just reference the workbook like this:

    oXL.workbooks(wNAME).sheets(sNAME).Range(rNAME) = vResults

    You can now download an example workbook demonstrating the VBScript Swarm technique by following the red link at the bottom of this page to a guest post I wrote for Ajay.

  1. Multithreaded VBA - An Approach To Processing Using VBScript | Excel & VBA - da Tab Is On wrote:

    […] developer and consultant (and a newbie blogger). A few months ago, I wrote a post on how one can simulate multithreading in VBA using Excel. Daniel picked up a thread from there and commented that he had some good success in […]


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel