Excel VBA - industrialisation...Part Four "Enumerating Addin references"

Checking references within an Excel Spreadsheet is crucial to success.  A reference is a way for the Excel run-time environment to know where to find code - think of it as a way to #include code if you think in C++. 

A problem is that references exist to files that are outside of the control of the Excel run-time.  This leads to some nasty manifestations of "Dll Hell" for the Excel user. This is something that can be examined through the use of VBE.

On github you can find the code to reproduce this. To reproduce:

1. Download the code from github
2. Note that this code was written on 64bit Excel on 64bit hardware, so the Windows API declaration and call in the code will require modification to work on 32bit Excel versions.  I leave this as an exercise for the reader.
3. Create a new Excel workbook.
4. Save the new workbook as an xlsm format workbook
5. Enter the VBA editor
6. Import the module you just downloaded from github
7. Add a reference to VBE within the new workbook
8. Execute the "EntryPointGetReferences" subroutine

You will see the references appear in the debug window looking something like below:

IsBroken=False BuiltIn=True Description="Visual Basic For Applications" LongPath=C:\Program Files\Common Files\microsoft shared\VBA\VBA7.1\VBE7.DLL GUID={000204EF-0000-0000-C000-000000000046} MajorMinor=4.2 Name=VBA Type=TypeLib

IsBroken=False BuiltIn=False Description="Microsoft Visual Basic for Applications Extensibility 5.3" LongPath=C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB GUID={0002E157-0000-0000-C000-000000000046} MajorMinor=5.3 Name=VBIDE Type=TypeLib

If you look in the code you can see a rem'd out line:

Debug.Print ("Original=" & oRef.FullPath & " New=" & strLongFilePath)

If you un-rem this and rerun you will see these new lines interspersed with the lines as already shown above:

Original=C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA7.1\VBE7.DLL New=C:\Program Files\Common Files\microsoft shared\VBA\VBA7.1\VBE7.DLL

Original=C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB New=C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

This is where the Windows API call to GetLongPathNameW is used - to convert a 8.3 format "C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA7.1\VBE7.DLL" to a nice modern format "C:\Program Files\Common Files\microsoft shared\VBA\VBA7.1\VBE7.DLL".

This makes the data more human friendly and readable. 

This conversion to long file names is also very useful when creating a database of all spreadsheets within an enterprise spreadsheet portfolio.