Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Reusing DIM and Set statements

Posted on 2011-09-28
7
Medium Priority
?
409 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
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

877 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