Extract raw mark to UMS table from Edexcel Converter

[LosOjos]

This one is aimed at the Data Managers and Exams Officers amongst us mainly, though I'm sure if any of you techs shared this with yours they'd be happy!

 

I've been sitting on this script for a while but it dawned on me today others may also find it useful. Edexcel provide a spreadhseet which you can use to enter any raw mark for any of their units and get the UMS equivalent back (from this page: Understanding marks and grades | Pearson qualifications)

 

The problem is, it does this in a locked down spreadsheet, one raw mark at a time. Of course you could enter each raw mark and get the UMS to create yourself a conversion table, but being the lazy person I am, I wrote a VBA routine to do it for me!

 

Copy and paste this code in to a VBA module, then select the level, course and unit you want and the "Convert raw mark to UMS mark" option, then run the routine. You'll be left with a two column table in a new workbook you can use for your lookups wherever you need them.

 

Public Sub EdexcelExtractRawUMSTable()
   Dim x As Integer
   Dim EdexcelSheet
   Dim myBook
   Dim mySheet
   
   Set EdexcelSheet = ActiveWorkbook.ActiveSheet
   Set myBook = Application.Workbooks.Add()
   Set mySheet = myBook.Sheets(1)
   
   With mySheet.Range("$A$1")
       .Value = "RAW"
       .Offset(0, 1).Value = "UMS"
   
       For x = 1 To EdexcelSheet.Range("$I$14").Value
               EdexcelSheet.Range("$B$20").Value = x
               .Offset(x, 0).Value = x
               .Offset(x, 1).Value = EdexcelSheet.Range("$B$21").Value
       Next x
   
   End With
   
   Set myBook = Nothing
   Set mySheet = Nothing
   Set EdexcelSheet = Nothing
End Sub

[lobethrust]

The Spreadsheet supplied by Edexcel is PW protected (worksheets and VBA project). Do you know what the password is?

[LosOjos]

The Spreadsheet supplied by Edexcel is PW protected (worksheets and VBA project). Do you know what the password is?

 

I don't, but it doesn't matter, you can drop this code in to a new module in a new workbook, then open the Edexcel book, open VBA and hit run