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
I am adding this answer since programmers coming to VBA from more modern languages and searching Stack Overflow for multithreading in VBA might be unaware of a couple of native VBA approaches which sometimes help to compensate for VBA's lack of true multithreading.
If the motivation of multithreading is to have a more responsive UI that doesn't hang when long-running code is executing, VBA does have a couple of low-tech solutions that often work in practice:
1) Userforms can be made to display modelessly - which allows the user to interact with Excel while the form is open. This can be specified at runtime by setting the Userform's ShowModal property to false or can be done dynamically as the from loads by putting the line
UserForm1.Show vbModeless
in the user form's initialize event.
2) The DoEvents statement. This causes VBA to cede control to the OS to execute any events in the events queue - including events generated by Excel. A typical use-case is updating a chart while code is executing. Without DoEvents the chart won't be repainted until after the macro is run, but with Doevents you can create animated charts. A variation of this idea is the common trick of creating a progress meter. In a loop which is to execute 10,000,000 times (and controlled by the loop index i ) you can have a section of code like:
If i Mod 10000 = 0 Then
UpdateProgressBar(i) 'code to update progress bar display
DoEvents
End If
None of this is multithreading -- but it might be an adequate kludge in some cases.
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
As you probably learned VBA does not natively support multithreading but. There are 3 methods to achieve multithreading:
- COM/dlls - e.g. C# and the Parallel class to run in separate threads
- Using VBscript worker threads - run your VBA code in separate VBscript threads
- 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