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

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 个解决方案

#1

by far the best way of getting anything from a cells into memory (an array) is to use an array variant. I think the problem you are having is with index not with your method.

到目前为止,从单元格到内存(数组)中获取任何内容的最佳方法是使用数组变体。我认为你遇到的问题是索引而不是你的方法。

Hopefully this code should explain it.

希望这段代码能够解释它。

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

now the trick is to farm this off to a function that takes a few args( a 2d range, what you are looking for in that range) and returns your list.

现在的诀窍就是把它变成一个带有几个args的函数(一个2d范围,你在那个范围内寻找的东西)并返回你的列表。

To get your data back into a workbook

将数据恢复到工作簿中

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

make a range of the exact same size in terms of the bounds of your array and then ensuring you use .value just pop in the variant array.

根据数组的边界确定一个完全相同的范围,然后确保在变量数组中使用.value。

#1