将一个二维数组转换成一维数组

Question

如何解决使用Application.Index(array, row, column)将范围（=多维数组）转换为单维数组时遇到的每个单元格限制255个字符的问题？

下面是一个截断的示例，用于重现错误：

错误13. 类型不匹配

（完整代码位于超级用户上，我试图帮助另一个用户。）

如何重现

• Open a new Excel sheet and insert the formula =REPT("x",256) to cell A1
  This creates a 256 characters long string which is just 1 character too long for the last step

• Open the VBA editor (Alt+F11) and paste the below code somewhere

• Execute the code line by line with F8

  Function StringLengthTest()       
      Dim arr2D As Variant
      Dim arr1D As Variant        
      arr2D = Rows(1)
      arr1D = Application.Index(arr2D, 1, 0)        
  End Function
  

• You'll see the same error at the last line when Excel tries to convert a range (2D) to a 1D array while one of its cells has more than 255 characters.

为了证明这一点，将=REPT("x",256)更改为=REPT("x",255)，然后再次运行代码。这次它将正常工作。

问题：我应该用另一种方式声明变量吗？有更好的方法将范围（一开始始终是2D对象）转换为单个维数组吗？

我知道我可以使用循环遍历数组，并逐个保存所有2D阵列值到1D阵列。但那不够高效。想象一下非常大的工作表。

- nixda

---

你不能将arr2D作为一个二维数组来处理吗？在第一维中指定1，这样arr2D(1, v)就等同于arr1D(v)。 - user4039065

顺便提一下，对于数组的范围，可以使用LBound(arr2D, 2)和UBound(arr2D, 2)。 - user4039065

在这里使用循环比使用INDEX()要快大约两倍。 - Tim Williams

为什么要费力进行转换？正如@Jeeped所指出的那样，您不需要这样做，任何转换/复制都会浪费时间和空间。 - Blackhawk

HAHA...尝试使用Application.Transpose(Application.Transpose(Rows(1))) => 相同的行为...=REPT("x",256) 弹出错误 :D - Dirk Reichel

正如链接中所示，@Jeeped将使用Join...知道他会这样做 => For Each ...和string = string & [delimeter] & [Each]会更快...然而...至少，这并没有回答问题... - Dirk Reichel

Answer 1

到目前为止，将任何内容从单元格传递到内存（数组）中的最佳方法是使用数组变量。我认为您遇到的问题与索引有关，而不是您的方法。

希望这段代码能解释清楚。

Dim v_data As Variant
Dim rw As Long, cl As Long ' for row and column
Dim arr1d() As Variant
Dim count As Long

' I'm going to use UsedRange to get the whole sheet  .UsedSheet
' If you just want things from one row or column then just spec
' activesheet.range("A1:A100") or use cells()
With ActiveSheet.UsedRange
  ReDim v_data(1 To .Rows.count, 1 To .Columns.count)
  v_data = .Value
End With

'now we have all the things from that sheet.
'so to convert to 1d array where the cell value is say = 1
For rw = LBound(v_data) To UBound(v_data)
   For cl = LBound(v_data, 2) To UBound(v_data, 2) ' note the comma 2 for the second dimension bounds.
       If v_data(rw, cl) = 1 Then
           count = count + 1
           ReDim Preserve arr1d(1 To count)
           arr1d(count) = v_data(rw, cl)
       End If
   Next cl
Next rw

For count = LBound(arr1d) To UBound(arr1d)
    Debug.Print arr1d(count)
Next count

现在的诀窍是将此分配给一个函数，该函数需要几个参数（2D范围，您要在该范围内查找的内容），并返回您的列表。

要将数据放回工作簿中

ActiveSheet.Cells(1, 1).Resize(UBound(arr1d), 1).Value = arr1d

在数组边界范围内创建一个相同大小的范围，然后确保使用.value将变量数组插入其中。

- Keith Whatling