Localisation of Range Names

[Guest]

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

 

[Peter T]

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

 

[Guest]

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

 

[jkpieterse]

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

 

[Guest]

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