Scripting dictionary vba

' clsName Class Module Code
Public Name As String
Public maths As Long
Public science As Long
Public lang As Long
Public history As Long
 
 


' Standard module Code
Sub MakeDict()


    Dim dict As Dictionary
    
    ' Read the data to the dictionary
    Set dict = MultipleValues
    
    
    ' Write the Dictionary contents to a worksheet
    WriteToWorksheet dict, ThisWorkbook.Worksheets("Output")


End Sub


Private Function MultipleValues() As Dictionary


    ' Declare and create the Dictionary
    Dim dict As New Dictionary
    
    ' Get the worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("data")
    
    ' Get the range of all the adjacent data using CurrentRegion
    Dim rg As Range
    Set rg = sh.Range("A1").CurrentRegion


    Dim oNam As clsName, i As Long
    ' read through the data
    For i = 2 To rg.Rows.Count
    
        
        Set oNam = New clsName
        
        ' Set the values
        oNam.Name = rg.Cells(i, 1).Value
        oNam.Maths = rg.Cells(i, 2).Value
        oNam.science = rg.Cells(i, 3).Value
        oNam.lang = rg.cells(i, 4).Value
         oNam.history =rg.cells(i, 5).Value
        
       
        dict.Add oNam.Name, oNam
            
    Next i
    
    ' Return the dictionary to the Main sub
    Set MultipleValues = dict


End Function


' Write the Dictionary contents  to a worksheet
Private Sub WriteToWorksheet(dict As Dictionary, sh As Worksheet)
    
    ' Delete all existing data from the worksheet
    sh.Cells.ClearContents
    
    Dim row As Long
    row = 1
    
    Dim key As Variant, oNam As clsName
    ' Read through the dictionary
    For Each key In dict.Keys
        Set oNam = dict(key)
        With oNam
            ' Write out the values
            sh.Cells(row, 1).Value = .Name
            sh.Cells(row, 2).Value = .Maths
            sh.Cells(row, 3).Value = .science
            sh.Cells(row, 4).Value = .lang
            sh.Cells(row, 5).Value = .history
            row = row + 1
        End With
        
    Next key
End Sub