VBIDE - Visual Basic for Applications Extensibility 5.3

I am intrigued by the idea of writing code that will report on or edit existing code. Unfortunately I am yet to master the use of VBIDE - my skill level on this is currently little-to-none.

So every so often, I post a new VBIDE challenge hoping that some forum user will solve it for me (either from kindness and/or for the love of the challenge).


Here's challenge #5.

I am currently trying to list all procedures in a project (thank you, I know that MZTools already has the ability to do this). I have run into a problem and I want to know how to fix it. See code at bottom of this post.

When I run the Sub "ListMacrosUsedInTWB", I eventually hit a runtime error 35 (Sub or Function not defined) on the line 
lngModLineNo = lngModLineNo + .ProcCountLines(strProcName, vbext_pk_Proc)
It keeps occurring on ThisWorkbook module on the following property 
Public Property Let ribbonUI(ByRef iRib As IRibbonUI)

Here's my VBIDE module:
'***************************************************************************************
' Name      : VBIDE
' Purpose   : For reporting on or editing a VB Project
' Updated   : 20141118
' Notes     : Dev use only. Remove from end user version.
'
'
' Requirements/Dependencies:
'   Reference Library = Microsoft Visual Basic for Applications Extensibility 5.3
'   Modules =
'           t_Arrays_1_ReadWrite ( Write1or2DArrToNewWB )
'           t_Arrays_3_Edit ( fnavarSafeTranspose )
'***************************************************************************************
Option Explicit
Option Private Module

Private Const mstrcVBIDE_GUID As String = "{0002E157-0000-0000-C000-000000000046}"
Private mstrRangeAddress As String

Public Sub ListMacrosUsedInTWB()
    Dim VBComp          As VBIDE.VBComponent
    Dim VBCodeMod       As VBIDE.CodeModule
    Dim lngModLineNo    As Long
    Dim strModName      As String
    Dim strProcName     As String
    Dim avarOutput      As Variant
    Dim lngArrCnt       As Long
    Dim strThisModule   As String
    Dim wbkOutput       As Excel.Workbook

    If fnblnRefCheck(mstrcVBIDE_GUID) = False Then
        Call MsgBEnableVBIDE
        Exit Sub
    End If

    'get name of this module - we dont want to check this
    strThisModule = Application.VBE.ActiveCodePane.CodeModule

    ReDim avarOutput(1 To 2, 1 To 1)
    lngArrCnt = 0

    For Each VBComp In ThisWorkbook.VBProject.VBComponents

        strModName = vbNullString
        strModName = VBComp.Name

        If strModName = strThisModule Then
            'skip
        Else
            Set VBCodeMod = ThisWorkbook.VBProject.VBComponents(strModName).CodeModule
            With VBCodeMod

                lngModLineNo = .CountOfDeclarationLines + 1

                Do Until lngModLineNo >= .CountOfLines
                    strProcName = vbNullString
                    strProcName = .ProcOfLine(lngModLineNo, vbext_pk_Proc)

                    'expand array then write to last record
                    lngArrCnt = lngArrCnt + 1
                    ReDim Preserve avarOutput(1 To 2, 1 To lngArrCnt)
                    avarOutput(1, lngArrCnt) = strModName
                    avarOutput(2, lngArrCnt) = strProcName

                    lngModLineNo = lngModLineNo + .ProcCountLines(strProcName, vbext_pk_Proc)
                Loop
            End With

            Set VBCodeMod = Nothing
        End If
    Next VBComp

    'output array to new workbook
    avarOutput = fnavarSafeTranspose(avarOutput)
    Call Write1or2DArrToNewWB(avarOutput, False, True, 2, 1, wbkOutput)
    With wbkOutput.Worksheets(1)
        .Name = "Macros"
        .Range("A1:B1").Value = Array("ModName", "ProcName")
        .UsedRange.Columns.AutoFit
    End With
End Sub

Public Sub ListNamesUsedInTWB()
'/ Credits: adapted from code by JosephP
'/ Useful function to run on a WB that has large accumulation of names
'/ You can filter the output to see which names are obselete.

    Dim wbName          As Name
    Dim strWhere        As String
    Dim strName         As String
    Dim avarOutput      As Variant
    Dim lngArrCnt       As Long: lngArrCnt = 0
    Dim strThisModule   As String
    Dim wbkOutput       As Excel.Workbook

    If fnblnRefCheck(mstrcVBIDE_GUID) = False Then
        Call MsgBEnableVBIDE
        Exit Sub
    End If

    Application.ScreenUpdating = False

    ReDim avarOutput(1 To 6, 1 To 1)

    'get name of this module - we dont want to check this
    strThisModule = Application.VBE.ActiveCodePane.CodeModule


    For Each wbName In ThisWorkbook.Names

        strWhere = vbNullString
        strName = wbName.Name

        'for sheet-level names remove the sheet name
        If InStr(strName, "!") > 0 Then
            strName = Mid$(strName, InStr(strName, "!") + 1)
        End If

        'skip sheet print areas from output
        If Not strName = "Print_Area" Then

            lngArrCnt = lngArrCnt + 1
            ReDim Preserve avarOutput(1 To 6, 1 To lngArrCnt)

            avarOutput(1, lngArrCnt) = strName

            'check VBA for name usage
            Select Case fnblnIsNameInUseInVBA(strThisModule, strName, ActiveWorkbook.VBProject, strWhere)
            Case True
                avarOutput(2, lngArrCnt) = True
                avarOutput(4, lngArrCnt) = strWhere
            Case False
                avarOutput(2, lngArrCnt) = False
            End Select

            'check WS formulas for name usage
            Select Case fnblnIsNameInUseInWS(strName, strWhere)
            Case True
                avarOutput(3, lngArrCnt) = True
                avarOutput(5, lngArrCnt) = strWhere
                avarOutput(6, lngArrCnt) = mstrRangeAddress
            Case False
                avarOutput(3, lngArrCnt) = False
            End Select

            strName = vbNullString
        End If
    Next wbName

    'output array to new workbook
    avarOutput = fnavarSafeTranspose(avarOutput)
    Call Write1or2DArrToNewWB(avarOutput, False, True, 2, 1, wbkOutput)
    With wbkOutput.Worksheets(1)
        .Name = "NamesInUse"
        .Range("A1:F1").Value = Array("Name", "In VBA?", "In WS?", "Module", "Sheet", "rngCell")
        .UsedRange.Columns.AutoFit
    End With

    Application.ScreenUpdating = True
End Sub

Private Function fnblnIsNameInUseInVBA(ByRef strThisModule As String, ByRef strName As String, ByVal vbp As _
    VBProject, ByRef strModule As String) As Boolean
'/ Credits: adapted from code by JosephP

    Dim vbc             As VBIDE.VBComponent
    Dim lngStartLine    As Long: lngStartLine = 1
    Dim lngStartColumn  As Long: lngStartColumn = 1

    For Each vbc In vbp.VBComponents
        If Not vbc.Name = strThisModule Then
            If vbc.CodeModule.Find(target:=strName, StartLine:=lngStartLine, _
                startcolumn:=lngStartColumn, endline:=-1, endcolumn:=-1, wholeword:=True) Then
                fnblnIsNameInUseInVBA = True
                strModule = vbc.Name
                Exit For
            End If
        End If
    Next vbc
End Function

Private Function fnblnIsNameInUseInWS(ByRef strName As String, ByRef strSheetName As String) As Boolean
    Dim wsSheet As Excel.Worksheet
    Dim rngCell As Excel.Range

    For Each wsSheet In Worksheets

        wsSheet.Activate

        If fnblnFoundOnPage(strName, wsSheet.Name) Then
            fnblnIsNameInUseInWS = True
            strSheetName = wsSheet.Name
            Exit For
        End If

        If fnblnIsNameInUseInWS Then
            Exit For
        End If
    Next wsSheet
End Function

Private Function fnblnFoundOnPage(ByVal strFindString As String, ByVal strSheetName As String) As Boolean
'/ adapted from code by Ron de Bruin http://www.rondebruin.nl/win/s9/win006.htm
    Dim Rng As Excel.Range

    If Trim(strFindString) <> "" Then
        With Sheets(strSheetName).UsedRange
            
            Set Rng = .Find(What:=strFindString, after:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            
            If Not Rng Is Nothing Then
                fnblnFoundOnPage = True
                mstrRangeAddress = Rng.Address
            Else
                fnblnFoundOnPage = False
            End If
        End With
    End If
End Function

Private Sub MsgBEnableVBIDE()
    Dim Answer As Integer
    Dim Msg As String

    Msg = "This procedure can not continue. You need to enable the Microsoft VBA extensibility reference set." & vbNewLine & _
        " 1. In the VBA Editor, choose References from the Tools menu." & vbNewLine & _
        " 2. Scroll through list of Available References " & _
        "and make sure the Microsoft Visual Basic for Applications Extensibility check box is selected." & _
        vbNewLine & " 3. Close the dialog box."

    Answer = MsgBox(Msg, vbCritical)
End Sub

Private Function fnblnRefCheck(ByVal strGUID As String) As Boolean
    Dim lngRefCnt As Long
    With ThisWorkbook.VBProject.References
        For lngRefCnt = 1 To .Count
            If .Item(lngRefCnt).GUID = strGUID Then
                fnblnRefCheck = True
                Exit Function
            End If
        Next lngRefCnt
    End With
End Function