Range object
 

Does anyone know if the range object is declared global by
default. that is, could I cuse the range from any where in
the workbook without keep switching to the worksheet that
the named range is located.

For example is I name a range MyRange on sheet1 could I
address that MyRange while performing some operation on
sheet2?

I hope I presented my question in an understandable format.

Thanks
Desmond

Range object
 

If you are wanting to refer to the same range of cell
addresses regardless of which sheet, I think your best bet
would be to declare the cell range as a string, like:

Public MyRange as String

Then in a procedu

MyRange = "A1:A100"

ThisWorkbook.Worksheets(SheetOfMyChoice).Range(MyR ange)

You could also use a constant so you don't have to keep
assigning the string.

Public Const MyRange as String = "A1:A100"

Now MyRange will always = "A1:A100"

tod

-----Original Message-----
Does anyone know if the range object is declared global
by
default. that is, could I cuse the range from any where
in
the workbook without keep switching to the worksheet that
the named range is located.

For example is I name a range MyRange on sheet1 could I
address that MyRange while performing some operation on
sheet2?

I hope I presented my question in an understandable
format.

Thanks
Desmond
.

Range object
 

Desmond,

Absolutely. You can access a range without ever going near it in most
instances. As you have defined the name MyRange, the Range("MyRange") can be
accessed whether that sheet is active or not.

It is a good idea to go one step further and define a range object. For
instance

Dim oRng as Range

Set oRng = Range("MyRange")

You then have an object range that is in memory, so that each subsequent
time that you access the range via this object, VBA does not have to look it
up, so it is more efficient. Again, this can be accessed regardless of the
active sheet.

This also saves the innumerable Selects that you see in much code, which is
very inefficient. It is the Select statement that requires the sheet to be
active.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Desmond" wrote in message
...
Does anyone know if the range object is declared global by
default. that is, could I cuse the range from any where in
the workbook without keep switching to the worksheet that
the named range is located.

For example is I name a range MyRange on sheet1 could I
address that MyRange while performing some operation on
sheet2?

I hope I presented my question in an understandable format.

Thanks
Desmond

Range object
 

You most certainly can.

Select Sheet1.
Go into InsertNamesDefine Name and give a name of =Sheet1!myName, Refersto
=$A$1


Select Sheet2.
Go into InsertNamesDefine Name and give a name of =Sheet2!myName, Refersto
=$B$99

Piut some values in those cells, and add a formula in Sheet 1 of =myName,
and ditto in Sheet2. You will see they return different values.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Stefano Gatto" wrote in message
...
Desmond,

Aren't you talking about Names???

Names can contain ranges (beyond strings, numbers, errors, etc) and are
global to the workbook, except PrintArea or other reserved names like
that.
Actually I never understood if it's possible to restrict visibility of
Names
to a given worksheet or not. I think this has changed over the several
releases of Excel I worked on, so I think I need to catch up now.

Can someone else help here?

Stefano

"Bob Phillips" wrote in message
...
Desmond,

Absolutely. You can access a range without ever going near it in most
instances. As you have defined the name MyRange, the Range("MyRange")
can
be
accessed whether that sheet is active or not.

It is a good idea to go one step further and define a range object. For
instance

Dim oRng as Range

Set oRng = Range("MyRange")

You then have an object range that is in memory, so that each subsequent
time that you access the range via this object, VBA does not have to
look
it
up, so it is more efficient. Again, this can be accessed regardless of
the
active sheet.

This also saves the innumerable Selects that you see in much code, which
is
very inefficient. It is the Select statement that requires the sheet to
be
active.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Desmond" wrote in message
...
Does anyone know if the range object is declared global by
default. that is, could I cuse the range from any where in
the workbook without keep switching to the worksheet that
the named range is located.

For example is I name a range MyRange on sheet1 could I
address that MyRange while performing some operation on
sheet2?

I hope I presented my question in an understandable format.

Thanks
Desmond

Range object
 

Desmond,

Aren't you talking about Names???

Names can contain ranges (beyond strings, numbers, errors, etc) and are
global to the workbook, except PrintArea or other reserved names like that.
Actually I never understood if it's possible to restrict visibility of Names
to a given worksheet or not. I think this has changed over the several
releases of Excel I worked on, so I think I need to catch up now.

Can someone else help here?

Stefano

"Bob Phillips" wrote in message
...
Desmond,

Absolutely. You can access a range without ever going near it in most
instances. As you have defined the name MyRange, the Range("MyRange") can
be
accessed whether that sheet is active or not.

It is a good idea to go one step further and define a range object. For
instance

Dim oRng as Range

Set oRng = Range("MyRange")

You then have an object range that is in memory, so that each subsequent
time that you access the range via this object, VBA does not have to look
it
up, so it is more efficient. Again, this can be accessed regardless of the
active sheet.

This also saves the innumerable Selects that you see in much code, which
is
very inefficient. It is the Select statement that requires the sheet to be
active.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Desmond" wrote in message
...
Does anyone know if the range object is declared global by
default. that is, could I cuse the range from any where in
the workbook without keep switching to the worksheet that
the named range is located.

For example is I name a range MyRange on sheet1 could I
address that MyRange while performing some operation on
sheet2?

I hope I presented my question in an understandable format.

Thanks
Desmond