Excel/VBA:我如何将单行转换为单元格值的数组

Question

我以为这会很简单，但显然微软的天才们有不同的想法。

我正在编写Excel VBA来循环Excel工作表中的行和每行(一个范围对象)，我想提取单元格值作为一个数组。我做了一些搜索，发现了一些假设的解决方案，它们一点也不直观，也不起作用。

有谁对如何做到这一点有建议吗?

Private Sub Separate_By_DC()
Dim row_ As Range
Dim row_str As String

For Each row_ In ActiveSheet.UsedRange.Rows
Dim arr As Variant
arr = Row_To_Array(row_)
Debug.Print UBound(arr) - LBound(arr) + 1

' row_str = Concat_Row(row_)
' Debug.Print row_str
Next row_
End Sub
Private Function Row_To_Array(row_ As Range) As Variant
Row_To_Array = row_.Value
End Function

Answer 1

我认为最简单的方法是:

Sub test()
Dim mArr As Variant
Dim i As Long
mArr = Application.Transpose(Range("A1:A10")) 'Transpoose to make the array one dimensional
'loop through the array
For i = LBound(mArr, 1) To UBound(mArr, 1) 'For one dimensional you can remove ",1"
'do something with mArr value
Cells(i, "C").Value = mArr(i) 'Print the array value
Next i
End Sub

---

对于二维数组，您可以通过声明维度索引来遍历它:

Dim mArr As Variant
Dim i As Long
mArr = Range("A1:A10") 'Use 2 dimension
'loop through the array
For i = LBound(mArr, 1) To UBound(mArr, 1)
'do something with mArr value
Cells(i, "C").Value = mArr(i, 1)
Next i