Search code examples
arraysexcelvbafill

excel vba filling array

I have the following problem. I have a userform with entry fields. The user is going to enter a number for participants. I have four groups of participants:

Group A: 5
Group B: 6
Group C: 1
Group D: 2

Each participant should be named like this: {GA1, GA2, ..., GD2} I wanted to write this into an array in that order and then use this array to fill cells with the names but all I came up with were four for-loops to write it into the array and that failed too. Is there a better way to do this?

Dim GA As Integer
Dim GB As Integer
Dim GC As Integer
Dim GD As Integer
Dim PartSum As Integer


GA = TextBox32.Value
GB = TextBox33.Value
GC = TextBox34.Value
GD = TextBox35.Value

PartSum = GA + GB + GC + GD

Dim NamingArray() As String

ReDim NamingArray(1 To PartSum)

For i = 0 To GA
    NamingArray(i) = "GA " & CStr(i)
Next i

For j = GA To GA + GB
    NamingArray(i) = "GB " & CStr(j)
Next j

For k = GA + GB To GA + GB + GC
    NamingArray(i) = "GC " & CStr(k)
Next k

For l = GA + GB + GC To GA + GB + GC + GD
    NamingArray(i) = "GD " & CStr(l)
Next l

'check entries
For i = LBound(NamingArray) To UBound(NamingArray)

    MsgBox (NamingArray(i))

Next i
Solution

  • I can see three reasons why your code isn't behaving like you expect.

    First, the variables you use as indices in your For ... Next loops are inconsistent. In this loop, for instance, you increment k but use i to index into NamingArray. Note that i still has the value GA+1 left over from the first loop. 

    For k = GA + GB To GA + GB + GC
    NamingArray(i) = "GC " & CStr(k)
Next k

    Just use i for all your loops. No need to use a different letter every time.

    Second, you try to access element 0 of NamingArray, which doesn't exist. 

    ReDim NamingArray(1 To PartSum) ' starts at 1

For i = 0 To GA 
    NamingArray(i) = "GA " & CStr(i) ' attempt to refer to NamingArray(0)
Next i

    Third, your indexing is completely messed up more generally. For instance, NamingArray(GA) will be written to at the end of your first loop, and then overwritten at the beginning of your second loop. This happens for all your loops; their "jurisdictions" overlap (sorry, I'm Canadian). I've corrected this (and all the other errors) below. This works:

    For i = 1 To GA
    NamingArray(i) = "GA " & CStr(i)
Next i

For i = 1 + GA To GA + GB
    NamingArray(i) = "GB " & CStr(i - GA)
Next i

For i = 1 + GA + GB To GA + GB + GC
    NamingArray(i) = "GC " & CStr(i - GA - GB)
Next i

For i = 1 + GA + GB + GC To GA + GB + GC + GD
    NamingArray(i) = "GD " & CStr(i - GA - GB - GC)
Next i

    Now to answer your question: Is there a better way to do this? Yes. But this works fine, and though it isn't pretty, it isn't inefficient in any way.