Why is Excel VBA rounding my integer division results?

Question

I'm writing a VBA macro in Excel 2002 to manipulate date from a device that gives 10 values on each of 4 channels.

When I try to divide an integer by another integer to calculate the row and column, the result shows that it's using bankers rounding, as if it was a floating point variable.

Sub TestRounding()
    Dim Y As Integer
    Dim Field As Integer
    Dim Channel As Integer

    ActiveWorkbook.Sheets.Add
    ActiveWorkbook.ActiveSheet.Cells.Select
    range("A1").Select

    For Field = 1 To 40
        Channel = (Field - 1) / 10
        Y = Field - 1
        ActiveSheet.range("A1").Offset(Y, Channel).Value = Field
    Next Field

End Sub

Y is the row to put the output in, which I usually set with

Y = (Field -1) mod 10

but I've left it as

Y = Field - 1

to illustrate the point more clearly in the resulting worksheet.

Channel is supposed to be the column that the output is put in.

When I run this macro, the values 1-6 get put in column A, then 7-15 get put into Column B, 16-26 get put into Column C, then 27-35 in Column D then 36-40 in Column E.

What I expected was that values 1-10 go into column A, 11-20 into column B, 21-30 into column C and 31-40 into column D.

I'm used to C and C++ where if I divide an integer by another integer, the result is calculated using integer maths. What's different in VBA?

Answer 1

To use "integer maths" for division use \ instead of /.

Integer Division \

Dividing an item means cutting it in pieces or fractions of a set value. Therefore, the division is used to get the fraction of one number in terms of another. The Visual Basic language provides two types of operations for the division. If you want the result of the operation to be a natural number, called an integer, use the backlash operator "\" as the divisor. The formula to use is: Value1 \ Value2 This operation can be performed on two types of valid numbers, with or without decimal parts. After the operation, the result would be a natural number.

Decimal Division /
The second type of division results in a decimal number. It is performed with the forward slash "/". Its formula is: Value1 / Value2 After the operation is performed, the result is a decimal number.

Source

Answer 2

The back slash is used for integer division, and the forward slash for decimal division. Change your forward slash to a back slash and you should get the result you are expecting.

http://www.functionx.com/vbaexcel/Lesson04.htm