A sample project using Excel VBA
We have created a sample project using Excel VBA. Although it appears to be useful only for teachers at first sight, it can really be used in a variety of domains through slight modifications.
We have demonstrated many ideas of Excel VBA including:
1. Variables
2. Inputbox
3. Do while loop
4. For loop
5. Nest IF function
6. Excellent use of Message Box
7. Different data types
8. Cells property
9. Formatting font and cells
You may also visit my website to find more more details: https://www.exceltrainingvideos.com/s...
Here's the latest revised code as on 28-March-2020:
Option Explicit
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
Range("A3:I3").Columns.AutoFit
Dim question As String, StudentName As String, Grade As String
Dim erow As Long, marks As Long, Total As Long, counter As Long
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' to end program or 'y' to continue", "Question")
If question = "n" Or question = "no" Or question = "False" 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 - 1 & " " & 5 - (counter - 1) & " 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 GREATER THAN OR EQUAL TO 90 Then
Grade = "A"
Cells(erow, 8).Font.Bold = True
Cells(erow, 9).Font.ColorIndex = 5
Cells(erow, 9).Interior.ColorIndex = 6
ElseIf percentage GREATER THAN OR EQUAL TO 80 Then
Grade = "B"
ElseIf percentage GREATER THAN OR EQUAL TO 70 Then
Grade = "C"
ElseIf percentage GREATER THAN OR EQUAL TO 60 Then
Grade = "D"
Else
Grade = "Work Harder!"
End If
Cells(erow, 9).Value = Grade
Next counter
Loop
End Sub
NOTE: Please replace 'GREATER THAN OR EQUAL TO' by the angular bracket and '='.
For more knowledge read the book Excel 2016 Power Programming with VBA: http://amzn.to/2kDP35V
If you are from India you can get this book here: http://amzn.to/2jzJGqU
In questa pagina del sito puoi guardare il video online A sample project using Excel VBA della durata di ore minuti seconda in buona qualità , che l'utente ha caricato Dinesh Kumar Takyar 12 maggio 2013, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 152,709 volte e gli è piaciuto 569 spettatori. Buona visione!
