Why doesn't VBE.ActiveCodePane.CodeModule work when the VBE (code window) isn't open?

Question

Create a form that runs the following code.

MsgBox (VBE.ActiveCodePane.CodeModule)

And this message appears.

Now save, close, and reopen the database, and see this message:

Run-time error '91': Object variable or With block variable not set

If you open the Visual Basic Editor, it runs again. Even if you close the VBE, it still runs.

But when you close the whole application and reopen it, leaving the VBE closed, you get the error.

Why? What's going on here?

Popp

I had the problem in an Workbook_Open() handler and the ...Activate solution from Bas Verlaat below solved it nicely. Strangely the Err object was not available on this error so debugging was quite weird. – Meakem

Answer 1

You reference the active pane object. The object isn't set until a pane gets activated. So before you open the VBE, the object is not set yet. Once you close the VBE, the object remains, so you can still reference it.

To get a handle to the ActiveCodepane object, without opening the VBE, is by activating a VBComponent, like this:

VBE.ActiveVBProject.VBComponents("Module1").Activate

You can activate any VBComponent like this.

Teen

Answer 2

Well when the VBE is closed upon the first opening of the application, there is no ActiveCodePane, you can check this in a conditional upon loading your form:

If (Application.VBE.ActiveCodePane Is Nothing) Then MsgBox "ActiveCodePane is Nothing"

The VBE exists and properties and methods can be used, but there is no ActiveCodePane which is why you're receiving the null reference exception. Just opening the VBE will still produce your error if you closed all CodePanes before saving and closing previously (unless a module exists for some reason). You must the explicitly open a CodePane, to set the 'ActiveCodePane' property.

This makes sense. What is it that you're trying to access via the ActiveCodePane property? Perhaps I can help find a way around?

Edit

Presumably, as you develop this Form and associated Modules, you'll know what they're called, and would be able to use a different method than the ActiveCodePane, such as that which @Bas Verlaat mentioned. Alternatively, you can loop through each code pane in the active VBProject and try and match on a name or something:

Option Compare Database
Option Explicit

Private vbProj As VBIDE.VBProject
Private vbComp As VBIDE.VBComponent
Private vbMod As VBIDE.CodeModule

Private Sub Command0_Click()

    Set vbProj = Application.VBE.ActiveVBProject

    For Each vbComp In vbProj.VBComponents
        MsgBox vbComp.CodeModule
    Next

End Sub

Elwaine

I'm trying to get the code module name. I just find it a little strange that code would run fine during development but then break when deployed to production, which happened to me. – Popp

Ok. It's been a while since I've looked at VBE stuff. I'll see what I can come up with. – Elwaine

I've updated my answer to house a short loop to get at CodePanes/CodeModules regardless if the VBE or any CodePanes are open. I noticed that Application.VBE.CodePanes was actually only the collection of open code panes, not all. The method posted in my edit works when the VBE has not been opened previously. – Elwaine

Thank you. That's pretty slick. For future readers, just remember to add the reference to Microsoft Visual Basic for Applications Extensibility 5.3. – Popp

Good point, thanks for mentioning the reference. If either of the answers helped you, would you mind marking one as an answer so future readers may know what helped most? – Elwaine

Answer 3

As Bas Verlaat said, before you open the VBE, the object is not set yet.

Obviously the VBE won't be open when a user is using the program. Referencing the Active Code Pane should only be done when developing and debugging, and never in production.

For example the following custom made error message is great for debugging, but will fail if deployed to production.

ErrorHandler:
    MsgBox "Error " & Err.number & ": " & Err.Description & " in " & _
    VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"

Popp

Answer 4

Approach

Since sometimes it can't be avoided to use VBE functionality, the general solution for me is this ...

1. make sure to initialize/activate the relevant workbooks VB project (components)
2. make sure to initialize/activate other workbooks VB projects (components) after they are opened (if their VBE properties/code may be relevant later in the process)

VbeInit usage

To do this one could call (see code below)

• VbeInit in the beginning of some event (e.g. the Workbook_Open() event) and
• VbeInit someOpenedWorkbook whenever a relevant workbook was just opened (e.g. using Workbooks.Open(...))

Example Case

It works for the simpler above case. A more complex case, where this is very obvious and necessary, is when you rely on the CodeName of sheets in workbooks. E.g.

• if the user is allowed to reName some sheets, but they should still be uniquely identifiable by the VB application, via their CodeName and
• such sheets (e.g. serving as template sheets) are copied (sheet.Copy ...) to other workbooks.

Then the sheets CodeName will only be copied, if the sheets VBComponent has been activated (e.g. via myVBComponent.Activate or opening the source-sheet-containing workbook in the VBE).

The VbeInit procedure

Procedure VbeInit(Optional wb As Workbook)

    With Application.VBE
        Dim pj As VBProject:  For Each pj In .VBProjects
            'ignore unsaved (=> fully initialized) workbooks
            If Not wb Is Nothing Then If wb.FullName <> VBProjFilename(pj) Then GoTo continue

            Dim c As VBComponent:  For Each c In pj.VBComponents
                c.Activate
            Next c

continue:
        Next pj
    End With

End Procedure


Function VBProjFilename(pj As VBProject) As String
    On Error Resume Next  'leave result empty if workbook (code) not saved yet
    VBProjFilename = pj.Filename
End Function

Meakem