How to get a full list of VBA references & locations (1 Viewer)

Status
Not open for further replies.

isladogs

MVP / VIP
Local time
Today, 13:11
Joined
Jan 14, 2017
Messages
18,186
For most of us, you set references and then forget about them.

This is fine until you need to re-create a fresh copy of your database or deal with reference issues on a client machine.

In such cases, it can be difficult to identify where certain references are located and the small size of the reference window doesn't help.

I've adapted original code from the DevHut website to develop 2 simple routines giving a list of VBA references & their locations.

Both routines require the use of the 'Microsoft Visual Basic for Applications Extensibility 5.3' reference library


The first routine lists references to the Immediate window
REFERENCES
-------------------------------------------------
Description: 'Visual Basic For Applications', Name: 'VBA', FullPath: 'C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL', Guid: {000204EF-0000-0000-C000-000000000046}, Type: '0', BuiltIn: True, IsBroken: False, Major: 4, Minor: 1
Description: 'Microsoft Access 14.0 Object Library', Name: 'Access', FullPath: 'C:\Program Files\Microsoft Office\Office14\MSACC.OLB', Guid: {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}, Type: '0', BuiltIn: True, IsBroken: False, Major: 9, Minor: 0
Description: 'OLE Automation', Name: 'stdole', FullPath: 'C:\Windows\System32\stdole2.tlb', Guid: {00020430-0000-0000-C000-000000000046}, Type: '0', BuiltIn: False, IsBroken: False, Major: 2, Minor: 0
Description: 'Microsoft Office 14.0 Access database engine Object Library', Name: 'DAO', FullPath: 'C:\Program Files\Common Files\Microsoft Shared\OFFICE14\ACEDAO.DLL', Guid: {4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28}, Type: '0', BuiltIn: False, IsBroken: False, Major: 12, Minor: 0
Description: 'Microsoft Office 14.0 Object Library', Name: 'Office', FullPath: 'C:\Program Files\Common Files\Microsoft Shared\OFFICE14\MSO.DLL', Guid: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}, Type: '0', BuiltIn: False, IsBroken: False, Major: 2, Minor: 5
Description: 'Microsoft ActiveX Data Objects 6.1 Library', Name: 'ADODB', FullPath: 'C:\Program Files\Common Files\System\ado\msado15.dll', Guid: {B691E011-1797-432E-907A-4D8C69339129}, Type: '0', BuiltIn: False, IsBroken: False, Major: 6, Minor: 1
Description: 'Microsoft HTML Object Library', Name: 'MSHTML', FullPath: 'C:\Windows\System32\mshtml.tlb', Guid: {3050F1C5-98B5-11CF-BB82-00AA00BDCE0B}, Type: '0', BuiltIn: False, IsBroken: False, Major: 4, Minor: 0
Description: 'Microsoft Internet Controls', Name: 'SHDocVw', FullPath: 'C:\Windows\System32\ieframe.dll', Guid: {EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}, Type: '0', BuiltIn: False, IsBroken: False, Major: 1, Minor: 1
Description: 'Microsoft Forms 2.0 Object Library', Name: 'MSForms', FullPath: 'C:\WINDOWS\system32\FM20.DLL', Guid: {0D452EE1-E08F-101A-852E-02608C4D0BB4}, Type: '0', BuiltIn: False, IsBroken: False, Major: 2, Minor: 0
Description: 'Microsoft Visual Basic for Applications Extensibility 5.3', Name: 'VBIDE', FullPath: 'C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB', Guid: {0002E157-0000-0000-C000-000000000046}, Type: '0', BuiltIn: False, IsBroken: False, Major: 5, Minor: 3
Description: 'Microsoft XML, v6.0', Name: 'MSXML2', FullPath: 'C:\Windows\System32\msxml6.dll', Guid: {F5078F18-C551-11D3-89B9-0000F81FE221}, Type: '0', BuiltIn: False, IsBroken: False, Major: 6, Minor: 0
-------------------------------------------------
11 references found, 0 broken.

This is OK but in my view not very easy to read

The second routine creates a log file VBAReferenceLog.txt with the reference details in the same folder as your database.
This is both easier to read and gives you a 'hard' copy.
See attached file

If any references are missing, these will be marked as BROKEN

Copy whichever routine you prefer to a module


The code for each routine is below:

Code:
Option Compare Database
Option Explicit

Sub ListVBAReferences()

'===============================================
'Author: Colin Riddington, MendipDataSystems
'Date: 08/05/2017

'Adapted from code by Dirk Goldgar/Tom van Stiphout

'NOTE:
'This requires the use of the VBA reference library:
'Microsoft Visual Basic for Applications Extensibility 5.3
'==================================================

    On Error Resume Next
 
    Dim VBAEditor As VBIDE.VBE
    Dim VBProj As VBIDE.VBProject
    Dim ref As VBIDE.Reference
    
    Dim strRefDescription As String
    Dim lngCount As Long
    Dim lngBrokenCount As Long
    Dim blnBroken As Boolean
    
    Set VBAEditor = Application.VBE
    Set VBProj = VBAEditor.ActiveVBProject
    Set ref = VBProj.Reference
 
    Debug.Print "REFERENCES"
    Debug.Print "-------------------------------------------------"
 
    For Each ref In VBProj.References
        lngCount = lngCount + 1
        strRefDescription = vbNullString
        
        
        Err.Clear
        strRefDescription = strRefDescription & "Description: '" & ref.Description & "'"
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & "Description: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Name: '" & ref.Name & "'"
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Name: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
         
        Err.Clear
        strRefDescription = strRefDescription & ", FullPath: '" & ref.FullPath & "'"
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", FullPath: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Guid: " & ref.GUID
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Guid: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Type: '" & ref.Type & "'"
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Type: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", BuiltIn: " & ref.BuiltIn
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", BuiltIn: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", IsBroken: " & ref.IsBroken
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", IsBroken: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Major: " & ref.Major
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Major: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        strRefDescription = strRefDescription & ", Minor: " & ref.Minor
        If Err.Number <> 0 Then
            strRefDescription = strRefDescription & ", Minor: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        If blnBroken Then
            lngBrokenCount = lngBrokenCount + 1
            strRefDescription = "*BROKEN* " & strRefDescription
        End If
 
        Debug.Print strRefDescription
 
    blnBroken = False
    Next ref
 
    Debug.Print "-------------------------------------------------"
    Debug.Print lngCount & " references found, " & lngBrokenCount & " broken."
 
    If lngBrokenCount <> 0 Then
        MsgBox "Broken References were found in the VBA Project!", vbCritical + vbOKOnly
    End If
End Sub

Public Sub LogVBAReferences()

'==================================================
'Author: Colin Riddington, MendipDataSystems
'Date: 08/05/2017

'Adapted from code by Dirk Goldgar/Tom van Stiphout(DevHut website)

'NOTE:
'This requires the use of the VBA reference library:
'Microsoft Visual Basic for Applications Extensibility 5.3
'==================================================

    On Error Resume Next
    
    Const ForReading = 1, ForWriting = 2, ForAppending = 8

    Dim objFSO As Object
    Dim logStream As Object
     
    Dim VBAEditor As VBIDE.VBE
    Dim VBProj As VBIDE.VBProject
    Dim ref As VBIDE.Reference
    
    Dim strRefDescription As String
    Dim lngCount As Long
    Dim lngBrokenCount As Long
    Dim blnBroken As Boolean
    
    strFileName = strCurrentDBDir & "VBAReferenceLog.txt"
    
    If FormattedMsgBox("This will create a log file listing all VBA references used with the database.     " & _
        "@The log file will be saved as : " & vbNewLine & _
        vbTab & strFileName & vbNewLine & vbNewLine & _
        "Are you sure you want to do this now?        @", _
            vbQuestion + vbYesNo, "Create reference log?") = vbNo Then Exit Sub

    Set VBAEditor = Application.VBE
    Set VBProj = VBAEditor.ActiveVBProject
    Set ref = VBProj.Reference
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    
    'check if VBA reference text file exists
    If Dir(strFileName) <> "" Then
        'Delete current log File
        Kill strFileName
    End If
   
    'Create text file & enter version info
    dblStart = CDbl(Now())
    Set logStream = objFSO.OpenTextFile(strFileName, ForWriting, True)
    logStream.WriteLine ""
    
    logStream.WriteLine " VBA Reference Log File" & vbNewLine & _
        "================================" & vbNewLine & vbNewLine & _
        "Program Path: " & Application.CurrentProject.FullName & vbNewLine & _
        "Program Name: " & GetProgramName() & vbNewLine & _
        "Version: " & GetVersionNumber() & vbNewLine & _
        "Date/Time: " & Date & " - " & Time() & vbNewLine & vbNewLine
    
 
    'now loop through references collection and log info for each
    logStream.WriteLine "REFERENCES"
    logStream.WriteLine "-------------------------------------------------"
    logStream.WriteLine ""
 
    For Each ref In VBProj.References
        lngCount = lngCount + 1
        strRefDescription = vbNullString
        
        Err.Clear
        logStream.WriteLine " Description: '" & ref.Description & "'"
        If Err.Number <> 0 Then
            logStream.WriteLine " Description: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        logStream.WriteLine " Name: '" & ref.Name & "'"
        If Err.Number <> 0 Then
            logStream.WriteLine " Name: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
         
        Err.Clear
        logStream.WriteLine " FullPath: '" & ref.FullPath & "'"
        If Err.Number <> 0 Then
            logStream.WriteLine " FullPath: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        logStream.WriteLine " Guid: " & ref.GUID
        If Err.Number <> 0 Then
            logStream.WriteLine " Guid: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
        
        Err.Clear
        logStream.WriteLine " Version (Major/Minor): " & ref.Major & "." & ref.Minor
        If Err.Number <> 0 Then
            logStream.WriteLine " Version (Major/Minor): " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        logStream.WriteLine " Type: '" & ref.Type & "'"
        If Err.Number <> 0 Then
            logStream.WriteLine " Type: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        logStream.WriteLine " BuiltIn: " & ref.BuiltIn
        If Err.Number <> 0 Then
            logStream.WriteLine " BuiltIn: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        Err.Clear
        logStream.WriteLine " IsBroken: " & ref.IsBroken
        If Err.Number <> 0 Then
            logStream.WriteLine " IsBroken: " & "(error " & Err.Number & ")"
            blnBroken = True
        End If
 
        If blnBroken Then
            lngBrokenCount = lngBrokenCount + 1
            strRefDescription = "*BROKEN* " & strRefDescription
        Else
           
        End If
 
        'Debug.Print strRefDescription
        
        logStream.WriteLine "-------------------------------------------------"
        logStream.WriteLine ""
 
 
    blnBroken = False
    Next ref
 
    logStream.WriteLine lngCount & " references found, " & lngBrokenCount & " broken."
 
    If lngBrokenCount <> 0 Then
        MsgBox "Broken References were found in the VBA Project!", vbCritical + vbOKOnly
    End If
    
    logStream.Close

    'open log file
    Call fHandleFile(strFileName, WIN_NORMAL)
    
    
End Sub
 

Attachments

  • VBAReferenceLog.txt
    3.6 KB · Views: 929
Last edited:
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom