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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
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" :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,091
Messages
5,857,310
Members
431,870
Latest member
Muratculous

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