The Pluralize Function

How many times have you created messages for users in your Access applications that looked like these:

"2 record(s) inserted."
"3 watch(es) added to cart."
"You are about to create 1 new party(ies)."
"There is/are 5 day(s) until the task is due."
"You gambled your life savings and won -$25,000."

Yes, you degenerate, that's a negative sign before the $25K. That bet didn't work out so well for you. On the bright side, improving your user messages to handle pluralization is a lot easier than beating the house in Las Vegas.

Read on for one of the quickest, low-cost wins you can get in VBA: the Pluralize() function.

It's OK, I'm Lazy, Too

Let's be honest, the only reason you show user messages like the ones I listed is because you're lazy.

And that's OK! I'm lazy, too. Lazy programmers tend to be good programmers, so long as they're lazy in the right ways. I hate typing the same boilerplate code over and over again.

Given the choice between...

MsgBox x & " record(s) inserted."

...and...

MsgBox x & " record" & IIf(x <> 1, "s", "") & " inserted."

...I'm going to take the latter approach all of about three times before I say, "The heck with that!" and go back to throwing parentheses around my esses.

My problem is that the user experience really is better with the second approach. So, being a lazy programmer, I thought to myself, what if I could get the better user experience without much extra work as a programmer?

Necessity may be the mother of invention, but laziness is its father.

Better Developer Experience

Invigorated by the challenge, I sloughed off my laziness and set to work devising a solution.

The Pluralize() function formats a phrase to make verbs agree in number.

I want to point out a couple of things:

No ampersands (&) needed for string concatenation
The string we pass to the function looks a lot like the one from our original (lazy) approach

Better User Messages

Let's redo the user messages from the beginning of the article using the Pluralize() function.

Note: To minimize the width of the screenshots for the web, I'm using line continuation (_) and statement joining (:) characters in my examples below. I'm highlighting the important parts–the arguments to the function–in yellow. The rest is boilerplate.

Simple Plurals

For words where the plural form is created by appending one or more characters, those characters get added in square brackets.

Irregular Plurals

For words that have two different forms for singular and plural–where characters are changed and not simply appended–you use square brackets and a forward slash in between the characters used to create the singular and plural forms.

Different Words

The square brackets plus forward slash approach also works if there are two completely different words for the singular and plural forms.

Alternate Number Token

Pluralizing your messages shouldn't put a crimp in your social media game. Preserve those sweet, sweet hashtags by specifying an alternate number token for your message. I'm using the at sign (@) here, but any character will do as long as it doesn't appear elsewhere in your text.

Different Words for Positive and Negative

Using curly braces and a forward slash, you can provide different words for positive and negative numbers, such as won versus lost. The first word in the group applies to positive numbers (and zero), while the second word applies to negative numbers.

To avoid showing parentheses or a minus sign for negative numbers, the function accepts a custom number format. Custom number formats in VBA can have multiple groups separated with semicolons.

In the example above, the format breaks down as follows:

$#,##0: positive numbers
$#,##0: negative numbers (i.e., there is no difference in formatting for positive/negative numbers)
nothing: zero

Presented Without Comment

The Pluralize Function Code

The function includes verifiable doc tests in the comments. It also has a dependency on my RegExReplace function, which I include below the Pluralize() function for convenience.

'---------------------------------------------------------------------------------------
' Procedure : Pluralize
' Author    : Mike
' Date      : 10/21/2010 - 7/24/2014
' Purpose   : Formats a phrase to make verbs agree in number.
' Notes     : To substitute the absolute value of the number for numbers that can be
'               positive or negative, use a custom number format that includes
'               both positive and negative formats; e.g., "#;#".
' Usage     : Msg = "There [is/are] # record[s].  [It/They] consist[s/] of # part[y/ies] each."
'>>> Pluralize("There [is/are] # record[s].  [It/They] consist[s/] of # part[y/ies] each.", 1)
' There is 1 record.  It consists of 1 party each.
'>>> Pluralize("There [is/are] # record[s].  [It/They] consist[s/] of # part[y/ies] each.", 6)
' There are 6 records.  They consist of 6 parties each.
'>>> Pluralize("There was a {gain/loss} of # dollar[s].", -50, "#", "#;#")
' There was a loss of 50 dollars.
'>>> Pluralize("I {won/lost} # at the fair.  {I was thrilled./I'll never learn.}", 20, "#", "Currency")
' I won $20.00 at the fair.  I was thrilled.
'>>> Pluralize("There [is/are] # {more/less} finger[s] on his hand after the surgery.", -1, "#", "#;#")
' There is 1 less finger on his hand after the surgery.
'---------------------------------------------------------------------------------------
'
Function Pluralize(Text As String, Num As Variant, _
                   Optional NumToken As String = "#", _
                   Optional NumFormat As String = "")
    
    Const OpeningBracket As String = "\["
    Const ClosingBracket As String = "\]"
    Const OpeningBrace As String = "\{"
    Const ClosingBrace As String = "\}"
    Const DividingSlash As String = "/"
    Const CharGroup As String = "([^\]]*)"  'Group of 0 or more characters not equal to closing bracket
    Const BraceGroup As String = "([^\/\}]*)" 'Group of 0 or more characters not equal to closing brace or dividing slash

    Dim IsPlural As Boolean, IsNegative As Boolean
    If IsNumeric(Num) Then
        IsPlural = (Abs(Num) <> 1)
        IsNegative = (Num < 0)
    End If
    
    Dim Msg As String, Pattern As String
    Msg = Text
    
    'Replace the number token with the actual number
    Msg = Replace(Msg, NumToken, Format(Num, NumFormat))
    
    'Replace [y/ies] style references
    Pattern = OpeningBracket & CharGroup & DividingSlash & CharGroup & ClosingBracket
    Msg = RegExReplace(Pattern, Msg, "$" & IIf(IsPlural, 2, 1))
    
    'Replace [s] style references
    Pattern = OpeningBracket & CharGroup & ClosingBracket
    Msg = RegExReplace(Pattern, Msg, IIf(IsPlural, "$1", ""))
        
    'Replace {gain/loss} style references
    Pattern = OpeningBrace & BraceGroup & DividingSlash & BraceGroup & ClosingBrace
    Msg = RegExReplace(Pattern, Msg, "$" & IIf(IsNegative, 2, 1))
        
    Pluralize = Msg
    
End Function


'---------------------------------------------------------------------------------------
' Procedure : RegExReplace
' Author    : Mike Wolfe <mike@nolongerset.com>
' Date      : 11/4/2010
' Source    : https://nolongerset.com/now-you-have-two-problems/
' Purpose   : Attempts to replace text in the TextToSearch with text and back references
'               from the ReplacePattern for any matches found using SearchPattern.
' Notes     - If no matches are found, TextToSearch is returned unaltered.  To get
'               specific info from a string, use RegExExtract instead.
'>>> RegExReplace("(.*)(\d{3})[\)\s.-](\d{3})[\s.-](\d{4})(.*)", "My phone # is 570.555.1234.", "$1($2)$3-$4$5")
'My phone # is (570)555-1234.
'---------------------------------------------------------------------------------------
'
Function RegExReplace(SearchPattern As String, TextToSearch As String, ReplacePattern As String, _
                      Optional GlobalReplace As Boolean = True, _
                      Optional IgnoreCase As Boolean = False, _
                      Optional MultiLine As Boolean = False) As String
Dim RE As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = MultiLine
        .Global = GlobalReplace
        .IgnoreCase = IgnoreCase
        .Pattern = SearchPattern
    End With
    
    RegExReplace = RE.Replace(TextToSearch, ReplacePattern)
End Function

A Final Note About Languages

I developed this function to work primarily with English. It should be flexible enough to work with many other languages as it supports two completely different singular and plural forms, but I make no guarantees.

It will not work well with languages that have more than two plural forms. For example, some languages distinguish among zero, one, two, and more than two.

It will not work well with languages that don't treat zero the same as two or more (the way English does).

Localization is hard. Your mileage (kilometerage?) may vary.

Referenced articles

Python-inspired Doc Tests in VBA

Doc tests are not a replacement for unit or integration testing. But they do provide the best return on investment (ROI) of any type of test, mostly because the effort to write them is near zero.

No Longer SetMike Wolfe

Now you have two problems

Some people, when confronted with a problem, think “I know, I’ll use regular expressions.” Now they have two problems. --Jamie Zawinski

No Longer SetMike Wolfe

Image by Damiano Amato from Pixabay