Write an array into table (ListObject) - out of memory

Question

I have a problem with entering an array into a table which is formatted as a ListObject. I used the following code, but this is not working for all cases.

The problematic function is embedded in the main Sub as follows.

Dim SFH As New SheetFilterhandler

Set SFH.SetTable = loSourceList
SFH.SaveFilters
SFH.DisableFilters
Call UpdateListObject(loSourceList, loImportList)
SFH.EnableFilters

And the function can be reduced to this:

Public Function UpdateListObject(loTarget As ListObject, loSource As ListObject) As Boolean

On Error GoTo CreateErr
Dim PreviousContent As Variant
Dim Export As Variant

Export = loSource.DataBodyRange.value
PreviousContent = loTarget.DataBodyRange.value

Dim CurrentContent As Variant
ReDim CurrentContent(UBound(PreviousContent) + UBound(Export), UBound(PreviousContent, 2))

For ExportIndex = 1 To UBound(Export)
    For PreviousContentIndex = 1 To UBound(PreviousContent)
        'Export and PreviousContent is compared and written into CurrentContent
    Next PreviousContentIndex
    'new items
    If ItemNotExisting Then
        NewContentCounter = NewContentCounter + 1
        loTarget.ListRows.Add
        For i = 1 To UBound(Export, 2)
            CurrentContent(UBound(PreviousContent) + NewContentCounter, i) = Export(ExportIndex, i)
        Next i
    End If
Next ExportIndex

loTarget.DataBodyRange.Value2 = CurrentContent 'problematic line
UpdateListObject = True
Exit Function
CreateErr:
    UpdateListObject = False
    GetErrMsg = Err.Description
    Debug.Print "UpdateListObject: " & GetErrMsg
    Debug.Print PreviousContentIndex & "," & ExportIndex
End Function

This code is working fine for:

CurrentContent Variant/Variant(1 to 1294, 1 to 55); loTarget.DataBodyRange.Value2(1 to 647, 1 to 55)

But for the following it is not working:

CurrentContent Variant/Variant(1 to 3071, 1 to 20); loTarget.DataBodyRange.Value2(1 to 1543, 1 to 20)

The size is different, because ArrayName has only empty entries for the rows greater than ListObject max rows and can be ignored.

In this case I used the Debug.Print to show the error description.

GetErrMsg = Err.Description
Debug.Print GetErrMsg

The output was Nicht genügend Speicher which could be translated not enough memory. If there is any limit, I do not understand it, because. 1294 x 55 = 71170 and 3071 x 20 = 61420

The same behavior was seen also without the table.

With ActiveSheet
    .ListObjects(1).Unlist
    .UsedRange.Offset(1, 0).Clear
    .Range(.Cells(2, 1), Cells(UBound(CurrentContent) + 1, UBound(CurrentContent, 2))) = CurrentContent
End With

Sample data of CurrentContent

Expression	Value	Type
CurrentContent(1,1)	3709745	Variant/Double
CurrentContent(1,2)	"String 1"	Variant/String
CurrentContent(1,3)	#11.11.2023 11:11:11#	Variant/Date
CurrentContent(1,4)	#11.11.2023#	Variant/Date
CurrentContent(1,5)	"String 2 [1234567]"	Variant/String
CurrentContent(1,6)	"String 3"	Variant/String
CurrentContent(1,7)	"String 4"	Variant/String
CurrentContent(1,8)	"String 5"	Variant/String
CurrentContent(1,9)	"String 6"	Variant/String
CurrentContent(1,10)	"String 7"	Variant/String
CurrentContent(1,11)	""	Variant/String
CurrentContent(1,12)	"XX"	Variant/String
CurrentContent(1,13)	""	Variant/String
CurrentContent(1,14)	""	Variant/String
CurrentContent(1,15)	"1234567, 1234567"	Variant/String
CurrentContent(1,16)	"String 8"	Variant/String
CurrentContent(1,17)	"String 9"	Variant/String
CurrentContent(1,18)	#11.11.2023 11:11:11#	Variant/String
CurrentContent(1,19)	"Max Mustermann (1234567)"	Variant/String
CurrentContent(1,20)	Empty	Variant/Empty

Answer 1

I have a column where all Cells have a large string. When this column is deleted there is no issue anymore.

I guess there is a limit of characters in an array which can be written into Range. At the moment I do not know this limit.

Answer 2

I checked again the what is the root cause. I can not add a 1593 long string into a Range which begins with 88 times "=" (equal to character). This string in the array can not be written, even if I would do it like this:

Range("A1") = ProblematicString

But in this case the error is: "Anwendungs- oder objektdefinierter Fehler", "Application or object defined error"

This problem can be reduced to

Range("A1") = "=="