我可以在工作簿上编译VBA吗？ [英] Can I compile VBA on workbook open?

问题描述

我试图找到一种在工作簿打开的VBA中编译代码的方法。我可以通过打开VBA环境，进入Debug和编译VBAProject手动执行此操作，但是想知道每次打开工作簿时是否有办法通过代码执行此操作。

目的是将代码加载到计算机内存中，以防止由于使用某些Active X对象而导致的编译错误。如上所述，我可以手动执行此操作，但是它解决了问题，因为有很多用户使用这个工作簿是密码保护的，并不是所有的都可以访问调试选项。

解决方案

我想通过自动化VBE（Visual Basic Editor）可以尝试这样做。

REQUIREMENT ：

您需要转到 Excel /文件/选项/信任中心/信任中心设置，并检查选项信任访问VBA项目对象模型（出于安全原因，这是如果不检查它，下面的代码将会导致运行时错误1004 程序访问视觉基础项目不被信任）。显然，你只需要这样做一次（在每台计算机中你想执行自动编译，当然）。

编码： / p>

您的命令栏指令（即编译VBA项目）位于Excel应用程序的VBE对象内，特别是在命令栏中：

  Dim objVBECommandBar As Object 
设置objVBECommandBar = Application.VBE.CommandBars 
  

该对象现在将包含Visual Basic编辑器的整个命令栏。
特别是，你寻找ID按钮578，这实际上是编译VBA项目（你可以把观察者放在变量上，浏览全部在里面进入本地窗口，你可能想要搜索其他命令）。因此，总结：

 设置compileMe = objVBECommandBar.FindControl（Type：= msoControlButton，ID：= 578）
 compileMe.Execute 
  

这将允许编译项目。正如你所问，你只是把它放在这个工作簿的开放活动中：

  Private Sub ThisWorkbook_Open（）
 Set compileMe = objVBECommandBar.FindControl（Type：= msoControlButton，ID：= 578）
 compileMe.Execute'该项目应该被编译
 End Sub 
  

I am trying to find a way of compiling the code in VBA on workbook open. I can do this manually by opening the VBA environment, going into Debug and "Compile VBAProject" but was wondering if there is a way to do this through the code every time the workbook opens.

The purpose of this is to load the code into the computers memory to prevent a compile error due to use of some Active X Objects. As mentioned, I can do this manually and it solves the problem however, as there are many users that use this and the workbook is password protected, not all will have access to the debug option.

解决方案

I think you might try to do this by automating the VBE (Visual Basic Editor).

REQUIREMENT:

you need to go to Excel / File / Options / Trust Center / Trust Center settings and check the option Trust access to the VBA project object model (for security reasons this is deactivated by default, and if you don't check it the below code will raise the run-time error 1004 programmatic access to visual basic project is not trusted). Clearly, you only need to do this once (in each computer you want to execute the automated compilation, of course).

CODING:

Your command bar instruction (i.e. "Compile VBA Project") is inside the VBE object of the Excel Application, specifically in the command bars:

Dim objVBECommandBar As Object
Set objVBECommandBar  = Application.VBE.CommandBars

The object will now contain the entire command bar of the Visual Basic Editor. In particular, you look for the ID button "578", which is in fact the "Compile VBA Project" (you can put a watcher on the variable and browse all is inside into the local window, you might want to search for other commands). Hence, to summarize:

Set compileMe = objVBECommandBar.FindControl(Type:=msoControlButton, ID:=578) 
compileMe.Execute

This will allow the compilation of the project. As you were asking, you just put this into the This Workbook open event:

Private Sub ThisWorkbook_Open()
    Set compileMe = objVBECommandBar.FindControl(Type:=msoControlButton, ID:=578) 
    compileMe.Execute 'the project should hence be compiled
End Sub

这篇关于我可以在工作簿上编译VBA吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！