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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.