excel - Missing VBA compiler message for wrong method name

Question

Consider the following code:

Public Sub VBACompilerIsMad()

    Dim Ap As Application     
    Dim Wb As Workbook
    Dim Ws As Worksheet

    Debug.Print Ap.XXX ' No compile error
    Debug.Print Wb.XXX ' No compile error
    Debug.Print Ws.XXX ' Compile error

End Sub

When I compile this, I get a compiler error for referring to an inexisting member of Worksheet. However, if I comment out the last line, there is no compiler error, even though neither Application nor Workbook have a method or property XXX. It is as if I declared Ap and Wb as Object variables.

Why does the compiler treat Application / Workbook differently from Worksheet?

Are there any other classes like this, that the compiler seems to treat as if they were Object?

Answer 1

As I have been explained (kudos go respectively), this is a COM feature.

By default COM assumes an interface is extensible, that is, it allows adding members at run time. If that is not the desired behaviour, one can apply the [nonextensible] attribute to the interface definition, which declares the interface only accepts methods explicitly defined in the type library.

dispinterface _Application and dispinterface _Workbook do not have this flag set in the Excel type library, dispinterface _Worksheet does.

Similarly, ADO's dispinterface _Connection does not have [nonextensible], dispinterface _Command does.

To learn which are extensible, add a reference to TypeLib Info in the project's References and run:

Dim t As tli.TLIApplication
Set t = New tli.TLIApplication

Dim ti As tli.TypeLibInfo
Set ti = t.TypeLibInfoFromFile("excel.exe")

Dim i As tli.InterfaceInfo
For Each i In ti.Interfaces
    If (i.AttributeMask And tli.TYPEFLAG_FNONEXTENSIBLE) <> tli.TYPEFLAG_FNONEXTENSIBLE Then
      Debug.Print i.Name
  End If
Next

You will see that almost all interfaces are extensible here, so most of them get pushed out of the debug window and you will only see the last ones. Change the <> to = to print those that are not extensible, there are much less of them.