Recorded CROSSTAB Macro Is Interactive if Fields Not Selected

Last reviewed: November 29, 1994
Article ID: Q88144

The information in this article applies to:

Microsoft Excel for Windows, version 4.0

SUMMARY

In Microsoft Excel for Windows, it is possible to record a macro that automatically creates a cross-tabulation table. However, when you create your cross-tabulation table, you must select fields for both row categories AND for column categories, or the macro runs the Crosstab ReportWizard instead of building a cross-tabulation table automatically.

For information about how to create a cross-tabulation table, see pages 346-356 of the "User's Guide 1" for version 4.0.

MORE INFORMATION

Workaround

To correct this problem, record your macro again, this time selecting fields both for row categories and for column categories. After recording your macro, edit the CROSSTAB.CREATE function in your macro to remove the argument values for the category you don't want included.

To remove unwanted category argument values:

Activate your macro sheet.
If you are using the Global macro sheet, choose Unhide from the Window menu and select GLOBAL.XLM from the list in the Unhide dialog box.
Select the cell that contains the CROSSTAB.CREATE function in the macro you recorded.
Delete the argument for the category you want to remove.

Each category argument consists of an array of information surrounded by braces. Delete the argument (including the braces) but don't remove the comma. The comma must be left as a placeholder for the argument you remove.

For example, in the following formula

   =CROSSTAB.CREATE({"Vendor",0,"Auto","Auto","NNNNNNN"},
   {"Region",0,"Auto","Auto","NNNNNNN"},,TRUE,TRUE,1,TRUE,TRUE)

removing the column category argument (field name = Region) results in

   =CROSSTAB.CREATE({"Name",0,"Auto","Auto","NNNNNNN"},,,TRUE,
   TRUE,1,TRUE,TRUE)

Notice that the comma in the above formula has been left as a place holder. (The formulas above have been word wrapped for readability. They should appear on one line without any spaces.)

When you record a macro that creates a cross-tabulation table, Microsoft Excel enters the CROSSTAB.CREATE function, including its arguments, in a cell on your macro sheet.

If you don't select field names for both row categories and column categories when creating your cross-tabulation table, the Microsoft Excel Macro Recorder uses the interactive form of the function

   (=RUN"CROSSTAB.CREATE?", FALSE))

instead of the non-interactive form:

   (=CROSSTAB.CREATE().)

REFERENCES

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

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

KBCategory: kbfasttip
KBSubcategory:
Additional reference words: 4.00 crosstab

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: November 29, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.