VBA(Excel)初始化整个数组而不循环 [英] VBA (Excel) Initialize Entire Array without Looping
问题描述
我对 VBA 还很陌生,所以这可能是一个简单的问题,但在这里.
I am fairly new to VBA, so this may be a simple question but here goes.
我想在 VBA 中初始化整个数组 myArray
,比如整数.我知道我可以通过这样的简单初始化来做到这一点:
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:
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
我试过了,但编译器抱怨.然后我尝试了 myArray() = 0
,它也抱怨了这一点.
I tried that but the compiler complained. Then I tried myArray() = 0
and it complained about that, too.
谁能解释一下如何做到这一点,不循环?如果可能的话,我想在一份声明中做到这一点.
Can anyone explain how to do this, without looping? I'd like to do it in one statement if possible.
澄清:
我想将数组的每个元素初始化为某个初始值.因此,例如,如果我有一个包含 300 个整数的数组 Dim myArray(300) As Integer
,则所有 300 个元素都将保持相同的初始值(例如,数字 13).
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).
更多说明
我发现 this answer 表明您可以使用如下变量执行此操作:
I found this answer that 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?
推荐答案
这很简单,至少如果你想要一个基于 1 的 1D 或 2D 变体数组:
This is easy, at least if you want a 1-based, 1D or 2D variant array:
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
字节数组也不错:
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
如果你想在其他情况下避免循环,它会变得更加棘手.除非您的数组是 50K 或更大的条目,否则这并不值得.正如我在 较早的答案中所说,只需在循环中设置每个值,您就会很快足够.
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.
这篇关于VBA(Excel)初始化整个数组而不循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!