Dim as Reference

[argenta]

I've developed an Excel application and I want to manipulate the library references using the AddFromFile method.

However, when I type a statement such as :-

Dim Ref as Reference

It doesn't recognise the type Reference.

This seems strange as there's an example in John Walkenbach's book which has an example which includes this.

What am I doing wrong?

 

[tusharm]

You need to set a reference to the MS VBA Extensibility library. The current version is 5.3

 

[NateO]

Hello,

Or you could use a late bind, either way, considering adding with a GUID, there are examples of both here:

http://www.mrexcel.com/board2/viewtopic.php?t=57771

 

[argenta]

Thanks guys that's very helpful.

I have to admit I'm on the edge of my understanding of VBA etc but I'm very keen to get to understand it. Could either of you either give me a succint explanation of late v early binding or point me in the direction of a good source?

Thanks again.

 

[Smitty]

These links explain it pretty well: http://word.mvps.org/FAQs/InterDev/EarlyvsLateBinding.htm

http://peltiertech.com/Excel/EarlyLateBinding.html

The second link has more links.

Hope that helps,

Smitty

 

[Joe Was]

Does anyone have a good description of the:

Major & Minor GUID Reference Versions?

I thought I new, untill I played with my updated version of "NateO's" posted code:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> setReference()
<SPAN style="color:#00007F">Dim</SPAN> i%, myRefsCnt%, myType$, myValid$, mySpace$, myRef <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
myRefsCnt = ThisWorkbook.VBProject.References.Count
mySpace = <SPAN style="color:#00007F">String</SPAN>(22, " ")

<SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> myRefsCnt
<SPAN style="color:#00007F">Set</SPAN> myRef = ThisWorkbook.VBProject.References(i)

<SPAN style="color:#00007F">If</SPAN> myRef.BuiltIn = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
myType = "Automatically Built-In Reference Type!"
<SPAN style="color:#00007F">Else</SPAN>
myType = "Custom Reference Type!"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">If</SPAN> myRef.IsBroken = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
myValid = "Points to a Valid Reference!"
<SPAN style="color:#00007F">Else</SPAN>
myValid = "Points to an In-Valid Reference!"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> myRef <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
MsgBox "Reference:    " & i & "  of:  " & myRefsCnt & vbLf & _
mySpace & myValid & vbLf & _
mySpace & myType & vbLf & vbLf & _
"Name:            " & myRef.Name & vbLf & _
"Description:    " & myRef.Description & vbLf & _
"Folder:            " & myRef.fullpath & vbLf & vbLf & _
"GUID:            " & """" & myRef.GUID & """" & ", " & _
myRef.Major & ", " & myRef.Minor
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> i

<SPAN style="color:#00007F">Set</SPAN> myRef = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


I thought the "Major" version was the total number of this type of object reference that could be referenced and the "Minor" version was the version of the object reference to actually use?

But, in runing the code this does not test out to be the case?

 

[NateO]

Right, it's not nearly as consistent as I wish it was, as my tests with ADO, Excel, Access and XML show here: Link

Still, neither is adding by file...

 

[Joe Was]

I think your posted code works just fine!

The registry index does not need to be serial or in order, so you can have 2 objects of the same type listed with versions: 2,0 & 3,2 with no other versions listed. [I thought the Version was litteral, like: 2,0 = Version: 2.0] Or the Major and Minor Version was the index to registry of that Object, like: Version Reference "2,0" was the top [1 or first] listed reference [Minor = 0 + 1] of 3 [Major = 2 + 1] Objects of that name.

My problem is when I used it on a test project, the Major and Minor versions information returned does not fit what I thought these properties repesented, so the problem is in my understanding of:

Major & Minor GUID Reference Versions

and was looking for some ideas on what Major & Minor do mean?

As the Major & Minor Properties returned numbers that do not fit any model I thought they did represent, when I compaired all the returned values for a pattern, using the above code?

 

[tusharm]

Unless and until you run into problems with early binding, stick with it. There are a few benefits to late binding but, by and large, the benefits of early binding more than outweigh them. In all cases, I do development with early binding. For code that is distributed (or likely to be distributed) to multiple computers, I switch to late binding for distribution purposes, To implement that I use conditional compilation along the lines of the regular expression functions at
http://www.tmehta.com/regexp/add_code.htm

That guarantees that my code will work identically with either early or late binding.

That said, specific to your task, IMO you should not modify the references through VBA. It is part of the user's configuration and code has no business messing around with a user's configuration behind her/his back. If the user configuration does not match your requirement, simply inform the user that "file xyz should be referenced to use this software." Also, AFAIK, newer versions of XL consider this to be a potential security hazard.

Thanks guys that's very helpful.

I have to admit I'm on the edge of my understanding of VBA etc but I'm very keen to get to understand it. Could either of you either give me a succint explanation of late v early binding or point me in the direction of a good source?

Thanks again.

 

[NateO]

Hi Joe,

I see two problems, if you look at my two posts (the one I linked to and the one that follows).

With ADO, the Major and Minor versions follow. What's the problem then? The GUID strings are different! There's a pattern in the first 3, but the 4th? Not so much...

Now look at the Excel one in my next post to that thread, for some reason Microsoft is incrementing Minor versions where we expect them to be incrementing on Major versions... Access looks as we expect it, sort of.

XML has the same problem as ADO, the Major versions work as we expect, but the GUID strings are completely different.

Perfect world? Same GUID and the Major and Minor references are intuitive. Reality? Not so much. Bottom line? If you're going to work with GUIDs, you had better test them.

I could see some valid reasons for wanting to add references on the fly; not all libraries are created the same, perhaps you want to automate specifically to a specific library. E.g., an ADO Stream: Link.

But, my opinion is that if you do toggle someone's references, you'd better restore back to the original settings when you're done, much like you should if you toggle a Calculation setting in Excel, as mentioned in my last thought/code here. Every post where I have shown someone how to set a reference, I advise how to remove that reference.