vba excel vba中的数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9023880/
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
Array in excel vba
提问by user793468
I want to have an array list in vba, hence I have a variant declared in excel vba like:
我想在 vba 中有一个数组列表，因此我在 excel vba 中声明了一个变体，例如：
Dim Students(10) as variant
Now I want to store numbers in Students list. the numbers are not continuous. Sometime like:
现在我想在学生列表中存储数字。数字不连续。有时像：
Students(2,7,14,54,33,45,55,59,62,66,69)
How can I do this in vba? also how can I access the list items?
我怎样才能在vba中做到这一点？也如何访问列表项？
回答by Tony Dallimore
Students must be declared as a dynamic array. That is, an array whose bounds can be changed. Dim Students(10)gives an array whose bounds cannot be changed and cannot be loaded from an array.
必须将学生声明为动态数组。也就是说，一个可以改变边界的数组。 Dim Students(10)给出一个数组，它的边界不能改变，也不能从数组中加载。
Dim Students() As Variant
To load Students:
加载学生：
Students = Array(2,7,14,54,33,45,55,59,62,66,69)
To access the elements:
要访问元素：
Dim Inx As Long
For Inx = LBound(Students) to UBound(Students)
Debug.Print Students(Inx)
Next
LBound (Lower bound) and UBound mean that the for loop adjusts to the actual number of elements in Students.
LBound（下界）和UBound 表示for 循环会根据Students 中的实际元素数量进行调整。
回答by puzzlepiece87
This is too complex for you right now, and you'll probably never run into a situation where you'll need this, but:
现在这对您来说太复杂了，您可能永远不会遇到需要它的情况，但是：
I use the following method for forming more memory-efficient arrays (because Variant uses the most memory of any variable type) while still having the convenience of declaring the array contents in one line. To follow your example:
我使用以下方法来形成更节省内存的数组（因为 Variant 使用任何变量类型的最多内存），同时仍然可以方便地在一行中声明数组内容。按照你的例子：
Dim Students() As Long
Dim Array2() As String
Array2() = Split("2,7,14,54,33,45,55,59,62,66,69", ",")
ReDim Array1(0) As Long
For Loop1 = LBound(Array2()) To UBound(Array2())
ReDim Preserve Array1(0 To (UBound(Array1) + 1)) As String
Array1(Loop1) = Array2(Loop1)
Next Loop1
ReDim Preserve Array1(0 To (UBound(Array1) - 1)) As Long
Erase Array2
An example of accessing it would be something like:
访问它的一个例子是：
For Loop1 = LBound(Students) to UBound(Students)
Msgbox Students(Loop1)
Next Loop1
I learned this from here: http://www.vbforums.com/showthread.php?669265-RESOLVED-VBA-Excel-Assigning-values-to-array-in-a-single-line&p=4116778&viewfull=1#post4116778
我从这里了解到这一点：http: //www.vbforums.com/showthread.php?669265-RESOLVED-VBA-Excel-Assigning-values-to-array-in-a-single-line& p=4116778& viewfull=1#post4116778
回答by Skytunnel
You can add values to an Array like this...
您可以像这样向数组添加值...
For i = 1 to 10
Students(i) = i
Next i
Or like this
或者像这样
Students = Array(2,7,14,54,33,45,55,59,62,66,69)
Then you can access the values in the same manor. Note if you use the second option you'll need to declare it as follows:
然后您可以访问同一个庄园中的值。请注意，如果您使用第二个选项，则需要按如下方式声明它：
Dim Students() As Variant
回答by Mohammed Ehsan
Well, That depends on how you would supply the values for the array, would you get the values from Worksheet.Rangeor from TextBoxor ListBox, But basically the code would be something like that :
嗯，这取决于您如何为数组提供值，您是从Worksheet.Range还是从TextBox或ListBox获取值，但基本上代码将是这样的：
Dim students(10) as Integer
Dim Carrier as Integer
For i = LBound(students) To UBound(Students)
'some code to get the values you want to from whatever is your source
'then assign the value to Carrier
students(i)=Carrier
Next i
It is not good practice to dim an array as Variant when you certainly know that you are going to use integers only, as it will eat alot of memory that is not needed in the first place. You also should be aware of the bounds of the numbers that are going to be assigned, if it exceeds the Integer limit then you should use Double or Float. This is my first participation in the site,Cheers.
当您肯定知道您将只使用整数时，将数组变暗并不是一个好习惯，因为它会占用大量最初不需要的内存。您还应该注意将要分配的数字的界限，如果它超过整数限制，那么您应该使用 Double 或 Float。这是我第一次参与该网站，干杯。
