I try to use Some.dll version 5.0 within my code. However, Excel has multiple reference for the same dll, with earlier versions like 4.0 If I run: Set co = CreateObject("SomeClass.Object") even if I created a reference in Tools->References for the newest Some.dll, it calls one of the oldest dll, that does not have some functionality.
I do not have admin right to change registry, remove programs from my computer.
Is it possible somehow set reference to the newest version dll?
I tried: Private Declare Function LoadLibrary Lib "kernel32"... lb = LoadLibrary(C:\somepath\Some.dll) ' lb is the 5.0 version dll that should be used set co = CreateObject("lb.Object") - however, it is not working ("ActiveX... can't create object")
and looked through all the net, but could not find advise, how to create object with a specific dll path.
You need to find the full class name. ActiveX Data Objects provides a good example. Excel shows several possible references for different versions (2.0, 2.1, etc) in Tools > References:
If you use CreateObject to create a "ADODB.Recordset" object, you will get whatever the most recent version is, as set in the CurVer key of the class key in the registry. In this case: HKEY_CLASSES_ROOT\ADODB.RecordSet\CurVer = "ADODB.Recordset.6.0", so what is actually created is a "ADODB.Recordset.6.0" object, which corresponds to ADO v6.0. If I want to directly create the older version, I can do that by using the full name of the old version: "ADODB.Recordset.1".
Versions of ADODB.Recordset in registry:
In the following example my workbook has a reference to ADO v6.0, but I can still create a Recordset.1 object using CreateObject. We can see that rs1 really is a Recordset.1 because an error is raised when we try to call the StayInSync property, which exists for Recordset.6.0 but not for Recordset.1:
Sub TestADOVersions() Dim rsExplicit As ADODB.Recordset Dim rsDefault Dim rs6 Dim rs1 Set rsExplicit = New ADODB.Recordset Set rsDefault = CreateObject("ADODB.Recordset") Set rs6 = CreateObject("ADODB.Recordset.6.0") Set rs1 = CreateObject("ADODB.Recordset.1") Debug.Print rsExplicit.StayInSync 'Ok Debug.Print rsDefault.StayInSync 'Ok Debug.Print rs6.StayInSync 'Ok Debug.Print rs1.StayInSync 'Error --> StayInSync not a member of Recordset.1 End Sub
- Edited by Joshua Honig Wednesday, December 28, 2011 10:14 PM
You can add a reference like this
Set VBP = ThisWorkbook.VBProject
VBP.References.AddFromGuid (GUID as string, Major as Long, Minor as Long)
VBP.References.AddFromFile (FileName as string) ' path & dll-name
Be sure to remove any similarly named reference first, eg
On Error resume next
Set rf = VBP.References("myDllName")
If Not rf Is Nothing then
' maybe check major.minor before removing in case already got the good reference
On Error goto 0
Security settings will need to allow access to VBA Project
After setting the reference any declarations to the object model should be linked to the expected library, exposing the methods etc of that library. You should see it in Object Browser and get the approriate intellisense.
However the reference does not necessarily ensure that CreateObject or New will load the related object if there are other dll versions with the same GUID. Adding the version number with CreateObject as jmh suggests may help if the similarly named dlls have different GUIDs, try it.
- Edited by Peter Thornton (Excel MVP 2008-13)Moderator Wednesday, December 28, 2011 10:55 PM
Thanks, I tried this, but my case is a bit more complicated.
I cannot see the shown versioning in HKCR, all the CLSIDs point to the latest dll version.
Here the reference name as well as the versioning differs, however the dll name and the class name is common.
The only problem might come from HKCR\..\TypeLib, where I can see one ID that has 3 entry with 3 versions, that migh cause problem for Excel referencing - assuming TypeLib is the library from that Excel generates references, and not directly from CLSID, however I am not sure how this works exactly.