Tools | References - information about references

[L Mehl]

Hello --

Some code was posted here to list all current references to a worksheet.
Can someone tell me what .Major and .Minor mean?

Can I get other information about a reference, such as Version? What part
of the Object Browser contains this information?

Thanks for any help.

L Mehl
'------------------------------------

' Description, Name, GUID, #MAjor, #Minor, full path.

Dim refReference
Dim i As Integer, x As Integer
Dim iWorksheets As Integer, Y As Integer
Dim strResultsTableName As String

strResultsTableName = "Active VBE References"

'Count number of worksheets in workbook
iWorksheets = ActiveWorkbook.Sheets.Count

'Check for duplicate Worksheet name
i = ActiveWorkbook.Sheets.Count
For x = 1 To i
If Windows.Count = 0 Then Exit Sub
If UCase(Worksheets(x).Name) = UCase(strResultsTableName) Then
Worksheets(x).Activate
If Err.Number = 9 Then
Exit For
End If
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
'Exit Sub
End If
Next

'Add new worksheet at end of workbook
' where results will be located
Worksheets.add.Move after:=Worksheets(Worksheets.Count)

'Name the new worksheet and set up Titles
ActiveWorkbook.ActiveSheet.Name = strResultsTableName
ActiveWorkbook.ActiveSheet.Range("A1").Value = "Description"
ActiveWorkbook.ActiveSheet.Range("B1").Value = "Name"
ActiveWorkbook.ActiveSheet.Range("C1").Value = "GUID"
ActiveWorkbook.ActiveSheet.Range("D1").Value = "#Major"
ActiveWorkbook.ActiveSheet.Range("E1").Value = "#Minor"
ActiveWorkbook.ActiveSheet.Range("F1").Value = "Path"

ActiveCell.Offset(1, 0).Select
For Each refReference In Application.VBE.ActiveVBProject.References
With ActiveCell
.Value = refReference.Description
.Offset(0, 1).Value = refReference.Name
.Offset(0, 2).Value = refReference.GUID
.Offset(0, 3).Value = refReference.Major
.Offset(0, 4).Value = refReference.Minor
.Offset(0, 5).Value = refReference.FullPath
.Offset(1, 0).Select
End With
Next

' ...

 

[Rob van Gelder]

Major and Minor represent the portions of the version.

Add a reference to "Microsoft Visual Basic for Applications Extensibility"
library.

Then open the object browser. Choose VBIDE from the dropdown and take a look
at the Reference object.

From help on Major Property:
Returns a Long containing the major version number of the referenced type
library.

 

[L Mehl]

Rob --

Thank you.

Do I place code at the app's start which queries the user's system for
version numbers of the required DLLs and notifies the user if newer versions
need to be loaded??

If so, do you know if example code for such a process is available on the
web?

My code thus far derives the following for one of the reference files used:

for stdole2.tlb
#Major = 2
#Minor =0

do these relate to properties of the file:

File version is 2.10.3027.1
Product Version is 2.1 ?

Thanks for any help.

Larry Mehl

 

[Rob van Gelder]

Do I place code at the app's start which queries the user's system for

version numbers of the required DLLs and notifies the user if newer versions
need to be loaded??

You could do I guess. I don't do it, but maybe I should? I'll admit I don't
know much about version compatibility in this area.

More than likely you want to run your workbook on various PCs.
Just be aware that checking References is considered "Suspicious Activity"
by security features of later versions of Excel.

 

[Bob Phillips]

You need to be aware that this code does not tell you what versions of an
application that is installed on a machine, but just what is referenced. For
example, on my desktop machine I have XL97, Xl2000 and XL XP installed, but
the code shows me the Excel version that I am running from not all 3.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[L Mehl]

Thanks Rob and Bob --

This is DLL hell, isn't it.

What is the smart way to set up an app so it can be distributed to users
having various levels of Service Packs, versions of Excel, etc., so that the
app will be likely to run in those different environments?

The users of this app will most likely be able to understand the need to
update to a specific service pack, etc., if I am able to tell them what to
do.

I need to be able to tell them what to do.

Is one answer to "wrap" this Excel app in a dotNet Windows Forms
application?

Thanks for any further ideas.

Larry

 

[Bob Phillips]

Larry,

It sure is.

Dot.Net is a no-no, as Office apps don't fully support, 2003 was the first
to offer any sort of support.

There is only one sure fire way, and that is to develop your code using the
lowest version of Excel/VBA that is likely to be out in the field,
sacrificing some of the newer functionality. Backward compatibility exists,
forward never does.

If you want to use a newer version and your audience is amenable to
upgrading, send out installation notes that explain this explicitly, and
rely on them to do so before installing your app. Sometimes the manual
method works better<g>.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[L Mehl]

Thanks Bob --

I guess I shouldn't believe all the hype surrounding dotNET.

Instructing users in how to upgrade is probably the way we will go.

Larry