0

I have the following issue.

When I try to do :

cell.value=CStr(cell.value)

it works with numbers like 6,91. But when I try with numbers like 6,911 I get 6911 in return when I just want 6,911 instead. I'm using commas because I'm in Europe, I guess maybe VBA mixes it up with the American way of writing thousands with a comma.

Indeed, here I only want a decimal with 3 figures after the comma

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
zzz247
  • 97
  • 1
  • 7
  • Cell is a terrible name for a variable in Excel VBA. Can you please post more coide so we can see how you are declaring cell. – freeflow Nov 18 '21 at 12:47
  • 2
    If you want to format a cell, use its `NumberFormat` property – Rory Nov 18 '21 at 12:47
  • 4
    @freeflow I disagree - cell is a very good name. Pretty clear what you are referring to after all. :) – Rory Nov 18 '21 at 12:48
  • 6
    @freeflow Doesn't sound like I'd want to work for you. What's your issue with cell? It's not used by vba or the excel OM. – Rory Nov 18 '21 at 12:58
  • 1
    ... longstanding confusion about how `Cells` is part of the Excel Object Model but `cell` is *definitely* not. – BigBen Nov 18 '21 at 13:46
  • @Pᴇʜ I would argue that Cell is a poor choice of variable name when other better names exist. The use of Cell as a name is too close to what **could** be an Excel object. In addition '.Cell' **is ** a Word object. I have naming strategies which help in this situation and my way to resolve this conundrum is to prefix any variable defined within a method with 'my'. It is my opinion that 'myCell' is a much much better name for a variable then just 'Cell'. I have other similar naming strategies which greatly improve the readability of code. If you pm me I am happy to elaborate. – freeflow Nov 18 '21 at 17:08

1 Answers1

3

This does not what you expect it to do

cell.value=CStr(cell.value)

Here CStr(cell.value) will turn it into a String but if you write a string into a cell that looks like a number Excel "thinks" and turns it back into a number. Here comes the confusion.

If you want to format that cell as text use

Cell.NumberFormat = "@"
Cell.Value = Format$(cell.Value, "0.000")

or use Cell.Value = "'" & Format$(cell.Value, "0.000")

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73