Solved

Reusing DIM and Set statements

Posted on 2011-09-28
7
380 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 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 81

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel file not created as expected 7 50
Hiding column macro 10 28
Delete texts with font color 16 30
excel connection properties parameters grayed out 5 27
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

920 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

15 Experts available now in Live!

Get 1:1 Help Now