Solved

Reusing DIM and Set statements

Posted on 2011-09-28
7
379 Views
Last Modified: 2012-05-12
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")

Open in new window

0
Comment
Question by:bearblack
  • 4
  • 2
7 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 36718934
First of all, your variables aren't being declared as the type of variables you expect. Many of your variables are actually being declared as Variants.

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.
0
 
LVL 10

Expert Comment

by:plummet
ID: 36718942
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
0
 
LVL 80

Expert Comment

by:byundt
ID: 36718948
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
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 10

Assisted Solution

by:plummet
plummet earned 100 total points
ID: 36718957
byundt is quite right, and another good idea is to prefix the variable name with something meaningful so you can tell what it is, for example "ws" for worksheet:

Dim wsRegion As Worksheet, wsRegionalReport As Worksheet, wsRegion1 As Worksheet, wsRegion2 As Worksheet, wsRegion3 As Worksheet      

0
 
LVL 80

Accepted Solution

by:
byundt earned 400 total points
ID: 36718969
Note that variables in the Dim statement outside the sub are valid for subs and functions on that module sheet. They do not have scope for other module sheets. You need the Public keyword if you want the scope to extend to other module sheets.

Public Region As Worksheet, RegionalReport As Worksheet, Region1 As Worksheet, Region2 As Worksheet, Region3 As Worksheet       'These variables can be used by any sub on any module sheet

Sub BigMacro1()
'Assigns worksheets to the worksheet variables
Call BigMacro2
End Sub

Sub BigMacro2()
'Uses the worksheet variables
End Sub
0
 
LVL 80

Expert Comment

by:byundt
ID: 36719007
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.
0
 
LVL 2

Author Closing Comment

by:bearblack
ID: 36719069
Thanks for the additional detail!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now