Change name of named range across VBA code

[danco172]

I've inherited some VBA code that makes use of named ranges in a shorthand way where "xTodaysPrice" is a named range of one cell:

If iCol = [xTodaysPrice].Column Then...

The name needs to be changed now to something else, e.g. "xAvgPrice".

Besides using search/replace across the whole code is there a better way of setting this up so that in the future there is only one location that needs to change the name of the named range and the code automatically picks this up?

Thanks,
Dan

 

[music43]

Not sure if this is exactly what you need:

Sub Lime()
    x = InputBox(Prompt:="Type the named range to be changed.")
    y = InputBox(Prompt:="Type the new name for this range.")
    Range(x).Name = y
    ActiveWorkbook.Names(x).Delete
End Sub

I hope this helps

 

[music43]

Sorry - I have just re-read what you are asking. You want to be able to change the range in the code.

In that case: If you set x = "AvSales" or whatever near the start of the code, anytime after that you refer to call the named range x. If you need to change the Range Name this should just mean a single range at the top of your code.

I hope this helps more than my last answer. At least someone may have some use for it...

 

[pgc01]

Hi Dan
Welcome to the board

Change the syntax and access the named range with a range object. This way you use a string that you can initialise with the name of the named range in just one place, like:

Dim sRange As String
 
'...
 
sRange = "xTodaysPrice"
 
'...
 
If iCol = Range(sRange).Column Then

 

[danco172]

Thank you, both. That works.

My user also likes to use the Name Manager UI to manage the named ranges. Is there a way to catch his changes and pass them on to the code?

E.g., if he just changes the name of a range, can I catch it and change my String variable accordingly?

Thanks,
Dan