VBA cDate not working on Mac excel 2011(14.7.1)

问题:

I am trying to convert a date into long using VBA script. Below is the code snippet

Sub test()
    Dim str as string
    Dim d as variant
    str="1/1/2016"
    d=cdate(str)
end sub

The above snippet runs fine on windows but is giving me type mismatch error on MAC. Is there any turnaround to convert a valid string in a date.

回答1:

Your code did work on my local OS X machine with Excel. I have different region settings, though. I would suggest try using the international date format ("YYYY-mm-dd").

Sub Main
    Dim str as String
    Dim d as Variant
    str = "2016-01-01"
    d = CDate(str)
End Sub

Alternatively, you could try "Jan/1/2016".

---

P.S.: You can see your date & time settings in OS X under System Preferences -> Language & Region -> Advanced -> Dates:

回答2:

Your regional settings are the root cause.

Can you try modify them?

defaults write NSGlobalDomain AppleICUDateFormatStrings -dict 1 dd/MM/yyyy

回答3:

Maybe try removing the "/" before converting? I do not have a Mac to test:

Public Sub test()
    Dim str As String
    Dim d As Long 'should be Date, but your question states Long
    str = "1/1/2016"
    d = CDate(Replace(str, "/", ""))
    Debug.Print d
End Sub

Also, have you tried checking for missing references? Date for VBA not working in Excel 2011?

回答4:

You could use the IsDate() function to check if it is a date before converting. Otherwise use a workaround:

Sub test()
    Dim str as string
    Dim d as variant
    str = "1/1/2016"

    If IsDate(str) then
       d = CDate(str)
    Else
       Dim aDateParts() as String
       aDateParts() = Split(str, "/")

       ' DateSerial(Year, Month, Day)
       d = DateSerial(CInt(aDateParts(2)), CInt(aDateParts(0)), CInt(aDateParts(1)))
    End If
end sub

回答5:

Many different answer, so I may as well throw this way of doing it it into the ring,

Sub test()
    Dim str As String
    Dim d As Variant
    str = "1/1/2016"
    d = CDate(Format(str, "dd/MM/yyyy"))
    Debug.Print d
End Sub

回答6:

I didn't post this originally, because I felt it didn't address the root of the problem, however, if you want a workaround, you can use the below function in a similar way as you would use a CDate, by calling ConDate("12/12/2016").

This is is the way I approached the problem:

Sub MainTest()
    Dim InputString As String, OutputDate As Date

    InputString = "01/12/2016"
    OutputDate = ConDate(InputString)

    Debug.Print OutputDate, TypeName(OutputDate)
End Sub

Function ConDate(ByRef InputString As String) As Date
    Dim Day As Long, Month As Long, year As Long
    'mmddyyyy format
    Month = CLng(Left(InputString, InStr(1, InputString, "/", vbTextCompare) - 1))
    Day = CLng(Mid(InputString, InStr(1, InputString, "/", vbTextCompare) + 1, InStrRev(InputString, "/", , vbTextCompare) - InStr(1, InputString, "/", vbTextCompare) - 1))
    year = CLng(Right(InputString, 4))
    ConDate = DateSerial(year, Month, Day)
End Function

Not wanting to plagerise sebifeixler's answer I kept my original left/mid/right to separate the day/month/year, but I feel it's much neater to use his split function to separate the date by "/". A combination of the two would be a good workaround.