表
我首先将一个表数据体范围分配给一个有效的数组arr = tbl.DataBodyRange
。
当将数组分配回表时,tbl.DataBodyRange = arr
适用于行数大于1的任何数组。
当数组只有一行时,我得到
运行时91错误:";对象变量或With块变量未设置";。
我无法共享原始文件。
表DataBodyRange
到数组
错误语法
- 当您使用
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