Checking VBA References (fixing broken/missing references)

Hi experts,

I have been working on this problem a few years now (or so it feels) and haven't found a solution to it yet.

My problem is this:
I have an Excel spreadsheet that I plan to send out for testing and later for distributing to users with unknown configurations. I need a kind of checking references routine that searches through existing references and disables the references which are missing (also enables them later if they get installed at some later point). I thought the code I already had for this would do the trick (attached), but apparently our tester is getting the "Cannot find project or library." error when he runs it.

Any help appreciated,
eX.

Private Sub LibCheck() ' Source: MSDN
    Dim vbProj As VBProject ' This refers to your VBA project.
    Dim chkRef As Reference ' A reference.
    
   ' Refer to the activedocument's VBA project.
    Set vbProj = Application.ActiveWorkbook.VBProject
 
   ' Check through the selected references in the References dialog box.
    For Each chkRef In vbProj.References
        If (chkRef.IsBroken) Then
            ' Remove the reference from the reference list
            vbProj.References.Remove chkRef
        End If
    Next chkRef
End Sub

Open in new window

Gaute RønningenOwner, DeveloperAsked:
Who is Participating?
 
Rory ArchibaldCommented:
It woun't work for controls that you use on forms, but for say your ADO reference, you would use this:
Dim cnn as Object
Set cnn = CreateObject("ADODB.Connection")
 
instead of:
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
 
and there would be no need to set a reference. When developing the code you do lose the benefit of Intellisense and you have to define all constants, so you can't just use adCmdText for example, you have to use 1 or define your own constant.
0
 
Rory ArchibaldCommented:
Is that code in a module separate from all other functions? (note that actually declaring an object as VBProject or Reference assumes a reference to the VBA Extensibility library!)
0
 
Gaute RønningenOwner, DeveloperAuthor Commented:
It's in a separate module, I use a Sub in the same module ("Public Sub Init()") to run it on "Workbook.Activate" in MS Excel.

And yes, it uses the Visual Basic for Applications Extensibility 5.3 (VBIDE) which has already been added to the reference list.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Rory ArchibaldCommented:
But you are thereby assuming that *that* reference is OK!
Have you asked your tester which line causes the error? (and do you have code in the Workbook_Open event?)
0
 
Gaute RønningenOwner, DeveloperAuthor Commented:
Yes I assume that reference is there, cause I thought that all Office installations had it installed.

He's getting the error on:
     Space$(lpcbData)
Where lpcbData = 1024 - part of my registry read/write module, which should require any references at all...

And I used to have the code on Workbook_Open, but that caused the code not to run after the user had chosen to enable macros (after getting security warning). So I moved it to Workbook_Activate.
0
 
Rory ArchibaldCommented:
"And I used to have the code on Workbook_Open, but that caused the code not to run after the user had chosen to enable macros (after getting security warning)."
That makes no sense to me at all. And if the code won't run from there, it probably won't run from the Activate event either. Have you checked to see the code is actually running at startup? (I suspect not)
0
 
GrahamSkanRetiredCommented:
In VBA, string functions will fail with that error if *any* referenced library is missing.
0
 
Gaute RønningenOwner, DeveloperAuthor Commented:
The code does not run on startup. It runs after the user has enable macros - as I cannot get any macro code to run before the enable macro security dialog. When I put it on Workbook_Open *I assume* it would try to run it before the security dialog, but couldn't so it wouldn't run it at all. But when I put it on Workbook_Activate *I assume* it would run when user had chosen to enable macros.
0
 
Rory ArchibaldCommented:
The open event would fire before the activate event, so I don't see why you would move it. Clearly NO code is going to run before Excel asks you if you want to run code!! ;)
Anyway, have you checked that it is actually running at all?
0
 
Gaute RønningenOwner, DeveloperAuthor Commented:
Yes it is running as I have a sub hiding certain sheets, which it does (and this is after the LibCheck sub).
0
 
Rory ArchibaldCommented:
Well, all I can see that could be wrong initially is that this:
    Set vbProj = Application.ActiveWorkbook.VBProject
really ought to be:
    Set vbProj = ThisWorkbook.VBProject
 
to be sure you are looking at the right workbook.
0
 
aikimarkCommented:
I'd do something like a conversion of all your objects to generic late binding before releasing your VBA application to your users.  You could trap the CreateObject() error with an On Error statement and then take the appropriate action(s).
0
 
aikimarkCommented:
...and that means that there should be NO REFERENCES in your application beyond that which is minimally required to run the Office VBA application code.
0
 
GrahamSkanRetiredCommented:
I agree. I think that follows from what I said in comment: 24016975.

I suggest that you transfer your code into a stand-alone template/workbook which itself has minimal references.

One alternative is to use Late Binding for the set-up process.
0
 
aikimarkCommented:
@GrahamSkan

How about using conditional compilation to control the referenced (early binding) or non-referenced declaration statements?

I think reference removal or addition might be easier.
#Const IsTestEnv = -1
 
...
 
Sub Thing()
#If IsTestEnv Then
   Dim objDB As Database
#Else
   Dim objDB As Object
#End If
 
End Sub

Open in new window

0
 
Gaute RønningenOwner, DeveloperAuthor Commented:
Earlier I made the conclusion that I need an installation program to set up the right libraries to use. But I have no idea how to use any such program. (I.e. InstallShield or MSI package)

@aikimark:
I am unfamiliar with this "generic late binding" term. Does it mean that I set the spreadsheet to work with a minimum of references, then have them set up later by an automation in VBA?

@GrahamSkan:
Transfering the workbook to a new spreadsheet only causes trouble, as I had to do this earlier to get rid of some unexplained errors. Problems like formulas in the workbook got copied as the value and not the formula, so having to replace every formula manually - and I have a lot of those (basically using formulas to grab language strings from a "current language" column in a "language" sheet) It can be done, but it's more like my last option at the moment.

0
 
Rory ArchibaldCommented:
Late binding involves not setting extraneous references at all, declaring everything as the generic Object type and using literal values for constants that would have been in the referenced library (or declaring them yourself)
0
 
Gaute RønningenOwner, DeveloperAuthor Commented:
Could I please have an example or a link to an example on how to do this?

For example for one of my current references (attached screenshot)
(also the unselected ones in this picture is used).


references.jpg
0
 
Rory ArchibaldCommented:
Late binding involves not setting extraneous references at all, declaring everything as the generic Object type and using literal values for constants that would have been in the referenced library (or declaring them yourself)
0
 
Gaute RønningenOwner, DeveloperAuthor Commented:
Divided the points between you all. :)
0
 
aikimarkCommented:
If you check the box on the references dialog, it will show you just those references you currently have selected.

You might also use a version of the code snippet you posted with your question to (conditionally) add references during your testing and development.  However, it may be simpler to just copy/paste or uncomment the reference lines in the project with Notepad.

Doing conditional compiles will give you the intellisense feature while you are developing.  rorya has a good point to watch out for constants that are supplied with the reference (type library).  You can conditionally declare them with the same conditional compilation scheme I posted earlier.

Example:
#Const IsTestEnv = -1
 
...
 
#If IsTestEnv Then
  Dim cnn As ADODB.Connection
  Set cnn = New ADODB.Connection
#Else
  Dim cnn as Object
  Const adCmdText As Long = 1
  Set cnn = CreateObject("ADODB.Connection")
#End If

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.