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).

更多说明

我发现这个答案表明你可以用这样的变量来做到这一点:

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屋！