• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 804
  • Last Modified:

word VBA macro needs ordinal indicator

I would like to add the current date in the format such as 8th February 2009 to my Word 2007 template.  I can get 8 February 2009 but have no idea how to get the ordinal indicators (st nd rd th) added.
Sub Autonew()
'
' Autonew Macro
'
'
ActiveDocument.Bookmarks("date").Select
Selection.InsertDateTime DateTimeFormat:="d MMMM yyyy", InsertAsField:=False
 
End Sub

Open in new window

0
EugeneGardner
Asked:
EugeneGardner
  • 7
  • 4
  • 3
4 Solutions
 
Chris BottomleyCommented:
Hello EugeneGardner,

You could create a function in a code module and then call it as for example

selection.insertafter ordinal(date())

Regards,
Chris
Function ordinal(dt As Date) As String
Dim Day_Number As Integer 
    ordinal = Format(dt, "d""th"" mmmm yyyy")
    Day_Number = Day(dt)
    If Day_Number = 2 Or Day_Number = 22 Then
        ordinal = Format(dt, "d""nd"" mmmm yyyy")
    ElseIf Day_Number = 3 Or Day_Number = 23 Then
        ordinal = Format(dt, "d""rd"" mmmm yyyy")
    ElseIf Day_Number = 1 Or Day_Number = 21 Or Day_Number = 31 Then
        ordinal = Format(dt, "d""st"" mmmm yyyy")
    End If 
End Function

Open in new window

0
 
harfangCommented:
The following code should do what you need. The first function is a custom format function for dates with ordinal numbers, the second is an improved version of your Autonew macro, in that it tests for the existence of the bookmark and recreates it after overwriting it.

Good luck!
(°v°)
Option Explicit
 
Function MyDateFormat(pvarDate)
    
    Dim strReturn As String
    
    If Not IsDate(pvarDate) Then
        MyDateFormat = Null
        Exit Function
    End If
    
    Select Case Day(pvarDate)
        Case 1, 21, 31:     strReturn = "st"
        Case 2, 22:         strReturn = "nd"
        Case 3, 23:         strReturn = "rd"
        Case Else:          strReturn = "th"
    End Select
    strReturn = Day(pvarDate) & strReturn & " of " _
        & Format(pvarDate, "mmmm yyyy")
    MyDateFormat = strReturn
        
End Function
 
Sub Autonew()
    
    Dim rngDate As Range
    
    With ActiveDocument.Bookmarks
        If .Exists("date") Then
            Set rngDate = .Item("date").Range
            rngDate.Text = MyDateFormat(Date)
            .Add "date", rngDate
        End If
    End With
    
End Sub

Open in new window

0
 
harfangCommented:
Sorry Chris, your function is totally equivalent... (°v°)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Chris BottomleyCommented:
Thanks for that but as ever the beauty of the site is the number of permutations, and yours is a different approach so it's a question of what best meets the authors needs.

Chris
0
 
EugeneGardnerAuthor Commented:
That's great - thanks.  Excuse the cheek of adding to the question at this late stage, but I would really like to superscript the ordinal indicator.  I'm sure this must be possible by setting a marker, but can't find how that could be done.  Any ideas ?
Adding 75 to the points.
0
 
Chris BottomleyCommented:
try calling affixOrdinal as attached.

Chris
Function affixOrdinal(dt As Date) As Boolean
Dim Day_Number As Integer
Dim ordinal As String
 
    ordinal = "th"
    Day_Number = Day(dt)
    If Day_Number = 2 Or Day_Number = 22 Then
        ordinal = "nd"
    ElseIf Day_Number = 3 Or Day_Number = 23 Then
        ordinal = "rd"
    ElseIf Day_Number = 1 Or Day_Number = 21 Or Day_Number = 31 Then
        ordinal = "st"
    End If
    Selection.TypeText Text:=Day_Number
    With Selection.Font
        .Superscript = True
    End With
    Selection.TypeText Text:=ordinal
    With Selection.Font
        .Superscript = False
    End With
    Selection.TypeText Text:=Format(dt, " mmmm yyyy")
 
End Function

Open in new window

0
 
EugeneGardnerAuthor Commented:
Thanks Chris.  I have used harfang's code as I found it easier to follow.  I tried incorporating what I thought was the superscript  part of the last reply but I'm obviously missing something as nothing gets superscripted.   Did I put the 'Selection.Font.Superscript = True' in the wrong place or is something else wrong ?
Option Explicit
 
Function MyDateFormat(pvarDate)
    
    Dim strReturn As String
    
    If Not IsDate(pvarDate) Then
        MyDateFormat = Null
        Exit Function
    End If
    
    Select Case Day(pvarDate)
        Case 1, 21, 31:     strReturn = "st"
        Case 2, 22:         strReturn = "nd"
        Case 3, 23:         strReturn = "rd"
        Case Else:          strReturn = "th"
    End Select
  
    Selection.TypeText (strReturn)
    Selection.Font.Superscript = True
      
    strReturn = Day(pvarDate) & strReturn & " " _
        & Format(pvarDate, "mmmm yyyy")
    MyDateFormat = strReturn
        
End Function
 
Sub Autonew()
    
    Dim rngDate As Range
    
    With ActiveDocument.Bookmarks
        If .Exists("date") Then
            Set rngDate = .Item("date").Range
            rngDate.Text = MyDateFormat(Date)
            .Add "date", rngDate
        End If
    End With
    
End Sub

Open in new window

0
 
Chris BottomleyCommented:
Sorry to appear negative but I don't like the approach.  As presented it seems to return some data direct into the document and some via the function.  In my experience this part and part makes maintenance difficult.  Also in the function it looks as though everything is being set to superscript not just the ordinal .. and I don't think on first sight that the returned string is being used by autonew.

That said I am happy to help as you want but maybay Harfang is best to help resolve your changes in his sub.

Chris
0
 
Chris BottomleyCommented:
FWIW, my code being hard to follow ... ouch!

It calculates the ordinal itself then outputs day, swirtches to superscripts outputs the ordinal then switches back to normal and outputs the month and year.  I can't conceive any way of making it easier!

Chris
0
 
EugeneGardnerAuthor Commented:
Sorry - no ouch intended, just that for my level of knowledge, I need it spelled out really simply even if efficiency is sacrificed as I am not a programmer.
0
 
Chris BottomleyCommented:
:o)

I am generally not a proponent of efficiency in execution at the price of of understanding and whilst I suggested you seek Harfangs 'improvements' to your preferred form I am suspicious that by the time the switching between superscript and normal is said and done that it will be somewhat complicated rather than as I see it the step by step simplicity of the sub I posted.

I look forward to being proved wrong however as in the process I will no doubt learn something new.

Chris
0
 
harfangCommented:
> Sorry to appear negative but I don't like the approach.

No problem. It turns out you are right, since the function approach does not allow to change the formatting of a portion of the date.

I rewrote the macro as a stand-alone sub, while keeping the bookmark management intact. I really don't like to use the Selection for this, and so I tried to update the bookmark without actually selecting it.

EugeneGardner,

Please do not accept this as answer, except as assist if you use it. Chris gave you the answer first, and also provided for the additional request first, both times accurately.

Good luck
(°v°)
Sub Autonew()
    
    Dim strOrd As String
    Dim rngDate As Range
    
    ' check for bookmark
    If Not ActiveDocument.Bookmarks.Exists("date") Then
        MsgBox "Bookmark ""date"" not found!"
        Exit Sub
    End If
    
    ' choose ordinal abbreviation
    Select Case Day(Date)
        Case 1, 21, 31:     strOrd = "st"
        Case 2, 22:         strOrd = "nd"
        Case 3, 23:         strOrd = "rd"
        Case Else:          strOrd = "th"
    End Select
    
    ' store bookmark range
    Set rngDate = ActiveDocument.Bookmarks("date").Range
    
    ' replace with abbr.+space, superscripting 2 chars
    rngDate.Text = strOrd & " "
    rngDate.End = rngDate.End - 1
    rngDate.Font.Superscript = True
    rngDate.End = rngDate.End + 1
    
    ' add date around it
    rngDate.InsertBefore Day(Date)
    rngDate.InsertAfter Format(Date, "mmmm yyyy")
    
    ' recreate bookmark (for next time)
    ActiveDocument.Bookmarks.Add "date", rngDate
    
End Sub

Open in new window

0
 
EugeneGardnerAuthor Commented:
Thanks both.  FWIW I will use Harfang's solution as i find it easier to follow - the comments and spacing help.  
0
 
Chris BottomleyCommented:
That's fine as said it's up to you, but I assumed you wanted the ordinal in superscript not the whole data hence what you must have seen as complication in my code.

Chris
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now