4

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
Community
  • 1
  • 1
Jevgenius
  • 41
  • 1
  • 2
  • Sounds like permissions setting. What Trust Center Macro Settings he has? Excel: File -> Options, Trust Center. Trust Center -> Macro Settings. Is the "Trust access to the VBA project object model" ticked? – PatricK Dec 07 '15 at 22:05
  • What have you actually tried yourself to identify the problem? You need to show your research effort. – SierraOscar Dec 07 '15 at 22:09
  • It's best not to delete things from a collection while you're looping over that collection. Add the items to an array or a new collection and then delete them when you're done with the initial loop. – Tim Williams Dec 07 '15 at 23:02
  • Using unnecessary parentheses can also cause issues - it should be `.Remove f` instead of `.Remove (f)` – barrowc Dec 08 '15 at 00:41
  • @PatricK I checked that first thing. It's there. – Jevgenius Dec 09 '15 at 07:22
  • @Macro Man Well, i search for similar problems, usually it was a problem in the Trust settings OR .ThisWorkbook, had to be after Application, but that doesnt work for this case. – Jevgenius Dec 09 '15 at 07:26
  • @barrowc Thanks, will try. But funny thing is, that it was working for like 6 months. Must be the settings somewhere. – Jevgenius Dec 09 '15 at 07:26
  • @TimWilliams OK, will refactor that part. – Jevgenius Dec 09 '15 at 07:26
  • And also, its only problem at his PC, when i run that on my PC everything is OK. – Jevgenius Dec 09 '15 at 07:29

2 Answers2

3

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

Community
  • 1
  • 1
ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
1

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".

Andreas Covidiot
  • 4,286
  • 5
  • 51
  • 96
  • Currently I can reproduce the problem if I open Excel (2010) and the `Workbook_Open` event is accessing the VBComponents, although Trust Center settings are ok. It is working in Excel 2016 though :-/ – Andreas Covidiot Aug 06 '19 at 13:23
  • Since I only wanted to (generically) access the `Application.VBE.ActiveVBProject.VBComponents("MyModule").ActiveVBComponent.FileName` I replaced it by calling `MyWorkbook.FullName` for Excel 2010 support. – Andreas Covidiot Aug 07 '19 at 14:31