External Reference Check

[fredlo2010]

Hi everyone,

I am not sure this can be done; but lately I have been having some issues with this. Sometimes I use reference to other libraries in VBA so I can use intelisense, and get familiar with the objects, etc..

Problem is that I need to make sure that I turn those references off before deploying the project otherwise I will get "weird errors" things that do not make sense basically. "There is no file" but hey there is a file in that location I can see it...

Is there any way to check for references before wrapping up a project. The only thing I can think of is always deploying through a procedure and showing a message "Hey do not forget to check the references" at least until it becomes instinctive to some extent (I always remember to check for the objects that use external).

Thanks a lot for the help :)

[Bob Phillips]

Build using early binding, release with late binding.

Develop Early, Release Late
http://www.xldynamic.com/source/xld.EarlyLate.html

[fredlo2010]

Thanks xld,

That's what I have been doing but I keep forgetting this line at the end of the article


you can remove the reference to the Outlook type library

:(

[Zack Barresse]

Here is a routine, I generally keep it in my Personal.xlsb file, that you can use to list all references. It looks for an active workbook and adds a worksheet to it. If no workbook is active, it looks to the book it's being called from and lists them in a message box instead.

Sub ListReferences()
'
'For early binding, reference needed for:
' Microsoft Visual Basic for Applications Extensibility 5.3
'
Dim Project As Object ' VBIDE.VBProject
Dim Ref As Object 'VBIDE.Reference
Dim TargetBook As Workbook
Dim DestinationSheet As Worksheet
Dim IsVisible As Boolean
Dim RefCount As Long
Dim RefList As String
Dim TargetName As String
Dim Prompt As String
Dim Refs() As Variant

If ActiveWorkbook Is Nothing Then Set TargetBook = ThisWorkbook
If TargetBook Is Nothing Then Set TargetBook = ActiveWorkbook

TargetName = Left(TargetBook.Name, InStrRev(TargetBook.Name, ".") - 1)
Set Project = TargetBook.VBProject
IsVisible = Not TargetBook.IsAddin
If IsVisible Then IsVisible = UCase(TargetName) <> "PERSONAL"

RefCount = 1
ReDim Preserve Refs(1 To 9, 1 To RefCount)
Refs(1, RefCount) = "Name"
Refs(2, RefCount) = "Description"
Refs(3, RefCount) = "Full Path"
Refs(4, RefCount) = "Is Built-In"
Refs(5, RefCount) = "GUID"
Refs(6, RefCount) = "Is Broken"
Refs(7, RefCount) = "Reference Type"
Refs(8, RefCount) = "Major Number"
Refs(9, RefCount) = "Minor Number"

For Each Ref In Project.References
RefCount = RefCount + 1
ReDim Preserve Refs(1 To 9, 1 To RefCount)
Refs(1, RefCount) = Ref.Name
Refs(2, RefCount) = Ref.Description
Refs(3, RefCount) = Ref.FullPath
Refs(4, RefCount) = Ref.BuiltIn
Refs(5, RefCount) = Ref.GUID
Refs(6, RefCount) = Ref.IsBroken
Refs(7, RefCount) = IIf(Ref.Type = 0, "Project", "Type Library") '0 = vbext_rk_Project
Refs(8, RefCount) = Ref.Major
Refs(9, RefCount) = Ref.Minor
Next Ref

If RefCount = 1 Then
MsgBox "No references were found", vbExclamation, "Whoops!"
GoTo Exit_ListReferences
End If

If IsVisible Then
Set DestinationSheet = TargetBook.Worksheets.Add(After:=TargetBook.Worksheets(TargetBook.Worksheet s.Count))
DestinationSheet.Cells(1, 1).Resize(RefCount, UBound(Refs, 1)).Value = Application.Transpose(Refs)
DestinationSheet.Cells.EntireColumn.AutoFit
Else
RefList = vbNullString
For RefCount = 2 To UBound(Refs, 2)
RefList = RefList & vbNewLine
RefList = RefList & "Name: " & Chr(34) & Refs(1, RefCount) & Chr(34) & " (" & Refs(7, RefCount) & "), " & Refs(2, RefCount) & vbNewLine
RefList = RefList & "Built-In: " & Refs(4, RefCount) & ", Is Broken: " & Refs(6, RefCount) & vbNewLine
Next RefCount
Prompt = UBound(Refs, 2) - 1 & " references were found, but we can't output to an add-in (not all may show)" & vbNewLine & RefList
MsgBox Prompt, vbInformation, "Complete!"
End If

Exit_ListReferences:
Set DestinationSheet = Nothing
Set TargetBook = Nothing
Set Ref = Nothing
Set Project = Nothing

End Sub

I'd still recommend what Bob said though. If you're sending to others, it's generally always best to use late binding, but easiest to develop in early binding.

[fredlo2010]

wow wow and wow!

Thanks a lot Zack. This is amazing. Exactly what I was looking for. I will modify it a little with your permission to just show me if I have anything besides the regular references that I need.

I will post it as soon as I get home and work on it.

Of course I will keep developing in early and releasing in late. but this will help me work on.

This will be a very important project I am working on to release my works.
- Check and Update versions (done)
- Check for References ( Zack's code)
- Release readonly to production ( done)

I will keep this growing to keep releasing issues to a minimum.

Thanks

[Zack Barresse]

Always glad to help. No need for permission in modifying code posted here, it's given freely. :)