Multi-threading in VBA

Question

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

Answer 1

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.