8

In cell B2 I have variable with the value 297.123 before doing calculations In VBA I would like to round this to 297.12. See the code below as to what I have tried. Both have evaluated fuel to 297. What am I doing wrong?

Dim fuel As Integer
Dim linecount As Long

linecount2 = 2

fuel = Format(ws.Cells(linecount2, 2), "0.00")
fuel = Round(ws.Cells(linecount2, 2), 2)
Anthony
  • 542
  • 1
  • 8
  • 25

1 Answers1

12

Change Dim fuel As Long -> Dim fuel As Double

And both of your formula will work.

Dim fuel As Double
Dim linecount2 As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")


linecount2 = 2

fuel = Format(ws.Cells(linecount2, 2), "0.00")
fuel = Round(ws.Cells(linecount2, 2), 2)
fuel = WorksheetFunction.Round(ws.Cells(linecount2, 2), 2) '@suggested by T.M.


MsgBox "try"

End Sub
  • Dim Long is for Integers (whole numbers)
  • Dim Double is for numbers with decimal points (can store more decimal points, up to 15 digits)
  • Dim Single is for numbers with decimal points (store less decimal points, up to 7 digits)
Wizhi
  • 6,424
  • 4
  • 25
  • 47
  • Thank you!! Take care :) – Wizhi Nov 01 '18 at 10:46
  • 2
    Using `Round` only you are executing *Banker's rounding* with possibly unwanted results: it is a standard form of rounding used in financial and statistical operations in order to minimize significant rounding errors over multiple rounding operations by consistently rounding midpoint values in a single direction. In order to avoid so called **banker's rounding** (= midpoint value 5 always rounds to the nearest even number ) you can/should use **`WorkSheetFunction.Round`** instead. – T.M. Nov 01 '18 at 10:52
  • 1
    Thank you, I have added that :) – Wizhi Nov 01 '18 at 10:55
  • @Anthony have in mind that Integer takes values from -32,768 to 32,767.If in any case you must handle number outside those intervals you may use Long (-2,147,483,648 to 2,147,483,647. ) – Error 1004 Nov 01 '18 at 11:12