Automating Library Registration in VBA: A Step-by-Step Guide

Introduction

When developing business applications based on Microsoft Excel, you may have encountered the need to include various libraries through the Tools -> References menu. This can be quite inconvenient for users who lack technical experience. To avoid complicated instructions, we can automate the process of library registration and inclusion using VBA code. In this article, we will explore how to automate the registration of the "Microsoft HTML Object Library" (MSHTML)..

Problem Statement

The aim of this article is to simplify the process of using libraries for end-users who may not know how to do it manually. Today, we will learn how to write code that automatically checks whether a library is enabled, and registers it if it is not.

Final Code

Here is the code we will be using in our article:

Sub CheckAndRegisterHTMLObjectLibrary()
    Dim ref As Object ' Using Object instead of Reference
    Dim isHTMLLibEnabled As Boolean
    Dim libName As String, libGuid As String, libMinor As Integer, libMajor As Integer
    
    ' Library name
    libName = "MSHTML"
    libGuid = "{3050F1C5-98B5-11CF-BB82-00AA00BDCE0B}"
    libMajor = 4
    libMinor = 0
    
    ' Check if the library is already enabled
    isHTMLLibEnabled = False
    For Each ref In Application.VBE.ActiveVBProject.References
        If ref.name = libName Then
            isHTMLLibEnabled = True
            Exit For
        End If
    Next ref
    
    ' If the library is not enabled
    If Not isHTMLLibEnabled Then
        On Error Resume Next
        ' Try to add the library
        Application.VBE.ActiveVBProject.References.AddFromGuid libGuid, libMajor, libMinor
        On Error GoTo 0
        
        ' Check if the library was successfully added
        If Err.Number <> 0 Then
            MsgBox "Failed to add " & libName & ". Attempting to register it...", vbExclamation
            If RegisterMSHTML() Then
                ' Retry adding the library after registration
                On Error Resume Next
                Application.VBE.ActiveVBProject.References.AddFromGuid libGuid, libMajor, libMinor
                On Error GoTo 0
                If Err.Number = 0 Then
                    MsgBox libName & " successfully registered and enabled!", vbInformation
                Else
                    MsgBox "Failed to enable " & libName & " after registration. Please check manually.", vbCritical
                End If
            Else
                MsgBox libName & " registration failed. Ensure the mshtml.dll file exists.", vbCritical
            End If
        Else
            MsgBox libName & " successfully enabled!", vbInformation
        End If
    Else
        MsgBox libName & " is already enabled!", vbInformation
    End If
End Sub
Function RegisterMSHTML() As Boolean
    Dim WshShell As Object
    On Error Resume Next
    ' Using WScript.Shell to run regsvr32 command for registering mshtml.dll
    Set WshShell = CreateObject("WScript.Shell")
    WshShell.Run "regsvr32 /s mshtml.dll", 0, True
    RegisterMSHTML = (Err.Number = 0) ' Return True if no error occurred
    On Error GoTo 0
End Function

Implementation Steps

1. Library Registration

Before registering the library, let's find out where to locate the required DLL file and its name. In our case, it is mshtml.dll. To determine the library you need, you can:

• Open the Tools -> References menu in the VBA editor.
• Scroll through the list of available libraries and find the one you are interested in. To the right of the name, the associated file (.dll) is indicated.
• Note down the DLL name and GUID that you will need for registration and inclusion.

For the MSHTML library, the GUID looks like this:

{3050F1C5-98B5-11CF-BB82-00AA00BDCE0B}

2. Adding the Library via GUID

After the library is registered, we can add it to the VBA project using the method Application.VBE.ActiveVBProject.References.AddFromGuid, utilizing the library's GUID. The parameters you should specify include:

libGuid = "{3050F1C5-98B5-11CF-BB82-00AA00BDCE0B}"
libMajor = 4
libMinor = 0

Where libMajor and libMinor define the version of the library. To obtain the correct values for these parameters:

• In the Tools -> References menu, select the library you need (e.g., MSHTML).
• Run the code in the VBA editor to rewrite the library name as ref.name, as well as the parameters ref.GUID, ref.Major and ref.Minor. Here’s how it looks in the code:

For Each ref In Application.VBE.ActiveVBProject.References
    If ref.Name = libName Then
        libGuid = ref.GUID
        libMajor = ref.Major
        libMinor = ref.Minor
        Exit For
    End If
Next ref

3. Checking for Library Presence

Before registering or enabling the library, it is important to check whether it is already enabled. This can be done using the For Each loop:

For Each ref In Application.VBE.ActiveVBProject.References
    If ref.Name = libName Then
        isHTMLLibEnabled = True
        Exit For
    End If
Next ref

4. Calling the Function on Workbook Open

To automatically invoke the check and registration function for the library, you can add the following code to the Workbook_Open() procedure in the ThisWorkbook module:

Private Sub Workbook_Open()
    ModFunctions.CheckAndRegisterHTMLObjectLibrary
End Sub

Conclusion

This approach significantly simplifies the process of working with libraries for users of your Excel application. With the provided code, you can automate the registration and enabling of libraries, freeing users from cumbersome instructions. Just insert this code into your project, and you will be able to work effortlessly with MSHTML and other libraries without worrying about their manual configuration.

Do you need a professional business application developed based on Microsoft Excel to streamline your workflows?

Our team of experts is ready to assist you. We offer tailored solutions designed to meet your specific needs and business requirements. Leave the complexities to us and focus on growing your business! Contact us for a consultation and to discuss your project.