XL97: Error Passing Objects to Subroutine

ID: Q157407

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

When you run a macro in Microsoft Excel 97, you may receive the following error message:

Run-time error '424':
Object required

and the macro ends.

CAUSE

This problem may occur when all of the following conditions are true:

You pass an object, such as a drop-down list, to a subroutine.

You do not use the Call statement in the statement that calls the subroutine

You enclose the object that is being passed in parentheses in the statement that calls the subroutine.

NOTE: This problem does not occur in Microsoft Excel 5.0 and 7.0.

RESOLUTION

Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

The following subroutine generates a run-time error in Microsoft Excel 97:


   Sub Passing_Routine()

       'Pass the drop-down object that is on Sheet1 of your workbook
       'to the subroutine called "Populate_Dropdown"

       Populate_Dropdown (Sheets("sheet1").DropDowns("Drop Down 1"))

   End Sub

   '-------------------------------------------------------------------
   'This is the subroutine that is being called by the above subroutine
   'and takes the drop-down that is being passed as its argument.

   Sub Populate_Dropdown(mydropdown as DropDown)

       mydropdown.List = Array("a", "b", "c")

   End Sub

To resolve this problem, use either of the following methods.

Method 1

Use the Call statement to call the subroutine. For example, the following macro illustrates how to call the subroutine Populate_Dropdown:


   Sub Passing_Routine()

       'Pass the drop-down object that is on Sheet1 of your workbook
       'to the subroutine called "Populate_Dropdown".

       Call Populate_Dropdown (Sheets("sheet1").DropDowns("Drop Down 1"))

   End Sub

Method 2

Remove the parentheses that are around the object that is passed to the subroutine. In the following example, the parentheses are removed:


   Sub Passing_Routine()

       'Pass the drop-down object that is on Sheet1 of your workbook
       'to the subroutine called "Populate_Dropdown".

       Populate_Dropdown Sheets("sheet1").DropDowns("Drop Down 1")

   End Sub

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

You are not required to use the Call keyword when calling a procedure. However, if you use the Call keyword to call a procedure that requires arguments, these arguments must be enclosed in parentheses. If you omit the Call keyword, omit the parentheses that are around the argument list. To pass an array to a procedure, use the array name followed by empty parentheses.

For additional information about passing arguments to subroutines, please see the following article in the Microsoft Knowledge Base:

Q41535 Syntax Differs When Calling a SUB without the CALL Keyword

Additional query words: XL97 dropdown drop down byref byval parameter

Keywords : kberrmsg kbprg kbdta kbdtacode KbVBA
Version : WINDOWS:97
Platform : WINDOWS
Issue type :