Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-13-2019, 05:41 PM
eduzs eduzs is offline Windows 10 Office 2010 32bit
Competent Performer
 
Join Date: May 2017
Posts: 104
eduzs is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 02-13-2019, 07:38 PM
Guessed's Avatar
Guessed Guessed is offline Windows 10 Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,118
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

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
Reply With Quote
Reply

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


All times are GMT -7. The time now is 07:24 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft