Microsoft KB Archive/150242

= Error Messages in Macro (How to Create a Reference) =

Article ID: 150242

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh
 * Microsoft Excel 5.0 for Macintosh

-



This article was previously published under Q150242



SYMPTOMS
When you run a procedure in Microsoft Excel, you may receive one of the following error messages:

Sub or Function Not Defined

-or-

User-defined type not defined

-or-

Run-Time error '424' Object Required



CAUSE
You receive these error messages because a reference to a library that uses one of the functions in the procedure is missing and you need to add it.



MORE INFORMATION
Creating a reference to a .dll, .olb, or .xla file allows a procedure to use functions or other procedures by including just a function name, with or without arguments.

The following steps provide examples of how to duplicate the error and how to resolve it by adding the reference. The examples use the functions SQLOpen and SQLClose. Because SQLOpen and SQLClose are functions that are stored in the Xlodbc.xla ODBC add-in, you must create a reference to Xlodbc.xla in order to use the functions it contains.

Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition
 Create a new Microsoft Excel workbook. Press ALT+F11 (OPTION+F11 on Macintosh) to activate the Visual Basic Editor. On the Insert menu, click Module.  Type the following example code in the code window of the module: Sub SQL_OPEN_Example Dim chan As Variant

'Opens a channel to the Nwind data source. chan = SQLOpen("DSN=Nwind") 'Closes the channel to Nwind. SQLClose chan End Sub  Press F5 to run the macro. Because you did not create a reference to Xlodbc.xla, the following message appears:

Sub or Function not Defined

</li> Click Reset on the Run menu.</li> Create a reference to Xlodbc.xla by clicking References on the Tools menu and selecting the Xlodbc.xla check box. If Xlodbc.xla is not in the list of Available References, click the Browse button to locate the add-in and add it to the list.</li> Run the macro again. Now the macro should run without displaying an error.</li></ol>

Microsoft Excel versions 5.0 and 7.0 for Windows, 5.0 for Macintosh
<ol> Create a new Microsoft Excel workbook.</li> Insert a module sheet. To do this, point to Macro on the Tools menu, and then click Module.

NOTE: The following steps are done on the module sheet.</li>  Enter the following example code on the module sheet: Sub SQL_OPEN_Example Dim chan As Variant

'Opens a channel to the Nwind data source. chan = SQLOpen("DSN=Nwind") 'Closes the channel to Nwind. SQLClose chan End Sub </li> Press F5 to run the macro.</li> Since you did not create a reference to Xlodbc.xla, the following message will appear:

Sub or Function not Defined

</li> Create a reference to Xlodbc.xla by clicking References on the Tools menu and select the Xlodbc.xla check box. If Xlodbc.xla is not in the list of Available References, click the Browse button to locate the add-in and add it to the list.</li> Run the macro again. Now the macro should run without displaying an error.</li></ol>

Additional query words: 5.00a 5.00c 8.00 xl97

Keywords: kbcode kbhowto kbprogramming KB150242

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.