excel - 在 MsgBox 中将数字格式化为货币

Question

我在论坛中找到了以下代码，并为我的应用程序进行了改造。它在 MsgBox 中显示结账成本表。 .我想将数值格式化为货币，但不知道代码中的何处或如何执行此操作。我过去曾使用以下代码将变量格式化为货币，但并不真正知道如何在我的代码中应用。

这是我用来格式化货币的方法:

FormattedClosingVolume = Format(ClosingVolume, "#,##0")

这是我正在使用的代码，用于 MsgBox ;我想格式化列 AO要在 MsgBox 中显示的货币.

Sub Closing_Costs_Popup()
    Dim xRg As Range
    Dim xTxt As String
    Dim xCell As Range
    Dim xStr As String
    Dim xRow As Long
    Dim xCol As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
        xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
        xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
    'Set xRg = Application.InputBox("Please select range:", "Kutools for Excel", xTxt, , , , , 8)
    Set xRg = Worksheets("Closing Costs").Range("AO2:AP39")
    If xRg Is Nothing Then Exit Sub
    On Error Resume Next
    For xRow = 1 To xRg.Rows.Count
        For xCol = 1 To xRg.Columns.Count
            xStr = xStr & vbTab & xRg.Cells(xRow, xCol).Value '& vbTab
        Next
        xStr = xStr & vbCrLf
    Next
    MsgBox xStr, vbInformation, "Settlement Charges"
End Sub

Answer 1

考虑:

Sub Macro1()
    Dim n As Double, s As String
    n = 1234.56
    s = Format(n, "$#,##0.00")
    MsgBox s
End Sub

编辑#1:

要将其应用到您的代码中，例如:

xStr = xStr & vbTab & xRg.Cells(xRow, xCol).Value

将被替换为:

xStr = xStr & vbTab & Format(xRg.Cells(xRow, xCol).Value, "$#,##0.00")

(未经测试)