Identifying all called Personal.xlsb macros

[Desmond Walsh]

I would like to get a list of all Personal.xlsb macros called from my Excel workbook.

This is motivated by a requirement to migrate from my computer an Excel workbook without bundling a copy of Personal.xlsb with it.

Of course, this can be done manually by trying to compile the workbook code and pasting in Personal.xlsb macros until the compile succeeds.

My only idea on automating this would be to generate a list of all Personal.xlsb macros and then search for each macro name in the workbook code listing (target code). Each time a hit occurs, that Personal.xlsb's macro code would have to be added to the target code since of course the Personal.xlsb macro may call other Personal.xlsb macro's.

Is there any smarter way to do this ?

[Desmond Walsh]

The solution turned out to be quite easy to implement. The steps are ;

1. Generate a listing of all VBA code used by the workbook. Saved in Listing A
2. Generate a list of all macro names in the workbook source code. Saved in Listing A.1
3. Generate a listing of all VBA code used by the PERSONAL.xlsb. Saved in Listing B
4. Generate a list of all macro names in PERSONAL.xlsb source code. Saved in Listing B.1
5. Search workbook source code (A.1) for all occurrences of macro names in PERSONAL.xlsb (B.1)
   For each hit save the name and the start and end line references in PERSONAL.xlsb source code (B.1). Saved in Listing C
   Recursively search the found PERSONAL.xlsb macro for calls to other PERSONAL.xlsb macros
6. Add a new module to the workbook and add to it all the macros itemized in Listing C

Steps 1,2,3,4,6 use the object library VBIDE which is included by adding a reference to Microsoft Visual Basic for Applications Extensibility 5.3. The use of this library is very clearly documented in the late Chip Pearson's website. The code fragments are ;

1 Getting a list of all VBA source code

Please Login or Register  to view this content.

2 Getting a list of all macro names

Please Login or Register  to view this content.

6 Add a new module to the workbook

Please Login or Register  to view this content.

Here is an example of how to add a macro to say Module 1

Please Login or Register  to view this content.

Step 5 could be implemented in any scripting language (even as a VBA macro). However, I chose Perl because of expertise in Perl and because of Perl's superior implementation of regular expression processing and its suppport of recursive subroutine calls.

The end result is an automated process to add to a workbook all code dependency on PERSONAL.xlsb. This removes the neccessity to bundle PERSONAL.xlsb with the
workbook if it is being migrated to another host computer. And, the operation can be easily reversed by simply deleting the code module added in Step 6.

[Desmond Walsh]

The forum prevented me from adding the URL of the relevant section in Chip Pearson's website. Look for /Excel/vbe.aspx on cpearson.com