vba - 如何限制 VBA 中的函数？

Question

我正在使用 ComboBox 和 VBA 在 Excel 中制作下拉菜单。我已经能够让它向远程 api 发出请求，将返回的数据添加到隐藏的工作表中，并根据 api 的结果更新下拉选项。

我想做的是限制 API 请求。目前，如果 Excel 已经在处理 api 请求，它似乎不会触发该子程序。这并不理想，因为人们常常会快速连续地键入多个字符。我想为每个子调用添加一个计时器，如果在大约 250 毫秒内没有对子函数进行新的调用，则发送 api 请求。如果在 250 毫秒内再次调用，我想取消该子程序的执行。

最初，我尝试创建一个全局“process_id”，其中子进程将向当前全局添加 1，将其本地 id 设置为该值，等待 x 时间，检查其本地 id 是否 === 全局 id，如果不退出子程序。然而，现在看来，当计时器等待 x 时间时，第二个子程序永远不会运行，因此第一个子程序仍在运行，只是 x 秒后(并且第二个子程序根本不运行)。

如何在 Excel VBA 中限制子函数，以便在第一个子函数等待时可以运行相同的子函数？

Answer 1

如上所述；您需要一个具有毫秒精度的异步计时器

希望以下内容对您有用:

While the timer is set, no further timer should be set and when the timer fires the event, the timer stops itself so multiple calls during the 'DelayTimeSeconds' period should only result in one call to the API

' Adapted from https://groups.google.com/forum/?hl=en#!topic/microsoft.public.excel.programming/Ce--7sympZM
' These procedures require that you are using Office 2000 or later, because of the AddressOf function

Public Declare Function SetTimer Lib "user32" ( _
    ByVal HWnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
    ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

Private AllowFireTime As Single
Private TimerID As Long
Private TimerSeconds As Single
Private TimerSet As Boolean

Const DelayTimeSeconds As Single = 0.75

Sub TestFireDelay()
    CallAPIProc
End Sub

Private Function CallAPIProc()
    Dim NowTime As Single: NowTime = Timer
    If AllowFireTime > NowTime Then
        If TimerSet = False Then StartTimer AllowFireTime - NowTime
    Else
        AllowFireTime = NowTime + DelayTimeSeconds

        Call TestCall
        ' Code for API Call

    End If
End Function

Function StartTimer(Optional TimerSeconds = 1) ' how often to "pop" the timer.
    TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
    TimerSet = True
End Function

Function EndTimer()
    On Error Resume Next
    KillTimer 0&, TimerID
    TimerSet = False
End Function

Function TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
    ' The procedure is called by Windows. Put your timer-related code here.
    '
    Call EndTimer
    Call CallAPIProc
End Function

Function TestCall()
    Debug.Print Format(Now, "hh:mm:ss") & "." & Strings.Right(Strings.Format(Timer, "#0.00"), 2)
End Function