Dynamically call function in sheet's VBA module

[Chipper]

Hi all, I've looked everywhere for hours trying to figure out how to reference a sheet's VBA 'module' dynamically and can't figure it out/find it anywhere.

I'm running MS Office 2016

In short, I'm trying to use each sheet's provided VBA 'module'(I don't know what this is called) to store a 'main' function that would be called when looping through all of the sheets in the workbook, and each sheet's 'main' function would return an array which stored the meta data in their associated places. For example, there would be a sheet titled RSS_PoliticsAzerbaijan which would, when it's main() function was called, return an array with information like {"Politics",,,"Azerbaijan",,,}, where when the RSS_EconomyDjibouti main was called, would return {"Economy",,,"Djibouti",,,} etc. Unfortunately, I can't figure out how to dynamically reference a sheet's vba 'method'(i'm not sure what it is called). I've created most of the aspects to the overall project, and I already have 2 different major work-arounds, but I'd really prefer this method over the other work-arounds I have thought of. Any ideas?
18194
My code so far is below:


Private Sub LoopThroughSheets()
'create worksheet reference
Dim ws As Worksheet
'loop through all sheets in the workbook
For Each ws In ThisWorkbook.Sheets
With ws
'tell me current
Debug.Print ws.CodeName
'attempt to run Main function
ws.Application.WorksheetFunction.Main
End With
Next
End Sub

In each sheet's module, there is a sub or function (tried both) names Main that simply prints the sheet name to the immediate window.


Public Sub Main()
Debug.Print "Sheet2: (rssgeneral)"
End Sub

P.S. I wrote up a discussion on my project and what I was trying to do and why, but it was rather long so cut it out. if you want to know the context, let me know.

Thank you,
Chip

[Paul_Hossler]

Try something like this. Each Module in the attachment has a sub named 'Hello' in it




Option Explicit

Const vbext_ct_StdModule As Long = 1
Const vbext_ct_ClassModule As Long = 2
Const vbext_ct_MSForm As Long = 3

Sub LoopModules()
Dim VBMod As Object

For Each VBMod In ActiveWorkbook.VBProject.vbcomponents
If VBMod.Name <> "Mod_Main" Then
If VBMod.Type = vbext_ct_StdModule Then
Application.Run VBMod.codemodule & ".Hello"
End If
End If
Next
End Sub




For more reading

http://www.cpearson.com/Excel/VBE.aspx

[SamT]

Sheets("RSS_General").Main

We generally refer to ie: "RSS_General" as the Sheet's Name or the Sheet's Tab Name to diferentiate it for the Sheet's CodeName.

A Sheet's CodeName can be changed. Using Sheets("RSS_General") as an example, its CodeName is Sheet2, (No parens or quotes). The CodeName is the String in the block designated as "(Name)" in the Properties Window .

In your current case

Sheets("RSS_General").Main
And

Sheet2.Main
Will both work.


Typically, when I will be calling subs on a sheet, I change the CodeName to match the Tab Name
(Name) = RSS_General
So I can use

RSS_General.Main

Assuming you want to place a Sub Main() on several sheets, and loop thru them

Dim Sht As Object 'Or as Sheet or WorkSheet
For each Sht in ThisWorkBook.Sheets
On Error Resume Next
Sht.Main
Err = 0 'or something like that
Do Events
Next Sht
End sub

[Aflatoon]

Dim Sht As Object 'Or as Sheet or WorkSheet


Object is the correct declaration there. There is no such object as a Sheet, and Worksheet wouldn't work, since the Worksheet interface does not contain a Main method.

FWIW, if I were doing something similar, I would probably use an Interface and implement it in each applicable sheet. That way you can easily distinguish sheets that don't have the Main method from those that do.

[Chipper]

Thank you everyone for your input. My code now works and I learned a lot.

my code is as follows:


Private Sub LoopThroughSheets()
Dim Sht As Object
For Each Sht In ThisWorkbook.Sheets
Sht.Main
Next Sht
End Sub