Identifying all called Personal.xlsb macros

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

Dim mdl as Object
	With ws												' Declare ws as Workbook	
       	   For i = 1 To .VBProject.VBComponents.Count
               Set mdl = .VBProject.VBComponents(i)
               If .VBProject.VBComponents(i).CodeModule.CountOfLines > 0 Then
                   lcount = .VBProject.VBComponents(i).CodeModule.CountOfLines
                   strMod = .VBProject.VBComponents(mdl.Name).CodeModule.Lines(1, lcount)       	 ' String strMod contains source code
	       End if
	   Next i
	End with

2 Getting a list of all macro names

Dim Component       As VBComponent
	For Each Component In ws.VBProject.VBComponents							    ' Declare ws as Workbook	
            With Component.CodeModule
                Count = .CountOfDeclarationLines + 1
                Do Until Count >= .CountOfLines
                    MyList(N) = .ProcOfLine(Count, vbext_pk_Proc)					    ' Macro name added to array MyList
                    Count = Count + .ProcCountLines(.ProcOfLine(Count, vbext_pk_Proc), vbext_pk_Proc)
               	    f.WriteLine MyList(N)								    ' Save in disk file (A.1 or B.1)
                    If Count < .CountOfLines Then N = N + 1
                Loop
	    End With
            N = N + 1
    	Next

6 Add a new module to the workbook

Set VBProj = ws.VBProject
    	Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)     					'Adding component module type = "Module"
    	VBComp.Name = module_name									'Any name in string module_name

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

	Dim CodePan	 	As VBIDE.CodeModule
	Dim macro_code		As String
'	
'	Put macro code into string macro_code  (Not shown here)
	Set CodePan = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
	With CodePan
   		 .InsertLines .CountOfLines + 1, macro_code
	End With

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.

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