Cât de des primim datele în foile de calcul care ni se furnizează ca 12.26.2016 sau 26 (format Regatul Unit), doar pentru a ni se spune că data este invalidă sau că nu există luna 12? Acest articol explorează remedierea datelor cu VBA, folosind funcțiile TRIM, LEFT, RIGHT și MID.
Articolul presupune că cititorul are afișată panglica pentru dezvoltatori și este familiarizat cu Editorul VBA. Dacă nu, vă rugăm să Google „Fila Dezvoltator Excel” sau „Fereastra Cod Excel”.
Xlsm din acest exercițiu poate fi descărcat aici.
Nu problema noastră!
Cel mai bun loc pentru a rezolva o problemă este la sursă. Cu toate acestea, nicio măsură de convingere nu poate convinge departamentul de salarizare în acest caz că 12.26.1994 nu este o dată valabilă (cu excepția cazului în care este configurată astfel în Panoul de control al computerului pentru unele țări din Europa de Est).
De fapt, putem dovedi că nu este citibil de mașină. De exemplu, adăugarea a 7 zile la o dată:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
întrucât…
"=2017-01-01 + 7" = 2017/01/08.
Să presupunem că ei sugerează că nu este problema lor.
Formate de dată
Primul lucru pe care trebuie să ne asigurăm este dacă data este în format SUA sau Internațional.
Exemplul nostru arată clar că ne uităm la utilizarea în SUA, adică MDY, mai degrabă decât formatul internațional DMY.
Odată ce am stabilit sursa, trebuie să schimbăm formatele de date, astfel încât Excel să le poată înțelege, fie la nivel internațional, fie în SUA.
Cel mai bun mod de a face acest lucru este să schimbați data la aaaammzz, un format care nu necesită nicio calificare.
Cum lucram impreuna
Vom parcurge fiecare rând din document, apelând o funcție pentru a „corecta” data în funcție de țara sursă. Odată corectată data, vom calcula vârsta Angajatului.
Codul
Copiați următorul cod într-un modul nou:
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
Adăugați un buton la formularul dvs. și atribuiți-l la Sub Main.
Avertisment
Înainte de a adăuga prea mult cod complex la modulul dvs., rețineți că Excel nu este întotdeauna stabil în dezvoltarea de aplicații majore și, adesea, nu poate recupera codul deteriorat în sine. Rezultatul ar putea fi coruperea singurei copii, deoarece corupția are loc în „Salvare”.
Faceți backup frecvent și folosiți un instrument pentru a repara Coruperea fișierului Excel.
Introducerea autorului:
Felix Hooker este un expert în recuperarea datelor DataNumen, Inc., care este lider mondial în tehnologiile de recuperare a datelor, inclusiv repara rar corupție de fișiere și produse software de recuperare sql. Pentru mai multe informații vizitați www.datanumen.com