Multi-threading in VBA

Can't be done natively with VBA. VBA is built in a single-threaded apartment. The only way to get multiple threads is to build a DLL in something other than VBA that has a COM interface and call it from VBA.

INFO: Descriptions and Workings of OLE Threading Models

As you probably learned VBA does not natively support multithreading but. There are 3 methods to achieve multithreading:

1. COM/dlls - e.g. C# and the Parallel class to run in separate threads
2. Using VBscript worker threads - run your VBA code in separate VBscript threads
3. Using VBA worker threads executed e.g. via VBscript - copy the Excel workbook and run your macro in parallel.

I compared all thread approaches here: http://analystcave.com/excel-multithreading-vba-vs-vbscript-vs-c-net/

Considering approach #3 I also made a VBA Multithreading Tool that allows you to easily add multithreading to VBA: http://analystcave.com/excel-vba-multithreading-tool/

See the examples below:

Multithreading a For Loop

Sub RunForVBA(workbookName As String, seqFrom As Long, seqTo As Long)    For i = seqFrom To seqTo        x = seqFrom / seqTo    Next iEnd SubSub RunForVBAMultiThread()    Dim parallelClass As Parallel     Set parallelClass = New Parallel     parallelClass.SetThreads 4     Call parallelClass.ParallelFor("RunForVBA", 1, 1000) End Sub

Run an Excel macro asynchronously

Sub RunAsyncVBA(workbookName As String, seqFrom As Long, seqTo As Long)    For i = seqFrom To seqTo        x = seqFrom / seqTo    Next iEnd SubSub RunForVBAAndWait()    Dim parallelClass As Parallel    Set parallelClass  = New Parallel    Call parallelClass.ParallelAsyncInvoke("RunAsyncVBA", ActiveWorkbook.Name, 1, 1000)     'Do other operations here    '....    parallelClass.AsyncThreadJoin End Sub

I was looking for something similar and the official answer is no. However, I was able to find an interesting concept by Daniel at ExcelHero.com.

Basically, you need to create worker vbscripts to execute the various things you want and have it report back to excel. For what I am doing, retrieving HTML data from various website, it works great!

Take a look:

http://www.excelhero.com/blog/2010/05/multi-threaded-vba.html