Declaring Range

[fi.or.jp.de]

Hi All,

Is there any option to declare a range publicly.

I have more than 10 to 15 modules

I need to set the Rng as Range

i.e., set Rng = sheets("Sheet1").Range("A:A")
set Rng1 = Sheets("sheet1").Range("K:K")

.....So on..

I have to do, in each module i need to set the Rng or Rng1 or Rng2
etc....

Can i public declare or set the Ranges. so that I can use in any
module.

Please let me know, how can i achieve this.

Thanks in advance.

 

[Dave Peterson]

Yes.

I like to use this technique.

I'll dedicate a General module that contains these public variables.

Then inside this General module, I'll use:

Option Explicit
Public Rng as Range

But since these variables can lose their contents, I'll have a couple more items
to this module.

One is a flag variable and one is a procedure that re-initializes these
variables (just in case).

Option Explicit
Public Rng as Range
Public VarsAreInitialized as Boolean 'my flag variable

Sub InitializeTheVariables()
VarsAreInitialized = true 'the flag that keeps track

'your code for setting Vars
Set Rng = sheets("Sheet1").Range("A:A")

End Sub

============
Now in any old procedure, I can use that variable -- but I'll check first.


if varsareinitialized = false then
call InitializeTheVariables
end if
msgbox Rng.address(external:=true)

==================
All that said, unless that variable always points to the same stuff (or value or
...), it's usually better practice to declare and assign the variables in the
routine that needs them.

You may want to look at VBA's help for "scope and visibility" for more info.

 

[fi.or.jp.de]

Thanks dave

Yes.

I like to use this technique.

I'll dedicate a General module that contains these public variables.

Then inside this General module, I'll use:

Option Explicit
Public Rng as Range

But since these variables can lose their contents, I'll have a couple more items
to this module.

One is a flag variable and one is a procedure that re-initializes these
variables (just in case).

Option Explicit
Public Rng as Range
Public VarsAreInitialized as Boolean 'my flag variable

Sub InitializeTheVariables()
    VarsAreInitialized = true 'the flag that keeps track

    'your code for setting Vars
    Set Rng = sheets("Sheet1").Range("A:A")

End Sub

============
Now in any old procedure, I can use that variable -- but I'll check first..

if varsareinitialized = false then
    call InitializeTheVariables
end if
msgbox Rng.address(external:=true)

==================
All that said, unless that variable always points to the same stuff (or value or
  ...), it's usually better practice to declare and assign the variables in the
routine that needs them.

You may want to look at VBA's help for "scope and visibility" for more info.