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
– SierraOscar

2015-11-03 16:01:17 +00:00
Commented Nov 3, 2015 at 16:01 — SierraOscar
– SierraOscar, Commented Nov 3, 2015 at 16:01
Because it's specific to that particular sheet.

livefree75
– livefree75

2015-11-03 16:04:17 +00:00
Commented Nov 3, 2015 at 16:04 — livefree75
– livefree75, Commented Nov 3, 2015 at 16:04

livefree75 · Accepted Answer · 2015-11-03 16:16:13Z

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 3, 2015 at 16:16

answered Nov 3, 2015 at 16:07

livefree75

7758 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Williams Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related