VBA Overflow Issue

[danbrawl93]

Hey guys,

I'm having a real issue with some code that isn't working!

  For c = r_start To r_end
           
            If z <> 7 Then
            value = Evaluate("=SumProduct((A7:A" & z - 1 & " = A" & c & ")*(Numbervalue(" & column_letter & "7:" & column_letter & z - 1 & ")))")
            Else: value = 0
            End If
            
            If x <> (n + 1) Then
            value_2 = Evaluate("=SumProduct((A" & n + 1 & ":A" & x - 1 & " = A" & c & ")*(Numbervalue(" & column_letter & "" & n + 1 & ":" & column_letter & "" & x - 1 & ")))")
            Else: value_2 = 0
            End If
 
            Cells(c, 2).value = value + value_2
 Next c

The variables "value" & "value_2" are set to be integers. However, I seem to keep getting an overflow error for no obvious reason at certain points (e.g. when value = 20000 & value_2 = 15000). I know this is a vague question, but does anyone have any idea what the problem is at this stage? All the variables are correct in terms of the value which they should be.

Thanks so much!

 

[RoryA]

Which line actually causes the error?

 

[Derek Brown]

You could try changing "value" and "value_2" to type Long.
Excel VBA Variables. Using Variables in Excel VBA Macro Code

 

[danbrawl93]

Sorry, my question was poorly written - it would cause an error at seemingly random points depending on cell values. I figured it would be easier to try for a quick fix instead of probing into the specific code line errors, since this error causing line also seemed to vary as the loop proceeded. Anyway...

Thank you so much Derek, your solution worked! Could somebody please hazard a guess as to why "Dim as Long" had such an effect?

 

[Derek Brown]

The greatest value that can be stored in an Integer is 32,767.
Value + Value_2 would exceed that in the example that you gave.
I prefer to use Long where calculation is involved to prevent possible problems when Excel perform intermediate calculations.

 

[danbrawl93]

Derek, I had no idea - you are a legend! Thank you