0

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
TomDekkard
  • 17
  • 3
  • Why are you adding this at runtime? I can't think of anything it's necessary for, and it's available on any machine running VBA so can just be saved with the workbook. Adding it causes your project to reset, hence your problem. – Rory Mar 15 '21 at 21:55
  • Rory, this GUI is not tied to a specific file. Its purpose is meant to be imported into a new, blank xlsx file and then run. Then the user saves their file and closes it. Each time the user opens a new excel file to run this GUI they will have to import the reference library, Excel does not save that setting. Again, this GUI is run on many machines by many users, I do not want a fix that requires training users to operate differently or make changes to their settings, I am looking for a back end fix that enables the script to load a reference library and then run a GUI, that's it. – TomDekkard Mar 15 '21 at 23:38
  • OK, but why do you need the reference at all? I can't think of anything you can't do without it. – Rory Mar 16 '21 at 07:54
  • If you really insist on it, use `Application.Ontime` to schedule a routine that shows your form. That should allow the project to reset before the form is shown automatically. – Rory Mar 16 '21 at 09:05
  • Rory, thanks, I'll give this a try. To answer your question, the GUI has a dropdown menu that the script populates with all of the older versions of the same GUI that are currently loaded in the workbook, and selecting one from the dropdown runs that GUI. To find all the loaded userforms I needed VBIDE to perform the search (For Each vbc In ThisWorkbook.VBProject.VBComponents ... ). I'm not sure if there is a way around that which doesn't require the reference library but this is what I was able to find and it has been working so far. – TomDekkard Mar 16 '21 at 20:41
  • You don't need a reference to iterate the VBComponents. – Rory Mar 16 '21 at 21:44
  • Rory, You're right, looks like I can remove it while still iterating the VBComponents, I just can't check that vbc.Type=vbext_ct_MSForm anymore, I suppose for my case I don't need this since all older forms of the script have a similar name structure. Thanks! – TomDekkard Mar 18 '21 at 00:39
  • Either use the literal value `3` instead of that Enum or declare it yourself: `Const vbext_ct_MSForm as Long = 3` – Rory Mar 18 '21 at 08:26

0 Answers0