Error Messages in Macro (How to Create a Reference)

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, version 5.0
• Microsoft Excel 98 Macintosh Edition
• Microsoft Excel for the Macintosh, version 5.0

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

1. Create a new Microsoft Excel workbook.

2. Press ALT+F11 (OPTION+F11 on Macintosh) to activate the Visual Basic Editor.

3. On the Insert menu, click Module.

4. 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
   
   

5. Press F5 to run the macro.

6. Because you did not create a reference to Xlodbc.xla, the following message appears:

         Sub or Function not Defined
   
   

7. Click Reset on the Run menu.

8. 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.

9. Run the macro again. Now the macro should run without displaying an error.

Microsoft Excel versions 5.0 and 7.0 for Windows, 5.0 for Macintosh

1. Create a new Microsoft Excel workbook.

2. 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.

3. 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
   
   

4. Press F5 to run the macro.

5. Since you did not create a reference to Xlodbc.xla, the following message will appear:

         Sub or Function not Defined
   
   

6. 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.

7. Run the macro again. Now the macro should run without displaying an error.