Reusing DIM and Set statements
I have a large macro in VBA that I need to break up and use the call function. How do I enter the original dim and set statements (attached) so I don't have to reenter them when I "Call" a sub-routine so it will run error free.
Dim Region, RegionalReport, Region1, Region2, Region3 As Worksheet
Dim Region4, Region5, Region6, Region8, CCTV, Inspections As Worksheet
Dim RegionR1, RegionalReportR1, Region1R1, Region2R1, Region3R1 As Long
Dim Region4R1, Region5R1, Region6R1, Region8R1, CCTVR1, InspR1 As Long
Dim RegionC1, RegionalReportC1, Region1C1, Region2C1, Region3C1 As Long
Dim Region4C1, Region5C1, Region6C1, Region8C1, CCTVC1, InspC1 As Long
Dim nbr As Long, TabLoop As Worksheet, tabNbr As String
' Set Worksheets
Set Region = Sheets("Region")
Set RegionalReport = Sheets("Regional Report")
Set Region1 = Sheets("Region (1)")
Set Region2 = Sheets("Region (2)")
Set Region3 = Sheets("Region (3)")
Set Region4 = Sheets("Region (4)")
Set Region5 = Sheets("Region (5)")
Set Region6 = Sheets("Region (6)")
Set Region7 = Sheets("Region (7)")
Set Region8 = Sheets("Region (8)")
Set CCTV = Sheets("CCTV")
Set Inspections = Sheets("Inspections")
Put the DIM statements in a module, then they are available to all code.
You could put the Set statements into a sub which you can call from any point, or if you leave them where they are it should be OK, but a little untidy.
Hope that helps
You could put the Set statements into a sub which you can call from any point, or if you leave them where they are it should be OK, but a little untidy.
Hope that helps
If you want your variables to be used in more than one sub, do the declaration outside of a sub (and at the very top of the module).
Dim Region As Worksheet, RegionalReport As Worksheet, Region1 As Worksheet, Region2 As Worksheet, Region3 As Worksheet 'Correct way
Sub BigMacro1()
'Assigns worksheets to the worksheet variables
Call BigMacro2
End Sub
Sub BigMacro2()
'Uses the worksheet variables
End Sub
Dim Region As Worksheet, RegionalReport As Worksheet, Region1 As Worksheet, Region2 As Worksheet, Region3 As Worksheet 'Correct way
Sub BigMacro1()
'Assigns worksheets to the worksheet variables
Call BigMacro2
End Sub
Sub BigMacro2()
'Uses the worksheet variables
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is a discussion of scope and lifetime of VBA variables: http://www.ozgrid.com/VBA/variable-scope-lifetime.htm
Although you can extend the scope of variables outside a sub, doing so willy-nilly is not the best practice. If you have a fatal error while debugging your code, your variables lose their scope and value. From past experience, this will cause extensive project delays (several months in my case) while you debug the code.
Although you can extend the scope of variables outside a sub, doing so willy-nilly is not the best practice. If you have a fatal error while debugging your code, your variables lose their scope and value. From past experience, this will cause extensive project delays (several months in my case) while you debug the code.
ASKER
Thanks for the additional detail!
Dim Region, RegionalReport, Region1, Region2, Region3 As Worksheet 'Only Region3 is a Worksheet; the rest are Variant
Dim Region As Worksheet, RegionalReport As Worksheet, Region1 As Worksheet, Region2 As Worksheet, Region3 As Worksheet 'Correct way
Unlike some other languages, each VBA variable must be declared as something. If not, it will be a Variant.