Setting a global constant as worksheet?

[cbrf23]

I'm trying to set a global constant as a worksheet object.

I'm not a fan of globals usually, but it seems dumb (and its a pain in debugging) for me to keep passing pointers to the same sheet over and over.

I cant figure out how to set the constant as a worksheet.

If you type the below code into VBA, you'll find that the stuff I colored red in red is red in VBA, meaning its not the proper syntax. The bottom one is acceptable to VBA, but I get a compile error at run time that says "constant expression required".

Can anyone point me in the right direction?

[COLOR=red]Public Const G_wsCert as Worksheet Sheets("CERTIFICATION (2)")[/COLOR]
[COLOR=red]Public Const G_wsCert as Worksheet = Sheets("CERTIFICATION (2)")[/COLOR]
[COLOR=red]Public Const G_wsCert as Worksheet set = Sheets("CERTIFICATION (2)")[/COLOR]
[COLOR=red]Public Const G_wsCert as Worksheet set G_wsCert = Sheets("CERTIFICATION (2)")[/COLOR]
[COLOR=red]Public Const G_wsCert as Sheets("CERTIFICATION (2)")[/COLOR]
Public Const G_wsCert = Sheets("CERTIFICATION (2)")

 

[Jaafar Tribak]

You can't declare an Object constante. It won't compile.

The best you can do is to define the const as a string which holds the sheet name as follows :

Public Const G_wsCert As String = "CERTIFICATION (2)"

Sub Test()
    Set sh = Sheets(G_wsCert)
End Sub

But then again,you are still referencing the sheet .

 

[Jaafar Tribak]

Or you could declare G_wsCert as a worksheet variable and make it refer to the worksheet at the start of you routine. Then you can refer to the worksheet by the variable throughout your project as follows :

Public G_wsCert As Worksheet

Sub Test()

    Set G_wsCert = Sheets("CERTIFICATION (2)")
    
    MsgBox G_wsCert.Name
    
    'rest of your code

End Sub

 

[cbrf23]

I forgot about this thread. Lol. Might as well share with you the workaround I came up with. The example below is for worksheets, but you could do the same thing for any type of object...

I created a class called claGlobalSheets and then set all the worksheet objects I needed as read only properties. This way they they cannot be changed...just like a constant.

'In the class module for claGlobalSheets
Option Explicit
Public Property Get SheetOne() As Worksheet
    Set SheetOne= Sheets("MySheet1")
End Property
Public Property Get SheetTwo() As Worksheet
    Set SheetTwo= Sheets("MySheet2")
End Property
Public Property Get SheetThree() As Worksheet
    Set SheetThree= Sheets("MySheet3")
End Property

I declared a global variable oSheets:

Public oSheets As claGlobalSheets

And from my main calling procedure, I create an instance of the object:

Set oSheets = New ClaGlobalSheets

Once instantiated the oSheets object can be accessed from any module:

'you could write this in any module:
Sub testName()
    MsgBox oSheets.Name
End Sub

 

[Jaafar Tribak]

Hi.

I don't see that you are gaining much from this Class workaroud. You are still required to reference the sheet over and over throughout the code.


What's the difference between :

Sub testName()
oSheets.SheetName 
End Sub

and :

Sub testName()
Sheets("SheetName")
 End Sub

 

[cbrf23]

Because, when the worksheet name changes, I only have to change it inside the osheets property, instead of having to hunt through multiple modules and replace it everywhere.

Also, less chance of a typing error causing problems.

 

[mikerickson]

Have you looked at code names?
http://www.mrexcel.com/forum/showthread.php?t=253546&highlight=codename

BTW, as written, the value of claGlobalSheets.SheetOne will change when the user activates a different workbook.

 

[cbrf23]

Have you looked at code names?
http://www.mrexcel.com/forum/showthread.php?t=253546&highlight=codename

BTW, as written, the value of claGlobalSheets.SheetOne will change when the user activates a different workbook.

I was not familiar with using the code name....that is a much more elegant solution! Thank you!

You could chagne it to thisworkbook.sheets(whatever) if you didnt want to change with the active workbook.... Also, the example I typed on the fly for getting a message box with the name wont work as written either. I noticed that but couldnt edit the post because it was past the time limit for editing.

should be

osheets.sheetone.name