Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

 

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

       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

Code Samples for Businesses, Schools & Personal Use

Updated 05/11/2017              

 

For most of us, you set VBA references and then forget about them.

This is 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 & 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:

References List & Locations

Screenshots

Click to download an example VBA reference text file

ReferenceLogFile ReferencesImmediateWindow

ListVBAReferences - output to Immediate window

Click the image to view a larger version ...

LogVBAReferences - output to text file

Click the image to view a larger version ...

Return to Code Samples Page