Use calculated variables globally

VorLag

Board Regular
Joined
May 13, 2013
Messages
205
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?

Code:
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,324
You can apply different scoping to variables.

Consider the following:
Code:
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:
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" :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,918
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top