Best way to handle singular/plural in messages

[JenniferMurphy]

Is there a really good way to generate messages in VBA that are grammatically correct with regard to singular and plural nouns with variable numbers?

I have a UDF that calculates how many wins a team needs to move into first place. I'd like messages like these:

• Just 3 wins needed
• Just 2 wins needed
• Just 1 win needed

I know I can do it with an IF statement, but then I have two places to edit if I change the message. What I have been doing is:

• Wins needed: 3
• Wins needed: 2
• Wins needed: 1

That sorta works, but I'd prefer the first set if there is an easy way to achieve it.

Thanks

 

[Zot]

You just need to remove s which can be handled with single line IF statement. I'm not sure if you need to edit two places with this simple demo.

Sub test()

strwin = " wins"
x = Range("A1")

If x = 1 Then strwin = Left(strwin, Len(strwin) - 1)
Statement = "Just " & x & strwin & " needed"
Range("C1") = Statement

End Sub

 

[JenniferMurphy]

You just need to remove s which can be handled with single line IF statement. I'm not sure if you need to edit two places with this simple demo.

Sub test()

strwin = " wins"
x = Range("A1")

If x = 1 Then strwin = Left(strwin, Len(strwin) - 1)
Statement = "Just " & x &strwin & " needed"
Range("C1") = Statement

End Sub

Thanks.

And here's another solution that does not involve an If statement:

wins=0
?"win" & left("s",1+(wins=1))
wins

wins=1
?"win" & left("s",1+(wins=1))
win

wins=2
?"win" & left("s",1+(wins=1))
wins

wins=10
?"win" & left("s",1+(wins=1))
wins

It can be used inline or in a separate line.

 

[Norie]

You could use Iif.

wins = 1
msg = "Just " & wins & " win" & IIf(wins > 1, "s", "") & " needed."
Debug.Print (msg)

wins = 2
msg = "Just " & wins & " win" & IIf(wins > 1, "s", "") & " needed."
Debug.Print (msg)

 

[JenniferMurphy]

You could use Iif.

wins = 1
msg = "Just " & wins & " win" & IIf(wins > 1, "s", "") & " needed."
Debug.Print (msg)

wins = 2
msg = "Just " & wins & " win" & IIf(wins > 1, "s", "") & " needed."
Debug.Print (msg)

Very nice!

I like it better than my solution.

Is the first "I" for "Insert" -- Insert If?

 

[Norie]

It's immediate, so Iif is an immediate if.

 

[JenniferMurphy]

It's immediate, so Iif is an immediate if.

Aha! That's makes sense. Thanks