Solved

Checking VBA References (fixing broken/missing references)

Posted on 2009-03-30
21
1,394 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:Gaute Rønningen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 4
  • +1
21 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24016747
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
 

Author Comment

by:Gaute Rønningen
ID: 24016786
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24016811
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Gaute Rønningen
ID: 24016884
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24016962
"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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 24016975
In VBA, string functions will fail with that error if *any* referenced library is missing.
0
 

Author Comment

by:Gaute Rønningen
ID: 24017003
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24017144
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
 

Author Comment

by:Gaute Rønningen
ID: 24017162
Yes it is running as I have a sub hiding certain sheets, which it does (and this is after the LibCheck sub).
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24017202
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
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 100 total points
ID: 24042992
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
 
LVL 45

Expert Comment

by:aikimark
ID: 24043016
...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
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 50 total points
ID: 24043196
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
 
LVL 45

Expert Comment

by:aikimark
ID: 24043470
@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
 

Author Comment

by:Gaute Rønningen
ID: 24047388
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24047475
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
 

Author Comment

by:Gaute Rønningen
ID: 24047694
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 350 total points
ID: 24047886
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24047974
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
 

Author Closing Comment

by:Gaute Rønningen
ID: 31564214
Divided the points between you all. :)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 24049621
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question