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.