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

MORE INFORMATION

The following are some properties and methods whose names are similar in Visual Basic 3.0 and Visual Basic for Applications: 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