Can we set sheet names and ranges as global variables?-CodePudding

I have three looping procedures that use a few sheets and a few ranges. I'd like to set this up so I can change the sheet names and ranges in one single place, rather than make the changes in three different places. Is that possible?

Now, I'm doing it like this.

' Loop One

Sub TestMe()

sheet_in = "Model In"
sheet_out = "Model Out"
range_in = "E10:AB300"
range_out = "E2"

Set sheet_in = Sheets(sheet_in).Range(range_in)
Set sheet_out = Sheets(sheet_out).Range(range_out) 
row_count = Sheets(sheet_in).Range(range_in).Rows.Count
col_count = Sheets(sheet_in).Range(range_in).Columns.Count

For j = 1 to col_count
   For i  = 1 to row_count
       range_out.Cells(K , 1).Value = range_in.Cells(i, j).Value
   Next i
Next j
End Sub

I feel like there's go to be a better way to do this.

CodePudding user response：

Consider defining constants in a dedicated module:

Public Const WS_SHEET_IN As String = "Model In"
Public Const RNG_SHEET_IN As String = "E10:AB300"

Public Const WS_SHEET_OUT As String = "Model Out"
Public Const RNG_SHEET_OUT As String = "E2"

In your regular code:

Sub TestMe()
    ' etc., etc.
    row_count = Worksheets(WS_SHEET_IN).Range(RNG_SHEET_IN).Rows.Count
End Sub

As always though "it depends" is the answer to what is the "best" way to organize your code.

For example:

Name your worksheet code module and use that name directly instead of referring to the tab name:

InSheet.Range(RNG_SHEET_IN)
OutSheet.Range(RNG_SHEET_OUT)

Use properties or functions to return commonly-accessed objects:

Function InRange() As Range
    Set InRange = thisworkbook.worksheets(WS_SHEET_IN).Range(RNG_SHEET_IN)
End function

Etc etc