PDA

View Full Version : Please help me to write the code for this Excel-VBA problem?



lush888222
07-09-2008, 09:50 PM
Write a subroutine which will be executed by clicking a command button which will ask the user to enter a series of scores (0-100) through an input box then calculate the average and assign a letter grade based on a 90-80-70-60 scale. The output should be through a message box.
:think:
can you at least tell me the procedure to do this??
Thank you so much.

JimmyTheHand
07-09-2008, 09:55 PM
Homework? Sounds like one...

lush888222
07-09-2008, 10:14 PM
Thanks so much for trying to help me, yes this is a homework problem, and I don't know how to solve this. This is a very simple problem, 'cause this is for a beginner's class. This is the only info given to me too. I have to write a code on VBA -excel on how toput a command button on the excel screen, and when you click it, it ask for the data range(range of values, assumes 1-100), on a excel sheet, assume on a certain column, and calculate the average of the values, and then assign a letter grade according to the average.
the only part I know is, assigning a letter grade, I hope the code for that is:

Function Grade(Percent)
'
' function to assign a letter grade based on percent
'
If Percent > 90 Then
Grade = "A"
ElseIf Percent > 80 Then
Grade = "B"
ElseIf Percent > 70 Then
Grade = "C"
ElseIf Percent > 60 Then
Grade = "C"
Else
Grade = "D"
End If
End Function

but the thing is this is not a function, but a subroutine so i have to change this.

JimmyTheHand
07-09-2008, 10:42 PM
Since this is a homework, I won't give you code, but will try to guide you nevertheless.

IMO the function is just fine as it is. You can call it from the subroutine.
And the commandbutton is of secondary importance right now. First you need a subroutine that works OK, and it's only the 2nd part of the job that you assign it to a button.

I suggest that you study the function MsgBox first, in the built in help.
It is for sending messages to the user. Try outputting strings, numbers, etc. When you are comfortable with outputting constant values, try to output the return value of your Function Grade(Percent). Of course, you need to supply the function with an argument.

Edit:
I just noticed that in the function there are two C grades. Correct it before doing anything else.
And please post back any code you write, so that we can check and comment it.
Use the VBA tags around your code, it looks nice that way.

lush888222
07-09-2008, 11:15 PM
is the code like this:
Option Explicit
Sub AverageCalc()
Dim average As Single
Range().Value = average.Value
InputBox = Range().Value
MsgBox = average.Value
If average.Value > 90 Then
Grade = "A"
ElseIf average.Value > 80 Then
Grade = "B"
ElseIf average.Value > 70 Then
Grade = "C"
ElseIf average.Value > 60 Then
Grade = "C"
Else
Grade = "D"
End If
MsgBox = Grade
End Sub

Edited by Aussiebear: When supplying code, please use the VBA Tags button to enclose your code.... it makes the code so much easier to read.

JimmyTheHand
07-10-2008, 12:27 AM
Forget about everything else for the moment, and concentrate on the usage of MsgBox function.
To display a constant 1 via messagebox, you need to use this code:
Msgbox 1
Now please try to answer these questions:
1. How do you display the following string: 'abcd'?
2. How do you display the sum of the integer variables A and B?
3. How do you display the integer part of 4.5? (Hint: use the INT function.)
4. How do you display the return value of your Grade(Percent) function?