在数组中重复N次

Working with Excel VBA.

使用Excel VBA。

I'm trying to create an array with repetitive patterns like this:

我正在尝试创建一个具有重复模式的数组:

a = Array(1,1,1,1,1,2,2,2,2,2,2)

Is there a neat solution (without going through a loop) to do this? In almost any other statistical language this is done with a repeat function. For example the "rep" function in R or the "repmat" fuction in Matlab:

是否有一个简洁的解决方案(不经过循环)来实现这一点?在几乎任何其他统计语言中，这都是通过重复函数完成的。例如R中的“rep”函数或Matlab中的“repmat”函数:

a = [repmat(1,1,5),repmat(2,1,6)]

The reason not to hard-code everything in the first place is because the actual array has varying length depending on some other variables.

首先不硬编码所有内容的原因是，实际数组的长度取决于其他一些变量。

3 个解决方案

#1

Output: A,B,B,1,1,1,2,2,2,2

输出:A、B,B,1,1,1,2,2,2,2

Sub ArrayHack()
    Dim a()

    BuildArray a, "A", 1
    BuildArray a, "B", 2
    BuildArray a, 1, 3
    BuildArray a, 2, 4

    Debug.Print Join(a, ",")

End Sub

Function BuildArray(a As Variant, v As Variant, n As Integer)
    Dim i As Integer, count As Integer

    On Error Resume Next
        count = UBound(a)
        If Err.Number <> 0 Then
            ReDim a(0)
            count = -1
        End If
    On Error GoTo 0

    ReDim Preserve a(count + n)

    For i = 1 To n
        a(count + i) = v
    Next

End Function

Output: 1,1,1,1,1,2,2,2,2,2

输出:1,1,1,1,1、2、2、2、2、2

Sub ArrayHack2()
    Dim a
    Dim s As String
    s = Replace(String(5, ","), ",", 1 & ",") & Replace(String(5, ","), ",", 2 & ",")
    s = Left(s, Len(s) - 1)

    a = Split(s, ",")

    Debug.Print Join(a, ",")

End Sub

#2

Here is a somewhat flexible function:

这里有一个灵活的函数:

Function BuildArray(ParamArray params()) As Variant
    Dim A As Variant, v As Variant
    Dim i As Long, j As Long, k As Long, n As Long, m As Long, b As Long

    n = UBound(params)

    If n Mod 2 = 0 Then
        b = params(n)
        n = n - 1
    End If

    For i = 1 To n Step 2
        m = m + params(i)
    Next i

    ReDim A(b To b + m - 1)
    k = b

    For i = 0 To n - 1 Step 2
        v = params(i)
        For j = 1 To params(i + 1)
            A(k) = v
            k = k + 1
        Next j
    Next i

    BuildArray = A
End Function

It takes any number of arguments. If there is an even number of arguments it breaks them into successive pairs of the form v,i where v is a value and i is the number of times to repeat the value, returning the resulting array with first index 0. If there is an odd number of passed parameters, the last parameter is interpreted as the base of the array. For example:

它包含任意数量的参数。如果有偶数个参数，它将它们分解成v的连续对，其中v是一个值，i是重复该值的次数，返回第一个索引为0的结果数组。如果传递的参数数目为奇数，则将最后一个参数解释为数组的基。例如:

Sub test()
    Dim A As Variant

    A = BuildArray(1, 3, 2, 4) 'creates 0-based array [1,1,1,2,2,2,2]
    'verify:
    Debug.Print "A = " & LBound(A) & " to " & UBound(A)
    Debug.Print "Items: " & Join(A, ",")

    A = BuildArray(1, 3, 2, 4, 1) 'creates 1-based array [1,1,1,2,2,2,2]
    'verify:
    Debug.Print "A = " & LBound(A) & " to " & UBound(A)
    Debug.Print "Items: " & Join(A, ",")
End Sub

Output:

输出:

A = 0 to 6
Items: 1,1,1,2,2,2,2
A = 1 to 7
Items: 1,1,1,2,2,2,2

#3

Like this?

像这样的吗?

Const U = 11                                        'U = (4 + Max(i)) * Max(i) + Max(j)
Sub ManipulatingArray()
Dim InputArray As String, i As Long, j As Long, MyArray(1 To U) As Variant
For i = 0 To 1
    For j = 1 To 5 + i
        MyArray((4 + i) * i + j) = i + 1
    Next
Next
Debug.Print Join(MyArray, ",")
End Sub

The above code will give you the output as your example.

上面的代码将给出输出作为示例。

#1