Division in VBA......not as expected....

String S = VAR1 \ VAR2 then EVALUATE(S) ---- gives error.

Do you mean?

Evaluate("int(" & Replace(S, "\", "/") & ")")

Originally Posted by jindon

Do you mean?

Evaluate("int(" & Replace(S, "\", "/") & ")")

I am creating various mathematical expressions at run time and evaluating those using the EVALUATE method. My expression in this case was "VAR1 \ VAR2" where Var1 = 7 and Var2 = 2.......... this gave me error.........

Originally Posted by enggrahul78

. My expression in this case was "VAR1 \ VAR2" where Var1 = 7 and Var2 = 2.......... this gave me error.........

Would be

Evaluate("int(" & VAR1 & "/" & VAR2 & ")")

With 7 in Cell F1 and 2 in Cell G1:

MsgBox "Quotient: " & Int([F1] / [G1]) & ", Remainder: " & [F1] Mod [G1]

For those who might be interested, here is (what I think is) an interesting note that I posted to the compiled VB newsgroups many, many, many years ago about Integer Division as it relates to the Banker’s Rounding issue…

If floating point numbers are involved, integer division may not return the answer you expect. Most people think x\y is short-hand for Int(x/y), probably because of its name “Integer” division. However, VB applies Banker’s Rounding to the numerator AND denominator BEFORE performing the division. Many would think that this…

MsgBox 4.5 \ 1.5

should display 3 in the MessageBox, however, it prints out 2 instead. Banker’s Rounding rounds the 4.5 to 4 (the nearest even number) and rounds 1.5 to 2 (again, the nearest even number) AND ONLY THEN does it do the division and truncate the fractional part (4/2 producing 2 as the answer).

To follow up on my last post, some may find this interesting as well...

Anything that requires rounding in VBA uses Banker’s Rounding (round 5s to the nearest previous even number). For example, both CLng(1.5) and CLng(2.5) will return 2 as the converted value because Banker’s Rounding lies at the heart of the conversion. HOWEVER, there is one (and only one as far as I have discovered) function in VBA that uses what I call “normal rounding”… you know, 5’s at the end of a number being rounded to the decimal position in front of the 5 always round up. That function is the Format function (along with some of its various incarnations, such as FormatNumber). For example, Format(2.5, "0") or Format(2.5, "#"), depending on if you want to force zeroes or not, will round up to 3 as expected (at least, as expected by me). Whatever number of decimal places you choose to round to (using the format pattern to set the number of decimal places). It appears that the Format function uses a Decimal subtype of a Variant as its data type since these expressions all round as expected for “normal rounding”…

Format("1.234567890123456789012345674", "0.00000000000000000000000000")
Format("1234.567890123456789012345675", "0.00000000000000000000000")

Notice the limit of 28 digits (and, of course, the need to feed numbers with more than 15 digits as a String).

So what logic shall i follow ??