Question posted 2014 ยท +4 upvotes
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?
Accepted answer +9 upvotes
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
Top excel-vba Q&A (6)
- How to clear the entire array? +58 (2010)
- How to change Format of a Cell to Text using VBA +55 (2011)
- Download attachment from Outlook and Open in Excel +43 (2012)
- Can a VBA function in Excel return a range? +36 (2009)
- 2 Dimensional array from range +34 (2013)
- Hiding an Excel worksheet with VBA +33 (2009)
excel-vba solutions on this site
.