excel - 异步 VBA 函数

Question

我有一个 VBA 函数，应该从用户的单元格中获取一些信息，使用该信息发出 POST 请求，然后在输出单元格中打印响应。

要求用户能够一次发出大约 2000 个请求，因此我认为使请求异步以帮助提高性能。

就目前而言，我有一个 ConnectToAPI 函数，它发出异步请求，然后将响应传递给回调函数。我遇到的问题是数据位于回调函数中，但我需要在查询函数中使用它才能返回它。

Function Query(ID, quote, field)  
  Application.Volatile
  Query = ConnectToAPI(ID)
  Some logic with parsed data from callback
End Function


Function ConnectToAPI(ID)

    Dim Request As New WebRequest
    Dim Client As New WebClient
    Client.BaseUrl = "http://www.endpoint.com"

    Dim Wrapper As New WebAsyncWrapper
    Dim Wrapper.Client = Client
    Dim Body As New Dictionary
    Body.Add "ID", ID
    Set Request.Body = Body
    Request.Method = HttpPost

    ConnectToAPI = Wrapper.ExecuteAsync Request, "CallbackFunction"
End Function

Function CallbackFunction
    Callback = Parsed Data
End function

所以最终在查询函数中，我想写

查询=(回调中解析的数据)

如何将回调中的数据传递回查询？

单元格中具有查询功能非常重要。数据更新频繁，所以我们希望客户能够通过计算工作簿来获取最新的数据。

根据我目前的情况，我的思考过程是回调将数据传递回 ConnectToAPI，然后将其传递给 Query。但是，我的函数返回 0，我认为这可能是因为一旦函数尝试返回，解析的数据将不可用。

作为引用，我正在使用 VBA-Web 库 https://github.com/VBA-tools/VBA-Web

Answer 1

VBA-Web/src/WebAsyncWrapper.cls

WebAsyncWrapper.ExecuteAsync 有一个可选参数:CallbackArgs。使用此参数向您传回 ID 或单元格地址。

ExecuteAsync 有一个接收参数数组的示例回调函数。

以下是如何将信息返回给函数进行处理。

Sub ConnectToAPI(ID As Variant, quote As Variant, field As Variant, CellAddress As Variant)

    Dim Request As New WebRequest
    Dim Client As New WebClient
    Client.BaseUrl = "http://www.endpoint.com"

    Dim Wrapper As New WebAsyncWrapper

    Dim Body As New Dictionary

    Body.Add "ID", ID
    Set Request.Body = Body
    Request.Method = HttpPost
    Set Wrapper.Client = Client
    Wrapper.ExecuteAsync Request, "Callback", Array(ID, CellAddress)
End Sub

Public Function Callback(Response As WebResponse, Args As Variant)
    Dim ID As Variant, CellAddress As Variant
    ID = Args(0)
    CellAddress = Args(1)
    With Worksheets("Web Requests")
        .Range(CellAddress).Value = Response
        .Range(CellAddress).Offset(0, 1).Value = ID
    End With
End Function

MSDN - Application.Volatile Method (Excel)

Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

我不建议尝试使用可用作工作表函数来返回 Web 请求的 UDF。 Application.Volatile 将导致每次值更改时刷新所有 2000 个查询。当第一个查询更新时，所有其他查询都将刷新。这将导致无限循环并使应用程序崩溃。

Function Query(ID, quote, field)  
  Application.Volatile
  Query = ConnectToAPI(ID)
  Some logic with parsed data from callback
End Function

使用 Worksheet_Change 事件将使用户能够更新信息，而不会出现与 Application.Volatile 相关的问题。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("A")) Is Nothing Then
        If Target.Count = 1 Then
            Debug.Print Target.Value, Target.Address
        End If
    End If
End Sub