Generic Way to Determine the Maximum Allowed Length a of String

Question

Take a look at this property(Given you have a table on the first worksheet):

Application.Sheets(1).ListObjects(1).name

How many characters can this property contain? Well, after testing out a few strings I've come to the conclusion that its 255, any string with more than 255 characters causes an error to be thrown:

Run-Time Error 5 - Invalid procedure call or arguement

Take a look at this property:

Application.Sheets(1).ListObjects(1).Summary

How many characters can this property contain? Again, test several strings and you'll come out with a number that's around 50,000, You set it any higher and you get the same error, except in this case excel will sometimes crash or spit out a different error(after multiple attempts):

Dim i As Integer
Dim a As String

For i = 1 To 5001
    a = a & "abcdefghih"
Next i

Application.Sheets(1).ListObjects(1).Summary = a

Method "Summary" of object 'ListObject' failed

This sort of "hidden" character limit comes up all over the place(here, here, less specifically here, and so classically here), and it doesn't seem like they're documented anywhere, for example take a look at the page for ListObject.Name, its not noted how many characters you can store in that variable...

So is there a better way to determine this? Are the strings you are setting in properties being stored in a fixed length string somewhere that can be accessed to determine what their maximum length is, or is there some other form of documentation that can be leveraged in order to obtain this information?

It strikes me as odd these character limits that are set on most strings within standard VBA objects, I wonder what their purpose is, why the designers choose to limit "ListObjects.name" to 255 characters and whether that was an arbitrary default limit or whether that was a conscious decision that was made. I believe that the standard string length is this, I wonder why the deviation from this standard.

To summarize the points I've made above and to condense this question into one sentence:

Is there a generic way to determine the maximum length of a string that can be set within an object's property, without first testing that string's property by giving it another value and ignoring errors/checking for character truncation?

Answer 1

First of all, if your intention is to store meta information about objects, you could maybe make use of CustomDocumentProperties. You can find examples on their usage here and here and some nice wrappers by Chip Pearson here.
Since they are still very limited (255 chars) in length (thanks for pointing that out!), the best solution might be to use CustomXMLParts like described here. The hard part would then be building correct XML using VBA, but maybe not impossible, if you add a reference to Microsoft XML.

But to provide some help with your question concerning maximum lengths for string properties, too, here is a test setup you can use to (relatively) quickly find these limits for arbitrary properties. Just replace the ActiveWorkbook.Sheets(1).Name on line 19 with the property you want to test and run TestMaxStringLengthOfProperty():

Option Explicit

Const PRINT_STEPS = True   ' If True, calculation steps will be written to Debug.Print


Private Function LengthWorks(ByVal iLengthToTest As Long) As Boolean

   Dim testString As String
   testString = String(iLengthToTest, "#")   ' Build string with desired length
   ' Note: The String() method failed for different maximum string lengths possibly
   '       depending on available memory or other factors. You can test the current 
   '       limit for your setup by putting the string assignment in the test space.
   '       In my tests I found maximum values around 1073311725 to still work.

   On Error Resume Next
   ' ---------------------------------------------------------------------------------
   '   Start of the Test Space - put the method/property you want to test below here

   ActiveWorkbook.Sheets(1).Name = testString

   '   End of the Test Space - put the method/property you want to test above here
   ' ---------------------------------------------------------------------------------
   LengthWorks = Err.Number = 0
   On Error GoTo 0

End Function


Private Sub TestMaxStringLengthOfProperty()

   Const MAX_LENGTH As Long = 1000000000 ' Default: 1000000000
   Const MAXIMUM_STEPS = 100     ' Exit loop after this many tries, at most

   ' Initialize variables for check loop
   Dim currentLength As Long
   Dim lowerBoundary As Long: lowerBoundary = 0
   Dim upperBoundary As Long: upperBoundary = MAX_LENGTH

   Dim currentStep As Long: currentStep = 0
   While True    ' Infinite loop, will exit sub directly
      currentStep = currentStep + 1
      If currentStep > MAXIMUM_STEPS Then
         Debug.Print "Exiting because maximum number of steps (" & _
                      CStr(MAXIMUM_STEPS) & _
                     ") was reached. Last working length was: " & _
                      CStr(lowerBoundary)
         Exit Sub
      End If

      ' Test the upper boundary first, if this succeeds we don't need to continue search
      If LengthWorks(upperBoundary) Then
         ' We have a winner! :)
         Debug.Print "Method/property works with the following maximum length: " & _
                     upperBoundary & vbCrLf & _
                     "(If this matches MAX_LENGTH (" & _
                      MAX_LENGTH & "), " & _
                     "consider increasing it to find the actual limit.)" & _
                      vbCrLf & vbCrLf & _
                     "Computation took " & currentStep & " steps"
         Exit Sub
      Else
         ' Upper boundary must be at least one less
         upperBoundary = upperBoundary - 1
         PrintStep upperBoundary + 1, "failed", lowerBoundary, upperBoundary, MAX_LENGTH
      End If

      ' Approximately halve test length
      currentLength = lowerBoundary + ((upperBoundary - lowerBoundary) \ 2)
         ' "\" is integer division (http://mathworld.wolfram.com/IntegerDivision.html)
         ' Using `left + ((right - left) \ 2)` is the default way to avoid overflows
         ' when calculating the midpoint for our binary search
         ' (see: https://en.wikipedia.org/w/index.php?title=Binary_search_algorithm&
         '                                        oldid=809435933#Implementation_issues)

      If LengthWorks(currentLength) Then
         ' If test was successful, increase lower boundary for next step
         lowerBoundary = currentLength + 1
         PrintStep currentLength, "worked", lowerBoundary, upperBoundary, MAX_LENGTH
      Else
         ' If not, set new upper boundary
         upperBoundary = currentLength - 1
         PrintStep currentLength, "failed", lowerBoundary, upperBoundary, MAX_LENGTH
      End If

   Wend

End Sub


Private Sub PrintStep(ByVal iCurrentValue As Long, _
                      ByVal iWorkedFailed As String, _
                      ByVal iNewLowerBoundary As Long, _
                      ByVal iNewUpperBoundary As Long, _
                      ByVal iMaximumTestValue As Long)
   If PRINT_STEPS Then
      Debug.Print Format(iCurrentValue, String(Len(CStr(iMaximumTestValue)), "0")) & _
                  " " & iWorkedFailed & " - New boundaries: l: " & _
                  iNewLowerBoundary & " u: " & iNewUpperBoundary
   End If
End Sub