Hvor ofte får vi datoer i regneark som leveres til oss som 12.26.2016, eller 26 (britisk format), bare for å bli fortalt at datoen er ugyldig eller at det ikke er noen måned 12? Denne artikkelen utforsker å fikse datoer med VBA, ved å bruke funksjonene TRIM, LEFT, RIGHT og MID.
Artikkelen forutsetter at leseren har utviklerbåndet vist og er kjent med VBA Editor. Hvis ikke, vennligst Google "Excel Developer Tab" eller "Excel Code Window".
xlsm i denne øvelsen kan lastes ned her..
Ikke vårt problem!
Det beste stedet å løse et problem er ved kilden. Men ingen grad av overtalelse kan overbevise lønnsavdelingen i dette tilfellet om at 12.26.1994 ikke er en gyldig dato (med mindre den er konfigurert i datamaskinens kontrollpanel for noen østeuropeiske land).
Faktisk kan vi bevise at den ikke er maskinlesbar. For eksempel å legge til 7 dager til en dato:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
mens …
"=2017-01-01 + 7" = 2017/01/08.
La oss anta at de antyder at det ikke er deres problem.
Datoformater
Det første vi må finne ut er om datoen er i USA eller internasjonalt format.
Vårt eksempel gjør det klart at vi ser på USA-bruk, dvs. MDY i stedet for internasjonalt format DMY.
Når vi har etablert kilden, må vi endre dataformatene slik at Excel kan gi mening om dem, enten det er internasjonalt eller i USA.
Den beste måten å gjøre dette på er å endre datoen til ååååmmdd, et format som ikke trenger noen kvalifisering.
Prosessen
Vi vil bla gjennom hver rad i dokumentet, og kaller en funksjon for å "korrigere" datoen i henhold til kildelandet. Når datoen er korrigert, vil vi beregne den ansattes alder.
Koden
Kopier følgende kode inn i en ny modul:
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
Legg til en knapp i skjemaet, og tilordne den til Sub Main.
Forbeholdet
Før du legger til for mye kompleks kode i modulen din, må du være oppmerksom på at Excel ikke alltid er stabil i større applikasjonsutvikling, og ofte ikke klarer å gjenopprette den skadede koden selv. Resultatet kan være korrupsjon av din eneste kopi siden korrupsjonen skjer i "Lagre".
Sikkerhetskopier ofte og bruk et verktøy for å fikse Excel-filkorrupsjon.
Forfatterintroduksjon:
Felix Hooker er en datagjenopprettingsekspert innen DataNumen, Inc., som er verdensledende innen datagjenopprettingsteknologier, inkludert reparasjon rar filkorrupsjon og sql-programvareprodukter. For mer informasjon besøk www.datanumen. Med