Geben Sie den Wochentag Mo – Fr in einem Arbeitsblatt mit VBA in Microsoft Excel
ein In diesem Artikel erstellen wir ein Makro, um die Wochentage zwischen zwei Daten zu extrahieren.
In diesem Beispiel müssen wir das Start- und Enddatum angeben, bevor wir das Makro ausführen. Das Makro wählt den Startdatumwert aus Zelle J8 und den Enddatumwert aus Zelle J9 aus. Nach Angabe des Start- und Enddatums kann das Makro durch Drücken der Schaltfläche „Senden“ oder Drücken der Tastenkombinationen Alt + F8 ausgeführt werden.
Beim Ausführen des Makros werden Wochentage zwischen zwei Daten auf einem neuen Blatt zurückgegeben. Das neu eingefügte Arbeitsblatt enthält Wochentage in Spalte A, gefolgt vom jeweiligen Datum in Spalte B. Das Ausgabedatum in Spalte B hat das Format TT.MM.JJ.
Auf jede Woche folgt eine leere Zeile, um zwischen zwei aufeinander folgenden Wochen zu unterscheiden.
Logische Erklärung
Im Makro haben wir die Wochentagsfunktion verwendet, um zwischen Wochentagen und Wochenenden zu unterscheiden. Mit der Formatierungsfunktion wird das Datum im gewünschten Format angezeigt.
Wochentagsfunktion
Die Funktion Wochentag gibt einen ganzzahligen Wert zurück, der den Wochentag darstellt.
Syntax
Wochentag (Date_Value, [First_Day_Of_Week])
Date_Value gibt den Datumswert an, für den Sie den Wochentag ermitteln möchten.
First_Day_Of_Week gibt an, welcher Wochentag als erster Wochentag betrachtet werden soll. Als Eingabe wird ein ganzzahliger Wert oder der aus der FirstDayOfWeek-Enumeration ausgewählte Wert verwendet. Wenn kein Wert angegeben wird, wird FirstDayOfWeek.Sunday als Standardwert verwendet.
Enumeration value |
Integer value |
Remarks |
FirstDayOfWeek.System |
0 |
First day of week specified in system settings |
FirstDayOfWeek.Sunday |
1 |
Sunday (default) |
FirstDayOfWeek.Monday |
2 |
Monday |
FirstDayOfWeek.Tuesday |
3 |
Tuesday |
FirstDayOfWeek.Wednesday |
4 |
Wednesday |
FirstDayOfWeek.Thursday |
5 |
Thursday |
FirstDayOfWeek.Friday |
6 |
Friday |
FirstDayOfWeek.Saturday |
7 |
Saturday |
Wie aus dem Code des Makros hervorgeht, haben wir Wochentag (i, 2) verwendet, um Montag als ersten Tag der Woche anzugeben.
Formatierungsfunktion
Die Format-Funktion verwendet einen Datumsausdruck als Eingabe und gibt ihn als formatierte Zeichenfolge zurück.
Syntax der Formatfunktion
Format (Date_Value, Format)
Date_Value gibt den Wert des Datumsdatentyps an.
Format verwendet einen Zeichenfolgenwert, um anzugeben, welche Art von Datumsformat erforderlich ist.
In der folgenden Tabelle sind einige beliebte Zeichen definiert, mit denen die erforderlichen Datums- / Zeitformate erstellt werden können:
Character |
Description |
d |
displays the day as a number without a leading zero (1–31) |
dd |
displays the day as a number with a leading zero (01–31) |
ddd |
displays the day as an abbreviation (Sun–Sat) |
dddd |
displays the day as a full name (Sunday–Saturday) |
w |
displays the day of the week as a number (1 for Sunday through 7 for Saturday) |
ww |
displays the week of the year as a number (1–54) |
m |
displays the month as a number without a leading zero (1–12) |
mm |
displays the month as a number with a leading zero (01–12) |
mmm |
displays the month as an abbreviation (Jan–dec) |
mmmm |
displays the month as a full month name (January–december) |
q |
displays the quarter of the year as a number (1–4) |
y |
displays the day of the year as a number (1–366) |
yy |
displays the year as a 2-digit number (00–99) |
yyyy |
displays the year as a 4-digit number (100–9999) |
h |
displays the hour as a number without leading zeros (0–23) |
hh |
displays the hour as a number with leading zeros (00–23) |
n |
displays the minute as a number without leading zeros (0–59) |
nn |
displays the minute as a number with leading zeros (00–59) |
s |
displays the second as a number without leading zeros (0–59) |
ss |
displays the second as a number with leading zeros (00–59) |
Um am Ende jeder Woche eine leere Zeile zu belassen, haben wir mithilfe der Wochentagsfunktion nach Sonntagen gesucht und den Wert der Variablen „StartingRow“ um 1 erhöht, sodass die nächste Zeile leer bleibt.
Wie im Code des Makros zu sehen ist, haben wir die Format-Funktion zweimal auf unterschiedliche Weise verwendet. Erstens haben wir Format (i, „ddd“) verwendet, um den Wochentag abzurufen, und zweitens haben wir Format (i, „dd.mm.yy“) verwendet, um das Datum im Format dd.mm.yyyy abzurufen.
Code Erklärung
NewWorksheet als Arbeitsblatt dimmen Set NewWorksheet = Worksheets.Add Above-Code wird verwendet, um den Arbeitsblatt-Objektnamen als „NewWorksheet“ zu deklarieren.
Die Methode Worksheets.Add wird verwendet, um das neue Arbeitsblatt zur Worksheets-Auflistung hinzuzufügen. Die Set-Anweisung wird verwendet, um das deklarierte Objekt mit dem neu eingefügten Blatt zu initialisieren.
For i = StartDate To EndDate Die FOR-Schleife wird verwendet, um eine Schleife vom Startdatum zum Enddatum durchzuführen.
Wenn Wochentag (i, 2) <6, wird die IF-Anweisung verwendet, um die Bedingung zu überprüfen und den Code basierend auf der Bedingung auszuführen. Die obige Bedingung überprüft die Wertrückgabe durch die Wochentagsfunktion. Wenn es kleiner als 6 ist, gibt die IF-Bedingung true zurück und der Code in der IF-Anweisung wird ausgeführt. Andernfalls wird es übersprungen.
StartingRow = 1 StartingRow = StartingRow + 1 Die Variable StartingRow wird verwendet, um durch Zeilen im Arbeitsblatt zu navigieren. Zu Beginn wird die Variable in der ersten Zeile initialisiert. Bei jeder erfolgreichen Ausführung der IF-Anweisung wird der Variablenwert um 1 erhöht und zur nächsten Zeile im Blatt verschoben.
Zellen (StartingRow, 1)
Die Cells-Eigenschaft wird verwendet, um auf eine bestimmte Zelle im Arbeitsblatt zu verweisen.
Zellen (Zeilennummer, Spaltennummer) können verwendet werden, um auf eine beliebige Zelle im Arbeitsblatt zu verweisen, indem eine bestimmte Zeilennummer und Spaltennummer als Parameter eingegeben werden. Im Code Cells (StartingRow, 1) gibt 1 die erste Spalte an und die Variable „StartingRow“ definiert die Zeilennummer.
Man kann die Codes leicht verstehen, da ich Kommentare zusammen mit Codes in das Makro aufgenommen habe.
Bitte folgen Sie unten für den Code
Option Explicit Sub ExtractWeekdays() 'Declaring two variables of Date data type Dim StartDate As Date, EndDate As Date 'Declaring worksheet variable Dim NewWorksheet As Worksheet Dim StartingRow, i As Long 'Getting start date and end date values from worksheet StartDate = Sheets("Macro").Range("J8").Value EndDate = Sheets("Macro").Range("J9").Value 'Initializing the starting row number for output StartingRow = 1 'Inserting new worksheet Set NewWorksheet = Worksheets.Add For i = StartDate To EndDate 'Using Weekday method to check whether it is Weekday or weekand If Weekday(i, 2) < 6 Then 'Inserting value on newly inserted worksheet 'Format method is used for formatting the date value NewWorksheet.Cells(StartingRow, 2) = Format(i, "dd.mm.yy") NewWorksheet.Cells(StartingRow, 1) = Format(i, "ddd") 'Updating StartingRow variable value for moving to next row StartingRow = StartingRow + 1 End If 'Inserting blank row for weekend If Weekday(i, 2) = 7 Then StartingRow = StartingRow + 1 End If Next i Set NewWorksheet = Nothing End Sub
Wenn dir dieser Blog gefallen hat, teile ihn mit deinen Freunden auf Facebook und Facebook.
Wir würden gerne von Ihnen hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern und für Sie verbessern können. Schreiben Sie uns unter [email protected]