Table DataBodyRange转Array

语法错误

• 当你使用Dim arr As Variant时，你允许arr变成任何东西（在这个例子中它变成了Nothing）。当你使用arr = tbl.DataBodyRange时，仍然没有错误。
  好语法
• 如果你用

Dim arr() As Variant

相反，arr只能传递一个数组。

• 如果你用

arr = tbl.DataBodyRange.Value

相反，如果范围是Nothing，则会发生错误。如果范围是一个单元格，则会发生错误。

密码

Option Explicit

Sub TableData()
    
    ' e.g.
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    
    Dim rg As Range: Set rg = tbl.DataBodyRange
    
    ' Prevent
    ' "Run-time error '91': Object variable or With block variable not set"
    ' when the table is empty.
    If rg Is Nothing Then
        MsgBox "The table is empty.", vbExclamation
        Exit Sub
    End If
    
    Dim Data() As Variant
    
    ' Prevent
    ' "Run-time error '13': Type mismatch"
    ' when the range is just one cell.
    If rg.Rows.Count * rg.Columns.Count = 1 Then ' one cell
        ' Note that this is only possible if the table has just one column.
        ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
    Else ' multiple cells
        Data = rg.Value
    End If
    
    ' Do your thing... e.g., increase each number in the first column by 1:
    
'    Dim cValue As Variant
'    Dim r As Long
'
'    For r = 1 To UBound(Data, 1)
'        cValue = Data(r, 1)
'        If VarType(cValue) = vbDouble Then ' is a number
'            Data(r, 1) = cValue + 1
'        End If
'    Next r
    
    rg.Value = Data
    
End Sub