VBA类型不匹配使用单个单元格的地址范围

Question

试图将范围传递到阵列。如果该范围是一个单元格（例如“ A1”），则返回A＆nbsp; type不匹配（错误13）。仅当传递给函数的范围至少为2个单元格时，它才能起作用。

arrayrng() = activesheet.range(rng.address).value 'BUG

同样，当范围是一个单元格时，错误处理程序解决方案的解决方案有一个错误：

arrayRng(0) = Rng.Value 'BUG

Public arrayRng() As Variant
    Function GetArraysFromRange(ByVal Rng As Range) As Variant
    Dim j  As Long
On Error GoTo SingleRange
arrayRng() = ActiveSheet.Range(Rng.Address).Value 'BUG

''Make  1D array into 2D array
Dim TempArray() As Variant
Dim i  As Long
ReDim TempArray(1 To UBound(arrayRng), 1 To 2)
For i = 1 To UBound(arrayRng)
    TempArray(i, 1) = arrayRng(i, 1)
Next i
arrayRng = TempArray

' copy array position (1) to position (2)for later modifying
    For j = LBound(arrayRng) To UBound(arrayRng)
       arrayRng(j, 2) = arrayRng(j, 1)
    Next
    
GetArraysFromRange = arrayRng
Erase arrayRng

Exit Function
SingleRange:
Err.Clear
arrayRng(0) = Rng.Value 'BUG
Resume
End Function

Answer 1

getrange：返回2D一个基于2D的数组中的范围的值，

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the values of a range ('rg') in a 2D one-based array.
' Remarks:      If ˙rg` refers to a multi-range, only its first area
'               is considered.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetRange( _
    ByVal rg As Range) _
As Variant
    Const ProcName As String = "GetRange"
    On Error GoTo ClearError
    
    If rg.Rows.Count + rg.Columns.Count = 2 Then ' one cell
        Dim Data As Variant: ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
        GetRange = Data
    Else ' multiple cells
        GetRange = rg.Value
    End If

ProcExit:
    Exit Function
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Function

• 请参见如何在我的一些帖子。