Overflow error at variable assignment using integers and date values

[SBF12345]

Greetings,

I am running into an overflow error and seeking assistance in how to handle. The line giving me trouble is below:

B = (wsPA.Cells(K, "E").Value - wsPA.Cells(RowLoop1, "E").Value) / (wsPA.Cells(K, "A").Value - wsPA.Cells(RowLoop1, "A").Value)

The E variables are integer values, while the "A" columns are DATE values of the type. The B variable is an integer value.

I think my question is how to maintain the B variable as an integer while including date variables of the MMDDYYYY type as a basis for the slope value. I would like to find the number of days between the date values as the denominator of the equation.

Thanks!

 

[Trebor76]

Hi SBF12345,

An interger variable can only hold numbers in the range -32,768 to 32,767. Try converting your integer variables to long.

A great overview of variables can be found here.

Regards,

Robert

 

[Norie]

Change the Integer declarations to Long.

The range of Integer variables is –32,768 to 32,767, which is easily exceeded when working with dates - for example 2008-01-01 (today's date) is stored as the integer value 43101.

 

[SBF12345]

Hmmm...I changed the B variable to Long but am still receiving the overflow error. I have the dates stored as "MM/DD/YYYY" in the cells on the sheet. I'd prefer to keep them in this format if possible. Could the date format be a contributing factor?

 

[Trebor76]

Could the date format be a contributing factor?

No, I wouldn't think so

Debug each part of the formula to see / verify what each component of your formula is returning i.e.

Debug.Print wsPA.Cells(K, "E").Value
Debug.Print wsPA.Cells(RowLoop1, "E").Value
Debug.Print wsPA.Cells(K, "A").Value
Debug.Print wsPA.Cells(RowLoop1, "A").Value

and then see if you can do the formula with the given results.

 

[StephenCrump]

Hmmm...I changed the B variable to Long but am still receiving the overflow error.

My guess is that RowLoop1 is exceeding 32,767.

As Norie and Trebor76 no doubt intended: Convert all your Integers to Longs (and always use Long - there's no point in using the Integer Data Type in VBA).

I'm curious though ... why are you constraining B to be integer when presumably the slope won't always be integer?

 

[SBF12345]

I changed all my integer variables to longs.

The problem was that the denominator value for the variable B was "0" and thus creating a problem, cannot divide by zero. I also had to change some of the variables around in the script, but this was unrelated.

On the record, I used Add watch to was the specific row values to determine their values at the moment of the error.