Reverse an array in Excel VBA

user_8010550 0
Viewed 3616

I have the following code, which based on the logic it should work.

I want it to be (4,3,2,1), but at the end of the loop I get 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

Any ideas?

vba excel arrays
3 Answers

If don't want to delete the original array you can create a copy of the array in reverse creation. See 1D and 2D array subs:

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
user_107216750
Related