Kuinka usein saamme päivämääriä meille toimitetuissa laskentataulukoissa 12.26.2016 tai 26/12/2016 (UK-muoto), vain jos meille ilmoitetaan, että päivämäärä on virheellinen tai ei ole kuukautta 26? Tässä artikkelissa tarkastellaan päivämäärien vahvistamista VBA: lla TRIM-, LEFT-, RIGHT- ja MID-funktioiden avulla.
Artikkelissa oletetaan, että lukijalla on kehittäjänauha ja että hän tuntee VBA-editorin. Jos ei, ota Googlen “Excel Developer -välilehti” tai “Excel Code Window”.
Tämän harjoituksen xlsm voidaan ladata täältä.
Ei meidän ongelmamme!
Paras paikka ongelman ratkaisemiseen on lähde. Mikään suostuttelu ei kuitenkaan voi vakuuttaa palkanlaskijalausta tässä tapauksessa, että 12.26.1994 ei ole kelvollinen päivä (ellei niin ole määritetty tietokoneen ohjauspaneelissa joissakin Itä-Euroopan maissa).
Itse asiassa voimme todistaa, ettei se ole koneella luettavissa. Esimerkiksi 7 päivän lisääminen päivämäärään:
"=01.01.2017 + 7" = #VALUE. "=2017.01.01 + 7" = #VALUE.
kun taas…
"=2017-01-01 + 7" = 2017/01/08.
Oletetaan, että heidän mielestään se ei ole heidän ongelmansa.
Päivämäärämuodot
Ensinnäkin meidän on varmistettava, onko päivämäärä USA: n vai kansainvälisessä muodossa.
Esimerkkimme tekee selväksi, että tarkastelemme Yhdysvaltojen käyttöä, toisin sanoen MDY: tä kansainvälisen DMY-muodon sijaan.
Kun lähde on löydetty, meidän on muutettava tietomuotoja niin, että Excel voi ymmärtää ne, olipa kyseessä sitten kansainvälinen tai USA.
Paras tapa tehdä tämä on muuttaa päivämäärä yyyymmdd, muotoon, joka ei vaadi pätevyyttä.
Process
Selataan asiakirjan jokaisen rivin läpi ja kutsumme funktion "korjaamaan" päivämäärän lähdekoodin mukaan. Kun päivämäärä on korjattu, laskemme työntekijän iän.
Koodi
Kopioi seuraava koodi uuteen moduuliin:
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
Lisää painike lomakkeeseesi ja määritä se Sub Mainiin.
Varoitus
Ennen kuin lisäät liikaa monimutkaista koodia moduuliin, muista, että Excel ei ole aina vakaa suuressa sovelluskehityksessä ja että se ei usein pysty palauttamaan itse vahingoittunutta koodia. Tuloksena voi olla ainoan kopion vioittuminen, koska vioittuminen tapahtuu "Tallenna" -kohdassa.
Varmuuskopioi usein ja käytä korjattavaa työkalua Excel-tiedoston vioittuminen.
Tekijän esittely:
Felix Hooker on tietojen palauttamisen asiantuntija DataNumen, Inc., joka on maailman johtava tietojen palautustekniikoissa, mukaan lukien korjaus rar tiedostovika ja sql-palautusohjelmistotuotteet. Lisätietoja osoitteessa www.datanumen.com