Declare and set project variables for worksheets and dynamic ranges

[jacko2401]

Hi

I am trying to declare and set worksheet and dynamic range variables globally. I have several worksheets and forms in a workbook and I have found it difficult to keep track of where all my variables are stored so that when I make any amendments to the location of the worksheet or range, they are done throughout any coding from 1 central variable declaration point.

Specically I have 3 main worksheets where the name of those worksheet should never change but they could be in future in future development. In each sub and form that references those worksheets I am constantly starting the code within the sub or form code to be
Dim wksMain As Worksheet, wksLists As Worksheet, wksWorkings As Worksheet
Set wksMain = Sheets("Main")
Set wksLists = Sheets("wksLists")
Set wksWorkings = Sheet("Workings")

In some forms or sub procedures, I have some variables for dynamic ranges that are set when the workbook is opened or changed when a form updates specific cells. These variables are again referenced in several sub and form procedures.

Dim lngCalcLastRow As Long
Dim rngCalc As Range

lngCalcLastRow = wksMain.Range("B" & wksMain.Rows.Count).End(xlUp).Row 'Find the last row with data in column B
Set rngCalc = wksMain.Range("B3:B" & lngCalcLastRow) 'creates the range with data from B3 to the last cell in Column B with data in it


What is the preferred and robust way of declaring these variables so that I can:
a) declare and set the objects in a central way
b) be able to refer to them from any sub procedure of form procedure in the workbook
c) Make it easier for me to keep track of them so if any of these variable objects need to change, I can update 1 place and not several.

Any help will be much appreciated

 

[ChrisM]

Kind of a textbook case on the need for global variables. You can insert a module and put them there, or put them in the "thisworkbook" module that already exists. Either place has the same level of scope. Use the Public keyword instead of Dim:

Public YourVar as String

If you go this route, you will need to assign values to the variables, most logically within the Workbook_Open event.

Or, you can instead declare them as constants, again at the top of a module:

Const myVar As String = "hi"

The constant route satisfies all three of your requirements.

 

[jacko2401]

Kind of a textbook case on the need for global variables. You can insert a module and put them there, or put them in the "thisworkbook" module that already exists. Either place has the same level of scope. Use the Public keyword instead of Dim:

Public YourVar as String

If you go this route, you will need to assign values to the variables, most logically within the Workbook_Open event.

Or, you can instead declare them as constants, again at the top of a module:

Const myVar As String = "hi"

The constant route satisfies all three of your requirements.

Hi Chris

Thanks for your comments on using global variables.

However, I tried to use const but it won't let me assign it as range or worksheet. It comes back with a Compile error on completing that line of code (e.g. Const mrVar As Worksheet = Sheets("Main")

I can create the global variable using Public myVar As Worksheet or PublicmyVar As Range at the top of a standard module. For these worksheets that won't change, I can set their values in the workbook_open event. This works well but I have problems with the dynamic ranges.

Since the ranges can be changed after the workbook is opened, I can't set values in the workbook_open event. Ideally I would like to find a way to centralise setting the value in 1 place? I have been thinking whether I can create a specific sub procedure that would set these common dynamic range variables and then call this sub produre from the specific sub of form procedure that requires them. Is this recommended or is there a disadvantage to this method? Something like:

Public lngCalcLastRow As Long
Public rngCalc As Range

Sub SetGlobals()

lngCalcLastRow = wksMain.Range("B" & wksMain.Rows.Count).End(xlUp).Row 'Find the last row with data in column B
Set rngCalc = wksMain.Range("B3:B" & lngCalcLastRow) 'creates the range with data from B3 to the last cell in Column B with data in it

End Sub


Sub mySub()

Call SetGlobals

End Sub