Chipper
01-30-2017, 03:21 PM
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
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