Multi-threading in VBA

Question

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

Antinucleon

Most approaches outlined here are technically multiprocessing, not multithreading. – Foxing

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

Sessler

@blog.tkacprow.pl - None of those methods are natively VBA. They all rely on something outside of VBA. – Sessler

I agree. They are not "native" VBA approaches. There are, however, two other methods that do not require resorting to COM/DLL. Both involve only VBscript and VBA - both languages are almost identical. 1 involves almost only VBA (with VBscript only to create VBA threads). – Bettis

@blog.tkacprow.pl - You may not have to resort to writing your own DLL but you do have to use COM as that is the only means (well, COM and Win32 API) by which VBScript and VBA can communicate with objects outside themselves. Anytime you see GetObject or CreateObject, you are using COM. The best you can achieve is using a solution that is effectively multi-process and thereby achieve multiple threads. – Sessler

Yes, you are right in this respect :-). Thanks for noticing. Either way there are still alternatives to dll - that is the only thing i wanted to stress – Bettis

Using a single-threaded apartment does NOT mean that you can only have one thread, as the answer implies. See the document linked in the answer. The apartment model affects how a thread uses memory. The fact that VBA does not support multi-threading is unrelated to its apartment model. – Eshman

@SamPorch - If we're being pedantic, STA and MTA have nothing to do with how memory is used. Apartments are logical constructs for thinking about concurrency (see the link in the answer). It isn't possible in VBA to spin up another thread that does work because it will synchronize all calls to a single thread. So, yes, under the hood, in the VBA process, there are multiple threads at work. However, for VBA developer, they are hamstrung in terms of spinning up their own threads to do work parallel because of the STA. – Sessler

As a side note, declaring CreateThread and calling it like CreateThread 0, 0, AddressOf ThreadProc, 0, 0, 0 will cause Excel to crash, if I remember correctly. However, I tested this on PowerPoint one day, Excel is likely to behave so. – Cystic

If SO had some kind of messaging system, I'd use it for this purpose. My question is related to this discussion and I'm hopeful that someone here will possess the required knowledge to get past this particular stumbling block. Thanks! – Diegodiehard

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

Bettis

Is the 1 and 1000 mandatory in my case i have a range of large cells to be process how dose it fit with this multithreading please? – Varus

The 1 and the 1000 are simply parameter of the example RunForVBA function. A ParallelAsync thread need not have these parameters. You can just as well keep only the workbookName param. Download the tool and look at the examples inside: analystcave.com/excel-vba-multithreading-tool – Bettis

I took a good look at i think i have to customize ParallelForopen sourced by you right – Varus

Is there a faster way to pass the results back from the "threads" to the master code, other than writing on a worksheet? I need to receive arrays of data from multiple websocket streams with the least possible delay for a trading bot – Japanese

The "tool" is absent, like there was never any. No sign of download URL. The add-in does not work. Simply, cannot be initialized when calling "NEW". Probably, a hoax. Tried in Office 2019 – Judicative

Answer 3

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

Evaginate

You can have a hundred VBScript programs fetching data from the web, if VBA is single-threaded, it can only handle one result at a time. – Aubigny

@Aubigny But if the script is doing all the work, and the returning of the result takes minimal processing, then this is still effective in reducing processing time (though yes, it is not true threading). You are queuing a number of very small actions instead of very large actions. – Yecies

@Yecies I agree that there are ways to make certain activities faster. But VBA is not multi-threaded. Period. – Aubigny

To do XMLHttp scraping in VBA, I recommend reading Tushar Mehta's excellent article on the topic. Having used this method for many years now, I can attest that it is both fast and reliable. If you didn't know it, you would think that it's multi-threaded since the requests are being handled asynchronously. Example: youtu.be/Z0n7Wd7WiIE – Kielty

Answer 4

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.

Dorman

Thanks. Unfortunately, this doesn't really address the original ask, which had to do with multi-threading from VBA. Using these methods may help keep the GUI from locking up, but ultimately increases run-time, which is why many programmers prefer to run certain iterative tasks on multiple-threads where technically feasible. – Filomenafiloplume

I agree -- I just thought that it couldn't hurt to point out a couple of pure VBA kludges that sometimes help – Dorman

valid observations. – Doriedorin

Very helpful and additive answer! 👍 – Teishateixeira

To add to these workarounds; asynchronous programming is possible with VBA + a little WinAPI or certain libraries that support it - this approach can be used instead of DoEvents to increase responsiveness (without any nasty side effects. See this and this for scheduling asynchronous web requests, and arbitrary VBA functions respectively (full disclosure; I wrote both of those) – Weinhardt

Answer 5

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 <> 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.

Mellman

Answer 6

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 & "\" & fileName
    If openVisible Then newApp.Visible = True
    Set openNewInstance = newApp.Workbooks.Open(ThisWorkbook.Path & "\" & 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 = "'" & otherWkb.Name & "'!" & 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 > 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

Advert

The reason VBScript is more commonly chosen for the workers is a tiny footprint compared to the massive footprint for each worker when running additional VBA hosts. Try your idea with 100 workers. – Wyoming

Answer 7

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 & a & r & b & sn, vbMinimizedNoFocus
End Sub

Yusuk

Answer 8

'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("191.9.202.255", 1958)    
'port recieving
     numSock1=sock.open(5963)
'create thread
     hThread= CreateThread (byval 0&,byval 16384, Add , byval 0&, ByVal 1958, ptrt )
     edit3.text=str$(hThread)


' use 
Declare Function CreateThread Lib "kernel32" Alias "CreateThread" (lpThreadAttributes As long, ByVal dwStackSize As Long, lpStartAddress As Long, lpParameter As long, ByVal dwCreationFlags As Long, lpThreadId As Long) As Long

Gillum