Run macro by name

[shg]

I have a workbook where various sheets get published based on various criteria. For each sheet to be published, I want to run a short macro (that takes no arguments) behind each sheet. Depending on the sheet, it may autofilter a table, or set the zoom, or do nothing at all. This doesn't work:

Sub x()
  Dim wks As Worksheet
 
  For Each wks In Worksheets
    If isGonnaGetPublished(wks) Then
      Application.Run wks.CodeName & ".BeforePublish"
    End If
  Next wks
   
    ' other stuff
End Sub

What have I forgotten?

Thanks in advance.

 

[richh]

Does each worksheet have the same BeforePublish function? If it's named using the standard convention of [WorsheetName]_[FunctionName]([Args]) If so, perhaps this would work:

Sub x()
    Dim wks As Worksheet
 
    For Each wks In Worksheets
        If isGonnaGetPublished(wks) Then
            Call wks.Name & "_BeforePublish"
        End If
    Next wks
  
    ' other stuff
End Sub

 

[Jaafar Tribak]

I have a workbook where various sheets get published based on various criteria. For each sheet to be published, I want to run a short macro (that takes no arguments) behind each sheet. Depending on the sheet, it may autofilter a table, or set the zoom, or do nothing at all. This doesn't work:

Sub x()
  Dim wks As Worksheet
 
  For Each wks In Worksheets
    If isGonnaGetPublished(wks) Then
      Application.Run wks.CodeName & ".BeforePublish"
    End If
  Next wks
 
    ' other stuff
End Sub

What have I forgotten?

Thanks in advance.

Are the BeforePublish macros located in their corresponding worksheet modules respectively ? If so, as far as I can tell, your code should work.

Have you considered using the vba interaction CallByName Method as follows :

 For Each wks In Worksheets
    If isGonnaGetPublished(wks) Then
        CallByName wks, "BeforePublish", VbMethod
    End If
 Next wks

 

[shg]

Does each worksheet have the same BeforePublish function?

Richh, thanks for responding.

No, each (publishable) worksheet has its own, some of which do nothing, and their names are all identical. Here's one

Sub BeforePublish()
  With Me
    .AutoFilterMode = False
    .Range("tbl").CurrentRegion.AutoFilter Field:=2, Criteria1:="<>"
    .PageSetup.Zoom = 122
  End With
End Sub

Have you considered using the vba interaction CallByName Method ...:

That's perfect, Jaafar, thank you.

 

[RoryA]

This doesn't work

Just out of curiosity, what was happening? That original code should have worked.

 

[shg]

Well that's weird; I reran it so I could post the error message, and now it works fine. I think that's debugging by intimidation ("Don't make me tell Rory you don't work!)".

Thanks for weighing in.

 

[RoryA]

My colleagues frequently complain that their computers always work perfectly when I stand next to them so they can show me what’s wrong…

 

[shg]

At random times, the same problem recurs. The message says the macro may not exist, or macros are disabled. When that happens, CallByName also fails (different message, did'nt write it down).

Restart Excel, problem goes away.

Rory, can you come stand by my 'puter pro re nata?