Syntax error when parsing String to Date

Question

I'm trying to parse a String in VB to Date in an Excel macro like this:

Sub report_macro()

    Dim nRow As Long
    Dim sDueDate As String
    Dim dueDate As Date

    For nRow = 5 To 65536
        If Not Range("A" & nRow).Value = "" Then
            sDueDate = Range("G" & nRow).Value
            dueDate = Date.ParseExact(sDueDate, "yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo)
            If Range("H" & nRow).Value = 57600 Then

                Range("A" & nRow & ":I" & nRow).Select
                Selection.Font.Bold = True
                With Selection.Interior
                    .Color = 13551615
                End With
            End If
        Else
            Exit For
        End If
    Next nRow

End Sub

I'm getting Sytnax error at the line:

dueDate = Date.ParseExact(sDueDate, "yyyy-MM-dd", System.Globalization.DateTimeFormatInfo.InvariantInfo)

What am I doing wrong?

score 0 · Answer 1 · edited May 23 '17 at 12:14

0

Date.ParseExact is a VB.NET method. You are running a macro in Excel which uses VBA not VB.NET (Differences)

Your best option is probably just to assign the value directly to the date variable (the Value property is actually a Variant):

dueDate = Range("G" & nRow).Value

Alternatively you can do a simple conversion using CDate

If you really want to parse it have a look at this answer: A better CDate for VB6

edited May 23 '17 at 12:14

Community

1
1

answered Mar 31 '14 at 08:14

Matt Wilko

26,994
10
93
143

Thanks. Isn't there anything more simple to just assign a date from a cell formatted as a date to a Date variable? It seems as something common to do, so I thought it would be trivial. – pmichna Mar 31 '14 at 08:22
1

To assign a Date to a variable from some cell, why not just: Dim DT as Date: If IsDate(myCell) then DT = myCell – Ron Rosenfeld Mar 31 '14 at 11:08

Syntax error when parsing String to Date

1 Answers1