CODEWITHSUNDEEP
arraysvbaexcelrepeat
data-monkey
data-monkey

Reputation: 1715

Repeat a Number N Times in an Array

Working with 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:

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.

Upvotes: 5

Views: 4162

Answers (3)

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3378

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.

Upvotes: -1

John Coleman
John Coleman

Reputation: 52008

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:

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

Upvotes: 3

user6432984
user6432984

Reputation:

Output: 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

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

Upvotes: 5

Related Questions