CreateObject and dll selection without CLSID RRS feed

  • Question

  • 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.

    Wednesday, December 28, 2011 9:03 PM


  • I should use function called CreateObjectFromFile("path\dll", "Class in dll")

    Thanks for all your help!

    • Marked as answer by stoneclub Thursday, December 29, 2011 11:29 AM
    Thursday, December 29, 2011 11:28 AM

All replies

  • 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
    Wednesday, December 28, 2011 10:12 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
    VBP.References.Remove rf
    end if
    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.

    Peter Thornton

    Wednesday, December 28, 2011 10:46 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.

    Wednesday, December 28, 2011 11:32 PM
  • I should use function called CreateObjectFromFile("path\dll", "Class in dll")

    Thanks for all your help!

    • Marked as answer by stoneclub Thursday, December 29, 2011 11:29 AM
    Thursday, December 29, 2011 11:28 AM
  • Where is this function? I cannot find it in Outlook VBA...
    Friday, April 22, 2016 5:17 PM