Integer rounding issue in VBA

[Showard27]

Hi all

I have come across a bit of a problem in my VBA coding which is throwing up a issue.

I take out a rounding error in my code with this statement, with EntryList declared as an Integer

EntryList = (LastCol - 10) / 2

so if LastCol is 71 then EntryList is set to 30 (ie 71-10 = 61 & 61 / 2 = 30.5 therefore the Integer is 30)....perfect
however
if LastCol is 73 then EntryList is being set to 32 (which indicates VBA/Excel is rounding up not taking the Integer from the Answer which is 31)
Have I found an anomaly, or am I missing something

Many thanks, am flummoxed
S

 

[Rick Rothstein]

Hi all

I have come across a bit of a problem in my VBA coding which is throwing up a issue.

I take out a rounding error in my code with this statement, with EntryList declared as an Integer

EntryList = (LastCol - 10) / 2

so if LastCol is 71 then EntryList is set to 30 (ie 71-10 = 61 & 61 / 2 = 30.5 therefore the Integer is 30)....perfect
however
if LastCol is 73 then EntryList is being set to 32 (which indicates VBA/Excel is rounding up not taking the Integer from the Answer which is 31)
Have I found an anomaly, or am I missing something

I am about to go to sleep, so I don't have time to write up a full response, but the problem is due to Banker's Rounding (you can Google it). VB using Banker's Rounding for all of its rounding with one exception... rounding done by the Format function is what most people consider normal rounding. Try changing the statement to this...

EntryList = Format((LastCol - 10)/2, "0")

 

[Showard27]

Many thanks

With a bit of investigation the Integer declaration does indeed round strangely sometimes up to the nearest even number, not sure if I would class this strictly as bankers rounding, however the Int function seems to operate properly. so I have modified as follows and it works.

Entrylist = Int(LastCol - 10) / 2

S

 

[Rick Rothstein]

Many thanks

With a bit of investigation the Integer declaration does indeed round strangely sometimes up to the nearest even number, not sure if I would class this strictly as bankers rounding, however the Int function seems to operate properly. so I have modified as follows and it works.

Entrylist = Int(LastCol - 10) / 2

The Int function does not actually round values, at least not in the context one normally associates with rounding. Int returns the first integer it comes to after rounding towards zero... that rounding takes place no matter what the fractional part is the number is... actual rounding, the kind I was referring to deals with how a number ending 5 rounds to the previous digit position. Banker's Rounding rounds to the even number whereas "normal rounding" rounds all 5 up to the digit that is one greater than the previous digit. And as I said, in all situations where actual rounding occurs within all VB functions and operations that force rounding, that rounding will be Banker's Rounding except for the Format function which performs "normal rounding" when it format pattern requires rounding to take place.