Excel VBA named range running out of space in refersto

Question

So I have a large suite of code that creates an archive of data in sheets used by employees. Part of what makes this functional is named ranges on each sheet of usable data. In order for the data integrity to remain, I need to copy the named range objects from the archive sheet to its copy. The named ranges are built programatically and function as expected on the sheets. The problem I'm having is when I go to archive the sheet. Here is the code I'm using to handle the named range object:

For Each n In OldSht.Names
    NamedRangeRefersTo = n.RefersTo
    NamedRange = n.Name
    TrimmedName = Right(n.Name, Len(n.Name) - InStr(1, n.Name, "!", vbTextCompare))
    OldSht.Names(n.Name).Delete
    OldSht.Names.Add Name:=ArchiveNamedRange, RefersTo:=NamedRangeRefersTo
Next n

The strings that grab data from n are used to add the same name object to the new sheet.

The problem I'm having is when a named range is referencing too large of a range when it hits the line Oldsht.Names.Add, it returns error 1004. I figured out it was the size of the referenced range by messing around with it. I haven't found the exact triggering cause, but this code works as-is when I use it on most of the named ranges. On large data sets with a joined data type that results in a very large named range (it would take a long time to explain how the ranges are built in text. It's a group of 8 sub functions with over 2000 lines of code), this results in the 1004 error.

What I'm confused by is why I can build the named range, use the named range, and copy the named range without issues (if I comment out the offending line, it executes perfectly but I lose data integrity). But when I take the referenced range into a code value, delete the old name reference, then add a new name(with a different name) and assign it the same refersto value of the old name, it can have this problem. I don't understand how it would be different doing this rather than just copying/renaming the name object. Unfortunately, I haven't found a workaround as of yet, nor have I found a clear cause of this error other than the fact that when I remove data or use smaller sets of data in test scenarios, I never have the problem. Does anyone have any ideas of what I can do? Does anyone have any ideas how a named range could be referring to a small enough range that it can be created, but using its refersto value to create a new named range could cause errors only when that is referencing a large range?

I wish I could provide some more concrete examples but unfortunately it would be very difficult to scrub enough sensitive info to provide the full code that would be necessary to reproduce my exact scenarios. Any ideas would be much appreciated.

As requested here's where ArchiveNamedRange gets set:

If Len(OldSht.Name) > 21 Then
ArchiveShtName = Left(OldSht.Name, 21) & DatePart("m", Date) & DatePart("d", Date) & DatePart("yyyy", Date)
Else
ArchiveShtName = OldSht.Name & DatePart("m", Date) & DatePart("d", Date) & DatePart("yyyy", Date)
End If

ArchiveNamedRange = ArchiveShtName & NameObjectName & "Test"

NameObjectName is just the name of the type of object and is passed in from another function. I'm not having an issue with the name just fyi. In the most extreme example the ArchiveNamedRange value at debug run time is = "OutageSystemProcedureMMDDYYYYSecurityRedactionTest" so the name might reach 50 and if things get crazier it might run upwards of 60 characters but it won't ever go beyond that or come anywhere near the 255 character limit. Ultimately, I haven't seen ArchiveNamedRange have an invalid value. It's just a string and it always has a value.

Edit- Through my troubleshooting I've found that my code works when NamedRangeRefersTo has a length of 2075, but does not work when it has a length of 2091. So somewhere between 2075 characters and 2091 characters is a breaking point for assigning a string to RefersTo: in a named range.

So let's just assume there is a character limit for some reason of 2080 (or whatever it actually is between 2075 and 2091). When I initially find and create these named ranges, they are being given a range object. When I am copying the ranges I am copying as a string. Somehow when I pass a ranged object into RefersTo: it accepts characters beyond 2080 but when I pass in a string it does not. Given that this is my only breaking point of a large suite of code I'd rather find a workaround for this than have to re-factor the entire concept of my archive system. If I use a range object for copying the named ranges, their references follow the old Sheet. That means that when I copy the name over it can be "CriticalSystemsTest1" and referto: "CriticalSystemsTest1!$A$2,..." but once I copy that over and rename the archive worksheet (now CriticalSystems562015) the references adjust to be "CriticalSystems562015Test1!$A$2,..."

So I had to copy as a string to avoid that problem (it breaks data on the new sheet). All I really need is a creative way to overcome this character limit issue on my string. Rebuilding the named range from scratch on the new sheet is also not going to work. So I guess if anyone has ideas for how to work around this string size issue or a way of trimming the string while maintaining functionality of the named range, that would be amazing.

Each of these names has a worksheet level scope, so maybe if there's a way of using just the cell address($A$2) in RefersTo: so it doesn't also contain the worksheet reference (SheetName!), that would be a potential solution but I haven't figured out if that's even possible.

Answer 1

The reason the range definitions as strings are so long is that there are many areas within them. So one workaround would be to build up a new Range object area by area. You can use the string address of each area without running into any limits as each area only has a short reference. Using Range.Address gets the cell reference without the sheet reference, so you can create a new Range on a different sheet but with the same cells. Then use Union() to join all the areas and create the new name using the newly built Range instead of a string:

    Dim i As Long, oldRange As Range, newRange As Range

    Set oldRange = n.RefersToRange
    Set newRange = oldSht.Range(oldRange.Areas(1).Address(External:=False))
    For i = 2 To oldRange.Areas.Count
            Set newRange = Union(newRange, oldSht.Range(oldRange.Areas(i).Address(External:=False)))
    Next i
    oldSht.Names.Add Name:="ArchiveNamedRange", RefersTo:=newRange

A couple of notes:

For ranges with many areas this is slow. If you can reliably tie down the threshold where you have problems, it would probably be worth testing for this first and only using this workaround where it was needed.

When testing I also ran into problems with using Worksheet.Range("some very long string range reference"), so this limitation isn't confined to named ranges.