VBA-Programmierung: Suchen und finden

Visual Basic for Applications, kurz VBA, ist bestens geeignet, um in umfangreichen Tabellen Kriterien zu suchen, Merkmale zuzuordnen und daraus Auflistungen zu erzeugen. Das alles per Knopfdruck.

An einem Beispiel will ich erklären, wie dabei vorgegangen werden kann.

Auf der Website des BSI sind unter Grundschutzkataloge / Hilfsmittel sind Kreuzreferenztabellen zu finden, die die Verknüpfungen der Bausteine mit den Gefährdungen und den Maßnahmen enthalten.

Durch Datenschützer wurde vor einiger Zeit eine ergänzende Zuordnung zu den Datenschutzkontrollzielen vorgenommen.

Was sich inhaltlich dahinter verbirgt, das kann bei Interesse über die BSI-Site herausgefunden werden.

Die Beispieltabelle wurde durch mich zusammengetragen und umfasst ca. 3080 Datensätze. Hier will ich mich mit 53 Datensätzen begnügen. Die Beispieltabelle ist so aufgebaut, eine Datei dazu kannst Du am Ende des Beitrags herunterladen:

Die Aufgabe soll sein, die Datensätze aus dem Baustein B 1.1 aufzulisten, wenn das Datenschutzkontrollziel “Zugangskontrolle” relevant ist.

Alle Programmschritte sollen in diesem Worksheet (Arbeitsmappe) stattfinden. Die Tabelle mit den Basisdaten (s.o.) befindet sich im Blatt “Basisdaten”. Die gefundenen Datensätze sollen im Blatt “Auswertung” abgelegt werden.

Den Tabellenkopf aus “Basisdaten” B6:M6 kopiere ich schon gleich in das Blatt “Auswertung” nach A4:L4 , das muss nicht mit dem Makro geschehen.

Ich beginne, im VBA-Editor im Modul1 mit dem Schreiben des Makros.

Makro benennen und Variablen deklarieren

Zuerst der Name des Makros: Sub Baustein()

Suchbegriff ist “B 1.1”, ein Text: Dim strSuch As String

Zelle (Bereich) mit Suchergebnis: Dim rngFund

Elemente der Kopfzeile
Gefährdung: Dim strGef As String
Maßnahme: Dim strMass As String
Zyklus: Dim strZyk As String
Siegel: Dim strSieg As String
Zugangskontrolle: Dim strZG As String

Zählvariable: Dim i As Integer

Arbeitsblätter:
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Den Variablen Werte zuweisen und Arbeitsblätter referenzieren

Jetzt kann es auch schon los gehen.
Ich deklariere Variablen. i=5
Ich referenziere die Arbeitsblätter. Set ws1=Worksheets (“Auswertung”)
Set ws2=Worksheets (“Basisdaten”)

Im Blatt “Auswertung lösche ich bisherige Suchergebnisse. Dafür wähle ich den Bereich von A5:L4000.
Warum bis L4000? Um sicher zu gehen, dass dort keine Informationen mehr stehen.
Der zu löschende Bereich kann auch kleiner oder größer gewählt werden.

Range(“A5:L4000”).ClearContents

Die Suchprozedur

Ich teile dem Makro jetzt mit, wo der Suchbegriff “strSuch” zu finden ist.

strSuch=ws1.Range(“B2”).Value

Jetzt wird die Fundzelle / -bereich referenziert und die Suchanweisung gegeben.

Set rngFund=ws2.Range(“A7:A59”).Find(what:=strSuch,LookIn:=xlValues,LookAt:=xlWhole)

Wenn nichts mehr gefunden wird: If Not rngFund Is Nothing Then
setze die erste Adresse als Fundadresse:firstAddress=rngFund.Address

Bedingung, nur wenn Zugangskontrolle: If rngFund.Offset(0,6)=”X” Then

Das soll jetzt getan werden: Do
strGef=rngFund.Offset(0,1)
strMass=rngFund.Offset(0,2)
strZyk=rngFund.Offset(0,3)
strSieg=rngFund.Offset(0,4)
strZG=rngFund.Offset(0,6)

Offset ist das Pendant in VBA zu BEREICH.VERSCHIEBEN in Excel. Die Anweisung zu strGef bedeutet, gehe von der Fundzelle (rngFund) 6 Spalten nach rechts (0 Zeilen, 6 Spalten).

Die Informationen sind nun in den Variablen gespeichert, müssen aber noch ins Blatt “Auswertung” übertragen werden.

Suchergebnisse übertragen

ws1.Range(“A”&i)=strGef
ws1.Range(“B”&i)=strMass
ws1.Range(“C”&i)=strZyk
ws1.Range(“D”&i)=strSieg
ws1.Range(“F”&i)=strZG

Die Ergebnisse des ersten Schleifendurchlaufs sind eingetragen.
Das Makro soll jetzt den nächsten Datensatz finden und wieder eintragen.

Ende If-Abfrage:End If

Set rngFund=ws2.Range(“A7:A59”).FindNext(rngFund)

Zählvariable wird erhöht: i=i+1

Ende der Schleife: Loop While Not rngFund Is Nothing And rngFund.Address <> firstAddress

End If

leere Zeilen im Blatt “Auswertung” löschen: Range(“A5:A4000”).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Mitteilung über Erledigung: MsgBox (“Die Tabelle zum Baustein ” & Range(“B2″).Value & ” ist erstellt!”)
End Sub

Das fertige Makro

Jetzt noch mal das gesamte Makro zusammenhängend:

Sub Baustein()
Dim strSuch As String
Dim rngFund
Dim strGef As String
Dim strMass As String
Dim strZyk As String
Dim strSieg As String
Dim strZG As String
Dim i As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
i=5
Set ws1=Worksheets (“Auswertung”)
Set ws2=Worksheets (“Basisdaten”)
Range(“A5:L4000”).ClearContents
strSuch=ws1.Range(“B2”).Value
Set rngFund=ws2.Range(“A7:A59”).Find(what:=strSuch,LookIn:=xlValues,LookAt:=xlWhole)
If Not rngFund Is Nothing Then
firstAddress=rngFund.Address
If rngFund.Offset(0,6)=”X” Then
Do
strGef=rngFund.Offset(0,1)
strMass=rngFund.Offset(0,2)
strZyk=rngFund.Offset(0,3)
strSieg=rngFund.Offset(0,4)
strZG=rngFund.Offset(0,6)
ws1.Range(“A”&i)=strGef
ws1.Range(“B”&i)=strMass
ws1.Range(“C”&i)=strZyk
ws1.Range(“D”&i)=strSieg
ws1.Range(“F”&i)=strZG
End If
Set rngFund=ws2.Range(“A7:A59”).FindNext(rngFund)
i=i+1
Loop While Not rngFund Is Nothing And rngFund.Address <> firstAddress
End If
Range(“A5:A4000”).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
MsgBox (“Die Tabelle zum Baustein ” & Range(“B2″).Value & ” ist erstellt!”)
End Sub

Diese Makro ermöglicht nur einen Bruchteil von denkbaren Suchergebnissen. Sinnvoll wäre, eine Maßnahme zu wählen und abzufragen, in welchen Bausteinen diese welchen Gefährdungen entgegenwirkt, oder eine Gefährdung zu wählen und abzufragen, welche Maßnahmen in welchen Bausteinen zu ergreifen sind.

Sinnvoll wäre auch, die Suchergebnisse mit den Bezeichungen der Bausteine, Gefährdungen und Maßnahmen zu ergänzen.

Ich habe diesbezüglich bereits weitere Teilprogramme programmiert und getestet. Bei Interesse will ich sie gern vorstellen.

Bleibt noch, eine Schaltfläche zu setzen, ihr das Makro zuzuweisen und die Schaltfläche anzuklicken.

Die Beispieltabelle findest Du in dieser Datei: