General discussion

  • Creator
    Topic
  • #2130774

    Currancy output Function

    Locked

    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

All Comments

  • Author
    Replies
    • #3546578

      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”

    • #3548409

      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.68)

      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

      • #3548402

        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)”)

      • #3440779

        Currancy output Function

        by judge dread ·

        In reply to Currancy output Function

        Please take note to my additional comment I just added

    • #3440536

      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

    • #3440782

      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.

    • #3573560

      Currancy output Function

      by judge dread ·

      In reply to Currancy output Function

      This question was closed by the author

Viewing 4 reply threads