Jak często otrzymujemy daty w arkuszach kalkulacyjnych dostarczonych nam 12.26.2016 lub 26 (format brytyjski), tylko po to, aby dowiedzieć się, że data jest nieprawidłowa lub nie ma 12 miesiąca? W tym artykule omówiono ustalanie dat w VBA przy użyciu funkcji TRIM, LEFT, RIGHT i MID.
W artykule założono, że czytelnik ma wyświetloną wstążkę programisty i zna edytor VBA. Jeśli nie, skorzystaj z Google „Excel Developer Tab” lub „Excel Code Window”.
Plik XLSM w tym ćwiczeniu można pobrać w tym miejscu.
To nie nasz problem!
Najlepszym miejscem do rozwiązania problemu jest jego źródło. Jednak żadna ilość perswazji nie może przekonać działu płac w tym przypadku, że 12.26.1994 nie jest poprawną datą (chyba że tak skonfigurowano w Panelu sterowania komputera dla niektórych krajów Europy Wschodniej).
W rzeczywistości możemy udowodnić, że nie nadaje się do odczytu maszynowego. Na przykład dodanie 7 dni do daty:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
natomiast…
"=2017-01-01 + 7" = 2017/01/08.
Załóżmy, że sugerują, że to nie ich problem.
Formaty dat
Pierwszą rzeczą, którą musimy ustalić, jest to, czy data jest w formacie amerykańskim, czy międzynarodowym.
Nasz przykład jasno pokazuje, że patrzymy na użycie w USA, tj. MDY zamiast międzynarodowego formatu DMY.
Po ustaleniu źródła musimy zmienić formaty danych, aby Excel mógł je zrozumieć, czy to na arenie międzynarodowej, czy w USA.
Najlepszym sposobem na to jest zmiana daty na format rrrrmmdd, który nie wymaga żadnych kwalifikacji.
Proces
Przejdziemy przez każdy wiersz dokumentu, wywołując funkcję „poprawiającą” datę według kraju źródłowego. Po poprawieniu daty obliczymy wiek Pracownika.
Kod
Skopiuj następujący kod do nowego modułu:
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
Dodaj przycisk do formularza i przypisz go do Sub Main.
Zastrzeżenie
Przed dodaniem zbyt złożonego kodu do modułu należy pamiętać, że program Excel nie zawsze jest stabilny przy tworzeniu dużych aplikacji i często nie jest w stanie samodzielnie odzyskać uszkodzonego kodu. Rezultatem może być uszkodzenie Twojej jedynej kopii, ponieważ uszkodzenie ma miejsce w „Zapisz”.
Często twórz kopie zapasowe i korzystaj z narzędzi do naprawy Uszkodzenie pliku Excel.
Wprowadzenie autora:
Felix Hooker jest ekspertem w dziedzinie odzyskiwania danych w DataNumen, Inc., która jest światowym liderem w technologiach odzyskiwania danych, w tym naprawa rar uszkodzenie pliku i oprogramowanie do odzyskiwania sql. po więcej informacji odwiedź www.datanumen.com