Sample Project in VBA

Project in Excel using macro

How to enter data and calculate grades automatically

As soon as the Excel macro is run the headers appear.
Then the user gets an inputbox to enter the name of the student.
After entering the name, the user is asked to enter the marks of the student in 5 subjects of 100 marks each using the ‘for’ loop.
Applying standard calculation code and ‘if’ function the total, percentage and grade of the student is calculated.
If the student achieves an ‘A’ grade, this is formatted to highlight his achievement in red color. In fact, a custom form has been created for the user in Excel to automate grade calculations.
The entire code has been reproduced below.
Watch the video below to see the macro in action:

Macro Code: Here’s the revised simpler code.

Sub calculating_grades()

Range(“A3”).Value = “Name”
Range(“B3”).Value = “Marks1”
Range(“C3”).Value = “Marks2”
Range(“D3”).Value = “Marks3”
Range(“E3”).Value = “Marks4”
Range(“F3”).Value = “Marks5”
Range(“G3”).Value = “Total”
Range(“H3”).Value = “Percentage”
Range(“I3”).Value = “Grade”
Range(“A3:I3”).Font.Bold = True
Range(“A3:I3”).Font.ColorIndex = 5
Range(“A3:I3”).Interior.ColorIndex = 6

Dim erow As Long
Dim counter As Integer
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

question = Application.InputBox(“Do you wish to enter data? Type ‘n’to end program or ‘y’ to continue”, “Question”)

If question = “n” Or question = “no” Then
MsgBox “Bye!”
Exit Sub
StudentName = Application.InputBox(“Enter the student’s name”, “Student’s Name”)
Cells(erow, 1).Value = StudentName
‘assume 5 subjects
‘initialize marks, Total
marks = 0
Total = 0
For counter = 2 To 6
marks = Application.InputBox(“Enter the student’s marks in the 5 subjects”, “Marks”)
Cells(erow, counter).Value = marks
MsgBox “You entered marks” & counter – 1 & ” ” & 6 – counter & ” to go”
Total = Total + marks
Cells(erow, 7).Value = Total
Dim percentage As Single
percentage = 0
percentage = Total / 5
Cells(erow, 8).Value = percentage
If percentage >= 90 Then
Grade = “A”
Cells(erow, 8).Font.Bold = True
Cells(erow, 9).Font.ColorIndex = 5
Cells(erow, 9).Interior.ColorIndex = 6
ElseIf percentage >= 80 Then
Grade = “B”
ElseIf percentage >= 70 Then
Grade = “C”
ElseIf percentage >= 60 Then
Grade = “D”
Grade = “Work Harder!”
End If
Cells(erow, 9).Value = Grade
Next counter
End If
End Sub

7 thoughts on “Sample Project in VBA

  1. sir,

    I wanted to automate a web application. I have coded the steps, but after the web application is launched, the connection of my vba object is lost with IE. I think the application requires a session with username, which is took by default as user launches the web app, but here the web app is launched via vba, so no user is refered to session. Please help me with this.

    1. Dim objIE As InternetExplorer ‘special object variable representing the IE browser
      Dim aEle As HTMLLinkElement ‘special object variable for an (link) element
      Dim y As Integer ‘integer variable we’ll use as a counter
      Dim result As String ‘string variable that will hold our result link

      ‘initiating a new instance of Internet Explorer and asigning it to objIE
      Set objIE = New InternetExplorer

      ‘make IE browser visible (False would allow IE to run in the background)
      objIE.Visible = True

      ‘navigate IE to this web page (a pretty neat search engine really)
      objIE.navigate “http://iscls3apps/Timesheet/TimesheetInformation/ReportsView”

      ‘From here the connection of objIE gets lost. and an error appears on the screen.


      ‘wait here a few seconds while the browser is busy
      Do While objIE.Busy = True Or objIE.readyState 4: DoEvents: Loop

      ‘in the search box put cell “A2” value, the word “in” and cell “C1” value
      objIE.document.getElementById(“txtFromDate”).Value = _
      Sheets(“Host”).Range(“A2″).Value & ” in ” & Sheets(“Host”).Range(“C1”).Value
      objIE.document.getElementById(“txtToDate”).Value = _
      Sheets(“Host”).Range(“A2″).Value & ” in ” & Sheets(“Host”).Range(“C2”).Value
      objIE.document.getElementById(“ddlStatus”).Value = _
      Sheets(“Host”).Range(“A2″).Value & ” in ” & Sheets(“Host”).Range(“C3”).Value
      objIE.document.getElementById(“txtProjectCode”).Value = _
      Sheets(“Host”).Range(“A2″).Value & ” in ” & Sheets(“Host”).Range(“C4”).Value
      objIE.document.getElementById(“txtSubmitterEmpNo”).Value = _
      Sheets(“Host”).Range(“A2″).Value & ” in ” & Sheets(“Host”).Range(“C5”).Value
      objIE.document.getElementById(“txtAuthorizerEmpNo”).Value = _
      Sheets(“Host”).Range(“A2″).Value & ” in ” & Sheets(“Sheet1”).Range(“C6”).Value

      ‘click the ‘go’ button

  2. Sir, this is an intranet application, running on private server. the link opens up, but after it, the connection is lost from objIE object.

  3. Hello Sir,

    I wanted to reach you out for a query on VBA .
    Wasnt sure where to post it, that why posting it here.
    Kindly share how to reach to you.


  4. Thank you, I have recently been looking for information about this topic
    for a while and yours is the best I have discovered till now.
    However, what in regards to the bottom line? Are you sure
    concerning the source?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.