Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Auf Monatsblatt beim Datum eingetragene Namen zählenFormellösungArrayfunktion/Matrixfunktion

Kategorien: Datum/Zeit ▸ Datum und Tabelle ▸ Matrix

(Tipp 333) Nachricht zum Beitrag an Autor Nach oben

Auf Blättern, die mit Monatsnamen benannt sind, befinden sich in Zeile 1 Ab Spalte A nebeneinander die Datumsangaben des Monats (bis AE). Unter diesen Angaben sind ab Zeile 2 die anwesenden Mitarbeiter eingetragen. Wie kann ich diese zählen?

Ab Excel 365

Zunächst muss das Blatt mit dem Monatsnamen des aktuellen Datums ermittelt werden. Das geht mit Indirekt:

=INDIREKT(TEXT(HEUTE();"MMMM")&"!A1:AE1")

Diese Formel liefert die komplette erste Zeile mit den Datumsangaben des aktuellen Monats. Sie kann nun in der Funktion FILTER() als Suchbereich verwendet werden. Damit können alle Mitarbeiter des Tages ermittelt werden (hier bis Zeile 30):

=FILTER(INDIREKT(TEXT(HEUTE();"MMMM")&"!A2:AE30");INDIREKT(TEXT(HEUTE();"MMMM")&"!A1:AE1")=HEUTE())

Die Formel gibt aus der Datumsspalte alle Einträge zurück, die bis Zeile 30 liegen.

Da aber mehr Zeilen (wie hier bis 30) verwendet werden müssen (die maximale Anwesenheit muss ja berücksichtigt werden), liefert diese Formel für die leeren Zellen, in denen also kein Mitarbeiter eingetragen ist, jeweils eine 0. Das kann also weder mit ANZAHL() noch mit ANZAHL2() gezählt werden, da sonst die 0 immer einfließen würde.

Wir verwenden im Beispiel SUMME(Wenn( und lassen die Einträge zählen, die <> 0 sind:

=SUMME(WENN(FILTER(INDIREKT(TEXT(HEUTE();"MMMM")&"!A2:AE30");INDIREKT(TEXT(HEUTE();"MMMM")&"!A1:AE1")=HEUTE())<>0;1;0))

Ältere Versionen

Hier ist die Formel etwas länger:

=ANZAHL2(INDIREKT(TEXT(HEUTE();"MMMM")&"!"&LINKS(ADRESSE(1;VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"));4);1+(VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"))>26))&"2:"&LINKS(ADRESSE(1;VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"));4);1+(VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"))>26))&"1000"))

Balkendiagramm in ZelleFormellösungTipp

Kategorie: Programmiertechnik ▸ Darstellung

(Tipp 175) Nachricht zum Beitrag an Autor Nach oben

Wie kann man eine %-Zahl als Balkendiagramm in einer Zelle darstellen?

Eine Zahl soll grafisch dargestellt werden, aber ein Diagrammobjekt soll es auch nicht gleich sein? Kein Problem - es geht auch mit einfachen Kästchen oder anderen Zeichen, die in vorhandenen Schriftarten enthalten sind. Also: Die Zelle, in/an der der Balken erscheinen soll, als Wingdings formatieren und eingeben:

=WIEDERHOLEN("n";B1*100)

In B1 steht im Beispiel die Zahl, die dargestellt werden soll. Die 100 so anpassen, dass die Länge des Balkens wie gewünscht ist.

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

Formeln druckenFormellösungTipp

Kategorien: Drucken/Seite und Tabelle ▸ Formeln

(Tipp 182) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich anstelle der Ergebnisse die Formeln drucken?

Sollen alle Formeln des Blattes gedruckt werden:

  • Optionen
  • Erweitert
  • Optionen für dieses Arbeitsblatt anzeigen
  • Anstelle der berechneten Werte Formeln in Zellen anzeigen

Alternativ geht es auch mit der Tastenkombination Strg und ` (Graviszeichen, Taste rechts neben ß mit Umschalttaste drücken).

Sollen nur ausgewählte Formeln gedruckt werden, kann man als erstes in die Zelle ein Hochkomma (') eingeben, dann wird der Inhalt der Zelle als Text behandelt. Das empfiehlt sich übrigens auch dann, wenn eine längere Formel nicht beendet ist, aber die Mappe geschlossen werden soll.

Ab Excel 365

Sollen die Formeln zusätzlich zu den Ergebnissen gedruckt werden, können die mit der Funktion FORMELTEXT() angezeigt werden:

=FORMELTEXT(A1)

Numerische und alphanumerische Werte sortieren (mit Regex)Makro/Sub/ProzedurUDF - benutzerdefinierte Funktion

Kategorien: Filter/Sortieren und Stringoperationen ▸ Teile

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

Wie kann ich folgende Zahlen in Spalte A sortieren: 100, 101, 100a, 100b, 102? Dazwischen befinden sich keine leeren Zellen.

Hinweis:
Dieser Artikel zeigt gleichzeitig Möglichkeiten zur Arbeit mit benutzerdefinierten Funktionen (Datenfeld als Rückgabewert bzw. Ergebnis), mehrdimensionalen Arrays, regulären Ausdrücken (regular Expressions), verschiedenen Schleifen und mehr.

Variante 1

Die erste Variante hält sich genau an die Aufgabenstellung, dass also an einer führenden Ganzzahl genau ein Buchstabe sein darf. Dazwischen darf sich keine Leerstelle befinden.

Es wird eine Schleife über alle Zellen so lange durchlaufen, bis die Daten tatsächlich sortiert sind, bolSortiert also nicht mehr False wird.

In der Schleife werden aus der gerade durchlaufenen Zeile und aus der nächsten Zeile die Zahlen extrahiert, aus 100b also die 100. Diese Zahlen werden den Variablen c und d zugewiesen.

Im nächsten Schritt wird geprüft, ob die folgende Zahl kleiner als die aktuelle ist. Wenn ja, werden die beiden Zellinhalte getauscht, so dass die kleinere Zahl zuerst steht.

Sind die Zahlen jedoch gleich, wie es bei 100a und 100b der Fall ist, wird geprüft, ob die rechte Stelle (Right(b, 1)) der nächsten Zeile kleiner als die der aktuellen Zeile ist. Zum Beispiel ist das a in 100a kleiner als das b in 100b. Wenn ja, werden die Zellen getauscht.

In beiden ja-Fällen wird die Variable bolSortiert auf False gesetzt, so dass die Do-Loop-Schleife weiß, dass sie von vorn beginnen, also die For-Schleife über die Zellen neu starten soll.

Sub SortierenEinfach() Dim lngEZ As Long, lngLZ As Long, intS As Integer, lngI As Long Dim a, b, c, d Dim bolSortiert As Boolean lngEZ = 1 'erste Zeile - anpassen! intS = 1 'Spalte A lngLZ = Cells(Rows.Count, intS).End(xlUp).Row Do bolSortiert = True For lngI = lngEZ To lngLZ - 1 a = Cells(lngI, intS) b = Cells(lngI + 1, intS) If IsNumeric(a) = False Then If a <> "" Then c = Left(a, Len(a) - 1) Else c = 0 Else: c = a End If If IsNumeric(b) = False Then If b <> "" Then d = Left(b, Len(b) - 1) Else d = 0 Else: d = b End If If CInt(d) < CInt(c) Then Cells(lngI, intS) = b Cells(lngI + 1, intS) = a bolSortiert = False ElseIf CInt(c) = CInt(d) Then If Right(b, 1) < Right(a, 1) Then Cells(lngI, intS) = b Cells(lngI + 1, intS) = a bolSortiert = False End If End If Next Loop While bolSortiert = False End Sub


Erweiterte Variante mit benutzerdefinierter Funktion (UDF)

Was nun aber, wenn man nicht voraussetzen kann, dass die Syntax dem Schema entspricht? Wenn es zum Beispiel Leerstellen zwischen Zahl und Buchstaben oder mehrere Buchstaben geben kann? Oder vielleicht sogar negative Zahlen?

In dem Fall versagt die erste Variante.

Für diese Eventualitäten müssen wir den String aus Zahl und Text besser aufteilen. Mit Right(String, 1) geht es nicht, da wir damit immer nur den letzten Buchstaben erwischen würden. Split ist auch nicht möglich, da wir kein Trennzeichen haben und der Split auf "" den kompletten String zurückgibt. Also müssen wir das selbst machen.

Da das etwas länger wird und wir den Code zum Extrahieren der Zahl zweimal brauchen, er also zweimal in der Scheife vorkommen würde, schreiben wir dazu eine Funktion, der wir den kompletten String aus Zahl und möglichen Buchstaben übergeben. Als Ergebnis erhalten wir ein Datenfeld aus zwei Elementen. Im ersten steht die extrahierte Zahl oder 0, wenn keine Zahl im String entalten ist. Im zweiten Element steht die Buchstabenfolge vom Schluss des Originalstrings oder "", wenn es keine Buchstaben gibt.

Die beiden Elemente werden vorbelegt, so dass später nur die Änderungen übergeben werden müssen. Zu sehen ist, dass als Zahl erkannt wird, wenn es sich um eine Zahl, ein Komma oder ein Minus handelt. Bei Bedarf kann das natürlich angepasst werden. Wichtig ist, dass die Zahlen-Zeichenfolge nicht mehr erweitert wird, wenn einmal ein anderes Zeichen erkannt wurde. Deshalb (und falls mal keine Ziffer am Anfang steht) wurde bolZahl = True gesetzt. Sobald im String kein Zahlzeichen (Ziffer, Komma, Minus) gefunden wird, ist diese Variable False und es wird alles dem Buchstabenstring zugeordnet - auch, wenn später im String noch eine Ziffer kommen sollte.

Damit der Zahlenstring später als Zahl erkannt wird, wird einfach mit 1 multiplizert, um etwas zu berechnen. Man kann das natürlich auch an eine extra dafür deklarierte Variable übergeben.

Das ist die Funktion:

Function StringSplit(ByVal varWert As Variant) Dim arrErgeb(1 To 2) Dim strZahl As String, strString As String, bolZahl As Boolean Dim intN As Integer arrErgeb(1) = 0 'vorbelegen, später werden nur Änderungen übergeben arrErgeb(2) = "" If IsNumeric(varWert) Then arrErgeb(1) = varWert * 1 Else strZahl = "": strString = "": bolZahl = False For intN = 1 To Len(varWert) If IsNumeric(Mid(varWert, intN, 1)) Or Mid(varWert, intN, 1) = "," Or Mid(varWert, intN, 1) = "-" Then If strString = "" Then strZahl = strZahl & Mid(varWert, intN, 1) bolZahl = True Else If intN = 1 Then bolZahl = True If bolZahl Then strString = strString & Mid(varWert, intN, 1) End If Next arrErgeb(1) = IIf(strZahl = "", 0, strZahl * 1) arrErgeb(2) = Trim(strString) End If StringSplit = arrErgeb End Function

Die eigentliche Routine zum Sortieren ist dann vom Aufbau her wie die vorige, nur dass anstelle der Variablen c und d die Rückgaben aus der Funktion stehen:

Sub SortierenAlphaNum() Dim lngEZ As LongPtr, lngLZ As LongPtr, intS As Integer, lngZ As LongPtr Dim a, b Dim bolSortiert As Boolean Dim arrA(), arrB() lngEZ = 1 'erste Zeile - anpassen! intS = 1 'Spalte A lngLZ = Cells(Rows.Count, intS).End(xlUp).Row Do bolSortiert = True For lngZ = lngEZ To lngLZ - 1 a = Cells(lngZ, intS) b = Cells(lngZ + 1, intS) arrA = StringSplit(Cells(lngZ, intS)) arrB = StringSplit(Cells(lngZ + 1, intS)) 'die nächste Zahl ist kleiner als die aktuelle: If arrB(1) < arrA(1) Then Cells(lngZ, intS) = b Cells(lngZ + 1, intS) = a bolSortiert = False 'nächste Zahl ist wie die aktuelle, mögliche Strings prüfen ElseIf arrA(1) = arrB(1) Then If arrB(2) < arrA(2) Then Cells(lngZ, intS) = b Cells(lngZ + 1, intS) = a bolSortiert = False End If End If 'Falls Datensätze im Spiel sind und die Daten sortiert werden sollen, 'können die folgenden Zeilen die Nummern gesplittet in die Nachbarzellen 'schreiben, um dann danach zu sortieren: Cells(lngZ, intS + 1) = arrA(1) Cells(lngZ, intS + 2) = arrA(2) If lngZ = lngLZ - 1 Then Cells(lngZ + 1, intS + 1) = arrB(1) Cells(lngZ + 1, intS + 2) = arrB(2) End If Next Loop While bolSortiert = False End Sub


Microsoft VBScript Regular Expressions

Variante mit regulären Ausdrücken/Regular Expressions

Das mit der Funktion ist zu lang? Kein Problem, es geht auch kürzer. Wenn im VB-Projekt ein Verweis zu Microsoft VBScript Regular Expressions gesetzt wird, können wir reguläre Ausdrücke verwenden.

Das Suchmuster wäre in diesem Fall:

regex.Pattern = "^([-0-9,]+)([ a-zA-Z0-9]*)$"

Das heißt, am Anfang des Strings mit Zahl und Buchstaben muss eins der Zeichen von 0 bis 9, ein Minus oder ein Komma wenigstens einmal (deshalb das +) stehen. Dahinter kann (deshalb das *) eine Kombination aus Leerzeichen, Buchstaben und weiteren Ziffern folgen. Diese beiden Teile stehen in runden Klammern und werden dann an die Variablen arrA und arrB übergeben, wenn Matches gefunden wurden. Im jeweils ersten Element (arrA(1) und arrB(1) ) der Variablen stehen wieder die Zahlen, die - damit sie nicht als Text erkannt werden - mit 1 multipliziert werden.

Der Rest ist dann so wie bei den anderen beiden Varianten.

Sub SortierenAlphaNumRegex() Dim regex As New RegExp Dim regMatches As MatchCollection, regMatch As Match Dim lngEZ As LongPtr, lngLZ As LongPtr, intS As Integer, lngZ As LongPtr Dim a, b Dim bolSortiert As Boolean Dim arrA(1 To 2), arrB(1 To 2) lngEZ = 1 'erste Zeile - anpassen! intS = 1 'Spalte A lngLZ = Cells(Rows.Count, intS).End(xlUp).Row regex.Pattern = "^([-0-9,]+)([ a-zA-Z0-9]*)$" Do bolSortiert = True For lngZ = lngEZ To lngLZ - 1 a = Cells(lngZ, intS) arrA(1) = 0: arrA(2) = "" Set regMatches = regex.Execute(Cells(lngZ, intS)) If regMatches.Count > 0 Then arrA(1) = regMatches(0).SubMatches(0) * 1 arrA(2) = regMatches(0).SubMatches(1) End If b = Cells(lngZ + 1, intS) arrB(1) = 0: arrB(2) = "" Set regMatches = regex.Execute(Cells(lngZ + 1, intS)) If regMatches.Count > 0 Then arrB(1) = regMatches(0).SubMatches(0) * 1 arrB(2) = regMatches(0).SubMatches(1) End If 'die nächste Zahl ist kleiner als die aktuelle: If arrB(1) < arrA(1) Then Cells(lngZ, intS) = b Cells(lngZ + 1, intS) = a bolSortiert = False 'nächste Zahl ist wie die aktuelle, mögliche Strings prüfen ElseIf arrA(1) = arrB(1) Then If arrB(2) < arrA(2) Then Cells(lngZ, intS) = b Cells(lngZ + 1, intS) = a bolSortiert = False End If End If 'Falls Datensätze im Spiel sind und die Daten sortiert werden sollen, 'können die folgenden Zeilen die Nummern gesplittet in die Nachbarzellen 'schreiben, um dann danach zu sortieren: Cells(lngZ, intS + 1) = arrA(1) Cells(lngZ, intS + 2) = arrA(2) If lngZ = lngLZ - 1 Then Cells(lngZ + 1, intS + 1) = arrB(1) Cells(lngZ + 1, intS + 2) = arrB(2) End If Next Loop While bolSortiert = False End Sub


Beschleunigen: Sortieren per Array

Schnell wird man feststellen, dass die Laufzeit bei solchen Routinen sehr lang werden kann. Die Ursache liegt hier jedoch weniger bei den Schleifen, sondern eher darin, dass immer wieder Lese- und Schreibzugriffe auf die Zellen erfolgen. Das bremst die Schleifen aus.

Nun könnten wir mit Application.ScreenUpdating = False die Bildschirmaktualisierung ausschalten. Das würde tatsächlich ein paar Zehntelsekunden bringen, vielleicht auch Sekunden. Schneller geht es jedoch, wenn wir für den eigentlich Sortiervorgang gar nicht auf die Zellen zugreifen.

Dazu lesen wir alle Zellen zunächst in einen Array ein:

Dim arrSamm(), arrTemp lngEZ = 1 'erste Zeile - anpassen! intS = 1 'Spalte A lngLZ = Cells(Rows.Count, intS).End(xlUp).Row ReDim Preserve arrSamm(1 To 2, lngEZ To lngLZ) For lngZ = lngEZ To lngLZ arrSamm(1, lngZ) = Cells(lngZ, intS) Next

arrSamm() hat hier zwei Spalten (1 To 2), obwohl nur eine reichen würde. Die zweite Spalte wird in diesem Beispiel nur die aufgesplitteten Strings, also die Zahlen und die Buchstaben, zur späteren Ausgabe aufnehmen - diese jeweils als Datenfelder. In der ersten SDpalte von arrSamm() werden also die zu sortierenden Strings stehen, in der zweiten die getrennten Daten.

Sind die Daten im Array, führen wir dort den Sortiervorgang durch. Das Prinzip ist genau das der bisherigen Varianten, nur eben nicht an Zellen.

Erst ganz zum Schluss schreiben wir den - nun sortierten - Array wieder in die Zellen:

For lngZ = lngEZ To lngLZ Cells(lngZ, 1) = arrSamm(1, lngZ) arrTemp = arrSamm(2, lngZ) Cells(lngZ, 2) = arrTemp(1) Cells(lngZ, 3) = arrTemp(2) Next

Die Routine als Ganzes:

Sub SortierenAlphaNumRegexArray() Dim Regex As New RegExp Dim regMatches As MatchCollection, regMatch As Match Dim lngEZ As LongPtr, lngLZ As LongPtr, intS As Integer, lngZ As LongPtr Dim a, b Dim bolSortiert As Boolean Dim arrA(1 To 2), arrB(1 To 2) Dim arrSamm(), arrTemp lngEZ = 1 'erste Zeile - anpassen! intS = 1 'Spalte A lngLZ = Cells(Rows.Count, intS).End(xlUp).Row ReDim Preserve arrSamm(1 To 2, lngEZ To lngLZ) For lngZ = lngEZ To lngLZ arrSamm(1, lngZ) = Cells(lngZ, intS) Next Regex.Pattern = "^([-0-9,]+)([ a-zA-Z0-9]*)$" Do bolSortiert = True For lngZ = lngEZ To lngLZ - 1 a = arrSamm(1, lngZ) arrA(1) = 0: arrA(2) = "" Set regMatches = Regex.Execute(a) If regMatches.Count > 0 Then arrA(1) = regMatches(0).SubMatches(0) * 1 arrA(2) = regMatches(0).SubMatches(1) arrSamm(2, lngZ) = arrA 'für die spätere Ausgabe in den Nachbarzellen End If b = arrSamm(1, lngZ + 1) arrB(1) = 0: arrB(2) = "" Set regMatches = Regex.Execute(b) If regMatches.Count > 0 Then arrB(1) = regMatches(0).SubMatches(0) * 1 arrB(2) = regMatches(0).SubMatches(1) arrSamm(2, lngZ + 1) = arrB 'für die spätere Ausgabe in den Nachbarzellen End If If arrB(1) < arrA(1) Then 'die nächste Zahl ist kleiner als die aktuelle: arrSamm(1, lngZ) = b: arrSamm(2, lngZ) = arrB arrSamm(1, lngZ + 1) = a: arrSamm(2, lngZ + 1) = arrA bolSortiert = False ElseIf arrA(1) = arrB(1) Then 'nächste Zahl ist wie die aktuelle, mögliche Strings prüfen If arrB(2) < arrA(2) Then arrSamm(1, lngZ) = b: arrSamm(2, lngZ) = arrB arrSamm(1, lngZ + 1) = a: arrSamm(2, lngZ + 1) = arrA bolSortiert = False End If End If Next Loop While bolSortiert = False For lngZ = lngEZ To lngLZ 'Ausgabe Cells(lngZ, 1) = arrSamm(1, lngZ) arrTemp = arrSamm(2, lngZ) Cells(lngZ, 2) = arrTemp(1) Cells(lngZ, 3) = arrTemp(2) Next End Sub

Und schon benötigt das Ganze nur noch einen Bruchteil der bisherigen Laufzeit.


Beispieldatei

Diese Beispiele sind in der Beispieldatei enthalten: alphanum_sort.xlsm.

Beachten Sie aber, dass die Routine SortierenEinfach() bei den erweiterten Daten eine Fehlermeldung bringt, denn diese Routine funktioniert nur auf der Basis der in der Aufgabenstellung vorgegebenen Syntax der Daten ZahlBuchstabe. In der Fehlermeldung wird auch angezeigt, an welchem String diese Routine scheitert.

Im Beispiel ist das Problem der Vergleich der Zeile 5 (103b) mit der nächsten Zeile (,55aaa). Die Syntax mit vorangestelltem Komma (was in Excel durchaus möglich ist) und mehreren Buchstaben nach der Zahl kann diese einfache Routine nicht. Die anderen kommen damit klar.

Download: alphanum_sort.xlsm

Verzeichnis auslesen (Makro und Funktion)Makro/Sub/ProzedurUDF - benutzerdefinierte Funktion

Kategorien: Dateien und Ordner ▸ Ordner und Dateien und Ordner ▸ Dateien

(Tipp 32) Nachricht zum Beitrag an Autor Nach oben

Wie kann man mit VBA ein Verzeichnis mit allen darin befindlichen Dateien auslesen?

An der Stelle des Sternchens in Dir(strOrdner & "*.*") können auch bestimmte Dateiendungen eingetragen wreden, so dass nur nach Dateien eines Typs gesucht wird.

Subs

Sollen Dateien nur direkt im Ordner, nicht aber in Unterordnern, gesucht werden, reicht dieser Code:

Sub Suchen_nur_Dateien() Dim strOrdner As String, strDatei As String Dim lngZ As LongPtr strOrdner = "C:\Eigene Dateien\" lngZ = 2 Range("a1:e50000").ClearContents strDatei = Dir(strOrdner & "*.*") Do While strDatei <> "" If strDatei <> "" Then lngZ = lngZ + 1 Cells(lngZ, 1) = strOrdner & strDatei 'Pfad Cells(lngZ, 2) = FileLen(strOrdner & strDatei) 'Größe Cells(lngZ, 3) = FileDateTime(strOrdner & strDatei) 'Datum/Zeit Cells(lngZ, 4) = strDatei 'nur Dateiname End If strDatei = Dir Loop End Sub

Soll auch in Unterordnern gesucht werden, ist dies eine Möglichkeit:

Private lngZ As LongPtr Sub Suchen_mit_Unterordnern() 'Aufruf lngZ = 2 Range("a1:e50000").ClearContents Dateisuche "C:\Eigene Dateien", "*.*" End Sub Sub Dateisuche(strOrdner As String, strDateien As String) Dim strTemp As String, strWdhlg As String strOrdner = strOrdner & IIf(Right(strOrdner, 1) <> "\", "\", "") strTemp = Dir(strOrdner & strDateien) Do While Len(strTemp) Cells(lngZ, 1) = strOrdner & strTemp 'Pfad Cells(lngZ, 2) = FileLen(strOrdner & strTemp) 'Größe Cells(lngZ, 3) = FileDateTime(strOrdner & strTemp) 'Datum/Zeit Cells(lngZ, 4) = strTemp 'nur Dateiname lngZ = lngZ + 1 strTemp = Dir() Loop strTemp = Dir(strOrdner, vbDirectory) Do While Len(strTemp) If (strTemp <> ".") And (strTemp <> "..") Then If (GetAttr(strOrdner & strTemp) And vbDirectory) = vbDirectory Then Dateisuche strOrdner & strTemp, strDateien lngZ = lngZ - 1 strWdhlg = Dir(strOrdner, vbDirectory) lngZ = lngZ + 1 Do While strWdhlg <> strTemp: strWdhlg = Dir(): Loop End If End If strTemp = Dir() Loop On Error GoTo 0 End Sub


Matrixfunktion für dynamische Arrayformel

Wenn es nicht zu viele Dateien sind, können die auch mit einer Formel, die in einer Zelle steht, ausgegeben werden (ab Excel 365). Dazu dient folgende benutzerdefinierte Funktion (UDF):

Function Suchen_nur_Dateien(strOrdner) Dim strDatei As String, arrTemp() Dim lngArr As LongPtr lngArr = 0 strDatei = Dir(strOrdner & "*.*") Do While strDatei <> "" If strDatei <> "" Then lngArr = lngArr + 1 ReDim Preserve arrTemp(1 To 4, 1 To lngArr) arrTemp(1, lngArr) = strOrdner & strDatei 'Pfad arrTemp(2, lngArr) = FileLen(strOrdner & strDatei) 'Größe arrTemp(3, lngArr) = FileDateTime(strOrdner & strDatei) 'Datum/Zeit arrTemp(4, lngArr) = strDatei 'nur Dateiname End If strDatei = Dir Loop Suchen_nur_Dateien = arrTemp End Function

Wenn in A1 der Ordner (z. B. C:\Eigene Dateien\) steht, kann in eine andere Zelle folgende Formel eingetragen werden:

=MTRANS(Suchen_nur_Dateien(A1))

Damit werden die gefundenen Dateien ab der Zelle mit der Formel gefloatet eingetragen (verschüttet). Auch die Suche mit den Unterordnern lässt sich in dieser Form gestalten - aber Vorsicht, damit die Formel nicht ewig zum Berechnen braucht.