Range object

D

Desmond

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
 
T

Tod

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

MyRange = "A1:A100"

ThisWorkbook.Worksheets(SheetOfMyChoice).Range(MyRange)

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
 
B

Bob Phillips

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

Bob Phillips

You most certainly can.

Select Sheet1.
Go into Insert>Names>Define Name and give a name of =Sheet1!myName, Refersto
=$A$1


Select Sheet2.
Go into Insert>Names>Define 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)
 
S

Stefano Gatto

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
 
Top