Programmatically list references in Excel
 

I have a workbook that uses a reference that may be missing. Is there some
way I can test for it before execution so that an error specifically about
the reference is displayed?

Thanks,
Barb Reinhardt

Programmatically list references in Excel
 

Check out Chip Pearson's Site

http://www.cpearson.com/Excel/MissingReferences.aspx

"Barb Reinhardt" wrote:

I have a workbook that uses a reference that may be missing. Is there some
way I can test for it before execution so that an error specifically about
the reference is displayed?

Thanks,
Barb Reinhardt

Programmatically list references in Excel
 

There's Missing, as in "not there at all", but there's also "not quite
right". You can check the IsBroken property of each reference which will
certainly tell you if "not there" but not necessarily if "not right",
typically due to some library version difference.

Dim wb As Workbook
Dim oRef As Object
Set wb = ThisWorkbook
For Each oRef In wb.VBProject.References
Debug.Print oRef.IsBroken, oRef.Name
Next

If you suspect there might be problem with references chances are you code
is going to break before you can even test. But you might just be able to
survive long enough if you fully qualify all your VB functions and
constants. This is particularly important with Strings & DateTime functions
but worth looking at absolutely everything (just one unqualified thing can
make it all fail).

s = VBA.Strings.Left$(

could mean spending a lot of time with the Object Browser

I used this approach where I might be programmatically adding a reference
which I know probably does not exist at the start.

Best approach of course is to work out why you anticipate a problem with
missing ref's and avoid the problem. If you are distributing a file that's
been saved with some later version library than that of the user, there
might be nothing you can do (depending on the particular ref), at least not
programmatically.

Regards,
Peter T


"Barb Reinhardt" wrote in message
...
I have a workbook that uses a reference that may be missing. Is there
some
way I can test for it before execution so that an error specifically about
the reference is displayed?

Thanks,
Barb Reinhardt

Programmatically list references in Excel
 

Try the ISBroken Property

Somthing like....


Debug.Print Application.VBE.ActiveVBProject.References.Item("A DODB").IsBroken



"Barb Reinhardt" wrote:

I have a workbook that uses a reference that may be missing. Is there some
way I can test for it before execution so that an error specifically about
the reference is displayed?

Thanks,
Barb Reinhardt