Error Using WORKBOOK.ADD() without First Argument

• Microsoft Excel for Windows, versions 4.0, 5.0

SYMPTOMS

Microsoft Excel requires a worksheet name for the first argument in the WORKBOOK.ADD() function. If you use this macro function without the first argument, you receive a macro error when you run the macro that contains this function.

In Microsoft Excel version 5.0, if you use this function in a Microsoft Excel version 4.0 macro sheet without the first argument, you receive a general protection (GP) fault.

CAUSE

You can use the WORKBOOK.ADD function to add one or more documents to a workbook in Microsoft Excel version 4.0. If the worksheet argument is left out in most functions, the active sheet is assumed. This is not the case for the WORKBOOK.ADD function.

The WORKBOOK.ADD function requires a first argument and will halt on a macro error if no argument is given. This also applies to the WORKBOOK.COPY() and WORKBOOK.MOVE() functions.

WORKAROUNDS

Microsoft Excel version 5.0

To avoid receiving a GP fault in Microsoft Excel version 5.0, use either of the following methods:

Method 1: Use the WORKBOOK.MOVE function instead of the WORKBOOK.ADD

          function. You  can use the WORKBOOK.MOVE function without the
          first argument to move  the active sheet to the specified
          workbook, unless the active sheet is  the only sheet contained in
          the workbook.

          to  another workbook. The following example moves the active
          sheet to BOOK1:

             ActiveSheet.Move Workbooks("Book1").Sheets(1)

          Note that the sheet is inserted before Sheet1 in BOOK1.

Microsoft Excel version 4.0

To avoid receiving a macro error in Microsoft Excel version 4.0, use GET.DOCUMENT(1) for the first argument. This returns the name of the active sheet.

REFERENCES

"Function Reference," version 4.0, page 461