Split Funktion aus VBA
Die Split Funktion wird sehr häufig angewandt wenn es darum geht einen bestimmten Text (String) zu zerlegen.
Die Syntax (der Aufbau) dieser Funktion ist:
Die einzelnen Bereiche einer Funktion nennt man Aufruf und Parameter. Der Aufruf ist SPLIT und die einzelnen Parameter, die die Funktion “füttern” werden hintereinander durch Komma getrennt aufgeführt. Dabei sind Parameter mit einer runden Klammer notwendig (mandatory) und Parameter mit einer eckigen Klammer nicht notwendig (optional)
- Der Teil, der “gesplittet” werden soll, muss ein Text (String) sein (Expression as String).
- Als nächstes definiert man das “Trennzeichen”, an dem der Text getrennt werden soll (Delimiter)
Dieser Teil ist optional, das heisst, man kann ihn weglassen. In diesem Fall, wird automatisch das Leerzeichen als Trennzeichen genommen - Der nächste Parameter bestimmt die Grenze, ab der der Text gesplittet werden soll. gibt man keinen Wert an, wird der gesamte Text ab Position 1 des Textes betrachtet.
Jede Zahl würde bestimmen, ab wo der Splitprozess beginnen soll. - Der letzte Parameter ist ein Vergleichsparameter (compare), der entweder auf “binary” oder “Text” Level vergleicht. Wenn man nichts angibt, dann wird auf “Binary” verglichen.
Am besten eignet sich die Split Funktion bei immer wiederkehrenden Export Dateien wie z.B. Reports aus SAP oder bei CSV Dateien. Die Split Funktion zerlegt den Text in einzelne Datenfelder die über einen Index angesprochen werden können.
Ein einfaches Beispiel für eine Split Funktion:
sText = "Das ist ein Beispieltext"
split_sText = split(sText) 'wie ihr seht, ohne jegliche Parameter
'Das Ergebnis in split_sText lautet:
split_sText(0) = "Das"
split_sText(1) = "ist"
split_sText(2) = "ein"
split_sText(3) = "Beispieltext"
'Unser Funktionsaufruf von oben kann man so:
split_sText = split(sText)
'oder so schreiben:
split_sText = split(sText, " ")
'Wenn wir unseren Text so ausgeben lassen würden, wie ihn Meister Joda aus Starwars ausprechen würde, dann können wir die einzelnen Datenfelder folgendermaßen wieder zu einem neuen Satz zusammenfügen.
sJodaText = split_sText(2) & " " & split_sText(3) & " " & split_sText(0) & " " & split_sText(1)
sJodaText = "ein Beispieltext Das ist"
Einige Informationen vorab, die es euch erleichtert, die folgenden Beispiele besser zu verstehen.
Die Split Funktion erzeugt IMMER ein ein-dimensionales Array. Das hört sich jetzt schwieriger an als es ist. Die Bereiche, in die ein Text zerlegt wird, kann man direkt ansprechen. So ist das erste Feld, also der erste Bereich immer das Feld 0, das nächste Feld 1 usw. Das heisst, um das erste Feld anzusprechen, kann ich die 0 wählen und um das letzte Feld anzusprechen, brauche ich die Anzahl der zerlegten Felder. In unserem Beispiel von oben, wäre das die 3. Unter VBA kann man das erste und letzte Feld eines Arrays (eines Datenbereichs) mit Hilfe von LBound und UBound ermitteln. Wie das geht zeigen wir jetzt:
LBound ermittelt das erste Datenfeld (low bound = untere Grenze)
UBound ermittelt das letzte Datenfeld (upper bound = obere Grenze)
Wir listen hier einige Beispiel aus realen Prozeduren auf, mit weiteren Erläuterungen.
Beispiel: Den Buchstaben der Spalte einer Zelle ermitteln
Buchstabe = Split(Cells(1, 3).Address, "$")(1) 'Spaltenbuchstabe ermitteln
'In diesem Fall suchen wir den Spaltenbuchstabe der Zelle Cells(1,3)
'Mit Hilfe von .Address ermittel wir die "Adresse" dieser Zelle = "$C$1"
'Wenn wir diesen String ("$C$1") jetzt "zerlegen" und als Trennzeichen "$" benutzen, dann wird uns die Splitfunktion 3 Felder ausgeben.
Das erste Feld ist immer LINKS vom ersten Trennzeichen, jedes weitere Feld dann analog dazu.
Und weil das Ergebnis einer Splitfunktion immer ein eindimensionales Array ist, beginnt dieses immer mit 0 als erstes Datenfeld. Außer man benutzt gleich am Anfang des jeweiligen Moduls den Parameter “Option Base 1”, dann starten alle Arrays, die eigentlich 0 als erstes Datenfeld haben mit 1 als erstem Datenfeld. Aber das sind speziellere Anforderung, die uns hier nicht kümmern.
Zurück zu unserem Beispiel.
Buchstabe = Split(Cells(1, 3).Address, "$")(1) 'Spaltenbuchstabe ermitteln
Ergibt:
Beispiel: Den Dateinamen eines UNC-Pfades oder generell eines Strings mit einer Pfadangabe ermitteln:
Sub get_filename()
'Pfad und Dateiname einer Stringvariablen zuordnen
sPath_Filename = "c:UsersDefaultDesktopbild.jpg"
'Diese Stringvariable mit Hilfe der String Funktion zerlegen
'mit dem Trennzeichen ""
split_sPath_Filename = Split(sPath_Filename, "")
'Die Obergrenze der zerlegten Felder ermitteln
iObergrenze = UBound(split_sPath_Filename)
'Den Dateinamen ermitteln
sFilename = split_sPath_Filename(iObergrenze)
'Das Ganze kann man auch in einem Rutsch erledigen
sFilename = Split(sPath_Filename, "")(UBound(Split(sPath_Filename, "")))
End Sub
Beispiel: CSV Dateien auslesen und einen bestimmten Bereich auslesen:
Eigentlich der Klassiker, wenn es darum geht Texte oder Textzeilen zu zerlegen, weil CSV Dateien zu 99% einzelne Felder mit einem Semikolon trennt.
Ich beschreibe kurz, wie man mit einfachen VBA Mitteln eine Datei einliest und Zeile für Zeile durchgeht. Bei diesem zeilenweisen Durchgang, “schnappen” wir uns dann einen bestimmten Feldbereich und speichern diesen ab.
Es gibt zwei grundsätzliche Herangehensweisen, eine Datei auszulesen.
- Datei öffnen, den Inhalt mit einem Schwung in eine Variable Speichern und die Datei wieder schließen. Dann mit dieser Variablen weiter arbeiten.
- Die Datei öffnen und während sie geöffnet ist, zeilenweise durchgehen, sich die Informationen holen und anschliessend die Datei wieder schließen.
Ihr müsst selber entscheiden, wie ihr vorgehen wollt, ich zeige Euch beide Ansätze. Voraussetzung für beide Fälle ist:
Unsere Testdatei liegt im Order C:temp und heisst testDatei.csv
Sie hat folgenden Inhalt:
Artikel-Nr.;Artikelbezeichnung;Verbrauch;Lager;
Art123;Erster Artikel;35446;10880;
Art234;Zweiter Artikel;40389;5598;
Art567;Dritter Artikel;38588;10071;
Art890;Vierter Artikel;70243;24895;
Wenn wir nun aus dieser Datei den Lagerbestand zusammenrechnen wollten, dann könnten wir das folgendermaßen machen:
- Datei öffnen, auslesen und wieder schließen
- alle Zeilen einzeln in ein Array einlesen (mit Hilfe der Split Funktion)
- durch das Array “wandern” und uns mit Hilfe der Split Funktion den Lagerbestand ausgeben und summieren
Wie wir oben ja schon gelernt haben, würde die erste Daten-Zeile (ohne die Überschriftenzeile), wenn wir sie mit der Split Funktion einlesen würden folgendermaßen “gesplittet” werden:
Sub datei_auslesen()
'Dateipfad und Name
sPfadDateiname = "C:temptestDatei.csv"
'hier wird die Funktion mit dem Parameter aufgerufen und der Inhalt zurückgegeben
sDateiInhalt = getFileContent(sPfadDateiname)
'der Dateiinhalt muss als erstes so gesplittet werden, dass die einzelnen Zeilen in ein Splitarray geschrieben werden
'deshalb splitten wir den kompletten Dateiinhalt mit vbCrLf (carriage return und line feed)
split_sDateiInhalt = Split(sDateiInhalt, vbCrLf)
'um durch die einzelen Zeilen durchgehen zu können "loopen" wir uns durch das eindimensionale Splitarray
'i2 dient nur als Zählvariable
lLagerbestand = 0
For i2 = LBound(split_sDateiInhalt) + 1 To UBound(split_sDateiInhalt) '+ 1 damit die Überschrift ausgeschlossen wird
If split_sDateiInhalt(i2) <> "" Then 'damit "leere" Zeilen (hier Zeile 6) ausgeschlossen werden
lLagerbestand = lLagerbestand + Split(split_sDateiInhalt(i2), ";")(3)
End If
Next i2
End Sub
Function getFileContent(sPfadDateiname)
'ermitteln einer freien Dateinummer
fFile = FreeFile
Open sPfadDateiname For Input As #fFile
sDateiInhalt = Input(LOF(1), #fFile)
Close #fFile
getFileContent = sDateiInhalt
End Function
Beim zeilenweise Auslesen einer Datei ändern wir die Funktion “getFileContent” folgendermaßen:
Function getFileContent(sPfadDateiname)
'ermitteln einer freien Dateinummer
fFile = FreeFile
Open sPfadDateiname For Input As #fFile
Do Until EOF(fFile)
Line Input #fFile, strLine
If strLine <> "" And InStr(1, strLine, "Lager") = 0 Then 'damit "leere" Zeilen und die Kopfzeile ausgeschlossen werden
lLagerbestand = lLagerbestand * 1 + Split(strLine, ";")(3) * 1 '*1 damit die Ausgabe des Stringwertes in eine Zahl gewandelt wird, zum Addieren
End If
Loop
Close #fFile
End Function
