CODEWITHSUNDEEP
excelvbams-accessmodule
DecimalTurn
DecimalTurn

Reputation: 4129

Modules is always empty in Excel VBA

I was looking at this question: Excel-VBA Import Module from text file (without requiring trust center)

The code presented makes use of the .Modules property member which seems to be a collection of VBA modules.

However, I can't seem to use it in Excel VBA. It always returns a collection with zero objects or I get a mismatch error:


Sub Test1()

 Dim mds As Modules
 Set mds = Application.Modules
 Debug.Print mds.Count
 'Output: 0

End Sub

Sub Test2()

 Dim mds As Modules
 Set mds = ThisWorkbook.Modules 'This line will return a type mismatch error
 Debug.Print mds.Count

End Sub

Sub Test3()

 Dim mds
 Set mds = ThisWorkbook.Modules
 Debug.Print mds.Count
 'Output: 0

End Sub

This problem seems to be specific to Excel VBA because if I add a VBA module to an Access project and try to run Test1, I get a non-zero number. Also note that Modules is only documented for Access.

I tried to look at the definition in the Object Browser. It's a bit weird because the property is hidden and it is returning a Sheets object instead of Modules.

enter image description here

Was that feature removed from Excel for security reasons or something? I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2310 Build 16.0.16907.20000) 64-bit.

Upvotes: 1

Views: 353

Answers (1)

taller
taller

Reputation: 18898

ThisWorkbook.Modules (or Application.Modules) is a secret object which is not mentioned on Microsoft Excel online documents. It only includes modules which are created via <Modules>.Add. The normal modules are NOT in its coverage.

Using ThisWorkbook.VBProject.VBComponents can manipulate all VBA components, but enabling Trust access to the VBA project object model is needed.

Option Explicit
Sub Demo()
    Dim mds As Module, lib As String
    Debug.Print "Before:" & ThisWorkbook.Modules.Count
    Set mds = ThisWorkbook.Modules.Add 'Create a new Library Module
    mds.Name = "Library"
    lib = "d:\temp\demo.bas"
    mds.InsertFile lib 'Insert the code
    Debug.Print "After:" & ThisWorkbook.Modules.Count
    Debug.Print "VBCs:" & ThisWorkbook.VBProject.VBComponents.Count
End Sub

Output

Before:0
After:1
VBCs:4

4 VBC: Sheet1, ThisWorkbook, Library, Module1

enter image description here

Upvotes: 3

Related Questions