Anyone know how to format a number as currency, e.g. 132421309 --> $132,421,309.00? I tried using combinations of the format() function and parsing characters onto the number but ideally there is a way to have the two entries equal. In other words, if a user enters $10 the program recognized 10 (without me having to split the string “$10” and then look for the $ leader and then reconstruct the number along and then using val() ) and likewise the program will display the correct number as a currency after it does its function.
Format(value, "\\$-###,###,##0.00") I use that as a method in a module extending Currency:
Function FormatDollar(Extends value As Currency) As String
Return Format(value, "\\$-###,###,##0.00")
End Function
I have a counter part that extends string:
Function CurrencyValue(Extends s As String) As Currency
Return Val(s.ReplaceAll(",", "").ReplaceAll("$", ""))
End Function
[quote=23438:@Jeremy Cowgar]
I have a counter part that extends string:
Function CurrencyValue(Extends s As String) As Currency
Return Val(s.ReplaceAll(",", "").ReplaceAll("$", ""))
End Function[/quote]
This can & probably should figure out the international currency & thousand separator before just assuming they are $ , and .
There’s code on my page from long ago that figures this stuff out
Hm, I was editing my message to state why I chose my names (FormatDollar and CurrencyValue) stating that I only have to deal with US Dollar currencies, but when I hit save, it said I don’t have permission… Seems you’re post beat me to the edit.
Anyway, yes… If you deal with multiple currency types, your methods will have to be a bit more complex. The FormatDollar name was chosen, because it formats as a dollar value, the CurrencyValue method was named because it returns a value of the Currency type. If you deal with multiple currencies, you would need to modify CurrencyValue and add other methods (if you want to format for given types) such as FormatYen
One could also have a smart FormatCurrency to determine the users currency format, but that is beyond what I know.
Norman, how do I get to your page to see the international currency code?
A belated thanks.
Hi Norman, your link goto ‘Safari Can’t find server’
Works fine here. Also see http://www.downforeveryoneorjustme.com/http://www.great-white-software.com/
never mind… the link is working now…
[quote=23438:@Jeremy Cowgar]Format(value, "\\$-###,###,##0.00") I use that as a method in a module extending Currency:
Function FormatDollar(Extends value As Currency) As String
Return Format(value, "\\$-###,###,##0.00")
End Function
I have a counter part that extends string:
Function CurrencyValue(Extends s As String) As Currency
Return Val(s.ReplaceAll(",", "").ReplaceAll("$", ""))
End Function[/quote]
Can I Invoke it at TextChange event on TextField?
I use
me.Mask="###,###,###.##"or
me.text = Format(val(me.text),"\\$###,###,###.##")But It doesn’t work, the idea is while typing format are applying it.
You will have a hard time doing what you want. The currency symbol will bother Val. You probably find that you have syntax errors.
Your approach is also prone to errors (if unhandled, crashes) if the input is not strictly controlled and restricted. Perhaps a better approach is to format a label next to the input field (or to format the input field), after leaving the text field and validating the content. Alternately, execute all the validation and formatting when the enter key is pressed.