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:
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

question = “y”
Do While question = “y”
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
question = Application.InputBox(“Do you wish to enter data? Type ‘n’end program or ‘y’ to continue”, “Question”)
If question = “n” Or question = “no” Then End
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 & ” ” & 5 – 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 Sub

Further reading:
Excel Macro Examples & Free Downloads

5 thoughts on “Sample Project in VBA

  1. Surendra singh


    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. Surendra singh

      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. Surendra singh

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


Leave a Reply

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