VBA - Auto check/uncheck microsoft script run time

Question

I had the following function that auto add Microsoft Script Runtime Reference to the Reference list. However, if the user already had included Microsoft script runtime, it will show error Name conflicts with existing module,project, object library.

How do I set an condition that auto adds Microsoft script runtime if it is not included in the reference and do nothing if it has already being added?

Private Function AddScriptingLibrary() As Boolean

Const GUID As String = "{420B2830-E718-11CF-893D-00A0C9054228}"

On Error GoTo errHandler
ThisWorkbook.VBProject.References.AddFromGuid GUID, 1, 0
AddScriptingLibrary = True
Exit Function
errHandler:
MsgBox Err.Description

End Function

Did you see [THIS](https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically/9880276#9880276)? — Siddharth Rout, Nov 28 '17 at 06:56
@SiddharthRout After a second though, I think it does not solve my problem since it will add another Reference — Max, Nov 28 '17 at 07:20
It will not add another reference if you check for it before :) — Siddharth Rout, Nov 28 '17 at 07:44
Why would you need code to check if the reference is set in its own workbook? The reference is saved with the workbook so unless you're expecting a user to go in and *uncheck* it for some reason, it makes no sense to me. — Rory, Nov 28 '17 at 08:18

ThunderFrame · Accepted Answer · 2017-11-28T07:33:48.560

You'll need to enumerate the references of the project first, in order to check if the reference is already present.

I've added a reference to Microsoft Visual Basic for Applications Extensibility 5.3

Option Explicit

Function AddScriptingLibrary()

    Const GUID_Scripting = "{420B2830-E718-11CF-893D-00A0C9054228}"

    Dim proj As VBIDE.VBProject
    Dim ref As VBIDE.Reference
    Dim ScriptingLibraryIsReferenced As Boolean

    Set proj = ThisWorkbook.VBProject

    For Each ref In proj.References
      If ref.GUID = GUID_Scripting Then
          ScriptingLibraryIsReferenced = True
          AddScriptingLibrary = True
          Exit Function
      End If
    Next ref

    If Not ScriptingLibraryIsReferenced Then
        On Error GoTo errHandler
        proj.References.AddFromGuid GUID_Scripting, 1, 0
        AddScriptingLibrary = True
        Exit Function

errHandler:
    MsgBox Err.Description
      End If

End Function

EDIT this does the same, but without the early-bound reference to Visual Basic For Applications Extensibility 5.3 reference:

Option Explicit

Function AddScriptingLibrary()

    Const GUID_Scripting = "{420B2830-E718-11CF-893D-00A0C9054228}"

    Dim proj As Object 'VBIDE.VBProject
    Dim ref As Object 'VBIDE.Reference
    Dim ScriptingLibraryIsReferenced As Boolean

    Set proj = ThisWorkbook.VBProject

    For Each ref In proj.References
      If ref.GUID = GUID_Scripting Then
          ScriptingLibraryIsReferenced = True
          AddScriptingLibrary = True
          Exit Function
      End If
    Next ref

    If Not ScriptingLibraryIsReferenced Then
        On Error GoTo errHandler
        proj.References.AddFromGuid GUID_Scripting, 1, 0
        AddScriptingLibrary = True
        Exit Function

errHandler:
    MsgBox Err.Description
      End If

End Function

But then, if you're happy with the down-sides of late-bound code, you don't even need the reference to Scripting.Runtime, because you can just use:

Option Explicit

Sub PrintDriveCount()

    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")

    'Print the number of drives in the FileSystemObject
    Debug.Print FSO.Drives.Count

End Function

This certainly answers the question, but since there is a need to add another reference manually, I guess we are back to the initial problem. I feel like replacing `VBIDE.VBProject` and `VBIDE.Reference` by `Object` would probably be more appropriate. — DecimalTurn, Nov 28 '17 at 07:18
Sorry I new to this, learning and using code on the spot and not really sure about how it works. I am able to understand the concept of the codes. I will pass the excel workbook to other users as well. Do they have to add the reference to **Microsoft Visual Basic for Applications Extensibility 5.3** as well? Or when I pass the workbook to them, it is already added, since it is added by me. — Max, Nov 28 '17 at 07:18
Adding the reference isn't necessary, but then neither is adding the reference to Scripting Runtime. All of the objects can be used late-bound, but it will be slower, you won't get design-time compiliation, or Intellisense. — ThunderFrame, Nov 28 '17 at 07:27
@DecimalTurn Thanks, by applying ThunderFrame's answer and your suggestion it works out fine. I unchecked the `Microsoft Visual Basic for Applications Extensibility 5.3` and it looks good. — Max, Nov 28 '17 at 07:27

VBA - Auto check/uncheck microsoft script run time

1 Answers1