CODEWITHSUNDEEP
excelvba
Kes Perron
Kes Perron

Reputation: 477

VBA Format function is ignored

I have this code:

Sub NewSummary()
    Set ws = ThisWorkbook.Sheets("Sheet1")
    iWorking = 3
    iTotal = 4
    dPercent = iWorking / iTotal
    pPercent = Format(dPercent, Percent)
    sWorking = iWorking & " / " & iTotal
    sWorking = Format(sWorking)
    ws.Cells(1, 1).Value = sWorking
    ws.Cells(2, 1).Value = pPercent
End Sub

The output that I expect is:
3 / 4, 75.00%

But what I get is:
4-Mar, 0.75

Excel is overriding the "3 / 4" string and assuming I want a date. How do I suppress that? And why is the Format function not converting 0.75 to 75.00%?

Upvotes: 1

Views: 240

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

Named numeric formats are supported, but they're not language-level keywords you can use just like any other variable. As David stated above, Percent is just an undeclared local variable holding a Variant/Empty value (which means your module is missing Option Explicit).

Supply the named format in a string literal:

pPercent = Format(dPercent, "Percent")

While this works, I personally prefer to use explicit number formatting, that way you have explicit and full control on the number format; for example if you want a single decimal, or need more than two, using a named format won't cut it.

Upvotes: 0

David Zemens
David Zemens

Reputation: 53663

you can't format a string like that. THe spaces around the / are a problem, but more generally, formatting "3/4" as a string literal relies on some implicit type conversion, and in this case it's assumed to be a date string (representing March 4 in US Locale), which is why you get date-like results.

Do simply:

Option Explicit

Sub NewSummary()
    Dim ws As Worksheet

    Dim iWorking As Long, iTotal As Long
    Dim dPercent As Double
    Set ws = ThisWorkbook.Sheets("Sheet1")

    iWorking = 3
    iTotal = 4
    dPercent = iWorking / iTotal
    ws.Cells(1, 1).Value = Format(dPercent, "0.00%")

End Sub

Upvotes: 2

Related Questions