In dit artikel zullen we een macro maken om de weekdagen tussen twee datums te extraheren.
In dit voorbeeld moeten we de startdatum en einddatum opgeven voordat de macro wordt uitgevoerd. Macro kiest de startdatumwaarde uit cel J8 en de einddatumwaarde uit cel J9. Na het specificeren van de start- en einddatum, kan de macro worden uitgevoerd door op de knop "Verzenden" te drukken of door op de sneltoetsen Alt + F8 te drukken.
Bij het uitvoeren van de macro worden weekdagen tussen twee datums op een nieuw blad weergegeven. Het nieuw ingevoegde werkblad bevat weekdagen in kolom A, gevolgd door de respectieve datum in kolom B. De uitvoerdatum in kolom B heeft de indeling dd.mm.jj.
Elke week wordt gevolgd door een lege rij om onderscheid te maken tussen twee opeenvolgende weken.
Logische uitleg
In de macro hebben we de functie Weekday gebruikt om onderscheid te maken tussen weekdagen en weekends. De formaatfunctie wordt gebruikt om de datum in het gewenste formaat weer te geven.
weekdag functie
De functie Weekday retourneert een geheel getal dat de dag van de week vertegenwoordigt.
Syntaxis
Weekdag( Date_Value, [First_Day_Of_Week] )
Datum_Waarde specificeert de datumwaarde waarvoor u de dag van de week wilt vinden.
Eerste dag van de week specificeert welke dag van de week als de eerste dag van de week moet worden beschouwd. Het neemt een geheel getal of de waarde gekozen uit de FirstDayOfWeek-opsomming als invoer. Als er geen waarde is opgegeven, wordt FirstDayOfWeek.Sunday als standaardwaarde gebruikt.
|
Opsommingswaarde |
Integere waarde |
Opmerkingen |
|
FirstDayOfWeek.System |
0 |
Eerste dag van de week opgegeven in systeeminstellingen |
|
FirstDayOfWeek.Zondag |
1 |
Zondag (standaard) |
|
FirstDayOfWeek.Maandag |
2 |
maandag |
|
FirstDayOfWeek.Dinsdag |
3 |
Dinsdag |
|
FirstDayOfWeek.Woensdag |
4 |
woensdag |
|
FirstDayOfWeek.Donderdag |
5 |
donderdag |
|
FirstDayOfWeek.Friday |
6 |
vrijdag |
|
FirstDayOfWeek.Zaterdag |
7 |
zaterdag |
Zoals blijkt uit de code van de macro, hebben we Weekday(i, 2) gebruikt om maandag als eerste dag van de week op te geven.
Formaatfunctie:
De functie Opmaak neemt een datumuitdrukking als invoer en retourneert deze als een opgemaakte tekenreeks.
Syntaxis van formaatfunctie
Formaat(Datum_Waarde,formaat)
Datum_Waarde specificeert de waarde van het datumgegevenstype.
Formaat gebruikt tekenreekswaarde om aan te geven welk type datumnotatie vereist is.
De volgende tabel definieert enkele populaire tekens die men kan gebruiken om de vereiste datum-/tijdnotaties te creëren:
|
Karakter |
Beschrijving |
|
NS |
geeft de dag weer als een getal zonder voorloopnul (1 - 31) |
|
dd |
geeft de dag weer als een getal met een voorloopnul (01 - 31) |
|
ddd |
geeft de dag als afkorting weer (zon - za) |
|
dddd |
geeft de dag als volledige naam weer (zondag - zaterdag) |
|
met wie |
geeft de dag van de week weer als een getal (1 voor zondag tot en met 7 voor zaterdag) |
|
ww |
geeft de week van het jaar weer als een getal (1 - 54) |
|
m |
geeft de maand weer als een getal zonder voorloopnul (1 - 12) |
|
mm |
geeft de maand weer als een getal met een voorloopnul (01 - 12) |
|
mmm |
geeft de maand als afkorting weer (jan - dec) |
|
mmm |
geeft de maand weer als een volledige maandnaam (januari - december) |
|
Q |
geeft het kwartaal van het jaar weer als een getal (1 - 4) |
|
ja |
geeft de dag van het jaar weer als een getal (1 - 366) |
|
yy |
geeft het jaar weer als een 2-cijferig nummer (00 - 99) |
|
yyyy |
geeft het jaar weer als een 4-cijferig nummer (100 - 9999) |
|
H |
geeft het uur weer als een getal zonder voorloopnullen (0 - 23) |
|
hh |
geeft het uur weer als een getal met voorloopnullen (00 - 23) |
|
N |
geeft de minuut weer als een getal zonder voorloopnullen (0 - 59) |
|
nn |
geeft de minuut weer als een getal met voorloopnullen (00 - 59) |
|
s |
geeft de seconde weer als een getal zonder voorloopnullen (0 - 59) |
|
ss |
geeft de tweede weer als een getal met voorloopnullen (00 - 59) |
Om aan het einde van elke week een lege rij te laten, hebben we met de weekdagfunctie op zondagen gecontroleerd en de waarde van de variabele "StartingRow" met 1 verhoogd, zodat de volgende rij leeg blijft.
Zoals te zien is in de code van de macro, hebben we de functie Opmaak twee keer op verschillende manieren gebruikt. Ten eerste hebben we Format (i, "ddd") gebruikt om de weekdag te krijgen en ten tweede hebben we Format (i, "dd.mm.yy") gebruikt om de datum in dd.mm.yyyy-formaat te krijgen.
Code uitleg
Dim nieuw werkblad als werkblad
Stel NewWorksheet = Worksheets.Toevoegen
Bovenstaande code wordt gebruikt om de objectnaam van het werkblad te declareren als "NewWorksheet". Worksheets.Add methode wordt gebruikt om het nieuwe werkblad toe te voegen aan de verzameling werkbladen. Set-instructie wordt gebruikt om het gedeclareerde object te initialiseren met het nieuw ingevoegde blad.
Voor i = startdatum tot einddatum
FOR-lus wordt gebruikt om van startdatum naar einddatum te lopen.
Als weekdag(i, 2) < 6 dan
IF-instructie wordt gebruikt om de voorwaarde te controleren en de code uit te voeren op basis van de voorwaarde. Bovenstaande voorwaarde controleert de waardeteruggave door de weekdagfunctie. Als het minder is dan 6, retourneert de IF-voorwaarde waar en wordt de code in de IF-instructie uitgevoerd. Anders wordt het overgeslagen.
Startrij = 1
Startrij = Startrij + 1
De variabele StartingRow wordt gebruikt om door rijen op het werkblad te gaan. Bij het begin wordt de variabele geïnitialiseerd op de eerste rij. Bij elke succesvolle uitvoering van de IF-instructie wordt de variabelewaarde met 1 verhoogd, naar de volgende rij in het blad.
Cellen (Startrij, 1)
De eigenschap Cellen wordt gebruikt om naar een bepaalde cel op het werkblad te verwijzen. Cellen (rijnummer, kolomnummer) kunnen worden gebruikt om naar elke cel op het werkblad te verwijzen door een bepaald rijnummer en kolomnummer als parameters in te voeren. In de code Cells (StartingRow, 1) specificeert 1 de eerste kolom en de variabele "StartingRow" definieert het rijnummer.
Men kan de codes gemakkelijk begrijpen, aangezien ik opmerkingen samen met codes in de macro heb opgenomen.
Volg hieronder voor de code:
Optie Explicit Sub ExtractWeekdays() 'Declaratie van twee variabelen van het gegevenstype Datum Dim StartDate As Date, EndDate As Date 'Declaratie van werkbladvariabele Dim NewWorksheet As Worksheet Dim StartingRow, i As Long 'Begindatum- en einddatumwaarden ophalen uit werkblad StartDate = Sheets( "Macro").Range("J8").Value EndDate = Sheets("Macro").Range("J9").Value 'Initialiseren van het startrijnummer voor uitvoer StartingRow = 1 'Nieuw werkblad invoegen Set NewWorksheet = Worksheets. Add For i = StartDate To EndDate 'Weekdagmethode gebruiken om te controleren of het weekdag of week is en If Weekday(i, 2) < 6 Dan 'Waarde invoegen op nieuw ingevoegd werkblad' Opmaakmethode wordt gebruikt voor het opmaken van de datumwaarde NewWorksheet.Cells( StartingRow, 2) = Format(i, "dd.mm.yy") NewWorksheet.Cells(StartingRow, 1) = Format(i, "ddd") 'Updaten van de waarde van de StartingRow-variabele om naar de volgende rij te gaan StartingRow = StartingRow + 1 End If 'Lege rij voor weekend invoegen If Weekday(i, 2) = 7 Then Startrij = Startrij + 1 End If N ext i Set NewWorksheet = Nothing End Sub
Als je deze blog leuk vond, deel hem dan met je vrienden op Facebook. Ook kunt u ons volgen op Twitter en Facebook.
We horen graag van u, laat ons weten hoe we ons werk kunnen verbeteren en voor u kunnen verbeteren. Schrijf ons op de e-mailsite