Run-time Error '6': Overflow

[raven1124]

Hello Everyone,

I am currently trying to run this code and it seems a little bit buggy.
It used to run before I close the my excel file but it is providing inaccurate computations specifically coming from (FRed, FGreen....etc.)

Now, when I try to re-run the file (after restarting my computer); I get Run-time error 6: overflow
I tried to google the problem and majority of them were suggesting to use larger data type. So far, what I tried are the following (Integer, Long & Double)

Here's my code as your reference

        Dim wsDatabase, wsTracker As Worksheet
        Set wsDatabase = Worksheets("Sheet2")
        Set wsTracker = Worksheets("Sheet1")
        
        
        Dim Red, Yellow, Green, Blue As Double
        Dim FRed, FYellow, FGreen, FBlue, TotalPop, PopulationPerformance As Double
        
        Red = Excel.Application.WorksheetFunction.CountIfs(wsDatabase.[C:C], wsTracker.Range("C5").Value, wsDatabase.[H:H], wsTracker.Range("C11").Value, wsDatabase.[N:N], "Red")
        Yellow = Excel.Application.WorksheetFunction.CountIfs(wsDatabase.[C:C], wsTracker.Range("C5").Value, wsDatabase.[H:H], wsTracker.Range("C11").Value, wsDatabase.[N:N], "Yellow")
        Green = Excel.Application.WorksheetFunction.CountIfs(wsDatabase.[C:C], wsTracker.Range("C5").Value, wsDatabase.[H:H], wsTracker.Range("C11").Value, wsDatabase.[N:N], "Green")
        Blue = Excel.Application.WorksheetFunction.CountIfs(wsDatabase.[C:C], wsTracker.Range("C5").Value, wsDatabase.[H:H], wsTracker.Range("C11").Value, wsDatabase.[N:N], "Blue")
        
        TotalPop = Red + Yellow + Green + Blue
        FRed = (Red / TotalPop) * 0.09523
        FYellow = (Yellow / TotalPop) * 0.238
        FGreen = (Green / TotalPop) * 0.2857
        FBlue = (Blue / TotalPop) * 0.3809
        
        PopulationPerformance = FRed + FYellow + FGreen + FBlue


        wsTracker.Range("F7").Value = FRed
        wsTracker.Range("F8").Value = FYellow
        wsTracker.Range("F9").Value = FGreen
        wsTracker.Range("F10").Value = FBlue
        wsTracker.Range("F11").Value = PopulationPerformance

 

[joeu2004]

Re: Help Needed: Run-time Error '6': Overflow

Dim Red, Yellow, Green, Blue As Double
Dim FRed, FYellow, FGreen, FBlue, TotalPop, PopulationPerformance As Double

This probably has nothing to do with the problem, but in those declarations, only Blue and PopulationPerformance are type Double. The others are type Variant.

If that is your intent, fine. Otherwise, you need "As Double" after each variable that want to be that type. For example:

Dim Red As Double, Yellow As Double, Green As Double, Blue As Double

Red = Excel.Application.WorksheetFunction.CountIfs(wsDatabase.[C:C], wsTracker.Range("C5").Value, wsDatabase.[H:H], wsTracker.Range("C11").Value, wsDatabase.[N:N], "Red")
Yellow = Excel.Application.WorksheetFunction.CountIfs(wsDatabase.[C:C], wsTracker.Range("C5").Value, wsDatabase.[H:H], wsTracker.Range("C11").Value, wsDatabase.[N:N], "Yellow")
Green = Excel.Application.WorksheetFunction.CountIfs(wsDatabase.[C:C], wsTracker.Range("C5").Value, wsDatabase.[H:H], wsTracker.Range("C11").Value, wsDatabase.[N:N], "Green")
Blue = Excel.Application.WorksheetFunction.CountIfs(wsDatabase.[C:C], wsTracker.Range("C5").Value, wsDatabase.[H:H], wsTracker.Range("C11").Value, wsDatabase.[N:N], "Blue")

TotalPop = Red + Yellow + Green + Blue
FRed = (Red / TotalPop) * 0.09523
FYellow = (Yellow / TotalPop) * 0.238
FGreen = (Green / TotalPop) * 0.2857
FBlue = (Blue / TotalPop) * 0.3809

You get an Overflow error if both Red and TotalPop are zero (i.e. 0/0), for example.

(You get a Divide By Zero error if only TotalPop is zero, which cannot happen in this case.)

 

[raven1124]

Re: Help Needed: Run-time Error '6': Overflow

Sorry for replying late..

Actually declaring the variables as not double is my mistake and forgot to explain that I tried them and see if I am getting different result.

Anyway, thanks for helping! And my bad, I totally forgot that TotalPop can return a value of 0 here depending on the result of the colors.