Excel VBA - industrialisation...Part Fourteen "Enumerating Excel.Model"

Excel 2013 included a number of changes - one of the big changes is the addition of Excel.Model.  In essence, this allows the user to treat data within the Excel spreadsheets as though it is contained within database tables. 

This allows the use to create validation and a number of other useful techniques.  This post is not an exposition of these changes, here we are concerned with Excel Industrialisation and in this post we cover how to enumerate the data contained within Excel.Model.

As usual, if you want to see the source code, pop over to github and it's all there.

1. Download the code from github 
2. Note that this code will only work in Excel 2013
3. Open the Excel workbook Tooling Spreadsheet v0_0_4.xlsm
4. Enter the VBA editor
5. In the module "getModels" execute the method "EntryPointGetConnections".
6. You will see an output as shown in [2] below.

So, we now see a huge range of diversity in how data is managed within Excel.  So far we have had posts about Enumerating DDE Servers,  Enumerating RTD Servers, Enumerating Addin references, Enumerating COMAddins, Enumerating OLEObjects, Enumerating XL4 Macro Sheets and Enumerating Connections.

This should be bringing the reader towards a conclusion that Excel is a package with a considerable diversity of interface technology.  This diversity is both a strength and a weakness, as many firms will have to support a vast range of interfaces even after the original developers and business users have left the firm. 

As we will see, Excel Industrialisation is the way to bring order and control to this diversity, while retaining all of the business benefits of the investments in that diverse code base.

[2] Immediate Window after running the code

1] Tooling Spreadsheet v0_0_4.xlsm user interface