Menu
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.
904
From your perspective, the Compile VBA Code command essentially just does a syntax check for you. It's no different than what happens when you run your VBA project -- it first does a syntax check, reporting any errors that it may find, and then "compiles" your code into an intermediary byte code that is then executed.
Steps to run macro automatically when the workbook opens: Step 2: Go to ThisWorkbook Tab. Step 3: Write down Private Sub Workbook_open() and hit enter. You will then see the below screen. You can write your code or basically whatever you want between this and it will automatically run every time the workbook is opened.
To open a workbook using VBA, you need to use the “Workbook. Open” method and specify the path of the file (make sure to specify the full path to the workbook with name and extension file type). This method has a total of fifteen optional arguments which you can use to deal with different kinds of files.
Double click ThisWorkbook in Project – VBAProject pane to open the ThisWorkbook (Code) window. 2. In the code window, select Workbook from the left drop down list. Notice that, in default, the Open will be displayed in right drop down list, if not, change it to Open.
Open an excel workbook. Press Alt+F11 to open VBA Editor. Insert a userform from Insert menu (UserForm1) Double click on ThisWorkbook from Project Explorer. Copy the above code and Paste in the code window. Save the file as macro enabled workbook.
Open an excel workbook Press Alt+F11 to open VBA Editor Double click on ThisWorkbook from Project Explorer Copy the above code and Paste in the code window
Click Save button. Now the code will run while the workbook is open. If you want to run the code while closing workbook each time, select Deactiviate from the right drop down list in the Code window, and copy the code you will run and paste between Private Sub Workbook_Deactivate () and End Sub.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
