General discussion

Locked

Currancy output Function

By Judge Dread ·
Could someone provide me the code to produce a US currency output which includes the dollar sign, commas, and rounded to two decimal places that also can be used for varying outputs to be totaled also rounded to two decimal places with the dollarsign and commas to obtain an overall average, sub totals and/or grand totals. I would appreciate your assistance

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Currancy output Function

by DKlippert In reply to Currancy output Function

You don't specify an application, but the general formatting code is:

$#,##0.00

If you were using Excel, the following formatting will also make negative numbers red with (), if the number is 0 it will display Zero, if the entry is text, it will say so.

$#,##0.00;[Red]$#,##0.00;"Zero";"Text"

Collapse -

Currancy output Function

by Judge Dread In reply to Currancy output Function

Please take note to my additional comment I just added

Collapse -

Currancy output Function

by sl-campbell In reply to Currancy output Function

This will work for VB/VBA:

myNewCurrency=Format$(Round(myCurrency,2),"\$#,##0.00;(\$#,##0.00)")

Assign or replace then variable myCurrency with the value that you want rounded and formated. Negative numbers use the second part which enclose the answer in brackets.

You could the create a function like this:

Public Function FormatUSCurrency(vCurrency As Variant) As String

'Using the variant ensures that even a string can be passed that is possibly a number.
'If nothing is passed then $0.00 is returned.

If IsNumeric(vCurrency) Then
vCurrency = CDbl(vCurrency)

Else
vCurrency = 0
End If

FormatUSCurrency = Format$(Round(vCurrency, 2), "\$#,##0.00;(\$#,##0.00)")

End Function

However, I suggest to do all the calculations first, either with the rounding for each variable or rounding only the results, and then, either for the sub totals or end results, do the formating.

It may be better to do it like this:

Where:
A=1.254
B=2.433

C=A+B

lblSubTotal1.Caption=Format$(Round(C,2)
,"\$#,##0.00;(\$#,##0.00)")

lblSubTotal1.Caption is then $3.69 (If rounded earlier the subtotal would have been $3.6

D=A-B

lblSubTotal2.Caption=Format$(Round(D,2),"\$#,##0.00;(\$#,##0.00)")

lblSubTotal2.Caption is then -$1.18

E=C+D

lblTotal.Caption=Format$(Round(E,2),"\$#,##0.00;(\$#,##0.00)")

lblTotal.Caption is then $2.51

Collapse -

Currancy output Function

by sl-campbell In reply to Currancy output Function

SORRY!

You do not need to use the Round function with the Format function as I have done here. I use my own Round function that takes the place of the VB Round function because of "wrong" rounding, and forgot about this habit. The Format function will round, but only takes into consideration, the first digit past the number of places to round.

The VB Round(and Format) function returns the correct rounded number only to a certain degree.
1.2545 would be rounded by VB as 1.25 where asmy Round function would return 1.26, (up to 5 decimal places are checked, from right to left, because of international usage).

So just use the following with-out the round function:

FormatUSCurrency = Format$(vCurrency, "\$#,##0.00;(\$#,##0.00)")

Collapse -

Currancy output Function

by Judge Dread In reply to Currancy output Function

Please take note to my additional comment I just added

Collapse -

Currancy output Function

by RRV In reply to Currancy output Function

if VB use
format(yourData,Currency)
Currency is the parameter for format. to set your currency format change it in regional setting of your system. To do that programatically use SetLocaleInfo API. You get exactly similar sample code in MSDN.microsoft.com

Collapse -

Currancy output Function

by Judge Dread In reply to Currancy output Function

Please take note to my additional comment I just added

Collapse -

Currancy output Function

by Judge Dread In reply to Currancy output Function

I apoligize to all who have taken the time to respond to my question. In my haste to submit my question, I forgot to further submit all the necessary information so my question could be answered. Sorry for being so vague. I need the currency format string to perform all the functions I originally submitted for HTML and also JavaScript. Again. plesae accept my apologies for not having supplied this important information in my original question.

Collapse -

Currancy output Function

by Judge Dread In reply to Currancy output Function

This question was closed by the author

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums