Tuesday, 16 January 2018

VBA - List the Tools->References needed for a module

So lots of code samples on this blog and it is necessary to list the Tools->References required for reader to add to compile the sample. I have to write this so often I've chosen to write a helper function. Might as well share. It actually lists for the whole project so the output might need editing. Common libraries like VBA, Excel etc. are excluded from output because they are given.


Option Explicit

Function ToolsReferences() As String
    Dim oRefs As Object 'VBIDE.References
    Set oRefs = ThisWorkbook.VBProject.References
    
    Dim dicLines As Object
    Set dicLines = CreateObject("Scripting.Dictionary")
    
    dicLines.Add dicLines.Count, "'* Tools->References"
    
    Dim vRefLoop As Variant
    For Each vRefLoop In oRefs
        Dim oRef As Object 'VBIDE.Reference
        Set oRef = vRefLoop
        
        If VBA.InStr(1, "|VBA|Office|stdole|Word|Excel|", "|" & oRef.Name & "|", vbTextCompare) = 0 Then
                
            dicLines.Add dicLines.Count, "'" & oRef.Name & vbTab & oRef.Description & vbTab & oRef.FullPath
        
        End If
    Next vRefLoop

    ToolsReferences = VBA.Join(dicLines.Items, vbNewLine)

End Function


1 comment: