自动打开子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屋！