如何在VBA中向现有矩阵添加计数器列? [英] How to add a counter column to existing matrix in VBA?

问题描述

如何在VBA中使用第一个列"中的计数器值获取新矩阵.假设我们有一个VBA矩阵，该矩阵的值是从单元格中获取的. A1单元格的值就是"A1".

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

输入矩阵:

+----+----+----+
| A1 | B1 | C1 |
+----+----+----+
| A2 | B2 | C2 |
+----+----+----+
| A3 | B3 | C3 |
+----+----+----+
| A4 | B4 | C4 |
+----+----+----+
| A5 | B5 | C5 |
+----+----+----+

我想在VBA矩阵的第一列中获得带有计数器值的新矩阵.

I would like to get new matrix with the counter value in the first column of VBA matrix.

以下是理想的结果:

+----+----+----+----+
|  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.

推荐答案

如果主要关注性能，请使用Redim Preserve在末尾添加一个新列，并使用OS API将每个列直接移至内存:

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

这篇关于如何在VBA中向现有矩阵添加计数器列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！