Recorded Macro Is Interactive If CROSSTAB.CREATE() Is Too Long

Last reviewed: July 16, 1997
Article ID: Q88245

The information in this article applies to:

  • Microsoft Excel for Windows, version 4.0
  • Microsoft Excel for the Macintosh, version 4.0

SUMMARY

You can record a macro in Microsoft Excel that automatically creates a cross-tabulation table. If the resulting CROSSTAB.CREATE() function is longer than 255 characters, your macro runs the Crosstab ReportWizard instead of building an Excel cross-tabulation table automatically.

MORE INFORMATION

Microsoft Excel enters the CROSSTAB.CREATE() function along with its arguments in a cell on your macro sheet when you record a macro that creates a cross-tabulation table.

If your field names are very lengthy or if you choose multiple fields for both row and column categories, the resulting CROSSTAB.CREATE() function may exceed the limit in Microsoft Excel of 255 characters per cell.

If the function is longer than 255 characters, Microsoft Excel is unable to enter the complete form of the function and enters the interactive form (=RUN(CROSSTAB.CREATE?,FALSE)) instead.

WORKAROUND

If the CROSSTAB.CREATE() function is too long to fit in the cell, you can use variables in place of its arguments.

To use variables in place of arguments,

  1. Activate your macro sheet. If you are using the Global macro sheet, choose Unhide from the Window menu.

  2. Create variables to hold the arguments for your CROSSTAB.CREATE() function.

    For information about creating variables, see "To create variables" below.

  3. Locate and select the cell containing the cross-tabulation function (=RUN(CROSSTAB.CREATE?,FALSE)).

  4. Edit the function to include your variables as arguments. Make sure you remove the question mark from the function name. For example, the resulting function might look something like the following

          =CROSSTAB.CREATE(Row_Array,Col_Array,Val_Array,<optional_args>)
    

    where Row_Array, Col_Array, and Val_Array are the variables you created for holding the arguments. <optional_args> are optional arguments that you may want to include. For more information on this formula, see pages 82-84 of the "Microsoft Excel Function Reference," version 4.0.

To create variables:

  1. From the Formula menu, choose Define Name.

  2. Type the name you want to use for your variable in the Name box.

  3. Enter the argument array in the Refers To box.

  4. Choose the Add button.

  5. Repeat these steps for each of the category arguments. When you have finished, choose the OK button to close the Define Name dialog box.

    Example -------

    To use the variable name Row_Array and the following argument settings

          Field_name = Sales
          Grouping_index = 3
          From = FALSE
          To = FALSE
          Subtotals = YNNNNNN
    

    type the following in the Name box

          Row_Array
    

    and type the following in the Refers to box:

          ={"Sales",3,FALSE,FALSE,"YNNNNNN"}
    

Be sure to include the equals sign (=) and the curly brackets.

REFERENCES

"Function Reference," version 4.0, pages 82-84

"User's Guide 1," version 4.0, pages 346-356


Additional query words: 4.00


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 16, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.