Excel VBA - 字典 - 存储和检索值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19987411/
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 - Dictionary - storing and retrieving values
提问by Krishna
I am working on below table and with help of Excel VBA - Dictionary - I am trying to capture all the details - 1) First step is to search in "Results Out" Column - if the value is "No" - then we need to read all the values with their appropriate header. 2) So for 2nd record - i.e., Name = XYZ - we need to store all the details. Based on No. of Subjects column - we need to store value of all the subjects and their corresponding marks - will be used for further calculation and generate the "Result" column.
我正在处理下表并借助 Excel VBA - 字典 - 我试图捕获所有详细信息 - 1）第一步是在“结果输出”列中搜索 - 如果值为“否” - 那么我们需要读取所有带有相应标题的值。2) 因此，对于第二条记录——即 Name = XYZ——我们需要存储所有详细信息。根据科目数列 - 我们需要存储所有科目的值及其相应的分数 - 将用于进一步计算并生成“结果”列。
I got it partially working - like I am able to capture details - but not able to store details of all the subject and their marks:
我让它部分工作 - 就像我能够捕捉细节 - 但无法存储所有主题及其标记的细节：
Sr. No. Results Out? Result Name Age No. of Subjects Subject Names Marks
1 Yes Pass ABC 21 3 Maths 10
Science 26
History 34
2 No XYZ 10 2 Maths 24
Science 36
Below is the code that I have used that is partially working:
下面是我使用的部分工作的代码：
Public Sub test_dict()
Dim dict As New Scripting.dictionary
Set dict = New dictionary
sSheetIndex = 1
intTargetRow = 2
Set objUsedRange = Worksheets.Item(3).UsedRange
For Iter = 1 To objUsedRange.Columns.Count
sCellName = objUsedRange.Cells(1, Iter)
sCellValue = objUsedRange.Cells(intTargetRow, Iter)
dict.Item(sCellName) = sCellValue
Next
For i = 0 To dict.Count - 1
s = dict.Items()(i)
Debug.Print dict.Keys()(i) & " " & dict.Items()(i)
Debug.Print s
Next i
End Sub
回答by Krishna
Resolved the issue with below code - had to use 2 seperate dictionaries:
使用以下代码解决了问题 - 必须使用 2 个单独的字典：
Public Sub test_dict()
Dim dict As New Scripting.dictionary
Set dict = New dictionary
sSheetIndex = 1
intTargetRow = 2
Set objUsedRange = Worksheets.Item(3).UsedRange
For Iter = 1 To objUsedRange.Columns.Count
sCellName = objUsedRange.Cells(1, Iter)
sCellValue = objUsedRange.Cells(intTargetRow, Iter)
dict.Item(sCellName) = sCellValue
If sCellName = "Subject Names" Then
Call test_dict_2
End If
Next
For i = 0 To dict.Count - 1
s = dict.Items()(i)
Debug.Print dict.Keys()(i) & " " & dict.Items()(i)
Debug.Print s
Next i
End Sub
Public Sub test_dict_2()
Dim dict_2 As New Scripting.dictionary
Set dict_2 = New dictionary
sSheetIndex = 1
intTargetRow = row_counter
Set objUsedRange = Worksheets.Item(3).UsedRange
For Iter = 1 To objUsedRange.Columns.Count
sHeader = objUsedRange.Cells(1, Iter)
sCellValue = objUsedRange.Cells(intTargetRow, Iter)
If sHeader = "No. of Subjects" Then
mv_cnt = sCellValue
End If
If sHeader = "Subject Names" Then
Dim a
a = Iter + mv_cnt
For Iter_2 = Iter To (a - 1)
sHeader = objUsedRange.Cells(1, Iter)
sCellName = objUsedRange.Cells(intTargetRow, Iter)
sCellValue = objUsedRange.Cells(intTargetRow, Iter + 1)
dict_2.Item(sCellName) = sCellValue
intTargetRow = intTargetRow + 1
Next
intTargetRow = row_counter
End If
Next
For i = 0 To dict_2.Count - 1
s = dict_2.Items()(i)
Debug.Print dict_2.Keys()(i) & " " & dict_2.Items()(i)
Debug.Print s
Next i
Set dict_2 = Nothing
End Sub
