Adding an Array to a Listobject Table, as a new row, in Excel using VBA

Working with Tables in Excel is much faster when I import the data to an array. BUT - how do I work the other way around?

I have an 1 dim array and would need each data to be written/saved in a new row/record in a Listobject Table.

I've done some prework:

Dim MyTable As ListObject
Set MyTable = Ws.ListObjects("TestTbl")

Dim NewRow As ListRow

Set NewRow = MyTable.ListRows.Add(AlwaysInsert:=True)

For i = 1 To MyTable.ListColumns.Count
     If Len(myArr(rowToCopy(s), i)) > 0 Then
         On Error Resume Next
         NewRow.Range(i).Formula = "=" & Replace(myArr(rowToCopy(s), i), "xxx", "SheetName")
         On Error GoTo 0
      Else
         NewRow.Range(i).Formula = "=" & "#N/A"
      End If

 Next i

This does work - but is were slow. I copy and compare each value to each NewRow.Range(i) - and thats a bit slow... It's 144 columns times 6 rows in the actual file.

Would it be possible to create an Array with the data and then copy the whole array into the Table, in one go instead?

I thought;

dim valueArray(1 to 144) as String

for i = 1 to 144
     If Len(myArr(rowToCopy(s), i)) > 0 Then
          On Error Resume Next
          valueArray(i).Formula = "=" & Replace(myArr(rowToCopy(s), i), "xxx", "SheetName")
          On Error GoTo 0
      Else
          valueArray(i).Formula = "=" & "#N/A"
      End If
next i

And then - in some way, Copy the whole array in one step into the Table, as a new row... Why cant I??

OBS: Im using another Table with a generic "Formula" and creates new rows with functioning formulas in the new table. Thats why im using .Formula.

EDIT: Make sure the formulas created is valid - otherwise you get an "Out of memory" error!

Solution

In order to insert values contained in a 1D array, please try the next adapted code:

Sub InsertValsInNewTableRow()
 Dim MyTable As ListObject,ws As Worksheet, arr, i As Long
 
 Set ws = Workseets("MySheet") 'please, use here your real sheet!!!
 Set MyTable =  ws.ListObjects("TestTbl")

 Dim NewRow As listRow

 Set NewRow = MyTable.ListRows.Add(AlwaysInsert:=True)

 ReDim arr(1 To NewRow.Range.Columns.count)
 For i = 1 To UBound(arr)
    arr(i) = i * 10 'just to have a loaded array...
                    'use here your necessary array
 Next i

 NewRow.Range.value = arr
End Sub

In order to insert formulas, as it looks you try doing, please use this version:

Sub InsertValsInNewTableRow()
 Dim MyTable As ListObject,ws As Worksheet, arr, i As Long
 
 Set ws = Workseets("MySheet") 'please, use here your real sheet!!!
 Set MyTable =  ws.ListObjects("TestTbl")

 Dim NewRow As listRow

 Set NewRow = MyTable.ListRows.Add(AlwaysInsert:=True)

 ReDim arr(1 To NewRow.Range.Columns.count)
 For i = 1 To UBound(arr)
    arr(i) = "=Today() + " & i 'of course, you can built here your necessary formula...
 Next i

 NewRow.Range.Formula = arr
End Sub

In both cases, if your 1D array already exists and it is zero based, you should only adapt: NewRow.Range.Resize(, UBound(arr))' adding a unit to resized number of columns: NewRow.Range.Resize(, UBound(arr) + 1)`...

An array does not have a Formula property!. You should write the formula string in each array element. Or extract from an existing range, if available, and then adapt each element of the array according to your need. To change the sheet name as it looks you try, for instance.

And a general piece of advice: You never use On Error Resume Next until you have a working code! It will only not let you see the error and you do not know what is to be changed. Even after having a working code, there are very seldom cases when you would need it. In this case, at least, using it can only create debugging problems, I think.