VBA中的API计时器 - 如何使安全 [英] API Timers in VBA - How to make safe
问题描述
API定时器安全的，什么时候没有？有没有一些 具体的 原则可以帮助我理解？
Option Explicit
公共声明函数SetTimer Libuser32（_
ByVal HWnd As Long，_
ByVal nIDEvent As Long，_
ByVal uElapse As Long ，$ _
ByVal lpTimerFunc As Long）As Long
公共声明函数KillTimer Libuser32（_
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
公共事件类型为整数
公共子重置（）
带有Sheet1.Shapes（MyLabel）
.Fill.Transparency = 0
.Line .Transparency = 0
.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB（0，0，0）
结束
Sheet1.Shapes（MyLabel）。Visible = msoTrue
End Sub
Sub StartTimer（）
iCounter = 1
重置
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
如果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
EndTimer
如果
iCounter = iCounter + 1
End Sub
公共函数ShowPopup（index As Integer）
Sheet1.Range（Hotzone.Index）。Value = index
iCounter = 1
如果bTimerEnabled = False然后
StartTimer
bTimerEnabled = True
重置
Else
重置
结束如果
与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 ）
结束
Sheet1.Range（a4：a6）。单元格（索引，1）.Value =索引
结束函数
@CoolBlue：崩溃的机制是什么？发生什么事情使Excel崩溃？
我可以给你扩展Siddarth Rout的答案，但不是一个完整的解释。
API调用不是VBA：它们存在于VBA的错误处理程序之外，当出现问题时，它们将不执行任何操作，也不会调用内存中不存在的资源，或尝试读取（或写入！）到Excel.exe指定的内存空间之外的内存。
当发生这种情况时，操作系统将进入并关闭应用程序。我们以前称之为一般保护错误，这仍然是该过程的有用描述。
现在有一些细节。
当您在VBA中调用函数时，您只需输入名称即可将其称为CheckMyFile（），这就是您在VBA中需要了解的所有内容。如果没有调用CheckMyFile，或者声明您的调用无法看到它，则编译器或运行时引擎将以断点的形式或在编译并运行之前发出警告来引发错误。 p>
在幕后，有一个与字符串CheckMyFile相关联的数字地址：我简化了一点，但是我们将该地址称为函数指针 - 遵循该地址，并且我们得到一个结构化的内存块，它存储函数参数的定义，存储值的空间，并在其后面，将这些参数引导到为执行VBA而返回的功能结构中值为函数输出的地址。
事情可能会出错，VBA做了很多工作，以确保当它们出错时，所有这些都会优雅地折叠起来。
如果将该函数指针指向不是VBA的外部应用程序或者（说） API定时器调用 - 你的函数仍然可以被调用，它仍然可以运行，一切都可以正常工作。
但是最好在该指针后面是一个有效的函数。
如果没有，外部应用程序将调用自己的错误处理程序，它们不会像VBA一样宽恕。
如果Excel和VBA处于忙状态，或者尝试使用该函数指针时可能不可用，则可能会放弃该调用，并且不执行任何操作：您可能会幸运的，就是那一次。但它可能会在Excel.exe进程中调用操作系统的愤怒。
如果调用导致错误，并且该错误不会被您的代码处理，VBA会将错误提高给调用者 - 并且，由于调用者是' t VBA，它可能无法处理：它将从操作系统中调用帮助。
如果是API调用，它是为假设在调用代码中设置了错误处理和应急管理的开发人员编写的。
这些假设是：
- 肯定会有一个有效的功能指针;
- 它被调用时绝对可用;
- ...它将不会向调用者发出错误。
通过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. But this code from http://optionexplicitvba.wordpress.com due to 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 without any problem.
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.
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 call 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:
- There will definitely be a valid function behind that pointer;
- It definitely be available when it is called;
- ...And it will raise no errors to the caller.
With an API call, 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屋！
