Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Arbeitsmappen eines Ordners druckenMakro/Sub/Prozedur

Kategorien: Dateien und Ordner ▸ Dateioperation und Drucken/Seite

(Tipp 20) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich mit VBA alle Arbeitsmappen eines Ordners ausdrucken lassen?

Der Code nimmt sich die erste Exceldatei, die im Ordner in der Variablen ist. Die ruft er auf, druckt deren erstes Blatt und schließt sie wieder. Mit Dir() wird nun die nächste Datei genommen und die gleiche Prozedur erneut ausgeführt - so lange, bis mit Dir() keine weitere Datei gefunden wird.

Sub AlleDrucken() Dim WB As Workbook Dim strFName As String strFName = Dir("C:\Excel\*.xls") While strFName <> "" Set WB = Workbooks.Open(Filename:=strFName) WB.PrintOut WB.Close strFName = Dir() Wend End Sub

Der Code funktioniert zwar, wie er hier angegeben ist. Allerdings ist die Gefahr eines Fehlers groß, wenn bereits Dateien geöffnet sind. Zumindest die Prüfung darauf sollte noch mit eingebaut werden.

Im Zusammenhang damit muss auch beachtet werden, ob eine offene Datei gespeichert ist. Wenn nicht, kommt es beim Schließen zur entsprechenden „Speichern?“-Frage durch Excel und der Code wird angehalten.

Arrayformeln (01)FormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 18) Nachricht zum Beitrag an Autor Nach oben

Was sind die Arrayformeln, die ab Excel 365 eingeführt wurden?

Mit Excel 365 wurde das System der Formeln neu erarbeitet, wie mehrfach berichtet wurde. Allerdings handelt es sich dabei eigentlich nur um eine Ergänzung, wenn auch eine sehr sinnvolle und nützliche. Die alten Formeln arbeiten ganz normal weiter - hier muss nicht befürchtet werden, dass irgendetwas nicht mehr funktioniert. Neu sind hingegen die Arrayformeln. Zum Vergleich: Bisher konnte in Formeln mit kompletten Bereichen gearbeitet werden, zum Beispiel mit Summewenn. Die Eingabe der Formeln musste dazu mit Strg + Umschalt + Enter abgeschlossen werden, woraufhin Excel um die Formel geschweifte Klammern {} legte. Die Formel wurde somit als Matrixformel erkannt und es wurde ein kompletter Bereich verarbeitet. Das ERgebnis hingegen stand als ein Wert in einer Zelle.
Mit den Arrayformeln wurde dies erweitert und auch vereinfacht. Sollen komplette Bereiche verarbeitet werden, wird die Eingabe der Formel ganz normal beendet; die geschweiften Klammern werden nicht mehr benötigt. So zum Beispiel diese Formel, die in B2:B13 Nach "Juni" sucht und aus C2:C13 die Werte addiert:

=SUMMEWENN(B2:B13;"Juni";C2:C13)

Auch hier haben wir in einer Zelle ein Ergebnis, nämlich die Summe. Der Unterschied bis hierher liegt nur in den fehlenden geschweiften Klammern.


Überlaufende bzw. verschüttete Formeln

Der größte Unterschied wird jedoch deutlich, wenn in eine Zelle folgende einfache Formel eingegeben wird:

=A2:C13

Geschieht dies bei einer Excelinstallation zum ersten Mal, erscheint eine Meldung:

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

Das Ergebnis wird dann auch deutlich: Die Formel liefert alle Werte, die sich im Bereich befinden, der in der Formel angegeben ist - von der 1 in A2 bis zur 600 in C13. Der erste Wert steht dabei in der Zelle mit der Formel, die anderen Werte rechts neben und unter dieser Zelle - die Formel ist übergelaufen. Das Verhalten ist vergleichbar mit der CSS-Eigenschaft float: left;. Allerdings steht die Formel tatsächlich nur in der einen Zelle; sie kann auch nur dort bearbeitet werden. Sichtbar wird das, wenn im Ergebnis der Inhalt einer anderen Zelle gelöscht werden soll - es geht nicht.

Dieses Verhalten, dass mehrere Ergebnisse ausgegeben werden sollen, kann vielfältig genutzt werden. Beispiel für eine weitere einfache Formel:

=WENN(C2:C13>400;B2:B13;"")

In Zeile 2 neben der Basistabelle liefert sie in den entsprechenden Zeilen die Monate, bei denen die Beträge größer als 400 sind. Das Gleiche passiert natürlich auch, wenn die Formel in eine andere Zeile eingetragen wird - nur hat man dann den Offset, weil die Ergebnisse ab der Zelle mit der Formel angezeigt werden.

Diese Formel würde die Zahlen in den entsprechenden Zeilen anzeigen, die zum "Juni" gehören:

=WENN(B2:B13="Juni";C2:C13;"")

Natürlich kann die als Arrayfunktion verwendete Wenn-Funktion auch in anderen Funktionen eingesetzt werden. Hier werden alle Zahlen aus C addiert, wenn sie größer als 400 sind:

=SUMME(WENN(C2:C13>400;C2:C13))

In dem Fall haben wir natürlich wieder nur ein Ergebnis, nicht eine Matrix aus mehreren Werten


Fehler: #ÜBERLAUF! bzw. #SPILL!

Dieser Fehler erscheint, wenn Excel eine Arrayformel nicht berechnen oder deren Ergebnisse nicht darstellen kann.

Meist wird dies der Fall sein, wenn schlicht zu wenig Platz für die Ausgabe ist. Dann erscheint in der Zelle mit der Formel die Meldung und es wird mit einem Rahmen dargestellt, wie viel Platz benötigt würde. Im Weg können dabei Zellinhalte sein, aber auch das Ende der Tabelle, verbundene Zellen usw.

Auch wenn in einer Arrayfunktion Zufallszahlen oder andere Ergebnisse verwendet werden, kann der Fehler erscheinen. Dann ist die innere Funktion (zum Beispiel eine für Zufallszahlen) noch nicht fertig, während die äußere aber schon rechnen möchte. Da aber die Ergebnisse der inneren Funktion fehlen bzw. unvollständig sind, kommt diese Meldung.


Schnittmengenoperator @

Möglicherweise haben Sie schon die Meldung bekommen:

Warum ist der @-Operator hier? Wir haben ein Upgrade der Formelsprache von Excel durchgeführt. Hieraus resultiert, dass Ihnen vielleicht in manchen Formeln der @-Operator auffallen wird. Ihre Formeln verhalten sich auf dieselbe Weise wie immer.

Gleichzeitig kann es sein, dass sich in Ihren Formeln auf einmal @-Zeichen nach den Gleichheitszeichen befinden, die Sie gar nicht eingetragen haben.

Dieses Verhalten hängt unmittelbar mit den Arrayformeln bzw. Arrayfunktionen zusammen. Nehmen wir obige Formel:

=WENN(C2:C13>400;C2:C13;"")

Sie liefert, wie wir gesehen haben, alle Werte, die größer als 400 sind, in und unter der Zelle mit der Formel. Was nun aber, wenn wir nur ein Ergebnis benötigen, das erste? Dieser Fall kann auftreten, wenn Formeln über mehrere Zellen gezogen oder wenn Funktionen verschachtelt werden sollen. In diesem Fall setzen wir direkt hinter das Gleichheitszeichen das @-Zeichen:

=@WENN(C2:C13>400;C2:C13;"")

Nun haben wir nur noch ein Ergebnis; in diesem Fall das erste, die 500.

Um bei diesem Beispiel zu bleiben: Setzen wir in der Formel die letzte Zeile absolut (durch das $-Zeichen), kann die Formel nach unten gezogen werden und wir haben als Ergebnis immer den Wert aus den Zellen ab der Zeile mit der Formel:

=@WENN(C2:C$13>400;C2:C$13;"")

Arrayformeln (04): EINDEUTIG/UNIQUE (Formel + VBA)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 116) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Funktion EINDEUTIG() (in VBA) nutzen?

Ab Excel 365 gibt es neben der Möglichkeit, Duplikate zu entfernen, auch eine Funktion zum Einsatz in einer Formel: EINDEUTIG(). Die Funktion sucht in einer Tabelle nach doppelten Datensätzen und gibt in der einfachen Variante jeden nur einmal aus. Weitere Informationen zu Parametern der Formel gibt es bei Microsoft: EINDEUTIG-Funktion.

Im Beispiel ist zu sehen, dass die Monate Februar und zweimal Mai im Ergebnis nur jeweils einmal erscheinen, weil diese Monate auch jeweils die gleichen Zahlen haben. Der Juni ist jedoch zweimal im Ergebnis enthalten, weil diese Datensätze unterschiedliche Zahlen haben und somit insgesamt unterschiedlich sind.

Verwendung in VBA

Auch mit VBA kann diese Funktion doppelte Datensätze ausfiltern, indem die englische Schreibweise zum Einsatz kommt:

Application.WorksheetFunction.Unique(Array)

Im Beispiel wird die Tabelle aus der Abbildung im Bereich A2:E15 verwendet. Hier sind die Datensätze bei Frau Linz identisch und zwei Datensätze bei Frau Herzig. Aus dieser Tabelle erstellen wir den Array:

arr = Range("A2:E15")

Zum Herausfiltern der doppelten Datensätze wird die Funktion eingesetzt:

arr = Application.WorksheetFunction.Unique(arr)

Weiterverarbeitung des Ergebnisarrays

Das Ergebnis ist nun in der Variablen arr der Array mit den eindeutigen Datensätzen. Dabei gibt es jedoch zwei verschiedene mögliche Fälle:

Es können (wie im Beispiel) mehrere Zeilen sein. Dann kann der Array von 1 bis zum Ubound (der hier die Anzahl der Zeilen im Ergebnis ist) mit arr(Zeile, Spalte) durchlaufen werden:

For intI = 1 To UBound(arr) MsgBox arr(intI, 1) & " " & arr(intI, 2) & ", " & arr(intI, 3) Next

Hier würde für jede Zeile eine MsgBox mit Anrede Name, Vorname erscheinen.

Es kann aber auch der Fall eintreten, dass im Ergebnis nur eine Zeile übrig bleibt, die nun als Array vorliegt. Hier enthält der Array jedoch nicht die einzelne Zeile als Arrayelement der ersten Dimension, sondern bereits die einzelnen Elemente in der ersten Ebene. In dem Fall würde ein Zugriff mit arr(Zeile, Spalte) zu einem Fehler führen, weil das Auslesen nur mit arr(Spalte) erfolgen darf.

Wenn wir im VBA-Code also beide Fälle berücksichtigen wollen, müssen wir prüfen, ob der Array aus mehreren Zeilen zu mehreren Spalten oder nur aus mehreren Spalten ohne Zeile besteht. Dazu bietet sich an, die Anzahl aller Elemente des Arrays festzustellen:

intAnzahlEl = Application.WorksheetFunction.CountA(arr)

Wenn diese Zahl gleich dem Ubound des Arrays ist, muss es sich um einen eindimensionalen handeln, weil das dann die einzelnen Spalten sind. Wenn nicht, handelt es sich um einen mehrzeiligen Array, weil es dann immer mehr Elemente als der Ubound sind (Zeilen * Spalten = intAnzahlEl).

Und so können wir in unserem Code gut die Weiche stellen - an der Stelle der MsgBoxen müsste die eigentliche Verarbeitung der Daten rein:

Sub Eindeutig_vba() Dim arr, intI As Integer, intAnzahlEl As Integer arr = Range("A2:E15") 'Zur Ausgabe von mehreren Zeilen 'arr = Range("A2:E2") 'Zur Testausgabe einer Zeile arr = Application.WorksheetFunction.Unique(arr) 'Anzahl aller(!) Elemente im Array: intAnzahlEl = Application.WorksheetFunction.CountA(arr) MsgBox "Ubound: " & UBound(arr) & vbNewLine & "Anzahl: " & intAnzahlEl If intAnzahlEl = UBound(arr) Then ' Es gibt nur eine Zeile MsgBox arr(1) & " " & arr(2) & ", " & arr(3) Else ' Mehrere Zeilen For intI = 1 To UBound(arr) MsgBox arr(intI, 1) & " " & arr(intI, 2) & ", " & arr(intI, 3) Next End If End Sub

Tipp - Sortieren:

Wenn das Ganze sortiert werden soll, kann das auch gleich am Anfang mit der integrierten Funktion erledigt werden:

arr = Application.WorksheetFunction.Unique(arr) arr = Application.WorksheetFunction.Sort(arr, 2)

In dem Beispiel wäre der frische Array nach den Namen sortiert.

Aus Blättern einzelne Dateien erstellen

Kategorie: Add-In ▸ Dateien und Ordner

(Tipp 578) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus Tabellenblättern einzelne Dateien erstellen?

Das Add-In fragt zunächst nach dem Ordner, in den die einzelnen Dateien gespeichert werden sollen. Dann werden die einzelnen Blätter unter deren Namen mit der Endung xlsx (kann im Code geändert werden) gespeichert, wobei geprüft wird, ob eine Datei mit diesem Namen im Ordner bereits existiert. Zum Anschluss kommt eine Erfolgsmeldung oder die Information, welche Blätter nicht gespeichert werden konnten.

Der Aufruf erfolgt im Menüband im Ribbon JL-Dateien erstellen.

Download: blaetter_speichern.xlam

Bedingte Formatierung: Drei Preise - günstigsten farbig kennzeichnenFormellösungArrayfunktion/MatrixfunktionTipp

Kategorien: Format ▸ Bedingt und Tabelle ▸ Matrix

(Tipp 326) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die günstigsten Preise hervorheben?

In einer Tabelle stehen in einer Spalte (hier: D) untereinander verschiedene Produktnamen. Bei jedem Produkt stehen rechts daneben (hier: E, F und G) die Preise für drei verschiedene Länder.

Die Preise bei jedem Produkt sollen automatisch farbig markiert werden: der günstigste grün, der höchste rot und ansonsten gelb.

Lösung


Die Aufgabe wird mit Bedingte Formatierung im Ribbon Start erfüllt.

Dazu müssen für jede der drei Preisspalten jeweils drei Regeln erstellt werden - eben eine für den günstigsten, eine für den höchsten und eine für den restlichen Preis. Wir fangen an mit der ersten Spalte, in der Preise enthalten sind, hier also E2:E19 und markieren diese. Anschließend rufen wir die bedingte Formatierung auf und wählen dort Neue Regel.

In der Auswahlliste wählen wir den Punkt Nur Zellen formatieren, die enthalten. Dies ist die Basis für die weiteren Formatierungen.

Grüne Formatierung

Die grüne Formatierung soll erscheinen, wenn der Preis am niedrigsten ist. Hier bietet sich also die Funktion MIN zum Vergleich mit den anderen beiden Preisen an. Wir stellen also beim Zellwert auf kleiner als und tragen rechts ein: =MIN(F2:G2). Mit dem Button Formatieren legen wir die grüne Hintergrundfarbe fest.

Mit OK übernehmen wir die Regel.

Rote Formatierung

Mit rotem Hintergrund soll gekennzeichnet werden, wenn der Preis am höchsten ist. Dazu ist die Funktion MAX sinnvoll.

Wir bleiben also im Dialog und wählen Neue Regel. Anschließend führen wir die gleichen Schritte wie bei der grünen Formatierung durch, nur eben mit größer als, MAX und der roten Formatierung.

Gelbe Formatierung

Einen gelben Hintergrund soll die Zelle bekommen, wenn der Preis nicht am höchsten und nicht am niedrigsten ist, wenn er also zwischen den beiden anderen Preisen liegt.

Wir bleiben weiterhin im Dialog und wählen wieder Neue Regel. Wenn wir nun wieder Nur Zellen formatieren, die enthalten anklicken, müsste beim Zellwert schon zwischen ausgewählt sein - wenn nicht, dies nachholen.

In die Felder schreiben wir jeweils =F2 und =G2. Gelben Hintergrund auswählen und bestätigen. Nun müssten die drei Regeln im Manager angezeigt werden.

Wenn nun Zahlen eingetragen werden, sollte das beim ersten Land funktionieren, die Hintergründe müssten automatisch entsprechend der Zahlen formatiert werden. Allerdings müssen die Schritte noch für die anderen beiden Länder wiederholt werden. Beim mittleren Land aufpassen; die Zellen in MIN und MAX müssen hier mit Semikolon getrennt werden, weil es keine Bis-Bereiche sind, sondern auseinanderliegende Zellen.


Auswertung: Matrixformel

Unter der Tabelle sollen nun noch zu jedem Land die grünen, roten und gelben Zellen gezählt werden. Das Problem: Mit einer reinen Formellösung können keine farbigen Zellen gezählt werden.

Ein Lösungsansatz ist, zu zählen, wie viele Zellen in der jeweiligen Spalte größer bzw. kleiner als die Zellen daneben sind. Damit wir nicht jede Zeile einzeln berücksichtigen müssen, verwenden wir dazu eine Matrixformel, die den Bereich einer Spalte über alle Zeilen hinweg erfasst.

Hinweis: Die geschweiften Klammern nicht eingeben, sondern die Eingabe der Formel mit Strg + Umschalt + Enter abschließen. Damit erscheinen die geschweiften Klammern automatisch. Ab Excel 365 sind die geschweiften Klammern nicht mehr notwendig.

Wir beginnen beim ersten Land, hier mit der Formel in E24. Es sollen die Zellen gezählt werden, die in den Zeilen die niedrigsten Preise haben. Dabei zählen wir aber nicht, sondern wir addieren für jede dieser niedrigsten Zellen die 1. Wir bilden also die Summe, hier das Grundgerüst:

=SUMME( wenn Zahl in der Zeile am niedrigsten ; dann addiere 1; sonst addiere 0))

Wir verwenden hier diese Logik (andere Varianten gibt es natürlich auch):

Wenn die Zahl beim Land 1 (E) kleiner als die Zahl beim Land 2 (F) ist, dann wenn die Zahl beim Land 1 (E) auch kleiner als die beim Land 3 (G) ist, dann addiere 1, sonst 0, sonst 0.

Wir addieren hier also zweimal 0 - einmal für die erste Bedingung (Land 1 nicht kleiner als Land 2) und einmal für die zweite Bedingung (Land 1 nicht kleiner als Land 3).

Diese Struktur bauen wir in die Formel ein, so dass die (an Strg + Umschalt + Enter denken!) nun so aussieht:

{=SUMME(WENN(E2:E19<F2:F19;WENN(E2:E19<G2:G19;1;0);0))}

Damit haben wir die Anzahl der grünen Zellen beim Land 1 in E24. Die gleiche Formel kommt zu den anderen Ländern. Vorsicht, die kann aber nicht gezogen werden, weil die Zellen in den Formeln angepasst werden müssen.

Ebenfalls die gleiche Formel, nur mit dem größer als >, kommt in die Zeile 26, wo die roten Zellen gezählt werden:

{=SUMME(WENN(E2:E19>F2:F19;WENN(E2:E19>G2:G19;1;0)))}

Bei den gelben Zellen geht es einfacher - einfach alle Zellen in der Spalte zählen und die grünen und roten subtrahieren:

=ANZAHL(E2:E19)-E24-E26

Eine Beispieldatei mit dieser Lösung: 326_preisvergleiche.xlsx

Besonderheit bei den EigenschaftenTipp

Kategorie: Basics ▸ OOP

(Tipp 218) Nachricht zum Beitrag an Autor Nach oben

Der Punkt spielt bei Objekten immer eine zentrale Rolle. Bei den Eigenschaften kommt noch das Gleichheitszeichen hinzu.

Man nennt das Objekt, danach hinter einem Punkt die Eigenschaft und dann nach einem Gleichheitszeichen, wie die Eigenschaft sein soll.

Beispiel:

Auto.Farbe = Rot

Dies gilt in VBA sowohl für Abfragen als auch für Zuweisungen. Man kann also mit dieser Syntax abfragen, ob ein Auto rot ist; man kann aber auch dem Objekt Auto die Farbe Rot zuweisen. Wenn man von anderen Sprachen, wie zum Beispiel JavaScript oder PHP kommt, muss man dies besonders beachten.



Bildschirmaktualisierung aus- und einschaltenMakro/Sub/ProzedurTipp

Kategorien: Programmiertechnik ▸ Darstellung und Tabelle ▸ Selection

(Tipp 109) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich erreichen, daß ein Makro nicht alle einzelnen Schritte anzeigt?

Dazu kann die Bildschirmaktualisierung (das ScreenUpdating) ausgeschaltet werden:

Application.ScreenUpdating = False

Bildschirmaktualisierung einschalten:

Application.ScreenUpdating = True

Das Ausschalten der Bildschirmaktualisierung hat auch immer ein paar Risiken, denn das Excelfenster ist ja dann „eingefroren“. Kommt es zu einem Fehler durch den Code, bleibt das Fenster auch eingefroren - der Anwender sieht dann schlicht keine Veränderungen mehr. Deshalb ist empfehlenswert, durch objektorientiertes Arbeiten (also Elemente direkt ansprechen, Verzicht auf .Select und .Activate) dafür zu sorgen, dass der Bildschirm nicht zappelt.

Soll die Aktualisierung trotzdem ausgeschaltet werden (weil vielleicht die Laufzeit kürzer wird), sollten Fehler abgefangen und per Sprungmarke am Ende des Codes die Bildschirmaktualisierung wieder eingeschaltet werden. Im einfachsten Fall wäre das so möglich:

On Error GoTo FEHLER … Code … FEHLER: Application.ScreenUpdating = True

Günstig ist auch, am Anfang den Status der Eigenschaft abzufragen und sie am Ende wieder so zu setzen, wie sie am Anfang war:

Sub MeinMakro() Dim bolAktScrUpd As Boolean bolAktScrUpd = Application.ScreenUpdating … Code … Application.ScreenUpdating = bolAktScrUpd 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:

Dateiname aus Pfad (Dir(), Regulärer Ausdruck, Arrayformel)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/Matrixfunktion

Kategorien: Dateien und Ordner ▸ Dateien und Stringoperationen ▸ Teile

(Tipp 24) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus einem Pfad (z. B. bei GetOpenFileName) den Dateinamen (bzw. Ordner) filtern?

Natürlich kann man den gesamten Pfad am Backslash splitten oder andere Stringoperationen anwenden. Am einfachsten ist es aber sicher, wenn man sich mit Dir() den Dateinamen zurückgeben lässt - das geht auch mit allen Dateien, nicht nur mit Exceldateien.

Dir()

Rein für den Dateinamen wäre dies eine einfache Möglichkeit:

Sub DateinamenExtrahieren() Dim varDName varDName = Application.GetOpenFilename If varDName = False Then MsgBox "Nichts gewählt." Else varDName = Dir(varDName) MsgBox varDName End If End Sub

Für alle Angaben aus dem Pfad, also Ordner und Datei, könnte folgende Variante genutzt werden:

Sub DateiPfad() Dim strGesamt As String, strDatei As String, strOrdner As String strGesamt = Application.GetOpenFilename strDatei = Dir(strGesamt) strOrdner = Left(strGesamt, Len(strGesamt) - Len(Dir(strGesamt))) MsgBox strDatei & vbNewLine & strOrdner & vbNewLine & strGesamt End Sub

Wenn davon ausgegangen werden kann, dass die Zeichenfolge des Dateinamens einmalig im Pfad ist, kann auch einfach ersetzt werden:

Sub DateiAusPfad2() Dim varPfad, strOrdner As String, strDatei As String varPfad = Application.GetOpenFilename If varPfad <> False Then strDatei = Dir(varPfad) strOrdner = Replace(varPfad, strDatei, "") MsgBox strDatei & vbNewLine & strOrdner End If End Sub


Regulärer Ausdruck

Noch eine Variante für die Freunde regulärer Ausdrücke:

Sub DateiAusPfad3() Dim varDName, Regex As Object, regMatches, regMatch varDName = Application.GetOpenFilename If varDName = False Then MsgBox "Nichts gewählt." Else If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "^(.+[\\\/])(.*)$" Set regMatches = Regex.Execute(varDName) MsgBox regMatches(0).SubMatches(1) Set Regex = Nothing End If End Sub

Der Schrägstrich wurde aufgenommen, weil Pfade in Onedrive gespeicherter Dateien mit Schrägstrich geliefert werden.

Sollen Ordner und Dateiname zurückgegeben werden, wäre dies möglich:

Sub DateiAusPfad4() Dim varPfad, strOrdner As String, strDatei As String Dim Regex As Object, regMatches, regMatch varPfad = Application.GetOpenFilename If varPfad <> False Then If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "^(.+[\\\/])(.*)$" Set regMatches = Regex.Execute(varPfad) strOrdner = regMatches(0).SubMatches(0) strDatei = regMatches(0).SubMatches(1) Set Regex = Nothing MsgBox strDatei & vbNewLine & vbNewLine & strOrdner End If End Sub

Die integrierte Funktion =ZELLE("Dateiname";A1) liefert den kompletten Pfad bis zum Tabellenblatt. Der Dateiname ist dabei in eckige Klammern eingeschlossen: Pfad[Dateiname]Blattname. Mit einem regulären Ausdruck können die einzelnen Bestandteile ausgegeben werden (ggf. noch Fehlerbehandlung einbauen):

Sub DateiAusZellFunktion() Dim strPfad, strOrdner As String, strDatei As String, strBlatt As String Dim Regex As Object, regMatches, regMatch strPfad = Evaluate("=cell(""filename"",A1)") If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "^(.*)\[(.*)\](.*)$" Set regMatches = Regex.Execute(strPfad) strOrdner = regMatches(0).SubMatches(0) strDatei = regMatches(0).SubMatches(1) strBlatt = regMatches(0).SubMatches(2) Set Regex = Nothing MsgBox strBlatt & vbNewLine & strDatei & vbNewLine & strOrdner End Sub


Dynamische Matrixformel (Arrayformel) und verschütteter Array

Per benutzerdefinierter Funktion können seit Excel 365 auch die einzelnen Ordner bzw. Bestandteile eines Pfades in Zellen ausgegeben werden. Dazu diese Funktion als Beispiel:

Function PfadDetails(ByVal strPfad As String) PfadDetails = "" If strPfad <> "" Then Select Case True Case InStr(1, strPfad, "\") > 0: PfadDetails = Split(Replace(strPfad, "\\", "\"), "\") Case InStr(1, strPfad, "/") > 0: PfadDetails = Split(Replace(strPfad, "//", "/"), "/") End Select End If End Function

In die Zelle kommt dann diese Formel:

=PfadDetails(A5)

Wenn wie hier im Beispiel in A5 ein Pfad steht, werden an der Zelle mit der Formel die einzelnen Elemente des Pfades ausgegeben. Das letzte Element sollte bei einem kompletten Pfad zu einer Datei der Dateiname sein.

Das Beispiel mit der Funktion =ZELLE("Dateiname";A1) kann auch als Arrayformel verwendet werden:

Function DateiAusZellFunktion(strFktPfad) Dim arrTemp(1 To 3) Dim Regex As Object, regMatches, regMatch DateiAusZellFunktion = "" If strFktPfad <> "" Then If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "^(.*)\[(.*)\](.*)$" Set regMatches = Regex.Execute(strFktPfad) If regMatches.Count = 1 Then arrTemp(1) = regMatches(0).SubMatches(2) arrTemp(2) = regMatches(0).SubMatches(1) arrTemp(3) = regMatches(0).SubMatches(0) DateiAusZellFunktion = arrTemp End If Set Regex = Nothing End If End Function

In die Zelle kommt dann =DateiAusZellFunktion(ZELLE("Dateiname";A1)) und in ihr sowie den Nachbarzellen werden Blattname, Dateiname und Ordnerpfad erscheinen.


Formeln/integrierte Funktionen

Den aktuellen Ordner gibt diese Funktion zurück:

=INFO("Verzeichnis")


In anderen Sprachen geht übrigens auch einfach Basename(Pfad).

Datentypen - Deklaration (Beispiele: Excel)Makro/Sub/ProzedurTipp

Kategorie: Basics ▸ Variablen

(Tipp 211) Nachricht zum Beitrag an Autor Nach oben

Variablennamen müssen mit einem Zeichen des Alphabets beginnen, innerhalb des Gültigkeitsbereichs eindeutig sein, und dürfen nicht länger als 255 Zeichen lang sein.

Jede Variable beansprucht Speicherplatz, was zur Verlängerung der Laufzeit eines Makros (einer Prozedur) führt. Damit sich dies in Grenzen hält, kann man einer Variablen zuweisen, wieviel Speicherplatz sie in Anspruch nimmt, indem man der Variablen einen Datentyp zuweist.

Sie können u. a. als einer der folgenden Datentypen deklariert werden:

  • Boolean
  • Byte
  • Integer
  • LongPtr
  • String
  • Range

Wird kein Datentyp angegeben, so ist der Datentyp Variant standardmäßig zugewiesen.

Variablen werden gewöhnlich mit der DIM-Anweisung deklariert.


Long für 32- und 64-Bit

Statt Long sehen Sie hier LongPtr. Der Grund ist, dass es bei Verwendung von Code mit Long-Variablen in der 64-Bit-Version des Microsoft Office (standardmäßig wird die 32-Bit-Version installiert) zu Problemen kommen kann: vba-tutorial.de: Datentypen.

LongPtr ist also, um den Kern der Aussagen im verlinkten Text zusammenzufassen, eine Art Weiche, die bei 32-Bit-Versionen auf Long und bei 64-Bit-Versionen auf LongLong „umschaltet“.

Kennzeichnend für das Problem ist zum Beispiel diese Fehlermeldung:

Der Code in diesem Projekt muss für die Verwendung auf 64-Bit-Systemen aktualisiert werden. Überarbeiten und aktualisieren Sie Declare-Anweisungen, und markieren Sie sie mit dem PtrSafe-Attribut.

Deshalb die Empfehlung: Immer davon ausgehen, dass der Code in beiden Versionen laufen soll und deshalb LongPtr verwenden sowie API-Deklarationen am Anfang des Moduls immer mit einer solchen „Weiche“ vorzunehmen:

#If VBA7 Then Private Declare PtrSafe Function … (ByRef … As LongPtr, ByVal … As LongPtr) As LongPtr #Else Private Declare Function … (ByRef … As Long, ByVal … As Long) As Long #End If

Sie sehen hier einmal das PtrSafe und dass jeder Long-Typ in der aktuellen Version als LongPtr deklariert wurde.

Weitere Beispele:


Boolean

Datentypen Boolean werden als 16-Bit-Zahlen (2 Bytes) gespeichert, die nur die Werte True oder False annehmen können.

Bsp:

Sub PruefeZeileOK() Dim bolPositionOK As Boolean If Selection.Row < 10 Then bolPositionOK = False Else bolPositionOK = True If bolPositionOK = False Then MsgBox ("Aktion an dieser Position nicht zulässig!") End Sub

Die folgende Funktion bekommt aus Prozeduren die Zeilenposition übergeben und prüft, ob die Aktion zulässig ist. Rückgabewerte: TRUE oder FALSE

Function PositionOK(lngZeilenposition As Long) As Boolean PositionOK = True If lngZeilenposition < 10 Then PositionOK = False End Function

Die Verwendung in einer Sub könnte dann so aussehen:

Sub Aufruf() Dim lngZeile As LongPtr lngZeile = ActiveCell.Row If PositionOK(lngZeile) = False Then MsgBox "Geht hier nicht, Zeile " & lngZeile & " zu niedrig.", vbOKOnly + vbExclamation, "Fehler" Exit Sub End If End Sub

Byte

Byte werden als einzelne 8-Bit-Zahlen (1 Byte) ohne Vorzeichen gespeichert und haben einen Wert im Bereich von 0 bis 255.

Integer

Integer werden als 16-Bit-Zahlen (2 Bytes) in einem Bereich von -32.768 bis 32.767 gespeichert.

String

Datentyp String kann Buchstaben, Zahlen, Leerzeichen und Satzzeichen enthalten.

Sub Meldung() Dim strText As String Dim strTitel As String strText = "Hallo 12345" strTitel = "*******Titel ******" MsgBox strText, , strTitel End Sub

Range

Datentyp Range gibt eine Zelle oder einen Zellbereich aus.

Sub ZeilenMarkieren() Dim rngBereich As Range Set rngBereich = Sheets("Tabelle1").Range("A1:C5") If rngBereich.Interior.ColorIndex = 3 Then rngBereich.Interior.ColorIndex = 5 Else rngBereich.Interior.ColorIndex = 3 End Sub

Formel als Ergebnis einer Formel (FORMELTEXT()/UDF)UDF - benutzerdefinierte FunktionFormellösung

Kategorie: Tabelle ▸ Formeln

(Tipp 418) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich per Formel eine Formel aus einer anderen Zelle anzeigen lassen?

Ab Excel 365 kann einfach die Funktion verwendet werden:

=FORMELTEXT(K3)

Die Formel mit dieser Funktion gibt die Formel zurück, die in K3 steht.


Für ältere Versionen kann die benutzerdefinierte Funktion verwendet werden:

Prüfen, ob in der Zelle eine Formel vorliegt und anschließend die Formel (ohne Gleichheitszeichen) mit dem Ergebnis ausgeben lassen:

Function Bezug(Zelle) If Zelle.HasFormula Then Bezug = Right(Zelle.Formula, Len(Zelle.Formula) - 1) & " = " & Zelle Else Bezug = "" End Function

In die Zelle kommt dann einfach die Formel =Bezug(D7), wobei hier in D7 die eigentliche Formel steht.

Zelle:B7C7D7E7
enthält:1015=B7+C7*2=Bezug(D7)
Ergebnis:  40B7+C7*2 = 40

Noch eine benutzerdefinierte Funktion dazu, die einfach das Gleichheitszeichen ersetzt (also löscht):

Function Bezug1(Zelle) If Zelle.HasFormula Then Bezug = Replace(Zelle.FormulaLocal, "=", "") Else Bezug = "" End If End Function

In die Zelle kann dann z. B. eingegeben werden: =Bezug1(A1)


Oder für die Freunde gepflegter regulärer Ausdrücke (Microsoft VBScript Regular Expressions-Objektbibliothek muss eingebunden sein!):

Function Bezug2(Zelle) Dim Regex As New RegExp, regMatches As MatchCollection, regMatch As Match Bezug1 = 0 Regex.Pattern = "^(=)(.*)$" Set regMatches = Regex.Execute(Zelle.FormulaLocal) If regMatches.Count > 0 Then Bezug1 = regMatches(0).SubMatches(1) End Function

Letzten Wert in einem Bereich ermittelnUDF - benutzerdefinierte Funktion

Kategorie: Tabelle ▸ Zellen

(Tipp 431) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich den letzten Wert in einem Bereich ermitteln?

Folgenden Code in ein Standardmodul kopieren:

Function LetzterWert(Bereich As Range) As Variant Dim Zelle As Range Application.Volatile For Each Zelle In Bereich If Zelle <> "" Then LetzterWert = Zelle Next End Function

Um den letzten Wert im Bereich A1:J2 zu suchen kommt dann in die Zelle:

=Letzterwert(A1:J2)

Mit negativen Zeiten rechnenFormellösung

Kategorie: Datum/Zeit ▸ Zeit

(Tipp 192) Nachricht zum Beitrag an Autor Nach oben

Ich benötige für unsere Zeiterfassung die Möglichkeit, auch negative Zeiten (in Stunden und Minuten) berechnen zu können. Beispiel: A1 = 100:00 A2 = 120:00 A3 = (A1-A2) Daraufhin werden nur ####... angezeigt.

Das Vorgehen ist hier immer etwas von den konkreten Gegebenheiten abhängig, denn die Rauten (####) werden nur angezeigt. Dahinter stecken trotzdem die Ergebnisse, man kann also mit diesen Rauten also sogar weiterrechnen. Zum Testen:

  • In A8 14:00 eintragen.
  • In B8 13:00 eintragen.
  • In C8 die Formel =B8-A8 eintragen. Da die Formel ein negatives Ergebnis ergibt, erscheinen die Rauten.
  • In C9 die Formel =C8*-1 eintragen. Es erscheint das korrekte Ergebnis, nur als positive Zahl.

Dieses Prinzip kann also verwendet werden, so dass mit der WENN-Funktion auf ein negatives Ergebnis geprüft wird und eine entsprechende Ausgabe erfolgt.

Alternativ kann die Prüfung bereits bei der Berechnung erfolgen:

=WENN(A8<B8;(A8-B8)*-1;A8-B8)

Nur ist hier der Nachteil, dass immer ein positives Ergebnis angezeigt wird. Ggf. könnte man per bedingter Formatierung darauf aufmerksam machen, aber die positive Zahl bleibt.

Kommt es auf weitere Berechnungen nicht an, sondern nur auf diese Anzeige, kann das Minuszeichen hinzugefügt werden:

=WENN(A8<B8;"-"&TEXT((A8-B8)*-1;"[hh]:mm");A8-B8)

Das negative Ergebnis liegt dann jedoch als Text vor - bei Berechnungen kann es nun Fehlermeldungen geben.

Wie vorgegangen wird, muss also im Einzelfall entschieden werden.

Auf das 1904-Datumsformat wird hier nicht eingegangen, weil das Probleme bereiten kann.



Modul als Textdatei speichernMakro/Sub/Prozedur

Kategorien: VBE und Dateien und Ordner ▸ Dateien

(Tipp 31) Nachricht zum Beitrag an Autor Nach oben

Wie kann man ein Modul als Textdatei speichern?

Der folgende Code speichert den gesamten Text des in der Variablen eingetragenen VBA-Moduls in eine Textdatei:

Sub Modulspeichern() Dim varZiel, strKomponente As String, intI As Integer, objX As Object Dim lngDNr As LongPtr strKomponente = "Modul1" varZiel = Application.GetSaveAsFilename("test", "Textdateien (*.txt), *.txt") If varZiel = False Then Exit Sub lngDNr = FreeFile Open varZiel For Output As #lngDNr Set objX = ThisWorkbook.VBProject.VBComponents(strKomponente).CodeModule With objX For intI = 1 To .countofLines Print #lngDNr, .Lines(intI, 1) Next End With Close #lngDNr End Sub

Natürlich muss das nicht in eine Textdatei gespeichert werden; statt Print #lngDNr, .Lines(intI, 1) kann die Ausgabe auch woanders erfolgen oder in einen Array eingetragen werden.

Bitte den Hinweis auf der Startseite beachten, wenn die Meldung Der programmatische Zugriff auf das Visual-Basic-Projekt ist nicht sicher. kommt:

Pause nur berechnen, wenn anwesendUDF - benutzerdefinierte Funktion

Kategorie: Datum/Zeit ▸ Zeit

(Tipp 170) Nachricht zum Beitrag an Autor Nach oben

Pausenregelungen von 9:00 - 9:15 Uhr und 13:00 - 13:30 Uhr. Pausen dürfen nur von der Arbeitszeit abgezogen werden wenn derjenige anwesend ist. Das Pause-Feld muss sich also nach den Komm- u. Geht-Feldern richten.

Das sind verschiedene Konstellationen, die ausgewertet werden müssen. Dazu gibt es natürlich verschiedene Möglichkeiten, hier wird mal der Einsatz von Select Case demonstriert.

Das Problem ist, dass es hier keine Variable gibt, deren Wert ausgewertet werden kann. Es sind immer Bedingungen, die in Kombination zutreffen oder eben nicht. Das heißt, statt der Variablen wird True im Select verwendet:

Function Pausenzeit(kommt, geht, P1Beginn, P1Ende, P2Beginn, P2Ende) Dim datErsteZeit As Date, datZweiteZeit As Date Select Case True Case geht < P1Beginn Or kommt > P2Ende Pausenzeit = 0: Exit Function 'geht vor erster Pause oder kommt nach zweiter Pause Case kommt >= P1Beginn And kommt <= P1Ende And geht > P1Ende datErsteZeit = P1Ende - kommt 'kommt in erster Pause, geht nach erster Pause Case kommt < P1Beginn And geht > P1Ende datErsteZeit = P1Ende - P1Beginn 'kommt vor erster Pause, geht nach erster Pause Case kommt >= P2Beginn And kommt <= P2Ende And geht > P2Ende datErsteZeit = 0: datZweiteZeit = P2Ende - kommt 'kommt in zweiter Pause, geht nach zweiter Pause Case kommt < P1Beginn And geht < P1Ende datErsteZeit = geht - P1Beginn: datZweiteZeit = 0 'kommt vor erster Pause, geht in erster Pause Case kommt >= P1Beginn And geht <= P1Ende datErsteZeit = geht - kommt: datZweiteZeit = 0 'kommt und geht in erster Pause End Select Select Case True Case geht < P2Beginn: datZweiteZeit = 0 'geht vor zweiter Pause Case geht >= P2Beginn And geht < P2Ende: datZweiteZeit = geht - P2Beginn 'geht in zweiter Pause Case Else: datZweiteZeit = P2Ende - P2Beginn End Select Pausenzeit = datErsteZeit + datZweiteZeit End Function

In die Zelle muss dann nur noch:

=Pausenzeit(A4;B4;B1;C1;D1;E1)

Rechnen ohne Gleichheitszeichen (Worksheet_Change)Makro/Sub/ProzedurUDF - benutzerdefinierte Funktion

Kategorien: Ereignisse ▸ Tabellen und Tabelle ▸ Formeln

(Tipp 417) Nachricht zum Beitrag an Autor Nach oben

In Spalte A werden Berechnungen ohne Gleichheitszeichen eigetragen. Wie erhalte ich in B die Ergebnisse?

Worksheet_Change-Ereignis

Die Routine wird im VBA-Editor in das Modul eingetragen, das durch Doppelklick auf die Tabelle, in der der Code wirken soll, geöffnet wird. Es werden hier zwei Varianten aufgezeigt: In Spalte B wird eine Formel eingetragen, die das Ergebnis liefert. Falls ein Ergebnis ohne Formel gewünscht wird, wird dies noch in Spalte C eingetragen.

Die Routine wird nur ausgeführt, wenn die Eingabezelle in Spalte 1 (A) ist. Dann werden zunächst die Zielzellen daneben in B und C geleert.

Da intern mit Punkt statt Komma als Dezimaltrenner gerechnet wird, wird ein eventuell vorhandenes Komma zuerst ersetzt. Anschließend wird mit Evaluate versucht, zu berechnen. Wird die Berechnung erkannt, wird ein Ergebnis geliefert, sonst der Fehler #NAME?. Letzteres kommt zum Beispiel vor, wenn ein Text in A eingetragen wurde.

Tritt kein Fehler auf, wird in B die entsprechende Formel eingetragen, in C direkt das Ergebnis.

Private Sub Worksheet_Change(ByVal Target As Range) Dim varTemp, varErg If Target.Column > 1 Then Exit Sub Range("B" & Target.Row & ":C" & Target.Row).ClearContents varTemp = Replace(Target, ",", ".") varErg = Application.Evaluate(varTemp) If Not IsError(varErg) Then Cells(Target.Row, 2).Formula = "" & "=" & varTemp & "" Cells(Target.Row, 3) = varErg End If End Sub


UDF - benutzerdefinierte Funktion

Es ist (in diesem Fall ab Excel 365) auch möglich, das Ergebnis der Berechnung ohne Gleichheitszeichen per Formel zu erhalten. Notwendig ist dazu eine solche benutzerdefinierte Funktion in einem Standardmodul:

Function Evaluate_String(ByVal strString As String, Optional intWas As Integer = 0) Dim varTemp, varErg Evaluate_String = "" varTemp = Replace(strString, ",", ".") varErg = Application.Evaluate(varTemp) If Not IsError(varErg) Then Evaluate_String = IIf(intWas <> 0, "=" & varTemp & "", varErg) End Function

In die Zelle, in der das Ergebnis der Formel ohne Gleichheitszeichen erscheinen soll, muss dann nur:

=Evaluate_String(C10)

Wenn die Formel nicht das Ergebnis, sondern die Formel (also mit Gleichheitszeichen) anzeigen soll, kann als zweiter Parameter etwas anderes als 0 verwendet werden, zum Beispiel:

=Evaluate_String(C10;1)

Sie hat dann ein vergleichbares Verhalten wie die integrierte Funktion FORMELTEXT().

Tabellenblatt auf mehrere Tabellenblätter aufteilen (EINDEUTIG(), FILTER())Makro/Sub/ProzedurArrayfunktion/Matrixfunktion

Kategorien: Mappe ▸ Tabellen und Tabelle ▸ Matrix

(Tipp 551) Nachricht zum Beitrag an Autor Nach oben

Auf einem Tabellenblatt befinden sich in Spalte C die Namen der Mitarbeiter, daneben ihre Daten. Für jeden Mitarbeiter kann es mehrere Zeilen geben. Wie kann ich für jeden Mitarbeiter ein neues Blatt per VBA erstellen, auf dem seine Daten untereinander aufgelistet sind?

Einsatz dynamischer Arrayfunktionen

Ein Lösungsansatz, der allerdings erst ab Excel 365 funktioniert, ist der Einsatz dynamischer Arrayfunktionen. Dadurch können die Daten vorgefiltert werden und per Schleife müssen nur noch die jeweiligen Ergebnismengen verarbeitet werden; eine Schleife über alle Zeilen ist nicht notwendig.

Gegeben ist die Tabelle mit den Namen in Spalte C ab Zeile 2 und Daten bis zur Spalte G. Dies müsste ggf. angepasst werden. Die letzte Zeile der Tabelle wird aufgrund der Daten in C automatisch erkannt.

Zuerst kommt die Tabellenblattfunktion EINDEUTIG() (UNIQUE()) zum Zug. Sie enthält jeden Namen aus Spalte C genau einmal. Dies ist wichtig, da für jeden Mitarbeiter ja nur ein Blatt erstellt werden soll. Über das Ergebnis dieser Formel kann dann die Hauptschleife laufen: For Each varName In varNamen

In der Schleife wird dann für jeden Namen wieder eine Arrayfunktion verwendet: FILTER(). Diese liefert einen Array, der bei mehreren Zeilen zum Mitarbeiter aus diesen Zeilen mit den Zellen besteht oder bei nur einer Zeile aus den einzelnen Zellen. Damit hier kein Fehler auftritt, wird mit If UBound(varFilt) = Application.WorksheetFunction.CountA(varFilt) Then geprüft, wie viele Elemente der Array hat. Ist die Größe des Arrays gleich der Anzahl der einzelnen Elemente, handelt es sich um eine Zeile; die Elemente sind die Zellen. Ist die Größe des Arrays kleiner als die Anzahl der einzelnen Elemente, handelt es sich um mehrere Zeilen.

Beispiel: Bei zwei Zeilen ist der Ubound = 2. Jede Zeile hat vier Zellen, also sind das 2 Zeilen * 4 Zellen = 8 Elemente. Ubound ist kleiner als die Anzahl der Elemente. Bei nur einer Zeile hat der Array vier Elemente (die Zellen eben). Da der Array hier nicht nach Zeilen untergliedert ist, sondern die Zellen in der ersten Ebene liegen, ist hier Ubound auch = 4.

Diese Arrayelemente werden dann nur noch auf das hinzugefügte Blatt eingelesen.

Der Code:

Sub Aufteilen_Filter() Dim lngZ As LongPtr, lngLZ As LongPtr, intZ As Integer Dim strAktBlatt As String, strFormel As String Dim wksNeu As Worksheet Dim varFilt, varNamen, varName strAktBlatt = "Mitarbeiter" lngLZ = Cells(Rows.Count, 3).End(xlUp).Row varNamen = Application.WorksheetFunction.Unique(Range("C2:C" & lngLZ)) If IsArray(varNamen) Then For Each varName In varNamen strFormel = "=FILTER(" & strAktBlatt & "!C2:G" & lngLZ & "," & strAktBlatt & "!C2:C" & lngLZ & "=""" & varName & """)" varFilt = Application.Evaluate(strFormel) If IsArray(varFilt) Then Set wksNeu = Worksheets.Add(after:=Sheets(Sheets.Count)) wksNeu.Name = varName Sheets(strAktBlatt).Range("C1:G1").Copy wksNeu.Range("C1") lngZ = 1 If UBound(varFilt) = Application.WorksheetFunction.CountA(varFilt) Then lngZ = lngZ + 1 For intS = 1 To UBound(varFilt) wksNeu.Cells(lngZ, intS + 2) = varFilt(intS) '+2 weil Spalte C Next Else For intZ = 1 To UBound(varFilt) lngZ = lngZ + 1 For intS = 1 To 5 wksNeu.Cells(lngZ, intS + 2) = varFilt(intZ, intS) Next Next End If End If Next End If End Sub


Schleife

Vor Excel 365 funktioniert die Variante mit den dynamischen Arrayfunktionen noch nicht. Deshalb hier noch eine ältere Möglichkeit:

Die Routine duchläuft die Spalte der Mitarbeiternamen von oben nach unten. Mit Hilfe der ZÄHLENWENN-Funktion wird geprüft, ob sich unterhalb der gerade durchlaufenen Zelle der Name des Mitarbeiters nochmals befindet. Wenn nicht, wird ein neues Blatt mit dem Namen des Mitarbeiters angelegt und es werden die Spaltenüberschriften eingefügt.

Zum Schluss werden die Daten zu den Mitarbeitern auf deren Blättern eingetragen.

Sub Aufteilen_Schleife() Dim lngZ As Long, lngLZ As Long, intAnzahl As Integer Dim lngAktZeile As Long Dim strAktBlatt As String, strName As String Dim ints, intAnzahlTB, intAnzahlSpalten As Integer Dim objNeuBlatt As Worksheet Dim lngErsteZeile As Long Dim strSpalte As String 'Hier anpassen: lngErsteZeile = 2 strSpalte = "C" strAktBlatt = ActiveSheet.Name lngLZ = Range(strSpalte & 65536).End(xlUp).Row 'Blätter mit den Spaltenüberschriften erstellen: For lngZ = lngErsteZeile To lngLZ If Sheets(strAktBlatt).Range(strSpalte & lngZ) <> "" Then intAnzahl = Application.WorksheetFunction.CountIf(Sheets(strAktBlatt).Range(strSpalte & lngZ + 1 & ":" & strSpalte & "65536"), Sheets(strAktBlatt).Range(strSpalte & lngZ)) If intAnzahl = 0 Then Set objNeuBlatt = Worksheets.Add(after:=Sheets(Sheets.Count)) objNeuBlatt.Name = Sheets(strAktBlatt).Range(strSpalte & lngZ) For ints = 1 To Sheets(strAktBlatt).Cells(1, Columns.Count).End(xlToLeft).Column objNeuBlatt.Cells(1, ints) = Sheets(strAktBlatt).Cells(1, ints) Next ints End If End If Next lngZ 'Übernahme der Daten auf die einzelnen Blätter: intAnzahlSpalten = Sheets(strAktBlatt).Cells(1, Columns.Count).End(xlToLeft).Column For lngZ = lngErsteZeile To lngLZ strName = Sheets(strAktBlatt).Range(strSpalte & lngZ) If strName <> "" Then lngAktZeile = Sheets(strName).Range(strSpalte & 65536).End(xlUp).Row + 1 Sheets(strName).Range(Sheets(strName).Cells(lngAktZeile, 1), Sheets(strName).Cells(lngAktZeile, intAnzahlSpalten)).Value = _ Sheets(strAktBlatt).Range(Sheets(strAktBlatt).Cells(lngZ, 1), Sheets(strAktBlatt).Cells(lngZ, intAnzahlSpalten)).Value End If Next lngZ End Sub

Tabellenblattnamen auslesen (VBA + Formel)Makro/Sub/ProzedurFormellösung

Kategorie: Tabelle ▸ Eigenschaften

(Tipp 134) Nachricht zum Beitrag an Autor Nach oben

Wie erhalte ich den Tabellenblatt-Namen?

VBA

Sub TabellenblattName() MsgBox ActiveSheet.Name End Sub


Formel

Die Funktion =ZELLE("Dateiname";A1) gibt den Pfad der Mappe bis zur Tabelle zurück. Der Dateiname steht dabei in eckigen Klammern, danach kommt der Tabellenname. Also kann der Teil von der letzten eckigen Klammer bis zum Schluss extrahiert werden:

=RECHTS(ZELLE("Dateiname");LÄNGE(ZELLE("Dateiname"))-FINDEN("]";ZELLE("Dateiname")))

Textdatei erstellen und Text wieder in Excel einlesenMakro/Sub/ProzedurUDF - benutzerdefinierte FunktionArrayfunktion/Matrixfunktion

Kategorie: Dateien und Ordner ▸ Dateioperation

(Tipp 33) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus einem Tabellenbereich eine Textdatei erstellen und diese Textdatei wieder in Excel einlesen?

In Spalte A wird solange gesucht, bis eine leere Zelle gefunden wird. Natürlich wäre auch eine For-Schleife mit Application.Cells(Rows.Count, 1).End(xlUp).Row möglich.

Die Daten aus A, B und C werden mit einem Semikolon als Trennzeichen in eine Textdatei eingelesen.

Einlesen in eine Textdatei mit immer gleichem Pfad:

Sub AlsTextSpeichern() Dim intI As Integer, lngDNr As LongPtr lngDNr = FreeFile 'Pfad anpassen Open "C:\Eigene Dateien\aus Tabelle.txt" For Output As #lngDNr intI = 2 'erste Zeile mit Angaben Do While Cells(intI, 1).Value <> "" 'Schleife, solange die Zelle nicht leer ist 'Übernehmen der Daten in die Textdatei Print #lngDNr, Cells(intI, 1) & ";" & Cells(intI, 2) & ";" & Cells(intI, 3) intI = intI + 1 Loop Close #lngDNr End Sub

Einlesen in eine Textdatei mit wählbarem Pfad:

Sub AlsTextSpeichern1() Dim intI As Integer, lngDNr As LongPtr Dim varPfad varPfad = Application.GetSaveAsFilename(InitialFileName:="Test", fileFilter:="Textdateien (*.txt), *.txt") If varPfad = False Then Exit Sub lngDNr = FreeFile Open varPfad For Output As #lngDNr intI = 2 Do While Cells(intI, 1).Value <> "" Print #lngDNr, Cells(intI, 1) & ";" & Cells(intI, 2) & ";" & Cells(intI, 3) intI = intI + 1 Loop Close #lngDNr End Sub

Textdatei in Exceldatei einlesen, immer gleicher Pfad:

Da das Semikolon als Trennzeichen verwendet wurde, brauchen wir die Textdatei als solche nicht aus- und in Excel einzulesen, sondern wir können die Datei direkt öffnen:

Sub AusTextAufrufen() On Error Resume Next 'falls Datei nicht existiert 'hier nur den Pfad ändern Workbooks.OpenText Filename:="C:\Eigene Dateien\aus Tabelle.txt", DataType:=xlDelimited, semicolon:=True End Sub

Textdatei in Exceldatei einlesen, wählbarer Pfad:

Sub AusTextAufrufen1() Dim varPfad varPfad = Application.GetOpenFilename(fileFilter:="Textdateien (*.txt), *.txt") If varPfad = False Then Exit Sub Workbooks.OpenText Filename:=varPfad, DataType:=xlDelimited, semicolon:=True End Sub

Gibt es andere Trennzeichen, erfolgt das Aufteilen auf die Zellen natürlich nicht unbedingt. Dann kann entweder mit Split() gearbeitet werden oder es kann mit der integrierten Methode Text in Spalten aufgeteilt werden.


Dynamische Arrayformel mit Matrixfunktion

Möglich ist natürlich auch ab Excel 365, die Textdatei mittels benutzerdefinierter Matrixfunktion auszulesen und die Ergebnisse als Array zu übergeben:

Function DateiEinlesen(strDatei, strTrenner, intSpalten) Dim intS As Integer, lngZ As LongPtr Dim lngDNr As Long, strZeile As String, arrTemp Dim arrS() lngDNr = FreeFile lngZ = 0 Open strDatei For Input As #lngDNr Do While Not EOF(lngDNr) Line Input #lngDNr, strZeile If strZeile <> "" Then arrTemp = Split(strZeile, strTrenner) lngZ = lngZ + 1 ReDim Preserve arrS(1 To intSpalten, 1 To lngZ) For intS = 1 To intSpalten If UBound(arrTemp) >= intS Then arrS(intS, lngZ) = arrTemp(intS) Else arrS(intS, lngZ) = "" End If Next End If Loop Close #lngDNr DateiEinlesen = Application.WorksheetFunction.Transpose(arrS) End Function

In die Zelle kommt dann nur noch die Formel:

=DateiEinlesen(Pfad zur Datei;Trennzeichen;Anzahl der Spalten)

=DateiEinlesen(A1;";";4)

Allerdings sollten die Dateien natürlich nicht zu groß sein, weil die Berechnung dieser Formel sonst alles verzögern würde.

Vornamen und Nachnamen trennenMakro/Sub/ProzedurUDF - benutzerdefinierte FunktionTipp

Kategorie: Stringoperationen ▸ Teile

(Tipp 124) Nachricht zum Beitrag an Autor Nach oben

In einem markierten Bereich befinden sich in jeweils einer Zelle Vornamen und Nachnamen, die durch Leerstellen getrennt sind. Wie kann ich Vornamen und Nachnamen in die Nachbarzellen einlesen lassen?

Hier wird an den Leerzeichen getrennt, ggf. müssen noch weitere Schreibweisen beachtet werden.

Schleife über die Zellen

Variante 1:

Sub Namen_trennen() Dim rngZelle As Range Dim intS As Integer Dim strV As String Dim arrTemp 'Bereich muß markiert sein, für jede Zelle in der Markierung: For Each Zelle In Selection With Zelle If .Value <> "" Then arrTemp = Split(.Value, " ") Select Case UBound(arrTemp) Case 0: Cells(.Row, .Column + 1) = .Value Case Else strV = "" For intS = 0 To UBound(arrTemp) - 1 strV = strV & IIf(strV <> "", " ", "") & arrTemp(intS) Next Cells(.Row, .Column + 1) = strV Cells(.Row, .Column + 2) = arrTemp(UBound(arrTemp)) End Select End If End With Next End Sub

Variante 2:

Sub Namen_trennen1() Dim intA As Integer, intB As Integer, intI As Integer Dim Zelle As Object 'Bereich muß markiert sein, 'für jede Zelle in der Markierung: For Each Zelle In Selection With Zelle If .Value <> "" Then 'Suche nach der ersten Leerstelle intA = InStr(.Value, " ") 'Schleife, falls mehrere durch leer getrennte Vornamen 'vorhanden sind, z. B. Ute Elke Meier For intI = 0 To Len(.Value) intB = InStr(Right(.Value, Len(.Value) - intA), " ") intA = InStr(Right(.Value, Len(.Value) - intA), " ") + intA Next 'Aufteilen auf die 1. Zelle rechts und die 2. Zelle rechts 'Vorname Cells(.Row, .Column + 1).Value = Left(.Value, intA - 1) 'Name Cells(.Row, .Column + 2).Value = Right(.Value, Len(.Value) - intA) End If End With Next End Sub

Text in Spalten

Variante 3:

Sub Namen_trennen2() Dim lngZeile As Long, lngSpalte As Long, strZiel As String lngZeile = ActiveCell.Row: lngSpalte = ActiveCell.Column strZiel = Cells(lngZeile, lngSpalte + 2).Address Selection.TextToColumns Destination:=Range(strZiel), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, _ FieldInfo:=Array(Array(1, 1), Array(2, 1)) End Sub

Es ist mit dieser Methode auch möglich, mehr als 2 Wörter, die mit Leerzeichen getrennt sind, in die Nachbarzellen zu übertragen. Sollten in nebenstehenden Zellen Daten stehen, muss man vor der Ausführung des Befehls darauf achten, entsprechend viele Spalten einzufügen.


Dynamische Arrayformel mit Matrixfunktion

Je nach Situation kann auch eine Arrayformel in Betracht gezogen werden:

Function NamenTrennen(ByVal strName As String, Optional intAnzahl As Integer = 5) Dim arrTemp, intS As Integer ReDim arrNamen(1 To intAnzahl) NamenTrennen = "" For intS = 1 To intAnzahl arrNamen(intS) = "" Next If strName <> "" Then arrTemp = Split(strName, " ") For intS = 0 To UBound(arrTemp) If intS < intAnzahl Then arrNamen(intS + 1) = arrTemp(intS) Next End If NamenTrennen = arrNamen End Function

In die Zelle kommt dazu diese überlaufende Formel:

=NamenTrennen(A1)

Da die Anzahl der Namensteile variieren kann, ein Array aber (in diesem Fall) immer gleich breit ist, ist eine Breite von fünf Zellen voreingetragen. Der Array wird dabei von links gefüllt, so dass die einzelnen Teile in der linken Zelle beginnen. Mit einem zweiten Parameter in der Funktion kann diese Breite geändert werden, zum Beispiel auf vier Zellen Breite:

=NamenTrennen(A1;4)

Besteht der Name dann aus mehr Teilen, werden die restlichen rechten Teile nicht angezeigt.



Workbook-EreignisseMakro/Sub/ProzedurTipp

Kategorien: Basics ▸ Ereignisse und Ereignisse ▸ Basics

(Tipp 98) Nachricht zum Beitrag an Autor Nach oben

Bei den Workbook-Ereignissen trifft eingeschränkt das zu, was bei den Application-Ereignissen steht. Der markanteste Unterschied ist, dass Workbook-Ereignisse - wie der Name schon sagt - nur die Elemente der Mappe mit dem Code betreffen und dass wir hier kein Klassenmodul einfügen müssen.

Im Visual-Basic-Editor (Alt & F11) reicht es, im Projektfenster auf Diese Arbeitsmappe doppelzuklicken und dann von Allgemein auf Workbook zu wechseln. Nun stehen im rechten Drop-Down-Feld die Ereignisse zur Verfügung:

Parameterinfo

Bei verschiedenen Prozeduren werden auch Parameter übergeben. Diese verhalten sich wie folgt:

Cancel:Die Boolsche Variable steht standardmäßig auf False. Setzt man sie auf True, wird das Ereignis nicht mehr ausgeführt. So kann man z. B. das Schließen der Arbeitsmappe verhindern, indem man Cancel = True innerhalb der Prozedur BeforeClose setzt.
Sh:Sh steht für das aktive Tabellenblatt. Man beachte auch die Eigenschaften und Methoden, die Sh zur Verfügung stehen. So erhält man z.B. über Sh.Name den Namen des aktiven Blattes.
Target:Target steht für den aktiven Bereich und wird häufig dazu benutzt, um den Bereich zum Ausführen eines bestimmten Makros zu bestimmen. So kann man mit: If Target.Address = $A$1 erreichen, daß das Makro nur dann ausgeführt wird, wenn die Zelle A1 aktiv ist.
Wn:Stellt das aktive Fenster dar.

Ereignisse:

Workbook_Activate
Tritt ein, nachdem die Arbeitsmappe aktiviert wurde.
Workbook_AddinInstall
Tritt ein, wenn die Arbeitsmappe als Add-In installiert wurde.
Workbook_AddinUninstall
Tritt ein, wenn die Arbeitsmappe als Add-In deinstalliert wurde.
Workbook_BeforeClose(Cancel As Boolean)
Tritt ein, bevor die Arbeitsmappe geschlossen werden soll.
Workbook_BeforePrint(Cancel As Boolean)
Tritt ein, wenn die Arbeitsmappe ausgedruckt werden soll. Man benutzt diese Prozedur häufig zum Aktualisieren der Daten vor dem Drucken.
Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Tritt ein, wenn die Arbeitsmappe gespeichert werden soll. Man benutzt diese Prozedur häufig zum Aktualisieren der Daten vor dem Speichern. SaveAsUI hat dabei den Wert True, wenn das Dialogfeld Save As angezeigt wird.
Workbook_Deactivate
Tritt ein, wenn die Arbeitsmappe deaktiviert wird, zum Beispiel beim Wechsel in eine andere Arbeitsmappe.
Workbook_NewSheet(ByVal Sh As Object)
Tritt ein, wenn ein neues Blatt eingefügt wird.
Workbook_Open
Tritt ein, wenn die Arbeitsmappe geöffnet wurde. Diese Prozedur wird häufig dazu verwendet, um Werte zu initialisieren, welche später in der BeforeClose-Prozedur wieder entfernt werden sollten.
Workbook_SheetActivate(ByVal Sh As Object)
Tritt ein, wenn ein Blatt aktiviert wird, also bei einem Blattwechsel.
Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
Tritt bei einem Doppelklick auf einem Tabellenblatt ein.
Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
Tritt bei einem Klick mit der rechten Maustaste in einem Tabellenblatt ein.
Workbook_SheetCalculate(ByVal Sh As Object)
Tritt ein, wenn Zellen eines Blattes neu berechnet werden. Dieses Ereigniss tritt auch dann ein, wenn sich ein Bezug verändert. Z. B. in B1 steht "= A1" und A1 wird verändert, so wird die Prozedur ausgeführt, da B1 neu berechnet wird.
Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Tritt ein, wenn mindestens eine Zelle in einem Blatt geändert wurde. Leider gibt es immer wieder Probleme bei externen Bezügen, wenn z.B. Daten per DDE geholt werden. Man sollte dann prüfen, ob man nicht mit Calculate zum Ergebnis kommt, indem man einen Bezug zur DDE-Zelle herstellt (=A1).
Workbook_SheetDeactivate(ByVal Sh As Object)
Tritt ein, wenn ein Blattwechsel stattgefunden hat.
Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Tritt ein, wenn sich die Markierung in einem Blatt ändert.
Workbook_WindowActivate(ByVal Wn As Excel.Window)
Tritt bei einem Fensterwechsel ein.
Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
Tritt bei einem Fensterwechsel ein.
Workbook_WindowResize(ByVal Wn As Excel.Window)
Tritt bei einer Veränderung der Fenstergröße ein.

Worksheet-EreignisseMakro/Sub/ProzedurTipp

Kategorien: Basics ▸ Ereignisse und Ereignisse ▸ Basics

(Tipp 99) Nachricht zum Beitrag an Autor Nach oben

Wie der Name schon sagt, geht es bei den Worksheet-Ereignissen um Aktivitäten in Bezug auf das einzelne Tabellenblatt.

Im Visual-Basic-Editor (Alt & F11) wird dazu im Projektfenster auf die jeweilige Tabelle doppelt geklickt und dann von "Allgemein auf "Worksheet gewechselt. Nun stehen im rechten Drop-Down-Feld die Ereignisse zur Verfügung.

Im Gegensatz zu den Workbook-Ereignissen, welche bei allen Tabellenblättern auftreten, sind die Worksheet-Ereignisse an das Tabellenblatt gebunden, indem sie stehen.

Parameterinfo

Bei verschiedenen Prozeduren werden auch Parameter übergeben. Diese verhalten sich wie folgt:

Cancel:Die Boolsche Variabel steht standardmäßig auf False. Setzt man sie auf True, wird das Ereignis nicht mehr ausgeführt. So kann man z. B. das Öffnen des Auswahlmenüs verhindern, indem man Cancel = True innerhalb der Prozedur "BeforeRightClick setzt.
Target:Target steht für den aktiven Bereich und wird häufig dazu benutzt, um den Bereich zum Ausführen eines bestimmten Makros zu bestimmen. So kann man mit: If Target.Address = $A$1 erreichen, daß das Makro nur dann ausgeführt wird, wenn die Zelle A1 aktiv ist.

Ereignisse:

Worksheet_Activate
Tritt ein, wenn das Tabellenblatt aktiviert wird, also Blattwechsel zu diesem Blatt hin.
Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Tritt bei einem Doppelklick innerhalb der Tabelle ein.
Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)
Tritt bei einem Klick mit der rechten Maustaste innerhalb der Tabelle ein.
Worksheet_Calculate
Tritt ein, wenn Zellen dieses Blattes neu berechnet werden. Dieses Ereignis tritt auch dann ein, wenn sich ein Bezug verändert. Z. B. in B1 steht "= A1" und A1 wird verändert, so wird die Prozedur ausgeführt, da B1 neu berechnet wird.
Worksheet_Change (ByVal Target As Excel.Range)
Tritt ein, wenn mindestens eine Zelle im Tabellenblatt geändert wurde. Leider gibt es immer wieder Probleme bei externen Bezügen, wenn z. B. Daten per DDE geholt werden. Man sollte dann prüfen, ob man nicht mit dem Calculate zum Ergebnis kommt, indem man einen Bezug zur DDE-Zelle herstellt (=A1).
Worksheet_Deactivate
Tritt ein, wenn man von diesem Blatt aus zu einem anderem wechselt.
Worksheet_SelectionChange(ByVal Target As Excel.Range)
Tritt ein, wenn sich die Markierung im Tabellenblatt ändert.

Zahl mit Trennzeichen trennenUDF - benutzerdefinierte Funktion

Kategorien: Stringoperationen ▸ Ersetzen und Stringoperationen ▸ Verketten

(Tipp 553) Nachricht zum Beitrag an Autor Nach oben

Eine Zahl, z. B. 8070110, soll nach jeder 0 einen Bindestrich haben, also so: 80-70-110.

Hier ist eine benutzerdefinierte Funktion:

Function zahl_aufteilen(Zahl, Ziffer, Trenner) Application.Volatile Dim intI As Integer Dim strTemp As String, strTrenner As String strTrenner = Ziffer & Trenner strTemp = Replace(Zahl, Ziffer, strTrenner) If Right(strTemp, 1) = Trenner Then strTemp = Left(strTemp, Len(strTemp) - 1) zahl_aufteilen = strTemp End Function

Dazu mit Alt und F11 den Editor aufrufen, ein Modul einfügen und die Function eingeben. In die Zelle kommt dann z. B. die folgende Formel:

=zahl_aufteilen(B6;0;"-")


Regulärer Ausdruck

Eine weitere Variante ist diese Funktion:

Function Zahl_Aufteilen_Regex(ByVal varZahl, ByVal strTrenner As String, intZiffer As Integer) Dim Regex As Object, regMatches Set Regex = CreateObject("VBScript.RegExp") Regex.Global = True Regex.Pattern = intZiffer & "\B" Set regMatches = Regex.Execute(varZahl) Zahl_Aufteilen_Regex = Regex.Replace(varZahl, intZiffer & strTrenner) Set Regex = Nothing End Function

In die Zelle käme diese Formel:

=Zahl_Aufteilen_Regex(B6;"-"; 0)

Zufallszahlen sortiert in einem bestimmten Bereich generieren (mit Arrayfunktion)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionArrayfunktion/Matrixfunktion

Kategorien: Tabelle ▸ Zellen und Filter/Sortieren

(Tipp 139) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich zwischen 6 und 15 Zufallszahlen zwischen 1 und 49 generieren? Die Zahlen sollen auf dem Blatt Tab1 in der Zeile 1 ab A1 stehen. Keine Zahl darf sich wiederholen.

Dazu gibt es verschiedene Möglichkeiten.

Direkt in Zellen eintragen

In der ersten Variante erfolgt die Arbeit direkt an den Zellen, weil hier die Funktionen Finden und Sortieren von Excel genutzt werden. Funktionen, die Excel zur Verfügung stellt, sind i. d. R. recht schnell, so dass das in diesem Fall sicher die kürzere und schnellere Variante ist:

Sub Zufall() Dim intWert As Integer, varWieviele, intI As Integer Dim bolVorhanden As Boolean, rngGef As Range varWieviele = InputBox("Wieviele Zahlen sollen erzeugt werden?", "Anzahl", 6) If Not IsNumeric(varWieviele) Then Exit Sub If varWieviele > 15 Then Exit Sub Sheets("Tab1").Range("A1:O1").ClearContents For intI = 1 To varWieviele intWert = Int((49 * Rnd) + 1) If intI = 1 Then Sheets("Tab1").Cells(1, intI) = intWert Else Do bolVorhanden = False Set rngGef = Range(Cells(1, 1), Cells(1, intI - 1)).Find(intWert) If Not rngGef Is Nothing Then bolVorhanden = True intWert = Int((49 * Rnd) + 1) End If Loop While bolVorhanden = True Sheets("Tab1").Cells(1, intI) = intWert End If Next Sheets("Tab1").Range("A1:O1").Sort Key1:=Sheets("Tab1").Range("A1"), Order1:=xlAscending, Orientation:=xlLeftToRight End Sub

Kern des Codes ist eine Schleife, die so lange läuft, wie eine Zufallszahl nicht mehr in den bisherigen Zufallszahlen gefunden wird. Erst dann wird sie als neue Zufallszahl verwendet.

Weiteres Beispiel - Schleife statt Find:

Sub Zufall() Dim intAnzahl As Integer, intMax As Integer, intMin As Integer Dim intWert As Integer, intI As Integer, intN As Integer Dim bolVorhanden As Boolean intAnzahl = 10 intMax = 49 intMin = 1 'alte Zahlen löschen Range(Cells(1, 2), Cells(intAnzahl, 2)).ClearContents Cells(1, 2) = Int((intMax * Rnd) + intMin) '1. Zahl erzeugen For intI = 2 To intAnzahl 'nächste Zahlen erzeugen Do bolVorhanden = False intWert = Int((intMax * Rnd) + intMin) For intN = 1 To intI 'Kontrolle ob schon vorhanden If Cells(intN, 2) = intWert Then bolVorhanden = True Exit For End If Next Loop While bolVorhanden = True Cells(intI, 2) = intWert 'Zahl eintragen Next End Sub


Erst Array, zum Schluss in Zellen

Manchmal kann die Arbeit mit Zellen aber auch von Nachteil sein. Deshalb ist hier der Vollständigkeit halber noch ein Beispiel, in dem Herangehensweisen mit einem Array aufgezeigt werden:

Sub Zufall1() Dim intWert As Integer, intI As Integer, intN As Integer, varWieviele Dim bolVorhanden As Boolean, bolSortiert As Boolean Dim arrZahlen() varWieviele = InputBox("Wieviele Zahlen sollen erzeugt werden?", "Anzahl", 6) If Not IsNumeric(varWieviele) Then Exit Sub If varWieviele > 15 Then Exit Sub ReDim Preserve arrZahlen(varWieviele) For intN = 0 To varWieviele - 1 intWert = Int((49 * Rnd) + 1) If intN = 0 Then arrZahlen(intN) = intWert Else Do bolVorhanden = False For intI = 0 To intN If arrZahlen(intI) = intWert Then bolVorhanden = True intWert = Int((49 * Rnd) + 1) Exit For End If Next Loop While bolVorhanden = True arrZahlen(intN) = intWert End If Next Do bolSortiert = True For intN = 0 To varWieviele - 2 If arrZahlen(intN + 1) < arrZahlen(intN) Then bolSortiert = False intWert = arrZahlen(intN + 1) arrZahlen(intN + 1) = arrZahlen(intN) arrZahlen(intN) = intWert End If Next Loop While bolSortiert = False Sheets("Tab1").Range("A1:O1").ClearContents For intN = 0 To varWieviele - 1 Sheets("Tab1").Cells(1, intN + 1) = arrZahlen(intN) Next End Sub

Das gesamte Erstellen der Zufallszahlen - bis hin zum Sortieren - erfolgt zunächst in einem Array. Auch hier erfolgt die Prüfung auf Doppelungen. Aber es ist zu sehen, dass dafür eine weitere Schleife eingebaut ist. Eine Funktion wie in PHP in_array() wäre da natürlich günstiger.

Auch zum Sortieren wird die innere Schleife so lange durchlaufen, bis im Array das nächste Element nicht kleiner als das gerade durchlaufene Element ist.

Erst ganz zum Schluss wird der fertige Array in die Zellen übernommen.

Die zweite Variante hat den Vorteil, dass sie beliebig - und unabhängig von Zellen - eingesetzt werden kann. Sie kann auch als eigenständige Funktion erstellt werden, die dann den Array mit den Zufallszahlen zurückgibt. So könnte die Funktion von überall aufgerufen werden; in die Zellen würde dann das Ergebnis eingetragen.

Oft stößt man bei Schleifen, die Zellen lesen und schreiben, auf das Problem, dass die Laufzeit enorm steigt. Deshalb sollte zumindest in Betracht gezogen werden, das Ganze mit Arrays zu erledigen und die Zellzugriffe auf ein Minimum zu beschränken.


Dynamische Arrayformel mit Matrixfunktion (ab Excel 365)

Natürlich kann das auch als Matrixfunktion für eine dynamische Arrayformel erstellt werden. Die Funktion erstellt einen Array mit eindeutigen Zahlen. Zum Schluss wird mit der Arrayfunktion SORTIEREN() sortiert und der Array ausgegeben.

Function Zufallszahlen_Eindeutig(ByVal intAnzahl As Integer, ByVal intMin As Integer, ByVal intMax As Integer) Dim intWert As Integer, intI As Integer, intN As Integer Dim bolVorhanden As Boolean, arrZahlen() Application.Volatile ReDim Preserve arrZahlen(1 To intAnzahl) For intN = 1 To intAnzahl intWert = Int((intMax * Rnd) + intMin) If intN = 1 Then arrZahlen(intN) = intWert Else Do bolVorhanden = False For intI = 1 To intN If arrZahlen(intI) = intWert Then bolVorhanden = True intWert = Int((intMax * Rnd) + intMin) Exit For End If Next Loop While bolVorhanden = True arrZahlen(intN) = intWert End If Next Zufallszahlen_Eindeutig = Application.WorksheetFunction.Sort(arrZahlen, 1, 1, 1) End Function

In die Zelle kommt einfach:

=Zufallszahlen_Eindeutig(Anzahl;Minimum;Maximum)

=Zufallszahlen_Eindeutig(5;1;49)

Sollen die Zahlen untereinander erscheinen, kann die Funktion in MTRANS() gesetzt werden:

=MTRANS(Zufallszahlen_Eindeutig(5;1;49))

Die Funktion ZUFALLSMATRIX() klingt danach, als ob sie die Aufgabe auch erfüllen könnte. Jedoch hat sie bei dieser Aufgabenstellung den großen Nachteil, dass sie sehr häufig mehrfach vorhandene Zahlen erzeugt. Das heißt, dass man so oft berechnen lassen müsste, bis jede Zahl eindeutig ist - und das kann etwas dauern.

Apropos eindeutig: Mit der Funktion EINDEUTIG() könnte natürlich geprüft werden, ob eindeutige Zufallszahlen vorliegen. Allerdings ist die Funktion manchmal schneller als die Sortieren-Funktion, so dass letztere Funktion noch rechnet, wenn EINDEUTIG() schon fertig ist. Das führt dann zum bekannten Fehler ÜBERLAUF.