VBA - runtime error

[GSXdan]

way too many problems.....

here is a little bit of code:

If Q1 <= Q2 And Q1 <= Q3 And Q1 <= Q4 And Q1 <= Q5 Then
Low = Q1
ElseIf Q2 <= Q1 And Q2 <= Q3 And Q2 <= Q4 And Q2 <= Q5 Then
Low = Q2
ElseIf Q3 <= Q1 And Q3 <= Q2 And Q3 <= Q4 And Q3 <= Q5 Then
Low = Q3
ElseIf Q4 <= Q1 And Q4 <= Q2 And Q4 <= Q3 And Q4 <= Q5 Then
Low = Q4
Else
Low = Q5
End If

If Low = Q1 Then
QuizScore = ((Q2 + Q3 + Q4 + Q5) / 100) * 0.3
ElseIf Low = Q2 Then
QuizScore = ((Q1 + Q3 + Q4 + Q5) / 100) * 0.3
ElseIf Low = Q3 Then
QuizScore = ((Q1 + Q2 + Q4 + Q5) / 100) * 0.3
ElseIf Low = Q4 Then
QuizScore = ((Q1 + Q2 + Q3 + Q5) / 100) * 0.3
Else
QuizScore = ((Q1 + Q2 + Q3 + Q4) / 100) * 0.3
End If

(low = Q2 which is 18) the problem is that in the second if statement, the QuizScore variable isnt being assigned anything when it is run. i can put my mouse over the Low and it says that it is Q2, and it says that Q2 = 18, and in the equation, all it says that (Q1 + Q3 + Q4 + Q5) / 100 = .79. it wont multiply it by 0.3 and it wont assign anything to QuizScore. what am i doing wrong?

thanks ^dan

[TwoRails]

Hi GSXdan,

Is the above an actual cut-n-paste??

Also, I don't see a low = 0 initialization, is it in there?

TwoRails

[GSXdan]

yea it is a cut-n-paste, but i am not sure about this low = 0 initialization thing you are talking about. i havent learnt that yet, but where would i put it?

thanks ^dan

[TwoRails]

HI GSXdan ,

It's been a while, so my syntax won't be correct, so it'll be up to you to correct things as / if needed. I'll also use the"!" for a comment, as that's what I'm use to.

Try:
! good housekeeping, always init vars:
! set it to something totally off, so an error can be detected:
Low = 888

! use more "( )"
If (Q1 <= Q2) And (Q1 <= Q3) And (Q1 <= Q4) And (Q1 <= Q5)

! repeat more "( )" below (I didn't to save time...)
Then
Low = Q1
ElseIf Q2 <= Q1 And Q2 <= Q3 And Q2 <= Q4 And Q2 <= Q5 Then
Low = Q2
ElseIf Q3 <= Q1 And Q3 <= Q2 And Q3 <= Q4 And Q3 <= Q5 Then
Low = Q3
ElseIf Q4 <= Q1 And Q4 <= Q2 And Q4 <= Q3 And Q4 <= Q5 Then
Low = Q4

! change to another elseif for debugging:
ElseIf Q5 <= Q1 And Q5 <= Q2 And Q5 <= Q3 And Q54 <= Q4 then
Low = Q5
End If

! test for error:
If Low = 888 Then do error routine 1

If Low = Q1 Then
! again, use more "( )" - you add the rest:
QuizScore = ( ( (Q2 + Q3 + Q4 + Q5) / 100) * 0.3)
ElseIf Low = Q2 Then
QuizScore = ((Q1 + Q3 + Q4 + Q5) / 100) * 0.3
ElseIf Low = Q3 Then
QuizScore = ((Q1 + Q2 + Q4 + Q5) / 100) * 0.3
ElseIf Low = Q4 Then
QuizScore = ((Q1 + Q2 + Q3 + Q5) / 100) * 0.3
Else
QuizScore = ((Q1 + Q2 + Q3 + Q4) / 100) * 0.3
End If

! test again, using different rtn to isolate:
If Low = 888 Then do error routine 2


HTH

TwoRails

[doctorgonzo]

Have you tried stepping through the code one line at a time to see what happens?

[GSXdan]

yea i stepped through it and it worked fine until here:

If Low = Q1 Then
QuizScore = ((Q2 + Q3 + Q4 + Q5) / 100) * 0.3
ElseIf Low = Q2 Then
QuizScore = ((Q1 + Q3 + Q4 + Q5) / 100) * 0.3

it went to ElseIf Low = Q2 and did the equation, and it showed that ((Q1 + Q3 + Q4 + Q5) / 100) = .79, but it didnt finish the equation or assign anything to QuizScore. after that nothing is right because it mostly depends on the QuizScore and QuizSum variables, which stay at 0.

thanks ^dan

[doctorgonzo]

I ran exactly what you wrote and it works just fine.

Did you properly declare the variables? Q1, Q2, Q3, Q4, Q5 should be Integers, and QuizScore should be Single. If you set QuizScore to be an Integer as well, it wouldn't work and you would probably get a runtime error.

[GSXdan]

ok i did what you said and it worked!! yea i had a couple variables as integers instead of single, so that was causing the problems. Thanks a lot doctorgonzo and TwoRails!!

^dan