How do I apply a custom format such as "Text:" 0.0% in Excel VBA

Question

I would like Excel VBA to write either a number or a percentage, depending upon a user control.

In Excel, I can hit Ctrl 1 to apply a custom format such as "Text:" 0.0% or "Text:" 0.0.

I have managed to write the text and then use either Format() or Round(), depending upon the control value. However, Format has two decimal places, and I would like a single decimal place.

Here is some illustrative code. As stated above, I would like to have control over the number of decimal places for the percentages (e.g. 0, 1, 2).

Select Case sControl
Case "Percentage"
  oRange = "Text: " & Format(dvalue + 0.000001, "Percent")
Case "Numeric"
  oRange = "Text: " & Round(dvalue + 0.000001, 1)
End Select

Any pointers on how to do this would be great. I am adding 0.000001 to dvalue in both cases, as I understand that this helps to maintain traditional rounding accuracy.

Cheers Tim

Solution

I think this is what you are looking for:

FormatPercent(1 + 0.000001, 1)

It formats as a percentage and the second argument (the 1 in this case) is the number of decimal points you want.

This is where I found it.

This is a very good resource for the Format function too.

Comments

[psychonomics]

Thanks to @pn7a for the answer below. I also found that you can specify your own format when using Format(), for example writing "0.0%" instead of "Percent". Format(1 + 0.000001, "0.0%")

[psychonomics]

I originally tried this, and it was fine without any text in the same cell. However it did not work when including text as well. Thanks for the pointer on the rounding. I will use Format() in both cases.

[K_B]

alright, good luck, I see the other answer got you on your way!

[Steven Martin]

Wait a second, isnt formatting the cell meant to just change how the cell looks , not truncate the value in the cell like this is doing? Theres a difference between .NumberFormat = "0.00%" and FormatPercent(range, 2)

[pn7a]

@StevenMartin Hey Steven, I posted this a very long time ago so my memory is a bit hazy. I agree with your point but the question indicates appending the "formatted" percentage value to the string "Text:" and assigning it to the oRange variable. For this reason I suggested formatting dvalue instead of creating a custom cell formatting and applying it to the target cell.