Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-11-2017, 01:50 PM   #1
ridders
but what do I know anyway
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, England
Posts: 872
Thanks: 23
Thanked 188 Times in 183 Posts
ridders will become famous soon enough
How to get a full list of VBA references & locations

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
Quote:
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
Attached Files
File Type: txt VBAReferenceLog.txt (3.6 KB, 50 views)

__________________
Colin
(Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
), Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!

Last edited by ridders; 05-12-2017 at 03:32 AM.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
mresann (06-16-2017)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Full list of VBA commands and arguements VBAn00bz Modules & VBA 3 01-29-2013 12:45 AM
Different Geographic Locations ASherbuck General 3 10-22-2008 10:32 PM
From and To locations ianbrister Queries 1 09-22-2008 09:56 AM
Having a list of record names displayed and opening the full record via click AWilderbeast Forms 21 09-21-2005 11:11 AM
References - in variable locations (with mde file) CutAndPaste General 2 03-19-2004 07:00 AM




All times are GMT -8. The time now is 07:39 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World