Как да коригирате дати във вашия работен лист на Excel с VBA

Колко често получаваме дати в електронни таблици, предоставени ни като 12.26.2016 г. или 26 г. (формат във Великобритания), само за да ни се каже, че датата е невалидна или няма месец 12? Тази статия изследва фиксиране на дати с VBA, използвайки функции TRIM, LEFT, RIGHT и MID.

Статията предполага, че на читателя е показана лентата за програмисти и е запознат с редактора на VBA. Ако не, моля Google „Раздел за програмисти на Excel“ или „Прозорец на кода на Excel“.

Xlsm в това упражнение може да бъде изтеглен тук.

Не е нашият проблем!

Най-доброто място за решаване на проблем е при източника. Въпреки това, никакво убеждаване не може да убеди отдела за заплати в този случай, че 12.26.1994 г. не е валидна дата (освен ако не е конфигурирано в контролния панел на компютъра за някои страни от Източна Европа).

Всъщност можем да докажем, че не се чете машинно. Например, добавяне на 7 дни към дата:

"=01.01.2017 + 7" = #VALUE. 

"=2017.01.01 + 7" = #VALUE.

като има предвид ...

"=2017-01-01 + 7" = 2017/01/08.

Нека приемем, че те предполагат, че не е техен проблем.

Формати на дати

Първото нещо, което трябва да установим, е дали датата е в САЩ или международен формат.

От нашия пример става ясно, че разглеждаме употребата в САЩ, т.е. MDY, а не международния формат DMY.

След като установим източника, трябва да променим форматите на данните, така че Excel да може да ги осмисли, независимо дали в международен план или в САЩ.

Най-добрият начин да направите това е да промените датата на yyyymmdd, формат, който не се нуждае от квалификация.

Процесът

Ще преминем през всеки ред в документа, като извикаме функция за „коригиране“ на датата според страната на източника. След като датата бъде коригирана, ние ще изчислим възрастта на служителя.

Кодексът

Копирайте следния код в нов модул:

Option Explicit

Sub Main()
    Dim strNewFormat As String
    Dim strDate As String
    Sheets("Main").Range("B4").Select
    
    'Cycle through the sheet rows, using IDNumber as an anchor
    'to prevent a premature halt caused by a blank date of birth
    Do While ActiveCell > ""
        If ActiveCell.Offset(0, 2) > "" Then
            strDate = ActiveCell.Offset(0, 2)
            
            'Remove leading or trailing spaces
            strDate = Trim(strDate)
            
            'Call the function
            strNewFormat = ReformatDate(strDate, "USA")
            
            'Write the result from the function ReformatDate to a new column
            ActiveCell.Offset(0, 3) = strNewFormat
            
            'Determine age by subtracting the previous column from today's date
            ActiveCell.Offset(0, 4) = "=(NOW()-RC[-1])/365.25"
            
            'Convert to intger, thus lopping off decimal places
            ActiveCell.Offset(0, 4) = Int(ActiveCell.Offset(0, 4))
        End If
        Range("B" & ActiveCell.Row + 1).Select
    Loop
End Sub

Function ReformatDate(sDate As String, sSource As String)
    Dim yyyy, mm, dd As String
    yyyy = Right(sDate, 4)
    If sSource = "USA" Then
        mm = Left(sDate, 2)
        dd = Mid(sDate, 4, 2)
    Else
        mm = Mid(sDate, 4, 2)
        dd = Left(sDate, 2)
    End If
    ReformatDate = yyyy & "-" & mm & "-" & dd
End Function

Добавете бутон към вашия формуляр и го задайте на Sub Main.

Протест

Преди да добавите твърде много сложен код към вашия модул, имайте предвид, че Excel не винаги е стабилен при основната разработка на приложения и често не може да възстанови самия повреден код. Резултатът може да бъде повреждането на единственото ви копие, тъй като корупцията се случва в „Запазване“.

Архивирайте често и използвайте инструмент за поправяне Корупция на файлове в Excel.

Въведение на автора:

Феликс Хукър е експерт по възстановяване на данни в DataNumen, Inc., която е световен лидер в технологиите за възстановяване на данни, включително ремонт rar файл корупция и sql софтуерни продукти за възстановяване. За повече информация посетете WWW.datanumen.com