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?

Why is the function in a worksheet module? – SierraOscar Nov 03 '15 at 16:01 — SierraOscar, Nov 03 '15 at 16:01
Because it's specific to that particular sheet. – livefree75 Nov 03 '15 at 16:04 — livefree75, Nov 03 '15 at 16:04

livefree75 · Accepted Answer · 2015-11-03T16:16:13.397

2

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.

edited Nov 03 '15 at 16:16

answered Nov 03 '15 at 16:07

livefree75

720
8
18

You can just use `MatrixSheet.isValidContract(contract)` without declaring a variable of that type. The `MatrixSheet` object is already instantiated in your workbook's VB Project. – Tim Williams Nov 03 '15 at 17:44

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

1 Answers1