0

I tried to round down my value following: How to round to 2 decimal places in VBA?

I send Outlook email, where the costs rounded to two decimal phases in Excel are changed to being rounded down to three decimal places in the Outlook HTML body.

enter image description here

I did something like this:

Dim bs as Worksheet
Dim Cost As Single  
Dim linecount2 As Long
 
Set bs = Sheets("BoM")
linecount2 = 2
Cost = Format(bs.Range("E80")(linecount2, 2), "0.00")

I get:

Type mismatch

I tried another solution:

Cost = Format(bs.Cells(linecount2, 2), "0.00")
Cost = Round(bs.Cells(linecount2, 5), 80)  ' because my cell is E80
Cost = WorkshhetFunction.Round(bs.Cells(linecount2, 5), 80)  ' because my cell is E80

Here the debugger points to the last line, saying

variable is not defined

The previous example was based on cell B2. My situation refers to cell E80.
How do I round this number to two decimal places?

Community
  • 1
  • 1
Geographos
  • 827
  • 2
  • 23
  • 57
  • 1
    I highly recommend reading the documentation for [the `Cells` Property](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.cells) and [the `Round` function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function), because your "other solutions" code is currently rounding to cell **E2** to **80** decimal places, not cell **E80** to **2** decimal places. – Chronocidal Jul 31 '20 at 11:33
  • Every answer and comment on this page missed the mark. Don't use `Format()`. Don't use `Round()`. You are using the `.Value` property of the `Range` object (and remember that `.Value` is the default property of `Range`, so that's what you are accessing if you don't specify a property)... and that is your problem. Instead use the `.Text` property. This way you get a text string that is IDENTICAL to what Excel is actually displaying in the cell. So, for example: `Cost = bs.Range("E80")(linecount2, 2).Text` – Excel Hero Aug 13 '20 at 16:10

2 Answers2

2

The Round Function is what you need. e.g. Round(2.35235, 2) = 2.35 The data type for Cost is inappropriate, use a Double instead.

The last line errors because of a typo WorkshhetFunction should be WorksheetFunction

Absinthe
  • 3,258
  • 6
  • 31
  • 70
  • This round function applies to specified values I think. if I rewrite: ?WorksheetFunction.Round - I am getting Print WorksheetFunction.Round – Geographos Jul 31 '20 at 11:56
  • The linked function is VBA, the one you're using is Excel, called via VBA. Make life easy for yourself and just use the VBA one :) – Absinthe Jul 31 '20 at 12:13
  • Be aware that VBA uses so called *bankers' rounding* (= midpoint value 5 always rounds to the nearest even number), which differs from the `Worksheetfunction.Round()` function. C.f. [Rounding...](https://stackoverflow.com/questions/137114/rounding-in-ms-access/137177#137177) and [How Excel VBA round doubles to integers](https://stackoverflow.com/questions/46850958/how-excel-vba-rounds-doubles-to-integers/48042374#48042374)@Absinthe – T.M. Jul 31 '20 at 17:01
1

Format returns a string, thus:

Dim bs as Worksheet
Dim Cost As String 
Dim linecount2 As Long

Set bs = Sheets("BoM")

linecount2 = 2

Cost = Format(bs.Range("E80")(linecount2, 2), "0.00")
' or:
Cost = Format(bs.Range("E80")(linecount2, 2), "£0.00")
' or:
Cost = Format(bs.Range("E80")(linecount2, 2), "Currency")

Of course, now when Cost is text, remove any later attempt to format the value.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • I am using: Cost = Format(bs.Range("E80")(linecount2, 2), "0.00") and I am getting "Type mismatch" When I change to Dim Cost as String then I have no value appeared When I don't define the linecount2 then I am getting value from the upper right cell (F79") with round to 1 decimal only. – Geographos Jul 31 '20 at 11:53
  • 1
    OK I got it. The problem was in linecount somewhere. I used linecount2 = 1 and next: Cost = Round(bs.Range("E80")(linecount2, 1), 2 – Geographos Jul 31 '20 at 12:02
  • If you want the text that is displayed in a cell on a worksheet, use Range.Text for an exact copy. You should NOT use Format() or Round(). Your situation is exactly why the Range.Text property exists. Think about it... You've formatted the worksheet to display what you want, why should you have to format it again in VBA??? – Excel Hero Aug 14 '20 at 03:29