CODEWITHSUNDEEP
excelvba
Dolphin975
Dolphin975

Reputation: 341

Pass variables to a function inside Evaluate()

please forgive my English. Because of the differences between DateDif (worksheet Excel function) and DateDiff (VBA function) (one of them calculates the full month difference and the other counts the offset of the months themselves without paying attention to the days), I need to use in a VBA function the worksheet DateDif, passing variables to it. This simple example works: anni = Application.Evaluate("=DATEDIF(""01/01/2020"", ""01/01/2021"", ""y"")") while this one gives me "error 2015" (data1 and data2 are of course the dates read from the worksheet's cells A1 and A2): dummy = "DATEDIF(""" & data1 & """,""" & data2 & """,""Y"")" anni = Application.Evaluate(dummy) What am I doing wrong? Thank you!

Upvotes: 1

Views: 498

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

International Issue?

Depending on how you read the data, you can do one of the following:

Sub calcAnni()

    Dim anni As Long
    Dim data1 As String, data2 As String, dummy As String

    data1 = Replace(Format([A1], "mm/dd/yyyy"), ".", "/")
    data2 = Replace(Format([A2], "mm/dd/yyyy"), ".", "/")
    dummy = "=DATEDIF(""" & data1 & """,""" & data2 & """,""Y"")"
    anni = Application.Evaluate(dummy)
    Debug.Print anni & " [" & data1 & "," & data2 & "]" & " - String"
    '[A3] = data1
    '[A4] = data2

End Sub

Sub calcAnni2()

    Dim anni As Long
    Dim date1 As Date, date2 As Date
    Dim data1 As String, data2 As String, dummy As String
    date1 = [A1]
    date2 = [A2]
    data1 = Replace(Format(date1, "mm/dd/yyyy"), ".", "/")
    data2 = Replace(Format(date2, "mm/dd/yyyy"), ".", "/")
    dummy = "=DATEDIF(""" & data1 & """,""" & data2 & """,""Y"")"
    anni = Application.Evaluate(dummy)
    Debug.Print anni & " [" & date1 & "," & date2 & "]" & " - Date"
    Debug.Print anni & " [" & data1 & "," & data2 & "]" & " - String"
    '[A3] = data1
    '[A4] = data2

End Sub

Sub calcAnni3()

    Dim anni As Long
    Dim date1 As Date, date2 As Date
    Dim dummy As String
    date1 = [A1]
    date2 = [A2]
    dummy = "=DATEDIF(""" & Replace(Format(date1, "mm/dd/yyyy"), ".", "/") _
      & """,""" & Replace(Format(date2, "mm/dd/yyyy"), ".", "/") & """,""Y"")"
    anni = Application.Evaluate(dummy)
    Debug.Print anni & " [" & date1 & "," & date2 & "]" & " - Date"
    '[A3] = data1
    '[A4] = data2

End Sub

Upvotes: 1

Related Questions