Excel VBA填充数组 [英] 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

每个参与者都应这样命名:{GA1，GA2，...，GD2}我想以此顺序将其写入数组，然后使用该数组用名称填充单元格，但我想出了所有有四个for循环将其写入数组，但也失败了.有更好的方法吗?

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

推荐答案

我可以看到三个原因导致您的代码不符合您的预期.

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

首先，在For ... Next循环中用作索引的变量不一致.例如，在此循环中，您递增k，但使用i索引到NamingArray.请注意，i仍具有第一个循环剩余的值GA+1.

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

只需对所有循环使用i.无需每次都使用不同的字母.

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

第二，您尝试访问NamingArray的元素0，该元素不存在.

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

第三，更一般地说，您的索引被完全弄乱了.例如，NamingArray(GA)将在第一个循环结束时写入，然后在第二个循环开始时覆盖.这发生在所有循环中；他们的管辖权"重叠(对不起，我是加拿大人).我已更正了此错误(以及所有其他错误).这有效:

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.

这篇关于Excel VBA填充数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！