Errors in using the Application.VBE object inside VBA

3

In support of this, " How do I inherit a form in VBA? " >, I followed the guidelines and examples from support.microsoft.com/en-us/kb/204330 with the purpose of generating forms dynamically.

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

In remarks the errors are described, however, for lack of experience in VBA I could not solve the problems.

They seem to be related to the need for version adaptation, but I also do not know how to solve in this case, my Excel is 2016 (Microsof Office 365)

. >

There are two remarks in the test code of the examples that I ran and disabled after the errors occurred, just take the remarks and run to test each.

One note, the indication to enable the Microsoft Visual Basic for Applications Extensibility reference was made prior to testing, as per the site's guidance.

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?

    
asked by anonymous 16.05.2016 / 14:36

1 answer

3

Your code had some problems / errors:

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

  

(In Microsoft Excel, replace ActiveDocument with ActiveWorkbook;   Microsoft PowerPoint, substitute ActivePresentation.)

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

In this 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 question. Access to the project via code is potentially dangerous because it can be used by viruses to corrupt files. So the user (yes, only him) needs to authorize this access (otherwise you get the second line error). This authorization needs to be configured in Excel. In this my other answer has the step-by-step.

    
16.05.2016 / 20:12