VBA dictionary concept problem

[straw]

Hi all, I am following some sites to learn vba dictionary right now. However, some codes I can't understand. Please help to explain. Really appreciate your help. :bow:

Questions are inside "" after the code.

Please see codes below.


Sub ProcessData1()
Dim dict As Dictionary
Dim i As Integer
Dim targetRow As Integer
Dim name As String
Dim subject As String
Dim score As Double
Dim more As Boolean
Set dict = New Dictionary
more = True
i = 2
Worksheets("English").UsedRange.Clear
Worksheets("Physics").UsedRange.Clear
Worksheets("Biology").UsedRange.Clear
While more
name = Worksheets("Data").Cells(i, 1).Value
subject = Worksheets("Data").Cells(i, 3).Value
score = Worksheets("Data").Cells(i, 4).Value
If dict.Exists(subject) Then
targetRow = dict.Item(subject) "At this time, dict.item(subject)=score or name, or both?"
Else
targetRow = 1 "Why does targetRow=1 here?"
End If
Worksheets(subject).Cells(targetRow, 1) = name
Worksheets(subject).Cells(targetRow, 2) = score
dict.Item(subject) = targetRow + 1
i = i + 1
If Len(Worksheets("Data").Cells(i, 1)) = 0 Then more = False "Does this code mean that go until the last filled row?"
Wend
End Sub

31080

[arnelgp]

the code somewhat is incomplete. how does the each element in the dict gets Added, that is missing.

[straw]

Thanks arnelgp! the code misses the part of create 3 sheets named "English", "Physics", "Biology". After creating these 3 sheets, it can run perfectly. I got the whole tutorial from a website and I believe that's all codes they provided.

As an example here's a table of raw exam data by subject:



Name
Date
Subject
Score


Alex
04/02/2009
Biology
60.00


Alex
06/03/2009
English
60.00


Angela
04/02/2009
Biology
47.00


Angela
06/03/2009
Physics
47.00


Bharat
02/02/2009
English
64.00


Bharat
04/03/2009
English
64.00


Christine
03/02/2009
Physics
52.00


Christine
05/03/2009
Physics
52.00


George
03/02/2009
Physics
71.00


George
05/03/2009
English
71.00


Gilbert
03/02/2009
Physics
14.00


Gilbert
05/03/2009
Physics
14.00


Jane
02/02/2009
English
56.00


Jane
04/03/2009
Biology
56.00


Peter
02/02/2009
English
45.00


Peter
03/02/2009
Physics
52.00


Peter
04/03/2009
English
45.00


Peter
05/03/2009
Physics
52.00


The VBA code to do the processing looks like this:


Sub ProcessData1()
Dim dict As Dictionary
Dim i As Integer
Dim targetRow As Integer
Dim name As String
Dim subject As String
Dim score As Double
Dim more As Boolean
Set dict = New Dictionary
more = True
i = 2
Worksheets("English").UsedRange.Clear
Worksheets("Physics").UsedRange.Clear
Worksheets("Biology").UsedRange.Clear
While more
name = Worksheets("Data").Cells(i, 1).Value
subject = Worksheets("Data").Cells(i, 3).Value
score = Worksheets("Data").Cells(i, 4).Value
If dict.Exists(subject) Then
targetRow = dict.Item(subject)
Else
targetRow = 1
End If
Worksheets(subject).Cells(targetRow, 1) = name
Worksheets(subject).Cells(targetRow, 2) = score
dict.Item(subject) = targetRow + 1
i = i + 1
If Len(Worksheets("Data").Cells(i, 1)) = 0 Then more = False
Wend
End Sub


Running this allows us to process the data and produce a sheet that looks like this:



Alex
60


Bharat
64


Bharat
64


George
71


Jane
56


Peter
45


Peter
45




Thanks again!

[p45cal]

how does the each element in the dict gets Addedwithout actually opening the file to check, I think on this line:
dict.Item(subject) = targetRow + 1
(I think it's called implicit addition)

[arnelgp]

:thumb
you are correct!