1

When running excel VBA under VB control a compile error opens the VBE window, flags the offending line and stops with a message box. I would like to trap such errors before these actions occur.

    Dim objVBECommandBar As Object
    Set objVBECommandBar  = Application.VBE.CommandBar
    Set compileMe = objVBECommandBar.FindControl(Type:=msoControlButton, ID:=578) 
    compileMe.Execute
Community
  • 1
  • 1
AllanB
  • 11
  • 1

2 Answers2

0

I came across this question because I had a similar or same problem. If the 'Compile VBA Project' option is greyed out, then the code will throw an error.

This is the error handling that I added:

Private Sub compileVBA()

    Dim objVBECommandBar As Object, compileMe As Object
    Set objVBECommandBar = Application.VBE.CommandBars
    Set compileMe = objVBECommandBar.FindControl(Type:=msoControlButton, ID:=578)
On Error GoTo err
    compileMe.Execute
    Debug.Print "Compiled"
    Exit Sub
err:
    Debug.Print "Failed to compile VBA"
End Sub
Shawn H
  • 1,118
  • 3
  • 10
  • 24
0

This is the code that works for me. If the compilation is run successfully the compilation command will not be enabled after running. Open to suppress the alert when compilation is run. "Application.DisplayAlerts" set to FALSE did not work.

Sub CompileWorkbook()
    Dim objVBECommandBar As Object
    Set objVBECommandBar = Application.VBE.CommandBars
    Dim compileMe As CommandBarButton
    Set compileMe = objVBECommandBar.FindControl(Type:=msoControlButton, ID:=578)
    If compileMe.Enabled = True Then compileMe.Execute
    If compileMe.Enabled = True Then Debug.Print "Error Compiling"
End Sub
Davidms
  • 1
  • 1