Can a function or sub be called using a variable name?

macropod · #1 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

omahadivision · #2 12-03-2013, 11:08 AM

Thanks! I think I understand it except for the "Then" statement. Is that specific to a certain macro?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
object variable or with block variable not set	MJP143	Excel	1	02-11-2013 05:07 AM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM
excel where is procedure being called from	davids67	Excel Programming	1	11-18-2011 06:48 AM
What is this symbol called as?	Franky	Word	3	11-12-2011 08:16 AM
What is this right-click sub-menu called?	wornways	Word	3	08-13-2010 09:17 PM