Mike French

asked on

# Overflow error in Excel

I am getting an overflow error with this code. Please help me understand why. When I comment out the variables j, k, & z I don't get the error. The data is wrong but i don't get the error. What is causing this?

```
Public Sub CalculateYTDCustomerSalesAndMargins()
Dim UniqueCustomers, Output, Claimbacks As Variant
Dim i, c, j, k, z As Double
UniqueCustomers = Range("UniqueCustomers")
Claimbacks = Range("ClaimBacks")
ReDim Output(1 To UBound(UniqueCustomers), 1 To 5)
j = 0 'Sales variable
k = 0 'Cost of sales variable
z = 0 'Claim backs variable
For c = 1 To UBound(UniqueCustomers)
For i = 1 To UBound(Claimbacks)
If UniqueCustomers(c, 2) = Claimbacks(i, 5) Then
j = j + Claimbacks(i, 11) * Claimbacks(i, 10)
k = k + Claimbacks(i, 12) * Claimbacks(i, 10)
z = z + Claimbacks(i, 22)
Output(c, 1) = j
Output(c, 2) = k
Output(c, 3) = 1 - k / j
Output(c, 4) = z
Output(c, 5) = 1 - (k - z) / j
End If
Next i
j = 0 'Sales variable
k = 0 'Cost of sales variable
z = 0 'Claim backs variable
Next c
Range("E2", Range("I" & UBound(UniqueCustomers) + 1)) = Output
End Sub
```

In line 4, all the variables except z will be Variants, so will take on the type at first usage.

At lines 11 and 12, j and k will become integers, which will cause that error if an attempt is made to set to any value greater than 32767.

Declare each variable with its type

Dim i As Double, c As Double, j As Double, k As Double, z As Double

At lines 11 and 12, j and k will become integers, which will cause that error if an attempt is made to set to any value greater than 32767.

Declare each variable with its type

Dim i As Double, c As Double, j As Double, k As Double, z As Double

ASKER

Graham,

I tried all this but nothing works. These are all small values that would not exceed 32767 in any case. However even with them set as a "double" data type I still get the error. For whatever reason, it is being caused by lines 27 and 29. I don't know why dividing these numbers is causing this. I have fixed the issue by inserting an "IF" statement that sets Output(c, 3 or 5) to "0" if "k" or "j" are "0". Additionally, "c" reaches 47 before it occurs? Why doesn't it do it before that? I can't see anything in the data that is different before the 47th occurrence?

I tried all this but nothing works. These are all small values that would not exceed 32767 in any case. However even with them set as a "double" data type I still get the error. For whatever reason, it is being caused by lines 27 and 29. I don't know why dividing these numbers is causing this. I have fixed the issue by inserting an "IF" statement that sets Output(c, 3 or 5) to "0" if "k" or "j" are "0". Additionally, "c" reaches 47 before it occurs? Why doesn't it do it before that? I can't see anything in the data that is different before the 47th occurrence?

If they are all small values, declare each of them as Integer.

What are the values on the right hand side of the equation when it fails?

Actually, I agree that it would help to provide a sample workbook (edited for simplicity and confidentiality)

What are the values on the right hand side of the equation when it fails?

Actually, I agree that it would help to provide a sample workbook (edited for simplicity and confidentiality)

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

Graham,

Sorry I haven't gotten back to you on this. Really busy time of year. anyway, the fix I referred to above is working and I don't have time right now to test your solution. I will test it though as soon as possible.

Thanks for all your help!

Sorry I haven't gotten back to you on this. Really busy time of year. anyway, the fix I referred to above is working and I don't have time right now to test your solution. I will test it though as soon as possible.

Thanks for all your help!

I couldn't tell whether it applies here, but I should have mentioned that there is a surprising feature (i.e. a bug) whereby an overflow error occurs unexpectedly. This code:

You can get around the problem in several ways:

```
Sub Overflow()
Dim a As Double
a = 10000 * 10000
End Sub
```

raises an overflow error because the calculation sees the first two operands and assumes that the whole thing deals with integers.You can get around the problem in several ways:

```
Sub Overflow()
Dim a As Double
a =10000# * 10000
End Sub
Sub Overflow()
Dim a As Double
a = CDbl(10000) * 10000
End Sub
Sub Overflow()
Dim a As Double
Dim b As Double
b = 1
a = b * 10000 * 10000
End Sub
```

I have used the integer and double types in this illustration, but the problem could occur with other types.
Coul you sen a sample?

Regards