Microsoft Office Forums - View Single Post

Guessed · 02-13-2019, 07:38 PM

On English language systems the comma and dot are reversed so you would need to adapt the function accordingly.

You will need two different formats to deal with whole numbers vs numbers with decimal places.

Code:

Sub Test_FormatMyNumber()
  MsgBox FormatMyNumber(1234)
  MsgBox FormatMyNumber(123)
  MsgBox FormatMyNumber(1234.1)
  MsgBox FormatMyNumber(1234.12)
End Sub

Function FormatMyNumber(MyNumber As Double) As String
  If MyNumber = Round(MyNumber, 0) Then
    FormatMyNumber = Format(MyNumber, "#,###")
  Else
    FormatMyNumber = Format(MyNumber, "#,###.###")
  End If
End Function