vba VBA中的多线程

Multi-threading in VBA

提问by Kshitij Saxena -KJ-

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

这里有人知道如何让 VBA 运行多个线程吗？我正在使用 Excel。

采纳答案by Thomas

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.

不能用 VBA 本地完成。VBA 内置于单线程单元中。获得多个线程的唯一方法是在具有 COM 接口的 VBA 之外的其他东西中构建一个 DLL 并从 VBA 调用它。

INFO: Descriptions and Workings of OLE Threading Models

信息：OLE 线程模型的说明和工作原理

回答by AnalystCave.com

As you probably learned VBA does not natively support multithreading but. There are 3 methods to achieve multithreading:

正如您可能了解到的，VBA 本身并不支持多线程，但是。实现多线程有3种方法：

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.

1. COM/dlls- 例如 C# 和在单独线程中运行的 Parallel 类
2. 使用 VBscript 工作线程- 在单独的 VBscript 线程中运行您的 VBA 代码
3. 使用例如通过 VBscript 执行的 VBA 工作线程- 复制 Excel 工作簿并并行运行您的宏。

I compared all thread approaches here: http://analystcave.com/excel-multithreading-vba-vs-vbscript-vs-c-net/

我在这里比较了所有线程方法：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/

考虑到方法#3，我还制作了一个 VBA 多线程工具，允许您轻松地将多线程添加到 VBA：http: //analystcave.com/excel-vba-multithreading-tool/

See the examples below:

请参阅以下示例：

Multithreading a For Loop

多线程 For 循环

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

异步运行 Excel 宏

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

回答by stanleykylee

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.

我正在寻找类似的东西，官方答案是否定的。但是，我能够在 ExcelHero.com 上找到 Daniel 提出的一个有趣的概念。

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!

基本上，您需要创建worker vbscripts 来执行您想要的各种事情并将其报告回excel。对于我正在做的事情，从各种网站检索 HTML 数据，效果很好！

Take a look:

看一看：

http://www.excelhero.com/blog/2010/05/multi-threaded-vba.html

http://www.excelhero.com/blog/2010/05/multi-threaded-vba.html

回答by John Coleman

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.

我正在添加这个答案，因为程序员从更现代的语言来到 VBA 并在 VBA 中搜索 Stack Overflow 的多线程可能不知道一些本地 VBA 方法，这些方法有时有助于弥补 VBA 缺乏真正的多线程。

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:

如果多线程的动机是在执行长时间运行的代码时有一个响应速度更快的 UI，并且不会挂起，那么 VBA 确实有几个在实践中经常起作用的低技术解决方案：

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

1) 可以使用户表单无模式显示 - 这允许用户在表单打开时与 Excel 交互。这可以在运行时通过将 Userform 的 ShowModal 属性设置为 false 来指定，或者可以通过放置行在加载时动态完成

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:

2) DoEvents 语句。这会导致 VBA 将控制权交给操作系统以执行事件队列中的任何事件 - 包括 Excel 生成的事件。一个典型的用例是在代码执行时更新图表。如果没有 DoEvents，在运行宏之前不会重新绘制图表，但使用 Doevents，您可以创建动画图表。这个想法的一个变体是创建进度表的常见技巧。在执行 10,000,000 次（并由循环索引i控制）的循环中，您可以有一段代码，如：

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.

这些都不是多线程——但在某些情况下它可能是足够的。

回答by Erik A

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.

我知道这个问题指定了 Excel，但由于 Access 的相同问题被标记为重复，所以我将在这里发布我的答案。原理很简单：打开一个新的 Access 应用程序，然后在该应用程序中打开一个带有计时器的表单，将要执行的函数/子发送到该表单，如果计时器命中则执行任务，并在执行完成后退出应用程序完成的。这允许 VBA 处理数据库中的表和查询。注意：如果您专门锁定了数据库，它会抛出错误。

This is all VBA (as opposed to other answers)

这都是 VBA（与其他答案相反）

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)

（表单名称 = MultiThreadingEngine，没有设置任何控件或属性）

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.

实现对参数的支持应该很容易，但是返回值很困难。

回答by Stiebl

As said before, VBA does not support Multithreading.

如前所述，VBA 不支持多线程。

But you don't need to use C# or vbScriptto start other VBA worker threads.

但是您不需要使用 C# 或 vbScript来启动其他 VBA 工作线程。

I use VBA to create VBA worker threads.

我使用 VBA 创建 VBA 工作线程。

First copy the makro workbook for every thread you want to start.

首先为您要启动的每个线程复制 makro 工作簿。

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

然后，您可以通过创建 Excel.Application 的实例来启动新的 Excel 实例（在另一个线程中运行）（为了避免错误，我必须将新应用程序设置为可见）。

To actually run some task in another thread i can then start a makro in the other application with parameters form the master workbook.

为了在另一个线程中实际运行一些任务，我可以在另一个应用程序中使用来自主工作簿的参数启动一个 makro。

To return to the master workbook thread without waiting i simply use Application.OnTime in the worker thread (where i need it).

要在不等待的情况下返回主工作簿线程，我只需在工作线程（我需要的地方）中使用 Application.OnTime。

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.

作为信号量，我只是使用一个与所有线程共享的集合。对于回调，将主工作簿传递给工作线程。在那里可以重用 runMakroInOtherInstance 函数来启动回调。

'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

回答by Darijo11

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