CODEWITHSUNDEEP
vbaexcelglobal-variables
teepee
teepee

Reputation: 2714

Proper procedure for declaring global variables in VBA?

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?

Upvotes: 4

Views: 1186

Answers (2)

enderland
enderland

Reputation: 14145

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?

When I use global variables in VBA, I do three things.

  1. I always preface global variables with a g_ prefix. It seems often that a global variable in VBA is useful. But I've also spent far too long trying to track down "what variables are global or not?" in other people's code. Keeping a very clear naming convention will save you and whoever looks at your code a TON of hassle in the future.

    This is even more important if you are less experienced as a developer. Avoiding globals is hard in VBA, and the less experience you have, the more likely it is you will use globals. For others to help or maintain the code this becomes so important.

  2. If you are going to be using even a small number of global variables, you must use Option Explicit unless you want to cause nightmares in maintaining code. It's hard enough to track down these errors when you wrote code let alone months or years later.

  3. I always create a module which is called "GlobalVariables" or something similar. That module contains all of the global declarations in one location. For larger code bases this can become longer but it has always paid off for me because I know exactly where all my globals are defined. None of the "which file is this variable actually being defined in?" game.

Just an unrelated note, too, in your first example - I would use the code name rather than that function. Each VBA worksheet has a sheet name ("Main" in your case) as well as a codename, which you can set in VBA and remains the same. This prevents users from changing the name of "Main" and breaking code.

You can also refer directly to them similar to how you are using MAIN.Cells. KazJaw has a good example of this.

Upvotes: 4

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

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.

enter image description here

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.

enter image description here

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

Upvotes: 4

Related Questions