Microsoft Office Forums VBA format function

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-13-2019, 05:41 PM
eduzs eduzs is offline VBA format function Windows 10 VBA format function Office 2010 32bit
Competent Performer
VBA format function
 
Join Date: May 2017
Posts: 157
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
__________________
Backup your original file before doing any modification, test in a throwaway copy of your file.
Reply With Quote
  #2  
Old 02-13-2019, 07:38 PM
Guessed's Avatar
Guessed Guessed is offline VBA format function Windows 10 VBA format function Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,240
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
VBA format function Keeping the source format when extracting data from multiple cells using Vlookup function praboos2001 Excel 6 11-15-2016 04:40 AM
VBA format function Restrict Editing function disable insert textbox function IanM_01 Word 5 11-21-2015 02:29 AM
VBA format function Using Left function to format a range USAOz Excel 4 09-10-2015 03:00 AM
VBA format function #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 03:59 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft