在Excel VBA中反转一个数组

Question

我有以下代码，根据逻辑它应该可以运行。

我希望输出为(4,3,2,1)，但是在循环结束时，t=(4,3,3,4)。

Sub try()

  Dim t As Variant

  t = Array(1, 2, 3, 4)
  a = UBound(t)

  For k = 0 To a
    t(k) = t(a - k)
  Next k

End Sub

有什么想法吗？

- Walter Sanchez

Answer 1

在进行转换之前，您需要使用临时变量来存储内容，否则它将被覆盖。

您是想要这样做吗？

Sub try()
    Dim t As Variant, tmp As Variant
    Dim a As Long, b As Long, i As Long

    t = Array(1, 2, 3, 4)

    a = UBound(t): b = LBound(t)

    For i = 0 To ((a - b) \ 2)
        tmp = t(i)
        t(i) = t(a)
        t(a) = tmp
        a = a - 1
    Next i

    For i = 0 To UBound(t)
        Debug.Print t(i)
    Next i
End Sub

- Siddharth Rout

Answer 2

当你执行t(k) = t(a - k)时，你将t(a-k)赋值给t(k)，但是t(k)中存储的值会丢失。你需要在另一个变量（下面示例中的变量x）中暂时存储它，然后可以像这样交换t(k)和t(a-k)之间的值：

Sub try()

  Dim t As Variant
  Dim x As Variant
  Dim b As Integer

  t = Array(1, 2, 3, 4)
  a = UBound(t)
  b = (a - 1) / 2

  For k = 0 To b
    x = t(k)
    t(k) = t(a - k)
    t(a - k) = x
  Next k

End Sub

注意，你只需要迭代数组大小的一半（向下取整），否则你会再次交换值并最终得到相同的起始数组。

- Bruno 82

Answer 3

如果不想删除原始数组，您可以创建一个反向创建的数组副本。 请参见1D和2D阵列子程序：

Option Base 1
Sub CopyArrayinReverseColumns1D()
  Dim OriginalArr As Variant
  Dim newarr As Variant
  Dim a As Long
  Dim i As Long

  OriginalArr = Array(1, 2, 3, 4)
  newarr = Array(0, 0, 0, 0)
  a = UBound(OriginalArr)
  
  For i = 1 To a
    newarr(i) = OriginalArr(a - i + 1)
    Debug.Print newarr(i)
  Next
End Sub

Sub CopyArrayinReverseColumns2D()
    Dim OriginalArr(2, 4) As Variant
    Dim newarr(2, 4) As Variant
    Dim a As Long
    Dim b As Long
    Dim i As Long
    Dim n As Long
   OriginalArr(1, 1) = 65
   OriginalArr(1, 2) = 70
   OriginalArr(1, 3) = 75
   OriginalArr(1, 4) = 80
   
   OriginalArr(2, 1) = 85
   OriginalArr(2, 2) = 90
   OriginalArr(2, 3) = 95
   OriginalArr(2, 4) = 100
   
  a = UBound(OriginalArr, 1)
  b = UBound(OriginalArr, 2)

  For i = 1 To a
        For n = 1 To b
          newarr(i, n) = OriginalArr(a - i + 1, n)
           Debug.Print newarr(i, n)
        Next
  Next
End Sub

- Noam Brand