Excel VBA Add Reference Library without Closing Userform GUI

180 Views Asked by At

I have a script (see below) that loads a Reference Library and then presents the user with a GUI (Userform). The problem is, after loading the library it ends the script. Not immediately after the command (like a crash), but after it finishes running through. In this case it means that the GUI does not show up; however upon rerunning the script (when it does not have to load the reference library because it is already loaded), the GUI shows up fine.
I do not want to have to manually run this script twice every time that I use it, additionally I cannot set up my system to remember this reference library, there are many computers that run this script.
Is there any way of either:

  1. suppressing this behavior of ending of the script when it loads a library

  2. making the script automatically call itself again after it finishes running through once

    Sub AddReference()
    'Add a reference library to the project using the GUID
    Dim strGUID As String, theRef As Variant, i As Long
    On Error Resume Next
    
    strGUID = "{0002E157-0000-0000-C000-000000000046}"  'GUID for VBA Extensibility 5.3
    
    'Remove any missing references
    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
        Set theRef = ThisWorkbook.VBProject.References.Item(i)
        If theRef.isbroken = True Then
            ThisWorkbook.VBProject.References.Remove theRef
        End If
    Next i
    Err.Clear
    
    'ThisWorkbook.VBProject.References.AddFromGuid GUID:=strGUID, Major:=1, Minor:=0
    ThisWorkbook.VBProject.References.AddFromFile "C:\Program Files\Common Files\microsoft shared\VBA\VBA6\VBE6EXT.OLB"
    
    Select Case Err.Number
    Case Is = 32813
         'Reference already in use
    Case Is = vbNullString
         'Reference added without issue  
    Case Else
         'An unknown error was encountered, so alert the user
         MsgBox "A problem occurred while loading the reference library!"
         End
    End Select
    On Error GoTo 0
    
    MyGUI.Show
    
    End Sub
    
0

There are 0 best solutions below