Wednesday, 27 August 2014

Code for marking, Part 3

A very quick post with no images because I've got a lot on at the moment -

Here's some code to perform some basic maths on exam marks as per my previous 2 posts. Note that it will work with values split with separators (such as "|") in the case of answers to multiple questions.

I could make a UserForm to make it pretty, but for now Input Boxes will do the trick just as well. (You might like to run this at the same time as my highlighting code if you want to see the changes. You can always change back to a normal font format later)

Sub Test()
    Dim rCell As Range
    Dim rRange As Range
    Dim sSeparatorToUse As String
    Dim sOperatorToUse As String
    Dim dInputValue As Double
    
    If TypeName(Selection) <> "Range" Then Exit Sub
    
    sSeparatorToUse = InputBox("Input an separator")
    
    If sSeparatorToUse = "" Then Exit Sub
    
    sOperatorToUse = InputBox("Input an operator ( + - * / ) ")
    
    If sOperatorToUse = "" Then Exit Sub
    
    dInputValue = InputBox("Input a value")
    
    If Not IsNumeric(dInputValue) Then Exit Sub
    
    Set rRange = Selection
    
    For Each rCell In rRange.Cells
        
        Call PerfomMathOnSplitCellValues(rCell, sSeparatorToUse, sOperatorToUse, dInputValue)
        
    Next rCell
    
    Set rRange = Nothing
    
End Sub

Sub PerfomMathOnSplitCellValues(rCellMath As Range, sSeparator As String, sOperator As String, dValue As Double)
    Dim tmp As Variant
    Dim lCount As Long
    Dim sTmpString As String
    
    On Error Resume Next
    
    With rCellMath
        
        tmp = Split(.Text, sSeparator)
        
        For lCount = 0 To UBound(tmp)
            
            If lCount = 0 Then
                
                sTmpString = ""
                
                Select Case sOperator
                    
                    Case Is = "+"
                    sTmpString = CStr(Val(tmp(lCount)) + dValue)
                    
                    Case Is = "-"
                    sTmpString = CStr(Val(tmp(lCount)) - dValue)
                    
                    Case Is = "*"
                    sTmpString = CStr(Val(tmp(lCount)) * dValue)
                    
                    Case Is = "?"
                    sTmpString = CStr(Val(tmp(lCount)) / dValue)
                    
                End Select
                
            Else
                
                Select Case sOperator
                    
                    Case Is = "+"
                    sTmpString = sTmpString & sSeparator & CStr(Val(tmp(lCount)) + dValue)
                    
                    Case Is = "-"
                    sTmpString = sTmpString & sSeparator & CStr(Val(tmp(lCount)) - dValue)
                    
                    Case Is = "*"
                    sTmpString = sTmpString & sSeparator & CStr(Val(tmp(lCount)) * dValue)
                    
                    Case Is = "/"
                    sTmpString = sTmpString & sSeparator & CStr(Val(tmp(lCount)) / dValue)
                    
                End Select
                
            End If
            
        Next lCount
        
        rCellMath.Value = sTmpString
        
    End With
    
    On Error GoTo 0
End Sub


Some useful (?) stuff and maybe unusual (?) stuff coming up. Give me a week or so.

See you next time.

No comments :

Post a comment