Error Messages in Macro (How to Create a Reference)

ID: Q150242

The information in this article applies to:

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.

Additional query words: 5.00 5.00a 5.00c 7.00 8.00 xl97

Keywords          : kbcode kbprg PgmHowto 
Version           : WINDOWS:5.0,7.0,97; MACINTOSH:5.0,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: February 4, 1998