VBA 如何将array数组逆序排列呢？---包含1维数组，2维数组举例

 

代码1：试图利用  strreverse 取反，但失败-----局限性太大

• 好像没有数组直接反转的 函数
• 我尝试利用strreverse转一下看看
• 这个字符串取反strreverse，连10都变01，不可行，不是按单个元素取反序的。
• 局限性较大
• 少数情况，每个element 都是但字符的  但数字字母的也许可用。

Sub jackma_arrtest1()

'好像没有数组直接反转的 函数
'我尝试利用strreverse转一下看看

Dim arr1

arr1 = Array(1, 2, 3, 4, 5, 5, 5, 8, 9, 10, "5")

arr2 = Join(arr1, "-")
Debug.Print "arr2是变成了-相连的字符串", arr2

arr3 = StrReverse(arr2)
Debug.Print "arr2是取反的字符串"; arr3

arr4 = Split(arr3, "-")

For i = LBound(arr4) To UBound(arr4)
   Debug.Print arr4(i)
Next
Debug.Print "很遗憾，这个字符串取反，连10都变01，不可行，不是按单个元素取反序的"

End Sub

 

 

代码2 成功代码： 我的想法就是 index反过来 从高到低

两种思路有小差异，都可以

• 思路1：直接把原数组，倒序，写入另外一个新数组
• 思路2：原数组，正序，倒着写入一个新数组---但这个要注意，写完了才能全部展示，否则错误

Sub ponymago1()

Dim arr1()
Dim arr2()
Dim arr3()

arr1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)
ReDim arr2(UBound(arr1) - LBound(arr1))  '如果不 redim 下面使用时会报错越界
ReDim arr3(UBound(arr1) - LBound(arr1)) '如果不 redim 下面使用时会报错越界

k = 0
For i = UBound(arr1) To LBound(arr1) Step -1
  Debug.Print "arr1(" & i & ")=" & arr1(i); "  ";
  arr2(k) = arr1(i)
  Debug.Print "arr2(" & k & ")=" & arr2(k)
  k = k + 1
Next
Debug.Print


j = 0
'  Debug.Print UBound(arr1)
'  Debug.Print LBound(arr1)
'  Debug.Print UBound(arr1) - LBound(arr1) + j
For i = LBound(arr1) To UBound(arr1)
  arr3(UBound(arr3) - LBound(arr3) + j) = arr1(i)
  Debug.Print "arr3(" & UBound(arr3) - LBound(arr3) + j & ")=" & arr3(UBound(arr3) - LBound(arr3) + j)
  j = j - 1
Next
Debug.Print

For i = LBound(arr3) To UBound(arr3)
   Debug.Print "arr3(" & i & ")=" & arr3(i)
Next

End Sub

 

 

代码3：正确逆序排列了2维数组

• 数组遍历， for each  会先逐列遍历
• 当用2个数组分别遍历的时，教训就是，两个数组的 循环变量，应该完全独立，这种错误犯了多次了
• 记得你循环的起点，终点，方向（逆序，从大到小赋值就应该是  j1=j1-1）
• 循环变量的插入位置，一定要注意
• 参考过：  http://club.excelhome.net/thread-1120315-1-1.html

Sub charleszhang1()
Dim arr2()
arr1 = Range("d1:g3")

'Debug.Print LBound(arr1, 1)
'Debug.Print UBound(arr1, 1)
'Debug.Print LBound(arr1, 2)
'Debug.Print UBound(arr1, 2)



'会先按第1列，第2列，数据逐步陈列数据
For Each x In arr1
    Debug.Print x;
Next
Debug.Print

For m = LBound(arr1) To UBound(arr1)
    For n = LBound(arr1, 2) To UBound(arr1, 2)
        Debug.Print arr1(m, n);
    Next
    Debug.Print
Next
Debug.Print

i1 = LBound(arr1)
ReDim arr2(LBound(arr1, 1) To UBound(arr1, 1), LBound(arr1, 2) To UBound(arr1, 2))
For i = LBound(arr1, 1) To UBound(arr1, 1)
   j1 = UBound(arr1, 2)
   For j = LBound(arr1, 2) To UBound(arr1, 2)
'       arr2(i1, j1 + UBound(arr2, 2) - LBound(arr2, 2)) = arr1(i, j)
       arr2(i1, j1) = arr1(i, j)
       j1 = j1 - 1
'       arr2(i, j) = arr1(i, j)
   Next
   i1 = i1 + 1
   Debug.Print
Next


For x = LBound(arr2) To UBound(arr2)
    For y = LBound(arr2, 2) To UBound(arr2, 2)
        Debug.Print arr2(x, y);
    Next
    Debug.Print
Next
Debug.Print



End Sub

 

 

 

代码3-2 另外一种写法，2维数组逆序

Sub charleszhang1()
Dim arr2()
arr1 = Range("d1:g3")


For x = LBound(arr1) To UBound(arr1)
    For y = LBound(arr1, 2) To UBound(arr1, 2)
        Debug.Print arr1(x, y);
    Next
    Debug.Print
Next
Debug.Print



'ReDim arr2(3, 4)
ReDim arr2(LBound(arr1, 1) To UBound(arr1, 1), LBound(arr1, 2) To UBound(arr1, 2))

i1 = LBound(arr2)
For i = LBound(arr1, 1) To UBound(arr1, 1)
   j1 = LBound(arr2, 2)
   For j = UBound(arr1, 2) To LBound(arr1, 2) Step -1

       arr2(i1, j1) = arr1(i, j)
       Debug.Print arr2(i1, j1);

       j1 = j1 + 1
   Next
   i1 = i1 + 1
   Debug.Print
Next


End Sub

 

 

代码4：EXCEL表，逆序排列数据

Sub test5()

arr1 = Range("d1:g3")


For i = LBound(arr1, 1) To UBound(arr1, 1)
    k = 0
    l = 12
    For j = LBound(arr1, 2) To UBound(arr1, 2)
'        Cells(1, 9).Resize(UBound(arr1), UBound(arr, 2)) = arr1(i, j)
         Cells(i, 9 + k) = arr1(i, j)
         
         Cells(i + 5, l) = arr1(i, j)
         k = k + 1
         l = l - 1
    Next
Next


End Sub