Tag: vba

Getting last name and first name from full name with a delimited comma

Rather than using the “text to columns” procedure in excel, if you want to use a function to separate the last name and the first name for simplicity of use and the ability to copy the functionality anywhere, here are two functions that are written for you.

I wrote these functions because CANVAS LMS does not have separate columns for first and last names in the grade book.   However, they do have an option of showing the full name delimited by a comma.  This is preserved when you export the GradeBook.

Figure 1: Display name as separated by commas.

To use them, press Alt-F11.  It will open up the Microsoft Visual Basic for Applications window.  Choose Insert>Module.  It will show up as Module1 by default in the VBA Project window.  Good to rename the module to say “LastFirstNameBreak” using the Properties Window.  Cut and paste the two functions in the module, and save your excel file.  You will need to save the excel file as an .xslm file though.

Figure 2. Microsoft VBA Window.  Functions are shown below.

Function BreakLastName(FullName)
' This function separates the last name from the
' full name that is delimited by a comma
FullNameTrim = Trim(FullName)
leng = Len(FullNameTrim)
' Loop checks where the comma is
For i = 1 To leng
If Mid(FullNameTrim, i, 1) = "," Then
ival = i
Exit For
End If
Next i
BreakLastName = Left(FullNameTrim, ival - 1)
End Function
Function BreakFirstName(FullName)
' This function separates the first name from the
' full name that is delimited by a comma
FullNameTrim = Trim(FullName)
leng = Len(FullNameTrim)
For i = 1 To leng
If Mid(FullNameTrim, i, 1) = "," Then
ival = i
Exit For
End If
Next i
BreakFirstName = Right(FullNameTrim, leng - ival - 1)
End Function

To use the functions, just use them like any other Excel function.  BreakLastName separates the last name, while BreakFirstName separates the first name.

Figure 3. Using the functions in an Excel spreadsheet.

____________________________

This post is brought to you by

End of semester grading VBA module

In spite of learning management systems making assigning letter grades simpler, these systems still leave much to be desired when asked to incorporate extra credit or curving of an assessment grade.  To overcome this drawback, I download the grades to a excel spreadsheet and calculate the overall score of each student.  Based on the overall score, one needs to then assign a letter grade for the transcripts.  To assign 13 different plus/minus grading letters manually can be a good candidate for making a mistake in a large size class.  So I use a VBA code (how to access VBA editor) to assign letter grades.

INPUTS AND OUTPUTS
c1 = overall percentage score
fungrade = letter grade

CITATION
Autar Kaw, End of semester grading VBA module, last retrieved at https://autarkaw.wordpress.com/2015/12/21/end-of-semester-grading-vba-module/, December 21, 2015.

FUNCTION 
Function fungrade (c1 As Integer) As String
Dim gra As String
Select Case c1
Case Is >= 98
gra = “A+”
Case Is >= 90
gra = “A”
Case Is >= 86
gra = “A-”
Case Is >= 83
gra = “B+”
Case Is >= 80
gra = “B”
Case Is >= 76
gra = “B-”
Case Is >= 73
gra = “C+”
Case Is >= 70
gra = “C”
Case Is >= 66
gra = “C-”
Case Is >= 63
gra = “D+”
Case Is >= 60
gra = “D”
Case Is >= 56
gra = “D-”
Case Is >= 0
gra = “F”
End Select
fungrade = gra
End Function

PS. You can modify the above given VBA code as needed.  If you want a less hard-coded version, you can modify by having two more inputs – 1) a vector of lowest limit of score for a particular letter grade, and 2) a corresponding vector of the same length of letter grades.

This post is brought to you by