自动打开子VBA [英] Auto Open Sub VBA
问题描述
我有一个宏,该宏会自动使用以下命令打开双击的Excel工作簿:Sub Auto_Open()
在模块中包含脚本,但是在VB编辑器ThisWorkbook
中的相同脚本在没有模块的情况下使用:Private Sub Workbook_Open()
不起作用.>
为什么有任何想法?直接从ThisWorkbook
自动打开有什么优势?如果可以实现后者,那么我不需要任何模块.
[Public] Sub Auto_Open()
是旧的/旧的方法.除了其名称外,没有什么可以说该过程在首次打开工作簿时自动运行",如何将其连接起来以在Open上运行是一种魔力:同名的公共过程在Open上运行,如果您拥有一个公共过程,谁知道呢?两个不同模块中的两个这样的过程.该名称也不是标准的:这里没有遵循公认的且相当熟悉的Interface_Member
约定(两个标识符之间带有下划线):Excel对象模型中没有Auto
对象.
Private Sub Workbook_Open()
是一个事件处理程序过程,用于处理Workbook
接口所实现的Workbook
接口的Open
事件;像Auto_Open
一样,它在打开工作簿时运行,但是其机制更加透明,并且可以像其他所有事件处理过程一样正常工作:Workbook
是接口,Open
是事件/成员.>
这是一个Workbook
事件,具有有趣的含义:以编程方式打开(从VBA代码)打开的工作簿将始终在启用宏的情况下打开-如果该工作簿具有Workbook_Open
处理程序,则该宏将运行-但是成为实际事件意味着您的调用代码可以决定使用Application.EnableEvents = False
将其关闭.
至于它没有运行,禁止宏安全性设置,我唯一想到的原因是名称中是否有错字.永远不要手动键入事件处理程序签名-通过使用代码窗格顶部的下拉菜单,您可以使VBE为您生成正确的事件处理程序签名.
当左侧下拉列表包含对象/接口的名称,而右侧下拉列表包含事件/成员的名称时,您知道您正在查看事件处理程序.
I have a macro that automatically opens double-clicking Excel workbook using: Sub Auto_Open()
with script in a module, but same script in VB editor ThisWorkbook
with no modules using: Private Sub Workbook_Open()
doesn't work.
Any ideas why, and is there any advantage to directly auto-opening from ThisWorkbook
? I don't have a need for any modules if the latter could be accomplished.
[Public] Sub Auto_Open()
in a standard/procedural module is the old/legacy way to go. Nothing besides its name says "this procedure runs automatically when the workbook is first opened", how it's wired-up to run on open is kind of magic: a public procedure by that name runs on open, and who knows what happens if you have two such procedures in two separate modules. The name isn't standard either: the well-established and rather familiar Interface_Member
convention (with the underscore between the two identifiers) isn't respected here: there's no Auto
object in the Excel object model.
Private Sub Workbook_Open()
is an event handler procedure that handles the Open
event of the Workbook
interface, which ThisWorkbook
implements; like Auto_Open
, it runs when the workbook is opened, however its mechanics are much more transparent, and work exactly like every other event-handling procedure: Workbook
is the interface, Open
is the event/member.
It being a Workbook
event has interesting implications: a workbook that's programmatically opened (from VBA code) will always open with macros enabled - and if that workbook has a Workbook_Open
handler, then that macro will run - however it being an actual event means your calling code can decide to switch it off with Application.EnableEvents = False
.
As for it not running, barring macro security settings the only reason I can think of is if there's a typo in the name. Never type event handler signatures by hand - by using the dropdowns at the top of the code pane, you make the VBE generate the correct event handler signatures for you.
You know you're looking at an event handler procedure when the left-hand-side dropdown contains the name of the object/interface, and the right-hand-side dropdown contains the name of the event/member:
这篇关于自动打开子VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!