How to use DateValue function in VBA?

The DateValue function in VBA (Visual Basic for Applications) is used to convert a string representing a date into a Date data type. This function can be useful when you need to manipulate dates stored as strings in a standardized date format.

Here’s how you can use the DateValue function in VBA:

  • Open the Visual Basic for Applications editor by pressing ALT + F11 in Excel.
  • Insert a new module or use an existing one in the VBAProject you’re working on.
  • Write a VBA subroutine or function where you want to use DateValue.

Here’s an example of using DateValue in a subroutine


Sub UseDateValue()
    Dim dateStr As String
    Dim actualDate As Date
    ' Assign a string representing a valid date
    dateStr = "March 15, 2023"
    ' Use the DateValue function to convert the string to a date
    actualDate = DateValue(dateStr)
    ' Print the date to the Immediate Window (press Ctrl + G to view)
    Debug.Print actualDate
    ' Output: 15/03/2023 (depending on your system's date settings)
End Sub

The DateValue function assumes that the date is provided in the date format recognized by your system locale settings. If you pass an invalid date string that cannot be interpreted as a date, VBA will raise a runtime error (Type mismatch error). To handle such errors, you can use the IsDate function before attempting to convert the string to a date, or use error handling with On Error.

Here’s how you can do this with error handling


Sub UseDateValueSafely()

    Dim dateStr As String
    Dim actualDate As Date
    ' Assign a string representing a valid date
    dateStr = "InvalidDate"
    ' Error handling to catch an invalid date conversion
    On Error GoTo ErrorHandler
    actualDate = DateValue(dateStr)
    On Error GoTo 0
    ' Continue with your code
    ' ...

    Exit Sub

    MsgBox "The provided string is not a valid date: " & dateStr, vbExclamation
    Exit Sub
End Sub

Remember that DateValue only parses the date part and does not include the time. If you also need to parse the time, you’d use the CDate function or manipulate the date and time parts separately and then combine them.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *