Menu
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?
749
Using Debug to Find Compile/Syntax Errors To compile a code, click on the Debug option in the toolbar and click on Compile VBAProject. When you compile a VBA project, it goes through the code and identifies errors (if any). In case it finds an error, it will show you a dialog box with the error.
I have resolved same error by following these 4 steps : Open Excel file which is having issue, press Alt + F11 go into its Visual Basic Editor. From the Tools menu select References ( Note, if references option is disabled in tools menu try closing and reopening file and enable Macro before proceeding next steps)
Compile errors refer to a wider group of VBA errors, which include syntax errors. Compile errors also identify problems with your code when considered as a whole. The syntax of each individual line may be correct, but when put together, the lines of your code don't make sense.
To find compilation errors, we use Debug->Compile VBA Project from the Visual Basic menu. When you select Debug->Compile, VBA displays the first error it comes across. When this error is fixed, you can run Compile again and VBA will then find the next error.
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.
117
A bit of a hypothesis:
You can call a stored procedure on an ADODB.Connection object like a native method (at the bottom). (The examples for this on several msdn sites look oddly messed up).
So there is some mechanism like 'anonymous/dynamic methods' in VBS/VBA. It may be a similar mechanism activated here for Application and Workbook classes - although I don't see where and how exactly.
A test supports the basic idea:
I have tested this with a reference to Microsoft ActiveX Data Objects 2.8 Library:
Public Sub testCompiler() Dim cn As ADODB.Connection Dim cmd As ADODB.Command Debug.Print cn.XXX Debug.Print cmd.XXX End Sub cn.XXX does not throw a compile error, cmd.XXX does.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
