VBA format function

eduzs · #1 02-13-2019, 05:41 PM

Hi,

I need to use vba FORMAT function to return this:
Result = Format(MyNumber,"x")
MyNumer Result
123 123

1234 1.234
1234,1 1.234,1
1234,12 1.234,12
What "x" will produce these results?
Thanks

Guessed · #2 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Keeping the source format when extracting data from multiple cells using Vlookup function	praboos2001	Excel	6	11-15-2016 04:40 AM
Restrict Editing function disable insert textbox function	IanM_01	Word	5	11-21-2015 02:29 AM
Using Left function to format a range	USAOz	Excel	4	09-10-2015 03:00 AM
#REF! Error in calling VBA function disappears when function is copied	lcaretto	Excel Programming	2	05-26-2014 07:19 PM
Change format of date when using Now function in VB code	Bondai	Excel Programming	2	03-02-2012 05:09 PM