#1
|
|||
|
|||
VBA format function
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
__________________
Backup your original file before doing any modification. |
#2
|
||||
|
||||
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
Thread Tools | |
Display Modes | |
|
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 |