VBA Global Variables are variables which are declared before the start of any macro in the module. When the variables are declared by using either “Public” or “Global,” it becomes “Global Variable.” Sub Procedure Variables Cannot Use Anywhere.
Declaring a Worksheet Object You can also declare a variable as a worksheet, making it easy to use that worksheet in a VBA code. First, use the DIM keyword, and then the name of the variable. After that, specify the object type as a worksheet.
I'm refactoring a number of modules in an Excel 2003 workbook and the same set of worksheets are declared in each procedure in each module; I'd like to just declare them once globally. I can set the worksheet name as a literal, e.g.:
Public Const xlwkGSModel = "gs_model" As String
And then in the procedure use:
...ActiveWorkbook.Worksheets(xlwkGSModel).Cells(1,1)
But is there a way to declare the worksheet object so that the code in the procedure could be:
...xlwkGSModel.Cells(1,1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With