Excel VB | workbook summary & VBA report

It useful when working with large workbooks to be able to summaries the contents of them and in particular see the file properties without having to look through the file options in windows.

My Excel VBA Addin below does just that -the options are Workbook Summary, Worksheets Summary, VBA Summary and Range Name Summary.

The Worksheet Summary is a bit buggy in Excel 2007 as there is a problem with the .SpecialCells(xlCellTypeBlanks) function:


This behavior occurs if you select more than 8,192 non-contiguous cells with your macro. Excel only supports a maximum of 8,192 non-contiguous cells through VBA macros. Typically, if you try to manually select more than 8,192 non-contiguous cells, you receive the following error message: The selection is too large. However, when you use a VBA macro to make the same or a similar selection, no error message is raised and no error code is generated that can be captured through an error handler.

I have tried to solve this by using the UsedRange function for the sheets albeit I know this won’t work in all places. If you are still using 2003 or 97 you can change this in the code to reflect all cells on the sheets.

Set x = w.UsedRange.SpecialCells(xlCellTypeConstants, 23)

Another note is that if you want to run a summary of the VBA code you need to go into maco security and check the “Trust access to the VBA project object model” (this by default is not ticked and you will be prompted that VBA summary can’t be created).


Leave a Reply

Your email address will not be published. Required fields are marked *