Solved

Checking VBA References (fixing broken/missing references)

Posted on 2009-03-30
21
1,296 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now