Any way to call a worksheet method that may or may not exist?

[Dick Watson]

I'm adding a whole bunch of little formatting wedges in VBA on individual
sheets as necessary. I want to call then on workbook save. This works:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Sheet3.DoTheKludges
Sheet4.DoTheKludges
Sheet6.DoTheKludges
Sheet9.DoTheKludges
Sheet10.DoTheKludges

End Sub

But it's irredeemably ugly and unmaintainable and doesn't scale well as
sheets get added to or subtracted from this list.

So I was thinking of something along these lines:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim objWks As Worksheet

For Each objWks In Me.Worksheets

On Error Resume Next
objWks.DoTheKludges
On Error Goto 0

Next

End Sub

Of course, this doesn't work since, I'm guessing, the objWks.DoTheKludges
method call can't be resolved when the runtime compile occurs and that isn't
trapped by the On Error.

Any thoughts how to get around this?

 

[Bob Phillips]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim objWks As Worksheet

For Each objWks In Me.Worksheets

Application.Run "'" & objWks.Codename & "'!DoTheKludges"
Next

End Sub

 

[Dick Watson]

Cool. I'll try it!

 

[Dick Watson]

The 'sheetname'!macroname form was No Joy.

But here's what does work:

On Error Resume Next
Application.Run objWks.CodeName & ".DoTheKludges"
On Error GoTo 0

Thanks again!

 

[Bob Phillips]

Isn't that what I said?

 

[Dave Peterson]

.. vs !

Isn't that what I said?

--
__________________________________
HTH

Bob

 

[Dick Watson]

And lose the single ticks.

 

[Bob Phillips]

I concede that the ! was a typo on my part, but I wouldn't the single ticks
mattered. Unnecessary, true, as you wanted the codename, but not a problem.