Sometimes you need to unite two projects into one in VBA. In these two projects, if you are using similar “own built libraries” you are in a kind of trouble. The trouble is called Compile Error :Ambiguous name detected: name
Then you start looking, which one and where you should delete and which one and where you should be using. It is no fun at all.
Fortunately, there is a Library called Applications Extensibility 5.3. library which allows you to do fancy stuff with code in VBA. E.g., you may create a code writing code or you may read the code of your code. Long story short, you may loop through the VBA program, select all the modules and print the names of the sub-routines or the functions. And their “parents” if needed. Once you have them, you can simply sort them and see which one are repeatable. Then the whole task becomes easier. This is how the code looks like:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
'--------------------------------------------------------------------------------------- ' Purpose : Prints all subs and functions in a project ' Prerequisites: Microsoft Visual Basic for Applications Extensibility 5.3 library ' CreateLogFile ' How to run: Run GetFunctionAndSubNames, set a parameter to blnWithParentInfo ' If ComponentTypeToString(vbext_ct_StdModule) = "Code Module" Then ' ' Used: ComponentTypeToString from -> http://www.cpearson.com/excel/vbe.aspx '--------------------------------------------------------------------------------------- Option Explicit Private strSubsInfo As String Public Sub GetFunctionAndSubNames() Dim item As Variant strSubsInfo = "" For Each item In ThisWorkbook.VBProject.VBComponents If ComponentTypeToString(vbext_ct_StdModule) = "Code Module" Then ListProcedures item.name, False 'Debug.Print item.CodeModule.lines(1, item.CodeModule.CountOfLines) End If Next item CreateLogFile strSubsInfo End Sub Private Sub ListProcedures(strName As String, Optional blnWithParentInfo = False) 'Microsoft Visual Basic for Applications Extensibility 5.3 library Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim LineNum As Long Dim ProcName As String Dim ProcKind As VBIDE.vbext_ProcKind Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents(strName) Set CodeMod = VBComp.CodeModule With CodeMod LineNum = .CountOfDeclarationLines + 1 Do Until LineNum >= .CountOfLines ProcName = .ProcOfLine(LineNum, ProcKind) If blnWithParentInfo Then strSubsInfo = strSubsInfo & IIf(strSubsInfo = vbNullString, vbNullString, vbCrLf) & strName & "." & ProcName Else strSubsInfo = strSubsInfo & IIf(strSubsInfo = vbNullString, vbNullString, vbCrLf) & ProcName End If LineNum = .ProcStartLine(ProcName, ProcKind) + .ProcCountLines(ProcName, ProcKind) + 1 Loop End With End Sub Function ComponentTypeToString(ComponentType As VBIDE.vbext_ComponentType) As String 'ComponentTypeToString from http://www.cpearson.com/excel/vbe.aspx Select Case ComponentType Case vbext_ct_ActiveXDesigner ComponentTypeToString = "ActiveX Designer" Case vbext_ct_ClassModule ComponentTypeToString = "Class Module" Case vbext_ct_Document ComponentTypeToString = "Document Module" Case vbext_ct_MSForm ComponentTypeToString = "UserForm" Case vbext_ct_StdModule ComponentTypeToString = "Code Module" Case Else ComponentTypeToString = "Unknown Type: " & CStr(ComponentType) End Select End Function |
In stead of CreateLogFile you may simply print the file. With Debug.Print strSubsInfo Or search a bit for CreateLogFile in my GitHub repository, it should not be tough! A small hint – it is probably here.
That’s all! Cheers! 🙂