Software für Sie.

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.

Basis: In Spalte A (im Code also 1) stehen aufsteigend sortiert und ohne leere Zellen dazwischen die Zeiten.

Zum Umsetzen der Aufgabenstellung benötigen wir vier einzelne Routinen. Diesen müssen wir zwei Variablen zur Verfügung stellen, die deshalb ganz am Anfang des Moduls deklariert werden müssen:

Private lngZ As LongPtr Private bolC As Boolean

In der ersten Routine Start() löschen wir die Zellinhalte neben den Uhrzeiten und legen in lngZ die erste Zeile mit einer Uhrzeit fest. Dazu setzen wir bolC auf True. Das Makro, das später die Eintragungen vornimmt, wird diese Variable prüfen und den Vorgang nur fortsetzen, wenn diese auf True gesetzt ist. Anschlie�end starten wir den Vorgang mit der Sub ZeitFestLegen():

Sub Start() lngZ = 2 'erste Zeile mit Uhrzeit Range(Cells(lngZ, 2), Cells(Rows.Count, 3)).ClearContents bolC = True ZeitFestLegen End Sub

Die Sub ZeitFestLegen() hat nur folgende Aufgaben:

• prüfen, ob in Spalte A der Zeile lngZ eine Zeit steht,
• prüfen, ob bolC noch True ist,
• wenn zweimal ja: festlegen, dass bei der Zeit in Zeile lngZ etwas passiert (das Makro Eintragen ausführen) - zunächst ist das hier die Zeit in Zeile 2, weil wir die bereits in lngZ festgelegt haben:

Sub ZeitFestLegen() Dim datZeitAngabe As Date If Cells(lngZ, 1).Value = "" Then Exit Sub If bolC = False Then MsgBox "Makro wurde angehalten" Exit Sub End If datZeitAngabe = Cells(lngZ, 1).Value Application.OnTime datZeitAngabe, "Eintragen" End Sub

Nun wei� der Code, wann etwas passieren soll, nämlich bei der ersten Zeit. Mit der Sub Eintragen() legen wir fest, was passieren soll. Hier werden einfache Texte in die Nachbarzellen eingetragen.

Nach dem Eintragen wird die Zeile lngZ um 1 erhöht und wieder die Sub ZeitFestLegen aufgerufen, die nun dafür sorgt, dass das Ganze bei der nun in der nächsten Zeile gefundenen Zeit von vorn losgeht - wenn eine Zeit in dieser neuen Zelle steht und bolC immer noch True ist:

Sub Eintragen() Cells(lngZ, 2).Value = "Hallo, Spalte B" Cells(lngZ, 3).Value = "Hallo, Spalte C" lngZ = lngZ + 1 ZeitFestLegen End Sub

Das sollte schon mal funktionieren. Aber: Es gibt für den Anwender keine Möglichkeit, das vorzeitig abzubrechen. Bei der nächsten Zeit wird eingetragen und sofort die neue Zeit festgelegt.

Deshalb sollten wir eine Möglichkeit einbauen, den Wert der Variablen bolC auf False zu setzen, damit die Sub ZeitFestLegen den Vorgang abbricht. Dazu reicht das Stück Code, das man vielleicht an einen Button o. ä. binden kann:

Sub Stoppen() bolC = False End Sub

Damit wird die Datei einfach geöffnet, wenn sie existiert:

Workbooks.Open FileName:="C:\Eigene Dateien\Test.xlsx"

Sicherheitshalber sollte vorher geprüft werden, ob die Datei existiert, um Fehlermeldungen zu vermeiden:

Dim strPfad As String strPfad = "C:\Temp\Test.xlsx" If Dir(strPfad) = "" Then MsgBox "Die Datei " & vbNewLine & strPfad & vbNewLine & "existiert nicht.", vbOKOnly + vbCritical, "Fehler" Else Workbooks.Open Filename:=strPfad End If

Ggf. sollte noch geprüft werden, ob eine Datei mit dem Namen bereits offen ist. Dazu kann man den Fehler abfangen oder vorher mit For Each objMappe in Workbooks prüfen, ob objMappe.name wie der Name der aufzurufenden Datei ist.

Dateiname in Zelle

Wenn der Dateiname in einer Zelle (hier A1) steht, kann so vorgegangen werden:

Sub Aufruf() Workbooks.Open FileName:=Range("A1") End Sub

Dabei darf natürlich nicht der Ordnerpfad vergessen werden, in dem sich diese Datei befindet. Der kann komplett mit in A1 stehen oder noch vorangestellt werden.

Auch hier sollte vorher noch mit Dir() geprüft werden, ob die Datei überhaupt existiert.

Voraussetzung für dieses Beispiel ist, dass auf dem Server ein eigenes PHP-Script erstellt werden kann, das dann natürlich auch eine Adresse (URL) hat.

In diesem Beispiel sind die hochzuladenden Dateien als Konstanten angegeben:

Private Const strDatei1 As String = "C:\â?¦\test1.jpg" Private Const strDatei2 As String = "C:\â?¦\test2.jpg" Private Const strDatei3 As String = "C:\â?¦\test3.jpg"

Natürlich können die - wie ebenfalls die Script-URL - dem Script auch dynamisch übergeben werden, zum Beispiel als Array. Anpassungen müssen nur an der Sub DateiUpload() vorgenommen werden, der Rest kann so bleiben.

Dies ist der VBA-Code:

Private Const strURL As String = "https://â?¦Ihre URLâ?¦/â?¦Ihre PHP-Dateiâ?¦.php" Private Const strDatei1 As String = "C:\â?¦\test1.jpg" Private Const strDatei2 As String = "C:\â?¦\test2.jpg" Private Const strDatei3 As String = "C:\â?¦\test3.jpg" Sub DateiUpload() Dim strPostDaten As String, strBoundary As String strBoundary = "---------------------------7da24f2e50046" strPostDaten = "" strPostDaten = strPostDaten & Header_Datei(strDatei1, strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei2, strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei3, strBoundary) strPostDaten = strPostDaten & "--" & strBoundary & "--" With CreateObject("Microsoft.XMLHTTP") .Open "POST", strURL, False .SetRequestHeader "Content-Type", "multipart/form-data; boundary=" & strBoundary .Send SendDaten(strPostDaten) MsgBox .ResponseText End With End Sub Function Header_Datei(strDateiPfad As String, strBoundary As String) Dim strDateiKonv As String, strDateiName As String Header_Datei = "" If strDateiPfad <> "" Then strDateiName = DateiName(strDateiPfad) strDateiKonv = DateiKonv(strDateiPfad) Header_Datei = "--" & strBoundary & vbCrLf & _ "Content-Disposition: form-data; name=""file[]""; filename=""" & strDateiName & """" & vbCrLf & _ "Content-Type: application/octet-stream" & vbCrLf & vbCrLf & strDateiKonv & vbCrLf End If End Function Function DateiName(strpfad As String) DateiName = "" If strpfad <> "" Then DateiName = Mid$(strpfad, InStrRev(strpfad, "\") + 1) End Function Function DateiKonv(strpfad As String) Dim lngDNr As LongPtr, bytBuffer() As Byte DateiKonv = "" lngDNr = FreeFile Open strpfad For Binary Access Read As lngDNr If LOF(lngDNr) > 0 Then ReDim bytBuffer(0 To LOF(lngDNr) - 1) As Byte Get lngDNr, , bytBuffer DateiKonv = StrConv(bytBuffer, vbUnicode) End If Close lngDNr End Function Private Function SendDaten(strText As String) As Byte() SendDaten = StrConv(strText, vbFromUnicode) End Function

Der VBA-Code bereitet die Dateien für den Upload vor, erstellt die richtige Syntax und sendet sie an das PHP-Script:

// Verzeichnis, in dem die Anhänge zwischengespeichert werden: $uploadverz = 'uploadtest/'; // Vorsicht: Ggf. prüfen, ob vorhanden und leer. // File[]-Array sinnvoll sortieren: $dateiarray = array(); foreach($_FILES['file'] as $k1 => $v1) { foreach($v1 as $k2 => $v2) { $dateiarray[$k2][$k1] = $v2; } } // Anhänge speichern, Daten für Versand aufbereiten $a = 0; $ausg = ""; foreach ($dateiarray as $file) { // Ggf. prüfen, ob Dateiname schon im Verzeichnis vorhanden ist und // ggf. umbenennen: $datname = basename($file['name']); // Vorbereitung, falls Dateiname im Verzeichnis existiert: $t = explode(".", $datname); // an Dateiendung splitten $endung = $t[count($t)-1]; $datname_pref = substr($datname, 0, strlen($datname) - strlen($endung) - 1); $uploadpfad = $uploadverz . $datname; // Prüfen, ob Datei existiert: $n = 0; while (file_exists($uploadpfad)) { $n++; $uploadpfad = $uploadverz . $datname_pref . "(" . $n . ")." . $endung; } if (move_uploaded_file($file['tmp_name'], $uploadpfad)) { $a++; $ausg .= $uploadpfad . "\n"; } } // Meldung, die in VBA in der MsgBox als .ResponseText erscheint: echo $a . " Datei(en) erfolgreich hochgeladen." . "\n\n" . $ausg;

Dazu gibt es verschiedene Möglichkeiten. Manche haben Nachteile, zum Beispiel Probleme mit UTF-8 oder dass gesendete Daten in den Server-Logs erscheinen.

Empfehlenswert ist das MSXML2.XMLHTTP-Objekt, mit dem per POST Formulardaten ganz einfach gesendet und Antworten empfangen werden können. Die Daten werden einfach als key=value-Kombinationen an die Adresse des Server-Scripts (Beispiel: PHP) übergeben. Ein einfaches Beispiel dazu:

Sub Post_mit_Response() Dim strURL As String, strPostDaten As String strURL = "https://example.org/phptest.php" strPostDaten = "" strPostDaten = strPostDaten & "name=Müller" strPostDaten = strPostDaten & "&vorname=Hans" strPostDaten = strPostDaten & "&strasse=Blumenweg 22" strPostDaten = strPostDaten & "&ort=Musterort" With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) If .ResponseText <> "" Then 'Response je nach Anforderung prüfen MsgBox .ResponseText 'Antwort des Servers ausgeben Else MsgBox "Schiefgegangen.", vbOKOnly + vbCritical, "Fehler" End If End With End Sub

Ein Beispiel für ein zugehöriges PHP-Script:

$adresse = "Die Adresse ist:\n\n"; $adresse .= $_POST['vorname'] . " " . $_POST['name'] . "\n"; $adresse .= $_POST['strasse'] . "\n"; $adresse .= $_POST['plz'] . " " . $_POST['ort'] . "\n"; echo $adresse;

In diesem Beispiel würde PHP aus den einzelnen Angaben eine formatierte Adresse gestalten und diese an VBA senden.

Dadurch können u. a. Daten aus Zellen oder Userformen ganz einfach per Mausklick, Tastendruck oder auch per Event an ein Script auf dem Server gesendet werden, das dann die weitere Verarbeitung erledigt. Somit können Mails versendet oder Eintragungen in Datenbanken (z. B. mySQL) vorgenommen werden usw.

Die Antwort des Servers ist hier natürlich nur reiner Text. Für komplexere Vorgänge sollten Sie sich ansehen, was in der Zusammenarbeit von VBA mit JSON möglich ist.

Download: excel_php_json.xlsm

Die einfachste Variante ist:

Workbooks("Test.xlsm").SendMail Recipients:="beispiel@example.org", Subject:="Hallo"

Komfortabler geht es jedoch mit dem RoutingSlip-Objekt:

Sub Mailen() Workbooks("Test.xlsm").HasRoutingSlip = True With Workbooks("Test.xlsm").RoutingSlip .Recipients = "beispiel@example.org" .Subject = "Hallo" .Message = "Schöne Grü�e" End With Workbooks("Test.xlsm").Route End Sub

Soll die Mail an mehrere Empfänger gesendet werden, mu� die Recipients-Eigenschaft als Array gesetzt werden:

Array("...", "...",........)

Mit Aufrufen, die Outlook nutzen, gibt es jedoch immer wieder Probleme. Eine mögliche (und eigentlich gute) Ursache ist, dass Microsoft Spam vermeiden möchte und so eine Meldung einblendet, die auf den automatisierten Mailversand hinweist:

Eine Anwendung versucht, Nachrichten in Ihrem Auftrag zu versenden. Falls dies unerwartet geschieht, klicken Sie auf 'Verweigern'. �berprüfen Sie au�erdem, ob Sie über die aktuelle Version Ihres Antivirusprogramms verfügen.

Das ist sicher gut gemeint, aber bei Automatisierungen eben auch sehr störend.

Deshalb kann die �berlegung günstig sein, ob automatisierter Mailversand nicht besser von Outlook entkoppelt und unabhängig davon gestaltet werden kann. Wenn Sie die Möglichkeit haben, ein PHP-Script auf einem Server zu speichern, sollten Sie sich dazu den Tipp Mails mit VBA und PHP versenden (mehrere Empfänger, mehrere Anhänge) ansehen.

Mit Dialogfeld ist es übrigens so möglich:

Application.Dialogs(xlDialogSendMail).Show arg1:="beispiel@example.org", arg2:="Das ist der Betreff."

Die Beispiele zeigen, wie die letzte ausgefüllte Zelle zu ermitteln ist. In jedem Fall muss dann nur Zeile oder Spalte um 1 erhöht werden, um auf die erste leere Zelle zu kommen.

VBA

Grundsätzlich:

Mit Cells(Rows.Count, 1).End(xlUp).Row können wir abfragen, welche die letzte ausgefüllte Zelle in Spalte A (also 1) ist.

Brauchen wir die letzte ausgefüllte Zelle in einer Zeile, also die Spaltenangabe dieser Zelle, können wir Cells(1, Columns.Count).End(xlToLeft).Column verwenden (hier für Zeile 1).

Diese beiden Codestücke funktionieren auch zuverlässig, wenn nicht gerade die erste Zelle in der Spalte bzw. in der Zeile leer ist. In dem Fall würde auch die 1 zurückgegeben - was hie�e, dass diese erste Zelle Inhalt hätte. Aus diesem Grund muss, wenn dies eine Rolle spielen kann, vorher geprüft werden, ob die erste Zelle der Spalte/Zeile auch leer ist. Damit wird dies berücksichtigt:

Dim strAddr As String 'Spalte A, entspricht 1: If IsEmpty(Cells(1, 1)) Then strAddr = Cells(1, 1).Address Else strAddr = Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Address End If Range(strAddr).Value = "Erste leere Zelle"

Stehen in der ersten Zeile grundsätzlich Spaltenüberschriften, spielt das bei der ersten ausgefüllten Zelle in einer Spalte natürlich keine Rolle.

Das Eintragen erfolgt dann einfach in die nächste Zelle. Entweder:

Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Select

oder:

a = Cells(Rows.Count, 2).End(xlUp).Row + 1

Entsprechend ist es auch bei der ersten leeren Zelle in einer Zeile:

a = Cells(2, Columns.Count).End(xlToLeft).Column + 1

Wenn etwas in die erste freie Zelle in einer Zeile kopiert werden soll, kann man z. B. so vorgehen:

Sub NaechstfreieSpalte() Selection.Copy Destination:=Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1) End Sub

Formel

Mit der Funktion ADRESSE() in Verbindung mit ANZAHL2() kann die letzte ausgefüllte Zelle ermittelt werden, wenn sich keine leeren Zellen im Bereich befinden:

=ADRESSE(ANZAHL2(B1:B34)+2;2;4)

Das +2 bedeutet, dass der zu prüfende Bereich erst in B3 beginnt.

Oder:

=INDIREKT("B"&ANZAHL2(B2:B10000)+1)

Das +1 hei�t, dass der erste Wert in B in Zeile 2 steht. Steht der erste Wert in Zeile 1, kann es weggelassen werden. Steht der erste Wert aber in Zeile 3, wäre es +2 usw.

Eine andere Variante, die aber nur bis Spalte Z funktioniert, ist:

=LINKS(ADRESSE(ZEILE($B$6);SPALTE($B$6));3)&ANZAHL2($B$3:$B$65536)+ZEILE($B$3)-1

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.

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

In Zeiten zunehmender Vernetzung spielt natürlich auch das Json-Format eine gro�e Rolle, zunehmend auch für Excel. Das Senden und Empfangen der Daten ist kein Problem, während die Analyse dieser Daten mit VBA vergleichsweise umständlich ist. Einfache Möglichkeiten, wie beispielsweise mit jQuery, geben die Bordmittel von VBA nicht her.

Wesentlich vereinfacht wird dies jedoch mit dem JsonConverter von Tim Hall, den es hier zum Download gibt: VBA-tools/VBA-JSON. Zur Installation müssen (alles für Windows) nur zwei Schritte erfolgen:

• Modul JsonConverter.bas in das VBA-Projekt einfügen
• im Editor unter Extras ▸ Verweise ein Häkchen bei Microsoft Scripting Runtime setzen

Damit steht schon die Funktion JsonConverter.ParseJson() zur Verfügung.

Eine Datenebene

Das Verwenden soll hier zunächst an einem einfachen Beispiel gezeigt werden. Dazu nutzen wir dieses PHP-Script, das einen Adressdatensatz als Json-Objekt zur Verfügung stellt, den wir mit VBA abrufen:

$a['vorname'] = "Max"; $a['nachname'] = "Mütze"; $a['strasse'] = "Mützenweg 55"; $a['plz'] = "01234"; $a['ort'] = "Mützenhausen"; echo json_encode($a);

Zunächst rufen wir das Ganze vom Server ab:

strURL = "https://example.org/jsonabfrage.php" strPostDaten = "" 'Hier käme etwas rein, wenn was an den Server übermittelt werden sollte With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten)

Damit empfangen wir .ResponseText. Zum Vermeiden von Fehlern durch den JsonConverter ist hier schon wichtig, diese Rückgabe zu prüfen. Im einfachsten Fall mit If InStr(1, .ResponseText, "{") > 0 Then.

In .ResponseText steckt nun dieses Objekt:

Das weisen wir mit der Funktion JsonConverter.ParseJson(.ResponseText) einer Variablen zu, die als Dictionary-Objekt deklariert ist:

Set dicParsed = JsonConverter.ParseJson(.ResponseText)

In der Variablen sind nun die einzelnen Einträge aus dem ResponseText als Key=Value-Paare enthalten. Da die Variable dicParsed ein Objekt ist, verfügt sie über verschiedene Eigenschaften, mit denen wir arbeiten können. So können wir z. B. einfach eine Schleife über die Keys laufen lassen und haben somit deren Werte:

strAusgabe = strAusgabe & dicParsed.Count & " Einträge:" & vbNewLine & vbNewLine For Each varWert In dicParsed.Keys strAusgabe = strAusgabe & varWert & ": " & dicParsed(varWert) & vbNewLine Next varWert MsgBox strAusgabe

Das komplette VBA-Script zum Testen:

Sub Post_JSONAusgabe_1() Dim strURL As String, strPostDaten As String Dim dicParsed As Dictionary Dim varWert As Variant, strAusgabe As String strURL = "https://example.org/jsonabfrage.php" strPostDaten = "" 'Hier käme etwas rein, wenn was an den Server übermittelt werden sollte With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) 'Die Antwort muss geprüft werden, da sonst ein Fehler beim JsonConverter auftritt. 'Hier einfach auf das Vorhandensein der geschweiften Klammer: If InStr(1, .ResponseText, "{") > 0 Then strAusgabe = "" Set dicParsed = JsonConverter.ParseJson(.ResponseText) strAusgabe = strAusgabe & dicParsed.Count & " Einträge:" & vbNewLine & vbNewLine For Each varWert In dicParsed.Keys strAusgabe = strAusgabe & varWert & ": " & dicParsed(varWert) & vbNewLine Next varWert MsgBox strAusgabe Else MsgBox "Schiefgegangen: " & .ResponseText, vbOKOnly + vbCritical, "Fehler" End If End With End Sub

Mehrere Datenebenen

Häufig haben wir es jedoch mit Verschachtelungen zu tun, dass also in einem Array-Element ein weiterer Array (bzw. in einem Json-Element ein weiteres Json-Objekt) steckt. im vorigen Beispiel waren alle Elemente auf einer Ebene, aber genau so könnten die Elemente insgesamt ein Wert eines übergeordneten Elements sein:

• values
  • vorname="Max"
  • nachname="Mütze"
  • strasse="Mützenweg 55"
  • plz="01234"
  • ort="Mützenhausen"

Wir hätten hier also den Key â??valuesâ??, dem als Wert ein weiterer Array, bestehend aus Key-Value-Paaren, zugeordnet ist. Ein Beispiel, wie dies mit PHP möglich wäre:

$a['vorname'] = "Max"; $a['nachname'] = "Mütze"; $a['strasse'] = "Mützenweg 55"; $a['plz'] = "01234"; $a['ort'] = "Mützenhausen"; $b['values'] = $a; echo json_encode($b);

In VBA würde somit dieses Json-Objekt ankommen:

Zum Dekodieren verwenden wir wieder die bekannte Funktion und weisen das Ganze der Dictionary-Variablen zu:

Set dicParsed = JsonConverter.ParseJson(.ResponseText)

Wenn wir nun an die relevanten Inhalte wollen, müssen wir beachten, dass die als Array (bzw. genauer als Dictionary) im übergeordneten Schlüssel â??valuesâ?? stecken. Das heiÃ?t, dass wir den ansprechen müssen. Wir durchlaufen also die einzelnen Elemente in dicParsed("values"):

'Hier werden nur die Einträge aus â??valuesâ?? verwendet, also das, was in PHP in $a steckt: For Each varWert In dicParsed("values").Keys strAusgabe = strAusgabe & varWert & ": " & dicParsed("values")(varWert) & vbNewLine Next varWert

Hier ist das gesamte Script zum Testen, zum Schluss ergänzt um ein paar Ausgaben zur Verdeutlichung:

Sub Post_JSONAusgabe_2() 'PHP: 'Beachten: Hier gibt es eine Verschachtelung. 'Im Array $a sind die eigentlichen Daten enthalten. '$a['vorname'] = "Max"; '$a['nachname'] = "Mütze"; '$a['strasse'] = "Mützenweg 55"; '$a['plz'] = "01234"; '$a['ort'] = "Mützenhausen"; 'Zuletzt wird $a jedoch dem Array $b zugewiesen, hier dem Key â??valuesâ??: '$b['values'] = $a; 'Im Json ist also diese Verschachtelung auch enthalten und wird so ausgegeben: 'echo json_encode($b); Dim strURL As String, strPostDaten As String Dim dicParsed As Dictionary Dim varWert As Variant, strAusgabe As String strURL = "https://example.org/jsonabfrage.php" strPostDaten = "" With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) If .ResponseText <> "" Then 'Anpassen, auf Fehler prüfen! strAusgabe = "" 'dicParsed ist der komplette(!) verschachtelte Array: Set dicParsed = JsonConverter.ParseJson(.ResponseText) 'Hier werden nur die Einträge aus â??valuesâ?? verwendet, also das, was in PHP in $a steckt: For Each varWert In dicParsed("values").Keys strAusgabe = strAusgabe & varWert & ": " & dicParsed("values")(varWert) & vbNewLine Next varWert MsgBox "In PHP in $a enthalten:" & vbNewLine & vbNewLine & strAusgabe 'Nun wird der Array um einen Eintrag erweitert (entspräche in PHP $a['alter'] = 32;): 'dicParsed("values")("alter") = 32 dicParsed("values").Add "alter", 32 strAusgabe = "Das gesamte Json-Objekt ist nun:" & vbNewLine & vbNewLine strAusgabe = strAusgabe & JsonConverter.ConvertToJson(dicParsed) 'komplettes Json-Objekt strAusgabe = strAusgabe & vbNewLine & vbNewLine strAusgabe = strAusgabe & "während im Key â??valuesâ?? (ehemals $b) nur das enthalten ist:" & vbNewLine & vbNewLine strAusgabe = strAusgabe & JsonConverter.ConvertToJson(dicParsed("values")) 'Inhalt nur von "values" MsgBox strAusgabe Else MsgBox "Schiefgegangen.", vbOKOnly + vbCritical, "Fehler" End If End With End Sub

Download: excel_php_json.xlsm

Die Aufgabenstellung, per Mausklick oder automatisiert Mails zu versenden, ist sehr weit verbreitet. Es gibt auch viele Tipps, wie das mit Outlook erfolgen kann. Jedoch gibt es immer wieder Probleme, sei es durch Rechtebeschränkungen, wegzuklickende Meldungen von Outlook usw.

Am sinnvollsten ist deshalb eine Lösung, mit der man weitestgehend unabhängig von Officeprogrammen ist. Hier besteht die Möglichkeit, VBA nur zum Versand der Maildaten an einen Server zu benötigen, der dann den Rest des Erstellens und Versands der Mail übernimmt.

Voraussetzung für dieses Beispiel ist, dass auf dem Server ein eigenes PHP-Script erstellt werden kann, das dann natürlich auch eine Adresse (URL) hat.

Basis ist der Beitrag Dateiupload. Der VBA-Code dazu wurde hier um die Möglichkeit, Text zu versenden, erweitert. Prinzipiell trifft hier aber das Gleiche zu - vor allem, dass die oben verwendeten Konstanten auch dynamisch verwendet werden können. Hier wird nur das Prinzip aufgezeigt, die Anpassungen müssen dann entsprechend der eigenen Gegebenheiten erfolgen. Dies betrifft insbesondere auch die Sicherheit. Denken Sie daran, die manuell zu ändernden Daten vorher zu prüfen. Diese Scripte sind auch leicht zum Versand von Massenspam zu verwenden.

Dies ist der VBA-Code, der um die Text-Funktion erweitert wurde:

Private Const strURL As String = "https://�Ihre URL�/�Ihre PHP-Datei�.php" Private Const strDatei1 As String = "C:\�\test1.jpg" Private Const strDatei2 As String = "C:\�\test2.jpg" Private Const strDatei3 As String = "C:\�\test3.jpg" Private Const strMailEmpfaenger As String = "info1@example.org;info2@example.org;info3@example.org" Private Const strAbsText As String = "Max Mütze" Private Const strAbsMail As String = "xyz@example.org" Private Const strBoundary As String = "---------------------------7da24f2e50046" Sub DateiUploadUndMail() Dim strPostDaten As String strPostDaten = "" strPostDaten = strPostDaten & Header_Text("empfaenger", strMailEmpfaenger, "text/plain", strBoundary) strPostDaten = strPostDaten & Header_Text("abstext", strAbsText, "text/plain", strBoundary) strPostDaten = strPostDaten & Header_Text("absmail", strAbsMail, "text/plain", strBoundary) strPostDaten = strPostDaten & Header_Text("betreff", "Das ist der Betreff.", "text/plain", strBoundary) strPostDaten = strPostDaten & Header_Text("mailtext", "Hallo!" & vbNewLine & vbNewLine & "Ich bin der Mailtext.", "text/html", strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei1, strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei2, strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei3, strBoundary) strPostDaten = strPostDaten & "--" & strBoundary & "--" With CreateObject("Microsoft.XMLHTTP") .Open "POST", strURL, False .SetRequestHeader "Content-Type", "multipart/form-data; boundary=" & strBoundary .Send SendDaten(strPostDaten) MsgBox .ResponseText End With End Sub Function Header_Text(strKey As String, strValue As String, strMime As String, strBoundary) Header_Text = "--" & strBoundary & vbCrLf & _ "Content-Disposition: form-data; name=""" & strKey & """;" & vbCrLf & _ "Content-Type: " & strMime & "; charset=UTF-8" & vbCrLf & vbCrLf & strValue & vbCrLf End Function Function Header_Datei(strDateiPfad As String, strBoundary As String) Dim strDateiKonv As String, strDateiName As String Header_Datei = "" If strDateiPfad <> "" Then strDateiName = DateiName(strDateiPfad) strDateiKonv = DateiKonv(strDateiPfad) Header_Datei = "--" & strBoundary & vbCrLf & _ "Content-Disposition: form-data; name=""file[]""; filename=""" & strDateiName & """" & vbCrLf & _ "Content-Type: application/octet-stream" & vbCrLf & vbCrLf & strDateiKonv & vbCrLf End If End Function Function DateiName(strpfad As String) DateiName = "" If strpfad <> "" Then DateiName = Mid$(strpfad, InStrRev(strpfad, "\") + 1) End Function Function DateiKonv(strpfad As String) Dim lngDNr As LongPtr, bytBuffer() As Byte DateiKonv = "" lngDNr = FreeFile Open strpfad For Binary Access Read As lngDNr If LOF(lngDNr) > 0 Then ReDim bytBuffer(0 To LOF(lngDNr) - 1) As Byte Get lngDNr, , bytBuffer DateiKonv = StrConv(bytBuffer, vbUnicode) End If Close lngDNr End Function Private Function SendDaten(strText As String) As Byte() SendDaten = StrConv(strText, vbFromUnicode) End Function

Damit werden die Daten an das PHP-Script gesendet, das diese empfängt und daraus die Mails erstellt sowie diese versendet. Auch hier unbedingt daran denken, die Daten noch auf Gültigkeit zu prüfen!

Das komplette PHP-Script:

/* Wichtig: Da das Script prinzipiell auch zu Versand von Massenspam geeignet ist, sollten ganz am Anfang noch Kennwort und Nutzername (oder eine andere Absicherung) abgefragt werden. Diese Angaben können von VBA aus ganz einfach als POST-Paare mitgeschickt werden. */ // Verzeichnis, in dem die Anhänge zwischengespeichert werden: $anhangverz = 'uploadtest/'; // Vorsicht: Ggf. prüfen, ob vorhanden und leer. // Mailadressen durch Semikolon getrennt $empfstrings = $_POST['empfaenger']; // Text, der als Absender erscheint: $abstext = $_POST['abstext']; // Mailadresse des Absenders: $absmail = $_POST['absmail']; $betreff = utf8_encode($_POST['betreff']); $mailtext = utf8_encode($_POST['mailtext']); // File[]-Array sinnvoll sortieren: $dateiarray = array(); foreach($_FILES['file'] as $k1 => $v1) { foreach($v1 as $k2 => $v2) { $dateiarray[$k2][$k1] = $v2; } } // Anhänge speichern, Daten für Versand aufbereiten $anhangarray = array(); $a = 0; foreach ($dateiarray as $file) { // Ggf. prüfen, ob Dateiname schon im Verzeichnis vorhanden ist und // ggf. umbenennen: $datname = basename($file['name']); $uploadpfad = $anhangverz . $datname; if (move_uploaded_file($file['tmp_name'], $uploadpfad)) { $a++; $anhangarray[$a]['pfad'] = $uploadpfad; $anhangarray[$a]['dateiname'] = $datname; $anhangarray[$a]['groesse'] = filesize($uploadpfad); $dateiinhalt = fread(fopen($uploadpfad, "r"), filesize($uploadpfad)); $anhangarray[$a]['data'] = chunk_split(base64_encode($dateiinhalt)); } } $empf_array = explode(";",$empfstrings); $encoding = mb_detect_encoding($mailtext, "utf-8, iso-8859-1, cp-1252"); $mime_boundary = "-----=" . md5(uniqid(microtime(), true)); $versendet = 0; // Mail an jeden Empfänger schicken: foreach($empf_array as $einzelmail) { if (trim($einzelmail) != "") { $header ="From:" . $abstext . "<" . $absmail . ">\n"; $header.= "MIME-Version: 1.0\r\n"; $header.= "Content-Type: multipart/mixed;\r\n"; $header.= " boundary=\"" . $mime_boundary . "\"\r\n"; $content = "This is a multi-part message in MIME format.\r\n\r\n"; $content.= "--".$mime_boundary."\r\n"; $content.= "Content-Type: text/plain; charset=\"$encoding\"\r\n"; $content.= "Content-Transfer-Encoding: 8bit\r\n\r\n"; $content.= $mailtext . "\r\n"; if ($a > 0) { // Ggf. Mailanhänge anfügen for ( $n = 1; $n <= $a; $n++) { $content.= "--" . $mime_boundary . "\r\n"; $content.= "Content-Disposition: attachment;\r\n"; $content.= "\tfilename=\"" . $anhangarray[$n]['dateiname'] . "\";\r\n"; $content.= "Content-Length: " . $anhangarray[$n]['groesse'] . ";\r\n"; $content.= "Content-Type: application/pdf; name=\"" . $anhangarray[$n]['dateiname'] . "\"\r\n"; $content.= "Content-Transfer-Encoding: base64\r\n\r\n"; $content.= $anhangarray[$n]['data'] . "\r\n"; } } $content .= "--" . $mime_boundary . "--"; if (mail($einzelmail, $betreff, $content, $header)) { $versendet++; } } } // Ggf. Mailanhänge löschen if ($a > 0) { for ( $n = 1; $n <= $a; $n++) { if ($anhangarray[$n]['pfad'] != "") {unlink($anhangarray[$n]['pfad']);} } } // Meldung, die in VBA in der MsgBox als .ResponseText erscheint: echo $versendet . " Mail(s) erfolgreich gesendet.";

Makros werden in der Regel in der Mappe gespeichert, in der sie benötigt werden. Aber es gibt auch Makros, die immer zur Verfügung stehen sollen, die also sofort beim Aufruf von Excel bereit sein sollen.

Häufig liest man hierzu die Empfehlung, man solle die Makros in der sogenannten Persönlichen Makroarbeitsmappe speichern. Diese Methode hat jedoch gravierende Nachteile. Besser ist es, wenn man sich Add-Ins erstellt und diese über den Add-Ins-Manager einblendet.

Erstellen eines Add-Ins

1. Zuerst erstellt man ganz normal seine Makros/VBA-Routinen, indem man sie aufzeichnet oder selbst schreibt (siehe hierzu auch Wo gibt man nun die Makros ein?).
2. Anschlie�end wählt man in Excel in der Mappe, in der sich die Makros befinden, den Befehl Datei - Speichern unter und gibt einen aussagekräftigen Dateinamen ein.
3. Dann wählt man im Dialogfeld ganz unten den Dateityp Microsoft Excel-Add-In (*.xlam). Daraufhin wechselt Excel automatisch in den Pfad, in dem sich standardmä�ig die Add-Ins befinden - man kann den Ordner auch wechseln. Hauptsache ist natürlich, dass man später wei�, wohin man gespeichert hat.
4. Mit einem Klick auf Speichern steht das Add-In sofort zur Verfügung. Excel speichert es und ergänzt den Namen mit der Endung .xlam.

Nun ist das Add-In gespeichert und steht zur Verfügung, arbeiten kann man damit aber noch nicht. Um das Add-In zu aktivieren, geht man wie folgt vor:

Einbinden des Add-Ins

Aktuell

1. Datei ▸ Optionen
2. Add-Ins
3. Verwalten: Excel-Add-Ins ▸ Los
4. Durchsuchen
5. Add-In-Datei suchen
6. OK
7. Prüfen, dass das Add-In in der Liste enthalten und das Häkchen gesetzt ist
8. OK

Ab Excel 2007

1. Office-Schaltfläche (oben links)
2. Excel-Optionen
3. Add-Ins
4. Verwalten: Add-Ins > Gehe zu ...
5. Durchsuchen
6. Add-In-Datei suchen
7. OK
8. Prüfen, dass das Add-In in der Liste enthalten und das Häkchen gesetzt ist
9. OK

Frühere Excel-Versionen

1. Extras
2. Add-Ins-Manager
3. Durchsuchen
4. Add-In-Datei suchen
5. OK
6. Prüfen, dass das Add-In in der Liste enthalten und das Häkchen gesetzt ist
7. OK

Fertig - nun stehen alle Makros dieses Add-Ins immer zur Verfügung, auch, wenn man Excel beendet und neu startet.

Nachträgliches Bearbeiten der Makros

Möchte man die Makros nachträglich ändern oder ergänzen, ist dies auch kein Problem. Man wechselt mit der Tastenkombination Alt + F11 in den VBA-Editor, in dem man nun (standardmä�ig) links oben im Projektexplorer den Namen des Add-Ins findet. Doppelklickt man darauf, werden die einzelnen Elemente (Tabellen, Module, usw.) sichtbar und durch einen Doppelklick auf das entsprechende Element sieht man den Code der/des Makros bereits vor sich und kann ihn bearbeiten. Nicht vergessen, zu speichern!

Add-In in normale Excel-Datei umwandeln

Normalerweise sieht man das Add-In nur im VBA-Editor, nicht aber in Excel. Manchmal möchte man aber aus dem Add-In wieder eine "normale" Mappe erstellen, damit man die Tabellen in Excel bearbeiten kann. Das ist auch kein Problem. Man gibt dazu einfach irgendwo diesen Code ein und führt ihn aus. Wechselt man nun nach Excel, hat man als Mappe das Add-In vor sich und kann wie in einer normalen Excelmappe arbeiten:

Sub Test() Workbooks("Name_des_Add-ins.xla").Isaddin = False End Sub

personl.xls

Auch mit einer Datei mit dem Namen PERSONAL.XLSB kann man Makros allgemein verfügbar machen. Dazu wählt man folgende Schritte:

1. Menü Extras - Makro - Aufzeichnen
2. Namen des Makros eingeben
3. Bei Makro speichern in wählen: Persönliche Makroarbeitsmappe
4. Schritte durchführen, die das Makro später ausführen soll
5. Aufzeichnung beenden
6. Excel beenden, Frage nach Speichern mit Ja beantworten
7. Excel neu starten

Nun ist die Datei PERSONAL.XLSB automatisch gespeichert und wird bei jedem Excelstart mitgeöffnet. Sie kann auch bearbeitet werden, indem man mit Alt + F11 in den Editor wechselt.

Auch wenn oft empfohlen wird, zentrale Makros in dieser Datei zu speichern, rate ich davon ab. Im Unterschied zu einem Add-In ist sie eine Mappe, die beim Excelstart mit aufgerufen wird und kann über das Menü Fenster - Einblenden eingeblendet werden. Daraus könnten sich Probleme ergeben, wenn mit der Workbooks-Auflistung gearbeitet wird. So wirkt sich z. B. folgender Code auch auf die PERSONAL.XLSB aus:

For each x in Workbooks ... Next

Auf ein Add-In wirkt es sich nicht aus.

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

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

Je nachdem, was den Gegebenheiten am besten entspricht:

=WENN(UND(ISTZAHL(A1);ISTZAHL(A2));A1+A2;"")

=WENN(A1="";"";WENN(A2="";"";A1+A2))

=WENN(A1+A2>0;A1+A2;"")

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:

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.

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:

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.