Hvor ofte får vi datoer i regneark leveret til os som 12.26.2016 eller 26/12/2016 (UK-format), kun for at få at vide, at datoen er ugyldig, eller at der ikke er nogen måned 26? Denne artikel udforsker fastsættelse af datoer med VBA ved hjælp af TRIM, VENSTRE, HØJRE og MID-funktioner.
Artiklen antager, at læseren har udviklerbåndet vist og er bekendt med VBA Editor. Hvis ikke, bedes du Google “fanen Excel-udvikler” eller “vinduet Excel-kode”.
Xlsm i denne øvelse kan downloades link..
Ikke vores problem!
Det bedste sted at løse et problem er ved kilden. Imidlertid kan ingen overtalelse overbevise lønningsafdelingen i dette tilfælde om, at 12.26.1994 ikke er en gyldig dato (medmindre det er konfigureret i computerens kontrolpanel for nogle østeuropæiske lande).
Faktisk kan vi bevise, at det ikke er maskinlæsbart. For eksempel at tilføje 7 dage til en dato:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
der henviser til ...
"=2017-01-01 + 7" = 2017/01/08.
Lad os antage, at de antyder, at det ikke er deres problem.
Datoformater
Den første ting, vi skal fastslå, er, om datoen er i USA eller internationalt format.
Vores eksempel gør det klart, at vi ser på USA-brug, dvs. MDY snarere end internationalt format DMY.
Når vi først har etableret kilden, er vi nødt til at ændre dataformaterne, så Excel kan forstå dem, hvad enten det er internationalt eller i USA.
Den bedste måde at gøre dette på er at ændre datoen til åååååmdd, et format der ikke behøver nogen kvalifikation.
Processen
Vi cykler gennem hver række i dokumentet og kalder en funktion til at "rette" datoen i henhold til kildelandet. Når datoen er rettet, beregner vi medarbejderens alder.
Koden
Kopier følgende kode til et nyt 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
Føj en knap til din formular, og tildel den til Sub Main.
Advarsel
Inden du tilføjer for meget kompleks kode til dit modul, skal du være opmærksom på, at Excel ikke altid er stabil i større applikationsudvikling og ofte ikke er i stand til at gendanne selve den beskadigede kode. Resultatet kan være korruption af din eneste kopi, da korruption sker i "Gem".
Sikkerhedskopier ofte og brug et værktøj til at rette Excel-filkorruption.
Forfatter Introduktion:
Felix Hooker er en datagendannelsesekspert i DataNumen, Inc., som er verdens førende inden for datagendannelsesteknologier, herunder reparere rar fil korruption og SQL-genopretningssoftwareprodukter. For mere information besøg www.datanumen.com