你需要检查你的Range中有多少单元格，你想转换成数组，使用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

如果你以后必须处理一个二维数组（例如，循环遍历值），那么

If r2.Cells.Count > 1 Then
    a2 = r2
ElseIf r2.Cells.Count = 1 Then
    ReDim a(1 To 1, 1 To 1)
    a2(1, 1) = r2
End If

这样做的第二个好处是，在这两种情况下，LBound都是1，因为将多个单元格转换为数组总是返回LBound为1的数组，而不是0。

Excel会根据是否引用单个单元格来更改.Value和.Value2返回的内容。
要获得一致的结果，您需要一个 Package 函数

Option Explicit

Public Function RangeGetArray(ByVal r As Range) As Variant()
    Dim data() As Variant
    If r.Rows.Count = 1 And r.Columns.Count = 1 Then
        ReDim data(1 To 1, 1 To 1)
        data(1, 1) = r.Value
    Else
        data = r.Value
    End If
    RangeGetArray = data
End Function

Public Function RangeSetArray(ByVal r As Range, ByRef data() As Variant)
    Dim n As Long, m As Long
    n = UBound(data, 1) - LBound(data, 1) + 1
    m = UBound(data, 2) - LBound(data, 2) + 1
    If n = 1 And m = 1 Then
        r.Value = data(1, 1)
    Else
        r.Resize(n, m).Value = data
End Function