PDA

View Full Version : [SOLVED] Define Worksheet As Public Variable



Edmond
02-19-2019, 10:10 AM
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
02-19-2019, 10:27 AM
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
02-19-2019, 10:43 AM
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
02-19-2019, 02:30 PM
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
02-21-2019, 07:36 AM
Sorry for the lack of information. But I have found what I was looking for with your answer :)

Thank you again!