none
Currency format in vb RRS feed

  • Question

  • I have a macro that emails a range of data that works fine but I would like to format the output to currency with no decimals. Here is the line of code that I have but I don't know how to tell it to format the data in B2 as currency with no decimal points when it adds it to the email (in my excel sheet that field is formatted as currency)

    strbody = "Here is your Pipeline Volume:" & " " & ActiveSheet.Range("B2") & "<br />"



    MEC

    Thursday, July 14, 2016 11:55 PM

Answers

  • Hi MEC,

    >> I have everything working except that the output from b2 onto the email has to be either currency with no decimals or comma with no decimals.

    Do you mean you want to output the value from B2 whose value is “$10.12” to email? If so, you could try “ActiveSheet.Range("B2").Text” instead of “ActiveSheet.Range("B2")”. If you want “$10”, you could try “Split(ActiveSheet.Range("B2").Text, ".")(0)”.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by mecerrato Friday, July 15, 2016 4:30 PM
    Friday, July 15, 2016 2:39 AM

All replies

  • Have a look at this: The Currency Format

    "If you use .Value with a cell formatted as currency the value will be converted to Currency datatype by VBA. Of course you probably won’t see this because most of the time you immediately do some other VBA operation (like arithmetic) that converts it from currency to Double.

    .Value2 is faster than .Value and avoids this under-the-covers currency truncation: there is no good reason to ever use .Value apart from laziness (its the defaulr .Range property)"

    Edit: Sorry that might not be exactly what you were looking for, so try this:

    Range.NumberFormat Property (Excel)

    Range.NumberFormatLocal Property (Excel)

    Cells.Select
    Selection.NumberFormat = "#,##0"
    Range("A1").Select
    Application.CalculateFull
    End Sub
    and if you need a symbol, substitute this in:
    Selection.NumberFormat = "$#,##0"


    • Edited by fierycargo Friday, July 15, 2016 12:16 AM
    Friday, July 15, 2016 12:04 AM
  • That didn't work but I think I am negating anything I do because of the code I am using to create the HTML email. I found different parts of code on the web to create what I needed to do and I honestly can't say i fully understand it. I have everything working except that the output from b2 onto the email has to be either currency with no decimals or comma with no decimals. I added a ' to the part that I tried using the numberformat feature because it did not work. Here is all the code that creates the email:

    Sub Pipeline_EmailBranchNetRegs()
    '
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Signature As String

    If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
      ActiveSheet.ShowAllData
    End If
    ActiveSheet.Range("$a$6:$AQ$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
    ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=31, Criteria1:=Range("H4")
    Set rng = Nothing
    ' Only send the visible cells in the selection.
    Set rng = ActiveSheet.Range("a6", ActiveSheet.Range("H6").End(xlDown))

    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected. " & _
               vbNewLine & "Please correct and try again.", vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
       .Display
    End With
    Signature = OutMail.HTMLBody
    strbody = "Here is your Net Reg Pipeline:" & "<br />" & ActiveSheet.Range("A1") & " " & ActiveSheet.Range("B1") & "<br />" & ActiveSheet.Range("A2") & " " & ActiveSheet.Range("B2") ' .NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

    With OutMail
        '.to = ActiveSheet.Range("F4")
        .Subject = ActiveSheet.Range("H4") & " " & "Net Reg Pipeline"
        .HTMLBody = "<BODY style=font-size:12.5pt;font-family:Calibri>" & "</p>" & strbody & RangetoHTML(rng) & Signature
        .Display
    End With

    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub

    Function RangetoHTML(rng As Range)
        Dim fso As Object
        Dim ts As Object
        Dim TempFile As String
        Dim TempWB As Workbook

        TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

        'Copy the range and create a new workbook to past the data in
        rng.Copy
        Set TempWB = Workbooks.Add(1)
        With TempWB.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial xlPasteValues, , False, False
            .Cells(1).PasteSpecial xlPasteFormats, , False, False
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With

        'Publish the sheet to a htm file
        With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With

        'Read all data from the htm file into RangetoHTML
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.ReadAll
        ts.Close
        RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")

        'Close TempWB
        TempWB.Close savechanges:=False

        'Delete the htm file we used in this function
        Kill TempFile

        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
    End Function


    MEC

    Friday, July 15, 2016 12:53 AM
  • Hi MEC,

    >> I have everything working except that the output from b2 onto the email has to be either currency with no decimals or comma with no decimals.

    Do you mean you want to output the value from B2 whose value is “$10.12” to email? If so, you could try “ActiveSheet.Range("B2").Text” instead of “ActiveSheet.Range("B2")”. If you want “$10”, you could try “Split(ActiveSheet.Range("B2").Text, ".")(0)”.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by mecerrato Friday, July 15, 2016 4:30 PM
    Friday, July 15, 2016 2:39 AM
  • Format(Range("B1").Value, "$#,##0")
    Friday, July 15, 2016 4:30 AM
    Moderator
  • Thanks Edward, that worked like a charm

    MEC

    Friday, July 15, 2016 4:30 PM
  • Edward your solution worked, can you also tell me how to do the same thing but with a variable (Goal)

    Here is my code:

    Dim RegRng As Range

    Goal = RegRng.Offset(0, 1).Value

    strbody = "Snapshot of Current Pipeline and MTD Registration Count:" & "<br />" & "Goal = " & Goal & "<br />"

    in the above code I would like Goal to be displayed as currency


    MEC

    Tuesday, July 19, 2016 2:29 PM
  • Hi,

     

    >>how to do the same thing but with a variable

     

    A simple piece of code to return the Goal as currency below.

    Sub test()

    Dim RegRng As Range

    Dim strbody As String

    Set RegRng = Worksheets("Sheet1").Range("A1")

    Goal = RegRng.Offset(0, 1).Value

    strbody = "Goal = " & Format(Goal, "$#,##0")

    MsgBox (strbody)

    End Sub

     

    For your situation, try

    strbody = "Snapshot of Current Pipeline and MTD Registration Count:" & "<br />" & "Goal = " & Format(Goal, "$#,##0") & "<br />"

    Wednesday, July 20, 2016 5:59 AM
    Moderator