Updated 5 Nov 2017

For most of us, you set VBA references and then forget about them.
That works fine until you need to 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. The small size of the Access reference window doesn't help.

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

The first procedure ListVBAReferences lists references to the Immediate window.



The second routine LogVBAReferences saves these to a text file for future use.


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

Copy one or both procedures to a standard module

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 MsgBox("This will create a log file listing all VBA references used with the database. " & vbCrLf & vbCrLf & _
       "The log file will be saved as : " & vbCrLf  & _
       vbTab & strFileName & vbNewLine & vbCrLf  & _
       "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
       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

Click to download an example VBA reference text file



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Do let me know if you find any bugs in the application.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington                       Mendip Data Systems                       Last Updated 5 Nov 2017

Return to Example Databases Page

Return to Top