"Method not Applicable...." When Referring to Excel Objects

ID: Q121213

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Visual Basic Standard Edition for Windows, version 3.0

SYMPTOMS

In Visual Basic 3.0, when you use OLE Linking and Embedding to access methods or properties of Visual Basic for Applications objects in Microsoft Excel, and if those methods or properties have names similar to Visual Basic 3.0 names, you may receive the following error message:

Method not Applicable for this Object

STATUS

This behavior is by design. To use the method or property in Visual Basic 3.0, you must enclose it in brackets ([ ]).

NOTE: This behavior does not occur in Microsoft Visual Basic version 4.0.

MORE INFORMATION

The following are some properties and methods whose names are similar in Visual Basic 3.0 and Visual Basic for Applications:

Show
AddItem
RemoveItem

Because these are keywords in Visual Basic 3.0, properties or methods that have the same name in Microsoft Excel return an error when referred to via OLE Automation. This is because Visual Basic 3.0 considers them as its own properties or methods. If the method or property is enclosed in brackets, then Visual Basic 3.0 will correctly pass the property or method to Microsoft Excel.

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft Support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

The following example shows the correct procedure to call the show method of a Microsoft Excel dialog object from Visual Basic 3.0. In the example, "dialog1" is a dialog sheet in a Microsoft Excel workbook named Mybook.xls.


'Type the following two lines in the declaration section of the Form

Dim Xlobj As Object
Dim Xldlg As Object

'Assign the following to the Click event of Command1 button

Sub Command1_Click ()
     Set Xlobj = GetObject("C:\Excel\Mybook.xls")
     Set Xldlg = Xlobj.Parent.Sheets("dialog1")
     'Xldlg.Show will return the error mentioned above
     'Correct syntax
     Xldlg.[Show]
End Sub

Additional query words: 5.00c 8.00 97 Visual Basic 3.0 Reference VB3

Keywords : kbinterop xlwin
Version : WINDOWS:3.0,5.0,5.0c,7.0,97
Platform : WINDOWS
Issue type :