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

问题描述

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

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

什么时候API计时器安全，什么时候不安全?有一些 特定 原则可以帮助我理解吗? 崩溃的机理是什么:到底是什么使Excel崩溃?

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:崩溃的机理是什么:到底是什么使Excel崩溃?

我可以给您扩展Siddarth Rout的答案，但不能提供完整的解释.

API调用不是VBA:它们存在于VBA的错误处理程序之外，并且当发生错误时，它们将不执行任何操作，或者调用内存中不存在的资源，或者尝试读取(或写入！)内存超出Excel.exe的指定内存空间

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

现在了解一些细节.

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

在幕后，有一个数字地址与字符串'CheckMyFile'相关联:我在简化一下，但是我们将该地址称为功能指针-在该地址之后，我们转到结构化的内存块中，该内存块存储函数参数的定义，其存储值的空间，然后在其后面寻址将这些参数定向到为执行VBA而创建的函数结构中并将值返回到函数输出的地址的地址. /p>

事情可能会出错，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屋！