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.

dailydoseofexcel.com/archives/2015/01/09/…

Tim Williams
– Tim Williams

2021-06-07 19:23:36 +00:00
Commented Jun 7, 2021 at 19:23 — Tim Williams
– Tim Williams, Commented Jun 7, 2021 at 19:23

Алексей Р · Accepted Answer · 2021-06-07 19:16:29Z

1

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

answered Jun 7, 2021 at 19:16

Алексей Р

7,6272 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chris neilsen · Accepted Answer · 2021-06-07 18:54:01Z

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 7, 2021 at 18:54

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

2 Comments

Erik Remkus Over a year ago

InsertRowRange is also Nothing.

chris neilsen Over a year ago

I've tested this. InsertRowRange is not Nothing when DataBodyRange is Nothing. This is also confirmed in the link Tim posted under the Q

VBasic2008 · Accepted Answer · 2021-06-07 19:46:44Z

0

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

answered Jun 7, 2021 at 19:46

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

2 Comments

VBasic2008 Over a year ago

@BigBen: I wrote the InputArray already to the table using the HeaderRowRange?

BigBen Over a year ago

Never mind, hadn't tested my idea. You're right, the table automatically resizes so that DataBodyRange is not nothing after writing the array.

Collectives™ on Stack Overflow

Write Array To Table

3 Answers 3

Comments

2 Comments

Write an Array to a Table

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Write an Array to a Table

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related