Overflow Error

[Bishop]

I have the following code:

Dim varSum As Integer, varCount As Integer
Dim ScoringAve As Double
Dim i As Integer
For i = 1 To 18
If Me.Controls(ScoreBox(i)).Text <> "" Then
varSum = varSum + Me.Controls(ScoreBox(i)).Text
varCount = varCount + 1
End If
Next
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?

 

[Patrick Molloy]

put a break on the line and check the values of the variables.

 

[Bishop]

I don't get the error when I step through it. Using random sample data I
ended up with:

30/9=3.3333333333333

another random sample yielded:

27/11=2.454545454545

Do I need to restrict the output? I will be assigning the value to a label
caption and will only need it accurate to one decimal place.

 

[norie]

Could it be because varCount is 0?

And could that have something to do with ScoreBox(i) ?

Is that an array or are you trying to refer to controls on a userform
or worksheet?

 

[Bishop]

Ok, I've tried everything. I can't figure out why this is giving me an
overflow error. I've tried every combination I can think of. Changing dim
datatypes, forcing datatypes... nothing works. I have the following code:

Dim varSum As Integer, varCount As Integer
Dim ScoringAve As Double
Dim i As Integer
For i = 1 To 18
If Me.Controls(ScoreBox(i)).Text <> "" Then
varSum = varSum + Me.Controls(ScoreBox(i)).Text
varCount = varCount + 1
End If
Next

.Cells(LastRow + 1, 30) = (CLng(varSum) / CLng(varCount))

How do I fix this?!

 

[Dave Peterson]

First, I would never use "as Integer". I'd always use "as Long". Same with "As
Single". I'd use "As Double".

Second, you never posted what was in those 18 textboxes. You could add:
Debug.print "i=" & i & " -- " & Me.Controls(ScoreBox(i)).Text
And copy from the immediate window and then paste into any followup message.

So without knowing what's in those textboxes, I'd suggest:

Dim varSum As Long, varCount As Long
Dim ScoringAve As Double
Dim i As Long
For i = 1 To 18
If Me.Controls(ScoreBox(i)).Text <> "" Then
varSum = varSum + Me.Controls(ScoreBox(i)).Text
varCount = varCount + 1
End If
Next
ScoringAve = cdbl(varSum) / cdbl(varCount)

But I didn't test any of it. I'm sure my values wouldn't match what you're
seeing.

 

[Bishop]

The only thing going in the textboxes are integers 1-5 (a rating like 5 stars
kind of thing)

 

[Bishop]

I think the problem is when all of the textboxes are empty. Your suggestion
works... but only if at least one of the textboxes has a value in it.

 

[EricG]

Things to check:

1. You are using the ".Text" attribute of your controls. Should you be
using ".Value" instead? What type of controls are they?
2. Put in a check for varCount = 0 before the line where you calculate
ScoringAve. Something like "If (varCount = 0) Then msgBox "OMG! varCount =
0!"
3. You're doing integer math all the way until the end of the calculation
for ScoringAve. Perhaps try converting the Integers to Doubles first, i.e.
cDbl(varSum) / cDbl(varCount)
4. Check to see if your controls have a Null value in them - that can screw
up most calculations.

HTH,

Eric

 

[Dave Peterson]

I'd check to see if the values in the textboxes are really numeric:

If Me.Controls(ScoreBox(i)).Text <> "" Then
if isnumeric(me.controls(Scorebox(i)).text then
varSum = varSum + Me.Controls(ScoreBox(i)).Text
varCount = varCount + 1
end if
End If

It could stop other typos, too.

 

[Dave Peterson]

In fact...

If Me.Controls(ScoreBox(i)).Text <> "" Then
if isnumeric(me.controls(Scorebox(i)).text then
varSum = varSum + cdbl(Me.Controls(ScoreBox(i)).Text)
varCount = varCount + 1
end if
End If

 

[keiji kounoike]

I might be wrong, but when all of the textboxes are emtpty, varCount
become 0. so, ScoringAve = (varSum / varCount) is equivalent to
ScoringAve = (varSum / 0 ). the result will end up with Overflow error.

Keiji

 

[Dave Peterson]

And adding Keiji's response:

if varcount = 0 then
'what should happen to scoringave
else
ScoringAve = varSum) / varCount
end if

I would keep the tests for numbers, too.