使用范围 VBA 填充数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15657635/
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
Populating an array with range VBA
提问by user1545643
I have been given the job of fixing a holiday spreadsheet at work, the problem with the document is that there are direct references, indirect references to other worksheets and some parts non referenced, so if you want to put people in a different order it completely messes the whole thing up. So, what I have been trying to do is to populate an array with the peoples names, sort the array, then cross reference that with the original and find a new order so that this new order can be implemented throughout the worksheets without messing things up.
我的工作是修复工作中的假期电子表格，该文档的问题是存在对其他工作表的直接引用、间接引用和未引用的某些部分，因此如果您想将人们放在完全不同的顺序中把整个事情搞砸了。所以，我一直在尝试做的是用人们的名字填充一个数组，对数组进行排序，然后将它与原始数组进行交叉引用并找到一个新顺序，以便可以在整个工作表中实现这个新顺序而不会弄乱事情.
The problem is that I can't seem to get the arrays to populate, I have looked at the other answers on here, but I'm sure I'm using redim properly and that tends to be the problem.
问题是我似乎无法填充数组，我已经查看了此处的其他答案，但我确定我正确使用了 redim 并且这往往是问题所在。
So Previous is the original listing, Current is the sorted list and Position is the number that relates the previous to the current.
因此，Previous 是原始列表，Current 是排序列表，Position 是将前一个与当前相关联的数字。
Sub Sorting()
Dim Previous() As Variant
Dim Current() As Variant
Dim maxrow As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim Position() As Long
Dim rng As Range
Dim strTemp As String
k = 0
i = 3
maxrow = 3
Do While Worksheets(1).Cells(i, 1).Value <> "STAT.HOL'S (ST)"
maxrow = maxrow + 1
i = i + 1
Loop
maxrow = maxrow - 1
ReDim Previous(0 To maxrow)
ReDim Position(0 To maxrow)
ReDim Current(0 To maxrow)
Previous = Range("a4", Range("a" & maxrow))
Current = Previous
For i = 0 To maxrow
For j = 0 To maxrow
If Current(i) > Current(j) Then
strTemp = Current(i)
Current(i) = Current(j)
Current(j) = strTemp
End If
Next j
Next i
For i = 0 To maxrow
For j = 0 To maxrow
If Previous(i) = Current(j).Value Then
Position(k) = j
k = k + 1
End If
Next j
Next i
End Sub
Thanks for your help. Amy
谢谢你的帮助。艾米
采纳答案by Olle Sj?gren
You do populate the arrays, but you are adressing them the wrong way.
您确实填充了数组，但是您以错误的方式处理它们。
When you assign a range to an array, the array is automatically redimensioned to a two-dimensional array, one for the rows and one for the columns.
当您为数组分配一个范围时，该数组会自动重新定义为二维数组，一个用于行，另一个用于列。
Since your range is just one columm, the second dimension is always 1. So after you run the line Previous = Range("a4", Range("a" & maxrow))you have an array that is dimensioned as:
由于您的范围只是一列，因此第二个维度始终为 1。因此，在运行该行后，您将Previous = Range("a4", Range("a" & maxrow))获得一个尺寸为的数组：
Previous(1 to 10, 1 To 1)
Also, your For loops could be changed to something like this:
此外，您的 For 循环可以更改为如下所示：
For i = LBound(Current, 1) To UBound(Current, 1)
Next i
回答by Charles Williams
Excel ranges are always treated as having 2 dimensions even when they are a single column.
Also you don't need to redim your variant arrays - just assign the range to a plain variant variable and it will create a variant containing a 2-dimensional array:
Excel 范围始终被视为具有 2 个维度，即使它们是单列。此外，您不需要重新调整您的变体数组 - 只需将范围分配给一个普通的变体变量，它将创建一个包含二维数组的变体：
Dim Previous as variant
Previous = Range("a4:a" & maxrow)
