Can I compile VBA on workbook open?

Question

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.

Slifka

If not this, then does anyone know where the compiled files are stored? I am thinking that instead of compiling the new workbook, would it not just be possible to send all users these compiled files and provide instructions for where these files must be stored within the temp directory? – Slifka

Thank you Matteo, this isn't really urgent, just something I have had lots of trouble with in many workbooks previously including my current project. I look forward to seeing your solution. – Slifka

There you go, it's in the answer. I've just tested on one of my projects and it seems to work fine. Don't hesitate to point out possible downsides, it's the first time I try something like this as well. – Attached

Just curious but would creating an addin to distribute your code solve the problem? – Mayflower

Answer 1

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

Attached

Thank you so much for this, you solved the biggest part of my problem. I am now trying to figure out how to do this with a locked workbook as in such workbooks the debug->compile option is disabled. Do you know of any way of keeping the compile option enabled whilst locking the code? – Slifka

This is a different question, i suggest you to open another thread (both because it's good to separate the problems for the other users and because i cannot follow you up, won't be home for the next 24 hours ;) – Attached

I created a new module and put this code in there but it still asks me to compile when opening ? Is there something specific I'm missing ? Private Sub ThisWorkbook_Open() Dim objVBECommandBar As Object Set objVBECommandBar = Application.VBE.CommandBars Set compileMe = objVBECommandBar.FindControl(Type:=msoControlButton, ID:=578) compileMe.Execute 'the project should hence be compiled End Sub – Selfseeking

@AspiringDeveloper you should not put the code in a module, but inside the ThisWorkbook_Open event. Check this page – Attached