worksheet Names 255 or less byte truncation - bug?

[TheAntiGates]

I've learned not to shout "BUG!" in case there's an explanation or if it's a documented "feature" but AAR this one is easily reproducible.

The code below suggests that there is a limit of 255 or 258 or 238 bytes for some named ranges that consist of values; variations on the code probably produce numerous truncations at less than 255 bytes.

Run the code, and everything notable is in msgboxes. Note, I only tested XL03 so YMMV. You should get an error after the 2nd msgbox; the final 3 msgboxes should each report less than 300 if they misbehave for you as they do for me.

Note that this uses a specialized .Names element, not the common range defined by worksheet addresses.
Sub Names255_and_258_and_238ByteTruncation()
Dim sByte As String, sStr As String, i As Long, j As Long
Dim myArray(1 To 300, 1 To 2), myVariant

On Error GoTo Heck
sStr = ""
For i = 1 To 25
sByte = Chr(Asc("A") + i - 1) ' "A" then "B" etc.
For j = 1 To 10
sStr = sStr & sByte '"AAAAAAAAAABBBBBBBBBBCCCCCCCCC etc.
Next j
Next i
MsgBox "Okay length and string: " & Len(sStr) & " <" & sStr & ">"
ThisWorkbook.Names.Add "aRange", sStr 'works.

'Do same thing but 260 bytes, not 250
sStr = ""
For i = 1 To 26
sByte = Chr(Asc("A") + i - 1)
For j = 1 To 10
sStr = sStr & sByte
Next j
Next i
MsgBox "Too long length and string: " & Len(sStr) & " <" & sStr & ">"
ThisWorkbook.Names.Add "bRange", sStr 'BOOM. Too big.

'Now the 258 byte truncation:
On Error GoTo 0
For i = 1 To 300
myArray(i, 1) = Chr(Asc("A") + (i Mod 26) - 1)
myArray(i, 2) = Chr(Asc("A") + (i Mod 26) - 1)
Next i
ThisWorkbook.Names.Add "Range258", myArray
MsgBox "This sure must be at least 300, but it's " & Len(ActiveWorkbook.Names("Range258").RefersTo) & " and doesn't even have a closing brace: <" & ActiveWorkbook.Names("Range258").RefersTo & ">"

'Now the 238 byte truncation: (note, sometimes I got 245)
On Error GoTo 0
For i = 1 To 300
myArray(i, 1) = 0.01 * i * j 'DIFFERENT THAN ABOVE
myArray(i, 2) = 0.01 * i * j 'DIFFERENT THAN ABOVE
Next i
ThisWorkbook.Names.Add "Range238", myArray
MsgBox "This sure must be at least 300, but it's " & Len(ActiveWorkbook.Names("Range238").RefersTo) & " and doesn't even have a closing brace: <" & ActiveWorkbook.Names("Range238").RefersTo & ">"

'This variant assignment will only pick up 238 bytes anyway, so the data truly is gone (truncated)
myVariant = ThisWorkbook.Names("Range238")
MsgBox "This sure must be at least 300, but it's " & Len(myVariant) & " and doesn't even have a closing brace: <" & myVariant & ">"
Exit Sub
Heck:
MsgBox "More than 255 gave error " & Err.Number & ":" & Err.Description
Resume Next
End SubNote that it's not just .RefersTo that is short. The ranges (i.e. .Names) themselves are nastily truncated. Not only are they truncated, they can end without closing quotes or delimiters.

[Aflatoon]

https://support.office.com/en-us/article/Define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64

Note the part about Name length about a third of the way down.

[snb]

Did you know ?

msgbox len(string(300,"_"))

and


msgbox len(space(300))

[TheAntiGates]

https://support.office.com/en-us/article/Define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64

Note the part about Name length about a third of the way down.I believe that is talking about the .Name property of names, not the content (.RefersTo)

(Hmm, as to "content," I wonder if .Value and .RefersTo ever differ, thinking aloud, not important)

[TheAntiGates]

Did you know ?

msgbox len(string(300,"_"))

and


msgbox len(space(300))Sorry, don't follow your point; both give 300 here.

[Aflatoon]

The limit applies to all the fields in the Name dialog. Try manually entering more than 255 characters.

Note: the limit only applies to strings, as far as I am aware.

[TheAntiGates]

By that I guess that .Name, .Value, and all the .refers... properties are all capped at 255. I'll buy that.

Any ideas on those limitations that are different than 255 in first post? (Sorry the code snippet looks long, but it's simple stuff, and it's quick to walk through, or just F5 it and watch the msgboxes)

[Aflatoon]

As I mentioned, the limit applies to strings, not arrays. There was a fixed limit to array size in 97 and 2000, but AFAIK, it's only limited to available memory in later versions.

[TheAntiGates]

As I mentioned, the limit applies to strings, not arrays. There was a fixed limit to array size in 97 and 2000, but AFAIK, it's only limited to available memory in later versions.Yes, I'm talking about strings. Look at (or rather, run) the code. Maybe you just glanced at the loop variables and assumed that I was indicating a limit on the number of assignments(i.e. array elements); rather, it builds up strings that would achieve string lengths of 300 or greater, but then getting Len <> 255.

[Aflatoon]

You are assigning arrays, not strings, to the name. The arrays may contain strings but that is not the same thing.

[TheAntiGates]

Original question is still unanswered if anyone else can explain what's going on. The issue(s) is excruciatingly obvious if anyone just runs the code: just start a workbook, insert module1, paste and run. F8 and shift-F8 through it if you're concerned; that's what I always do. You should get an [innocuous] error after the 2nd msgbox; the final 3 msgboxes should each report less than 300 if they misbehave for you as they do for me. I would have said "less than 256" except .... as you'll see ... not quite.

[mikerickson]

I've found that if you overrun Names, they will just truncate to the length that they want, without giving you an error. The data will be lost, but you won't get an error.

To use Names to store long string values, code like this will work.


Function GetNameValue(someName As String) As String
Dim Index As Long
Index = 1
Do
GetNameValue = GetNameValue & Evaluate(ThisWorkbook.Names(someName & "_" & Index).RefersTo)
Index = Index + 1
Loop Until GetNameValue Like "*" & Chr(5) & "*"
GetNameValue = Left(GetNameValue, Len(GetNameValue) - 1)
End Function

Sub setNameValue(someName As String, ByVal itsValue As String)
Dim Index As Long
Dim oneChunk As String
Index = 1
Do
oneChunk = Left(itsValue, 200)
itsValue = Mid(itsValue, 201)
If itsValue = vbNullString Then oneChunk = oneChunk & Chr(5)
ThisWorkbook.Names.Add Name:=someName & "_" & Index, RefersTo:="=""" & oneChunk & """"
Index = Index + 1
Loop Until itsValue = vbNullString
End Sub

Sub test()
Dim xStr As String
setNameValue "myName", String(2000, "x")
xStr = GetNameValue("myName")
MsgBox Len(xStr)
End Sub