XL97: Error Passing Objects to Subroutine

• 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

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.

  -and-

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

  -and-

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

RESOLUTION

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 engineers 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 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

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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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:

   ARTICLE-ID: Q41535
   TITLE     : Syntax Differs When Calling a SUB without the CALL Keyword