VBA：反转数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40563940/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA: Reverse an array?
提问by rits
How could I reverse an array that is full of integers e.g.:
我怎么能反转一个充满整数的数组，例如：
[1;5;8;45;54]
To:
到：
[54;45;8;5;1]
Are there any built in functions I could use?
我可以使用任何内置功能吗？
I tried using thismethod:
我尝试使用这种方法：
Array.Reverse(arr)
I added Mscorlib.dll from Tools > References, but it showed error: Syntax error. At the Array.Reverse(arr) location.
我从工具> 引用添加了 Mscorlib.dll，但它显示错误：语法错误。在 Array.Reverse(arr) 位置。
回答by user3598756
you could use ArrayListclass and wrap its Reversemethod:
你可以使用ArrayListclass 并包装它的Reverse方法：
Function ReverseArray(arr As Variant) As Variant
Dim val As Variant
With CreateObject("System.Collections.ArrayList") '<-- create a "temporary" array list with late binding
For Each val In arr '<--| fill arraylist
.Add val
Next val
.Reverse '<--| reverse it
ReverseArray = .Toarray '<--| write it into an array
End With
End Function
to be used like:
像这样使用：
Sub main()
Dim arr As Variant
arr = ReverseArray(Array(1, 2, 3, 4, 5)) '<-- it returns an array of Variant/Integer with values 5,4,3,2,1
End Sub
回答by Andre
Array.Reversesounds like VB.Net, not VBA.
Array.Reverse听起来像 VB.Net，而不是 VBA。
Chip Pearson has functions for just about anything you will want to do with arrays (and other structures).
Chip Pearson 具有几乎可以处理数组（和其他结构）的任何功能。
http://www.cpearson.com/excel/vbaarrays.htm--> ReverseArrayInPlace
http://www.cpearson.com/excel/vbaarrays.htm-->ReverseArrayInPlace
The relevant part is:
相关部分是：
Ndx2 = UBound(InputArray)
' loop from the LBound of InputArray to the midpoint of InputArray
For Ndx = LBound(InputArray) To ((UBound(InputArray) - LBound(InputArray) + 1) \ 2)
'swap the elements
Temp = InputArray(Ndx)
InputArray(Ndx) = InputArray(Ndx2)
InputArray(Ndx2) = Temp
' decrement the upper index
Ndx2 = Ndx2 - 1
Next Ndx
回答by NOYB
Andre's answer referring to Chip Pearson's function I believe the +1 in the for loop is in error that in cases of LBound and UBound not BOTH being EVEN or BOTH being ODD results in the mid point reversal being reverted. i.e. the difference between LBound and UBound being ODD.
Andre 的回答是指 Chip Pearson 的函数，我相信 for 循环中的 +1 是错误的，在 LBound 和 UBound 的情况下，既不是偶数也不是奇数会导致中点反转被逆转。即 LBound 和 UBound 之间的区别是 ODD。
Consider 0 = LBound and 9 = UBound.
考虑 0 = LBound 和 9 = UBound。
9 + 1 = 10 / 2 = 5
9 + 1 = 10 / 2 = 5
So the loop will be for Ndx = 0 to 5. That is 6 iterations. One iteration too many.
所以循环将用于 Ndx = 0 到 5。即 6 次迭代。一次迭代太多了。
Results in the following swaps.
Ndx = 0, Ndx2 = 9: 0<>9
Ndx = 1, Ndx2 = 8: 1<>8
Ndx = 2, Ndx2 = 7: 2<>7
Ndx = 3, Ndx2 = 6: 3<>6
Ndx = 4, Ndx2 = 5: 4<>5
Ndx = 5, Ndx2 = 4: 5<>4
导致以下交换。
Ndx = 0, Ndx2 = 9: 0<>9
Ndx = 1, Ndx2 = 8: 1<>8
Ndx = 2, Ndx2 = 7: 2<>7
Ndx = 3, Ndx2 = 6: 3<>6
Ndx = 4, Ndx2 = 5: 4<>5
Ndx = 5, Ndx2 = 4: 5<>4
So the mid point elements 4 and 5 are swapped, then swapped back.
Resulting in the order of: 9,8,7,6,4,5,3,2,1,0
所以中点元素 4 和 5 被交换，然后交换回来。
结果的顺序为：9,8,7,6,4,5,3,2,1,0
Also LBound should be added to the UBound, not subtracted. If subtracted then it only works for LBound of zero. Consider 50 = LBound, 100 = UBound. That would result in For Ndx = 50 to 25. Note, this is supposed to be a FROM, TO calculation not an iterations count calculation.
此外，LBound 应该添加到 UBound 中，而不是减去。如果减去，则它仅适用于 LBound 为零。考虑 50 = LBound，100 = UBound。这将导致 For Ndx = 50 到 25。请注意，这应该是 FROM、TO 计算而不是迭代计数计算。
Here is my functions for reversing one and two dimensional arrays.
They are also able to optionally retain a specified number of header rows.
这是我用于反转一维和二维数组的函数。
他们还可以选择保留指定数量的标题行。
' Reverse array (one dimensional), optionally retain header rows.
Private Sub Reverse_Array_1d(ByRef Ary As Variant, Optional Header_Rows As Integer = 0)
Dim Dimension_Y As Integer ' Rows (height)
Dim Y_first As Long
Dim Y_last As Long
Dim Y_last_plus_Y_first As Long
Dim Y_next As Long
Dimension_Y = 1
Y_first = LBound(Ary, Dimension_Y) + Header_Rows
Y_last = UBound(Ary, Dimension_Y)
Y_last_plus_Y_first = Y_last + Y_first
Dim tmp As Variant
For Y = Y_first To Y_last_plus_Y_first / 2
Y_next = Y_last_plus_Y_first - Y
tmp = Ary(Y_next)
Ary(Y_next) = Ary(Y)
Ary(Y) = tmp
Next
End Sub
ReDim Ary(0 To 9) As Variant
Header_Rows = 1
Call Reverse_1d_Array(Ary, CInt(Header_Rows))
' Reverse array (two dimensional), optionally retain header rows.
Private Sub Reverse_Array_2d(ByRef Ary As Variant, Optional Header_Rows As Integer = 0)
Dim Dimension_Y As Integer ' Rows (height)
Dim Y_first As Long
Dim Y_last As Long
Dim Y_last_plus_Y_first As Long
Dim Y_next As Long
Dimension_Y = 1
Y_first = LBound(Ary, Dimension_Y) + Header_Rows
Y_last = UBound(Ary, Dimension_Y)
Y_last_plus_Y_first = Y_last + Y_first
Dim Dimension_X As Integer ' Columns (width)
Dim X_first As Long
Dim X_last As Long
Dimension_X = 2
X_first = LBound(Ary, Dimension_X)
X_last = UBound(Ary, Dimension_X)
ReDim tmp(X_first To X_last) As Variant
For Y = Y_first To Y_last_plus_Y_first / 2
Y_next = Y_last_plus_Y_first - Y
For X = X_first To X_last
tmp(X) = Ary(Y_next, X)
Ary(Y_next, X) = Ary(Y, X)
Ary(Y, X) = tmp(X)
Next
Next
End Sub
ReDim Ary(0 To 9, 0 To 3) As Variant
Header_Rows = 1
Call Reverse_2d_Array(Ary, CInt(Header_Rows))
回答by Rich S
(1): The simple but limited solution, if your data is made of of either single characters or palindromes. Let's assume your array is named vTmp, and you can pick a delimiter that doesn't appear in your data -- I'll use "|". Then a one-line approach is:
(1)：简单但有限的解决方案，如果您的数据由单个字符或回文组成。假设您的数组名为 vTmp，您可以选择一个未出现在您的数据中的分隔符——我将使用“|”。那么一种单行方法是：
vTmp = VBA.Split(VBA.StrReverse(VBA.Join(vTmp, "|")), "|")
It is limited, because each value will be reversed as well. It could be solved by replacing the values with indexes that refer to values in another array, but then it's no simpler than the other solutions.
它是有限的，因为每个值也会被反转。它可以通过用引用另一个数组中的值的索引替换值来解决，但它并不比其他解决方案简单。
(2): The full solution, as complete Excel VBA function:
(2)：完整的解决方案，如完整的Excel VBA函数：
Public Function rxReverse(uInput) As Variant
'' Simply reverses the order of items in input array.
'' If Input is singular, then it reverses the content.
''
'' v1 Rich Sulin 05-08-2019
Dim vTmp As Variant
Dim a1 As Long, a2 As Long, a3 As Long
Dim i As Long, j As Long
''
rxReverse = vbNullString
''
'' uInput is a Range object?
If TypeOf uInput Is Range Then
a1 = 1
If uInput.Columns.Count > 1 Then
a2 = uInput.Columns.Count
ReDim vTmp(a1 To a2) As Variant
a3 = 1 + (a2 - a1) / 2
For i = a1 To a3
j = a2 - i + 1
vTmp(j) = uInput(1, i).Value
vTmp(i) = uInput(1, j).Value
Next i
ElseIf uInput.Rows.Count > 1 Then
a2 = uInput.Rows.Count
ReDim vTmp(a1 To a2) As Variant
a3 = 1 + (a2 - a1) / 2
For i = a1 To a3
j = a2 - i + 1
vTmp(j) = uInput(1, i).Value
vTmp(i) = uInput(1, j).Value
Next i
Else
vTmp = VBA.StrReverse(VBA.CStr(uInput.Value))
End If
''
'' uInput is an Array?
Else
Select Case VBA.VarType(uInput)
Case Is >= vbArray
a1 = LBound(uInput)
a2 = UBound(uInput)
ReDim vTmp(a1 To a2) As Variant
a3 = 1 + (a2 - a1) / 2
For i = a1 To a3
j = a2 - i + 1
vTmp(j) = uInput(i)
vTmp(i) = uInput(j)
Next i
''
'' uInput is an irrelevant type?
Case vbNull, vbEmpty, vbError
Exit Function
''
'' uInput is a singular data; reverse it.
Case Else
vTmp = VBA.StrReverse(VBA.CStr(uInput))
End Select
End If
''
rxReverse = vTmp
''
'' If called from a Worksheet, vertically, then orient output array "vertically"
If VBA.IsObject(Application.Caller) Then
If Application.Caller.Rows.Count > 1 Then
rxReverse = Application.WorksheetFunction.Transpose(vTmp)
End If
End If
End Function
