isladogs
MVP / VIP
- Local time
- Today, 18:02
- Joined
- Jan 14, 2017
- Messages
- 18,534
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
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:
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
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
Last edited: