How to add a counter column to existing matrix in VBA?
arrays, excel, matrix, vba
Solution
If your main concern is the performance, then use `Redim Preserve` to add a new column at the end and use the OS API to shift each column directly in the memory:
Private Declare PtrSafe Sub MemCpy Lib "kernel32" Alias "RtlMoveMemory" ( _
ByRef dst As Any, ByRef src As Any, ByVal size As LongPtr)
Private Declare PtrSafe Sub MemClr Lib "kernel32" Alias "RtlZeroMemory" ( _
ByRef src As Any, ByVal size As LongPtr)
Sub AddIndexColumn()
Dim arr(), r&, c&
arr = [A1:F1000000].Value
' add a column at the end
ReDim Preserve arr(LBound(arr) To UBound(arr), LBound(arr, 2) To UBound(arr, 2) + 1)
' shift the columns by 1 to the right
For c = UBound(arr, 2) - 1 To LBound(arr, 2) Step -1
MemCpy arr(LBound(arr), c + 1), arr(LBound(arr), c), (UBound(arr) - LBound(arr) + 1) * 16
Next
MemClr arr(LBound(arr), LBound(arr, 2)), (UBound(arr) - LBound(arr) + 1) * 16
' add an index in the first column
For r = LBound(arr) To UBound(arr)
arr(r, LBound(arr, 2)) = r
Next
End Sub
Problem
How to get a new matrix in VBA with a counter value in the first "column". Suppose we have a VBA matrix which values we get from cells. The value of `A1` cell is simply "A1". ``` Dim matrix As Variant matrix = Range("A1:C5").value ``` Input matrix: ``` +----+----+----+ | A1 | B1 | C1 | +----+----+----+ | A2 | B2 | C2 | +----+----+----+ | A3 | B3 | C3 | +----+----+----+ | A4 | B4 | C4 | +----+----+----+ | A5 | B5 | C5 | +----+----+----+ ``` I would like to get new matrix with the counter value in the first column of VBA matrix. Here are desired results: ``` +----+----+----+----+ | 1 | A1 | B1 | C1 | +----+----+----+----+ | 2 | A2 | B2 | C2 | +----+----+----+----+ | 3 | A3 | B3 | C3 | +----+----+----+----+ | 4 | A4 | B4 | C4 | +----+----+----+----+ | 5 | A5 | B5 | C5 | +----+----+----+----+ ``` One way to do it is looping. Would there be any other more elegant way to do it? We are dealing here with large data sets, so please mind the performance.