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?
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
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.
SheetX in the VB editor to CR and you can access it from anywhere with out initialization.
externdeclaration at the top of every module that wants to use this (and you need to make sure that the code that makes this assignment is used before you use it elsewhere).