如何在 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屋！