Multi-threading in VBA

Question

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

User · Answer

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

User · Answer

speed up thread      dim lpThreadId as long      dim test as long      dim ptrt as long  initparams      ptrt varptr lpThreadId       Add   CODEPTR thread   opensocket 191 9 202 255  change depending on configuration      numSock   Sock Connect  quot 191 9 202 255 quot   1958       port recieving      numSock1 sock open 5963   create thread      hThread  CreateThread  byval 0 amp  byval 16384  Add   byval 0 amp   ByVal 1958  ptrt        edit3 text str  hThread      use  Declare Function CreateThread Lib  quot kernel32 quot  Alias  quot CreateThread quot   lpThreadAttributes As long  ByVal dwStackSize As Long  lpStartAddress As Long  lpParameter As long  ByVal dwCreationFlags As Long  lpThreadId As Long  As Long

User · Answer

I know the question specifies Excel  but since the same question for Access got marked as duplicate  so I will post my answer here  The principle is simple  open a new Access application  then open a form with a timer inside that application  send the function sub you want to execute to that form  execute the task if the timer hits  and quit the application once execution has finished  This allows the VBA to work with tables and queries from your database  Note  it will throw errors if you ve exclusively locked the database   This is all VBA  as opposed to other answers   The function that runs a sub function asynchronously  Public Sub RunFunctionAsync FunctionName As String      Dim A As Access Application     Set A   New Access Application     A OpenCurrentDatabase Application CurrentProject FullName     A DoCmd OpenForm  MultithreadingEngine      With A Forms  MultiThreadingEngine            TimerInterval   10          AddToTaskCollection  FunctionName      End With End Sub   The module of the form required to achieve this    form name   MultiThreadingEngine  doesn t have any controls or properties set   Public TaskCollection As Collection  Public Sub AddToTaskCollection str As String      If TaskCollection Is Nothing Then         Set TaskCollection   New Collection     End If     TaskCollection Add str End Sub Private Sub Form Timer       If Not TaskCollection Is Nothing Then         If TaskCollection Count  lt  gt  0 Then             Dim CollectionItem As Variant             For Each CollectionItem In TaskCollection                 Run CollectionItem             Next CollectionItem         End If     End If     Application Quit End Sub   Implementing support for parameters should be easy enough  returning values is difficult  however

User · Answer

Sub MultiProcessing Principle       Dim k As Long  j As Long     k   Environ  NUMBER OF PROCESSORS       For j   1 To k         Shellm  msaccess    C  Autoexec mdb      Next     DoCmd Quit End Sub  Private Sub Shellm a As String  b As String    Shell modificirani     Const sn As String            Const r As String               Shell sn  amp  a  amp  r  amp  b  amp  sn  vbMinimizedNoFocus End Sub

User · Answer

As said before  VBA does not support Multithreading   But you don t need to use C  or vbScript to start other VBA worker threads   I use VBA to create VBA worker threads   First copy the makro workbook for every thread you want to start   Then you can start new Excel Instances  running in another Thread  simply by creating an instance of Excel Application  to avoid errors i have to set the new application to visible    To actually run some task in another thread i can then start a makro in the other application with parameters form the master workbook   To return to the master workbook thread without waiting i simply use Application OnTime in the worker thread  where i need it    As semaphore i simply use a collection that is shared with all threads  For callbacks pass the master workbook to the worker thread  There the runMakroInOtherInstance Function can be reused to start a callback    Create new thread and return reference to workbook of worker thread Public Function openNewInstance ByVal fileName As String  Optional ByVal openVisible As Boolean   True  As Workbook     Dim newApp As New Excel Application     ThisWorkbook SaveCopyAs ThisWorkbook Path  amp       amp  fileName     If openVisible Then newApp Visible   True     Set openNewInstance   newApp Workbooks Open ThisWorkbook Path  amp       amp  fileName  False  False   End Function   Start macro in other instance and wait for return  OnTime used in target macro  Public Sub runMakroInOtherInstance ByRef otherWkb As Workbook  ByVal strMakro As String  ParamArray var   As Variant      Dim makroName As String     makroName        amp  otherWkb Name  amp        amp  strMakro     Select Case UBound var          Case -1              otherWkb Application Run makroName         Case 0              otherWkb Application Run makroName  var 0          Case 1              otherWkb Application Run makroName  var 0   var 1          Case 2              otherWkb Application Run makroName  var 0   var 1   var 2          Case 3              otherWkb Application Run makroName  var 0   var 1   var 2   var 3          Case 4              otherWkb Application Run makroName  var 0   var 1   var 2   var 3   var 4          Case 5              otherWkb Application Run makroName  var 0   var 1   var 2   var 3   var 4   var 5      End Select End Sub  Public Sub SYNCH OR WAIT       On Error Resume Next     While masterBlocked Count  gt  0         DoEvents     Wend     masterBlocked Add  BLOCKED   ThisWorkbook FullName End Sub  Public Sub SYNCH RELEASE       On Error Resume Next     masterBlocked Remove ThisWorkbook FullName End Sub  Sub runTaskParallel               Dim controllerWkb As Workbook     Set controllerWkb   openNewInstance  controller xlsm        runMakroInOtherInstance controllerWkb   CONTROLLER LIST FILES   ThisWorkbook  rootFold  masterBlocked         End Sub

User · Answer

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

User · Answer

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

User · Answer

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

[multithreading] Multi-threading in VBA

Examples related to multithreading

Examples related to vba

Examples related to excel