VBA - Auto check/uncheck microsoft script run time

user_8903456 0
Viewed 2259

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
vbide vba excel
1 Answers

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
user_57571590
Related