Global variable declaration from class module

Question

I have a workbook with multiple modules and multiple subs. There are some variables though that are usesd constantly in most subs such as given worksheest.

eg

dim cr as worksheet
set cr=sheets("combined_report")

I have this written in way too many subs. Can I write this once in say a class module and use "cr" from any sub in any module without having to reassign it?

Answer 1

You can do this with a function in a standard module and cache the reference using the Static keyword:

Function CR() As Worksheet
    Static CRSheet As Worksheet
    If CRSheet Is Nothing Then Set CRSheet = Sheets("combined_report")
    Set CR = CRSheet
End Function

Answer 2

Yes, you can!

Generally, you can define a global variable in any module before the declaration of your subs/functions using the Global keyword, e.g.

Option Explicit

Global cr as Worksheet

Public Sub mySub...

The global variable will keep it's scope - but of course you have to initialize is first, i.e. assign a value/object to it.

It is best practice to prefix global variable with a g, e.g. gWSMain, so you'll always know you're dealing with a global variable.

In case you only want a global variable for one or more specific worksheets in your workbook, you don't need a global variable at all! Instead, you can access them directly with their code names. Those are usually Sheet1, Sheet2, etc. - but you can change the name in the properties window.

These worksheets are available globally in your application, the same way as ThisWorkbook is.