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 "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.

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"}
   

REFERENCES

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

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