Microsoft Office Forums - View Single Post - [Solved] Can a function or sub be called using a variable name?

macropod · 12-02-2013, 11:07 PM

Here's a simple demo of a Function taking an argument. The UDF below can use a formula like ‘=Ordinal (A1)’ or 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
For Each Cell In Target
  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
End Sub