|
|
|
Inhalt\
|
|
|
|
[1. Was ist VBA?](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#1-was-ist-vba)\
|
|
|
|
[2. Warum VBA?](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#2-warum-vba)\
|
|
|
|
[3. Anwendungsbeispiele](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#3-anwendungsbeispiele)\
|
|
|
|
[3.1. Das Menü „Erweiterte Funktionen“ im HPK](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#31-das-men%C3%BC-erweiterte-funktionen-im-hpk)\
|
|
|
|
[3.2. Abgleich von Daten aus Word-Dateien](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#32-abgleich-von-daten-aus-word-dateien)\
|
|
|
|
[3.3. Ermittlung des Nachweisstatus für Lebensdaten](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#33-ermittlung-des-nachweisstatus-f%C3%BCr-lebensdaten)\
|
|
|
|
[4. Erste Ansätze zur eigenen Programmierung](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#4-erste-ans%C3%A4tze-zur-eigenen-programmierung)\
|
|
|
|
[4.1. Vorbemerkung](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#41-vorbemerkung)\
|
|
|
|
[4.2. Aktivierung der Entwicklertools](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#42-aktivierung-der-entwicklertools)\
|
|
|
|
[4.3. Anlegen eines Formulars oder eines Moduls](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#43-anlegen-eines-formulars-oder-eines-moduls)\
|
|
|
|
[4.4. Definition eines Makros](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#44-definition-eines-makros)\
|
|
|
|
[4.5. Zugriff auf Excel-Arbeitsmappen bzw. -Tabellen](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#45-zugriff-auf-excel-arbeitsmappen-bzw-tabellen)\
|
|
|
|
[4.6. Zugriff auf Zellen](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#46-zugriff-auf-zellen)\
|
|
|
|
[4.7. Vergleiche](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#47-vergleiche)\
|
|
|
|
[4.8. If-Verzweigungen](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#48-if-verzweigungen)\
|
|
|
|
[4.9. Schleifen](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#49-schleifen)\
|
|
|
|
[4.10. Formular gestalten](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#410-formular-gestalten)\
|
|
|
|
[4.11. Start des Makros bzw. des Formulars](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#411-start-des-makros-bzw-des-formulars)\
|
|
|
|
[5. Links](https://gitlab-ce.rrz.uni-hamburg.de/uahh-digitale-dienste/anleitungen/-/wikis/Visual-Basic-for-Applications-(VBA)-in-Excel-im-Kontext-des-Hamburger-Professorinnen-und-Professorenkatalogs:-Anwendungs-und-Code-Beispiele#5-links)
|
|
|
|
|
|
|
|
## 1. Was ist VBA?
|
|
|
|
- Eine aus Visual Basic abgeleitete Skriptsprache
|
|
|
|
- Eingebunden in Microsoft-Office-Produkte
|
|
|
|
- Ermöglicht Programmierung innerhalb der Anwendungen, insbesondere von Formularen und Makros
|
|
|
|
## 2. Warum VBA?
|
|
|
|
- Großer Datenbestand im HPK
|
|
|
|
- Nur in Form von Excel-Tabellen erfasst, eine „richtige“, relationale Datenbank ist nicht vorhanden → Verknüpfen von Daten und gleichzeitige Änderung von vielen Datensätzen oftmals schwierig
|
|
|
|
- Über VBA können Abfragen mit komplexen Bezügen und Filtern ausgeführt und Daten aus verschiedenen Arbeitsmappen kombiniert verarbeitet werden
|
|
|
|
- Verarbeitung mithilfe von VBA erspart viel händische Arbeit und verhindert Flüchtigkeitsfehler
|
|
|
|
→ Wege, bei denen so wenig händische Arbeit wie möglich notwendig ist, sind bei der Arbeit mit solchen Daten unbedingt zu bevorzugen
|
|
|
|
## 3. Anwendungsbeispiele
|
|
|
|
### 3.1. Das Menü „Erweiterte Funktionen“ im HPK
|
|
|
|
- Grundlage: Formularfeld mit Buttons, die verschiedene Funktionen aufrufen können
|
|
|
|
- Anzeigen von Online-Einträgen und Hinzufügen dieser zur Arbeitsmappe
|
|
|
|
- Verschiedene Web-Suchen
|
|
|
|
→ vereinfacht häufig auftretende Arbeitsschritte zu Aktionen, die mit einem Klick erledigt werden können
|
|
|
|
### 3.2. Abgleich von Daten aus Word-Dateien
|
|
|
|
- Auswertungen aus den Personalakten-Beständen im Staatsarchiv wurden für jede Person tabellarisch in fast 1000 Word-Dateien erfasst
|
|
|
|
- Um nachzutragen, welche der Lebensdaten im Katalog durch diese Auswertungen belegt werden können, wurde ein Makro entwickelt, dass alle Word-Dateien automatisch durchgeht und die Lebensdaten ausliest und in die HPK-Excel-Datei einträgt, welche Angaben belegt werden können
|
|
|
|
→ Aufwand von wenigen Stunden inklusive Programmierung, ansonsten hätten die über 1000 Dateien händisch abgeglichen werden müssen
|
|
|
|
### 3.3. Ermittlung des Nachweisstatus für Lebensdaten
|
|
|
|
- Es sollten möglichst nur Daten zu Geburt und Tod veröffentlicht werden, die aus belastbaren Quellen nachgewiesen werden können → insbesondere zur Anfangszeit des HPK wurden keine Quellennachweise geführt, diese werden sukzessive nachgepflegt
|
|
|
|
- Makro erlaubt den Abgleich der Ortstabelle des HPK mit der Haupttabelle, sodass in der Ortstabelle ersichtlich wird, welche Daten belegt sind und damit veröffentlich werden können
|
|
|
|
→ Abgleich mit Daten aus anderen Arbeitsmappen, sodass nicht händisch doppelt gepflegt werden muss und Fehler vermieden werden
|
|
|
|
|
|
|
|
## 4. Erste Ansätze zur eigenen Programmierung
|
|
|
|
### 4.1. Vorbemerkung
|
|
|
|
Änderungen, die durch Makros vorgenommen werden, können nicht ohne Weiteres rückgängig gemacht werden – so können beispielsweise bei der falschen Angabe von Zellenverweisen ungewollt Daten überschrieben werden. Es ist deshalb immer zu empfehlen, bei der Entwicklung von Makros auf einer Kopie zu arbeiten und erst nach erfolgreichen Tests das entsprechende Makro auf die Originaldatei anzuwenden.
|
|
|
|
Arbeitsmappen, die Makros enthalten, müssen mit der Endung „.xlsm“ abgespeichert werden.
|
|
|
|
Die folgenden Schritte sind dafür gedacht, in einem ersten Ansatz die Möglichkeiten des Einsatzes von VBA aufzuzeigen. Um den Code kurz und verständlich zu halten, wurde etwa auf Option explicit bzw. die Deklarierung der Variablen und die Einhaltung anderer Konventionen guter Programmierpraxis vorerst weitestgehend verzichtet.
|
|
|
|
### 4.2. Aktivierung der Entwicklertools
|
|
|
|
Um auf VBA zugreifen zu können, müssen über „Datei“ → „Optionen“ → „Menüband anpassen“ in der rechten Spalte bei den Hauptregisterkarten die Entwicklertools aktiviert werden.\
|
|
|
|
\
|
|
|
|
_Abb. 1: Aktivierung der Entwicklertools._
|
|
|
|
|
|
|
|
In dem neu hinzugefügten Reiter kann nun mit einem Klick auf „Visual Basic“ der Editor gestartet werden.\
|
|
|
|
\
|
|
|
|
_Abb. 2: Der Reiter "Entwicklertools"._
|
|
|
|
### 4.3. Anlegen eines Formulars oder eines Moduls
|
|
|
|
Mit einem Rechtsklick auf die entsprechende Arbeitsmappe lassen sich nun Formulare oder Module hinzufügen.\
|
|
|
|
\
|
|
|
|
_Abb. 3: Hinzufügen von Modulen oder Formularen._
|
|
|
|
|
|
|
|
### 4.4. Definition eines Makros
|
|
|
|
Ein Makro wird durch die Vergabe eines Namens und die Schlüsselworte <code>Sub</code> und <code>End Sub</code> definiert.\
|
|
|
|
\
|
|
|
|
_Abb. 4: Ein einfaches Makro._
|
|
|
|
### 4.5. Zugriff auf Excel-Arbeitsmappen bzw. -Tabellen
|
|
|
|
Mithilfe des Befehls Set kann einer beliebig zu benennenden Variable (hier: worksheet1) eine Arbeitsmappe bzw. ein bestimmtes Tabellenblatt einer Arbeitsmappe zugeordnet werden, um darauf über VBA zugreifen zu können. Eine entsprechende Code-Zeile könnte wie folgt aussehen, wenn sich das gewünschte Tabellenblatt (hier: Haupttabelle) innerhalb der Datei befindet, in der auch das Makro gespeichert wird:
|
|
|
|
```visualbasic
|
|
|
|
Set worksheet1 = ThisWorkbook.Worksheets("Haupttabelle")
|
|
|
|
```
|
|
|
|
Befindet sich das gewünschte Tabellenblatt (hier: Ergänzungstablle) in einer anderen Arbeitsmappe (hier: Beispielmappe2.xlsx), ist deren Dateiname beim Aufruf wie folgt mit anzugeben:
|
|
|
|
```visualbasic
|
|
|
|
Set worksheet2 = Workbooks("Beispielmappe2.xlsx").Worksheets("Ergänzungstabelle")
|
|
|
|
```
|
|
|
|
Die entsprechende Arbeitsmappe muss dafür bereits geöffnet sein.
|
|
|
|
### 4.6. Zugriff auf Zellen
|
|
|
|
Es gibt verschiedene Möglichkeiten, auf Zellen bzw. Zellenbereiche zuzugreifen. Bei der Arbeit mit dem HPK hat sich der Zugriff auf Zellen in einem Tabellenblatt-Objekt über das Schlüsselwort Cells(Zeilennummer, Spaltennummer) bewährt:
|
|
|
|
```visualbasic
|
|
|
|
worksheet1.Cells(2, 12) = worksheet2.Cells(34, 219)
|
|
|
|
```
|
|
|
|
In diesem Beispiel wird der Zelle in Zeile 2, Spalte 12 des dem Objekt worksheet1 zugeordneten Tabellenblatts der Wert aus der Zelle in Zeile 34, Spalte 219 des dem Objekt worksheet2 zugeordneten Tabellenblatts zugewiesen.
|
|
|
|
|
|
|
|
### 4.7. Vergleiche
|
|
|
|
VBA bietet eine Reihe von Vergleichsoperatoren, mit denen Werte verglichen werden können. Dazu zählen etwa < und >, aber auch Schlüsselworte wie LIKE. Eine Liste der Operatoren findet sich etwa hier: VBA-Operatoren bei Microsoft Learn. Einer der am häufigsten verwendeten Operatoren dürfte = sein, wie er auch im obigen Beispiel Anwendung findet. In folgender Zeile wird geprüft, ob der Wert im ersten Tabellenblatt dem im zweiten entspricht:
|
|
|
|
```visualbasic
|
|
|
|
If worksheet1.Cells(w1RowCtr, 1) = worksheet2.Cells(w2RowCtr, 1) Then
|
|
|
|
```
|
|
|
|
Auf die Verbindung mit einer If-Abfrage soll im nächsten Unterkapitel eingegangen werden.
|
|
|
|
### 4.8. If-Verzweigungen
|
|
|
|
Ein elementarer Bestandteil wahrscheinlich jeder Programmiersprache sind If-Verzweigungen, die es erlauben, basierend auf einer oder mehrerer Prüfbedingungen Entscheidungen zu treffen und daraufhin bestimmte Verfahrenswege zu verfolgen. Die Struktur einer If-Verzweigung sieht in VBA grundsätzlich wie folgt aus:
|
|
|
|
```visualbasic
|
|
|
|
1 If Pruefvariable = Pruefwert Then 'Hier wird geprüft, ob der Wert einer Variable einem Prüfwert entspricht – ist dies der Fall, wird mit Zeile 2 fortgefahren, ansonsten mit Zeile 3
|
|
|
|
2 'beliebiger Verarbeitungsschritt
|
|
|
|
3 Else If Pruefvariable = Pruefwert2 Then 'Sollte die Prüfung in Zeile 1 sich nicht als wahr erwiesen haben, wird in dieser Zeile auf eine andere Bedingung geprüft – sollte diese wahr ergeben, wird in Zeile 4 fortgefahren, ansonsten in Zeile 5
|
|
|
|
4 'beliebiger Verarbeitungsschritt
|
|
|
|
5 Else 'Mit dem Schlüsselwort Else werden alle Fälle abgefangen, die keine der vorherigen Prüfbedingungen erfüllen. Für diese wird der in Zeile 6 definierte Schritt durchgeführt
|
|
|
|
6 'beliebiger Verarbeitungsschritt
|
|
|
|
7 End If 'Die If-Verzweigung muss mit End If geschlossen werden
|
|
|
|
```
|
|
|
|
Innerhalb einer If-Verzweigung können also unterschiedliche Bedingungen abgefragt werden. Darüber hinaus ist es möglich, mehrere Prüfbedingungen mit den Schlüsselworten AND und OR zu verknüpfen, sodass die Prüfung beispielsweise nur dann „wahr“ ergibt, wenn beide Bedingungen erfüllt sind:
|
|
|
|
```visualbasic
|
|
|
|
If Pruefvariable = Pruefwert AND Pruefvariable2 = Pruefwert2 Then
|
|
|
|
```
|
|
|
|
Es ist darüber hinaus möglich, mithilfe des Schlüsselwortes Not auf die Negation einer Bedingung zu prüfen:
|
|
|
|
```visualbasic
|
|
|
|
If Not Pruefvariable = Pruefwert Then
|
|
|
|
```
|
|
|
|
### 4.9. Schleifen
|
|
|
|
Schleifen eigenen sich gut, um eine Vielzahl von Datensätzen nacheinander abzuarbeiten und für jeden dabei beispielsweise eine Prüfung durchzuführen. Im obigen Beispiel wurden etwa zwei For-Next-Schleifen verwendet – die erste durchläuft dabei Zeile für Zeile die erste Tabelle, während die zweite Schleife bei jeder Zeile der ersten Tabelle alle Zeilen der zweiten Tabelle durchläuft und eine Prüfung auf eine bestimmte Bedingung durchführt. Für eine For-Next-Schleife wird klassischerweise eine Zählervariable und ein Höchstwert definiert, bis zu der diese Variable hochgezählt werden soll. Im obigen Beispiel lautet die Zählervariable für die erste Schleife w1RowCtr. Diese beginnt mit dem Wert 2 und wird bei jedem Schleifendurchlauf um den Wert 1 erhöht, bis der Maximalwert w1MaxRow, der der Anzahl der Zeilen des ersten Tabellenblatts entspricht, erreicht ist. Der Aufruf sieht damit folgendermaßen aus:
|
|
|
|
```visualbasic
|
|
|
|
1 For w1RowCtr = 2 To w1MaxRow 'Hier wird die Schleife initialisiert und festgelegt, dass der Zähler w1RowCtr vom Wert 2 bis zum Wert w1MaxRow hochgezählt werden soll
|
|
|
|
2 'beliebiger Verarbeitungsschritt
|
|
|
|
3 Next w1RowCtr 'In dieser Zeile wird der Zählerwert für den nächsten Schleifendurchlauf um 1 erhöht
|
|
|
|
```
|
|
|
|
Um ganze Tabellenblätter Zeile für Zeile durchzugehen, bietet es sich an, den Zähler in Bezug zu den Zeilen zu definieren – es ist jedoch auch möglich, diesen unabhängig von Zeilen zu verwenden, um beispielsweise einen Befehl 100 Mal aufzurufen:
|
|
|
|
```visualbasic
|
|
|
|
For Zaehler = 1 To 100
|
|
|
|
'Beliebiger Verarbeitungsschritt
|
|
|
|
Next Zaehler
|
|
|
|
```
|
|
|
|
### 4.10. Formular gestalten
|
|
|
|
Über die Toolsammlung können dem User-Formular verschiedene Elemente wie Buttons und Text hinzugefügt werden. Die Eigenschaften der Elemente lassen sich im Eigenschaftsfenster festlegen.
|
|
|
|
\
|
|
|
|
_Abb. 5: Die Bearbeitungsmaske für ein Formular, das mithilfe der Toolsammlung bestückt werden kann._\
|
|
|
|
|
|
|
|
Um das Formular aufrufen zu können, muss zur Arbeitsmappe ein kleines Makro hinzugefügt werden, das den eigentlichen Aufruf des Formulars durchführt. Der Code für das Makro lautet dabei wie folgt, wobei der Wert UserForm1 entsprechend des Namens des gewünschten Formulars angepasst werden muss:
|
|
|
|
|
|
|
|
```visualbasic
|
|
|
|
Sub showUserForm()
|
|
|
|
UserForm1.Show
|
|
|
|
End Sub
|
|
|
|
```
|
|
|
|
### 4.11. Start des Makros bzw. des Formulars
|
|
|
|
Makros können zum einen mit einem Klick auf direkt aus dem Visual-Basic-Editor gestartet werden. Es gibt darüber hinaus beispielsweise die Möglichkeit, ein Formularsteuerelement wie etwa einen Button zum Tabellenblatt hinzuzufügen und dieses mit einem Makro zu verknüpfen, das entweder selbst bestimmte Funktionen ausführt oder wiederum ein Formular aufruft.\
|
|
|
|
\
|
|
|
|
_Abb. 6: Hinzufügen eines Formularsteuerelements._\
|
|
|
|
Diesem kann darauf hin das gewünschte Makro zugewiesen werden.\
|
|
|
|
\
|
|
|
|
_Abb. 7: Makro zuweisen._
|
|
|
|
## 5. Links
|
|
|
|
[Visual Basic for Applications bei Microsoft Learn](https://learn.microsoft.com/de-de/office/vba/library-reference/concepts/getting-started-with-vba-in-office)\
|
|
|
|
[Visual Basic for Applications Sprachreferenz bei Microsoft Learn](https://learn.microsoft.com/de-de/office/vba/language/reference/user-interface-help/visual-basic-language-reference)\
|
|
|
|
[https://www.vba-tutorial.de/](https://www.vba-tutorial.de/) |
