excel vba 填充数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6383337/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
excel vba filling array
提问by user366121
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?
每个参与者都应该这样命名：{GA1, GA2, ..., GD2} 我想按照这个顺序把它写入一个数组，然后用这个数组用名字填充单元格，但我想出的只有四个-loops 将其写入数组，但也失败了。有一个更好的方法吗？
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
回答by Jean-Fran?ois Corbett
I can see three reasons why your code isn't behaving like you expect.
我可以看到为什么您的代码不像您期望的那样运行的三个原因。
First, the variables you use as indices in your For ... Nextloops are inconsistent. In this loop, for instance, you increment kbut use ito index into NamingArray. Note that istill has the value GA+1left over from the first loop.
首先，您在For ... Next循环中用作索引的变量不一致。例如，在这个循环中，您递增k但用于i索引到NamingArray. 请注意，i仍然具有GA+1第一个循环遗留的值。
For k = GA + GB To GA + GB + GC
NamingArray(i) = "GC " & CStr(k)
Next k
Just use ifor all your loops. No need to use a different letter every time.
只需i用于所有循环。无需每次都使用不同的字母。
Second, you try to access element 0 of NamingArray, which doesn't exist.
其次，您尝试访问NamingArray不存在的元素 0 。
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:
第三，您的索引完全混乱。例如，NamingArray(GA)将在第一个循环结束时写入，然后在第二个循环开始时覆盖。这发生在你所有的循环中；他们的“管辖权”重叠（对不起，我是加拿大人）。我已经在下面更正了这个（以及所有其他错误）。这有效：
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.
现在回答你的问题：有没有更好的方法来做到这一点？是的。但这工作得很好，虽然它不漂亮，但它在任何方面都不是低效的。
回答by Dick Kusleika
Name your textboxes tbxGA, tbxGB, tbxGC, and tbxGD, and use this code to write to a range.
将您的文本框命名为 tbxGA、tbxGB、tbxGC 和 tbxGD，并使用此代码写入一个范围。
Private Sub cmdWrite_Click()
Dim i As Long, j As Long
Dim ctl As Control
Dim lCnt As Long
Dim aOutput() As String
Dim lTotal As Long
For i = 65 To 68
Set ctl = Me.Controls("tbxG" & Chr$(i))
lTotal = lTotal + Val(ctl.Text)
Next i
ReDim aOutput(1 To lTotal, 1 To 1)
For i = 65 To 68
Set ctl = Me.Controls("tbxG" & Chr$(i))
For j = 1 To Val(ctl.Text)
lCnt = lCnt + 1
aOutput(lCnt, 1) = "G" & Chr$(i) & j
Next j
Next i
Sheet1.Range("A1").Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput
Unload Me
End Sub
回答by Wudang
Why bother with an array? Declare an int as a cursor
为什么要麻烦数组？将 int 声明为游标
Assuming you want them across row 1
假设您希望它们跨第 1 行
dim col as integer
dim Acount as integer (etc)
'get Acount, Bcount etc from form
for col 1 =1 to Acount
cells(1,col).value = "GA" & col
next
for col =1 Acount to Bcount-1
cells(1,col).value = "GB" & col
next
etc
等等
回答by Mike Woodhouse
If the list isn't going to change, then your array might be simply declared something like this:
如果列表不会改变，那么您的数组可能会简单地声明如下：
Dim participantNames
participantNames = Array("GA1","GA2","GA3","GA4","GA5","GB1","GB2","GB3","GB4","GB5","GB6","GC1","GD1","GD2")
If the list of letters and counts might vary, then you probably need a function, something like:
如果字母和计数的列表可能会有所不同，那么您可能需要一个函数，例如：
Option Explicit
Public Function GroupIDs(grpNames, grpCounts) As Variant
Dim grpIndex
Dim countIndex
Dim output As New Collection
For grpIndex = LBound(grpNames) To UBound(grpNames)
For countIndex = 1 To grpCounts(grpIndex)
output.Add "G" & grpNames(grpIndex) & countIndex
Next
Next
ReDim outputArray(1 To output.Count)
For countIndex = 1 To output.Count
outputArray(countIndex) = output(countIndex)
Next
GroupIDs = outputArray
End Function
... which you might call with:
...你可能会打电话给：
GroupIds(Array("A", "B", "C", "D"),Array(5, 6, 1, 2))
