Datums in VBA vertonen vaak problemen. Vooral ook omdat de Amerikaans manier om datums te schrijven verschilt van de Nederlandse manier. Enige voorzichtigheid, en regelmatig testen is hier dus wel gepast.
Volgende codes geven: Datum; Tijd; Jaar; Maand; Dag.
Datum:
Range("A1").Value
= Date
Datum + Tijd:
Range("A1").Value
= Now
Tijd:
Range("A1").Value = Time
Huidig Jaar (In plaats van Date, kan je ook een datumvariabele gebruiken.)
Range("A1").Value = Year(Date)
Huidige Maand:
Range("A1").Value
= Month(Date)
Huidige Dag:
Range("A1").Value
= Day(Date)
Algemeen bekend is dat getallen in VBA gewoon zonder aanhalingtekens geschreven kunnen worden.
v.b.
2596
Ook bekend is dat tekst tussen aanhalingstekens moet komen
v.b.
Dit is een tekst
Minder gekend is dat datums tussen hekjes moeten komen.
v.b.
#2/4/2004#
Hierbij moeten we even opletten dat we de Amerikaanse schrijfwijze gebruiken. Dus: Eerst de maand; dan de dag; en dan het jaar.
Een reeks voorbeelden moet dit duidelijk maken:
MsgBox 2 / 4 / 2004
Terwijl dit in de programmacode misschien nog leesbaar is, blijkt de uitvoering ervan een totaal waardeloos getal op te leveren. Namelijk: 2 Gedeeld door 4 Gedeeld door 2004
MsgBox "2/4/2004"
Dit lijkt een goed resultaat te geven. En in een dergelijke toepassing is dit allicht bruikbaar. MAAR de MsgBox laat geen datum zien, maar een tekst (String), die de vorm van een datum heeft aangenomen. Zo zou je zonder problemen ook dit kunnen schrijven:
MsgBox "32/4/2004"
Er is totaal niets wat hiertegen bezwaar zou maken.
Met het volgende voorbeeld zitten we goed, maar het blijft opletten:
MsgBox #2/4/2004#
Dit geeft de datum 4-2-04 (Reeds merkbaar in de MsgBox). Let er dus op dat maanden en dagen verwisseld worden. Om dit helemaal duidelijk te maken, kan je ook even dit uitvoeren:
MsgBox Format(#2/4/2004#, "dd-mmmm-yy")
Helemaal duidelijk wordt het indien je in de VBA-editor een datum probeert in te vullen die in het Nederlands wel kan, maar niet in het Amerikaans.
MsgBox #13/4/2004#
Hier zal Excel spontaan de cijfers 13 en 4 omwisselen van plaats.
Een totaal onmogelijke datum resulteert echter in een foutmelding.
MsgBox #32/4/2004#
Natuurlijk kunnen we de functie DateValue gebruiken. Deze functie zet een geldige tekenreeks om in een aanvaardbare datum.
Hierbij moeten we opmerken dat een tekststring de lokale datumnotatie gebruikt (Nederlands dus), terwijl een datum als argument de amerikaanse datumnotatie gebruikt.
Voer even volgende macro uit:
Sub
test()
Range("A1").Value
= DateValue("1-2-2004")
Range("C1").Value
= DateValue(#1/2/2004#)
End
Sub
Cel A1 zal hier 1-2-04 (1 februari 2004) als resultaat geven.
Cel C1 geeft echter 2-1-04 (2 Januari 2004) als resultaat.
Helemaal duidelijk wordt het wanneer je in de tekststring de 2 wil vervangen door een nederlandstalige maandnaam. DIT wordt zonder problemen aanvaard. Indien je echter de 2 vervangt door een Engelstalige maandnaam, krijg je een fout.
Indien je echter in de datumwaarde de 1 wil vervangen door een nederlandstalige maandnaam, krijg je onmiddellijk een foutmelding. Vervang je de 1 echter door een Engelstalige maandnaam, dan schijnt er totaal geen probleem te zijn.
We mogen hieruit dus afleiden dat het niet gewenst is om
de tekststring te gebruiken in de VBA-code. Dit omdat dit vrijwel zeker problemen
gaat veroorzaken indien je de macro zou afspelen op een anderstalige Excel-versie.
Een van de zaken die vele fouten voorkomen, is het goed declareren van uw variabelen.
Stel: je hebt een waarde in het geheugen van Excel, wat een datum moet voorstellen.
Indien je de waarde niet zou declareren, dan moet Excel zelf raden wat jij ermee bedoelt.
Dim x
De x in dit voorbeeld wordt door Excel als Variant beschouwd. Kan dus om het even wat zijn. Een getal een tekst een datum .
Dim x As Date
De x in dit voorbeeld kan enkel nog een datumwaarde zijn (Ook tijdswaarde). Indien je er bijvoorbeeld een ongeldige waarde van zou willen maken, dan krijg je een foutmelding, en weet je dat je fout zit.
Om de problemen met datums te illustreren neem ik hier enkele testen met een UserForm, en 4 TextBoxen.
De TextBoxen noemen: TextBox1; TextBox2; TextBox3 en Textbox4.
Ik zet in cel A1 de datum 6-2-04. Ik kies speciaal voor deze datum omdat de dagen en maanden omwisselbaar zijn, en toch nog een geldige datum kunnen geven.
Nu zet ik in de programmacode van het UserForm volgende code:
Private
Sub UserForm_Initialize()
Dim dt
As Date
dt
= Range("A1").Value
TextBox1.Value = Range("A1").Value
TextBox2.Value = dt
TextBox3.Value = Format(Range("A1").Value,
"dd-mmmm-yy")
TextBox4.Text = Range("A1").Value
End
Sub
Let er op dat enkel TextBox2 ιn TextBox3 met een datum gevuld worden. TextBox1 wordt gewoon gevuld met de waarde van cel A1, terwijl TextBox4 wordt gevuld met de TEKST!!! Van cel A1.
Indien je nu het UserForm uitvoert, krijg je 4 correcte datums in de tekstboxen.
Wat we nog uit deze test mogen afleiden, is dat Excel in ELKE tekstbox wordt gevuld met een tekstwaarde (String), vermits elke tekstbox de Nederlandstalige datum weergeeft. Dit alles kan enigszins verwarrend overkomen (Excel is in mijn ogen zelf niet consequent) maar het is zo, en zoals we later zullen zien blijkt Excel zelf niet altijd deze waarde als tekst te willen aanvaarden.
Voeg nu een CommandButton aan het UserForm toe, met de naam: CommandButton1. En plaats volgende code in het UserForm:
Private Sub CommandButton1_Click()
Range("C1").Value = TextBox1.Value
Range("C2").Value
= TextBox2.Value
Range("C3").Value
= TextBox3.Value
Range("C4").Value
= TextBox4.Value
End Sub
Voer daarna nogmaals het UserForm uit, en druk op de zopas toegevoegde commandbutton. Nu pas zie je reeds een hoop fouten. Op uitzondering van de waarde uit TextBox3 geven alle andere textboxen de 6de maand als datum. Plots blijken dag en maand van plaats verwisseld te zijn. Maar vergis u niet Ook TextBox3 geeft een foutieve waarde terug. Ook al lijkt dit op een datum het is een tekst. Als bewijs kan je de opmaak van deze cel kopiλren, en op een van de andere cellen plakken. Misschien tot uw verbazing krijg je in deze andere cel geen datumopmaak, maar een gewoon getal. Uitzondering hier: Indien de cellen reeds op voorhand geformatteerd zijn.
Een kleine wijziging aan de programmacode blijkt niet veel te helpen. Eerst haalde ik de waarde (Value) van de textboxen op. Nu haal ik de tekst (Text) op met hetzelfde (foutieve) resultaat.
Private
Sub CommandButton1_Click()
Range("C1").Value
= TextBox1.Text
Range("C2").Value
= TextBox2.Text
Range("C3").Value
= TextBox3.Text
Range("C4").Value
= TextBox4.Text
End
Sub
Het probleem zit hem dus duidelijk in de verschillen tussen Nederland en Amerika wat betreft het noteren van datums. Bijgevolg zal men er in Amerika dus veel minder last mee hebben. Terwijl het vullen van de tekstboxen met een waarde uit een werkblad nog redelijk lukt, blijkt het een groot probleem te zijn deze waarde terug te geven aan een werkblad.
Excel blijkt de waarde in de tekstboxen niet meer als tekst te beschouwen, maar waar mogelijk als datum.
Jawel, als datum, maar dan wel volgens de Amerikaanse datumnotatie. Vandaar dat TextBox3 niet vertaald werd.
Een mogelijke oplossing bied de functie DateValue. Deze functie aanvaard een tekststring in de Lokale taal. Net hetgeen waar de 4 tekstboxen mee gevuld werden. Vervang nogmaals de code achter de button door volgende code:
Private
Sub CommandButton1_Click()
Range("C1").Value
= DateValue(TextBox1.Value)
Range("C2").Value
= DateValue(TextBox2.Value)
Range("C3").Value
= DateValue(TextBox3.Value)
Range("C4").Value
= DateValue(TextBox4.Value)
End Sub
Merk hierbij op dat de functie DateValue zowel tekst als datums als argument aanvaard. Bij het ophalen van een waarde uit een textbox neemt deze functie echter aan dat het over een tekst (string) gaat. Sterker nog: Indien je in een van de textboxen de amerikaanse datumnotatie zou willen gebruiken, krijg je een fout.
Een oplossing voor dit probleem blijkt om ook de waarde uit de TextBoxen te valideren als Date. Herinner u wat ik eerder reeds zei, dat het goed declareren van uw variabelen zeer nuttig kan zijn.
Maak nogmaals een Commandbutton, met de naam CommandButton2, en hang daar volgende code aan vast:
Private
Sub CommandButton2_Click()
Dim dt
As Date
dt
= TextBox1.Value
Range("D1").Value
= dt
dt
= TextBox2.Value
Range("D2").Value = dt
dt
= TextBox3.Value
Range("D3").Value
= dt
dt
= TextBox4.Value
Range("D4").Value
= dt
End
Sub
En nu blijkt plots alles wιl correct te werken!
Wil dit nu zeggen dat enkel dit laatste van belang is? En dat hoe men de textbox vult van geen belang meer is? Ik moet toegeven dat het correct zou kunnen werken maar ik huiver bij de gedachte dat iets wat zoveel fouten kan veroorzaken niet strikt onder controle gehouden wordt. Persoonlijk geef ik dus de voorkeur om de TextBox te vullen zoals TextBox2 (eventueel in combinatie met TextBox3) en de uitvoer ook via een gedeclareerde variabele.
In VBA beschikken we eveneens over enkele datumfuncties, zoals:
DateAdd
DateDiff
DatePart
DateSerial
DateAdd
Deze functie geeft een datum terug waaraan een opgegeven tijdsinterval is toegevoegd.
Een voorbeeld:
MsgBox DateAdd("m",
1, #2/7/2004#)
Dit voorbeeld geeft 7 Maart 2004 als resultaat.
Het eerste argument m geeft de intervaleenheid op. In dit geval dus maand.
We hebben hier volgende mogelijkheden:
yyyy . Jaar
q . kwartaal
m maand
y .dag van het Jaar (Opletten dus: in tegenstelling tot wat je zou denken voegt dit teken slechts x aantal dagen toe)
d .dag
w dag van de week (Eveneens opletten: geen week enkel ιιn dag)
y; d; w geven dus hetzelfde resultaat.
ww .week
h .uur
n minuut
s seconde
Tweede argument van deze functie 1, geeft het aantal eenheden van de intervaleenheid die bij de datum moeten worden opgeteld. Deze functie geeft ALTIJD een geldige datum terug. Dit argument moet steeds een Integer zijn (geheel getal). Indien je gebruik zou maken van een getal met komma (punt in VBA), dan wordt dit getal afgerond naar het eerste geheel getal naar 0 toe. 1,7 wordt dus 1. U kan echter wιl negatieve getallen als tweede argument gebruiken.
MsgBox DateAdd("m",
-2, #7/2/2004#)
Dit geeft bijvoorbeeld 2 mei 2004 als resultaat.
Het laatste argument: #2-7-2004#
Hiervoor kan je een echte datumwaarde opgeven (tussen hekjes #), waarbij je er op moet letten de Amerikaanse datumnotatie te gebruiken.
Ook mogelijk kan je een datumtekst (string) als argument opgeven.
Vb.
MsgBox DateAdd("d",
5, "8-februari-2004")
Alhoewel dit ongetwijfeld duidelijker leesbaar is, kan dit gewoonweg niet werken indien je je bestanden doorgeeft aan een anderstalige computer.
Een derde mogelijkheid heb je door het derde argument te vervangen door een celverwijzing. Op deze manier zal je natuurlijk geen hinder hebben van andere taalversies.
MsgBox DateAdd("d", 5, Range("A1").Value)
DateDiff
Deze functie geeft het verschil tussen 2 datums op in de vooropgestelde tijdsintervaleenheid. Hierbij moeten we rekening houden dat deze functie geen rekening houd met kleinere tijdsintervaleenheden als degene die je wenst na te kijken. Een voorbeeld maakt dit duidelijk:
MsgBox DateDiff("m",
#1/1/2004#, #1/31/2004#)
Deze test geeft 0 (maanden) als resultaat. Ook al zitten er 30 dagen tussen beide datums. Met dit gegeven valt nog te leven, vermits je nog steeds kan argumenteren dat 30 dagen nog steeds geen hele maand is. Maar wat te zeggen over volgende test:
MsgBox DateDiff("m",
#1/31/2004#, #2/1/2004#)
Deze test geeft 1 (maand) als resultaat. Ook al zit er slechts 1 dag tussen beide datums. Niet erg nauwkeurig, maar indien je dit weet, kan je er rekening mee houden.
Wat het eerste; tweede en derde argument van deze functie betreft, kan ik gewoon verwijzen naar de opmerkingen die ik bij DateAdd gegeven heb.
Men heeft hier nog de mogelijkheid om 2 extra argumenten aan de functie toe te voegen.
DatePart
Geeft als resultaat een Variant (Integer) met het opgegeven deel van een bepaalde datum.
Indien je wil weten in de hoeveelste week van het jaar een bepaalde datum valt, moet je als eerste argument ww gebruiken.
MsgBox DatePart("ww", #2/1/2004#, vbMonday)
Eerste en tweede argument kennen we intussen van beide
voorgaande functies. Het
derde argument (Bestaat eveneens in de functie DateDiff
maar ik vind het in deze functie meer van toepassing) geeft aan op welke dag de eerste dag van de week valt. Men kent hiervoor volgende argumenten:
VbMonday;
vbTuesday; vbWednesday; vbThursday; vbFriday; vbSaturday; vbSunday
Bij het weglaten van dit argument wordt de week gestart op een Zondag. Het is eveneens mogelijk om getallen te gebruiken in plaats van deze visual basic constanten. Maar dit kan je natuurlijk moeilijk duidelijker noemen.
DateSerial
Deze functie geeft een geldige datum, waarbij als argumenten het jaar; de maand en de dag worden gegeven.
Simpel voorbeeld:
MsgBox DateSerial(2004,
2, 1)
Dit geeft 1 februari 2004 als resultaat.
Deze functie heeft echter meer mogelijkheden dan je op het eerste zicht zou denken. Zo is het mogelijk mιιr dagen of maanden op te geven dan mogelijk. Voorgaande datum heeft 29 dagen in februari. Indien je nu als dag-argument 30 zou invullen, krijg je 1 Maart als resultaat (ιιn dag op overschot dus).
MsgBox DateSerial(2004,
2, 30)
Sterker nog: deze functie aanvaard ook NEGATIEVE getallen.
MsgBox DateSerial(2004,
2, -1)
Dit geeft 30 januari 2004 als resultaat. Niet 31 januari dus, zoals je zou kunnen denken. Het is dus niet de datum 1 februari - 1 dag, maar 1 februari - 2 dagen. -1 dag wordt gereserveerd voor het getal 0.
En nu de verrassing: het bereik van datums in VBA.
Terwijl in een gewoon werkblad niet meer gerekend kan worden met datums van voor 1 januari 1900, kan je in VBA rekenen met datums tussen: 1 Januari 100 en 31 december 9999. Nog steeds onvoldoende om te rekenen met tijden uit de oudheid, maar ik vermoed dat dit ruim moet volstaan om ergens een stamboom of zoiets op te zetten.
Een voorbeeld voor wat mogelijk is:
Maak een UserForm, met daarin een
Textbox . Naam: TextBox1
Commandbutton Naam: CommandButton1
En plak vervolgens volgende code achter de CommandButton:
Private
Sub CommandButton1_Click()
Range("A1").Value
= _
Format(DateSerial(1900, 1, 1 - TextBox1.Value), "dd-mmmm-yyyy")
End Sub
Bij het uitvoeren van het UserForm kan je in het textvlak het aantal dagen invullen waarvan je de datum wil zien voor 1 januari 1900.
AL DEZE ZAKEN ZIJN GETEST IN
EXCEL 2000 (NL-versie). INDIEN ER VERSCHILLEN MOCHTEN ZIJN IN
ANDERE EXCEL-VERSIES, WAARDOOR DEZE TEKST NIET MEER
KLOPT, GELIEVE ME ER DAN VAN OP DE HOOGTE TE BRENGEN.
Luc