vba - 确定字符串最大允许长度 a 的通用方法

Question

看看这个属性(假设您在第一个工作表上有一个表格):

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

该属性可以包含多少个字符？好吧，在测试了几个字符串之后，我得出的结论是它是 255，任何超过 255 个字符的字符串都会导致抛出错误:

Run-Time Error 5 - Invalid procedure call or arguement

看看这个属性:

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

该属性可以包含多少个字符？再次测试几个字符串，你会得到一个大约 50,000 的数字，你将其设置得更高，你会得到相同的错误，但在这种情况下，excel 有时会崩溃或吐出不同的错误(多次尝试后):

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

这种“隐藏”字符限制随处可见( here 、 here 、 less specifically here 、 and so classically here )，例如，它们似乎没有记录在任何地方看一下 ListObject.Name 的页面，它没有注明您可以在该变量中存储多少个字符...

那么有没有更好的方法来确定这一点？您在属性中设置的字符串是否存储在固定长度的字符串中，可以访问该字符串以确定其最大长度，或者是否可以利用其他形式的文档来获取此信息？

令我感到奇怪的是，在标准 VBA 对象中的大多数字符串上设置的这些字符限制，我想知道它们的目的是什么，为什么设计者选择将“ListObjects.name”限制为 255 个字符，以及这是否是任意默认值限制或者这是否是一个有意识的决定。我相信标准字符串长度是 this ，我想知道为什么会偏离这个标准。

总结我上面提出的观点并将这个问题浓缩为一句话:

是否有一种通用方法可以确定可以在对象属性中设置的字符串的最大长度，而无需首先通过给定另一个值并忽略错误/检查字符截断来测试该字符串的属性？

Answer 1

首先，如果您的目的是存储有关对象的元信息，您可以使用CustomDocumentProperties 。您可以找到有关其用法的示例 here和 here和一些漂亮的 wrapper 由 Chip Pearson here .
由于它们的长度仍然非常有限(255 个字符)(感谢您指出这一点!)，最好的解决方案可能是使用 CustomXMLParts 如 described here 。困难的部分是使用 VBA 构建正确的 XML，但如果您添加对 Microsoft XML 的引用，也许并非不可能。

但是为了对有关字符串属性最大长度的问题提供一些帮助，这里有一个测试设置，您可以使用它(相对)快速找到任意属性的这些限制。只需将第 19 行的 ActiveWorkbook.Sheets(1).Name 替换为您要测试的属性并运行 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