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

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:

Keywords :
Version :
Platform :
Issue type :


Last Reviewed: March 26, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.