Search code examples
excelstringformatmismatch

Why does VBA raise a "type mismatch" error when I use "Format(string_variable, "###.#") and the value of the string_variable is set to 0.0

My stock-trading code formats price changes (single variable) for transmission to my cell phone and limit the number of insignificant digits. When the price change is zero the module raises a "Type Mismatch" error. All non-zero values work fine. I have written a simple module which exhibits this behavior.

Sub test_format()
    Dim test_single As Single, output As String
    test_single = 0#
    output = Str(Format(test_single, "###.#"))
    Debug.Print test_single; output
End Sub
Solution

  • You need to remove the Str() Function

    Sub test_format()
    Dim test_single As Single, output As String
    test_single = 112.25
    output = Format(test_single, "##0.0")
    Debug.Print output
End Sub

    My Output:

    112.3

    EDIT: Updated the formatting to "##0.0". Now if you get the value 0.0 it won't just return " . " but will actually return 0.0