Macro to calculate model complexity

In my view, the complexity of a model can be summarized by the number of different paths thru the model.  The higher the number the more complex.  To count the paths with a macro the legal paths need to be identified by foreign key constraints.  If your model represents its relationships the attached macro can not only count the paths but it color codes the tables by changing their background color to highlight the 'importance' of the table in the model.  This is also very handy when getting familiar with a new model.

Each table has two path counts associated with it; one towards root table(s), another towards leaf table(s).  The counts are stored in attachments to the table so you can display them on your diagram if you want.

The color coding is done by creating a relative scale such that tables having scores in the lowest 25% are white, the next 25% light, 25% medium and 25% get a dark background.  The darkest tables represent the tables that have the most paths.  The table with the MAX leaf or root score gets a  distinctive dark color so it can be spotted among others in the top 25%.  Dark red represents root tables (those with the most child paths).  Dark green (leaf tables) have the most parent paths.  White tables have few or no precedents or dependents.

When scoring a table, a loop is arbitrarily counted as 3 tables.  My logic being if one follows the recursion the counter would overflow so stopping somewhere is required.  Since loops are usually not really all that complex I picked 3 but if you want to make them more expensive you can just change the literal to something else.

Since there is no intrinsic way to tell once a macro starts if/when it's done, I pop up a message box when you start the macro telling you to wait for the "Processing Complete" message box.  Large models sometimes take a while to analyze and color.  If you run from the macro editor, debug messages appear in the immediate window to assure you something really is happening:)

  TreeComplexityDiagram.zip