Errors in using the Application.VBE object inside the VBA

Question

In support of the solution of the question "How to inherit a VBA form?", executed the guidelines and examples of the site support.microsoft.com/en-us/kb/204330 for the purpose of dynamically generating forms.

The code below refers to a form with two buttons that trigger the routines of the examples.

Us Remarks However, due to lack of experience in the VBA I couldn’t solve the problems.

They seem to be related to the need for version adaptation, but I also don’t know how to solve this case, man Excel is the 2016 (Microsof Office 365).

Two Remarks in the code that are tests of the examples I ran and deactivated after the occurrence of the errors, just take the Remarks and run to test each.

An observation, the indication to activate the reference Microsoft Visual Basic for Applications Extensibility was made prior to the tests, according to the guidance of the site.

Private Sub CommandButton1_Click()

 VbeCompName

End Sub


Sub VbeCompName()

 'MsgBox ActiveDocument.VBProject.VBComponents.Count
 'Erro: O objeto é obrigatório

 'MsgBox Application.VBE.ActiveVBProject.VBComponents.Count
 'Erro: O acesso de programação ao projeto do Visual Basic não é confiável

 X = VBE.ActiveVBProject.VBComponents.Count
 'Erro: O objeto é obrigatório

 For I = 1 To X

  MsgBox VBE.ActiveVBProject.VBComponents(I).Name

 Next I

End Sub


Private Sub CommandButton2_Click()

 BuildMyForm

End Sub


Sub BuildMyForm()

 Set mynewform = _
    VBE.ActiveVBProject.VBComponents.Add(ComponentType:=vbext_ct_MSForm)
 'Erro: O objeto é obrigatório

 With mynewform
      .Properties("Height") = 246
      .Properties("Width") = 616
      .Name = "HelloWord"
      .Properties("Caption") = "This is a test"
 End With

End Sub

What is wrong or missing in this case? Is there a similar functional example?

Answer 1

Your code had some problems/errors:

1 - In Excel, the object is not called ActiveDocument, and yes ActiveWorkbook. This is written in the documentation (just pay a little attention ;) ):

(In Microsoft Excel, replace Activedocument with Activeworkbook; in Microsoft Powerpoint, substitute Activepresentation.)

2 - The documentation is general for all Office tools, but in Excel the object VBE is a property of the object Application. So you always need to use Application.VBE....

That way, the code that works looks like this:

Sub VbeCompName()

 MsgBox ActiveWorkbook.VBProject.VBComponents.Count

 MsgBox Application.VBE.ActiveVBProject.VBComponents.Count

 X = Application.VBE.ActiveVBProject.VBComponents.Count

 For I = 1 To X

  MsgBox Application.VBE.ActiveVBProject.VBComponents(I).Name

 Next I

End Sub

In addition, there is another important issue. Access to the project via code is potentially dangerous as it can be used by viruses to damage files. That’s why, the user (yes, only it) needs to authorize this access (otherwise you get the error of the second line). This authorization needs to be configured in Excel. In this my other answer has the step-by-step.