Multithreading in VBA Excel [duplicate]

Question

How can I write code in VBA to get multi-threaded parsing?

I've looked at this tutorial, but it is not working.

I have 10000 sites, each site in one row in column A. I need at least 10 concurrent threads to parse info between tags <div></div>, take tag <a> with rel="external" from index.php on each site, then save the results to each row in column B.

Answer 1

You can use multithreading in VBA but NOT natively. There are however several possibilities to achieve multithreading in VBA:

1. C#.NET COM/dlls - create a COM/dll in C#.NET which allows you to freely create threads and reference it from VBA like other external libraries. See my post on this: here. See also this Stackoverflow post on referencing C# methods from within VBA: Using a C# dll inside EXCEL VBA
2. VBscript worker threads - partition your algorithm into as many VBscripts as you need threads and execute them from VBA. The VBscripts can be created automatically via VBA. See my post on this: here
3. VBA worker threads - copy your Excel workbook as many times as you need threads and execute them via VBscript from VBA. The VBscripts can be created automatically via VBA. See my post on this: here

I analyzed all these approaches and made a comparison of the pro's and con's and some performance metrics. You can find the whole post here:

http://analystcave.com/excel-multithreading-vba-vs-vbscript-vs-c-net/

Answer 2

As @Siddharth Rout points out in his comment, the answer is no. But to expand on this a little, even methods that would seem to run in the background and enable multi-threading like abilities do not allow multithreading.

A great example of this is Application.OnTime. It allows a procedure to be run at a point in the future.

This method allows the user to continue editing the workbook until the preset amount of time has elapsed and the procedure is called. At first glance, it might seem possible that clever use of this would enable multiple code fragments to run simultaneously. Consider the following fragment:

For a = 1 To 500000000
Next a

The For...Next loop on my machine takes about 5 seconds to complete. Now consider this:

Application.OnTime Now + TimeValue("00:00:1"), "ztest2"
For a = 1 To 500000000
Next a

This calls "ztest2" one second after the Application.OnTime statement is read. It's conceivable that, since the For...Next loop takes 5 seconds and .OnTime will execute after 1 second, perhaps "ztest2" will be called in the midst of the For...Next loop, i.e., psuedo-multithreading.

Well, this does not happen. As running the above code will show, Application.OnTime must wait patiently until the For...Next loop is done.