Write Array To Table

Question

I am trying to write a function to output the data that is in a 2D array to an empty table.

Function WriteArrayToTable(InputArray() As Variant, TableName As String, SheetName As String)
    Dim MyTable As ListObject: Set MyTable = Worksheets(SheetName).ListObjects(TableName)
    MyTable.DataBodyRange.Value = InputArray
End Function

However, I get an object variable not set on the second line. I've tried the various permutations of including and excluding Set and DataBodyRange.Value. When I set my watches, it can find MyTable just fine and InputArray is the appropriate size however MyTable.DataBodyRange is Nothing.

http://dailydoseofexcel.com/archives/2015/01/09/finding-the-next-row-in-a-listobject-in-vba/ — Tim Williams, Jun 07 '21 at 19:23

score 1 · Accepted Answer · answered Jun 07 '21 at 19:16

Try this code:

Function WriteArrayToTable(InputArray() As Variant, TableName As String, SheetName As String)
    Dim MyTable As ListObject: Set MyTable = Worksheets(SheetName).ListObjects(TableName)
    Dim target As Range 'helper var for output point
    With MyTable
        If .DataBodyRange Is Nothing Then
            ' table is empty: output point - under the header
            Set target = .HeaderRowRange.Cells(1, 1)
        Else
            ' table not is empty: output point - at the end of the table
            Set target = .DataBodyRange.Cells(.DataBodyRange.Rows.Count, 1)
        End If
        target.Offset(1).Resize(UBound(InputArray, 1), UBound(InputArray, 2)).Value = InputArray
    End With
End Function

score 0 · Answer 2 · answered Jun 07 '21 at 18:54

0

As you have observed, when a Table is empty, DataBodyRange is Nothing.

When that is the case, use InsertRowRange instead

If MyTable.DataBodyRange Is Nothing Then
    MyTable.InsertRowRange = ...
Else
    `...
End If

answered Jun 07 '21 at 18:54

chris neilsen

52,446
10
84
123

1

InsertRowRange is also Nothing. – Erik Remkus Jun 07 '21 at 19:02
I've tested this. `InsertRowRange ` is _not_ Nothing when `DataBodyRange ` is Nothing. This is also confirmed in the link Tim posted under the Q – chris neilsen Jun 07 '21 at 20:52

score 0 · Answer 3 · answered Jun 07 '21 at 19:46

Write an Array to a Table

It is assumed that the array has the same number of columns as the table.

Option Explicit

Sub WriteArrayToTable( _
        ByVal InputArray As Variant, _
        ByVal TableName As String, _
        ByVal SheetName As String)
    Dim MyTable As ListObject
    Set MyTable = ThisWorkbook.Worksheets(SheetName).ListObjects(TableName)
    Dim srCount As Long: srCount = UBound(InputArray, 1)
    MyTable.HeaderRowRange.Offset(1).Resize(srCount).Value = InputArray
    With MyTable.DataBodyRange
        Dim drCount As Long: drCount = .Rows.Count
        If drCount > srCount Then
            .Resize(drCount - srCount).Offset(srCount).Delete
        End If
    End With
End Sub

@BigBen: I wrote the `InputArray` already to the table using the `HeaderRowRange`? — VBasic2008, Jun 07 '21 at 19:59
Never mind, hadn't tested my idea. You're right, the table automatically resizes so that `DataBodyRange` is not nothing after writing the array. — BigBen, Jun 07 '21 at 20:01

Write Array To Table

3 Answers3

Write an Array to a Table