TheAntiGates
03-22-2016, 11:14 PM
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.
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.