API reference · Arrays

InsertLines

Append rows or overwrite trailing rows, interpreting a 1D array as one row.

FunctionArrays→VariantSource L670

Signature#

Function InsertLines(ByVal vArr As Variant, ByVal vLines As Variant, Optional ByVal bRedim As Boolean = True) As Variant

Parameters#

Parameter	VBA type	Default	Description
`vArr`	`Variant`ByVal	Required	1D row or 2D destination matrix. A 1D destination is treated as one row with row subscript 1.
`vLines`	`Variant`ByVal	Required	1D array contributing one row, or 2D block contributing all of its rows. Its column count must equal the destination width; column lower bounds may differ.
`bRedim`	`Boolean`ByVal	`True`	True appends below the destination. False preserves its row count and replaces the same number of trailing rows.

Returns#

Normally a Variant containing a 2D array. A 2D destination keeps its row lower bound and column bounds; a 1D destination produces row lower bound 1 and uses its element bounds as columns. Unsupported rank or incompatible widths return Empty. Non-array fallback behavior can return an input unchanged.

Example#

SEE THE TRANSFORMATIONAppend one row

Before

transactions2D · 2 × 2

Rows 1..2 · Columns 1..2

row / col	1	2
1	“Sales”	100
2	“Fees”	12

Additional input

newRow1D · (2,)

Indices 0..1 · one dimension

0	1
“Sales”	80

After

result2D · 3 × 2

Rows 1..3 · Columns 1..2

row / col	1	2
1	“Sales”	100
2	“Fees”	12
3	“Sales”	80

Here the 1D input is interpreted as a row.

Shaded headers are array indices, not data. Quoted cells are text. Values shown are derived from the source, not an Excel execution.

Sub ExampleInsertLines()
    Dim a As New Arrays
    Dim transactions As Variant, newRow As Variant, result As Variant

    ReDim transactions(1 To 2, 1 To 2)
    transactions(1, 1) = "Sales": transactions(1, 2) = 100
    transactions(2, 1) = "Fees": transactions(2, 2) = 12
    newRow = VBA.Array("Sales", 80)
    result = a.InsertLines(transactions, newRow)
    Debug.Print UBound(result, 1)      ' Expected: 3
    Debug.Print result(3, 1), result(3, 2) ' Expected: Sales, 80
End Sub

Behavior and edge cases#

There is no insertion-position parameter. The function appends at the bottom or replaces trailing rows.
Unlike DimExpand and several analytical functions, InsertLines interprets a 1D array as a horizontal row. A 1D destination receives output row bound 1; its original element bounds become output column bounds.
Column counts must match, but column subscripts need not match. Source row and column offsets are translated by position.
When bRedim=False, supplying more rows than the destination contains returns Empty. Otherwise only the trailing supplied-height rows are replaced.
If vLines is not an array, the function immediately returns vArr unchanged. Otherwise, if vArr is not an array, it returns vLines unchanged. These fallbacks precede rank validation and ignore bRedim.
Neither input array is modified. A successful normal array-to-array operation always produces a 2D array, even when both arguments are 1D.

Performance#

O((r + k) × c) copy work for r destination rows, k supplied rows and c columns. Output storage is O((r + k) × c) for append or O(r × c) for overwrite. Non-array fallback branches return before the loops.

View implementation

Function InsertLines(ByVal vArr As Variant, ByVal vLines As Variant, Optional ByVal bRedim As Boolean = True) As Variant
    'Appends rows to a 2D array or overwrites its last rows when bRedim is False

    Dim vArr_Out                As Variant
    Dim lRow                    As Long
    Dim lCol                    As Long
    Dim lRowSource              As Long
    Dim lColSource              As Long
    Dim lStart                  As Long
    Dim lNumLines               As Long
    Dim lFirstRow               As Long
    Dim lLastRow                As Long
    Dim lFirstCol               As Long
    Dim lLastCol                As Long
    Dim iDim                    As Integer
    Dim iLinesDim               As Integer

    If Not IsArray(vLines) Then
        InsertLines = vArr
        Exit Function
    End If

    If Not IsArray(vArr) Then
        InsertLines = vLines
        Exit Function
    End If

    iDim = Me.DimCounter(vArr)
    iLinesDim = Me.DimCounter(vLines)
    If iDim <> 1 And iDim <> 2 Then Exit Function
    If iLinesDim <> 1 And iLinesDim <> 2 Then Exit Function

    If iDim = 1 Then
        lFirstRow = 1
        lLastRow = 1
        lFirstCol = LBound(vArr)
        lLastCol = UBound(vArr)
    Else
        lFirstRow = LBound(vArr, 1)
        lLastRow = UBound(vArr, 1)
        lFirstCol = LBound(vArr, 2)
        lLastCol = UBound(vArr, 2)
    End If

    If iLinesDim = 1 Then
        If UBound(vLines) - LBound(vLines) <> lLastCol - lFirstCol Then Exit Function
        lNumLines = 1
    Else
        If UBound(vLines, 2) - LBound(vLines, 2) <> lLastCol - lFirstCol Then Exit Function
        lNumLines = UBound(vLines, 1) - LBound(vLines, 1) + 1
    End If

    If Not bRedim Then
        If lNumLines > lLastRow - lFirstRow + 1 Then Exit Function
    End If

    If bRedim Then
        ReDim vArr_Out(lFirstRow To lLastRow + lNumLines, lFirstCol To lLastCol)
    Else
        ReDim vArr_Out(lFirstRow To lLastRow, lFirstCol To lLastCol)
    End If

    For lRow = lFirstRow To lLastRow
        For lCol = lFirstCol To lLastCol
            If iDim = 1 Then
                vArr_Out(lRow, lCol) = vArr(lCol)
            Else
                vArr_Out(lRow, lCol) = vArr(lRow, lCol)
            End If
        Next lCol
    Next lRow

    lStart = UBound(vArr_Out, 1) - lNumLines + 1

    For lRow = lStart To UBound(vArr_Out, 1)
        lRowSource = lRow - lStart
        For lCol = lFirstCol To lLastCol
            If iLinesDim = 1 Then
                vArr_Out(lRow, lCol) = vLines(LBound(vLines) + lCol - lFirstCol)
            Else
                lColSource = LBound(vLines, 2) + lCol - lFirstCol
                vArr_Out(lRow, lCol) = vLines(LBound(vLines, 1) + lRowSource, lColSource)
            End If
        Next lCol
    Next lRow

    InsertLines = vArr_Out
End Function