Refering to code in worksheet

[dugdugdug]

Sheet1 is named Graph and contains some code:

Sub MyWorksheetCode

' just some code

End Sub

In Module1, I have the following:

Sub MyModuleCode

Dim wks as Worksheet

Set wks = Worksheets("Graph")

' Call wks.MyWorksheetCode <- This does not work

Call Sheet1.MyWorksheetCode

End Sub

Why is it in the above, calling the subroutine using the variable wks does not work?

Thanks

 

[dugdugdug]

That's my point. The generic Worksheet class does not have a MyWorksheetCode method. Your specific Sheet1 class, which inherits from the generic class, does have that method. You can declare the variable as a Variant (as you have) or an Object or as Sheet1:

Sub MyModuleCode()
' any of these will work
Dim wks as Sheet1
' Dim wks as Object
' Dim wks

Set wks = Worksheets("Graph")
Call wks.MyWorksheetCode
End Sub

You have other answers, but the code I posted worked for me:

'Sheet1 module
Sub MyWorksheetCode()
    MsgBox "Hello"
End Sub

'General module
Sub MyModuleCode()
    Dim wks As Worksheet
    Set wks = Worksheets("Graph")
    Application.Run wks.CodeName & ".MyWorksheetCode"
End Sub

Thanks both these sound good.