Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Reusing DIM and Set statements

Posted on 2011-09-28
7
Medium Priority
?
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 81

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 81

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Assisted Solution

by:plummet
plummet earned 400 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 81

Accepted Solution

by:
byundt earned 1600 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 81

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

719 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