Excel VBA - Determine if a Module is included in a project

0
votes

I am currently working on a project where I am unable to verify that all modules are installed. There is a growing group of modules being used for common functions for programs I work with. I have tried some solutions on the web which I couldn't use as I am unfamiliar with Activeworkbook.VBProject.VBComponents() methods.

It was mentioned that I should check tools reference for Microsoft Visual Basic For Applications Extensibility and I checked that with no result. Any help would be appreciated. :)

References:

https://www.mrexcel.com/forum/excel-questions/284317-vba-function-check-if-particular-macro-exists.html

https://www.devhut.net/2010/12/09/ms-access-vba-determine-if-a-module-exists/

here is my code:

Option Explicit

Public Function Is_Module_Loaded(name As String) As Boolean
    Dim Module As Object
    Dim Module_Name As String
    Module_Name = name
    Is_Module_Loaded = False


    On Error GoTo errload
        Set Module = ActiveWorkbook.VBProject.VBComponents(Module_Name).CodeModule

    Is_Module_Loaded = True

    If (0 <> 0) Then
errload:
        MsgBox ("MODULE: " & Module_Name & " is not installed please add")
        Stop
    End If

End Function

When Running the Code I don't get any error that is very helpful excluding my own which is reporting wrong saying my module is absent when it isn't.

2
excelvbavbe
This has a good overview of what's involved in tasks like this: cpearson.com/excel/vbe.aspx Have you set the "Trust access to VBProject" checkbox in Excel Options? – Tim Williams
Note that all you can do is verify whether the VBA project contains a module that's named after what you're looking for - and that doesn't necessarily mean that's the actual module you want with the contents you need it to have. Consider having a copy of the module you want in ThisWorkbook, and using the VBIDE API to replace the module with it if it exists in the target project, or add it if it doesn't. Now, instead of a hand-crafted error message that's actually suppressing the would-be helpful error, consider MsgBox Err.Description. What's the actual error? – Mathieu Guindon
Mathieu Can you Elaborate on the MsgBox Err.Description part I am unfamiliar with this. I believe in the original I have no error even without my custom error message. also I was unable to figure out the VBIDE API but the idea of the code loading the modules from a location would be very useful. – Zackd41pro

2 Answers

1
votes

EDIT: updated to add the workbook as a second parameter

Try this:

Sub tester()

    Debug.Print Is_Module_Loaded(ThisWorkbook, "Module4")
    Debug.Print Is_Module_Loaded(ActiveWorkbook, "Module4")

End sub


Public Function Is_Module_Loaded(wb as Workbook, name As String) As Boolean

    Dim Module As Object

    On Error Resume Next
    Set Module = wb.VBProject.VBComponents(name).CodeModule
    On Error GoTo 0

    Is_Module_Loaded = Not Module Is Nothing

    If Not Is_Module_Loaded Then
        MsgBox ("MODULE: " & name & " is not installed in '" & _
                wb.Name & "' please add")
    End If

End Function
0
votes

So I believe I have found a solution.

Credit to: Tim Williams, Mathieu Guindon and Joe Phi (See link) for guidance to the solution

Reference: (https://stackoverflow.com/a/46727898/10297459)

Noted problems: With the original Tim mentioned that not setting a workbook could have me referencing the proper workbook, this was the major problem as I had other workbooks open that it was trying to reference.

    Option Explicit

Public Function Is_Module_Loaded(name As String, Optional wb As Workbook) As Boolean 
'!!!need to reference: microsoft visual basic for applications extensibility 5.3
        Dim j As Long
        Dim vbcomp As VBComponent
        Dim modules As Collection
            Set modules = New Collection
        Is_Module_Loaded = False

    'check if value is set

        If wb Is Nothing Then
            Set wb = ThisWorkbook
        End If
        If (name = "") Then
            GoTo errorname
        End If

    'collect names of files
        For Each vbcomp In ThisWorkbook.VBProject.VBComponents

            If ((vbcomp.Type = vbext_ct_StdModule) Or (vbcomp.Type = vbext_ct_ClassModule)) Then
                modules.Add vbcomp.name
            End If

        Next vbcomp

    'Compair the file your looking for to the collection
        For j = 1 To modules.Count
            If (name = modules.Item(j)) Then
                Is_Module_Loaded = True
            End If
        Next j
        j = 0

    'if Is_module_loaded not true
        If (Is_Module_Loaded = False) Then
            GoTo notfound
        End If

    'if error
        If (0 <> 0) Then
errorname:
            MsgBox ("Function BootStrap.Is_Module_Loaded Was not passed a Name of Module")
            Stop
        End If
        If (0 <> 0) Then
notfound:
            MsgBox ("MODULE: " & name & " is not installed please add")
            Stop
        End If

End Function