Method 'VBE' of object '_Application' failed

Question

One of my clients has issue running the Excel VBA code below. He gets the following error Method 'VBE' of object '_Application' failed, but only once, after he opens the VBE, it starts to work. Also, it was working for him until yesterday.

He is using Excel 2010.

This is the code that throws the error.

For Each f In Application.VBE.ActiveVBProject.VBComponents
    If InStr(1, f.Name, "UserForm") = 1 Then
        Application.VBE.ActiveVBProject.VBComponents.Remove (f)
    End If
Next f

Answer 1

You'll need to enure that Access to the VBE is trusted, by navigating to the Trust Center in Excel Options.

Some actions on the VBComponents require that the VBE has been opened at least once, or at least, that the VBA has referenced the VBE before it tries to enumerate the vbComponents.

It looks like your client's code might be running in an auto_open procedure or Workbook_Open event. You may find that the code begins to work again if you explicitly add a line the refers to the VBE before you enumerate the vbComponents.

Debug.Assert Application.VBE.ActiveVBProject.Name <> vbNullString

For Each f In Application.VBE.ActiveVBProject.VBComponents
    If InStr(1, f.Name, "UserForm") = 1 Then
        Application.VBE.ActiveVBProject.VBComponents.Remove (f)
    End If
Next f

See the details in the answers on this question: VBA changing sheet codename Run-Time Error 9: Subscript out of range

Answer 2

the Debug.Assert ... mentioned by @ThunderFrame did not work for me, but the Trust Center Options did: (depending on your Excel version something like):

Excel  ->  Menu  ->  File
->  Options
    ->  Trust Center
        ->  Macro Settings
            ->  Developer Macro Settings
                ->  [x] Trust access to the VBA project object model

A general approach to solve such VBComponent-dependent code problems is to use the VbeInit procedure given here that is flexible to be called multiple times for each newly opened workbook that may not yet be "VBComponent-initialized".