I want to get the value of two Datepicker on VBA ms-Word and Calculate the difference with DateDiff

Question

I want to get the value of two Datepicker on VBA ms-Word and then use DateDiff to calculate the difference between two dates and make it display on the document. I can get the value of the Datepicker by using "ContentControl.Range.Text" but when I use it with DateDiff is saying "Type missmatch".

 Sub Test1()
'
' Test1 Macro
'
'
Dim doc As Document
Set doc = Application.ActiveDocument

Dim CCtrl As String
CCtrl = "Date1"

Dim chkExt As Boolean
chkExt = False

Dim control As ContentControl
For Each control In doc.ContentControls
    If control.Tag = CCtrl Then
        chkExt = True
        Exit For
    End If
Next

If chkExt = False Then
    MsgBox "Nothing"
    Exit Sub
End If

Dim ChangeType As Date
ChangeType = control.Range.Text

Debug.Print IsDate(ChangeType)

End Sub

UPDATE:I Find the problem The display of the date is affecting the code if the date is display this way is working fine Normal Format

but if it display in Thai format is not working Thai Format

so is there any workaround to make the display as Thai format but the value of it is the same as the normal one.

I found the Solution Thanks to my Friend so the way to do it is by split the date and then use DateDiff this way on the Document the date still show as thai format but still got the value to use in DateDiff Here the Code that work for me:

Sub Test1()
'
' Test1 Macro
'
'
Dim doc As Document
Set doc = Application.ActiveDocument

Dim CCtrl, CCtrl2 As String
CCtrl = "Date1"
CCtrl2 = "Date2"



Dim chkExt As Boolean
chkExt = False

Dim control As ContentControl
Dim control2 As ContentControl


    For Each control2 In doc.ContentControls
        If control2.Tag = CCtrl2 Then
        control2.DateDisplayFormat = "d ´´´´ bbbb"
         Dim Result1() As String
        Result1() = Split(control2.Range.Text)
        Dim MyClasses1 As New Collection
         MyClasses1.Add Item:="01", Key:="Á¡ÃÒ¤Á"
         MyClasses1.Add Item:="02", Key:="¡ØÁÀҾѹ"
         MyClasses1.Add Item:="03", Key:="ÁÕ¹Ò¤Á"
         MyClasses1.Add Item:="04", Key:="àÁÉÒ¹"
         MyClasses1.Add Item:="05", Key:="¾ÄÉÀÒ¤Á"
         MyClasses1.Add Item:="06", Key:="ÁԶعÒ¹"
         MyClasses1.Add Item:="07", Key:="¡Ã¡®Ò¤Á"
         MyClasses1.Add Item:="08", Key:="ÊÔ§ËÒ¤Á"
         MyClasses1.Add Item:="09", Key:="¡Ñ¹ÂÒ¹"
         MyClasses1.Add Item:="10", Key:="µØÅÒ¤Á"
         MyClasses1.Add Item:="11", Key:="¾ÄȨԡÒ¹"
         MyClasses1.Add Item:="12", Key:="¸Ñ¹ÇÒ¤Á"
         Dim engDate1 As String
         engDate1 = Result1(0) & "/" & MyClasses1(Result1(1)) & "/" & Result1(2)
         Debug.Print IsDate(engDate1)
        Exit For
        End If
    Next



    For Each control In doc.ContentControls
        'check if there is a tag
        If control.Tag = CCtrl Then
        control.DateDisplayFormat = "d ´´´´ bbbb"
        
        'For spliting Date as 00 00 00
        Dim Result() As String
        Result() = Split(control.Range.Text)
        
        'Creating Keys
        Dim MyClasses As New Collection
         MyClasses.Add Item:="01", Key:="Á¡ÃÒ¤Á"
         MyClasses.Add Item:="02", Key:="¡ØÁÀҾѹ"
         MyClasses.Add Item:="03", Key:="ÁÕ¹Ò¤Á"
         MyClasses.Add Item:="04", Key:="àÁÉÒ¹"
         MyClasses.Add Item:="05", Key:="¾ÄÉÀÒ¤Á"
         MyClasses.Add Item:="06", Key:="ÁԶعÒ¹"
         MyClasses.Add Item:="07", Key:="¡Ã¡®Ò¤Á"
         MyClasses.Add Item:="08", Key:="ÊÔ§ËÒ¤Á"
         MyClasses.Add Item:="09", Key:="¡Ñ¹ÂÒ¹"
         MyClasses.Add Item:="10", Key:="µØÅÒ¤Á"
         MyClasses.Add Item:="11", Key:="¾ÄȨԡÒ¹"
         MyClasses.Add Item:="12", Key:="¸Ñ¹ÇÒ¤Á"
        
        'Put together the Date
        Dim engDate As String
        engDate = Result(0) & "/" & MyClasses(Result(1)) & "/" & Result(2)
        Debug.Print IsDate(engDate)
        Exit For
        End If
    Next
     Dim ccdisplayDateDiff As ContentControl
     Dim displayDateDiff As String
     displayDateDiff = "displayDateDiff"
     For Each ccdisplayDateDiff In doc.ContentControls
        If ccdisplayDateDiff.Tag = displayDateDiff Then
         ccdisplayDateDiff.Range.Text = DateDiff("d", engDate, engDate1)
         Exit For
        End If
     Next
End Sub

ps. The Alien language is Thai language that can't be display and in the collection is the month in Thai language.Sorry for my bad English.

Thanks everyone for helping :)

Answer 1

You can achieve this simply by changing the date locale and format to English, collecting your data, then change it back to Thai.

Something like this:

Sub Test2()
    Dim doc As Document
    Set doc = Application.ActiveDocument

    Const CCtrl As String = "Date1"
    Const CCtrl2 As String = "Date2"
    Const displayDateDiff As String = "displayDateDiff"

    Dim control As ContentControl
    Dim ccdisplayDateDiff As ContentControl
    Dim engDate As String, engDate1 As String
    For Each control In doc.ContentControls
        Select Case control.Tag
        Case CCtrl, CCtrl2
            control.DateDisplayLocale = wdEnglishUS
            control.DateDisplayFormat = "MMMM d YYYY"
            
            If control.Tag = CCtrl Then
                engDate = control.Range.Text
            ElseIf control.Tag = CCtrl2 Then
                engDate1 = control.Range.Text
            End If
            
            control.DateDisplayLocale = wdThai
            control.DateDisplayFormat = "d ´´´´ bbbb"
        
        Case displayDateDiff
            Set ccdisplayDateDiff = control
        End Select
    Next
    
    If Not ccdisplayDateDiff Is Nothing Then ccdisplayDateDiff.Range.Text = DateDiff("d", engDate, engDate1)
    
End Sub