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

 

[RoryA]

Because the Worksheet class (from which Sheet1 inherits) does not have a MyWorksheetCode method.

 

[Andrew Poulsom]

Try:

Application.Run wks.CodeName & ".MyWorksheetCode"

 

[nuked]

Try wrapping your worksheet code in a module and call it from the worksheet rather than the other way round.

Module code:

Sub DoSomethingWithAChart(obj as Variant)

End Sub


Worksheet code:

DoSomethingWithAChart(me.Chart1)

 

[Sixthsense]

ADVERTISEMENT

Or don't declare it........

Sub MyModuleCode

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

 End Sub

 

[dugdugdug]

Because the Worksheet class (from which Sheet1 inherits) does not have a MyWorksheetCode method.

Sorry if I'm being ignorant but

Sub MyWorksheetCode()
' just some code
End Sub

is in Sheet1.

Try:

Application.Run wks.CodeName & ".MyWorksheetCode"

Afraid it didn't work. (BTW, what is CodeName)?

Or don't declare it........

Sub MyModuleCode

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

 End Sub

Not declaring doesn't even compile.

However, this works:

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

So it seems it doesn't like it if wks is declared as a Worksheet.

 

[Sixthsense]

ADVERTISEMENT

But for me its working fine

 

[dugdugdug]

But for me its working fine

Probably because you haven't got Option Explicit on.

 

[RoryA]

So it seems it doesn't like it if wks is declared as a Worksheet.

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

 

[Andrew Poulsom]

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