如何在 VBA 中制作安全的 API 计时器? [英] How to make safe API Timers in VBA?

问题描述

我在很多地方都读到过 VBA 中的 API 计时器存在风险，如果您在计时器运行时编辑单元格会导致 Excel 崩溃.

此代码来自 http://optionexplicitvba.wordpress.com 由 Jordan Goldmeier 编写，似乎没有这个问题.它使用计时器淡出一个弹出窗口，当它淡出时，我可以单击并在单元格和公式栏中输入文本.

API 计时器何时安全，何时不安全?是否有一些具体的原则可以帮助我理解?崩溃的机制是什么:究竟发生了什么导致 Excel 崩溃?

选项显式公共声明函数 SetTimer Lib "user32" ( _ByVal HWnd As Long, _ByVal nIDEvent As Long, _ByVal uElapse As Long, _ByVal lpTimerFunc As Long) As Long公共声明函数 KillTimer Lib "user32" ( _ByVal HWnd As Long, _ByVal nIDEvent As Long) As Long公共 TimerID 一样长公共 TimerSecons 作为单个公共 bTimerEnabled 作为布尔值公共 iCounter 作为整数公共 bComplete 作为布尔值公共事件类型作为整数公共子重置()使用 Sheet1.Shapes("MyLabel").Fill.Transparency = 0.Line.Transparency = 0.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)结束于Sheet1.Shapes("MyLabel").Visible = msoTrue结束子子启动定时器()计数器 = 1重启TimerID = SetTimer(0&, 0&, 0.05 * 1000&, AddressOf TimerProc)结束子子结束定时器()KillTimer 0&, TimerIDbTimerEnabled = FalsebComplete = 真结束子Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _ByVal nIDEvent As Long, ByVal dwTimer As Long)出错时继续下一步调试.打印 iCounter如果 iCounter >50 那么使用 Sheet1.Shapes("MyLabel").Fill.Transparency = (iCounter - 50)/50.Line.Transparency = (iCounter - 50)/50.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = _RGB((iCounter - 50)/50 * 224, _(iCounter - 50)/50 * 224, _(iCounter - 50)/50 * 224)结束于万一如果 iCounter >100 那么Sheet1.Shapes("MyLabel").Visible = msoFalse结束计时器万一iCounter = iCounter + 1结束子公共函数 ShowPopup(index As Integer)Sheet1.Range("Hotzone.Index").Value = index计数器 = 1如果 bTimerEnabled = False 那么启动定时器bTimerEnabled = True重启别的重启万一使用 Sheet1.Shapes("MyLabel").Left = Sheet1.Range("Hotzones").Cells(index, 1).Left + _Sheet1.Range("Hotzones").Cells(index, 1).Width.Top = Sheet1.Range("Hotzones").Cells(index, 1).Top - _(.高度/2)结束于Sheet1.Range("a4:a6").Cells(index, 1).Value = index结束函数

解决方案

@CoolBlue: 崩溃的机制是什么:究竟是什么导致 Excel 崩溃?

我可以对 Siddarth Rout 的回答进行扩展，但不能提供完整的解释.

API 调用不是 VBA:它们存在于 VBA 的错误处理程序之外，当出现问题时，它们要么什么都不做，要么调用内存中不存在的资源，或者尝试读取(或写入！)超出 Excel.exe 指定内存空间的内存

发生这种情况时，操作系统将介入并关闭您的应用程序.我们过去称其为一般保护错误"，这仍然是对该过程的有用描述.

现在了解一些细节.

当你在 VBA 中调用一个函数时，你只需写下名字——我们称之为CheckMyFile()"——这就是你在 VBA 中需要知道的全部内容.如果没有任何名为 'CheckMyFile' 的东西可以调用，或者在您的调用看不到它的地方声明了它，编译器或运行时引擎将在编译和运行之前以断点或警告的形式引发错误.

在幕后，有一个与字符串CheckMyFile"相关联的数字地址:我简化了一点，但我们将该地址称为函数指针 - 跟随该地址，我们进入存储函数参数定义的结构化内存块，其存储值的空间，以及在此之后将这些参数定向到为执行 VBA 而创建的函数结构的地址，并将值返回到函数输出的地址.

事情可能会出错，VBA 做了很多工作来确保所有这些在出错时都能优雅地折叠起来.

如果您将该函数指针指向非 VBA 的某个对象 - 外部应用程序或(例如)API 计时器调用 - 您的函数仍然可以被调用，它仍然可以运行，并且一切会工作.

当您将函数指针传递给 API 时，我们将其称为回调"，因为您调用其计时器函数，然后它会回调您.

但最好在那个指针后面有一个有效的函数.

如果没有，外部应用程序将调用自己的错误处理程序，它们不会像 VBA 那样宽容.

如果 Excel 和 VBA 处于忙碌"状态或在尝试使用该函数指针时不可用，它可能只是挂断电话而不做任何事情:您可能很幸运，就那么一次.但它可能会降低操作系统对 Excel.exe 进程的愤怒.

如果回调导致错误，并且您的代码未处理该错误，则 VBA 会将错误报告给调用者 - 由于调用者不是 VBA，它可能无法处理那:它会从操作系统中寻求帮助".

如果是 API 调用，则它是为假定已将错误处理和应急管理置于调用代码中的开发人员编写的.

这些假设是:

1. 那个指针后面肯定会有一个有效的函数；
2. 调用时肯定可用；
3. ...它不会给调用者带来任何错误.

对于 API 回调，调用方是操作系统，它检测到错误的响应将关闭您.

所以这是一个非常简单的流程大纲 - 一个为什么"而不是什么"的解释.

没有过度简化的完整解释是针对 C++ 开发人员的.如果你真的想要深入的答案，你必须学会​​用指针编程；并且您必须熟悉内存分配、异常、错误指针的后果以及操作系统用于管理正在运行的应用程序和检测无效操作的机制的概念和实践.

VBA 的存在是为了让您了解这些知识并简化编写应用程序的任务.

I read in various places that API timers are risky in VBA, that if you edit a cell while the timer is running it will crash Excel.

This code from http://optionexplicitvba.wordpress.com written by Jordan Goldmeier does not seem to have this problem. It fades a pop-up using the timer and while its fading, I can click and enter text in cells and the formula bar.

When is the API timer safe and when is it not? Are there some specific principles to help me understand? And what is the mechanism of the crash: what is happening exactly to make Excel crash?

Option Explicit
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

Public TimerID As Long
Public TimerSeconds As Single
Public bTimerEnabled As Boolean
Public iCounter As Integer
Public bComplete As Boolean

Public EventType As Integer

Public Sub Reset()
    With Sheet1.Shapes("MyLabel")
        .Fill.Transparency = 0
        .Line.Transparency = 0
        .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
    End With
    Sheet1.Shapes("MyLabel").Visible = msoTrue
End Sub

Sub StartTimer()
    iCounter = 1
    Reset
    TimerID = SetTimer(0&, 0&, 0.05 * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
    KillTimer 0&, TimerID
    bTimerEnabled = False
    bComplete = True
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
    ByVal nIDEvent As Long, ByVal dwTimer As Long)

    On Error Resume Next

    Debug.Print iCounter
    If iCounter > 50 Then
        With Sheet1.Shapes("MyLabel")
            .Fill.Transparency = (iCounter - 50) / 50
            .Line.Transparency = (iCounter - 50) / 50
            .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = _
                RGB((iCounter - 50) / 50 * 224, _
                     (iCounter - 50) / 50 * 224, _
                     (iCounter - 50) / 50 * 224)
        End With
    End If

    If iCounter > 100 Then
        Sheet1.Shapes("MyLabel").Visible = msoFalse
        EndTimer
    End If

    iCounter = iCounter + 1
End Sub

Public Function ShowPopup(index As Integer)

    Sheet1.Range("Hotzone.Index").Value = index

    iCounter = 1

    If bTimerEnabled = False Then
        StartTimer
        bTimerEnabled = True
        Reset
    Else
        Reset
    End If

    With Sheet1.Shapes("MyLabel")
        .Left = Sheet1.Range("Hotzones").Cells(index, 1).Left + _
            Sheet1.Range("Hotzones").Cells(index, 1).Width
        .Top = Sheet1.Range("Hotzones").Cells(index, 1).Top - _
                (.Height / 2)
    End With
    Sheet1.Range("a4:a6").Cells(index, 1).Value = index

End Function

解决方案

@CoolBlue: And what is the mechanism of the crash: what is happening exactly to make Excel crash?

I can can give you an expansion of Siddarth Rout's answer, but not a complete explanation.

API calls are not VBA: they exist outside VBA's error-handlers and when things go wrong they will either do nothing, or call on a resource in memory that doesn't exist, or attempt to read (or write!) to memory that's outside the designated memory space for Excel.exe

When that happens, the Operating System will step in and shut your application down. We used to call this a 'General Protection Fault' and that's still a useful description of the process.

Now for some details.

When you call a function in VBA, you just write the name - let's call it 'CheckMyFile()' - and that's all you need to know within VBA. If there's nothing called 'CheckMyFile' to call, or it's declared where your call can't see it, the compiler or the runtime engine will raise an error in the form of a breakpoint, or a warning before it compiles and runs.

Behind the scenes, there's a numeric address associated with the string 'CheckMyFile': I'm simplifying a bit, but we refer to that address as a Function Pointer - follow that address, and we get to a structured block of memory that stores definitions of the function parameters, space for their stored values and, behind that, addresses directing those parameters into the functional structures created to execute your VBA and return values to the address for the function's output.

Things can go wrong, and VBA does a lot of work to ensure that all this folds up gracefully when they do go wrong.

If you give that function pointer to something that isn't VBA - an external application or (say) an API Timer Call - your function can still be called, it can still run, and everything will work.

We refer to this as a 'Callback' when you hand the function pointer to the API, because you call its timer function, and it calls you back.

But there had better be a valid function behind that pointer.

If there isn't, the external application will call its own error-handlers, and they won't be as forgiving as VBA.

It might just drop the call and do nothing if Excel and VBA are in a 'busy' state or otherwise unavailable when it tries to use that function pointer: you might be lucky, just that once. But it might call down the wrath of the operating system on the Excel.exe process.

If the callback results in an error, and that error isn't handled by your code, VBA will raise the error to the caller - and, as the caller isn't VBA, it'll probably have no way of handling that: and it'll call for 'help' from the operation system.

If it's an API call, it was written for developers who are assumed to have put the error-handling and contingency management in place in the calling code.

Those assumptions are:

1. There will definitely be a valid function behind that pointer;
2. It definitely be available when it is called;
3. ...And it will raise no errors to the caller.

With an API callback, caller is the operating system, and its response to detecting an error will be to shut you down.

So that's a very simple outline of the process - a 'why' rather than a 'what' explanation of it.

The full explanation, without the oversimplifications, is for C++ developers. If you really want the answer in depth, you must learn to program with pointers; and you must become fluent with the concepts and practice of memory allocation, exceptions, the consequences of a bad pointer and the mechanisms used by an operating system to manage running applications and detect an invalid operation.

VBA exists to shield you from that knowledge and simplify the task of writing applications.

这篇关于如何在 VBA 中制作安全的 API 计时器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！