How to declare and use public functions and subroutines in Worksheet code

Question

I have a public function declared in one of my worksheet modules:

Public Function isValidContract(contract As String) As Boolean
    ' Code reads cell values from the worksheet and determines
    ' if passed-in contract is one of them.
End Function

I'd like to be able to access it from other modules and Class modules. I've tried the following:

Public Sub someRandomSubInAntoherModule()

    Dim contract As String
    Dim sh as Worksheet

    ' Code that sets contract
    Set sh = Sheets("Matrix")
    If Not sh.isValidContract(contract) Then
        ' blah
    End If

End Sub

But I get a compile error: "Method or data member not found", probably because I declared sh as a Worksheet object, and the Worksheet object doesn't have an isValidContract() method. But I want to use the isValidContract() method defined in my Matrix worksheet.

The only way I can get it to work is to declare sh as an Object. But then I don't get the nifty little code hints when I type

sh.

Is there any way to dimension sh such that I get the code hints for the Worksheet object and my specific Matrix code?

Answer 1

OK - so I just figured it out.

Change the "Excel name" of the sheet to something that makes sense... in this case, I renamed Sheet1 to MatrixSheet by editing its Properties.

Then in the client code:

Public Sub someRandomSubInAntoherModule()

    Dim contract As String
    Dim sh as MatrixSheet

    Set sh = Sheets("Matrix")

    ' Code that sets contract
    If Not sh.isValidContract(contract) Then
        ' blah
    End If

End Sub

It compiles, I get code hints, it's great.