VBA Date Comparisons

VBA has native Date functions as well as a Date data type that allow for comparing and storing date information. The DateAdd function returns a future date (or previous date using a negative interval) using the specified interval. The interval can be in seconds, minutes, hours, days, months, weeks, or years. The DateDiff function will return the difference between two days in the specified interval.

The example below calculates a target date 180 days prior to the current date then finds all files in the specified folder that were last modified prior to the target date.

Sub Test()
    ArchiveFiles ActiveDesignFile.Path, 180
End Sub


Sub ArchiveFiles(strPath As String, numDays As Long)
    Dim dateTarget As Date
    Dim iDays As Long
    Dim strFile As String

    ' Target date is numDays before today
    dateTarget = DateAdd("d", numDays * -1, Date)

    ' find all files in current folder
    strFile = Dir(strPath & "\*")

    Do While Len(strFile) > 0
        ' get the number of days since the file was created or last modified
        iDays = DateDiff("d", FileDateTime(strFile), dateTarget)

        ' If older than numDays send to archive
        If iDays > numDays Then
            ' Archive file
        End If
        strFile = Dir
End Sub
Rod Wing

Rod Wing

Rod joined EnvisionCAD in March 2007 and has nearly three decades of experience working in the CADD industry. He led the IT evaluations of Bentley InRoads and AutoDesk Civil 3D at Wisconsin DOT in their overall CAiCE replacement evaluation. He also led the MicroStation V7 to V8 upgrade effort at Wisconsin DOT which included updating not only the CADD standards to accommodate the changes in the file format but also updating legacy MDL and macros to work in the new environment. Rod received a Bachelor of Science degree from the University of Wisconsin in Cartography and a Master of Science in Geographic and Cartographic Sciences from George Mason University. Rod has extensive experience in CADD as well as the areas of GIS, cartography, plotting, scanning, raster editing/manipulation, publishing, and file/data translations. In all of these areas, he has produced custom tools and developed workflows to enhance productivity between diverse sets of applications. He instructs MicroStation classes ranging from fundamentals to advanced applications.


Leave a Reply

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