Same Variable to be used in different Sub's

  • Thread starter Thread starter M Chahal
  • Start date Start date
M

M Chahal

Hi,

I need to create variables for worksheet names and the same for filter
criteria that will be used in different subs in the module - do i need to
'declare' each time in a new sub?

ex:
Sub X1()
V1 = "GI-ACE"
Sheets(V1).Select
Call S
End Sub

Sub S()
ActiveSheet.UnProtect ("util")
Range("B43:BW200").Select
Selection.ClearContents
Sheets("Data").Select
Selection.AutoFilter Field:=1, Criteria1:=V1 '#####this is where it fails'
Range("E3:G800").Select
Selection.Copy

Sheets(V1).Select '#####this is where it fails'
Range(B42).Select
Selection.PasteSpecial Paste:=xlPasteValues

any help appreciated
 
On Fri, 5 Mar 2010 05:24:01 -0800, M Chahal

Your code currently would not even compile, at least not if you put
the HIGHLY RECOMMENDED:
Option Explicit
at the top of each module. Also set the default in Code Window > Tools
Options > Require Variable Declaration.
Click to expand...

One option would be to use a Constant in the Declaration section (at
the top) of your Module:
Const ACE_SHEET as String = "GI-ACE"

Then everywhere you need it you can write code like:
Selection.AutoFilter Field:=1, Criteria1:=ACE_SHEET
 
hi,

I need to create variables for worksheet names and the same for filter
criteria that will be used in different subs in the module - do i need to
'declare' each time in a new sub?
Click to expand...
No, you don't as you have more than one way to solve it. But will
comment your code to show you mine best practice here.

Each module must start with

Option Compare Database
Option Explicit

The compare option is create always automatically. The create the
explicit option also, go to Tools\Options\Editor in the VBA IDE an
activate 'Require Variable Declaration'. This option ensures that each
variable is declared explicitly with the Dim statement to avoid this case:

Public Sub Test()

Message = "Test"
MsgBox Messsage

End Sub

This code will run, but display an empty message box. Using Option
Explicit you will get an compiler error.
Sub X1()
Click to expand...
Always declare your methods using an explicit scope modifier:

Public|Private Sub|Function FooBar()
V1 = "GI-ACE"
Sheets(V1).Select
Call S
End Sub
Click to expand...

Change it to:

Public Sub X1()

Dim sheetName As String

Sheets(sheetName).Select
S sheetName

End Sub

Public Sub S(ASheetName As String)

ActiveSheet.UnProtect "util"
Range("B43:BW200").Select
Selection.ClearContents
Sheets("Data").Select
Selection.AutoFilter Field:=1, Criteria1:=ASheetName
Range("E3:G800").Select
Selection.Copy
Sheets(ASheetName).Select
' Range(B42).Select
' This line above should fail also, as the variable B42 is not declared
' and initalized with a value.
Selection.PasteSpecial Paste:=xlPasteValues

End Sub



mfG
--> stefan <--
 
Public Sub X1()

Dim sheetName As String

Sheets(sheetName).Select
S sheetName

End Sub
Click to expand...
Either

Public Sub X1()

Dim sheetName As String

sheetName = "WhatEver"
Sheets(sheetName).Select
S sheetName

End Sub

or simpler

Public Sub X1()

Sheets("WhatEver").Select
S "WhatEver"

End Sub


mfG
--> stefan <--
 
M Chahal said:
Hi,

I need to create variables for worksheet names and the same for filter
criteria that will be used in different subs in the module - do i need to
'declare' each time in a new sub?

ex:
Sub X1()
V1 = "GI-ACE"
Sheets(V1).Select
Call S
End Sub

Sub S()
ActiveSheet.UnProtect ("util")
Range("B43:BW200").Select
Selection.ClearContents
Sheets("Data").Select
Selection.AutoFilter Field:=1, Criteria1:=V1 '#####this is where it
fails'
Range("E3:G800").Select
Selection.Copy

Sheets(V1).Select '#####this is where it fails'
Range(B42).Select
Selection.PasteSpecial Paste:=xlPasteValues

any help appreciated
Click to expand...
 
Back
Top