excel - Range.Address 在公式中使用时被截断(字符串太长)

Question

我有循环遍历用户选择范围的第 5 列的代码，如果第 5 列中的当前单元格匹配条件，则将同一行中的接下来 10 个单元格添加到范围中。在所有操作结束时，如果单元格 A1 为空，它将对单元格 A1 中的最终范围进行求和。

Sub SumItems()
   Dim selectedRange As Range
   Set selectedRange = Selection

   Dim sumRange As Range
   Dim i As Long
   For i = 1 To selectedRange.Rows.Count
      If selectedRange(i, 5) = "Yes" Then
         If sumRange Is Nothing Then
            Set sumRange = Range(selectedRange(i, 5).Offset(0, 1), selectedRange(i, 5).Offset(0, 10))
         Else
            Set sumRange = Union(sumRange, Range(selectedRange(i, 5).Offset(0, 1), selectedRange(i, 5).Offset(0, 10)))
         End If
      End If
   Next i

   If Not sumRange Is Nothing Then
      If IsEmpty(Range("A1")) Then
         Range("A1").Formula = "=SUM(" + sumRange.Address(False, False) + ")"
      Else
         MsgBox "Cannot paste in cell A1, it is not empty."
         Exit Sub
      End If
   Else
      MsgBox "No matching rows were found."
      Exit Sub
   End If
End Sub

我运行的纸张很长。因此 sumRange 通常是工作表中许多不同区域的并集。我注意到，一旦 sumRange.Areas.Count 超过 18，我的 Range("A1").Formula 语句就会出现问题。 sumRange.Address(False, False) 字符串在第 18 个区域之后被截断。我相信这是因为 .Address 字符串长度太长，并且它恰好与此表的 18 个区域重合。

我开发了一个临时解决方案，但它限制了子例程可以处理的区域数量，并且笨重且冗长。我设置了临时范围变量，如果 sumRange 具有超过 18 个区域，则将其拆分为多个区域，并且 IF 语句现在的内容类似于:

If Not sumRange Is Nothing Then
   If IsEmpty(Range("A1")) Then
      If sumRange.Areas.Count > 18 Then
         Dim k As Long
         Dim tmpSumRange1 As Range
         Dim tmpSumRange2 As Range
         Dim tmpSumRange3 As Range
         ' And so on...
         For k = 1 To sumRange.Areas.Count
            If k <= 18 Then
               If tmpSumRange1 Is Nothing Then
                  Set tmpSumRange1 = sumRange.Areas(k)
               Else
                  Set tmpSumRange1 = Union(tmpSumRange1, sumRange.Areas(k))
               End If
            ElseIf k > 18 And k <= 36 Then
               If tmpSumRange2 Is Nothing Then
                  Set tmpSumRange2 = sumRange.Areas(k)
               Else
                  Set tmpSumRange2 = Union(tmpSumRange2, sumRange.Areas(k))
               End If
            ElseIf k > 36 And k <= 54 Then
               If tmpSumRange3 Is Nothing Then
                  Set tmpSumRange3 = sumRange.Areas(k)
               Else
                  Set tmpSumRange3 = Union(tmpSumRange3, sumRange.Areas(k))
               End If

            ElseIf
               ' And so on until I get to k = 180...
            End If
         Next k

         If Not tmpSumRange1 Is Nothing And tmpSumRange2 Is Nothing And tmpSumRange3 Is Nothing Then
            Range("A1").Formula = "=SUM(" + tmpSumRange1.Address(False, False) + ")"
         ElseIf Not tmpSumRange1 Is Nothing And Not tmpSumRange2 Is Nothing And tmpSumRange3 Is Nothing Then
            Range("A1").Formula = "=SUM(" + tmpSumRange1.Address(False, False) + "," + tmpSumRange2.Address(False, False) + ")"
         ElseIf Not tmpSumRange1 Is Nothing And tmpSumRange2 Is Nothing And Not tmpSumRange3 Is Nothing Then
            Range("A1").Formula = "=SUM(" + tmpSumRange1.Address(False, False) + "," + tmpSumRange2.Address(False, False) + "," + tmpSumRange3.Address(False, False) + ")"
         ElseIf
            ' And so on all the way up to tmpSumRange10...
         End If
      Else
         Range("A1").Formula = "=SUM(" + sumRange.Address(False, False) + ")"
      End If
   Else
      MsgBox "Cannot paste in cell A1, it is not empty."
      Exit Sub
   End If
Else
   MsgBox "No matching rows were found."
   Exit Sub
End If

我知道必须有比这更好的解决方案。我在搜索中没有找到任何东西。我也没有发现任何人遇到过同样的问题。我需要 A1 单元格具有包含范围的 SUM 公式，否则我只需对子例程中的值求和并插入值而不是公式。

Answer 1

因此存在两个独立但可能相关的问题:

• Range.Address 最多截断 255 个字符，不会出现错误。这很奇怪，因为虽然 Excel 足够聪明，不会留下会引发 1004 的部分地址字符串，但同时它又愚蠢到允许截断 Address，这显然会产生如果用于公式等，会产生意想不到的结果。
• 如果字符串通过 ByVal 传递，则 Excel 函数的字符串参数有 255 个字符的限制(请参阅 this old thread )进行讨论，但我摘录了相关部分，如下)。

Excel function parameters will accept a reference to a string with length of up to 32767 (32k or 2^15 total possible lengths). ... But what if we pass a value instead of a reference?

we can pass a reference to a string up to 32k, we can only pass a string value up to 255

问题似乎是由公式长度造成的，但这是一个转移注意力的问题。

您肯定遇到了 Range.Address 的截断问题。

问题:Range.Address 在 255 个字符处截断

因为这种情况悄然发生，所以您基于截断地址构建的任何公式都将受到不利影响；结果不会是您所期望的，因为公式不会代表范围的所有部分!

我已将其记录为 Microsoft 的错误，以防您想要解决该问题:

https://github.com/MicrosoftDocs/VBA-Docs/issues/49

同时，幸运的是，解决方案非常简单(尽管实现起来并不简单!)，看看您是否可以适应您的目的，但想法是:

1. 创建一个 System.Collections.ArrayList，我们在其中存储所有单独的 Area 地址(或者，您可以使用 Variant，但这些使用 ReDim 等有点笨拙)
2. 使用 ToArray() 方法将 ArrayList 转换为变体数组
3. 在 Formula 字符串中使用 Join 函数的结果

<小时/>

Sub example()

Dim myNames As Object
Dim formula as String
Dim k As Long
Dim sumRange As Range
Dim thisArea As Range

' Example:
'   -- creates a range consisting of 100 Areas
'   -- this .Address will truncate to 252 characters!
For k = 1 To 200 Step 2
    Set thisArea = Range("A" & k)
    If sumRange Is Nothing Then Set sumRange = thisArea
    Set sumRange = Union(sumRange, thisArea)
Next

' Iterate the sumRange union and add each Area to an ArrayList
Set myNames = CreateObject("System.Collections.ArrayList")
For Each thisArea In sumRange.Areas
    myNames.Add thisArea.Address
Next

' Output to worksheet:
Dim outputCell As Range
Set outputCell = Range("F1")
formula = "=Sum(" & Join(myNames.ToArray(), ",") & ")"
outputCell.Formula = formula

End Sub

您可以在工作表中看到非常长的公式 (Len == 650):

如果您不习惯使用ArrayList，您可以将其强制作为“字符串:

”

' Iterate the sumRange union and add each Area to our formula string
formula = "=Sum("
For Each thisArea In sumRange.Areas
    formula = formula & thisArea.Address & ","
Next
formula = Left(formula, Len(formula) - 1)
formula = formula & ")"