Microsoft KB Archive/88245

{|
 * width="100%"|

Recorded Macro Is Interactive If CROSSTAB.CREATE Is Too Long

 * }

-

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 &quot;To create variables&quot; below.
 * 1) Locate and select the cell containing the cross-tabulation function (=RUN(CROSSTAB.CREATE?,FALSE)).
 * 2) 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,)

where Row_Array, Col_Array, and Val_Array are the variables you created for holding the arguments.  are optional arguments that you may want to include. For more information on this formula, see pages 82-84 of the &quot;Microsoft Excel Function Reference,&quot; 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:

={&quot;Sales&quot;,3,FALSE,FALSE,&quot;YNNNNNN&quot;}

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