Überlegungen und Vorgeschichte
Wie bei vielen anderen Menschen auch, gehört die Verwendung von Microsoft Excel bei mir zum Arbeitsalltag genauso dazu wie die morgentliche Tasse Kaffee oder das Online-Meeting via Microsoft Teams. Dabei bin ich in der Vergangenheit bei vielen meiner Listen an den Punkt gekommen, an welchem ich die aktuelle oder auch andere Exceldateien nach bestimmten Werten durchsuchen musste, oder dem Nutzer die Möglichkeit geben wollte dies zu tun. Jetzt bietet VBA* mit der Range.find Methode* schon eine recht gute Funktion zum Suchen nach bestimmten Werten an, doch musste ich hiermit entweder „das Grundgerüst“ für den Suchaufruf immer wieder neu schreiben und an die aktuelle Datei anpassen, oder den Code aus einer anderen Liste „recyclen“, wofür aber ebenfalls immer Anpassungen nötig waren.
Um mir diese Arbeit ein wenig zu erleichtern, kam mir die Idee eine Funktionsbibliothek oder Klasse zu programmieren, mit deren Hilfe ich die gewünschten Funktionen einfach in ein anderes Projekt, oder eine neue Liste importieren kann. Das Ergebnis ist die hier folgende SLSS SearchClass, in welcher ich initial erst einmal die für mich wichtigsten Funktionen umgesetzt habe.
Die folgenden Absätze geben einen tieferen Einblick in die Nutzungsmöglichkeiten und den Funktionsumfang der Klasse und sollten daher nicht als Schritt für Schritt Anleitung angesehen werden. Für die meisten Leute sollte sich die Anwendung der Klassenfunktionen eigentlich relativ leicht aus der im Downloadordner vorhanden Beispielsammlung (SLSS_SearchClass_X.X.X.X.xlsm) herauslesen lassen!
KOMMENTAR DES AUTORS
Funktionsumfang
Die Klasse stellt verschiedene Suchmethoden zur Verfügung, mit deren Hilfe unterschiedliche Suchanfragen abgearbeitet werden können. Der Unterschied liegt hierbei in den zurückgelieferten Werten. Die Rückgabe ist ein Variant aus einzelnen Variants (Array), welche bis auf wenige Ausnahmen immer die folgenden Werte beinhalten:
Zusammensetzung des Rückgabewertes
- Position [0]: Name der Arbeitsmappe in welchem das Objekt gefunden wurde.
- Position [1]: Name des Tabellenblattes in welchem das Objekt gefunden wurde
- Position [2]: Die Zelladresse an welcher sich das gefundene Objekt befindet
Beispielaufbau eines Returns mit 3 Funden: [ [Mappe, Blatt, Fundadresse] , [Mappe, Blatt, Fundadresse] , [Mappe, Blatt, Fundadresse] ]
Aktuell wurden folgende Rückgabefunktionen umgesetzt
- Rückgabe des ersten Fundes innerhalb der aktuellen / einer ausgewählten Datei
- Rückgabe aller Funde aus der aktuellen / einer ausgewählten Datei
- Rückgabe einer abweichenden Position ausgehend von der aktuellen Fundstelle (Offset-Position)
- Rückgabe des ersten Fundes innerhalb mehrerer ausgewählter Dateien
- Rückgabe aller Funde aus mehreren ausgewählten Dateien
- Rückgabe der Werte eines partiellen Zeilenbereiches / der kompletten Zeile des Fundes innerhalb der aktuellen / einer ausgewählten Datei*
- Rückgabe der Werte eines partiellen Zeilenbereiches / der kompletten Zeile des Fundes aus mehreren ausgewählten Dateien*
- Durchsuchen eines bestimmten Tabellenblattes
- Durchsuchen aller vorhandenen Tabellenblätter
- Farbiges Markieren der Fundstellen innerhalb eines Tabellenblattes / eines Bereiches innerhalb des Tabellenblattes
* abweichender Rückgabetyp: [ [Wert1_Spalte1, Wert1_Spalte2, Wert1_Spalte3] , [Wert2_Spalte1, Wert2_Spalte2, Wert2_Spalte3] ]
Eine Sammlung an Beispielaufrufen zu den unterstützten Suchfunktionen befindet sich innerhalb der VBA-Module in der Datei SLSS_SearchClass_X.X.X.X.xlsm, welche Bestandteil des Download-Verzeichnisses ist.
Klassendiagramm und Übersicht über die öffentlichen Klassenmethoden
Das Klassendiagramm und eine Übersicht über alle öffentlich verfügbaren Klassenmethoden habe ich in der folgenden Tabelle noch einmal zusammengestellt. Da die Liste an Funktionen und damit auch an verfügbaren Methoden mittlerweile recht lang ist, habe ich sie zur Verbesserung der Übersichtlichkeit minimiert. Für Interessierte kann die Liste durch Klicken auf den folgenden Banner angezeigt werden
Klassendiagramm
öffentliche Methoden zur Verwendung der SLSS SearchClass
| getFinding(searchString, caseSens) Parameter searchString: String caseSens: Boolean = True (optional) Rückgabewert Variant [ [Workbookname, Blattname, Zelladresse des Fundes] ] | Mit dieser Methode kann die aktuelle oder die zuletzt übergebene Datei- / Arbeitsblattkombination nach dem unter searchString eingegebenen Wert durchsucht werden. Der optionale Parameter caseSens ist eine Flag-Variable, ob die Groß- und Kleinschreibung bei der Suche berücksichtigt werden soll. True oder kein Wert angegeben führt die Suche mit, False hingegen ohne Beachtung der Groß- und Kleinschreibung durch! Wird der gesuchte Wert gefunden, wird die Suche in diesem Tabellenblatt abgebrochen, was dazu führt, dass immer nur der erste Fund zurückgegeben wird. Sind weitere Tabellenblätter vorhanden und wurde die Methode setAllSheets() aufgerufen, so wird die Suche im nachfolgenden Tabellenblatt fortgesetzt. |
| getFindingByOffset(searchString, offsetX, offsetY, caseSens) Parameter searchString: String offsetX: signed Integer offsetY: signed Integer caseSens: Boolean = True (optional) Rückgabewert Variant [ [Workbookname, Blattname, Zelladresse des Fundes] ] | Mit dieser Methode wird ebenfalls nach dem unter searchString eingegebenen Wert gesucht, doch wird hier nicht die Fundstelle selbst, sondern die Adresse welche über die Offset-Variablen beeinflusst wurde zurückgeliefert. So ist es zum Beispiel möglich auf andere Spalten / Reihen ausgehend von der Fundstelle zuzugreifen. Der optionale Parameter caseSens ist hier ebenfalls die Flag-Variable für die Beachtung der Groß- und Kleinschreibung! Wird der gesuchte Wert gefunden, wird die Suche in diesem Tabellenblatt abgebrochen, was dazu führt, dass immer nur der erste Fund zurückgegeben wird. Sind weitere Tabellenblätter vorhanden und wurde die Methode setAllSheets() aufgerufen, so wird die Suche im nachfolgenden Tabellenblatt fortgesetzt. |
| getFindList(searchString, caseSens, returnMode) Parameter searchString: String caseSens: Boolean = True (optional) returnMode: Integer = 0 (optional & nicht benötigt) Rückgabewert Variant [ [Workbookname, Blattname, Zelladresse des Fundes] , [Workbookname, Blattname, Zelladresse des Fundes] , … ] | Mit dieser Methode wird ebenfalls in der zuletzt übergebene Datei- / Arbeitsblattkombination nach dem unter searchString eingegebenen Wert gesucht. Der optionale Parameter caseSens ist hier ebenfalls die Flag-Variable für die Beachtung der Groß- und Kleinschreibung! Im Gegensatz zu getFinding() wird die Suche jedoch nicht nach dem ersten Fund abgebrochen, sondern das Tabellenblatt bis zum Ende nach weiteren Übereinstimmungen durchsucht. Werden weitere Übereinstimmungen gefunden, so werden diese ebenfalls dem Rückgabearray (Variant) hinzugefügt. |
| getFindListByOffset(searchString, offsetX, offsetY, caseSens) Parameter searchString: String offsetX: signed Integer offsetY: signed Integer caseSens: Boolean = True (optional) Rückgabewert Variant [ [Workbookname, Blattname, Zelladresse des Fundes] , [Workbookname, Blattname, Zelladresse des Fundes] , … ] | Mit dieser Methode wird ebenfalls nach dem unter searchString eingegebenen Wert gesucht, doch wird hier nicht die Fundstelle selbst, sondern die Adresse welche über die Offset-Variablen beeinflusst wurde zurückgeliefert. So ist es zum Beispiel möglich auf andere Spalten / Reihen ausgehend von der Fundstelle zuzugreifen. Der optionale Parameter caseSens ist hier ebenfalls die Flag-Variable für die Beachtung der Groß- und Kleinschreibung! Im Gegensatz zu getFindingByOffset() wird die Suche jedoch nicht nach dem ersten Fund abgebrochen, sondern das Tabellenblatt bis zum Ende nach weiteren Übereinstimmungen durchsucht. Werden weitere Übereinstimmungen gefunden, so werden diese ebenfalls dem Rückgabearray (Variant) hinzugefügt. |
| getRowValue(searchString, startAfterColPos, colRange, caseSens) Parameter searchString: String startAfterColPos: Integer = 0 (optional) colRange: Integer = 0 (optional) caseSens: Boolean = True (optional) Rückgabewert Variant [ [Wert1_Spalte1, Wert1_Spalte2, Wert1_Spalte3] , [Wert2_Spalte1, Wert2_Spalte2, Wert2_Spalte3] , … ] | Mit dieser Methode wird ebenfalls nach dem unter searchString eingegebenen Wert gesucht, doch wird hier nicht die Adresse der Fundstelle, sondern die Werte der Spalten der Fundreihe zurückgeliefert. Mit den optionalen Parametern startAfterColPos und colRange kann man die Startposition (ausgehend von der ersten Spalte) und die Anzahl der Spalten beeinflussen, um nur partielle Reihen zurückgeliefert zu bekommen. Lässt man diese Werte leer, so wird hingegen die komplette Dateireihe zurückgeliefert. Der optionale Parameter caseSens ist hier ebenfalls die Flag-Variable für die Beachtung der Groß- und Kleinschreibung! Die Suche wird bei dieser Methode nach dem ersten Fund einer Übereinstimmung fortgesetzt! |
| getAllFindings(searchString, caseSens) Parameter searchString: String caseSens: Boolean = True (optional) Rückgabewert Variant [ [Workbookname, Blattname, Zelladresse des Fundes] ] | Diese Methode funktioniert genauso wie die bereits oben aufgeführte Methode getFindings(), jedoch wird hier das Durchsuchen mehrerer Arbeitsmappen unterstützt. Diese müssen vorab durch den Aufruf von setWorkbook() der Klasse bekanntgemacht werden. Sind diese nicht geöffnet, so werden sie für das Durchsuchen geöffnet und anschließend durch den Aufruf der Methode doCleanUp() wieder geschlossen |
| getAllFindList(searchString, caseSens, returnMode) Parameter searchString: String caseSens: Boolean = True (optional) returnMode: Integer = 0 (optional & nicht benötigt) Rückgabewert Variant [ [Workbookname, Blattname, Zelladresse des Fundes] , [Workbookname, Blattname, Zelladresse des Fundes] , … ] | Diese Methode funktioniert genauso wie die bereits oben aufgeführte Methode getFindList(), jedoch wird hier das Durchsuchen mehrerer Arbeitsmappen unterstützt. Diese müssen vorab durch den Aufruf von setWorkbook() der Klasse bekanntgemacht werden. Sind diese nicht geöffnet, so werden sie für das Durchsuchen geöffnet und anschließend durch den Aufruf der Methode doCleanUp() wieder geschlossen. |
| getAllFindListByOffset(searchString, offsetX, offsetY, caseSens) Parameter searchString: String offsetX: signed Integer offsetY: signed Integer caseSens: Boolean = True (optional) Rückgabewert Variant [ [Workbookname, Blattname, Zelladresse des Fundes] , [Workbookname, Blattname, Zelladresse des Fundes] , … ] | Diese Methode funktioniert genauso wie die bereits oben aufgeführte Methode getFindListByOffset(), jedoch wird hier das Durchsuchen mehrerer Arbeitsmappen unterstützt. Diese müssen vorab durch den Aufruf von setWorkbook() der Klasse bekanntgemacht werden. Sind diese nicht geöffnet, so werden sie für das Durchsuchen geöffnet und anschließend durch den Aufruf der Methode doCleanUp() wieder geschlossen. |
| getAllRowValues(searchString, startAfterColPos , colRange, caseSens) Parameter searchString: String startAfterColPos: Integer = 0 (optional) colRange: Integer = 0 (optional) caseSens: Boolean = True (optional) Rückgabewert Variant [ [Wert1_Spalte1, Wert1_Spalte2, Wert1_Spalte3] , [Wert2_Spalte1, Wert2_Spalte2, Wert2_Spalte3] , … ] | Diese Methode funktioniert genauso wie die bereits oben aufgeführte Methode getRowValue(), jedoch wird hier das Durchsuchen mehrerer Arbeitsmappen unterstützt. Diese müssen vorab durch den Aufruf von setWorkbook() der Klasse bekanntgemacht werden. Sind diese nicht geöffnet, so werden sie für das Durchsuchen geöffnet und anschließend durch den Aufruf der Methode doCleanUp() wieder geschlossen. |
| markFindings(searchString, locationRange, markColorIndex) Parameter searchString: String locationRange: String (optional) markColorIndex: Integer (optional) Rückgabewert nichts / none | Mit dieser Methode ist es möglich Fundstellen innerhalb eines Tabellenblattes oder eines definierten Bereiches (Tabelle, Namens-Manager-Bereich, etc…) zu markieren. Es werden sowohl Fundstellen in Großschreibung, als auch in Kleinschreibung markiert. Durch Angabe des Namens eines Bestimmten Bereiches innerhalb des optionalen Parameters LocationRange kann das Markieren auf einen bestimmten Bereich beschränkt werden. Durch die Angabe eines VBA-Colorindex* im Parameter markColorIndex ist es möglich die Farbe, mit welcher die Zellen markiert werden, zu ändern. Durch das Senden eines leeren Strings innerhalb des Parameters searchString werden alle farbigen Markierungen im angegebenen Bereich zurückgesetzt! Hinweis: Befinden sich in der Liste bereits farbig markierte Zellen, so werden diese überschrieben, falls sich innerhalb der Zelle der gesuchte Wert befinden sollte. |
| deleteTableItems(locationRange, wSheet) Parameter locationRange: String wSheet: Integer (optional) Rückgabewert nichts / none | Diese Methode löscht den Inhalt einer bestimmten Range (locationRange) des aktuellen, oder unter wSheet namentlich angegebenen Tabellenblattes. |
| getWorkbook() Parameter nichts / none Rückgabewert String „NAME_DER_GEWÄHLTEN_ARBEITSMAPPE“ | Diese Methode gibt den Namen der aktuell für die Suche ausgewählten Arbeitsmappe zurück. |
| getClsWB() Parameter nichts / none Rückgabewert String „NAME_DER_AKTUELLEN_ARBEITSMAPPE“ | Diese Methode gibt den Namen der Arbeitsmappe zurück, in welcher die Klasseinstanz geladen wurde. |
| setWorkbook(wBook) Parameter wBook: String Rückgabewert nichts / none | Mit dieser Methode kann die zu durchsuchende Arbeitsmappe festgelegt werden. Die Übergabe der Arbeitsmappe durch den Parameter wBook kann dabei auf unterschiedliche Weise erfolgen. – leer (aktuelle Arbeitsmappe wird gewählt) – relative Pfadangabe oder Name inkl. Endung der Arbeitsmappe – absolute Pfadangabe inkl. Endung der Arbeitsmappe Um für die „All“ Methoden mehrere Arbeitsmappen zu öffnen, wiederholt man den Befehl innerhalb des gewünschten Suchablaufes manuell oder per Schleifenaufruf. |
| getSheet() Parameter nichts / none Rückgabewert String „NAME_DES_GEWÄHLTEN_TABELLENBLATTES“ | Diese Methode gibt den Namen des aktuell für die Suche ausgewählten Tabellenblattes zurück. |
| setSheet(wSheet) Parameter wSheet: String Rückgabewert nichts / none | Mit dieser Methode kann das zu durchsuchende Tabellenblatt festgelegt werden. |
| setAllSheets() Parameter nichts / none Rückgabewert nichts / none | Durch Aufruf dieser Methode werden alle in der / den zu durchsuchenden Arbeitsmappen enthaltenen Tabellenblätter während der Suche durchlaufen. Wird die Suche mit einer der „only first elements“ Methoden durchgeführt, so wir der erste Fund aus jedem Tabellenblatt zurückgeliefert. |
| selectWorkbook(wBook) Parameter wBook Rückgabewert nichts / none | Mit Hilfe dieser Methode kann die aktuelle (wenn wBook leer) oder eine andere Arbeitsmappe in den Vordergrund gebracht werden. Diese kann zum Beispiel aufgerufen werden um eine neue Arbeitsmappe in den Vordergrund zu holen, nachdem diese mit den gefundenen Werten befüllt wurden ist. |
| doCleanUp() Parameter nichts / none Rückgabewert nichts / none | Diese Methode räumt nach einer durchgeführten Suche alles wieder auf. So werden zum Beispiel durch die Suche geöffnete Arbeitsmappen wieder geschlossen. |
Einbinden der SLSS SearchClass in eine bestehende Exceldatei
Um die Suchklasse in ein bestehendes Exceldokument integrieren zu können, muss diese zuerst über den VBA-Editor, welchem man über den Menüpunkt „Entwicklertools“ und das Klicken der darin vorhandenen Schaltfläche „Visual Basic“ öffnen kann, eingebunden werden. Sollte der Menüpunkt „Entwicklertools“ nicht vorhanden sein, so muss dieser erst durch Anpassen des Menübandes sichtbar gemacht werden. Eine Schritt für Schritt Anleitung hierfür findet man unter „Wo finde ich in die VBA-IDE“ im Artikel Visual Basic for Applications*.
In der VBA-IDE angelangt, öffnet man durch einen Rechtsklick auf das gewünschte VBAProject und die Auswahl des Menüpunktes „Datei importieren“ das Importfenster.
Im geöffneten Import-Auswahldialog navigiert man nun zum Ordner (Downloadverzeichnis), in welchem sich die heruntergeladene Klassendatei mit der Bezeichnung „SLSS_SearchClass.cls“ befindet. Diese wählt man anschließend aus und bestätigt den Import durch das Betätigen des Öffnen-Buttons.
Nun sollte im Projekt-Explorer ein Unterordner mit der Bezeichnung „Klassenmodule“ vorhanden sein, in welchem sich die SLSS SearchClass befindet. Damit ist der Import der Klasse in ein bestehende Datei abgeschlossen.
Hinweis: Sollte es sich bei der aktuellen Excelliste um eine Datei ohne Makro-Funktionalität handeln (erkennbar an der Endung *.xlsx), so sollte diese jetzt als Datei mit Makros (*.xlsm) gespeichert werden. Dazu muss man im „Speichern unter“ Fenster den Dateityp auf „Excel-Arbeitsmappe mit Makros (*.xlsm)“ umstellen und die Datei speichern.
Beispiel eines ersten Suchaufrufes
Nachdem die Klasse nun Bestandteil der Exceldatei ist, kann es mit dem ersten Suchaufruf weitergehen. Ich verwende für die erste Erklärung einfach das Minimalbeispiel, welches sich ebenfalls als Modul „G_minimalExample“ in der Beispielsammlung der Datei „SLSS_SearchClass_X.X.X.X.xlsm“ befindet.
Nur die erste Fundstelle auswerten
Innerhalb der Sub-Anweisung erfolgt als erstes das Initialisieren einer neuen Instanz der Suchklasse. Hierfür muss über die Anweisung Dim sClass As SLSS_SearchClass eine Variable vom Typ SLSS_SearchClass angelegt werden. Anschließend wird die Instanz mittels Set sClass = New SLSS_SearchClass erzeugt.
Nun kann man auf die Suchmethoden, welche durch die Klasse bereitgestellt werden, zugreifen. Im Beispiel wird das aktuelle Tabellenblatt dieser Arbeitsmappe durchsucht, weshalb die Methoden setWorkbook() und setSheet(), respektive setAllSheets() nicht explizit aufgerufen werden müssen. Durch das Ausführen der getFinding() Methode wird die Suche gestartet. Da diese Methode nur die erste Fundstelle zurückliefert und in diesem Beispiel nur das aktuelle Tabellenblatt durchsucht wird, kann die Variable auch bei erfolgreicher Suche nur maximal ein Element enthalten. Das Suchergebnis wird anschließend in der Variable finding als Array aus Arrays gespeichert, welcher anschließend durch Iteration über die einzelnen Werte durchlaufen werden kann. Dies macht bei einem Wert zwar wenig Sinn, doch auf Grund der Gleichheit mit den Rückgabewerten der anderen Methoden habe ich mich auch hier für den Array aus Arrays entschieden. Im Beispiel übergebe ich den Suchstring „gasse“ und führe eine Suche ohne Beachtung der Groß- und Kleinschreibung (zu erkennen am False) durch. Der komplette Aufruf sieht daher wie folgt aus finding = sClass.getFinding("gasse", False).
Nun folgt auch schon die Auswertung der Suchergebnisse. Als erstes sollte man überprüfen, ob überhaupt eine Übereinstimmung mit der Suchanfrage gefunden wurde. Ist dies nicht der Fall, so ist die Variable finding leer und der Versuch des Iterierens über einen leeren Array würde zur Ausgabe eines Fehlers führen. Aus diesem Grund prüfe ich zuerst mittels IsEmpty(finding) = False ob der Array Elemente enthält.
Befinden sich Elemente innerhalb von finding, so nutze ich im Beispiel eine „for Each“-Schleife um die einzelnen Funde zu durchlaufen. Diese gebe ich im gezeigten Beispiel lediglich in einer Messagebox aus. Es ist aber kein großes Problem die Werte auch in anderer Form zu verwenden. Auch hierfür habe ich in die Beispielsammlung einige Verwendungsmöglichkeiten integriert.
Sub minimalExample_Single()
Dim sClass As SLSS_SearchClass
Set sClass = New SLSS_SearchClass
finding = sClass.getFinding("gasse", False) 'Aufruf von getFinding, weshalb nur die erste Übereinstimmung zurückgegeben wird
If IsEmpty(finding) = False Then
For Each element In finding
MsgBox "FIRST FOUND ONLY METHOD" & vbCrLf & _
"WBook: " & vbTab & element(0) & vbCrLf & _
"Sheet: " & vbTab & element(1) & vbCrLf & _
"Adress: " & vbTab & Workbooks(element(0)).Sheets(element(1)).Range(element(2)).Address() & vbCrLf & _
"Value: " & vbTab & Workbooks(element(0)).Sheets(element(1)).Range(element(2)).Value & vbCrLf
Next
Else
MsgBox "No findings with actual parameters!" & vbCrLf & vbCrLf & "Es wurde keine Übereinstimmung mit den eingegebenen Suchkriterien gefunden!"
End If
End Sub
Alle Fundstellen auswerten
Möchte man nun nicht nur die erste, sondern alle potentiellen Übereinstimmungen auswerten, so ändert sich lediglich der Aufruf der Suchmethode. Anstatt der zuvor verwendeten getFinding() Methode nutzt man hierfür die getFindList() Methode, welche die gleichen Parameter erwartet. Diese liefert jedoch alle Fundstellen im aktuellen Tabellenblatt.
Sub minimalExample_Multiple()
Dim sClass As SLSS_SearchClass
Set sClass = New SLSS_SearchClass
finding = sClass.getFindList("gasse", False) 'Aufruf von getFindList, weshalb alle Übereinstimmungen zurückgegeben werden
If IsEmpty(finding) = False Then
For Each element In finding
MsgBox "MULTIPLE FINDING METHOD" & vbCrLf & _
"WBook: " & vbTab & element(0) & vbCrLf & _
"Sheet: " & vbTab & element(1) & vbCrLf & _
"Adress: " & vbTab & Workbooks(element(0)).Sheets(element(1)).Range(element(2)).Address() & vbCrLf & _
"Value: " & vbTab & Workbooks(element(0)).Sheets(element(1)).Range(element(2)).Value & vbCrLf
Next
Else
MsgBox "No findings with actual parameters!" & vbCrLf & vbCrLf & "Es wurde keine Übereinstimmung mit den eingegebenen Suchkriterien gefunden!"
End If
End Sub
Wichtig – doCleanUp() am Ende des Suchaufrufes
Da man mit der SLSS SearchClass auch andere Arbeitsmappen durchsuchen kann, diese dazu aber geöffnet werden müssen, wollte ich das anschließende Schließen dieser so einfach wie möglich gestalten. Aus diesem Grund habe ich keinen öffentlichen CloseWorkbook-Befehl, sondern die doCleanUp() Methode eingeführt. Diese schließt alle durch die Suche geöffneten Arbeitsmappen und stellt den Ausgangszustand wie eingangs der Suche wieder her. Durchsuchte Arbeitsmappen, welche vorab bereits geöffnet waren, bleiben hingegen geöffnet um evtl. Änderungen in diesen nicht zu verwerfen.
Aus diesem Grund sollte die doCleanUp() Methode am Ende jeder durchgeführten Suche einmalig ausgeführt werden!
Weitere Beispiele für die Nutzung der Suchklasse
Da die vorab gezeigten Standard-Suchaufrufe der SLSS SearchClass nur einen Teil des aktuellen Umfangs zeigen, möchte ich hier nochmal auf einige weitere Funktionen ein wenig näher eingehen, da diese vielleicht für den einen oder anderen nützlich sein könnten. Einige dieser Funktionen nutzen dabei den gleichen Aufbau wie die bereits gezeigten Code-Snippets der beiden Minimalbeispiele. Dies soll nochmals veranschaulichen, wie die Nutzung der Klassenfunktionen in vielleicht bereits bestehende Projekte eingebunden werden könnte.
Die Funktionen der Klasse lassen sich auch einfach in ein Userform (Formular) integrieren und können dort zum Beispiel zum Befüllen von Listenfeldern genutzt werden. Zur Veranschaulichung habe ich der Beispielsammlung ein kleines Formular mit der Bezeichnung „SLSS_Searchform“ hinzugefügt, welches dazu genutzt werden kann in der aktuellen Arbeitsmappe nach bestimmten Werten zu suchen.
Die gefundenen Übereinstimmungen werden dann in der Tabelle „UserFormTable“ des Tabellenblattes „Userform-Suche“, über welches auch das Suchformular per Knopfdruck geöffnet werden kann, angezeigt.
Der Code für das Suchformular ist hierbei recht einfach gehalten und nutzt im wesentlichen ebenfalls den Aufruf und die Rückgabewerte der getFindList() Methode. Um das Formular noch schneller weiterverwenden zu können, habe ich anfangs der Sub-Routine mit dem Namen „bt_Start_Click()“, welche für das Ausführen der Suche zuständig ist, die Zieltabelle (Range Objekt) und das Zieltabellenblatt als Variablen angegeben.
Der komplette Code für den Suchaufruf und das Ausfüllen der Tabelle sieht dann wie folgt aus.
Private Sub bt_Start_Click()
Dim caseSens As Boolean, element As Variant, finding As Variant, sClass As SLSS_SearchClass, targetTable As String, targetSheet As String, tblRange As Range, rowIndex As Integer
'insert her the target for all findings (table name)
targetTable = "UserFormTable"
targetSheet = "Userform-Suche"
'create class instance
Set sClass = New SLSS_SearchClass
'check if textbox isn't empty
If SLSS_Searchform.tB_Search.Text <> "" Then
'check if checkbox for search with case sensitive restriction is checked
If SLSS_Searchform.chkBx_CaseSens.Value = True Then
caseSens = True
End If
'check if all sheets or a single sheet should been searched through
If SLSS_Searchform.cB_Location.Text = "Alle Tabellenblätter" Then
sClass.setAllSheets
Else
sClass.setSheet (SLSS_Searchform.cB_Location.Text)
End If
'start search with given parameters
finding = sClass.getFindList(SLSS_Searchform.tB_Search.Text, caseSens)
'check if something was found
If Not IsEmpty(finding) Then
'SPECIALFUNCTION!
'erase all data in the table with the name UserFormTable (see names-manager on sheet Userform-Suche)
Call sClass.deleteTableItems(targetTable, targetSheet)
'get destination table position (row)
Set tblRange = ActiveSheet.Range(targetTable)
rowIndex = tblRange.Row
'element = array element with workbook name , sheet name, address
For Each element In finding
'add new elements to destination table
ActiveSheet.Cells(rowIndex, tblRange.Column).Value = element(0)
ActiveSheet.Cells(rowIndex, tblRange.Column + 1).Value = element(1)
ActiveSheet.Cells(rowIndex, tblRange.Column + 2).Value = "Zelle: " + Workbooks(element(0)).Sheets(element(1)).Range(element(2)).Address()
ActiveSheet.Cells(rowIndex, tblRange.Column + 3).Value = Workbooks(element(0)).Sheets(element(1)).Range(element(2)).Value
'increase row index
rowIndex = rowIndex + 1
Next
Else
'no findings | keine Funde
MsgBox "No findings with actual parameters!" & vbCrLf & vbCrLf & "Es wurde keine Übereinstimmung mit den eingegebenen Suchkriterien gefunden!"
End If
Else
MsgBox "No text in search-textbox present!" & vbCrLf & vbCrLf & "Es wurde kein Suchbegriff in der Such-Textbox eingegeben!", vbInformation, "Kein Suchbegriff eingegeben!"
End If
End Sub
Mit der gleichen Vorgehensweise wie zuvor beim Formular, lässt sich auch eine Suchmaske innerhalb eines Tabellenblattes umsetzen. Dazu habe ich die beiden Tabellenblätter „Erste Funde“ und „Alle Funde“ angelegt und darin die für den Funktionsumfang gewünschten Steuerelemente platziert.
Mit diesen beiden Beispielen demonstriere ich auch die Möglichkeit, wie man die gefundenen Übereinstimmungen in einem neuen Tabellenblatt zusammentragen kann. Der Code für die beiden Tabellenblätter befindet sich im Modul mit der Bezeichnung „A_EXAMPLE_CALLS“ unter A_getFirstMatchInSpecificSheet() und B_getAllMatchesInSpecificSheet(). Abgesehen von den für die Auswertung der Steuerelemente benötigten Abfragen, beinhalten auch diese Funktionen wieder annähernd den gleichen Aufbau mittels Iteration über die Variable finding!
'check if something was found
'prüfe ob Übereinstimmungen gefunden
If Not IsEmpty(finding) Then
'element = array element with workbook name , sheet name, address
For Each element In finding
'only when new workbook was opened
If colInNewWB = True Then
'set row and column values
newWB.Sheets(1).Cells(rowCount, 1).Value = element(0)
newWB.Sheets(1).Cells(rowCount, 2).Value = element(1)
newWB.Sheets(1).Cells(rowCount, 3).Value = Workbooks(element(0)).Sheets(element(1)).Range(element(2)).Address()
newWB.Sheets(1).Cells(rowCount, 4).Value = Workbooks(element(0)).Sheets(element(1)).Range(element(2)).Value
rowCount = rowCount + 1
newWB.Sheets(1).columns("A:D").AutoFit
Else
'output findings as msgbox
MsgBox "SINGLE FOUND METHOD" & vbCrLf & _
"WBook: " & vbTab & element(0) & vbCrLf & _
"Sheet: " & vbTab & element(1) & vbCrLf & _
"Adress: " & vbTab & Workbooks(element(0)).Sheets(element(1)).Range(element(2)).Address() & vbCrLf & _
"Value: " & vbTab & Workbooks(element(0)).Sheets(element(1)).Range(element(2)).Value & vbCrLf
End If
Next
Else
'no findings | keine Funde
MsgBox "No findings with actual parameters!" & vbCrLf & vbCrLf & "Es wurde keine Übereinstimmung mit den eingegebenen Suchkriterien gefunden!"
End If
Eine Funktion, welche ich immer wieder gern den Nutzern meiner Excellisten zur Verfügung gestellt habe, ist das schnelle Durchsuchen eines Arbeitsblattes oder Teilbereiches und das farbige Hervorheben der Fundstellen, ähnlich der Suche mittels Strg + F, wie sie in vielen anderen Programmen angeboten wird. Hierfür habe ich die Funktion markFindings() umgesetzt, welche in der Beispielsammlung auf dem Tabellenblatt „markiere Fund“ vorgestellt wird.
Darin wurde dem Tabellenblatt einfach eine Textbox hinzugefügt, in welche der Suchtext eingegeben werden kann. Befindet sich innerhalb des Tabellenblattes oder eines explizit angegebenen Bereiches nun eine Übereinstimmung mit dem Suchtext innerhalb der Textbox, so wird diese Stelle farbig hervorgehoben. Die Farbe kann dabei als optionaler Parameter angegeben werden, um das Erscheinungsbild an die aktuelle Datei anzupassen. Hierzu übergibt man den gewünschten VBA-Colorindex* als Parameterwert an der 3. Stelle der Methode.
Call sClass.markFindings(tB_Search.Text, "MarkTable", 45) 'search only in MarkTable with colorindex 45 (orange)
Außerdem habe ich in das Beispiel eine Checkbox integriert, mit welcher es möglich ist zwischen der Suche und dem damit verbundenen Markieren innerhalb des kompletten Tabellenblattes, oder dem wie im obigen Screenshot zu sehenden Markieren eines bestimmten Bereiches (nur der Tabelle mit dem Namen „MarkTable“) umschalten zu können. Wählt man die Checkbox ab, so werden auch die Übereinstimmungen im unteren Teil des Tabellenblattes mit markiert.
Leert man die Textbox wieder, so werden die farblichen Markierungen in allen Fundstellen gelöscht, weshalb man diese Funktion nicht auf Listen oder Tabellenblätter anwenden sollte in welchen mit farbigen Zellen gearbeitet wird. Für die Zukunft möchte ich hierfür diese Funktion um das Speichern und Wiederherstellen der vorherigen Zellformatierung erweitern.
Der Beispielcode für den Aufruf der Klassenfunktion befindet sich übrigens nicht innerhalb eines Modules, sondern direkt in der Arbeitsmappe „Tabelle 2 (markierte Funde)“, da dieser in das OnChange-Event der Textbox intergiert wurde.
Hierbei wird der Klassenmethode bei jeder Änderung des Textbox Textes der neue Suchtext übergeben. Die if-Bedingung dient lediglich dem unterschiedlichen Ausführen, je nachdem ob die Checkbox für das Durchsuchen eines Teilbereiches markiert wurde, oder nicht! Mittels tB_Search.Activate wird der Cursor immer wieder zurück auf die Textbox gesetzt, um ein weiterschreiben zu ermöglichen. Natürlich wäre auch ein Kombination aus Textbox und Suchbutton denkbar!
Private Sub tB_Search_Change()
'called each time the text of textbox gets changed
Dim sClass As SLSS_SearchClass
Set sClass = New SLSS_SearchClass 'initialize new class instance
'check if checkbox for searching only in range of "MarkTable"-table (for tablename see name-manager) is checked
'Prüfen ob die Checkbox für das explizite Durchsuchen der MarkTable-Tabelle (für Tabellenname siehe Namens-Manager) gesetzt ist
If Tabelle2.ChkBx_TableRange.Value = True Then
Call sClass.markFindings(tB_Search.Text, "MarkTable") 'search only in MarkTable
Else
Call sClass.markFindings(tB_Search.Text) 'search entire sheet
End If
tB_Search.Activate 'reset focus to textbox (necessary)
End Sub
Mit der folgenden Methode ist es möglich sich teilweise oder komplette Datenreihen als Suchergebnis zurückliefern zu lassen. Wird der übergebene Suchwert innerhalb einer Zelle gefunden, so wird hier jedoch nicht der Name der Arbeitsmappe, des Tabellenblattes und die Zelladresse, sondern die Daten der aktuellen Datenreihe zurückgegeben. Um bei großen Tabellen die Möglichkeit zu haben nicht alle Werte der Reihe übermittelt zu bekommen, habe ich 2 optionale Parameter angelegt, mit welchen es möglich ist die Startposition innerhalb der Reihe und die Anzahl der ab da zurückgelieferten Elemente zu beeinflussen. Diese Funktion hat mir auch schon des Öfteren beim Sammeln von Daten aus anderen Arbeitsmappen gute Dienste geleistet.
Im Beispielcode des F_EXAMPLE_getRowValues Moduls befindet sich der Code für den Aufruf der getRowValue() und getAllRowValues() Methoden. Da die Position und Anzahl der zurückgegebenen Elemente von der Größe der jeweiligen Tabelle abhängig ist, verwende ich für die Ausgabe eine zweite „for Each“-Schleife mit deren Hilfe ich durch den zurückgegebenen Array innerhalb des Arrays iteriere.
finding = sClass.getRowValue("Test", 0, 0, True)
'check if returned array is empty
If Not IsEmpty(finding) Then
'open and set new workbook
Dim newWB As Workbook
Workbooks.Add
Set newWB = ActiveWorkbook
newWB.ActiveSheet.Name = "SingleWorkbook"
'initialize row and column position variable
rowCount = 1
colCount = 1
'PUSH ALL FROM getRowValue RETURNED ELEMENTS INTO NEW OPENED WORKBOOK ROW 1
'ALLE VON getRowValue ERHALTENEN WERTE (ZELLENWERTE DER AKTUELLEN REIHE) IN ZEILE 1 DER NEUEN ARBEITSMAPPE EINFÜGEN
'loop over returned array
For Each element In finding
'loop over each column value in returned element
For Each arrayElement In element
newWB.Sheets(1).Cells(rowCount, colCount).Value = arrayElement
colCount = colCount + 1 'increase column count for next returned column value
Next
colCount = 1 'reset column count for next row
rowCount = rowCount + 1 'increase row count for next row (element) in array
Next
End If
Das Verhalten dieser Funktionen kann natürlich auch manuell mit der normalen Suchmethodik über getFinding() oder getFindList() nachgebildet werden. Um nicht jedes Mal die Größe der Tabelle (Used Range) bestimmen zu müssen, halte ich diese Funktion jedoch für ein Stück komfortabler.
Screenshots SLSS SearchClass
Fazit
Die Funktionen der SLSS SearchClass habe ich nun schon in einige meiner privaten Excellisten eingebaut und bin mit dem Funktionsumfang soweit recht zufrieden. Einige Ideen, wie das angesprochene Speichern und Wiederherstellen der farblichen Ursprungsformatierung, habe ich zwar schon noch, doch benötige ich diese nicht allzu dringend. Nun bin ich gespannt, ob die Suchklasse genauso „beliebt“ sein wird wie mein Basti-Kalender*, welcher wissentlich auch anderen Nutzern schon gute Dienste geleistet hat.
Falls es noch Fragen oder Anregungen zur Suchklasse geben sollte, so können diese wie immer gern unten in die Kommentare oder per Mail an info@langer-sebastian.de gesendet werden!
Download SLSS SearchClass
Für meine Freunde und alle Interessierten biete ich hier die SLSS SearchClass zum Download an. Da es mit den *.ZIP Dateien evtl. zu Problemen beim Download kommen kann (ZIP-Archive werden in einigen Web-Browsern als potentielle Bedrohung erkannt und der Download blockiert), stelle ich die Datei sowohl als *.ZIP, als auch als *.7z zur Verfügung. Für die letztere Datei benötigt man das kostenlose Kompressionsprogramm 7-Zip* was hier* heruntergeladen werden kann. (https://www.7-zip.de/).
Download “SLSS SearchClass (Zip-Archiv)”
SLSS_SearchClass-1.0.0.0.zip – 32-mal heruntergeladen – 289,55 kBDownload “SLSS SearchClass (7-Zip-Archiv)”
SLSS_SearchClass-1.0.0.0.7z – 10-mal heruntergeladen – 254,01 kB
Debugging:
Trotz größter Sorgfalt und mehrfachem Testen kommt es immer wieder vor, dass sich in Software Bugs oder Fehler einschleichen, welche beim Erstellen übersehen, oder einfach nicht gefunden werden. Wenn jemand so einen Fehler finden sollte, oder sonstige Anregungen, Ideen oder Verbesserungen zum Programm hat, dann wäre es schön wenn dies einfach im Kommentarbereich kommuniziert wird. So kann ich die Änderungen in das nächste Versions-Update einfach mit einfließen lassen.
Haftungsausschluss:
Die hier veröffentlichte Software wurde auf mehreren Systemen fehlerfrei getestet. Dennoch kann für evtl. Beschädigungen, Instabilitäten oder sonstige Beeinträchtigungen, welche unmittelbar durch die Installation, Nutzung, oder in sonstiger Weise in Zusammenhang stehend mit der hier zum Download angebotenen Software auftreten keinerlei Haftung übernommen werden. Der Download, die Installation und Nutzung geschehen auf eigenes Risiko! Bei Problemen wenden sie sich bitte an info@langer-sebastian.de!