VBA Array repetition

Question

I am trying to make a function that takes in 3 values, and outputs that many of three different numbers in array format (in this case 1,2,4). For example: EntryArray(1,2,1) would output vertically: 1,1,2,2,2,4,4. However, it should repeat this array a certain number of times (given as the last value), and this part I can't get to work. For example: EntryArray(2,1,1,4) should give: 1,1,2,4,1,1,2,4,1,1,2,4,1,1,2,4

My code as follows:

Function EntryArray(One As Integer, Two As Integer, Four As Integer, Rept As Integer) As Integer()

Dim Length As Integer
Length = One + Two + Four

Dim entry() As Integer
ReDim entry(0 To Length, 0)

For i = 1 To Length
entry(i, 0) = 1
If i > One Then entry(i, 0) = 2
If i > Two + One Then entry(i, 0) = 4
Next i

EntryArray = entry

End Function

What is the problem? Sample data and the expected result would also be useful. — cybernetic.nomad, Feb 09 '22 at 18:48
I just want a function to repeat an array a certain number of times — Gabriel, Feb 09 '22 at 18:59

score 1 · Accepted Answer · answered Feb 09 '22 at 19:03

Simply create the loop that enters 1, 2, 4 and loop that Rept times. Use a seperate counter to track where to insert the value into entry

I've simplified the logic of inserting 1, 2, 4 and switched to type Long. There's no advantage in using Integer

Also, you have some "off by one" issues. Using 1 based array is easier

Function EntryArray(One As Long, Two As Long, Four As Long, Rept As Long) As Long()
    Dim i As Long, j As Long, n As Long
    Dim Length As Long
    Length = One + Two + Four

    Dim entry() As Long
    ReDim entry(1 To Length * Rept, 1 To 1)

    n = 1
    For j = 1 to Rept
        For i = 1 To One
            entry(n, 1) = 1
            n= n + 1
        Next
        For i = 1 To Two
            entry(n, 1) = 2
            n= n + 1
        Next
        For i = 1 To Four
            entry(n, 1) = 4
            n= n + 1
        Next
    Next

    EntryArray = entry

End Function

VBA Array repetition

1 Answers1