The Problem (Q-score 7, ranked #63rd of 95 in the VBA Core archive)
The scenario as originally posted in 2015
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.
Why this Range / Worksheet targeting trips people up
The question centers on reaching a specific cell, range, or workbook object. In VBA Core, this is the #1 source of failures after activation events: every property (.Value, .Formula, .Address) behaves differently depending on whether the parent Workbook is explicit or implicit.
The Verified Solution — niche answer (below median) (+7)
3-line VBA Core pattern (copy-ready)
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
When to Use It — classic (2013–2016)
Ranked #63rd in its category — specialized fit
This pattern sits in the 94% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the VBA Core archive for a higher-consensus alternative.
What changed between 2015 and 2026
The answer is 11 years old. The VBA Core object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.