Passing Module to subroutine

[Guest]

I'm trying to pass a Module object (if it is an object) to a routine, but I'm
having no luck. If I type it as an object, it gives me a ByRef error.
Everythign else I tried results in an even worse error. The VBA help file
for the topic "Referring to Sheets by Index Number" says that the Sheets
collection, includes modules ("If you want to work with all types of sheets
(worksheets, charts, modules, and dialog sheets), use the Sheets property.")
should work, but when I walk though the Sheets collection, all I get are
worksheets. Anyone know how I can pass a module object to a subroutine?

 

[Guest]

Humor me. Why do you want to pass a module? I know in C/C++ you can pass a
function but I am unaware of how or why you would want to in VBA.

 

[Chip Pearson]

The VBA help file

for the topic "Referring to Sheets by Index Number" says that
the Sheets
collection, includes modules

The modules in this case are code module sheets, which was where
VBA was written prior to Excel 97 which provided a proper editor
with code components. The modules referred to there are NOT the
same as a VBA code modules. If you want to pass a VBA code
module, use code like the following:

Sub AAA()
BBB VBComp:=ThisWorkbook.VBProject.VBComponents("Module1")
End Sub


Sub BBB(VBComp As VBIDE.VBComponent)
If VBComp.Type <> vbext_ct_StdModule Then
Exit Sub
End If
End Sub

Note: You'll need a reference to the Microsoft Visual Basic For
Applications Extensibility Library for this to work.

For further reference, see http://www.cpearson.com/excel/vbe.htm
and http://www.cpearson.com/excel/codemods.htm


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





message