Excel VBA中用户表单上的计时器 [英] Timer on user form in Excel VBA

问题描述

我有一些旧的Excel VBA代码，我想在其中定期运行任务.如果我使用的是VB6，则应该使用计时器控件.

I've got some old Excel VBA code where I want to run a task at regular intervals. If I were using VB6, I would have used a timer control.

我找到了 Application.OnTime()方法，它对于在Excel工作表中运行的代码很好用，但是我不能使其在用户窗体中工作.该方法永远不会被调用.

I found the Application.OnTime() method, and it works well for code that's running in an Excel worksheet, but I can't make it work in a user form. The method never gets called.

如何使Application.OnTime()以用户形式调用方法，或者还有其他方式来安排代码在VBA中运行?

How can I make Application.OnTime() call a method in a user form, or are there other ways to schedule code to run in VBA?

推荐答案

我找到了解决此问题的方法.如果您在模块中编写了一种方法，而该模块只是以用户形式调用了某个方法，则可以使用Application.OnTime()安排该模块方法.

I found a workaround for this. If you write a method in a module that just calls a method in your user form, then you can schedule the module method using Application.OnTime().

有点不对劲，但是除非有人有更好的建议，否则它会做.

Kind of a kludge, but it'll do unless somebody has a better suggestion.

这是一个例子:

''//Here's the code that goes in the user form
Dim nextTriggerTime As Date

Private Sub UserForm_Initialize()
    ScheduleNextTrigger
End Sub

Private Sub UserForm_Terminate()
    Application.OnTime nextTriggerTime, "modUserformTimer.OnTimer", Schedule:=False
End Sub

Private Sub ScheduleNextTrigger()
    nextTriggerTime = Now + TimeValue("00:00:01")
    Application.OnTime nextTriggerTime, "modUserformTimer.OnTimer"
End Sub

Public Sub OnTimer()
    ''//... Trigger whatever task you want here

    ''//Then schedule it to run again
    ScheduleNextTrigger
End Sub

''// Now the code in the modUserformTimer module
Public Sub OnTimer()
    MyUserForm.OnTimer
End Sub

这篇关于Excel VBA中用户表单上的计时器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！