Multi-threading in VBA

Question

Does anybody here know how to get VBA to run multiple threads? I am using Excel.

Answer 1

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

Answer 2

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 i End Sub  Sub 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 i End Sub  Sub 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