Define Worksheet As Public Variable

[Edmond]

Hi Everyone,

It may seem trivial but I don't know a lot about Public Sub.

As I am working on multiple sub at a time I was wondering if I could set up all the variable in one sub.

Which led me to that code (doesn't work):

Public ws As Worksheet

Sub Test()


Set ws = ActiveWorkbook.Worksheets("Sheet1")


End Sub


Sub Call_Test()


Debug.Print ws.Name


End Sub

When I run it, I have the Run-time error '91'.

Do you have any ideas of what I am doing wrong here?

Thanks a lot in advance!

[Paul_Hossler]

If I run Test, then Call_Test it works

If I just run Call_Test first, or after an error it doesn't since Excel clears things

[Edmond]

Indeed, it perfectly works.

So how would you proceed to let the user decide which Sub he wants to run?

Public ws As Worksheet, ws2 As Worksheet

Sub Test()


Set ws = ActiveWorkbook.Worksheets("Sheet1")
Set ws2 = ActiveWorkbook.Worksheets("Sheet2")
Call Call_Test


End Sub


Sub Call_Test()


Debug.Print ws.Name


End Sub


Sub Call_Test2()


Debug.Print ws2.Name


End Sub

[Paul_Hossler]

Indeed, it perfectly works.

So how would you proceed to let the user decide which Sub he wants to run?

Not nearly enough information, but I'm GUESSING that you mean the user runs Test() and then decides if it Call_Test or Call_Test2???



I'd architect it like this and let the user run Call_Test or Call_Test2

Option Explicit

Public ws As Worksheet, ws2 As Worksheet

Sub Call_Test()
    Init
    Debug.Print ws.Name

End Sub

Sub Call_Test2()
    Init
    Debug.Print ws2.Name

End Sub

Private Sub Init()
    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    Set ws2 = ActiveWorkbook.Worksheets("Sheet2")
End Sub

If that's not close, then some more information would be helpful

[Edmond]

Sorry for the lack of information. But I have found what I was looking for with your answer

Thank you again!