Final Score

Intermediate Exercise

Statement

Create an algorithm to access a student's final score. Consider that the final score is a weighted average and that within the semester there are three exams weighting 3, 5 and 7 respectively. Once the algorithm is done, create a macro to display an interface with an Inputbox to insert the student's exam scores and to show if the student was approved or not. Make the macro display an alert if the input is not numeric and end if the input is blank.

Suggested Concepts:

Dim
If Else
Do Loop
InputBox - Assigning Values
Msgbox - Running the Code
Function
IsNumeric - Function that returns True if the value is numeric.
Format - Returns a variant (string) containing an expression formatted according to instructions.
```
Format(10.5, "000.00") 'Returns 010.50
Format("0,424", "Percent") 'Returns 42.40%
```

Remember that in most cases there is more than one way to solve the exercise.

Hint: to call a function in one Sub but write its name.

Solved Exercise

Exercise Solution

First, create four variables, one for each exam and one for the final score.
```
Dim X As Variant
Dim Y As Variant
Dim Z As Variant
Dim Result As Double
```

Assign each exam variable to an Inputbox within a Do Loop until the input is higher than ten or is lower than zero. Make a conditional to display an alert MsgBox in the case of the input been not numeric or higher than ten or lower than zero

Do
    X = InputBox("Enter Test 1 score", "SuperExcelVBA")
        If Not IsNumeric(X) Or X > 10 Or X < 0 Then
            If X = vbNullString Then
                Exit Sub
            End If
            MsgBox "Invalid input! Number greater than 10 or smaller than 0 or not numeric", vbCritical, "SuperExcelVBA"
        End If
Loop Until X <= 10 And X >= 0

Do
    Y = InputBox("Enter Test 2 score", "SuperExcelVBA")
    If Not IsNumeric(Y) Or Y > 10 Or Y < 0 Then
        If Y = vbNullString Then
            Exit Sub
        End If
        MsgBox "Invalid input! Number greater than 10 or smaller than 0 or not numeric", vbCritical, "SuperExcelVBA"
    End If
Loop Until Y <= 10 And Y >= 0

Do
    Z = InputBox("Enter Test 3 score", "SuperExcelVBA")
    If Not IsNumeric(Y) Or Z > 10 Or Z < 0 Then
        If Z = vbNullString Then
            Exit Sub
        End If
        MsgBox "Invalid input! Number greater than 10 or smaller than 0 or not numeric", vbCritical, "SuperExcelVBA"
    End If

Finally, access the final score and display in a MsgBox if the student was approved or not

Result = ((X * 3) + (Y * 5) + (Z * 7)) / 15

If Result >= 5 Then
    MsgBox "Student was approved with final grade " & Format(Result, "0.00"), , "SuperExcelVBA"
Else
    MsgBox "Student failed and obtained final grade " & Format(Result, "0.00"), , "SuperExcelVBA"
End If

Consolidated Answer

    Sub Solution()

    Dim X As Variant
    Dim Y As Variant
    Dim Z As Variant
    Dim Result As Double

    Do
        X = InputBox("Enter Test 1 score", "SuperExcelVBA")
            If Not IsNumeric(X) Or X > 10 Or X < 0 Then
                If X = vbNullString Then
                    Exit Sub
                End If
                MsgBox "Invalid input! Number greater than 10 or smaller than 0 or not numeric", vbCritical, "SuperExcelVBA"
            End If
    Loop Until X <= 10 And X >= 0

    Do
        Y = InputBox("Enter Test 2 score", "SuperExcelVBA")
        If Not IsNumeric(Y) Or Y > 10 Or Y < 0 Then
            If Y = vbNullString Then
                Exit Sub
            End If
            MsgBox "Invalid input! Number greater than 10 or smaller than 0 or not numeric", vbCritical, "SuperExcelVBA"
        End If
    Loop Until Y <= 10 And Y >= 0

    Do
        Z = InputBox("Enter Test 3 score", "SuperExcelVBA")
        If Not IsNumeric(Y) Or Z > 10 Or Z < 0 Then
            If Z = vbNullString Then
                Exit Sub
            End If
            MsgBox "Invalid input! Number greater than 10 or smaller than 0 or not numeric", vbCritical, "SuperExcelVBA"
        End If
    Loop Until Z <= 10 And Z >= 0

    Result = FiNaLsCoRe(X, Y, Z)

        If Result >= 5 Then
            MsgBox "Student was approved with final grade " & Format(Result, "0.00"), , "SuperExcelVBA"
        Else
            MsgBox "Student failed and obtained final grade " & Format(Result, "0.00"), , "SuperExcelVBA"
        End If
    End Sub

    Function FiNaLsCoRe(X As Variant, Y As Variant, Z As Variant)
        FiNaLsCoRe = ((X * 3) + (Y * 5) + (Z * 7)) / 15
    End Function

Show Answer

Download exercise Download answer

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

INTRODUCTION

BASIC

INTERMEDIATE

ADVANCED