|
VBA voor Doe het Zelvers is een reeks artikelen, bedoelt voor mensen die met VBA in Excel aan de slag willen om taken te automatiseren of om deze toegankelijk te maken voor gebruikers met weinig Excel kennis. VBA is een volwaardige programmeertaal met een woordenschat en een zinsopbouw (syntaxis). Om deze goed te kunnen leren wordt het aangeraden om hierover boeken te lezen. Bijvoorbeeld uit deze willekeurige selectie. |
Vorige aflevering
De vorige keer hebben we Excel misbruikt om geautomatiseerd mail te versturen. Daarbij werd het onderwerp automatisch ingevuld, net als de inhoud van de e-mail. Dat gebeurde zowel met als zonder bijlages. Dit werd gedemonstreerd in een werkboek dat als bijlage was toegevoegd.
In deze aflevering
Nu gaan we aan de slag met datums. Het format van de datum zetten we naar onze hand en we gaan er mee rekenen. Hiernaast wordt er een handige manier van datuminvoer getoond met behulp van de Microsoft Date and Time Picker 6.0. Er zijn dit keer 2 bijlages: één met de Date and Time Picker en één zonder voor als de eerste niet werkt of als daar geen interesse voor is.
Bijlage met Date and Time Picker.
Bijlage zonder Date and Time Picker.
Werken met datums in VBA
Datums zijn belangrijke gegevens in Excel. Met datums kunnen periodes worden afgebakend. Ook worden gegevens vaak gerangschikt en/of geselecteerd aan de hand van de datum. Excel kent vele datumfuncties, ook bij de reguliere functies van de werkbladen zelf. Sorteren en autofilter maken daar een heleboel bewerkingen mogelijk aan de hand van de datum. Gebruik deze waar mogelijk en grijp alleen naar VBA als dat niet anders kan, of wanneer het echt iets toevoegt. VBA kent ook vele functies om bijvoorbeeld periodes uit te rekenen of om gegevens aan de hand van datums te rangschikken. De belangrijkste worden in deze aflevering behandeld. De werking van bepaalde functies wordt inzichtelijker wanneer je bekend bent met de wijze waarop Excel de datums berekend.
1900 Datumberekening
Voor de datumberekening in Excel wordt er van uitgegaan dat 1-1-1900 (de geboorte datum van de geestelijk vader van Excel?) dag 1 is. Alle datums worden hiernaar toe teruggerekend. 3-1-1900 is dus 3 enzovoorts. Uren worden gevormd door een 24e deel van de gehele dag en minuten door een 60e deel weer van de uren. Als laatste worden de seconden gevormd door weer een 60e deel van de minuten. Bijvoorbeeld de datum; “01-04-1957 23:53”. Excel rekent dit om tot 20911,99514. Dat is dan inderdaad nog net niet 20912; wat staat voor “02-04-1957 00:00”. Om het ingewikkeld te maken ondersteunde Excel voor Mac het 1904 datumsysteem. Er zit een verschil van 1462 (dagen) tussen beide systemen en dat kan zorgen voor lastige problemen. Vanaf Excel 2011 is ook bij de Mac 1900 het standaard datumsysteem. Bij de instellingen van Excel (voor Excel 2010/2013, via de Tab Bestand > Opties > Geavanceerd > 1904 datumsysteem gebruiken) kan worden gekozen welk datumsysteem wordt gebruikt. Nodeloos om te zeggen dat 1900 warm wordt aanbevolen.
Datumfuncties
Now
Geeft de huidige datum en tijd. De weergave wordt bepaald door de systeem(land)instellingen en de cel-eigenschappen.
Date
Geeft alleen de huidige datum. Ook deze weergave is conform de systeeminstellingen en de cel-eigenschappen.
DatePart("d", Now)
Geeft het dagnummer van de gespecificeerde datum. In dit geval het dagnummer van vandaag in de huidige maand.
DatePart("m", Now)
Geeft het maandnummer van de gespecificeerde datum. In dit voorbeeld het nummer van de huidige maand.
DatePart("yyyy", Now)
Geeft het jaartal van de gespecificeerde datum als resultaat. In dit geval het huidige jaar “2014”. Verglijkbaar met DatePart zijn de volgende functies:
Day (Now); geeft het nummer van de dag van de gespecificeerde datum.
Month (Now); geeft het getal van de maand.
Year (Now); geeft het jaartal van de gespecificeerde datum.
DateSerial(1957, 1, 4)
Deze functie geeft als resultaat een datum samengesteld uit het jaarnummer, de dag en het maandnummer en dit specifiek in deze volgorde. De weergave is in systeemformaat. De functie maakt het gemakkelijk om met jaren, dagen of maanden te rekenen. In het voorbeeld worden getallen gebruikt, maar dat kunnen ook variabelen zijn. Het volgende voorbeeld telt een maand op bij de genoemde datum:
DateSerial(1957, 1, 4 + 1)
Weekday(Now, vbMonday)
Het resultaat van deze functie is het weekdagnummer. Voor een juiste weergave is het nodig dat de startdag van de week wordt aangegeven. In Nederland is dat de maandag, dus vbMonday (of het getal 2). De naam van de dag is als volgt te achterhalen:
WeekdayName(Weekday(Now, vbMonday))
De naam van de dag wordt door WeekDayName niet rechtstreeks uit een datum afgeleid. De functie zet alleen het weekdagnummer om in de naam van die dag. Hetzelfde geldt voor de naam van de maand met:
MonthName(Month(Now))
Het resultaat van beide functies wordt gegeven in de taal van het systeem.
DateValue(“01-04-1981”)
Zet een datum of een als datum herkenbare tekst om in een getal. In dit geval 29677. Dat is dus het aantal dagen vanaf 1-1-1900 bij het 1900 datumsysteem.
TimeValue(Now)
Deze functie geeft de tijdwaarde van een gespecificeerde datumwaarde. Deze waarde kan ook de tijd in tekst zijn. Dat maakt het mogelijk om op een duidelijke manier met tijd te rekenen. Wil je bijvoorbeeld 30 minuten bij de huidige tijd optellen, dan kan dat als volgt:
dEindtijd = Now + TimeValue(“00:30:00”)
Timer
Het resultaat van hiervan is het aantal verstreken seconden sinds middernacht. De functie is bijvoorbeeld in te zetten om te meten hoelang een bepaalde procedure heeft geduurd. Dat kan bijvoorbeeld door de waarde van Timer in een (Public) variabele op te slaan en deze te vergelijken met de waarde van Timer aan het eind van de uitgevoerde procedure. Het verschil is dan de duur van de procedure in seconden.
DateAdd("yyyy", 5, Now)
Hiermee wordt een waarde opgeteld bij de gespecificeerde datum. In dit geval 5 jaar bij het huidige jaar.
DateAdd("q", 1, Now)
Dit telt een aantal kwartalen op. In dit voorbeeld dus 1. Andere intervallen zijn:
m; voor het aantal maanden dat opgeteld wordt.
ww; voor het aantal weken.
y; serieel getal voor dag van het jaar.
d; voor het aantal dagen.
w; voor de weekdagen.
H; voor het aantal uren.
N; voor het aantal minuten en als laatste:
s; voor het aantal seconden.
Het verschil tussen twee datums wordt als volgt berekend:
DateDiff("yyyy", “01-07-1957, Now)
Hiermee wordt het aantal jaren berekend tussen een bepaalde datum en de huidige datum. Dat zou dus bijvoorbeeeld iemands leeftijd kunnen zijn. Dat is een leuk gegeven. Helaas gaat dat niet zomaar op. DateDiff geeft een fout resultaat als de datum op of na de verjaardag in dezelfde maand ligt. Het kan dan even duren voor je een jaartje ouder wordt. Veel mensen hebben er geen probleem mee om even te wachten voor ze weer een jaartje ouder zijn. Echt netjes is het echter niet. Voor de juiste leeftijd moet er een check uitgevoerd worden of de geboortedag op of na de dag van huidige maand ligt. Dat kan als volgt:
iLeeftijd = DateDiff("yyyy", “01-04-1957”, Now) - IIf(Date >= _
DateSerial(Year(Now), Month(“01-04-1957”), Day(“01-04-1957”)), 0, 1)
Hier wordt met behulp van DateSerial de geboortedag en geboortemaand gecombineerd met het huidige jaar. Vervolgens wordt met de IIf functie bepaald of de geboortedag op of na de huidige dag van de maand ligt. Zo ja dan wordt er een jaar afgetrokken. Wanneer de geboortedag voor de huidige datum ligt gebeurt er niets. Op dezelfde manier kan het aantal maanden tussen de datums worden uitgerekend:
iMaanden= DateDiff("m", “01-04-1957”, Now) - IIf(Date >= _
DateSerial(Year(Now), Month(“01-04-1957”), Day(“01-04-1957”)), 0, 1)
Over het weeknummer is veel te vinden op internet. Excel geeft standaard niet het ISO weeknummer zoals wij dat gebruiken in Nederland. Er zijn meerdere formules in omloop waarmee het juiste weeknummer wel berekend kan worden. Vanaf Excel 2010 is dit wat simpeler. Hiervoor kan de functie DatePart worden gebruikt. Bij deze functie kan het ISO formaat (eerste 4 dagen van de week) worden ingesteld:
ISOWeek=DatePart("ww", Now, vbMonday, vbFirstFourDays)
Dat ziet er lekker simpel uit. In deze functie blijkt echter een bug te zitten. Eens in de 400 jaar gaat het fout omdat er bij de berekening niet voldoende rekening wordt gehouden met de schrikkeljaren. Dat is te corrigeren door er voor te zorgen dat de te gebruiken datum een dag is na de woensdag van de uit te rekenen week. Dus dat wordt dan bijvoorbeeld de donderdag
ISOweek = DatePart("ww", Now - _
Weekday(Now, vbMonday) + 4, vbMonday, vbFirstFourDays)
Door met behulp Weekday het dagnummer van de datum af te trekken wordt er teruggerekend naar de zondag van de voorafgaande week. Vier dagen erbij maakt er de donderdag van. Nu zou wél altijd het juiste weeknummer moeten worden weergegeven. Zie voor meer uitleg over het weeknummer onder andere: www.snb-vba.eu/VBA_ISO_weeknummer.html.
Voor de datum kennen we vele formats. Het format is o.a. afhankelijk van de land- instellingen. Door middel van de celeigenschappen is daar invloed op uit te oefenen. In VBA kan dat met het object Format. Dit is een Stringfunctie en hoort dus eigenlijk niet thuis in het rijtje van datum functies. Omdat de functie zoveel mogelijkheden voor de datum biedt wordt deze hier toch genoemd:
Format(“1-4-1957”, "ddd dd - mmm - yyyy")
Het resultaat hiervan is “ma 01 – apr – 1957”
Rekenen met de datum gaat natuurlijk fout wanneer er gerekend wordt met een eenheid die niet als datum wordt herkend door VBA. Of een waarde als datum kan worden herkend is bijvoorbeeld als volgt te controleren:
sCheck = IIf(IsDate(dDatum), "Dit is een datum", "Dit is geen datum"
In de bijlage staan de codevoorbeelden uitgewerkt. Voel je vooral uitgenodigd om daar mee te experimenteren.
De Date and Time Picker
|
Excel heeft verschillende manieren ingebouwd om te controleren of een ingevoerd gegeven een datum is. Dat kan bijvoorbeeld heel goed met gegevensvalidatie (uitleg). In VBA staat ons de eerder uitgelegde functie IsDate ter beschikking. De Date and Time Picker kan worden ingezet wanneer er veel met datums wordt gewerkt en het belangrijk is dat deze gemakkelijk zijn in te voeren. De Date and Time Picker is een kalender ActiveX toepassing, welke afkomstig is van Acces en bij een volledige office-installatie wordt mee geïnstalleerd. De ActiveX werkt vanaf Office 2007 en werkt alleen in de 32 bit versies van Office (welke het meest worden gebruikt). |
Ter demonstratie is er een .xlsm bijlage met werkende Date and Time Picker aan dit artikel toegevoegd. Dit is het werkboek: Bijlage Helpmij NB oktober_2014 Date&Time Picker 6.xlsm.
Wanneer deze met een Object is Missing fout opent is de Active X niet (of niet goed) geïnstalleerd. Zie dan verderop bij Geen Date and TimePicker? De juiste werking kan ook verhinderd worden door de beveiligingsinstellingen van Excel. Pas deze aan in de Tab Bestand > Opties > Vertrouwenscentrum > knop Instellingen voor het vertrouwenscentrum > ActiveX instellingen en dan de optie Waarschuwen of Alle toestaan.
|
|
| De Date and Time Picker kan ook op een userform worden gezet. De userform kun je bijvoorbeeld laten verschijnen na een dubbelklik en weer laten verdwijnen na een klik op de gewenste datum. Omdat het op een userform handiger is als de kalender uitgeklapt getoond wordt kiezen we nu voor het zusje van de Date and Time Picker, namelijk de Monthview. |
| Als eerste stap maken we een userform aan in de VBA editor. Hierdoor verschijnt de Werkset (of via menu Beeld > Werkset wanneer deze niet automatisch verschijnt). Nu voegen we het extra besturingselement toe door rechts op de Werkset te klikken en dan voor extra besturingselementen te kiezen. |
|
Kies vervolgens Microsoft Monthview in de uitklaplijst en teken deze passend op het userform. Het uiterlijk is naar de eigen smaak aan te passen door met de eigenschappen te spelen. In het voorbeeld is de TitelBackColor aangepast. De dagen bovenaan nemen ook die kleur aan. Net als de weeknummers wanneer deze zijn ingeschakeld (ShowWeekNumber). |
Nu is er nog wat code nodig om de ActiveX aan het werk te krijgen. We laten het userform verschijnen na een dubbelklik op de gewenste kolom met de volgende code:Deze procedure komt uiteraard als Event macro bij het gewenste blad.
Wanneer er een datum staat in de betreffende cel is het natuurlijk mooi als de Montview daarmee opstart. Dat kan met behulp van het Event Initialize bij het openen van de Userform:Het is dan nog mooier wanneer na een klik op de gewenste datum deze opgeslagen wordt in de juiste cel en het Userform vervolgens automatisch weer gesloten wordt. In deze mogelijkheid is voorzien. Dit kan op de volgende manier worden bereikt:
Op deze manier kan er op een gebruiksvriendelijke wijze datums worden ingevoerd.
Geen Date and Time Picker?
Wanneer Excel 2003 nog wordt gebruikt dan is er een alternatief; namelijk de Calendar Control 11.
Als de Microsoft Date and Time Picker niet te vinden is tussen de Besturingselementen, dan is deze waarschijnlijk niet (goed) geïnstalleerd. Dat kan alsnog worden gedaan op de volgende manier:
| Download het benodigde bestand hier. Sla het vervolgens op, bijvoorbeeld op het bureaublad en pak het uit. De download bevat twee bestanden. Namelijk mscomct2.ocx en mscomct2.inf. Bij een Windows 32 bit versie is het nu a piece of cake. |
Sla de .ocx op in de System32 map. Meestal is dit C:\Windows\System32 (de schijfletter is afhankelijk van de schijf waarop Windows geïnstalleerd staat). Klik er vervolgens met de rechtermuisknop op; kies Installeren en klaar is Kees.
Voor een 64-bit Windows versie is het helaas iets lastiger, maar goed te doen op de volgende manier:
- Sla de .ocx nu op in de map: "C:\Windows\SysWOW64"
- Type CMD in het zoekvak van het startmenu (Windows 7)
of startscherm (Windows 8)
- Klik met rechts op het resultaat en kies voor uitvoeren als administrator.
- Kopieer het volgende commando:
regsvr32 C:\Windows\SysWOW64\MSCOMCT2.OCX
en plak dit in de Opdrachtprompt (door de opdrachtprompt te selecteren en dan erin te plakken met rechtermuisknop > plakken)
- Na een klik op Enter verschijnt, als het goed is, het bericht dat de actie geslaagd is:
De Date and Time Picker zou nu naar behoren geïnstalleerd moeten zijn.
Alternatieven
Er zijn alternatieven voor de Date and Time Picker te vinden op het internet welke soms ook werken op een 64-bit Office systeem. Hier worden er een paar genoemd.
En hiermee komen we dan aan het einde van deze aflevering.
Samenvatting:
Excel kent vele mogelijkheden om datums te manipuleren en te gebruiken. Op die manier kan er goed met datums gerekend worden en kan er op allerlei verschillende manieren data worden gerangschikt of worden geselecteerd. In deze aflevering staan de belangrijkste voorbeelden. Daarnaast is er een manier beschreven om gebruik te maken van de Microsoft Date and Time Picker en zijn zusje; de Microsoft Monthview. Er is een methode aangereikt om deze alsnog te installeren als dat nog niet het gebeurd was.
Volgende aflevering
De volgende aflevering komt het debuggen aan de orde en worden er een aantal tips en trucs gegeven om het opsporen van fouten iets makkelijker te maken.