Cum să remediați datele în foaia de lucru Excel cu VBA

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ă!

Adăugarea a 7 zile la o dată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” = #VALOARE. 

„=2017.01.01 + 7” = #VALOARE.

î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.Data în format SUA

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:

Opțiune Explicit Sub Main() Dim strNewFormat As String Dim strDate As String Sheets("Main").Range("B4").Selectați „Circlă printre rândurile foii, folosind IDNumber ca ancoră” pentru a preveni o oprire prematură cauzată de o data nașterii goală Do While ActiveCell > "" If ActiveCell.Offset(0, 2) > "" Then strDate = ActiveCell.Offset(0, 2) 'Eliminați spațiile de început sau de sfârșit strDate = Trim(strDate) 'Apelați funcția strNewFormat = ReformatDate(strDate, "USA") 'Scrieți rezultatul din funcția ReformatDate într-o nouă coloană ActiveCell.Offset(0, 3) = strNewFormat 'Determinați vârsta scăzând coloana anterioară din data de astăzi ActiveCell.Offset(0, 4) = "=(ACUM()-RC[-1])/365.25" 'Conversie în număr întreg, decupând astfel zecimale ActiveCell.Offset(0, 4) = Int(ActiveCell.Offset(0, 4)) End If Range ("B" & ActiveCell.Row + 1).Selectați bucla End Sub-funcție ReformatDate(sData ca șir, sSursa ca șir) Dim aaaa, mm, dd ca șir aaaa = Right(sDate, 4) Dacă sSource = "SUA" Atunci mm = Left(sDate, 2) dd = Mid(sDate, 4, 2) Else mm = Mid(sDate, 4, 2) dd = Left(sDate, 2) End If ReformatDate = aaaa & "-" & mm & " -" & dd Funcția de sfârșit

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

Comentariile sunt închise.