vba 我们可以使用单个语句将字典项（数组）放入 Range 中吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14008866/
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
Can we put dictionary items(array) into a Range with a single statement?
提问by Arup Rakshit
Suppose i do have an array A1(6)=(45,25,,36,88),A2(6)=(14,25,11),A3(6)=(11,21,20,25,48).Now can we put those array values with the help of a single statement like single array assignment to a row,as here all the rows to a range of an Excel, Say here "C1:R3" range.
假设我有一个数组 A1(6)=(45,25,,36,88),A2(6)=(14,25,11),A3(6)=(11,21,20,25,48 ). 现在我们可以在单个语句的帮助下放置这些数组值，例如将单个数组赋值给一行，因为这里所有行都分配到 Excel 的范围，这里说“C1:R3”范围。
Dim R
R = Split(Join(A1, ",") & "," & Join(A2, ",") & "," & Join(A3, ","), ",")
Range("C5:T5").Value = R
Now can we do dictionary Items(which is an array) combine into a 1D array and assign back to a Range?
现在我们可以将字典项（它是一个数组）组合成一个一维数组并分配回一个范围吗？
For Each ChilID In ChildIDs
Redim ChildDetailArray(ArrIndex)
ChildMatchNum=objExcel1.Application.WorksheetFunction.Match(ChilID, ob3.Columns(1), 0)
ChildDetailArray=ob1.Range(ob1.Cells(ChildMatchNum,1),ob1.Cells(ChildMatchNum,ArrIndex+1)).Value
ChildDic.Add ChilID,ChildDetailArray '(ChildDetailArray is an array)
Next
EDIT1
编辑1
suppose a process#20 has 2 child processes say #12,#13. now i used a dictionary object Dic
Dic(12)=Arr(10,11,,,18) 'child details
Dic(13)=Arr(5,8,9,,,) ' child details
***Output:*** `1D array say ArrMerger()=(10,11,,,18,5,8,9,,,)`
The above For Loopis doing the same.now when the Loopwill be finished,i want those child details which are the item of Dic(12) and Dic(13) needs to be collected in an 1D array
上面For Loop是一样的。现在当Loop完成时，我希望将 Dic(12) 和 Dic(13) 项的那些子详细信息需要收集在一维数组中
UPDATE
更新
strJoin = ","
For ChildKey In ChildDic.Keys
strJoin=Join(ChildDic(ChildKey),",") & strJoin
Next
Thanks
谢谢
回答by bonCodigo
Can we put dictionary items(array) into a Range with a single statement??YES, You can get all dictionary items into a range.
我们可以用单个语句将字典项（数组）放入 Range 中吗？是的，您可以将所有字典项放入一个范围内。
Try this code and explain clearly/ comment any changes you require:
试试这个代码并清楚地解释/评论您需要的任何更改：
Code:
代码：
Option Explicit
Sub getMerged1DItems()
Dim d As Object, d2 As Object
Dim vArr As Variant
Dim vArr2 As Variant
Dim strJoin As String
Set d = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
'-- assume you have items in your dictionary
d.Add "Names", 1
d.Add "Titles", 2
d.Add "Jobs", 3
d.Add "Education", 4
d.Add "Experience", 5
'-- add dictionary items into an 1D array
vArr = d.Keys
'-- add 1D arryas into d2 dictionary as items
d2("v" & 1) = vArr
d2("v" & 2) = vArr
'-- join multiple 1D array items into one string delimitted by comma
strJoin = Join(d2("v" & 1), ", ") & "," & Join(d2("v" & 2), ", ")
'-- split the string by comma delimiter
vArr2 = Split(strJoin, ",")
'-- output to sheet using first 1D Array
Sheets(1).Range("B2").Resize(1, _
UBound(Application.Transpose(vArr))) = vArr
'-- output to sheet using dictionary
Sheets(1).Range("B4").Resize(1, _
UBound(Application.Transpose(d.Keys))) = d.Keys
'output to sheet using mergeed 1D array
Sheets(1).Range("B7").Resize(1, _
UBound(Application.Transpose(vArr2))) = vArr2
Set d2 = Nothing
Set d = Nothing
End Sub
Output:
输出：
