Reverse an array in Excel VBA

Question

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?

Answer 1

You have to use a temporary variable to store the stuff before you make the switch else it will be overwritten.

Is this what you are trying?

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

Answer 2

When you do t(k) = t(a - k) you assign t(a-k) to t(k), but then the value stored in t(k) is lost. You need to temporarily store that in another variable (variable x in the following example), then you can swap the values between t(k) and t(a - k) like this:

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

Notice that you only need to iterate a number of times that is half of your array size (rounded down) otherwise you'd swap back values again and would end up with the same starting array.