Software für Sie.

Am einfachsten und sichersten ist es natürlich, im relevanten Code das enstprechende Blatt direkt anzusprechen:

Sheets("xxx").irgendwas

Soll geprüft werden, ob es sich beim aktiven Blatt um das Blatt XXX handelt, ist dies möglich:

If ActiveSheet.Name = "XXX" Then ...

In PHP kann man sehr übersichtlich mit assoziativen Arrays arbeiten, wie zum Beispiel

$MeinArray['Vorname'] = 'Manfred';

In Excel geht das auch:

Setzen Sie im VBA-Editor unter Extras ▸ Verweise ein Häkchen vor Microsoft Scripting Runtime oder verwenden Sie Late Binding wie in den folgenden Beispielen. Nun können Sie wie folgt mit assoziativen Arrays arbeiten:

Basis ist das Dictionary oder die Collection. Dabei spielen immer Paare eine Rolle: Key=Item, also ein Schlüsselname und der Eintrag dazu. In den folgenden Beispielen wird der Einfachheit halber (und weil Collection ein paar Nachteile hat) nur das Dictionary verwendet.

Im Code sind hier die einzelnen Inhalte (Keys, Items) zum besseren Verständnis hardcodiert, also fest eingetragen. An dieser Stelle können natürlich auch Variablen oder Zellen verwendet werden, zum Beispiel Spalten- oder Zeilenüberschriften und Zellen mit Daten.

Eine Ebene

Im Beispiel sind die Key=Item-Paare auf einer Ebene. Das heiÃ?t, es darf auch keine zwei gleichnamigen Keys geben, sonst wird eine Fehlermeldung ausgegeben.

Sub AssozArray_Einfach() Dim dict, varKey, varKeys, varItems Dim intI As Integer Set dict = CreateObject("Scripting.Dictionary") dict.Add "name", "Müller" dict.Add "vorname", "Johann" dict.Add "wohnort", "Berlin" MsgBox dict("vorname") & " " & dict("name") & " wohnt in " & vbNewLine & dict("wohnort") & "." 'For Each varKey In dict.keys ' MsgBox varKey & vbNewLine & dict(varKey) 'Next varKeys = dict.Keys varItems = dict.Items For intI = 0 To dict.Count - 1 MsgBox varKeys(intI) & ": " & vbNewLine & varItems(intI) Next Set dict = Nothing End Sub

Die MsgBox zeigt, wie die Syntax Dictionary(Key) verwendet werden kann, um auf die einzelnen Elemente zuzugreifen. Darunter sind zwei Schleifen. Mit ihnen wird demonstriert, wie alle Elemente des Dictionarys ausgegeben werden können.

Verschachtelungen, mehrere Ebenen

Elemente von Arrays können wiederum Arrays sein. Dies geht auch bei den assoziativen Arrays bzw. Dictionarys in VBA - auch hier kann verschachtelt werden. Dabei trifft genau das Prinzip der Paare zu, nur dass an der Stelle des Items eine weitere Sammlung (Dictionary oder Collection) ist. Hier wird ein temporäres Dictionary verwendet, das vor jeder Verschachtelung neu erzeugt und danach wieder auf Nothing gesetzt wird. Vorbelegen oder �berschreiben der Items in den einzelnen Schritten funktioniert nicht, da sonst nur auf die letzten Einträge referenziert wird.

Wir wollen im Beispiel ein Dictionary aus sechs Personen erzeugen, das sind also sechs verschiedene Keys. Diesen sechs Personen können nun Details (Nachname, Vorname, Wohnort) zugeordnet werden, müssen aber nicht. So kann der Platz für eine Person auch nur reserviert sein, ohne dass Details enthalten sind.

Die Personen insgesamt sind die Einträge in dicPersonen mit den Keys Person x. Werden Details zugeordnet, erfolgt das Speichern zunächst im temporären Dictionary dictDetails, das dann als Item der Person zugeordnet wird. Gibt es keine Details, wird als Item einfach ein String verwendet.

Sub AssozArray_Levels() Dim dicPersonen, dictDetails, varKey, varKey1 Dim strAusg As String Set dicPersonen = CreateObject("Scripting.Dictionary") Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Müller" dictDetails.Add "vorname", "Klaus" dictDetails.Add "wohnort", "Berlin" dicPersonen.Add "Person 1", dictDetails Set dictDetails = Nothing Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Beispielfrau" dictDetails.Add "vorname", "Bärbel" dictDetails.Add "wohnort", "Hamburg" dicPersonen.Add "Person 2", dictDetails Set dictDetails = Nothing Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Mustermann" dictDetails.Add "vorname", "Franz" dictDetails.Add "wohnort", "Leipzig" dicPersonen.Add "Person 3", dictDetails Set dictDetails = Nothing dicPersonen.Add "Person 4", "Nicht vergeben." Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Sonne" dictDetails.Add "vorname", "Klara" dictDetails.Add "wohnort", "München" dicPersonen.Add "Person 5", dictDetails Set dictDetails = Nothing dicPersonen.Add "Person 6", "Auch nicht vergeben." MsgBox "Testausgabe: " & vbNewLine & dicPersonen("Person 1")("wohnort") & vbNewLine & dicPersonen("Person 2")("wohnort") strAusg = "" For Each varKey In dicPersonen.keys strAusg = strAusg & vbNewLine & "Person: " & varKey & vbNewLine On Error Resume Next For Each varKey1 In dicPersonen(varKey).keys strAusg = strAusg & "key: " & varKey1 & vbTab & "item: " & dicPersonen(varKey)(varKey1) & vbNewLine Next If Err.Number = 92 Then strAusg = strAusg & vbTab & vbTab & "item: " & dicPersonen(varKey) & vbNewLine Err.Clear End If Next MsgBox strAusg Set dicPersonen = Nothing End Sub

In der MsgBox mit der Testausgabe ist zu sehen, wie die Items ausgegeben werden können. Wir haben hier also nicht nur den Key einer Ebene, sondern hier wird mit Klammerpaaren bis zur unteren Ebene gearbeitet.

Schwieriger ist es mit der kompletten Ausgabe per Schleife. Hätten wir konsequent bei jeder Person eine Verschachtelung mit den Details, würde so etwas reichen:

For Each varKey In dicPersonen.keys strAusg = strAusg & vbNewLine & "Person: " & varKey & vbNewLine For Each varKey1 In dicPersonen(varKey).keys strAusg = strAusg & "key: " & varKey1 & vbTab & "item: " & dicPersonen(varKey)(varKey1) & vbNewLine Next Next

Da aber zwischendurch unverschachtelte Items direkt auf der ersten Ebene sind (Person 4 + 6), käme es hier zu einer Fehlermeldung, dem Fehler 92. Der wird abgefangen und separat behandelt; hier wird der Eintrag direkt ausgegeben. Der Schlüssel, also der Key, ist hier ja die oberste Ebene, die Person selbst.

Dynamische Arrayformel

Zum Verdeutlichen wird hier die zweite Variante als Array ausgegeben, so dass sie als Formel in einer Zelle verwendet werden kann (funktioniert ab Excel 365).

In die Zelle kann einfach =AssozArray_Array() eingegeben werden; als Ergebnis müssten die Key-Item-Paare zu jeder Person erscheinen.

Zum praktischen Einsatz ist dies sicher eher weniger geeignet, da dann eine andere Datenstruktur erforderlich wäre. Sollte das gebraucht werden, lässt sich aber der Array, der erstellt wird, einfach anpassen.

Function AssozArray_Array() Dim dicPersonen, dictDetails, varKey, varKey1 Dim strAusg As String Dim arrRet(1 To 14, 1 To 3), lngRet As LongPtr, intS As Integer Set dicPersonen = CreateObject("Scripting.Dictionary") Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Müller" dictDetails.Add "vorname", "Klaus" dictDetails.Add "wohnort", "Berlin" dicPersonen.Add "Person 1", dictDetails Set dictDetails = Nothing Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Beispielfrau" dictDetails.Add "vorname", "Bärbel" dictDetails.Add "wohnort", "Hamburg" dicPersonen.Add "Person 2", dictDetails Set dictDetails = Nothing Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Mustermann" dictDetails.Add "vorname", "Franz" dictDetails.Add "wohnort", "Leipzig" dicPersonen.Add "Person 3", dictDetails Set dictDetails = Nothing dicPersonen.Add "Person 4", "Nicht vergeben." Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Sonne" dictDetails.Add "vorname", "Klara" dictDetails.Add "wohnort", "München" dicPersonen.Add "Person 5", dictDetails Set dictDetails = Nothing dicPersonen.Add "Person 6", "Auch nicht vergeben." lngRet = 0 For Each varKey In dicPersonen.keys On Error Resume Next For Each varKey1 In dicPersonen(varKey).keys If varKey1 <> "" Then lngRet = lngRet + 1 arrRet(lngRet, 1) = varKey arrRet(lngRet, 2) = varKey1 arrRet(lngRet, 3) = dicPersonen(varKey)(varKey1) End If Next If Err.Number = 92 Then If dicPersonen(varKey) <> "" Then lngRet = lngRet + 1 arrRet(lngRet, 1) = varKey arrRet(lngRet, 2) = "" arrRet(lngRet, 3) = dicPersonen(varKey) End If Err.Clear End If Next Set dicPersonen = Nothing AssozArray_Array = arrRet End Function

Website-Tipp

Hier gibt es gute Erklärungen von Paul Kelly (englisch): Excel VBA Dictionary â?? A Complete Guide

Zuerst drehen wir in der aktiven Mappe auf dem Blatt Tabelle1 den Text:

Worksheets("Tabelle1").Rows("1:1").Orientation = 90

Dann drehen wir in der xyz.xls, die natürlich offen sein muss, den Text:

Workbooks("xyz.xls").Worksheets("Tabelle1").Rows("1:1").Orientation = 90

Und jetzt weisen wir an, dass A1 von der Tabelle1 der aktiven Mappe in die xyz.xls in die Tabelle2 in A1 kopiert werden soll (in einer Zeile):

Sheets("Tabelle1").Range("A1").Copy _ Workbooks("xyz.xls").Worksheets("Tabelle2").Range("A1")

Das geht alles ohne Select und Activate.

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.

Zum Sortieren der Blätter gibt es verschiedene Möglichkeiten, was auch etwas von den Rahmenbedingungen abhängig ist. Was soll zum Beispiel mit Blättern passieren, deren Namen nicht der Syntax der zu sortierenden Blattnamen entsprechen? Hier sind drei Beispiele, die ggf. noch angepasst werden müssen.

In dem Fall werden unterwegs (also nicht am Anfang) Blätter mit Namen, die nicht der Syntax entsprechen, nach hinten verschoben - ansonsten wird so lange sortiert, bis die Zahl im folgenden Blattnamen nicht mehr gö�er ist:

Sub BlaetterSortieren() Dim bolSortiert As Boolean Dim intBlatt As Integer, intBlatt1 As Integer Dim varAkt, varNaechst bolSortiert = False Do While bolSortiert = False bolSortiert = True For intBlatt = 1 To Sheets.Count varAkt = Replace(Sheets(intBlatt).Name, "Tab", "") If IsNumeric(varAkt) Then For intBlatt1 = intBlatt To Sheets.Count varNaechst = Replace(Sheets(intBlatt1).Name, "Tab", "") If IsNumeric(varNaechst) Then If varNaechst * 1 < varAkt * 1 Then Sheets(intBlatt1).Move Before:=Sheets(intBlatt) bolSortiert = False End If Else Sheets(intBlatt1).Move after:=Sheets(Sheets.Count) End If Next End If Next Loop End Sub

Ein Beispiel mit Sprungmarken, Reihenfolge Tab1, Tab2, Tab11:

Sub Blattsort() Dim intAnzahl As Integer, intN As Integer, intM As Integer, intI As Integer, intZahlM As Integer, intZahlN As Integer Dim WS As Worksheet intAnzahl = ActiveWorkbook.Worksheets.Count For intM = 1 To intAnzahl For intN = intM To intAnzahl On Error Resume Next For intI = 1 To Len(Worksheets(intN).Name) If IsNumeric(Right(Worksheets(intN).Name, intI)) = False Then intI = intI - 1 If intI = 0 Then GoTo TEXT intZahlN = Right(Worksheets(intN).Name, intI) Exit For End If Next intI For intI = 1 To Len(Worksheets(intM).Name) If IsNumeric(Right(Worksheets(intM).Name, intI)) = False Then intI = intI - 1 If intI = 0 Then GoTo TEXT intZahlM = Right(Worksheets(intM).Name, intI) Exit For End If Next intI If CInt(intZahlN) < CInt(intZahlM) Then Worksheets(intN).Move Before:=Worksheets(intM) GoTo NAECHSTE TEXT: If Worksheets(intN).Name < Worksheets(intM).Name Then Worksheets(intN).Move Before:=Worksheets(intM) NAECHSTE: Next intN Next intM MsgBox "Anzahl der Tabellen: " & intAnzahl End Sub

Ein kurzes Beispiel, aber die Reihenfolge ist Tab1, Tab11, Tab2:

Sub Blattsort1() Dim intX As Integer Dim bolY As Boolean Do bolY = True For intX = 1 To Sheets.Count - 1 If Sheets(intX).Name > Sheets(intX + 1).Name Then Sheets(intX + 1).Move Before:=Sheets(intX) bolY = False End If Next intX Loop Until bolY = True End Sub

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;

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

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

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

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