无法在VBA中从1个单元格范围创建阵列

问题描述：

我有读取范围并将其转换为数组以进行处理的代码. 不幸的是，当范围只有一个单元格时，它会失败.

I have code that reads ranges and converts them to arrays for processing. It unfortunately fails when the range only has one cell.

要解决这个问题，请考虑分别具有1和2个单元格的以下范围(r1，r2)，我要分别转换为数组a1和a2:

To boil down the problem, consider the following ranges (r1, r2) with respectively 1 and 2 cells, that I want to convert to arrays a1 and a2, respectively:

Sub ranges_to_arrays()

    Dim r1 As Range, r2 as Range
    Dim a1() As Variant, a2() as Variant

    Set r2 = Worksheets("test").Range("A1:A2")
    a2 = r2 ' Creates Variant(1 to 2, 1 to 1)

    Set r1 = Worksheets("test").Range("A1")
    a1 = r1 'Fails with a type mismatch

End Sub

即使范围只有一个元素，我如何确保将创建一个数组?

How can I ensure that an array will be created even if the range has only one element?

答

您需要检查要转换为数组的范围中有多少个单元格，请使用If r2.Cells.Count > 1 Then.

You need to check how many cells there are in your Range you are trying to convert to array, use If r2.Cells.Count > 1 Then.

代码

Option Explicit

Sub ranges_to_arrays()

    Dim r1 As Range, r2 As Range
    Dim a1() As Variant, a2() As Variant

    Set r2 = Worksheets("test").Range("A1:A2")
    If r2.Cells.Count > 1 Then
        a2 = r2 ' Creates Variant(1 to 2, 1 to 1)
    Else
        ReDim a2(0 To r2.Cells.Count - 1) ' redim array size to 1 (only 1 cell in range)
        a2(0) = r2
    End If

    Set r1 = Worksheets("test").Range("A1")
    If r1.Cells.Count > 1 Then
        a1 = r1 'Fails with a type mismatch
    Else
        ReDim a1(0 To r1.Cells.Count - 1) ' redim array size to 1 (only 1 cell in range)
        a1(0) = r1
    End If

End Sub

无法在VBA中从1个单元格范围创建阵列

相关推荐