Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Late Binding VBIDE.VBE in excel

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.

like image 414
Jake88 Avatar asked Apr 17 '15 01:04

Jake88


1 Answers

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

like image 111
brettdj Avatar answered Oct 04 '22 14:10

brettdj