VBA(Excel)初始化整个数组不循环 [英] VBA (Excel) Initialize Entire Array without Looping
问题描述
我对VBA很新,所以这可能是一个简单的问题,但是在这里。
我想初始化整个数组在VBA中,myArray
表示整数。我知道我可以通过简单的初始化这样做:
Dim myArray
myArray = Array(1, 2,4,8)
但是,如果数组很大,这很麻烦,我想将所有元素初始化为相同的值。理想情况是这样的:
myArray(:) = 0
/ pre>
我尝试过,但编译器抱怨。然后我尝试了
myArray()= 0
,它也抱怨这个。
任何人都可以解释如何做这个, 没有循环 ?
澄清:
我想初始化数组的每一个元素到一些初始值。
所以如果我有一个数组Dim myArray(300)As Integer
的300个整数,例如,所有300个元素将保持相同的初始值(比如说,
更多澄清
我发现这个答案,表示您可以使用如下变量来执行此操作:
Dim x As Double:x = 0
也许有一种方式稍微更新语法以使其适用于数组?
解决方案这很简单,至少如果你想要一个1 ,1D或2D变体数组:
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, ))
End Sub
字节数组也不是太差:
私人声明e Sub FillMemory Libkernel32别名RtlFillMemory_
(dest As Any,ByVal size As Long,ByVal fill As Byte)
Sub StuffBArr()
Dim i( 0至39)As Byte
Dim j(1至2,5,至29,2至6)As Byte
FillMemory i(0),40,13
FillMemory j(1,5) ,2),2 * 25 * 5,13
End Sub
您可以使用同样的方法来填充其他数字数据类型的数组,但是你只能被限制为可以用单个重复字节表示的值:
Sub StuffNArrs()
Dim i(0到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'给出-1
FillMemory i(0),5 * LenB(i(0)),& H80'给出-2139062144
FillMemory i(0),5 * LenB(i(0) & H7F'给出2139062143
FillMemory j(0),5 * LenB(j(0)),& HFF给出-1
FillMemory u(0),5 * LenB(u(0)),& HFF'给出-0.0001
FillMemory f(0),5 * LenB(f(0)),& HFF'给出-1。#QNAN
FillMemory f(0),5 * LenB(f(0)),& H80'给出-1.18e-38
FillMemory f(0),5 * LenB(f(0)),& H7F'给出3.40e + 38
FillMemory g(0),5 * LenB(g(0)) ,& HFF'给出-1。#QNAN
End Sub
如果你想在其他情况下避免循环,它变得更加光滑。除非你的数组是50K条目或更大,否则不值得。只要将一个值放在一个循环中,就像我在早期的答案中所说的那样,你会快速 。
I am fairly new to VBA, so this may be a simple question but here goes.
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
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.
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 Integer
of 300 integers, for example, all 300 elements would hold the same initial value (say, the number 13).More Clarification
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?
解决方案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
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.
这篇关于VBA(Excel)初始化整个数组不循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!