Reference Object Library within user defined function

[conradin.flurin]

Hello

I have written a UDF (Usder defined function), which accesses a access database and runs an SQL call in it and returns the data item.

I'd like to automatically reference the "Microsoft ActiveX Data Objects Library" I use for this UDF and I have written a sub that does this

Sub reference_ADODB_library()
    Dim i As Integer
    With ActiveWorkbook.VBProject
        For i = 1 To .References.Count
            If .References(i).Name = "ADODB" Then
                Exit Sub
            End If
        Next
        .References.AddFromGuid "{B691E011-1797-432E-907A-4D8C69339129}", 6, 1
    End With
End Sub

This Sub works alright, however my UDF function fails unless I first reference the object library manually before using the UDF.

Any thoughts on this?

Cheers!

 

[RoryA]

Why don't you late bind your UDF instead?

 

[conradin.flurin]

Why don't you late bind your UDF instead?

Hello Rory

The reason I didn't use late binding was because the this part of my code failed

    With cnt
        .Open (stConn)
        .CursorLocation =  [FONT=Arial][SIZE=2]adUseClient[/SIZE][/FONT]
    End With

but of course, I can just use the actual constant

    With cnt
        .Open (stConn) 
        .CursorLocation =  3
    End With

Thank you for taking me out of the tunnel

Cheers