将2D数组转换为1D数组-Converta2Darraytoa1Darray

How do I work around the 255 characters per cell limit when converting a range (= multidimensional array) to single dimensional array with the use of Application.Index(array, row, column)?

使用Application.Index(数组,行,列)将范围(=多维数组)转换为单维数组时,如何解决每个单元格限制255个字符?

The following truncated example reproduces the error:

以下截断的示例重现错误:

Error 13. Type mismatch

错误13.类型不匹配

(The complete code is on superuser where I tried to help another user).

(完整的代码是超级用户,我试图帮助其他用户)。

How to reproduce

• 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

  打开一个新的Excel工作表并将公式= REPT(“x”,256)插入单元格A1这将创建一个长度为256个字符的字符串,该字符串对于最后一步而言只有1个字符太长

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

  打开VBA编辑器(Alt + F11)并将以下代码粘贴到某处

• Execute the code line by line with F8

  用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.

  当Excel尝试将范围(2D)转换为1D数组时,您会在最后一行看到相同的错误,而其中一个单元格的字符数超过255个。

To prove this, change =REPT("x",256) to =REPT("x",255) and run the code again. This time it will work.

为了证明这一点,将= REPT(“x”,256)更改为= REPT(“x”,255)并再次运行代码。这次它会起作用。

Question: Should I declare my variables in another way? Is there a better way to convert a range (which is always a 2D object at first) to a single dimensional array?

问题:我应该以另一种方式声明我的变量吗?有没有更好的方法将范围(最初始终是2D对象)转换为单维数组?

I know I could use a loop to iterate through the arrays and save all 2D array values one by one to a 1D array. But that's not efficient. Imagine really large sheets.

我知道我可以使用循环迭代数组并将所有2D数组值逐个保存到1D数组。但那效率不高。想象一下真的很大的床单。

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

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