Switch synatx error issue

Question

In the following vba code in excel 2010 I am getting the error message Syntax error. I think it is to do with the switch statement but can't see what is wrong. Do you know what I have done wrong?

Sub timeperiod()

Dim change As Integer, colA As Date, i As Long, val As String

For i = 2 To Rows.Count

colA = Cells(i, 1).Value
change = Day(Now) - Day(colA)
val = Cells(i, 3).Value

If change <= 0 Then

val = Switch(_
DATEDIF (colA, Now,"m") < 3 And DATEDIF (colA, Now,"m") > 0,'1 - 2 months',_
DATEDIF (colA, Now,"m") < 5 And DATEDIF (colA, Now,"m") > 2,'2 - 4 months',_
DATEDIF (colA, Now,"m") < 7 And DATEDIF (colA,Now,"m") > 4,'4 - 6 months',_
DATEDIF (colA, Now,"m") <10 And DATEDIF (colA, Now,"m") > 6, '6 - 9 months',_
DATEDIF (colA, Now,"m") > 9, '9 months+')

ElseIf change > 0 Then

val = Switch (_
DATEDIF (colA, Now,"m") = 2, '2 - 4 months',_
DATEDIF (colA, Now,"m") = 4, '4 - 6 months',_
DATEDIF (colA, Now,"m") = 6, '6 - 9 months',_
DATEDIF (colA, Now,"m") = 9, '9 months+',_
DATEDIF (colA, Now,"m") < 2 And DATEDIF (colA, Now,"m") > 0,'1 - 2 months',_
DATEDIF (colA, Now,"m") < 4 And DATEDIF (colA, Now,"m") > 2,  '2 - 4 months',_
DATEDIF (colA, Now,"m") < 6 And DATEDIF (colA, Now,"m") > 4,  '4 - 6 months',_
DATEDIF (colA, Now,"m") < 9 And DATEDIF (colA, Now,"m") > 6, '6 - 9 months',_
DATEDIF (colA, Now,"m") > 9, '9 months+')

End If
Next i


End Sub

Answer 1

Several problems:

---

Use _ to continue lines:

val = switch( _
    ...

the space before _ is important (to enable variable names containing _ I guess?). You also can't comment except after the last segment

---

use double quotes " to create strings, single quotes ' are for comments.

---

use DateDiff in VBA. Usage DateDiff(Interval, Date1, Date2)

DateDiff ("m", colA, Now)

---

However, as Tensibai noted, a Select ... Case would be nicer here:

Select Case DateDiff("m", colA, Now)
    Case 1 to 2
        val = "1 - 2 months"
    Case 3 to 4
        val = "2 - 4 months"
    '...
    Case Is > 9
        val = "9+ months"
End Select

Answer 2

Tensibai is right in the regard SELECT...CASE might be better in this case, however, there is a SWITCH-Function in VBA.

There are some small mistakes in your syntax:

1. Its DateDiff not DATEDIF
2. In VBA you have to use double quotes " instead of single quotes ' to start and escape strings. Single quotes are used for commenting
3. DateDiff-Syntax is DateDiff(Interval, Date1, Date2), so you mixed the arguments up a bit

As for your code:

Sub timeperiod()

Dim change As Integer, colA As Date, i As Long, val As String

For i = 2 To Rows.Count

    colA = Cells(i, 1).Value
    change = Day(Now) - Day(colA)
    val = Cells(i, 3).Value

    If change <= 0 Then

        Debug.Print DateDiff("M", colA, Now)
        Select Case DateDiff("M", colA, Now)
            Case 1 To 2: val = "1 - 2 months"
            Case 2 To 4: val = "2 - 4 months"
            Case Is > 9: val = "9 months"
            ' and so on
            Case Else: val = "not categorized"
        End Select
    ElseIf change > 0 Then

    ' your code....

    End If

    Debug.Print val

Next i

End Sub

Answer 3

your syntax errors has been properly described by Martin and acradeprecinct, so I here concentrate on the logic

Switch() function evaluates expressions from left to right and stops at the first one evaluated as True.

you can exploit this and shorten down your code as follows:

Option Explicit

Sub timeperiod()
    Dim change As Integer, colA As Date, i As Long, val As String
    Dim dateDif As Long

    For i = 2 To Rows.Count
        colA = Cells(i, 1).value
        dateDif = DateDiff("m", colA, Now)
        change = Day(Now) - Day(colA)
        If change <= 0 Then
            val = Switch(dateDif <= 0, "Error", _
                         dateDif < 3, "1 - 2 months", _
                         dateDif < 5, "2 - 4 months", _
                         dateDif < 7, "4 - 6 months", _
                         dateDif < 10, "6 - 9 months", _
                         dateDif >= 10, "9 months+")

        ElseIf change > 0 Then
            val = Switch(dateDif < 0, "Error", _
                         dateDif < 2, "1 - 2 months", _
                         dateDif < 4, "2 - 4 months", _
                         dateDif < 6, "4 - 6 months", _
                         dateDif < 9, "6 - 9 months", _
                         dateDif >= 9, "9 months+")
        End If
    Next i
End Sub

I'd point out that your "labels" actually overlap each other, and I think you should consider to change the first number so that for the first Switch it would be:

        val = Switch(dateDif <= 0, "", _
                     dateDif < 3, "1 - 2 months", _
                     dateDif < 5, "3 - 4 months", _
                     dateDif < 7, "5 - 6 months", _
                     dateDif < 10, "7 - 9 months", _
                     dateDif >= 10, "10 months+")

and similarly for the other Switch statement

That said you can furtherly shorten down your code once you notice that the two Switch() statements differ for the comparative term of dateDif only, in correspondence of change value compared to 0

so you may code:

Option Explicit

Sub timeperiod()
    Dim change As Integer, colA As Date, i As Long, val As String
    Dim dateDif As Long, deltaDif As Long

    For i = 2 To Rows.Count
        colA = Cells(i, 1).value
        dateDif = DateDiff("m", colA, Now)
        change = Day(Now) - Day(colA)
        deltaDif = IIf(change <= 0, 1, 0)

        val = Switch(dateDif < 0, "Error", _
                     dateDif < 2 + deltaDif, "1 - 2 months", _
                     dateDif < 4 + deltaDif, "2 - 4 months", _
                     dateDif < 6 + deltaDif, "4 - 6 months", _
                     dateDif < 9 + deltaDif, "6 - 9 months", _
                     dateDif >= 9 + deltaDif, "9 months+")
    Next i
End Sub