Localisation of Range Names

Hi,

I would like to "localise" range names using VBA code.

Local range names (as I understand) are range names that are attached to the
sheet name on which they reside - so you can have several of the same range
names, but each one on a different sheet in the same workbook.

These range names have the sheet name infront of them separated by a "!"

e.g. sheet1!range1

I could, of course, create a string from the individual components and then
assign the address of cells represented by range1 to it - but it wouldn't be
very neat....

I just want to take range1 and localise it.

Ideas...?? Thanks...Chris

Hi Chris,

Code to do that would go something like this

-loop names at workbook-level
if InStr(nm.Name, "!") ignore, it's already a local name
set rng = nm.RefersToRange
if this succeeds (it's a range name), add a new similar name prefixed with
apostrophe & rng.parent.name & apostrophe & ! & nm.name refersto rng
(also having checked same local name doesn't already exist)
delete the old name

But -
- there are loads of pitfalls
- all the hard work has been done very well !

Get hold of the NameManager addin which you can get from the authors' sites
of Jan Karel Pieterse and Charles Williams (download sections).

www.jkp-ads.com
www.DecisionModels.com

Regards,
Peter T


"Chris Gorham" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I would like to "localise" range names using VBA code.
>
> Local range names (as I understand) are range names that are attached to
the
> sheet name on which they reside - so you can have several of the same
range
> names, but each one on a different sheet in the same workbook.
>
> These range names have the sheet name infront of them separated by a "!"
>
> e.g. sheet1!range1
>
> I could, of course, create a string from the individual components and
then
> assign the address of cells represented by range1 to it - but it wouldn't
be
> very neat....
>
> I just want to take range1 and localise it.
>
> Ideas...?? Thanks...Chris
>
>

Hi Chris,

Code to do that would go something like this

-loop names at workbook-level
if InStr(nm.Name, "!") ignore, it's already a local name
set rng = nm.RefersToRange
if this succeeds (it's a range name), add a new similar name prefixed with
apostrophe & rng.parent.name & apostrophe & ! & nm.name refersto rng
(also having checked same local name doesn't already exist)
delete the old name

But -
- there are loads of pitfalls
- all the hard work has been done very well !

Get hold of the NameManager addin which you can get from the authors' sites
of Jan Karel Pieterse and Charles Williams (download sections).

www.jkp-ads.com
www.DecisionModels.com

Regards,
Peter T


"Chris Gorham" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I would like to "localise" range names using VBA code.
>
> Local range names (as I understand) are range names that are attached to
the
> sheet name on which they reside - so you can have several of the same
range
> names, but each one on a different sheet in the same workbook.
>
> These range names have the sheet name infront of them separated by a "!"
>
> e.g. sheet1!range1
>
> I could, of course, create a string from the individual components and
then
> assign the address of cells represented by range1 to it - but it wouldn't
be
> very neat....
>
> I just want to take range1 and localise it.
>
> Ideas...?? Thanks...Chris
>
>

Thanks, I'll try this tomorrow....

I'm writing my own Range Name Manager...feel free to check out my website
www.mastertool.co.uk which contains all my add-ins...the linktracer is
amazing...

This version is the one currently being rewritten....

Chris

"Peter T" wrote:

> Hi Chris,
>
> Code to do that would go something like this
>
> -loop names at workbook-level
> if InStr(nm.Name, "!") ignore, it's already a local name
> set rng = nm.RefersToRange
> if this succeeds (it's a range name), add a new similar name prefixed with
> apostrophe & rng.parent.name & apostrophe & ! & nm.name refersto rng
> (also having checked same local name doesn't already exist)
> delete the old name
>
> But -
> - there are loads of pitfalls
> - all the hard work has been done very well !
>
> Get hold of the NameManager addin which you can get from the authors' sites
> of Jan Karel Pieterse and Charles Williams (download sections).
>
> www.jkp-ads.com
> www.DecisionModels.com
>
> Regards,
> Peter T
>
>
> "Chris Gorham" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I would like to "localise" range names using VBA code.
> >
> > Local range names (as I understand) are range names that are attached to
> the
> > sheet name on which they reside - so you can have several of the same
> range
> > names, but each one on a different sheet in the same workbook.
> >
> > These range names have the sheet name infront of them separated by a "!"
> >
> > e.g. sheet1!range1
> >
> > I could, of course, create a string from the individual components and
> then
> > assign the address of cells represented by range1 to it - but it wouldn't
> be
> > very neat....
> >
> > I just want to take range1 and localise it.
> >
> > Ideas...?? Thanks...Chris
> >
> >
>
>
>

HI CHris,

> I'm writing my own Range Name Manager...feel free to check out my website
> www.mastertool.co.uk which contains all my add-ins...the linktracer is
> amazing...

I'll check it out!

Regards,

Jan Karel Pieterse
Excel MVP
JKP Application Development Services
http://www.jkp-ads.com

please feel free to do so...but note that I'm 70% through a major upgrade.

I've looked at this localisation issue today...Peter's suggestion was really
a better way of coding my idea...but he has indicated via email that there
are problems ahead and indeed it appears that when a sheet name contains a
character such as "!" then it falls over. This is because Excel for some
reason starts putting the character ' at each end of the sheet name.

I've circuimvented this by always changing the sheet name to a temporary one
and then swopping it back...localisation then works fine using the code (or
close to it) from Peter.

Chris

"[email protected]" wrote:

> HI CHris,
>
> > I'm writing my own Range Name Manager...feel free to check out my website
> > www.mastertool.co.uk which contains all my add-ins...the linktracer is
> > amazing...
>
> I'll check it out!
>
> Regards,
>
> Jan Karel Pieterse
> Excel MVP
> JKP Application Development Services
> http://www.jkp-ads.com
>
>