Formatting Percentages Redux

[Gordon Bentley-Mix]

Ages ago I asked a question about formatting percentages (specifically in
TextBox controls on a UserForm) to display a plus or minus sign as
appropriate - something that the 'FormatPercent' function doesn't support. I
received a most helpful answer from either Jay or Doug - I've slept a few
times since then and can't remember exactly who it was - describing a method
for using the basic 'Format' function to meet my needs, and things have been
working a treat ever since.

Until now...

Here's the situation. I need a method whereby percentage values are
displayed with up to two digits after the decimal point but *only* if there
are digits to be displayed; for example: "50%", "33.3%", "6.25%".

Obviously FormatPercent isn't going to work for this because it only
supports specifying the number of digits after the decimal point, which
means a fixed number of digits (padded with trailing zeros) are always
displayed regardless; for example: either "50.00%", "33.30%", "6.25%" or
"50.0%", "33.3%", "6.2%" or "50%", "33%", "6%". Clearly less than ideal...

OTOH, using something like

Format(TextBox1.Value), "0.##%")

doesn't quite cut it either. The hash marks (#) do work as expected with
values that have one or two digits after the decimal point like "33.3%" and
"6.25%", but unfortunately I get things like "50.%" (note the extraneous
decimal point) when there are *no* digits after the decimal point.

Other than a "brute force" approach of doing something like parsing the
value using the Mid function to look for a decimal point in the penultimate
position and removing it, does anyone have a way around this? Or should I
get out my big hammer and change my username to Thor?

 

[Jay Freedman]

Hi Gordon,

How about this... Check whether the value represents an integer
percentage, then use a Format with the necessary string.

If (100 * TextBox1.Value - Int(100 * TextBox1.Value)) < 0.00001
Then
Debug.Print Format(TextBox1.Value, "0%")
Else
Debug.Print Format(TextBox1.Value, "0.##%")
End If

A test for "= 0" doesn't work because the difference (due to internal
representation error) can be 1E-6 to 2E-6.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Tony Jollans]

The Format function does misbehave rather badly here, but you have a single
case to trap so I'd just check for it - perhaps

Replace(Format(TextBox1.Value, "#.##%"), ".%", "%")

 

[Gordon Bentley-Mix]

Cheers guys! I'll give both methods a shot and see which one I like better
('cause I know they both work equally well).

See? I knew there'd be somebody out there who's smarter than I am. ;-P

 

[Gordon Bentley-Mix]

My final code - along with a bit of "history" of my development efforts. (I
know the NG will make it a bit messy to read. Sorry. Not much I can do about
it.)

Private Function fcnFormatEFPercent(PercentValue As String) As String
Dim Temp As String
If Len(PercentValue) > 0 Then
If IsNumeric(fcnExtractNumber(PercentValue)) = True Then
' *** THIS DOESN'T WORK BECAUSE FORMATPERCENT PADS WITH TRAILING
ZEROS ***
' Then Temp = FormatPercent((fcnExtractNumber(PercentValue)) /
100, 2)
' *** THIS DOESN'T WORK BECAUSE NO DIGITS ARE DISPLAYED AFTER THE
DECIMAL POINT ***
' Then Temp = FormatPercent((fcnExtractNumber(PercentValue)) /
100, 0)
' *** THIS DOESN'T WORK BECAUSE FORMAT ALWAYS PUTS IN THE DECIMAL
POINT EVEN IF THERE ARE NO TRAILING VALUES ***
' Then Temp = Format(fcnExtractNumber(PercentValue) / 100,
"0.##%")
' *** JAY'S SOL'N. THIS WORKS (WITH A SMALL MOD) BUT SEEMS A BIT
CLUNKY ***
' *** THIS IS THE MODDED PART. DUNNO WHY JAY MULTIPLIES THE VALUE
BY 100. FAILS WITH 2 DIGITS ***
' If (100 * fcnExtractNumber(PercentValue) - Int(100 *
fcnExtractNumber(PercentValue))) < 0.00001 Then
' *** BUT WORKS OK WITH JUST THE RAW VALUES. STILL CLUNKY THO ***
' If (fcnExtractNumber(PercentValue) -
Int(fcnExtractNumber(PercentValue))) < 0.00001 Then
' Temp = Format(fcnExtractNumber(PercentValue) / 100, "0%")
' Else
' Temp = Format(fcnExtractNumber(PercentValue) / 100,
"0.##%")
' End If
' *** TONY'S SOL'N. THIS DOES WORK - AND VERY ELEGANTLY, TOO! ***
Temp = Replace(Format(fcnExtractNumber(PercentValue) / 100,
"0.##%"), ".%", "%")
Else: Temp = UCase(PercentValue)
End If
Else: Temp = ""
End If
fcnFormatEFPercent = Temp
End Function

If you can wade through all the noise and the "belt-and-braces" redundancy -
there are a lot of UserForm-related conditions and business rules to
accommodate, and I also employ a custom function to address the problems
with getting just the "number" part out of a formatted value - you'll see
that I went with Tony's solution in the end. (Sorry Jay! No offense. It just
seemed a bit more elegant.)

Note that Jay's solution as originally written also failed with values like
"6.25", which ended up as just "6%". It seems to have something to do with
multiplying the value by 100 before doing the comparison, although why it
would continue to work as expected with values like "33.3" is a mystery to
me. Just comparing the raw values solved the problem.

In addition to resolving my original issue, this exercise has also refreshed
my acquaintance with the Replace function. I remember using it just once
before in a project a few years ago but had since forgotten about it. As a
result, I think there may have been a few cases where I've been using the
"Thor" approach to enforce/apply formatting to values when I could have been
a bit more "Tinkerbelle" in my touch. Thanks for reminding me Tony!
--
Cheers!

Gordon Bentley-Mix

Cheers guys! I'll give both methods a shot and see which one I like better
('cause I know they both work equally well).

See? I knew there'd be somebody out there who's smarter than I am. ;-P