Proper procedure for declaring global variables in VBA?

Question

I have a macro workbook with a number of worksheets that exist permanently, which are constantly cleared, updated, etc. Since they are referred to in various subroutines, I have made each corresponding worksheet object a pseudo-global variable in the following manner, for example for the "Main" sheet:

    Function MAIN() As Worksheet
        Set MAIN = ThisWorkbook.Sheets("Main")
    End Function

By doing so, I can then refer to each sheet in the other subroutines, for example:

    MAIN.Cells.ClearContents

I have also defined some pseudo-global constants which are located in a fixed place on the "Main" sheet in a similar way, for example:

    Function NumLines() As Integer
        NumLines = MAIN.Range("C3").Value
    End Function

In this way, I use "NumLines" just like any variable throughout the code. I expect that there is a more efficient way to manage globally accessed variables like these and was wondering, what would be a better way to accomplish this?

Answer 1

For reliable sheet reference I would suggest to use Sheet.CodeName Property. Each sheet has its unique CodeName which you could find in the place marked yellow on the picture below.

For quick reference to cell value I would suggest to use Range name. After you select you C3 cell you need to put unique name in the box marked yellow below. All Range names are unique in the workbook.

As a result you can use sheet and cell reference as presented below in each of your subroutines in your project.

Sub Test_Macro()

    Debug.Print MAIN.Name   '>> result: Sheet1
    Debug.Print Range("CellC3").Value   '>> result: 100

End Sub