I often need to determine the ordinal indicator (st/nd/rd/th) associated with a number, especially when I am working with dates. In this post, we will learn to write a simple user defined function in VBA, which will identify the ordinal indicator of an integer. Since the logic is quite straightforward, let us see how we can use the different control structures available in VBA.
A quick refresher on English Ordinal Indicators
The general rule is: numbers ending with 1, 2 and 3, get the ordinal indicators ‘st’, ‘nd’ and ‘rd’ respectively. The exceptions to the previous rule are numbers ending with 11, 12 and 13, which get the ‘th’ indicator.
The rest of the numbers get the ‘th’ indicator.
What are control Structures?
For those of you who are new to programming, in general, any code is executed one line after another. And control structures let you break that rule. They let us jump around unconditionally or conditionally. They even let us keep looping through lines. We will be focussing only on the control structures that let us jump around conditionally, which are generally referred to as Decision Structures.
The two popular decision structures available in VBA are:
Follow the links in the list for a detailed account on using these control structures. Although these links are related to VB.NET, and not VBA specifically, almost all the principles apply.
I highly recommend reading about the Select Case statement, as it allows you to implement complex decisions with minimal lines of code. I have used some of these principles in this post, and in the end, I will leave you with a weird way of using the Select Case statement, which I prefer over the If… Then… ElseIf… Else… structure.
Now let us take a look at how we can use the control structures to fetch the ordinal indicator of a number. Notice that I am using the Long data type to accept a number as it will let me work even with very large numbers. The integer data type would suffice for most use cases though.
If… Then… Else
Here is the UDF written using the If… Then… Else structure:
Function OrdinalIndicator(ByVal Number As Long) As String
If Number Mod 100 = 11 Or _
Number Mod 100 = 12 Or _
Number Mod 100 = 13 Then
OrdinalIndicator = "th"
ElseIf Number Mod 10 = 1 Then
OrdinalIndicator = "st"
ElseIf Number Mod 10 = 2 Then
OrdinalIndicator = "st"
ElseIf Number Mod 10 = 3 Then
OrdinalIndicator = "rd"
Else
OrdinalIndicator = "th"
End If
OrdinalIndicator = Number & OrdinalIndicator
End Function
Select… Case
Here is the UDF written using the Select… Case Structure:
Function OrdinalIndicator(ByVal Number As Long) As String
Select Case Number Mod 100
Case 11 To 13
OrdinalIndicator = "th"
Case Else
Select Case Number Mod 10
Case 1
OrdinalIndicator = "st"
Case 2
OrdinalIndicator = "nd"
Case 3
OrdinalIndicator = "rd"
Case Else
OrdinalIndicator = "th"
End Select
End Select
OrdinalIndicator = Number & OrdinalIndicator
End Function
Now that you have seen the regular implementation of the Select.. Case statement, let us look at the quirky way I promised earlier. You can pass TRUE as the test expression to the Select statement, and then use Boolean expression beside the Case statements to convert the Select… Case control structure into a compact If… Then… ElseIf… Else structure.
Let me show you what I mean:
Function OrdinalIndicator(ByVal Number As Long) As String
Select Case True
Case Number Mod 100 = 11, Number Mod 100 = 12, _
Number Mod 100 = 13
OrdinalIndicator = "th"
Case Number Mod 10 = 1
OrdinalIndicator = "st"
Case Number Mod 10 = 2
OrdinalIndicator = "nd"
Case Number Mod 10 = 3
OrdinalIndicator = "rd"
Case Else
OrdinalIndicator = "th"
End Select
OrdinalIndicator = Number & OrdinalIndicator
End Function
Downloads
I hope that you learnt something new about control structures today. Click on the link below to download a workbook, which contains the three UDFs.
