VBA Functions

Convert string to date

Function ConvertStringToDate(dateString As String) As Date

    Dim parts() As String

    parts = Split(dateString, ".")

    If UBound(parts) = 2 Then

        ' parts(0) = day, parts(1) = month, parts(2) = year

        ConvertStringToDate = DateSerial(CInt(parts(2)), CInt(parts(1)), CInt(parts(0)))

    Else

        Err.Raise vbObjectError + 1, , "Invalid date format."

    End If

End Function

Remove Duplicate

Function RemoveDuplicates(arr As Variant) As Variant

    Dim dict As Object

    Dim item As Variant

    Dim result() As Variant

    Dim i As Long

    Set dict = CreateObject("Scripting.Dictionary")

    

    ' Add unique items to dictionary

    On Error Resume Next

    For Each item In arr

        If Not dict.exists(item) Then

            dict.Add item, Nothing

        End If

    Next item

    On Error GoTo 0

    

    ' Transfer dictionary keys to result array

    ReDim result(1 To dict.Count)   'ReDim result(0 To dict.Count-1)

    i = 1                           'i=0

    For Each item In dict.Keys

        result(i) = item

        i = i + 1

    Next item

    RemoveDuplicates = result

End Function