Checking for existence of a function in each worksheet in the workbook, and calling this function it if found

[coldwork]

I have a worksheet with multiple sheets. Some of these sheets contain similar data, which needs to be extracted.Intention was to have the following function in each sheet:Function isThisAValidSheet () as Boolean isThisAValidSheet = TrueEnd FunctionThe sheet would have other functions and subs as well, which would need to be called in case this function returned 'True'.In the modules, I now need to check all sheets one by one to see whether this function exists. And if it exists, then I need to call a whole lot of other functions and sub-routines in the same sheet. I am using something similar to the following code in one of the procedures:sub doThis()Dim wkSheet as WorksheetFor Each wkSheet In ActiveWorkbook.Worksheets doesWorkSheetHaveTheInformationRequired = wkSheet.isThisAValidSheet if doesWorkSheetHaveTheInformationRequired = True then wkSheet.exportWorkSheet '....Other Stuff Goes Here end ifNext wkSheetend subUnfortunately, as soon as I try to run this procedure, I get the the error "Compile Error: Method or data member not found" and the".isThisAValidSheet" highlighted in the 'doThis' sub above.Kindly advise.Thank you very much.

 

[mikerickson]

Rather than putting a function in a module to "flag" it, it would be easier to put a hidden name scoped to that worksheet.

' in normal module

Sub MakeSheetValidFlag()
    Sheet1.Names.Add(Name:="_valid", RefersTo:="=True").Visible = False
End Sub

Then one could test if a sheet was valid with code like

' in normal module

Sub trial()
    MsgBox IsValidSheet(Sheet2)
End Sub

Function IsValidSheet(aWorksheet As Worksheet) As Boolean
    On Error Resume Next
    IsValidSheet = Evaluate(aWorksheet.Names("_valid").RefersTo)
    On Error GoTo 0
End Function

 

[coldwork]

I believe that this should solve the immediate problem. I will try it out. Unfortunately, I am on a ship, and internet connectivity is poor. To add to that, I have to use a Mac for online communications, while I am working on a PC which is not on the same network.

In any event, should I assume that the following is not possible:
"Calling a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); font-variant-ligatures: normal; font-variant-position: normal; font-variant-numeric: normal; font-variant-alternates: normal; font-variant-east-asian: normal; cursor: help; color: rgb(51, 51, 51);">VBA</acronym> sub or function which is in a worksheet from another sub or function which is in the module when the name of the worksheet is either not known, or contained in a worksheet object."

Thanks.

 

[mikerickson]

Actualy, now that I think about it, the coding is very similar.

in the code module for Sheet1

' in Sheet1 code module

Public Function IsValid() as Boolean
    IsValid = True
Exit Function

When calling IsValid for that sheet, there should be no problem. (Note the explicit declaration of a Public function.)

MsgBox Sheet1.IsValid

The problem arises from your using IsValid as a flag for the sheet. That means that you have to try to call IsValid from sheets that don't have it.
Buffering with On Error Resume Next, as was done for the Names approach above, is needed.

' in a normal module

Function SheetIsValid(aSheet) As Boolean
    On Error Resume Next
    SheetIsValid = aSheet.IsValid
    On Error Goto 0
Exit Sub

Sub Test()
    If SheetIsValid(Sheets("someSheet")) Then
        MsgBox "The sheet with tab name someSheet is valid"
    Else
        MsgBox "its not"
    End IF
Exit Sub

This also arises if IsValid is a property rather than a function.