A small VBA code snippet to display the most important metrics of an Excel workbook like the count of sheets, cells, formulas, constants, named ranges, charts, tables, lines of code and many more
When you are developing a large and complex Microsoft Excel model, you should definitely have an eye on the size of your workbook and its components: how many sheets does your workbook have? How many used cells? How many formula and constant cells? How many of the formulas return an error value? How many Named Ranges are defined? How many Charts, Tables, Pivot Tables, lines of VBA code, etc.?
These metrics will give you an overview on the size and complexity of your model, point you to areas with improvement potential and help you to optimize performance and minimize file size.
Some Microsoft Excel productivity Add-Ins have features providing this information. But what if you do not have such an Add-In available? Or what if you want to share your workbook with your colleagues or deliver it to a client? In this case it is very helpful to have a VBA code snippet which automatically calculates at least the most important workbook metrics and can easily be included in any workbook without the need of installing an Add-In.
Today’s article provides this code snippet to detect and display the most important key figures of a Microsoft Excel workbook. As always, the post comes with an example workbook including the open VBA code for free download.
The Excel Workbook Metrics
The following workbook metrics are detected and displayed in a message box:
- Model Size: physical size of the workbook in Megabytes and Bytes
- Worksheets: count of worksheets in the workbook (total, visible, hidden and very hidden).
In case you are not familiar with the .Visible property of a worksheet: very hidden sheets are worksheets which will not appear in the Unhide Sheet dialogue window of Excel. You can only change the visibility by setting the .Visible property in the VBE from xlSheetVeryHidden to xlSheetVisible or xlSheetHidden - Used Cells: count of used cells in the workbook, i.e. across all sheets (total, visible and invisible cells)
- Blank Cells: count of blank (empty) cells
- Constant Cells: count of cells containing a constant (number or text)
- Formula Cells: count of cells containing a formula
The metrics dialogue window does not only show the total number of formulas, but also divided by formulas returning numbers, texts, logical values (true or false) or errors. The number of error formulas contains all cells returning an error value like #N/A, #REF! or #VALUE!. You should definitely have an eye on the count of error formulas, but you also have to keep in mind that you sometimes may have written formulas which return an error value on purpose (e.g. producing #N/A for data source series which shall not be plotted). In a nutshell: if the count of error formulas is greater than 0, this does not necessarily mean that you have broken formulas or reference errors in your workbook. - Cond. Formatted Cells: count of cells with a conditional format
- Data Validation Cells: count of cells with a data validation rule
- Named ranges: count of named ranges
Please note that this metric does not include the names of the tables / list objects (although table names are shown in the Name Manager). - Pivot Tables: count of Pivot Tables
- Charts: count of charts (worksheet embedded charts only, i.e. without chart sheets)
- Other Objects: count of other objects like freeform shapes or images
- External links: count of links to other Excel workbooks
The count of external links does not represent the number of cells containing a link to another workbook, but rather the count of other Excel workbooks your model is referring to. I.e. if you have 500 cells referring to the same external workbook, the count of external links will still be only one. - VBA components: count of VBA modules, class modules and worksheet objects which actually contain VBA code
- Lines of VBA code: count of lines of VBA code across all VBA modules
This metric also includes all comment and blank lines in the code.
There are other key figures you may be interested in, like e.g. the count of chart sheets or the count of OLE and DDE links. I only included the metrics I am most interested in. If you want to add something, the code will most likely be along the same lines as for the other metrics and it shouldn’t be too difficult to add the necessary statements.
The VBA Implementation
The VBA implementation is one main sub (WorkbookStatistics) and three small helper functions (CountVBComponents, LinesofCode and CountofTables). Most of the metrics are detected by looping through all sheets of the active workbook and taking advantage of the .Cells and .SpecialCells properties of the Range object.
The code is pretty straight forward, so I won’t go into the details here. If you are interested, please download the example workbook (download link see below) and have a look.
Finally, instead of displaying the results in a message box, you could also write the results to a dedicated range on a predefined or newly inserted worksheet. No big deal, all you have to do is to replace the message box statement by a series of VBA lines writing the defined variables to a cell range on a sheet.
Examples
Here are a couple of examples demonstrating the Excel workbook metrics procedure in action:
A screenshot of the example workbook posted for download below:
Please be advised that the calculations, charts, named ranges, etc. in this workbook do not make much sense. The only purpose of this workbook is to provide the code and to demonstrate the results.
Here are the metrics of the workbook which came with the recent article Highlight Actions in Microsoft Excel:
There is a little bit more going on here than in the example workbook above, but it is still a relatively small Excel model.
Finally, here are the metrics of a larger Strategic Financial Model I recently developed for one of my clients:
24 worksheets, more than 700,000 used cells, almost 300,000 formula cells, 14 named ranges, 5 tables, 17 charts, 820 lines of code. This should make clear why I like to keep an eye on the workbook metrics of my models.
How to transfer the VBA code to your own workbook
Here is a step-by-step how to transfer the VBA sub to your own workbook:
- Download the example workbook (download link see next section)
- Unzip the downloaded file and open the workbook
- Open your own workbook
- Go to the VBE (Alt-F11)
- In the Project Explorer of the VBE (press CTRL-R, if the Project Explorer isn’t visible), click on the module “modWorkbookMetrics” and drag it to the VBE project of your workbook
- Finally, close the example file and save your workbook as a macro-enabled Excel workbook
- The VBA sub WorkbookStatistics is now available in your model and you can run the code from the View Macro dialog window (ALT-F8) or you assign the sub to a command button or an icon on a sheet
Download Link
Here is the Metrics Example workbook for free download:
Download Workbook Metrics Example (zipped Excel 2007-2016 workbook, 59K)
Stay tuned.
Leave a Reply