Can I compile VBA on workbook open?

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 ObjectSet 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 compiledEnd Sub