Use calculated variables globally

[VorLag]

All of my macros work, but I'm trying to get them to work more efficiently and make it easier to make any updates that might be needed in the future. I know that other people move calculated variables into their own functions and are able to integrate that into their macro.

If I can get an example of how to do it, I can go about getting the practice of doing this a couple dozen times myself. I've found myself using the same functions over and over again instead of using this other, efficient way other people use.

Here is one snippet that I use a lot to build the beginnings of a dynamic filepath that is in a bunch of macros that should/could be its own function. How do I set it up so that other macros can reference the variables "monabbr", "folnum", and "srchmnth" without having to use the full snippet?

SrchMnth = Range("C2").Value
MonAbbr = Left(SrchMnth, 3)

    If SrchMnth = "January" Then
    FoldNum = "01."
    ElseIf SrchMnth = "February" Then
    FoldNum = "02."
    ElseIf SrchMnth = "March" Then
    FoldNum = "03."
    ReportQ = 1
    ElseIf SrchMnth = "April" Then
    FoldNum = "04."
    ElseIf SrchMnth = "May" Then
    FoldNum = "05."
    ElseIf SrchMnth = "June" Then
    FoldNum = "06."
    ReportQ = 2
    ElseIf SrchMnth = "July" Then
    FoldNum = "07."
    ElseIf SrchMnth = "August" Then
    FoldNum = "08."
    ElseIf SrchMnth = "September" Then
    FoldNum = "09."
    ReportQ = 3
    ElseIf SrchMnth = "October" Then
    FoldNum = "10."
    ElseIf SrchMnth = "November" Then
    FoldNum = "11."
    ElseIf SrchMnth = "December" Then
    FoldNum = "12."
    ReportQ = 4
    Else: MsgBox "Invalid Month. Enter the full month name without any extra characters."
        Exit Sub
    End If

 

[RickXL]

You can apply different scoping to variables.

Consider the following:

Public v1 As Long
Dim v2 As Long
Sub xx()
    Dim v3 As Long
    v1 = 99
    v2 = 98
    v3 = 97
    Debug.Print v1; v2; v3
End Sub
Sub yy()
    Debug.Print v1; v2; v3
End Sub

If I run macro xx I will see 99 98 97 displayed in the Immediate window.

If I then run macro yy I will see just 99 and 97 in the Immediate window. This is becvause v3 is only known to xx because v3 was Dim'd there.

If I now create a new Module and insert the following code:

Sub zz()
    Debug.Print v1; v2; v3
End Sub

When I run it I will only see 99 because only v1 is Public. The Dim outside the macro in the first Module restricted the use of v2 to the first Module.

So, if you make all your variables Public they will be available everywhere.

The problem with global variables is that in five years time when you are bolting together some macros you will have forgotten which ones you used and there will be clashes that cause strange problems!


Probably a better way is to learn about Classes but I will leave that for "homework"