Binary Search and String Order Position

[Eldrod]

Hey, I'm hoping someone has a quick fix for this situation. I have a macro that renames specific named range names from a list. In the spreadsheets I run this against, there may be 10,000+ named ranges so it takes way too long to search my Named range Variant to locate the one to change. In order to rename a Named Range name without losing the RefersTo: part of it, I load a variant like ListOfNames = Activeworkbook.Names then use an index like ListofNames(Pointer) = NewNRName to rename it.

I'm using a binary subroutine to more quickly locate the matching entry in the ListOfNames, but I have run into a snag with names that contain numbers.

The ListOfNames table is automatically sorted when produced in such as way that "zzz_item_2_data" comes before "zzz_item_20_data". But when I run the routine, every comparison I use results in that first one being greater than the second item.

So lets say:
Needle = "zzz_item_2_data"
Haystack = "zzz_item_20_data"

StrComp(Needle,Haystack,vbBinaryCompare)
StrComp(Needle,Haystack)
If Needle < Haystack

all say Needle is greater than Haystack.

I know the issue is the "0" after the 2 on Haystack but need a way to cause the compares to give the same positioning as ListOfNames.

Any ideas? Thanks!

 

[AlphaFrog]

I would suggest you use a naming convention where each number within the names are 5 digits.

zzz_item_00002_data
zzz_item_00020_data

That way they are alphabetically and numerically in order.

You could write a routine to rename them all. It would be a bit of a pain but you would only have to do it once.

 

[Dan Waterloo]

Hi Eldrod,

I think you can use the StrComp function as long as you specify you want to do a textual comparison. That's the purpose of the "1" as the third argument in the funtion call below:

Sub TestGreater()
  MsgBox StrComp(Needle, Haystack, 1)
End Sub

 

[Eldrod]

Thanks, guys! Looks like the vbTextCompare option will probably work. I was concerned about its case insensitivity as these named ranges could have variable case in them, but I think I can work with this option.