VBA（Excel）初始化整个数组而不循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19336987/
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 (Excel) Initialize Entire Array without Looping
提问by user1205577
I am fairly new to VBA, so this may be a simple question but here goes.
我对 VBA 相当陌生，所以这可能是一个简单的问题，但这里是。
I would like to initialize an entire array myArray, say of integers, in VBA. I know that I can do this by a simple initialization like so:
我想myArray在 VBA 中初始化整个数组，比如整数。我知道我可以通过像这样的简单初始化来做到这一点：
Dim myArray
myArray = Array(1, 2, 4, 8)
But if the array is large this is cumbersome, and I'd like to initialize all of the elements to the same value. Ideally it would be something like this:
但是如果数组很大，这很麻烦，我想将所有元素初始化为相同的值。理想情况下，它会是这样的：
myArray(:) = 0
I tried that but the compiler complained. Then I tried myArray() = 0and it complained about that, too.
我试过了，但编译器抱怨。然后我试了一下myArray() = 0，它也抱怨了。
Can anyone explain how to do this, without looping? I'd like to do it in one statement if possible.
谁能解释如何做到这一点，而不循环？如果可能的话，我想在一份声明中做到这一点。
Clarification:
澄清：
I want to initialize every single element of the array to some initial value.
So if I have an array Dim myArray(300) As Integerof 300 integers, for example, all 300 elements would hold the same initial value (say, the number 13).
我想将数组的每个元素初始化为某个初始值。因此Dim myArray(300) As Integer，例如，如果我有一个包含300 个整数的数组，则所有 300 个元素都将具有相同的初始值（例如数字 13）。
More Clarification
更多说明
I found this answerthat states that you can do this with a variable like so:
我发现这个答案表明你可以用这样的变量来做到这一点：
Dim x As Double: x = 0
Perhaps there is a way to update the syntax slightly to make it applicable to arrays?
也许有一种方法可以稍微更新语法以使其适用于数组？
采纳答案by Chel
This is easy, at least if you want a 1-based, 1D or 2D variant array:
这很容易，至少如果您想要基于 1 的一维或二维变体数组：
Sub StuffVArr()
Dim v() As Variant
Dim q() As Variant
v = Evaluate("=IF(ISERROR(A1:K1), 13, 13)")
q = Evaluate("=IF(ISERROR(A1:G48), 13, 13)")
End Sub
Byte arrays also aren't too bad:
字节数组也不错：
Private Declare Sub FillMemory Lib "kernel32" Alias "RtlFillMemory" _
(dest As Any, ByVal size As Long, ByVal fill As Byte)
Sub StuffBArr()
Dim i(0 To 39) As Byte
Dim j(1 To 2, 5 To 29, 2 To 6) As Byte
FillMemory i(0), 40, 13
FillMemory j(1, 5, 2), 2 * 25 * 5, 13
End Sub
You can use the same method to fill arrays of other numeric data types, but you're limited to only values which can be represented with a single repeating byte:
您可以使用相同的方法来填充其他数值数据类型的数组，但仅限于可以用单个重复字节表示的值：
Sub StuffNArrs()
Dim i(0 To 4) As Long
Dim j(0 To 4) As Integer
Dim u(0 To 4) As Currency
Dim f(0 To 4) As Single
Dim g(0 To 4) As Double
FillMemory i(0), 5 * LenB(i(0)), &HFF 'gives -1
FillMemory i(0), 5 * LenB(i(0)), &H80 'gives -2139062144
FillMemory i(0), 5 * LenB(i(0)), &H7F 'gives 2139062143
FillMemory j(0), 5 * LenB(j(0)), &HFF 'gives -1
FillMemory u(0), 5 * LenB(u(0)), &HFF 'gives -0.0001
FillMemory f(0), 5 * LenB(f(0)), &HFF 'gives -1.#QNAN
FillMemory f(0), 5 * LenB(f(0)), &H80 'gives -1.18e-38
FillMemory f(0), 5 * LenB(f(0)), &H7F 'gives 3.40e+38
FillMemory g(0), 5 * LenB(g(0)), &HFF 'gives -1.#QNAN
End Sub
If you want to avoid a loop in other situations, it gets even hairier. Not really worth it unless your array is 50K entries or larger. Just set each value in a loop and you'll be fast enough,as I talked about in an earlier answer.
如果您想避免在其他情况下出现循环，它会变得更加棘手。除非您的数组是 50K 条目或更大，否则不值得。只需在循环中设置每个值，你就会足够快，正如我在之前的回答中所谈到的。
回答by Siddharth Rout
You can initialize the array by specifying the dimensions. For example
您可以通过指定维度来初始化数组。例如
Dim myArray(10) As Integer
Dim myArray(1 to 10) As Integer
If you are working with arrays and if this is your first time then I would recommend visiting Chip Pearson's WEBSITE.
如果您正在使用数组并且这是您第一次使用，那么我建议您访问 Chip Pearson 的网站。
What does this initialize to? For example, what if I want to initialize the entire array to 13?
这初始化为什么？比如我想把整个数组初始化为13怎么办？
When you want to initailize the array of 13 elements then you can do it in two ways
当您想初始化 13 个元素的数组时，您可以通过两种方式进行
Dim myArray(12) As Integer
Dim myArray(1 to 13) As Integer
In the first the lower bound of the array would start with 0so you can store 13 elements in array. For example
在第一个数组的下限将开始，0因此您可以在数组中存储 13 个元素。例如
myArray(0) = 1
myArray(1) = 2
'
'
'
myArray(12) = 13
In the second example you have specified the lower bounds as 1so your array starts with 1and can again store 13 values
在第二个示例中，您指定了下限，1因此您的数组开始1并可以再次存储 13 个值
myArray(1) = 1
myArray(2) = 2
'
'
'
myArray(13) = 13
Wnen you initialize an array using any of the above methods, the value of each element in the array is equal to 0. To check that try this code.
使用上述任何一种方法初始化数组时，数组中每个元素的值都等于0。要检查，试试这个代码。
Sub Sample()
Dim myArray(12) As Integer
Dim i As Integer
For i = LBound(myArray) To UBound(myArray)
Debug.Print myArray(i)
Next i
End Sub
or
或者
Sub Sample()
Dim myArray(1 to 13) As Integer
Dim i As Integer
For i = LBound(myArray) To UBound(myArray)
Debug.Print myArray(i)
Next i
End Sub
FOLLOWUP FROM COMMENTS
来自评论的跟进
So, in this example every value would be 13. So if I had an array Dim myArray(300) As Integer, all 300 elements would hold the value 13
所以，在这个例子中，每个值都是 13。所以如果我有一个数组 Dim myArray(300) As Integer，那么所有 300 个元素都会保存值 13
Like I mentioned, AFAIK, there is no direct way of achieving what you want. Having said that here is one way which uses worksheet function Reptto create a repetitive string of 13's. Once we have that string, we can use SPLITusing ","as a delimiter. But note this creates a variant array but can be used in calculations.
就像我提到的，AFAIK，没有直接的方法来实现你想要的。话虽如此，这是一种使用工作表函数Rept创建重复的 13 字符串的方法。一旦我们有了那个字符串，我们就可以使用SPLITusing","作为分隔符。但请注意，这会创建一个变体数组，但可用于计算。
Note also, that in the following examples myArraywill actually hold 301 values of which the last one is empty - you would have to account for that by additionally initializing this value or removing the last "," from sNumbefore the Splitoperation.
另请注意，在以下示例中，myArray实际上将保存 301 个值，其中最后一个为空 - 您必须通过额外初始化此值或从操作sNum之前删除最后一个“，”来解决这个问题Split。
Sub Sample()
Dim sNum As String
Dim i As Integer
Dim myArray
'~~> Create a string with 13 three hundred times separated by comma
'~~> 13,13,13,13...13,13 (300 times)
sNum = WorksheetFunction.Rept("13,", 300)
sNum = Left(sNum, Len(sNum) - 1)
myArray = Split(sNum, ",")
For i = LBound(myArray) To UBound(myArray)
Debug.Print myArray(i)
Next i
End Sub
Using the variant array in calculations
在计算中使用变体数组
Sub Sample()
Dim sNum As String
Dim i As Integer
Dim myArray
'~~> Create a string with 13 three hundred times separated by comma
sNum = WorksheetFunction.Rept("13,", 300)
sNum = Left(sNum, Len(sNum) - 1)
myArray = Split(sNum, ",")
For i = LBound(myArray) To UBound(myArray)
Debug.Print Val(myArray(i)) + Val(myArray(i))
Next i
End Sub
回答by tbur
I want to initialize every single element of the array to some initial value. So if I have an array Dim myArray(300) As Integer of 300 integers, for example, all 300 elements would hold the same initial value (say, the number 13).
Can anyone explain how to do this, without looping?I'd like to do it in one statement if possible.
我想将数组的每个元素初始化为某个初始值。因此，如果我有一个数组 Dim myArray(300) 作为 300 个整数的整数，例如，所有 300 个元素将保持相同的初始值（例如，数字 13）。
谁能解释如何做到这一点，而不循环？如果可能的话，我想在一份声明中做到这一点。
What do I win?
我赢什么？
Sub SuperTest()
Dim myArray
myArray = Application.Transpose([index(Row(1:300),)-index(Row(1:300),)+13])
End Sub
回答by EEM
This function works with variables for size and initial value it combines tbur & Filipe responses.
此函数使用大小和初始值的变量，它结合了 tbur 和 Filipe 响应。
Function ArrayIniValue(iSize As Integer, iValue As Integer)
Dim sIndex As String
sIndex = "INDEX(Row(1:" & iSize & "),)"
ArrayIniValue = Evaluate("=Transpose(" & sIndex & "-" & sIndex & "+" & iValue & ")")
End Function
Called this way:
这样调用：
myArray = ArrayIniValue(350, 13)
回答by Filipe
Fancy way to put @rdhs answer in a function:
将@rdhs 答案放入函数的奇特方法：
Function arrayZero(size As Integer)
arrayZero = Evaluate("=IF(ISERROR(Transpose(A1:A" & size & ")), 0, 0)")
End Function
And use like this:
并像这样使用：
myArray = arrayZero(15)
回答by Santosh
For VBA you need to initialise in two lines.
对于 VBA，您需要在两行中进行初始化。
Sub TestArray()
Dim myArray
myArray = Array(1, 2, 4, 8)
End Sub
