4

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?

Improve this question
1
  • 3
    +1: For asking a nice question, framing it properly, having a brave attempt of your own, and just for the curiosity and drive to improve. Refreshing stuff, this. Commented Jan 14, 2014 at 17:03

2 Answers 2

Reset to default
4

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@KazJaw: Thanks for the very simple solution to the global constants side of things. Up until now I've been having to change the range reference in the code everytime I move things around on the sheet. I knew about range naming before and used it for other things but never knew about the VBA connection. This is great.
4

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.

Improve this answer

3 Comments

+1: For a best practices tip and for Option Explicit.
@enderland: Thanks for your help. Why is "option explicit on" more important when dealing with global variables rather than just basic subroutines? (I am notorious for leaving most of my variables undeclared, which I should work on probably.)
@teepee sorry for never answering, but because with global variables you have them scattered throughout your code rather than simply in a single place (one method). At least in that situation you are able to see the other uses of the variable to "check" spelling. Globals have no such ability.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.