none
integrating age function into subs RRS feed

  • Question

  • i simply don't know what i'm doing here. I'm trying to get an age by using birthdate with content controls. here's my attempt. my ".text" is getting "object required" errors
    Option Explicit
    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim varBirthDate As Variant
    Dim ccbirthdates As Word.ContentControls
    Dim ccbirthdate As Word.ContentControl
    
    
    Set ccbirthdates = ActiveDocument.SelectContentControlsByTitle("birthdate")
    Set varBirthDate = ccbirthdate.Range.Text
    
    For Each ccbirthdate In ccbirthdates
    Age (varBirthDate)
    Next
    
    End Sub
    
    Function Age(varBirthDate As Variant) As Integer
           Dim varAge As Variant
    
          If IsNull(varBirthDate) Then Age = 0: Exit Function
    
          varAge = DateDiff("yyyy", varBirthDate, Now)
          If Date < DateSerial(Year(Now), Month(varBirthDate), _
                               Day(varBirthDate)) Then
             varAge = varAge - 1
          End If
          Age = CInt(varAge)
        End Function
    

    Wednesday, August 1, 2012 10:12 AM

Answers

  • Alright, well i was hoping to avoid this route unless forced, but i guess it came to it. I couldn't export the info to a content control text box, but i could to a combo box as the only entry and have it automatically selected. Not the most glamorous solution, but effective nonetheless. I suppose exporting to a selection range or text box might have been possible but i really feel more in control with content controls.
    Option Explicit
    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim varBirthDate As Variant
    Dim varAge As Variant
    Dim ccbirthdates As Word.ContentControls
    Dim ccbirthdate As Word.ContentControl
    Dim ccages As Word.ContentControls
    Dim ccage As Word.ContentControl
    Dim Age As Integer
    
    Set ccbirthdates = ActiveDocument.SelectContentControlsByTitle("birthdate")
    For Each ccbirthdate In ccbirthdates
        varBirthDate = CDate(ccbirthdate.Range.Text)
        varAge = DateDiff("yyyy", varBirthDate, Now)
          If Date < DateSerial(Year(Now), Month(varBirthDate), Day(varBirthDate)) Then
             varAge = varAge - 1
          End If
    
          Age = CInt(varAge)
          Set ccages = ActiveDocument.SelectContentControlsByTitle("age")
          For Each ccage In ccages
          ccage.DropdownListEntries.Clear
          ccage.DropdownListEntries.Add [Age]
          ccage.DropdownListEntries.Item(1).Select
          Next
    Next
    End Sub
    
    The actual age function came from: http://msdn.microsoft.com/en-us/library/office/bb243818(v=office.12).aspx
    • Marked as answer by tonayie Wednesday, August 1, 2012 11:34 PM
    Wednesday, August 1, 2012 11:34 PM

All replies

  • One issue is that this command:

    Set varBirthDate = ccbirthdate.Range.Text

    Is issued before ccbirthdate has been initialized. That's what's throwing your error. If you want to send the content control's text (i.e. a string) to your Age function, I think this would work:

    Set ccbirthdates = ActiveDocument.SelectContentControlsByTitle("birthdate")
    
    For Each ccbirthdate In ccbirthdates
    Age (ccbirthdate.Range.Text)
    Next

    However,  I don't know if your Age function would work, because I'm not too familiar with Visual Basic and DateDiff.

    It's not a problem per se, but many programmers would call it 'unsafe' to pass a 'Variant' to a function, because while it may compile, human error may then mean you feed it the wrong data type. If things go wrong, it makes it harder to find the cause.


    Wednesday, August 1, 2012 4:37 PM
  • Thanks a lot for the response. I think I've been making a lot of progress by myself. I tried jamming in the function into subroutine for now and it works pretty well. my issue now is simply to write the result as a text box content control text - or just about anything that will show a string. here's the revised code.

    Option Explicit
    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim varBirthDate As Variant
    Dim varAge As Variant
    Dim ccbirthdates As Word.ContentControls
    Dim ccbirthdate As Word.ContentControl
    Dim ccages As Word.ContentControls
    Dim ccage As Word.ContentControl
    Dim Age As String
    
    Set ccbirthdates = ActiveDocument.SelectContentControlsByTitle("birthdate")
    For Each ccbirthdate In ccbirthdates
        varBirthDate = CDate(ccbirthdate.Range.Text)
        varAge = DateDiff("yyyy", varBirthDate, Now)
          If Date < DateSerial(Year(Now), Month(varBirthDate), _
                               Day(varBirthDate)) Then
             varAge = varAge - 1
          End If
          Age = CInt(varAge)
    Next
    End Sub


    • Edited by tonayie Wednesday, August 1, 2012 4:48 PM
    • Proposed as answer by JosephFox Wednesday, August 1, 2012 5:28 PM
    Wednesday, August 1, 2012 4:48 PM
  • Well, good.

    Easiest way to display text is with a messagebox ( MsgBox(Age) )

    Edit: But probably you want something more integrated. Start a new thread if you have difficulty with that; new threads get more attention, so it's preferable as long as its a new question.

    • Edited by JosephFox Wednesday, August 1, 2012 4:55 PM
    Wednesday, August 1, 2012 4:53 PM
  • thanks Josephfox. you're right, i did want it a little more integrated - something that could be printed out. the task seems simple though, i'd rather not waste more forum space than i have already ^^.
    Wednesday, August 1, 2012 5:16 PM
  • Don't worry about wasting space...if you've looked around and can't work something out, it's always worth asking. Forums are all about not suffering in silence...you'd probably have got a quicker response to this except some regular posters appear to be on holiday.
    Wednesday, August 1, 2012 5:33 PM
  • Alright, well i was hoping to avoid this route unless forced, but i guess it came to it. I couldn't export the info to a content control text box, but i could to a combo box as the only entry and have it automatically selected. Not the most glamorous solution, but effective nonetheless. I suppose exporting to a selection range or text box might have been possible but i really feel more in control with content controls.
    Option Explicit
    Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
    Dim varBirthDate As Variant
    Dim varAge As Variant
    Dim ccbirthdates As Word.ContentControls
    Dim ccbirthdate As Word.ContentControl
    Dim ccages As Word.ContentControls
    Dim ccage As Word.ContentControl
    Dim Age As Integer
    
    Set ccbirthdates = ActiveDocument.SelectContentControlsByTitle("birthdate")
    For Each ccbirthdate In ccbirthdates
        varBirthDate = CDate(ccbirthdate.Range.Text)
        varAge = DateDiff("yyyy", varBirthDate, Now)
          If Date < DateSerial(Year(Now), Month(varBirthDate), Day(varBirthDate)) Then
             varAge = varAge - 1
          End If
    
          Age = CInt(varAge)
          Set ccages = ActiveDocument.SelectContentControlsByTitle("age")
          For Each ccage In ccages
          ccage.DropdownListEntries.Clear
          ccage.DropdownListEntries.Add [Age]
          ccage.DropdownListEntries.Item(1).Select
          Next
    Next
    End Sub
    
    The actual age function came from: http://msdn.microsoft.com/en-us/library/office/bb243818(v=office.12).aspx
    • Marked as answer by tonayie Wednesday, August 1, 2012 11:34 PM
    Wednesday, August 1, 2012 11:34 PM