excel vba代码为什么不能在开放状态下工作? [英] How come excel vba code not working on open?

问题描述

我有应该在开放状态下运行的代码，但是我不明白为什么它不起作用.如果我单步执行代码，我将可以正常工作.而且我可以手动运行它，但是代码永远无法在开放环境下运行.

I have code that is supposed to run on open, but I don't understand why it's not working. If I step through the code I will work just fine. And I can run it manually just fine, but the code never works on open.

我在ThisWorkbook中有代码，我已经搜索了其他线程，但是找不到修复程序.知道发生了什么事吗?

I have the code in ThisWorkbook, and I've searched for other threads but I can't find a fix. Any idea what's going on?

 Private Sub RefreshAndClose_Open()

    If DateDiff("h", "12", Hour(Now)) <= 0 And DateDiff("n", "50", Minute(Now)) <= 50 Then
        ThisWorkbook.RefreshAll
        DoEvents
        ThisWorkbook.Save
        DoEvents
        Application.Quit
    End If

End Sub

推荐答案

假设您是Excel.

您已打开Workbook，并且Worksheet处于活动状态.现在，用户单击某处，Selection更改.为此，需要运行许多低层的样板式非VBA代码，但是在某点或另一点，这会在Worksheet对象的胆量中的某个地方发生(伪代码):

You have a Workbook opened, and a Worksheet is active. Now the user clicks somewhere and the Selection changes. Lots of low-level boilerplate non-VBA code needs to run for this to happen, but at one point or another, this happens (pseudo-code) somewhere in the Worksheet object's guts:

RaiseEvent SelectionChange(ActiveCell)

这引发了Worksheet.SelectionChange事件:

引发此事件时，如果此事件有一个 handler ，则它将被调用.该处理程序需要连接-您可以使用WithEvents对象变量自己进行操作，但是Excel的文档模块使得连接处理程序非常容易-只需转到Worksheet模块您要处理的工作表事件，请从代码窗格的左侧下拉列表中选择Worksheet.

When this event is raised, if there's a handler for this event, that's when it will be invoked. This handler needs to be wired up - you can do that yourself using a WithEvents object variable, but Excel's document modules make it very easy to wire up a handler - simply go to the Worksheet module you want to handle worksheet events for, select Worksheet from the code pane's left-side dropdown...

...然后VBE为您创建处理程序过程-请注意两个下拉菜单的内容:

...and the VBE creates the handler procedure for you - note the contents of the two dropdowns:

处理程序过程以非常特定的方式命名:

The handler procedure is named in a very specific way:

LeftSideDropdownValue_RightSideDropdownValue

左侧下拉列表值"是您要为其实现成员的接口； 右侧下拉列表值"是您要实现的特定成员.

The "left-side dropdown value" is the interface you want to implement a member for; the "right-side dropdown value" is the specific member you're implementing.

如果将插入符号放入过程中，则左侧下拉列表将显示(General)-这意味着它只是另一个过程，如果没有调用它，则它只是另一个未使用过程可能还不存在.

If you placed the caret inside your procedure, the left-side dropdown would say (General) - that means it's just another procedure, and if nothing is invoking it then it's just another unused procedure that might as well not exist.

因此，您是Excel应用程序，并且正在打开工作簿.发生的事情完全相同:在从磁盘加载的启用了.xlsm宏的工作簿文件中反序列化一堆对象时，需要运行许多低层的样板非VBA代码，并且当一切顺利时，就会发生这种情况(伪代码)在打开的Workbook对象内胆的某处:

So, you're the Excel application, and you're opening a workbook. The exact same thing happens: lots of low-level boilerplate non-VBA code needs to run as a bunch of objects are being deserialized from the .xlsm macro-enabled workbook file loaded from disk, and when everything is good to go this happens (pseudo-code) somewhere in the opened Workbook object's guts:

RaiseEvent Open

这引发了Workbook.Open事件:

再一次，我们可以声明一个WithEvents对象变量并自己连接一个处理程序，但是在Excel中托管的VBA项目将始终具有一个已经为我们完成此任务的Workbook文档模块-因此，为了处理Workbook.Open事件，我们可以转到ThisWorkbook模块，从左侧下拉列表中选择Workbook，并让VBE为我们自动生成格式正确的处理程序:

Once again, we could declare a WithEvents object variable and wire-up a handler ourselves, but a VBA project hosted in Excel will always have a Workbook document module that already does this for us - so in order to handle the Workbook.Open event, we can go to the ThisWorkbook module, select Workbook from the left-side dropdown, and have the VBE automatically generate a well-formed handler for us:

Private Sub Workbook_Open()

End Sub

同样，VBA之所以知道此过程正在处理Workbook.Open事件，是因为名称如此表示:

Again, the reason VBA knows this procedure is handling the Workbook.Open event, is because the name says so:

[Interface]_[Member]

如果我们将名称更改为其他名称，我们将断开该链接，并且我们知道该链接已断开，因为左侧的代码窗格下拉列表将显示(General)而不是Workbook.

If we go and change the name to anything else, we break that link, and we know the link is broken because the left-side code pane dropdown will be saying (General) and not Workbook.

这种特殊的命名方案也是为什么应避免在自己的过程名称中使用下划线的原因:它们使过程看起来像事件处理程序，但它们却并非如此.在某些特定的高级方案中，在成员名称中使用下划线甚至会导致编译错误.最好尽早养成良好的习惯，坚持使用简单的PascalCase作为过程名称.

This special naming scheme is also the reason why you should avoid using underscores in your own procedure names: they make procedures look like event handlers, but they're not. In certain specific advanced scenarios, using underscores in member names can even cause compile errors. Best take the good habit early on, of sticking to simple PascalCase for procedure names.

如果我们以任何使其与事件的声明不兼容的方式更改参数列表，则会出现编译错误:

If we go and change the list of parameters in any way that makes it incompatible with the event's declaration, we get a compile error:

您可以安全地调整参数名称(尽管我认为这样做没有任何充分的理由)，但是参数数据类型及其顺序无法修改.

You can safely tweak the parameter names (although, I can't think of any good reason to do this), but the parameter data types and their ordering cannot be modified.

请参见所有内容您曾经想了解事件以获取更多信息(我写了这篇文章).

See everything you ever wanted to know about events for more info (I wrote that article).

这篇关于excel vba代码为什么不能在开放状态下工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！