CODEWITHSUNDEEP
arraysexcelvba
Walter Sanchez
Walter Sanchez

Reputation: 41

Reverse an array in Excel VBA

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?

Upvotes: 4

Views: 4700

Answers (3)

Noam Brand
Noam Brand

Reputation: 346

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

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149305

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

enter image description here

Upvotes: 5

Bruno 82
Bruno 82

Reputation: 519

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.

Upvotes: 1

Related Questions