Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Overflow error in Excel

Posted on 2014-01-13
7
Medium Priority
?
314 Views
Last Modified: 2014-02-28
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

Open in new window

0
Comment
Question by:mikef715
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39778501
Hi,

Coul you sen a sample?

Regards
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39778716
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
0
 

Author Comment

by:mikef715
ID: 39779303
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?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39779357
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)
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 39779493
It isn't clear how much debugging you have done, but try putting this debug print line in place of line 20:
       
 Debug.Print "c: " & c, "i: " & i, "j: " & j, "Cl11: " & Claimbacks(i, 11), "Cl10: " & Claimbacks(i, 10)

Open in new window

and tell us what appears in the Immediate window when it breaks on the error.
0
 

Author Comment

by:mikef715
ID: 39792272
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!
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39894367
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:
Sub Overflow()
    Dim a As Double
    a = 10000 * 10000
End Sub

Open in new window

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

Open in new window

I have used the integer and double types in this illustration, but the problem could occur with other types.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question