Late Binding VBIDE.VBE in excel

Question

Is it possible to late bind the VBIDE.VBE object in Excel? For example:

Dim VBAEditor As VBIDE.VBE

Instead becomes something similar to this (late-binded):

Dim VBAEditor As Object: set VBAEditor = CreateObject ("VBIDE.VBE")

My goal is to avoid having to manually go in an select the checkbox for the "Microsoft Visual Basic for Applications Extensibility 5.3" reference.

SOLUTION

Using the feedback below, I was able to do programmatically add the "Microsoft Visual Basic for Applications Extensibility 5.3" reference dynamically. Solution is as follows:

Sub mainFunction()

    Call AddLib("VBIDE", "{0002E157-0000-0000-C000-000000000046}", 5, 3)

    ' Bunch of working code goes here

End Sub

'******************************************************************************
'AddLib: Adds a library reference to this script programmatically, so that
'        libraries do not need to be added manually.
'******************************************************************************
Private Function AddLib(libName As String, guid As String, major As Long, minor As Long)

    Dim exObj As Object: Set exObj = GetObject(, "Excel.Application")
    Dim vbProj As Object: Set vbProj = exObj.ActiveWorkbook.VBProject
    Dim chkRef As Object

    ' Check if the library has already been added
    For Each chkRef In vbProj.References
        If chkRef.Name = libName Then
            GoTo CleanUp
        End If
    Next

    vbProj.References.AddFromGuid guid, major, minor

CleanUp:
    Set vbProj = Nothing
End Function

I was heavily inspired by this stack article on dynamic referencing in excel.

Why would you need to do this? That reference is the same across all versions anyway, and you can simply late bind as an alternative. — Rory, Apr 17 '15 at 08:20
@Rory, I wanted to make it easier for people to import and use my script in their excel document. This is another reason why I moved to GUID for the same reason you stated; GUID references are the same across all versions. I need to keep the `chkRef` in the code above as well to avoid an error related to "objects of the same name" being used. The code above does what I need it to do now. — Jake88, Apr 17 '15 at 12:45
Why not late bind then? It's a lot simpler and doesn't need any specific user settings in the Trust Center, unlike what you're doing. — Rory, Apr 17 '15 at 14:53
@Rory, I see what your mentioning about late-binding. I have changed my code above to late-bind the excel application instead. However, I am not familiar with the API enough to late bind VBProject to get around the trust setting. Any hints, documentation, or suggestions would be appreciated. — Jake88, Apr 17 '15 at 16:06
@Rory, if VBProject is a property I believe cannot use getObject to grab it. How does one get around not using VBProject to check for configured references? — Jake88, Apr 17 '15 at 16:14
You can't. Any access to the VBProject requires trusted access to be set. (I think I misunderstood what you were up to and thought you only wanted access to set the reference) — Rory, Apr 17 '15 at 16:14
Ok great. Thank you @Rory for pointing out my missing late-bind though :) — Jake88, Apr 17 '15 at 16:17

score 5 · Accepted Answer · edited May 23 '17 at 11:44

5

Yes, as per Excel forms: identify unused code you can use late binding

Dim VBProj
Dim VBComp
Set VBProj = ActiveWorkbook.VBProject
For Each VBComp In VBProj.vbcomponents

etc

edited May 23 '17 at 11:44

Community

1
1

answered Apr 17 '15 at 01:48

brettdj

54,857
16
114
177

In my code I currently use `Dim vbProj As VBIDE.VBProject` and `VBAEditor As VBIDE.VBE`. The VBAEditor allows me to check the references currently configured in the IDE itself. [I was heavily inspired by this stack article](http://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically-vba-excel). They do not appear to be the same object. Can I do the same with VBAEditor as VBProject? – Jake88 Apr 17 '15 at 02:14
3

@Jake88 late-binding means you're *not* declaring `As VBIDE.*`, and *not* getting IntelliSense for the members of whatever objects you're using, which means you'd better know the API like the palm of your hand. `VBIDE.VBE` is the "root" object of the API; it has a `.VBProjects` collection that contains `VBProject` objects, one of which corresponds to `ActiveWorkbook.VBProject` - note that there's a security setting that must be enabled in the host app (Excel) to allow access to that object. – Mathieu Guindon Apr 17 '15 at 02:29
Ok, I think I got it all working. I'll share what I was trying to do in my question above. – Jake88 Apr 17 '15 at 02:52

Late Binding VBIDE.VBE in excel

SOLUTION

1 Answers1