Although there are formulae for expressing numbers as ordinals, the resulting output is a string - which makes it had to evaluate dates where the day is expressed as an ordinal.
The following two sub-routines, placed in the relevant worksheet’s code module, together with the third sub-routine and the accompanying function in a standard module, will automatically apply ordinal formatting to values entered into a range named "Ordinal":
Code:
Private Sub Worksheet_Calculate()
Ordinals ActiveSheet.Range("Ordinal")
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Ordinals Target
End Sub
Public NmRng As String
Sub Ordinals(ByVal Target As Range)
Dim oCell As Range
If NmRng <> "" Then _
ActiveWorkbook.Names.Add Name:="Ordinal", _
RefersTo:=ActiveWorkbook.Names.Item("Ordinal") & "," & NmRng
If Intersect(Target, ActiveSheet.Range("Ordinal")) Is Nothing Then Exit Sub
On Error Resume Next
For Each oCell In Target
If IsNumeric(oCell.Value) Then oCell.NumberFormat = OrdFmt(oCell.Value)
Next
End Sub
Function OrdFmt(ByVal Num As Long) As String
Dim Cell As Range
If IsNumeric(Cell.Value) Then
OrdFmt = "#""" & Mid$("thstndrdthththththth", 1 - 2 * _
((Cell.Value) Mod 10) * (Abs((Cell.Value) Mod 100 - 12) > 1), 2) & """"
End If
End Function
One limitation of User-Defined Functions (UDFs) is that they can only change the value displayed in the cell that contains the formula. This means you can’t use the UDF to change a cell’s number format or add it to a named range. They can, however, set a variable's properties, and this provides a workaround. By setting the ‘NmRng’ variable above, you can have a UDF that indirectly adds the target cell's address to the named range and exploit the 'Worksheet_Change' and 'Worksheet_Calculate' events to apply ordinal number formatting to a cell with a formula like: =OrdVal(A1):
Code:
Function OrdVal(ByVal Num As Long) As Long
Application.Volatile
NmRng = ActiveSheet.Name & "!" & Selection.Address
If InStr(ActiveWorkbook.Names.Item("Ordinal"), NmRng) <> 0 Then NmRng = ""
OrdVal = Num
End Function
Alternatively, the UDF below can use a formula like =Ordinal (A1) or =Ordinal(37) for any cell on any worksheet to which the event-driven code is attached.
Code:
Function Ordinal(ByVal Num As Long) As Long
Ordinal = Num
End Function
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
On Error Resume Next
For Each Cell In ActiveSheet.UsedRange
If UCase(Cell.Formula) Like "=ORDINAL(?*)" Then
Cell.NumberFormat = "#,#""" & Mid$("thstndrdthththththth", 1 - 2 * _
((Cell.Value) Mod 10) * (Abs((Cell.Value) Mod 100 - 12) > 1), 2) & """"
End If
Next Cell
End Sub
The underlying values remain available for use as numbers in other formulae.
Of course, if only a limited range of cells in a large workbook can have this kind of formula, it would be wise to narrow the event's target range to just that. The above approach also allows a mix of ordinal and ordinary values to coexist in the target range.
Note: Decimal values expressed as ordinals are based on the nearest integer. As well, when driven by the Ordinal Function, the values are converted to strings.
