Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Arrayformeln (02): Eigene Arrayformeln, MTRANS/TRANSPOSEUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 27) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich eigene Arrayformeln erstellen und nutzen?

Vor Excel 365 konnten natürlich auch einfach benutzerdefinierte Funktionen (UDF) erstellt werden. Wurden sie als Tabellenblattformeln verwendet, galt natürlich das, was generell für Formeln zählte: Es konnte ein Ergebnis pro Zelle erscheinen. Brauchte man mehr Ergebnisse, musste eine Sub() ran, die die Eintragungen in die Zellen vornahm.

Mit den Arrayformeln ab Excel 365 gab und gibt es nun weitaus mehr und vor allem vielfältige Möglichkeiten. So kann eine eigene Funktion einen kompletten Array als Ergebnis liefern - dieser wird dann als übergelaufene Formel in die Zellen neben und unterhalb der Zelle mit der Formel eingetragen.

Als Beispiel eine Funktion, die einen einfachen Monatskalender erstellt:

Function MonatsKalender(ByVal intMonat As Integer, ByVal intJahr As Integer) Dim intArr As Integer, intI As Integer, datDatum As Date Dim arrRet() MonatsKalender = "" intArr = 0 datDatum = CDate("1." & intMonat & "." & intJahr) Do intArr = intArr + 1 ReDim Preserve arrRet(1 To 3, 1 To intArr) arrRet(1, intArr) = Format(datDatum, "DD.MM.YYYY") arrRet(2, intArr) = Format(datDatum, "DDDD") arrRet(3, intArr) = IIf(Weekday(datDatum, vbMonday) = 7, "Frei!", "Arbeiten!") datDatum = datDatum + 1 Loop While Month(datDatum) = intMonat MonatsKalender = arrRet End Function

An die Funktion werden als Parameter die Zahlen für Monat und Jahr übergeben. Die Funktion erhöht dann das Datum so lange, wie der Monat des aktuellen Datums gleich dem übergebenen Monat ist. Bei jedem Datum werden Datumsangabe, Wochentag und ein Eintrag ("Frei!" oder "Arbeiten!") in einen Array eingetragen.

Da wir im Voraus nicht wissen, wie viele Tage der Monat hat, wird der Array „unterwegs“ immer neu dimensioniert. Dies ist nur für die letzte Dimension möglich, also erfolgt dies auch hier so. Die einzelnen Tage stecken somit in der zweiten Dimension (1 To intArr), während die drei Angaben zum Datum in der ersten Dimension sind (1 To 3). Für Tabellenblattfunktionen sollte das Zählen mit 1 statt der 0 beginnen, wozu Option Base 1 gesetzt oder - wie hier - die Deklaration entsprechend erfolgen kann.

In die Zelle kann nun eingetragen werden, wobei in B1 die Monatszahl und in B2 die Jahreszahl steht:

=monatskalender(B1;B2)

Das Ergebnis ist sofort sichtbar: An der Zelle erscheint der Kalender. Nun können einfach in B1 bzw. B2 Monat oder Jahr geändert werden - der Kalender passt sich sofort an.


Transponieren: MTRANS oder TRANSPOSE

Allerdings wird es in vielen Fällen so sein, dass die Richtung der Ergebnisse nicht wie gewünscht ist. Wir haben im Array die Spalten redimensioniert und die Datumsangaben dort eingetragen, also erscheinen die Datumsangaben auch auf die Spalten verteilt.

Dies ist jedoch kein Problem - das Verhalten des Eintragens der Arrayelemente kann mit der integrierten Tabellenblattfunktion MTRANS() geändert werden. Dazu wird die eigene Funktion (oder bei Bedarf auch andere Funktionen) in Mtrans gesetzt:

=MTRANS(monatskalender(B1;B2))

Schon haben wir den Kalender so, wie wir ihn wahrscheinlich erwartet haben.

Eine Alternative ist, diese Tabellenblattfunktion MTRANS() gleich in der Funktion einzusetzen und den Array bereits vor der Ausgabe zu drehen:

MonatsKalender = Application.WorksheetFunction.Transpose(arrRet)

Zu sehen ist, dass im VBA-Code die englischsprachige Variante genutzt werden muss, die hier Transpose ist.

Arrayformeln (05): SORTIEREN/SORTIERENNACH (Formel + VBA)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 117) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Funktionen SORTIEREN()/SOERIERENNCH() (in VBA) nutzen?

Sortieren

Die Funktion SORTIEREN(), die ebenfalls ab Excel 365 zu Verfügung steht, ist vom Prinzip her identisch mit EINDEUTIG(), nur dass sie nicht filtert, sondern sortiert. In der Funktion übergibt man den Bereich und als zweiten Parameter den Index der Spalte, ausgegeben wird der sortierte Bereich.

Beispiel für eine Formel mit dieser Funktion, die nach der zweiten Spalte sortiert:

=SORTIEREN(A2:E15;2)

Mehr Informationen dazu gibt es bei Microsoft: SORTIEREN-Funktion

Auch die Verwendung in VBA ist identisch; die Rückgaben der Arrays und deren Auswertung unterscheiden sich nicht.


Sortierennach

Verfeinerte Sortiermöglichkeiten bietet diese Funktion, weil sie nach verschiedenen Spalten sortieren kann und auf diese Spalten auch andere Funktionen angewendet werden können (siehe auch SORTIERENNACH-Funktion). So sortiert diese Formel den Bereich A2:E15:

=SORTIERENNACH(A2:E15;JAHR(D2:D15);1;E2:E15;1)

Sortiert wird dabei zunächst nach dem Jahr in D2:D15 und zwar aufsteigend: JAHR(D2:D15);1. Anschließend wird nach den Beträgen in E2:E15 sortiert, ebenfalls aufsteigend: E2:E15;1.

Diese Formel sortiert nach den Wochentagen in D2:D15 aufsteigend und anschließend ebenfalls nach den Beträgen:

=SORTIERENNACH(A2:E15;WOCHENTAG(D2:D15);1;E2:E15;1)


VBA

In VBA kann es beim Einsatz leicht zu Fehlermeldungen kommen, wenn Funktionen auf die Bereiche angwendet werden, nach denen sortiert werden soll. Soll z. B. einfach nur nach den Datumsangaben in D2:D15 sortiert werden, reicht folgende Anwendung:

Dim a, b a = Range("A2:E15") b = Range("D2:D15") arr = Application.WorksheetFunction.SortBy(a, b, 1)

Wird jedoch bei b eine Funktion verwendet, schimpft Excel schnell. Abhilfe schafft hier, die Formel als String zusammenzustellen und diesen mit EVALUATE() berechnen zu lassen:

Dim strFormel As String strFormel = "=sortby(A2:E15, year(D2:D15), 1, E2:E15,1)" arr = Evaluate(strFormel)

Dieses Beispiel entspricht dem ersten Formelbeispiel. Die Auswertung bzw. weitere Verarbeitung des Ergebnisarrays würde dann wie bei EINDEUTIG() erfolgen; an die Stellen der MsgBoxen müsste der Code, der das Weitere erledigen soll:

Sub Sortierennach_vba() Dim arr, intI As Integer, intAnzahlEl As Integer Dim strFormel As String strFormel = "=sortby(A2:E15, year(D2:D15), 1, E2:E15,1)" arr = Evaluate(strFormel) intAnzahlEl = Application.WorksheetFunction.CountA(arr) MsgBox "Ubound: " & UBound(arr) & vbNewLine & "Anzahl: " & intAnzahlEl If intAnzahlEl = UBound(arr) Then ' Es gibt nur eine Zeile MsgBox arr(2) & ", " & arr(3) & ": " & Format(arr(4), "DD.MM.YYYY") Else ' Mehrere Zeilen For intI = 1 To UBound(arr) MsgBox arr(intI, 2) & ", " & arr(intI, 3) & ": " & Format(arr(intI, 4), "DD.MM.YYYY") Next End If End Sub

Arrayformeln (06): FILTER (Formel + VBA)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 127) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich Daten per Funktion filtern (und mit VBA auf Ergebnisse zugreifen)?

Datenfilter, Autofilter und seit Excel 365 nun auch eine Funktion FILTER() - verschiedene Möglichkeiten gibt es ja. Was konkret verwendet wird, wird sich immer nach den konkreten Umständen richten müssen. Mit der Funktion haben wir nun eine schnelle und sehr flexible Variante, die vor allem auch bei der Nutzung durch VBA dank reduzierter Ergebnismengen zu besseren Laufzeiten führen kann. Ausführliche Informationen gibt es natürlich bei Microsoft: FILTER-Funktion.

Der Einsatz in einer Formel ist sehr einfach: Das folgende Beispiel nimmt den Bereich A2:F15, sieht dort in D2:D15 nach, wo "Berlin" enthalten ist und gibt diese Zeilen als Bereich aus:

=FILTER(A2:F15;D2:D15="Berlin";"Nichts gefunden")

Mehrere Suchkriterien können durch das +-Zeichen für Oder bzw. das *-Zeichen für Und verknüpft werden. So sucht die folgende Formel nach Einträgen, die "Berlin" oder "Hamburg" enthalten:

=FILTER(A2:F15;(D2:D15="Berlin")+(D2:D15="Hamburg");"Nix gefunden!")

Diese Formel sucht nach Zeilen, die als Ort "Berlin" enthalten und ein Datum aus dem Jahr 2015 haben und deren Beträge größer als 200 sind:

=FILTER(A2:F15;(D2:D15="Berlin")*(JAHR(E2:E15)=2015)*(F2:F15>200);"Nix gefunden!")

Verknüpfungen von Und und Oder sind natürlich auch möglich. Dabei muss allerdings auf die richtige Klammersetzung geachtet werden. Die nächste Formel sucht Einträge mit ("Berlin oder "Hamburg") und aus dem Jahr 2015 und mit Beträgen größer als 200:

=FILTER(A2:F15;((D2:D15="Berlin")+(D2:D15="Hamburg"))*(JAHR(E2:E15)=2015)*(F2:F15>200);"Nix gefunden!")


VBA

Der Einsatz in VBA ist nahezu identisch, wenn mit EVALUATE() gearbeitet wird. Die Formel wird dazu als String zusammengesetzt und mit Evaluate wird diese Berechnung durchgeführt:

strFormel = "=filter(A2:E15, D2:D15=""Berlin"", """")" arr = Evaluate(strFormel)

Die weitere Verarbeitung des Ergebnisarrays erfolgt wie bei der Funktion EINDEUTIG() unter Weiterverarbeitung des Ergebnisarrays beschrieben. Hier ein Beispiel im Ganzen, das die letzte Formel nutzt:

Sub Filter_vba() Dim arr, intI As Integer, intAnzahlEl As Integer Dim strFormel As String strFormel = "=FILTER(A2:F15,((D2:D15=""Berlin"")+(D2:D15=""Hamburg""))*(year(E2:E15)=2015)*(F2:F15>200),""Nix gefunden!"")" arr = Evaluate(strFormel) intAnzahlEl = Application.WorksheetFunction.CountA(arr) MsgBox "Ubound: " & UBound(arr) & vbNewLine & "Anzahl: " & intAnzahlEl If intAnzahlEl = UBound(arr) Then ' Es gibt nur eine Zeile MsgBox arr(2) & ", " & arr(3) & ": " & Format(arr(4), "DD.MM.YYYY") Else ' Mehrere Zeilen For intI = 1 To UBound(arr) MsgBox arr(intI, 2) & ", " & arr(intI, 3) & ": " & Format(arr(intI, 4), "DD.MM.YYYY") Next End If End Sub

Combobox mit Monaten füllenMakro/Sub/Prozedur

Kategorien: Steuerelemente ▸ Userform und Datum/Zeit ▸ Steuerelemente

(Tipp 88) Nachricht zum Beitrag an Autor Nach oben

Wie kann man eine ComboBox in einer UserForm mit den Monatsnamen eines Jahres füllen?

Für jeden Monat wird ein beliebiges (aber in jedem Monat vorhandenes, also nicht der 31.) Datum erzeugt, das dann per Datumsformat in die Box eingetragen wird. Das Jahr spielt dabei keine Rolle, da es nur um den Monat geht.

Private Sub UserForm_Initialize() Dim intI As Integer ComboBox1.Clear For intI = 1 To 12 ComboBox1.AddItem Format(DateSerial(1998, intI, 1), "MMMM") Next intI ComboBox1.ListIndex = 0 End Sub

Das 1904-Datumssystem oder Wie kann ich mit negativen Zeiten rechnen?Tipp

Kategorien: Basics ▸ Datum/Zeit und Datum/Zeit ▸ Zeit

(Tipp 227) Nachricht zum Beitrag an Autor Nach oben

Oftmals bekommt man auf die Frage Wie kann ich negative Zeiten darstellen? die Antwort Stelle unter Optionen/Berechnen auf das 1904-Datumssystem um.

Diese Umstellung hat jedoch gravierende Folgen, denn sämtliche Daten von Arbeitsmappen, die unter dem Standard-Datumssystem erfasst worden sind, sind nicht kompatibel dazu.

Im Standard wird dem Datum 01.01.1900 der Wert 1 zugewiesen, im 1904-System bekommt der 01.01.1904 diesen Wert. Das bedeutet, dass alle Daten des Standarddatumssystems um 4 Jahre verschoben werden und somit dieses System sich im Grunde nur für reine Stundenberechnungen eignet.

Zweitens kann man immer noch nicht negative Zeiten eingeben, sondern diese nur als Ergebnis einer Berechnung erhalten. So ist die Eingabe -04:00 nicht erlaubt, aber =01:00-05:00 liefert das gewünschte Ergebnis.

Bevor man also auf das 1904-Datumssystem umstellt, sollte man sich Gedanken darüber machen, ob man dieses wirklich benötigt oder einen anderen Weg wählt. Siehe auch:

Datum aus Kalenderwoche errechnenUDF - benutzerdefinierte Funktion

Kategorie: Datum/Zeit ▸ Datum

(Tipp 174) Nachricht zum Beitrag an Autor Nach oben

Wie kann man aus einer angegebenen Kalenderwoche das Datum bestimmen? Folgende Daten sind gegeben: 1999 (Jahr) 42 (Kalenderwoche) 1 (Tag)

In die Zelle muss dann eingegeben werden: =TagAusKW(Jahr;KW;Tag).

Function TagAusKW(Jahr As Integer, KW As Integer, Tag As Integer) As Date Dim lngT As LongPtr lngT = DateSerial(Jahr, 1, 4) lngT = lngT - Weekday(lngT, 2) + 7 * KW - 7 If (Year(lngT + 4) = Jahr) Then TagAusKW = lngT + Tag End Function



Datum in einer Combobox auswählen, ohne daß dafür extra eine Tabelle hinterlegt istMakro/Sub/Prozedur

Kategorien: Steuerelemente ▸ Userform und Datum/Zeit ▸ Steuerelemente

(Tipp 80) Nachricht zum Beitrag an Autor Nach oben

Wie kann man ein Datum aus einer Combobox wählen, ohne dass man dafür erst eine Datumstabelle anlegen muss?

Der Code trägt die Datumsangaben vom 01.01. bis zum 31.01. des aktuellen Jahres in die ComboBox ein. Während der Schleife wird in intIndex die Stelle gespeichert, an der sich das heutige Datum befindet. Mit ComboBox1.ListIndex = intIndex wird zum Schluss dieser Eintrag ausgewählt:

Private Sub UserForm_Initialize() Dim datDatum As Date, intIndex As Integer intIndex = -1 For datDatum = CDate("01.01." & Year(Date)) To CDate("31.12." & Year(Date)) ComboBox1.AddItem datDatum If datDatum = Date Then intIndex = ComboBox1.ListCount - 1 Next datDatum If intIndex >= 0 Then ComboBox1.ListIndex = intIndex End Sub

Tipp: Damit das Datum in der ComboBox nur ausgewählt, aber nicht überschrieben werden kann, sollte die Eigenschaft Style auf 2 - frmStyleDropDownList gesetzt werden.

Datum nach Tag, Monat oder Jahr sortierenFormellösungArrayfunktion/Matrixfunktion

Kategorien: Datum/Zeit ▸ Datum und Filter/Sortieren

(Tipp 332) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich Datumsangaben in einem Bereich nicht nach Datum, sondern wahlweise auch nach Monat oder Tag sortieren?

Angenommen die Datumsangaben stehen in A2:A8:

Datum aufteilen

Die Datumsangaben können mit Formeln auf mehrere Spalten aufgeteilt werden:

  • Jahr: =WENN(ISTZAHL(A2);JAHR(A2);"")
  • Monat: =WENN(ISTZAHL(A2);MONAT(A2);"")
  • Tag: =WENN(ISTZAHL(A2);TAG(A2);"")

Anschließend kann ganz normal nach einer der Spalten sortiert werden.


Dynamische Arrayfunktion SORTIERENNACH() (ab Excel 365)

Mit dieser Funktion kann das Sortieren in einem Schritt erledigt werden, sie gibt alle Daten auf einmal sortiert aus.

Nach Monat aufsteigend (die 1 steht für aufsteigend):

=SORTIERENNACH(A2:A8;MONAT(A2:A8);1)

Nach Monat und Tag aufsteigend:

=SORTIERENNACH(A2:A8;MONAT(A2:A8);1;TAG(A2:A8);1)

Falls ein größerer Bereich in die Funktion eingetragen werden soll, falls zum Beispiel abzusehen ist, dass es mehr Datumsangaben werden, werden auch die noch leeren Zellen ausgegeben. Das sieht nicht schön aus. Dafür kann die Adresse der letzten Zelle mit INDIREKT() „zusammengebastelt“ werden. Hier als Beispiel über die Funktion ANZAHL():

=SORTIERENNACH(INDIREKT("A2:A"&ANZAHL(A2:A15)+1);MONAT(INDIREKT("A2:A"&ANZAHL(A2:A15)+1));1;TAG(INDIREKT("A2:A"&ANZAHL(A2:A15)+1));1)

Differenz zwischen zwei Datumswerten (DATEDIF)Formellösung

Kategorie: Datum/Zeit ▸ Datum

(Tipp 180) Nachricht zum Beitrag an Autor Nach oben

Um die Differenz zwischen 2 Datumswerten zu erhalten, nimmt man einfach die Differenz der beiden Werte und formatiert die Zelle als Standard. Was aber wenn die Zeitspanne größer als ein Jahr ist und man getrennt Jahre, Monate und Tage erhalten will?

Hierzu gibt es in Excel eine schöne Funktion: DATEDIF.

Syntax: DATEDIF(Datum1;Datum2;"Parameter")

Das Datum2 muß größer als das Datum1 sein, der Parameter muss immer in "" stehen.

yJahreDATEDIF("01.01.98";"15.02.99";"y")= 1
mMonateDATEDIF("01.01.98";"15.02.99";"m")= 13
dTageDATEDIF("01.01.98";"15.02.99";"d")= 410
ymMonate minus JahreDATEDIF("01.01.98";"15.02.99";"ym")= 1 (13 Monate -1 Jahr (12 Monate))
ydTage minus JahreDATEDIF("01.01.98";"15.02.99";"yd")= 45 (410 Tage - 1 Jahr (365 Tage!))
mdTage minus Monate minus JahreDATEDIF("01.01.98";"15.02.99";"md")= 14 (Differenz vom 01.02. & 15.02))

Das Ergebnis der letzten beiden Beispiele sieht seltsam aus - man beachte aber, dass es sich um die Differenz handelt.

Kalenderwoche nach DIN (VBA + Formel)UDF - benutzerdefinierte FunktionFormellösung

Kategorie: Datum/Zeit ▸ Datum

(Tipp 169) Nachricht zum Beitrag an Autor Nach oben

Wie kann die Kalenderwoche eines Datums nach DIN berechnet werden?

Formeln

Am einfachsten gehte s sicher so:

=ISOKALENDERWOCHE(A2)

=KALENDERWOCHE(A2;2)

Übrigens können (ab Excel 365) mit nur einer einzigen Formel alle Tage eines Jahres mit Kalenderwochen und Wochentagen (und was auch immer) angezeigt werden:

=TEXT(SEQUENZ(TAGE("31.12."&F1;"1.1."&F1)+1;1;"1.1."&F1;1);"TT.MM.JJJJ")&" (KW "&TEXT(ISOKALENDERWOCHE(SEQUENZ(TAGE("31.12."&F1;"1.1."&F1)+1;1;"1.1."&F1;1));"00")&" "&TEXT(SEQUENZ(TAGE("31.12."&F1;"1.1."&F1)+1;1;"1.1."&F1;1);"TTT")&")"


UDF - benutzerdefinierte Funktionen

Früher, als es die schönen Excelfunktionen noch nicht gab, musste man sich damit behelfen:

Diese Funktion stammt von Christoph Kremer:

Function DINKwoche(Datum) Dim tmp tmp = DateSerial(Year(Datum + (8 - WeekDay(Datum)) Mod 7 - 3), 1, 1) DINKwoche = ((Datum - tmp - 3 + (WeekDay(tmp) + 1) Mod 7)) \ 7 + 1 End Function

Zweite Möglichkeit:

Function kw(Datum As Date) As Single kw = Format(Datum, "ww", , vbFirstFourDays) - IIf(Weekday(Datum) = 1, 1, 0) End Function

Einige alte Beispiele auf diesen Seiten basieren noch auf diesen Berechnungen.

Ostersonntag aus Jahr errechnenFormellösung

Kategorie: Datum/Zeit ▸ Datum

(Tipp 203) Nachricht zum Beitrag an Autor Nach oben

Wie kann man das Datum des Ostersonntags aus einem Jahr errechnen?

Das Jahr steht in A1, die Zelle mit der Formel muss als Datum formatiert sein.

=DM((TAG(MINUTE(A1/38)/2+55)&".4."&A1)/7;)*7-6

UDF - Benutzerdefinierte Funktionen (auch Matrixfunktionen)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionTipp

Kategorien: Basics ▸ UDF und UDF ▸ Basics

(Tipp 164) Nachricht zum Beitrag an Autor Nach oben

Grundsätzliches

Benutzerdefinierte Funktionen, also UDF, sind Funktionen, die man sich selbst im VBA-Editor erstellt. Dazu benennt man sie nicht mit Sub, sondern mit Function. Der Name der Funktion liefert dabei den Rückgabewert. Heißt also eine Funktion "MeineFunktion(...)", schreibt man in die Zelle "=MeineFunktion(...)". Ebenfalls kann man Funktionen einsetzen, um durch Subs bestimmte Berechnungen durchführen zu lassen.

Beispiel: Eine Funktion, die immer das Datum zurückgibt, das in 14 Tagen liegt, Die Funktion braucht keine Parameter, sie wäre schlicht und einfach:

Function Datum_14Tage() Datum_14Tage = Date + 14 End Function

Darauf können wir einfach per Sub zugreifen:

Sub Datumstest() MsgBox Datum_14Tage End Sub

Oder in die Zelle eingetragen:

=Datum_14Tage()


Ergebnisse/Rückgaben

Prinzipiell können benutzerdefinierte Funktionen alle möglichen Datentypen als Rückgabewerte haben: Strings, Zahlen, Datumsangaben usw. Selbst Arrays können Ergebnisse sein, die dann von anderen Subs verarbeitet werden.


Dynamische Arrayformeln/Matrixfunktionen

Seit Excel 365 ist es sogar möglich, Arrays als Ergebnisse solcher (dann Matrix-) Funktionen in Zellen eintragen zu lassen. Nehmen wir folgende Funktion als Beispiel, die für einen Monat eine kleine Liste mit Wochentagen und Kalenderwochen erstellt:

Function Monatstabelle(ByVal intMonatszahl As Integer, ByVal intJahr As Integer) Dim datDatum As Date, arrS(), lngArr As LongPtr datDatum = CDate("1." & intMonatszahl & "." & intJahr) lngArr = 0 Do lngArr = lngArr + 1 ReDim Preserve arrS(1 To 3, 1 To lngArr) arrS(1, lngArr) = datDatum arrS(2, lngArr) = Format(datDatum, "DDD") arrS(3, lngArr) = Application.WorksheetFunction.IsoWeekNum(datDatum) datDatum = datDatum + 1 Loop While Month(datDatum) = intMonatszahl Monatstabelle = Application.WorksheetFunction.Transpose(arrS) End Function

Sie erwartet als Parameter die Zahl des Monats und das Jahr. Wenn diese Angaben in D1 und in E1 stehen, können wir in der Tabelle diese Arrayformel eintragen:

=Monatstabelle(D1;E1)

Tipp nebenbei: Mit Transpose oder in deutscher Syntax MTRANS kann eingestellt werden, in welcher Richtung ein Array ausgegeben wird. Diese (eigentlich Tabellenblatt-) Funktion transponiert den Array, so dass dieser wahlweise über Spalten oder über Zeilen ausgegeben wird.


Formel übergelaufen

Beim ersten Verwenden einer Matrixfunktion (also nicht nur einer eigenen) erscheint die Meldung:

Formel übergelaufen - Ihre Formel hat mehrere Werte zurückgegeben, weshalb wir sie in die benachbarten leeren Zellen haben überlaufen lassen.

Diese Meldung besagt nichts anderes, als dass sich die Zelle mit der Formel nun verhält, wie man es in CSS mit float: left; beschreiben würde. Die Ergebnisse der Formel fließen rechts und unterhalb von der Eingabezelle.

Mit dem Schnittmengenoperator @ nach dem Gleichheitszeichen können Sie übrigens einstellen, dass nur der erste Wert des Arrays in der Zelle erscheint.


Um zu gewährleisten, dass eine in einem Tabellenblatt eingesetzte UDF immer rechnet, sollte man an den Anfang der Funktion schreiben:

Application.Volatile

Wochendaten zur Zahl einer Kalenderwoche bestimmenMakro/Sub/ProzedurUDF - benutzerdefinierte Funktion

Kategorie: Datum/Zeit ▸ Datum

(Tipp 430) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus der Zahl einer Kalenderwoche die entsprechenden Daten der Woche errechnen?

Die folgende Funktion stellt ersten und letzten Tag der Kalenderwoche fest und gibt auf dieser Basis den Zeitraum aus:

Function Zeitraum(ByVal Jahr As Integer, ByVal KW As Variant) As String Dim datBeg As Date, lngT As LongPtr Application.Volatile Zeitraum = "" If IsNumeric(Jahr) And IsNumeric(KW) Then lngT = DateSerial(Jahr, 1, 4) lngT = lngT - Weekday(lngT, 2) + 7 * KW - 7 If (Year(lngT + 4) = Jahr) Then datBeg = lngT + 1 Zeitraum = Format(datBeg, "DD.MM.YYYY") & " - " & Format(datBeg + 6, "DD.MM.YYYY") End If End Function

In die Zelle muss dann nur noch:

=Zeitraum(Jahr;KW)

Soll es ohne Funktion in der Zelle funktionieren und der Zeitraum sofort eingetragen werden, kann folgender Code in das Klassenmodul der jeweiligen Tabelle (Doppelklick auf Tabelle im VBA-Editor):

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row >= 4 And Target.Column = 1 Then Application.EnableEvents = False Target = Zeitraum(Range("B1"), Target) Application.EnableEvents = True End If End Sub

Der Code wird ausgeführt, wenn die Eingabezelle in Spalte 1 und Zeile >= 4 liegt; verwendet wird das Jahr aus Zelle B1. Application.EnableEvents = False ist hier wichtig, weil die eingegebene KW durch den Zeitraum überschrieben wird und das Change-Ereignis sonst immer wieder aufgerufen würde.