VBA:: determine if UDF exists?

[George]

In a workbook w/ multiple sheets, w/ a loop thus ...

dim mySheet as worksheet
For Each mySheet In ActiveWorkbook.Sheets
mySheet.Activate
Worksheets(mySheet.Name).myUDF
Next mySheet

Can I test whether 'myUDF' exists in each mySheet?

I can solve my problem using 'On Error GoTo ...', or by creating a stub
in each sheet; I'm just wondering if there's a more direct way to get
there.

Thanks,
George

 

[Dave Peterson]

I think you'd have to know something about what the UDF needs passed to it, too.

I put this in a few worksheet modules:

Option Explicit
Function myUDF(SomeVar As Variant) As Variant
'do lots of work here
myUDF = 999
End Function


Then I could use something like this to look for it:

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
On Error Resume Next
Application.Run wks.CodeName & ".myUDF", "aa"
If Err.Number <> 0 Then
MsgBox "not found in: " & wks.CodeName & "--" & wks.Name
Err.Clear
Else
MsgBox "Found in: " & wks.CodeName & "--" & wks.Name
End If
On Error GoTo 0
Next wks
End Sub

But for the most part, if I'm gonna use the same UDF in various spots, I'll put
it in a general module and just have the other code call that single UDF.