ZMBP

Wissenschaftlich Arbeiten mit Computer und Internet
- Tabellenkalkulation

Uni Tuebingen

Kursübersicht

Suchen

Version Office 2021



Wissenschaftlich Arbeiten mit Microsoft Excel

4. Programmieren mit Excel - Makros und VBA

Excel weist eine Programmierschnittstelle auf. Um die ganze Funktionalität der Programmierung in Excel darzustellen reicht ein Kurstag nicht aus. Wir werden uns daher auf einen Einstieg beschränken. Ein ZEN-Spruch lautet: "Bei Dunkelheit bringt die erste Kerze die größte Helligkeit"


Programmieren wozu?



Microsoft Excel ist seit der Version Excel 95 mit der Skriptsprache Visual Basic for Applications - VBA ausgestattet.
Dadurch können in Excel Arbeitsschritte programmiert werden.

Kennzeichen von VBA

(siehe Wikipedia)
  • + Leistungsfähige Skriptsprache. Bis VisualBasic 6.0 war sogar ein Austausch von Modulen zwischen VBA und der eigenständigen Programmiersprache VB von Microsoft möglich.

  • ++ Weitverbreitet. VBA ist in allen MS Office-Produkten zu finden. In industriellen Bereichen von Groß- und mittelständischen Unternehmen ist VBA das Mittel, um schnell und effizient kleinere IT-Lösungen zu realisieren.

  • - Interpretierte Programmiersprache (es wird nicht alles kompiliert, d.h. in Maschinensprache übersetzt). Daher ist die Geschwindigkeit im Vergleich zu anderen Programiersprachen viel niedriger. Das macht sich bei der Auswertung von umfangreichen Tabellen bemerkbar.

  • - Verwirrende Syntaxvielfalt und Spaghetticode. VBA sollte zu früheren Basic-Dialekten Ähnlichkeiten besitzen. VBA ist durch eine prozedurale Programmierung gekennzeichnet (es gibt keine Vererbung, wie es mit gängiger Programmierung heute möglich ist)

  • -- Auslaufmodell. VBA wird von dem neuen Office 2007 und 2010 weiterhin unterstützt. Es bestehen aber Überlegungen bei Microsoft, VBA langfristig durch eine .NET-basierte Technik (System-Frameworks .NET sprich "dotnet"). zu ersetzen. Office 2007 kann durch ein Add-Inn das Visual Studio Tool VSTO beigebracht werden ("Visual Studio 2005 Tools for Office Second Edition” or “VSTO 2005 SE”).
    In Mac Office 2008 läuft VBA nicht mehr. Allerdings ist VBA in dem neuen Mac Office 2011 wieder enthalten. Es besteht Unsicherheit ob VBA auch langfristig von den Office Programmen unterstützt werden! Daher ist es empfhlenswert grössere Projekte nicht mehr in VBA zu schreiben.

  • --- Sicherheit. VBA unterliegt keinem ausgefeiltem Sicherheitssystem. Daher ist standardmäßig das Ausführen von Makros in Word und Excel eingeschränkt.
    Selbst mit geringen Programmierkenntnissen kann Schadcode (sog. Makroviren) mit nahezu beliebig großer Schadwirkung (Keylogger oder andere Spyware zu Datenspionage oder Betrugszwecken, Dialerbetrug, Spam, Zerstörung der Installation) erstellt werden.

    Daher benötigen Benutzer von Microsoft Office, die Makros einsetzen wollen, unbedingt einen Virenscanner, der Makroviren erkennt. Ansonsten ist es ratsam die Ausführung von Makros unmöglich zu machen.
    (Office 2003: Menü Extra : Optionen... : Sicherheit : Makrosicherheit...)
    In Office 2007 Dateien lassen sich aus diesem Grund keine Makros mehr abspeichern. Soll eine Excel-Datei mit VBA laufen, dann muss die Datei als Excel-Arbeitsmappe mit Makros (*.xlsm) abgespeichert werden.


    Excel-Arbeitsmappe mit Makros (*.xlsm)

    Abb.: Dialogfenster Speichern unter. Excel mit Makros als Dateityp xlsm abspeichern




Informationen zu Visual Basic for Applications - VBA

http://de.wikipedia.org/wiki/Visual_Basic_for_Applications


WikiBooks

http://de.wikibooks.org/wiki/VBA_in_Excel_-_Grundlagen


Skript zum Selberlernen (148 Seiten) von Josef Broukal

http://www.excel-werkstatt.de/index.php?site=download/download&kat=16&anzeigedl=36


Beispiele für VBA Programme

http://www.arstechnica.de/

http://www.held-office.de/


Literatur

Bernd Held, 2004, VBA mit Excel, Markt + Technik, ISBN 3-8272-6930X, 7,95 Eur
als preiswerter Einstieg

Bernd Held, 2010, Excel-VBA, Mit über 1000 Makros für Excel 2000 bis 2010, Markt + Technik, ISBN 978-3-8272-4534-2, 49,95 Eur
Nachschlagewerk, viele Beispiele


Michael Kofler und Ralf Nebelo, 2007, Excel 2007 programmieren, Addison-Wesley, 49,95
auch ein Nachschlagewerk, viele Hintergründe





4.1 Makrorekorder

Der Makrorekorder ist ein Assistent in Excel, der hilft Arbeitsabläufe in einem Programm (Makro) aufzuzeichnen. Das Makro wird in VBA geschrieben. Das Makro lässt sich daher anschliessend weiterbearbeiten und verändern.

Die Menüband Entwicklertools ist standardmassig nicht aktiviert. Diese Multifunktionsleiste wird für Makros und VBA benötigt. Zum Aktivieren des Menübandes Entwicklertools das Dialogfenster Excel-Optionen aufrufen

  • Menüband Datei : Excel-Optionen

  • Register Menüband anpassen

    Excel-Otionen. Aktivieren der Entwicklertools


  • Rechte Spalte Menüband anpassen, dort Menüband Entwicklertools aktivieren



Ein Makro aufzeichnen:

Ein Makro kann einfach "aufgezeichnet" werden. Dazu das Makro starten. Die gewünschten Aktionen in Excel durchführen und das Makro beenden. Das Makro ist nun in der Arbeitsmappe gespeichert und kann anschließend bearbeitet werden.

  • Multifunktionsleiste Entwicklertools Gruppe Code Symbol Makro aufzeichnen

    Multifunktionsleiste Entwicklertools Gruppe Code

    Abb.: Menüband Entwicklertools Gruppe Code


    Makro aufzeichnen
    Abb.: Dialogfenster Makro aufzeichen


  • Makroname eingeben. Eine nicht benutzte Tastenkombination verwenden. Schließlich kann eine Beschreibung eingefügt werden. Und auf OK drücken.

  • Jetzt kann das Makro aufgezeichnet werden bis das Symbol "Aufzeichnung beenden" gedrückt wird.
    Das Symbol Relative Aufzeichnung bestimmt ob relativ oder absolute Bezüge genommen werden!


    Makro aufzeichnen beenden
    Abb.: Symbolleiste Aufzeichnung beenden, siehe auch das Symbol Relative Aufzeichnung.


Bemerkung:

Der Code, den der Makrorecorder liefert ist nicht sehr elegant und"schnell". Der Macrorecorder arbeitet mit Selektieren.
Reverenzieren ist in einem Programm schneller. Siehe Diskussion Selektieren und Reverenzieren.

Beispiel Selektieren

Sub SelektiertKopieren()
Dim wkb As Workbook
Set wkb = ActiveWorkbook
Workbooks("Test1").Activate
Worksheets("Tabelle1").Select
Range("F10").Select
Selection.Copy
Workbooks("Test2").Activate
Worksheets("Tabelle2").Select
Range("B5").Select
ActiveSheet.Paste Destination:=ActiveCell
wkb.Activate
Application.CutCopyMode = False
End Sub


Das gleiche Beispiel als Reverenzieren

Sub ReferenziertKopieren()
Workbooks("Test1").Worksheets("Tabelle1").Range("F10").Copy _
Workbooks("Test2").Worksheets("Tabelle2").Range("B5")
Application.CutCopyMode = False
End Sub

Quelle Wikibooks.org




Beispiel

Aufgabe:

Tastenkombination Strg + k (ist ursprünglich Hyperlink einfügen) im Dialogfeld Makro aufzeichnen eingeben. Symbol "Relativer Verweis" aktivieren.

Während der Aufzeichnung in eine Zelle neben dem Messwert C4 klicken und "=C4*2" als Formel eingeben. Anschliessend die Aufzeichnung beenden.

Anschliessend neben einem beliebigen Messwert Strg+ k mit der Tastatur drücken.



Das Ergebnis der Makroaufzeichnung bearbeiten.

  • Menüband Entwicklertools Gruppe Code Symbol Makros

    Makro bearbeiten
    Abb.: Dialogfenster Makro

  • Schaltfläche Bearbeiten



    Visual Basic Editor
    Abb.: Visual Basic Editor - Bearbeiten des aufgezeichneten Makros.



Aufgabe:

Gebe eine Kommentarzeile ein. Kommentar fängt mit ' an.


Wie kann ein Makro gestartet werden?

1.Multifunktionsleiste Makro
  • Menüband Entwicklertools Gruppe Code Symbol Makros

  • In der Liste das gesuchte Makro auswählen und auf das Symbol Ausführen klicken


2.Tastenkombination
  • Vorher festgelegte Tastenkombination verwenden (Im Beispiel Strg + k)




3. Schnellzugriff
  • Menüband Datei : Excel-Optionen

  • Register Symbolleiste für den Schnellzugriff

    Schnellzugriffsleiste anpassen

    Abb.: Schnellzugriffsleiste anpassen.




  • Gewünschtes Makro hinzufügen

  • Das Makro kann in der Schnellzugriffsleiste aufgerufen werden.

    Makro in der Schnellzugriffsleiste



4. Schaltfläche
  • Schaltfläche in die Tabelle einfügen

    • Menüband Entwicklertools Gruppe Steuerelemente Symbol Einfügen

    • Formularsteuerelemente Schaltfläche

      Makro in der Schnellzugriffsleiste
      Abb.: Schaltfläche einfügen.


    • Eine Schaltfläche auf das Tabellenblatt aufziehen.

    • Dialogfenster Makro zuweisen und dort auf das gewünschte Makro klicken.

    • Schaltfläche sinnvoll benennen.



Beispiel Makro erstellen und bearbeiten.

Wir wollen ein Makro schreiben, das automatisch zu einer Liste den Mittelwert und die Standardabweichung einfügt.

  • Öffne eine leere Excel-Tabellenmappe.

  • Gebe in einer Spalte (A3:A10) Zahlenwerte ein.

  • Mauszeiger auf A11.

  • Makroaufzeichnung starten...

  • Mit Hilfe der Funktion Mittelwert den Mittelwert ausrechnen.

  • Mauszeiger auf das Feld daneben (B11). "Mittelwert" einfügen und fett formatieren.

  • Mauszeiger auf das Feld A12. Standardabweichng einfügen und im Feld daneben B12 mit "Standardabweichung" bezeichnen. Auch diese Feld fett formatieren.

  • Makroaufzeichnung beenden.


Bitte teste den Code in einer anderen Spalte. Warum geht das nicht? Bearbeite den Code so das unter jeder beliebigen Spalte Mittelwert und Standardabweichung eingefügt wird.

Hinweis: Aufzeichnung standardmaßig mit absoluten Bezügen. Bei der Aufzeichnung kann durch Aktivieren des Symbols Relative Aufzeichnung (s. o.) relative Bezüge eingestellt werden.

ActiveCell.Offset(1, 0).Select
bewegt die aktive Zelle relativ um eine Zeile nach unten.

ActiveCell.Offset(0, 1).Select
bewegt die aktive Zelle relativ um eine Spalte nach rechts.

Lösungsbeispiel




4.2 Visual Basic Editor

Excel enthält einen umfangreiche Entwicklungsumgebung für Visual Basic for Applications, den Visual Basic Editor.

  • Multifunktionsleiste Entwicklertools Gruppe Code Symbol Visual Basic oder ALT + F11 als Tastenkombination.



Visual Basic Editor
Abb.: Visual Basic Editor.


Im Fenster links oben wird der Projekt-Explorer gezeigt. Hier werden die geöffneten Arbeitsmappen, Module und UserForms gezeigt.

Im Eigenschaftsfenster darunter sind die dazugehöigen Eigenschaften der Objekte im Projekt-Explorer dargestellt.



Aufgabe
Öffne eine leeres Arbeitsblatt.

Schränke den Bereich in der Tabelle 1 ein, den man bearbeiten kann. Dazu im Eigenschaftsfenster "ScrollArea" einen Bereich eingeben (z.B. $A$1:$D$5). Versuche in Tabelle 1 außerhalb dieses Bereiches etwas einzugeben.




Beispiel zur Demonstration von Lokal-Fenster, Überwachungsfenster, Direktfenster bei Einzelprozedurschritten

Der VBA - Editor bietet viele Werkzeuge für die Überprüfung eines Codes.

- Öffne die Datei und mit Alt + F11 den VBA-Editor


- Öffne das Lokal-Fenster. Hier werden die Werte der Variablen während der Einzelprozedurschritten angezeigt.

Menü Ansicht : Lokal-Fenster

- Starte einen Einzelschritt

Menü Debuggen : Einzelschritt

Wird es jetzt klarer wie der Code funktioniert?


Setze das Überwachungsfenster ein:

Menü Ansicht : Überwachungsfenster

Menü Debuggen Überwachung hinzufügen

Öffne das Direktfenster
Menü Ansicht : Direktfenster

Im Code kann ein Wert in das Direktfenster mit

Debug.Print <Wert>

geschrieben werden.

Versuche in unserem Beispiel Debug.Print strOrt. Schreibe "Debug.Print strOrt" in eine geeignete Stelle im Code und beobachte das Direktfenster.




Grundlagen

Ein Modul einfügen

  • Menü Einfügen : Modul oder mit der rechten Maustaste im Projekt-Explorer

  • Einfügen : Modul



Visual Basic Editor Modul einfügen
Abb.: Visual Basic Editor - neues Modul einfügen.




Prozeduren

VBA ist eine prozedurale Programmiersprache. Programme sind in kleinen voneinander getrennten Programmierteilen sogenannten Prozeduren geschrieben. Ein Unterprogramm ("subroutine" - Sub) kann keinen Wert zurückgeben. Dazu werden Funktionen eingesetzt. In VBA werden Prozeduren in Module abgespeichert, in Module sind ein oder mehrere zusammengehörende Prozeduren zusammengefasst.

Funktion

Benutzerdefinierte Funktionen (UDF) siehe Link
  • Funktionen werden mit einem Parameter aufgerufen.
  • Arbeiten mit einem Rückgabewert.
  • Es lassen sich keine Formatierungen mit Funktionen erstellen oder verändern.
  • Können von einem Excel-Arbeitsblatt aufgerufen werden.

Sub

  • Prozeduren werden mit oder ohne Parameter aufgerufen.
  • Geben keine Werte zurück.
  • Können über andere Prozeduren,aufgerufen werden, nicht jedoch über eine Eingabe im Excel-Arbeitsblatt (-> dann wird eine Funktionen verwendet).
  • Sie können Excel- und VBA-Funktionen integrieren.



Aufbau Funktion

Function Funktionsname(Parameter1,Parameter2) As Integer
'Kommentar
..Code..

Funktionsname=...

End Function



Bemerkung:
Mit Funktionsname=... wird der Rückgabewert definiert.




Aufbau Sub

Sub Subname()
'Komentar
...Code...

End Sub



Ein wesentlicher Teil des VBA Codes sind Objekte und Methoden.

object.method

Übertragen könnte man das z.B. so erläutern. Auto ist das Objekt und faehrt ist die Methode

auto.faehrt



Prozeduraufrufe

Prozeduren werden mit "Call" aufgerufen. Call ist optional, es gehört aber zu einer guten Programmierung call zu verwenden.

Call Unterprogramm

Programm aus einem anderen Klassenmodul aufrufen:

Call Tabelle1.Unterprogramm

Prozeduren in anderen Arbeitsmappen oder Anwendungen werden mit "Run" gestartet.

Run "'Mappe1'!Messung1"

Die mit Run aufgerufene Arbeitsmappe wird - wenn nicht geöffnet - im aktuellen Verzeichnis (CurDir) gesucht. Aufrufe in anderen Verzeichnissen geht nicht!



Variablen

Variablen sind Platzhalter für Zeichenfolgen, Werte und Objekte. Sie können Werte oder Objekte enthalten.

Abhängig vom Ort und der Art ihrer Deklaration werden ihre Gültigkeit und die Lebensdauer ihrer Werte festgelegt.

  • Deklaration innerhalb einer Prozedur
    Gültigkeit ausschließlich für diese Prozedur und kann aus anderen Prozeduren nicht angesprochen werden.
  • Deklaration im Modulkopf
    Die Variable gilt für alle Prozeduren dieses Moduls, eine Weitergabe als Parameter ist nicht notwendig.
  • Deklaration im Modulkopf eines Standardmoduls als Public
    Die Variable gilt für alle Prozeduren der Arbeitsmappe, soweit das die Prozedur enthaltene Modul nicht als Private deklariert ist.



Variablentypen

Die gebräuchlichen Variablentypen:

Variablentyp Namenskonvention Res.Speicherplatz Kurzbezeichnung Beschreibung
Boolean bln 16 Bit, 2 Bytes    WAHR (1) oder FALSCH  (0)
Integer int 16 Bit, 2 Bytes % -32.768 bis 32.767
Long  lng 32 Bit, 4 Bytes & -2.147.483.648 bis 2.147.483.647
Currency cur   @ -922.337.203.685.477,5808 bis 922.337.203.685.477,5807
Single sng 32 Bit, 4 Bytes ! -3,402823E38 bis -1,401298E-45 für negative Werte und von 1,401298E-45 bis 3,402823E38 für positive Werte
Double dbl 64 Bit, 8 Bytes # -1.79769313486231E308 bis -4,94065645841247E-324 für negative Werte und von 4,94065645841247E-324 bis 1,79769313486232E308 für positive Werte
Date dat 64 Bit, 8 Bytes   Datum und Zeit
String str   $ Zeichenfolgen
Object obj 32 Bit, 4 Bytes   Objekte
Variant var 128 Bit, 16 Bytes   Alle Typen, Voreinstellung
benutzerdefinierter Typ typ     ein oder mehrere Elemente jeden Datentyps
Objekttyp       Objekte wie Workbook, Range

Quelle wikibooks.org



Variablendeklaration

Variablen sollten - müssen aber nicht deklariert und dimensioniert werden (ist aber gute Programmiertechnik).
Deklariert wird mit "DIM" zu Anfang des Moduls oder Prozedur. Dimensioniert wird mit "As" dem Variablentyp oder einer Kurzbezeichnung (siehe Tabelle oben)

Dim Variablenname as Variablentyp

Beispiel
Dim wert1 As Double

Dim wert1%

Mit der Anweisung zu Beginn des Moduls
Option Explicit
weigert sich Excel ein Programm auszuführen in dem nicht alle Variablen mit "DIM" deklariert werden.

Wenn unter Menü : Extra : Optionen : Editor die Option Variablendeklaration erforderlich aktiviert ist, fügt Excel in jedes neues Modul Option Explicit in den Code ein.

Ohne Deklaration und Dimensionierung gilt der Variablentyp Variant
Nachteil:
benötigt den größten Speicherplatz mit 16 Bytes
während des Programmablaufes kann der Typ mehrmals wechseln, was möglicherweise die Programmausführung erschwert.
Variant-Variablen benötigen erheblich längere Berechnungszeiten als andere.

Konstante

Wenn sich der Wert einer Variablen nicht ändert, kann die Variable als Konstante deklariert werden. Vorteil: Konstanten werden in VBA-Programmen schneller berechnet als Variablen. Konstanten werden generell im Allgemein-Abschnitt von Modulen deklariert

Const Konstantenname as Variablentyp

Beispiel
Const conWert As Integer = 2




Abfragen und Schleifen

Abfragen If..Then

Einzeilige Abfrage

If Bedingung Then [Anweisung] [Else Anweisung]

Bedingung = Ausdruck der wahr oder falsch ist.

Beispiel:

Sub WennDann_1()
'Einzeilige If--Then
EinDatum = ActiveCell.Value
If EinDatum <> Now Then EinDatum = Now
ActiveCell.Value = EinDatum
End Sub

Mehrzeilige Abfrage

If Bedingung Then
[Anweisung]
[ElseIf condition-n Then
[elseifAnweisung] ...

[Else
[elseAnweisung]]
End If

Sub WennDann2()
'Mehrzeiliges If--Then
Dim x, y
x = ActiveCell.Value

If x < 10 Then
y = "kleiner 10"
ElseIf x < 100 Then
y = "größer 10 und kleiner 100"
Else
y = "grösser 100"
End If

MsgBox ("Die Zahl ist " & y)


End Sub

Beispiel für ein einfaches Makro mit Inputbox

Sub Potenzieren()
'Potenziert eine eingegeben Zahl

Dim i As Single
Const pot = 2

i = InputBox("Geben Sie eine Zahl ein!", "Eingabe")
If i = 0 Then Exit Sub
i = i ^ pot
MsgBox ("Das Ergebnis lautet: " & i)

End Sub




Aufgabe

Tippe das Beispiel ab!
Der Editor hilft bei der Eingabe! Mit der Tab-Taste wird der Vorschlag des Quickinfos übernommen.
Versuche das VisualBasic Hilfesystem. Z.B. MsgBox markieren und auf F1 drücken.

Informiere dich in der Hilfe über verschiedene Möglichkeiten der InputBox und MsgBox.



Eingebaute Dialoge sind

InputBox

MsgBox

weitere Eigenschaften der Dialoge siehe VBA-Hilfe und Objektkatalog

  • VBA-Editor öffnen Alt + F11
  • Menü Ansicht : Objektkatalog
  • Klasse xlBuiltinDialog



Zum Ausführen des Makros verwende Menü Makro : Makros...

Oder siehe oben über die verschiedenen Möglichkeiten ein Makro auszuführen.

In dem Arbeitsblatt kann für das bequeme Ausführen eine Schaltfläche eingeführt werden (s.o. Symbolleiste für den Schnellzugriff)



Select Case

Im Gegensatz zu If..Then..Else arbeitet Select Case nur mit einem Testausdruck dessen Wert in beliebig vielen Case-Anweisungen untersucht werden kann.

Select Case Testausdruck
[Case liste
[Anweisung]] ...
[Case Else
[Anweisung]]
End Select

Beispiel:

Sub SelectCase()
Dim x
x = ActiveCell.Value
Select Case x
Case 1 To 5
MsgBox ("Die Zahl ist zwischen 1 und 5")
Case 6, 7, 8
MsgBox ("Die Zahl ist zwischen 6 und 8")
Case 9 To 10
MsgBox ("Die Zahl ist zwischen 9 und 10")
Case Else
MsgBox ("Nicht zwischen 1 und 10")
End Select
End Sub



Wann sollte welche Verzweigung gewählt werden?

Wann setze ich IF..Then..ElseIf und wann Select Case ein?

Select Case setzt voraus, dass ein Ausdruck eines Vergleiches mit allen anderen verglichen wird, und der sollte in der Zeile mit Select Case auftauchen. Damit eignet es sich beispielsweise zur Abfrage von Optionsfeldern, zur Abfrage von Bereichen (siehe Beispiel oben).

If..Then..ElseIf erlaubt es, völlig unterschiedliche Vergleiche auszuführen. If..Then..ElseIf eignet sich beispielsweise für Plausibilitätsabfragen am Anfang einer Funktion. Hier werden die Eingabedaten auf oft völlig unterschiedliche Kriterien geprüft, aber wenn nur eines erfüllt ist, gibt es eine spezielle Fehlermeldung.



Programmschleifen

Mehrfaches Ausführen eines Programmcodes mit Hilfe einer Programmschleife

Do ..Loop

Kopfgesteuerte Schleife:

Do [{While | Until} Bedingung]
[Anweisung]
[Exit Do]
[Anweisung]
Loop

Oder eine fussgesteuerte Schleife

Do
[Anweisung]
[Exit Do]
[Anweisung]
Loop [{While | Until} Bedingung]

Mit While wird eine Schleife durchlaufen solange die Bedingung erfüllt ist (Bedingung = True). Dagegen wird mit Until die Schleife durchlaufen solange die Bedingung nicht erfüllt wird (Bedingung = Falsch).

Beispiel "Divisor"

Sub werteDividieren()
Const divisor = 10

Sheets("Tabelle1").Activate

ActiveCell.Offset(0, -1).Select

Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 1).Value = ActiveCell.Value / divisor
ActiveCell.Offset(1, 0).Select
Loop

End Sub



Aufgabe

Öffne eine neue Excel Arbeitsmappe.

Füge diesmal den Code in VBA-Editor unter Tabelle1 ein. Steht das Programm auch in der Tabelle 2 zur Verfügung?

Fülle in Tabellenblatt Tabelle 1 eine Spalte mit Zahlen aus (A1 : A10)

Mauszeiger in B1 und das Makro ausführen.

Was macht das Programm? Diskutiere das Ergebnis. Wie könnte das Programm verbessert werden? Z.B. statt der Konstanten einen Zellbezug.

Lösungsbeispiel



While...Wend

Führt eine Serie Anweisungen aus solange die .Bedingungen wahr ist.

Syntax

While Bedingung
[Anweisung]
Wend

Beispiel

Sub Wend1()
Dim Counter
Counter = 0
While Counter < 20 ' Bedingung
ActiveCell.Offset(0, 1).Value = Counter
ActiveCell.Offset(1, 0).Select
Counter = Counter + 1 ' Stellt den Zähler höher.
Wend ' Endet wenn der Zähler > 19.
End Sub

For...Next

For--Next wird benutzt wenn die Anzahl der Schleifendurchgänge zu Beginn der Schleife bekannt ist.

For Zähler = start To Ende [Schritte step]
[Anweisung]
[Exit For]
[Anweisung]

Next [Zähler]

Schritte sind optional

Beispiel "Text und Zahlen trennen"

In einer Zelle sollen die Zahlen herausgefiltert werden

z.B. "72076 Tübingen" -> "72076"

"BT4578376xck" -> "457837"

Die Aufgabe ist als Funktion gelöst! Siehe Eigene Funktion schreiben

Function BuchstRaus(zelle)
Application.Volatile
Dim i As Integer
For i = 1 To Len(zelle)
Select Case Asc(Mid(zelle, i, 1))
Case 0 To 64, 123 To 197
BuchstRaus = BuchstRaus & Mid(zelle, i, 1)
End Select
Next i
End Function

Anmerkung: Application.Volatile löst eine Neuberechnung aus, wenn in irgendeinem Blatt irgendeiner Arbeitsmappe irgendein Wert eingegeben wird.



Aufgabe

Wie kann ein Programm aussehen, das die Zahlen aus einer Tabellenzelle beseitigt?



Lösungsbeispiel

Als Prozedur sieht das Program so aus

Sub BuchstRaus()
'Application.Volatile
Dim i As Integer
Dim zelle
Dim x

ActiveCell.Offset(0, -1).Select
zelle = ActiveCell.Value
For i = 1 To Len(zelle)
Select Case Asc(Mid(zelle, i, 1))
Case 0 To 64, 123 To 197
x = x & Mid(zelle, i, 1)
End Select
Next i

ActiveCell.Offset(0, 1).Select
ActiveCell.Value = x
End Sub




Achtung: Funktion und Prozedur können nicht den gleichen Namen haben.




4.3 Eigene Dialoge gestalten und programmieren (UserForm)

Mit VBA können eigene Fenster ("UserForm" oder "Benutzerformular") programmiert werden.

Für eine automatisierte Eingabe z.B. für Messreihen ist es sinnvoll Eingabemasken zu erstellen. Die eingebauten Eingabemasken (Inputbox) reichen oft nicht aus. Es besteht die Möglichkeit eigene Eingabemasken zu entwerfen.



Einfaches Beispiel

1. Tabelle einrichten

  • Tabelle in Tabellenblatt "Tabelle1" .

2. UserForm erstellen

  • Visual Basic Editor öffnen Alt+F11

  • Einfügen UserForm
    Name ist "UserForm1"

  • Ziehe ein Bezeichnungsfeld auf das Formular und
    gebe im Eigenschaftsfenster unter Caption den Titel
    "Einfaches Beispiel" ein.
    Stelle Schriftart auf "Arial und Schriftgrösse auf 14.
    (Siehe unter Font ...)

  • Füge ein Textfeld hinzu
    Name ist "Wert1"


  • Füge eine Befehlsschaltfläche (Button) hinzu.
    Name ist "Eingeben"
    Caption ist "Wert eingeben"

    Doppelklick auf die Schaltfläche im Userformular, nun kann Code eingegeben werden

    Private Sub Eingeben_Click()
    ActiveCell.Value = UserForm1.Wert1.Value
    ActiveCell.Offset(1, 0).Select
    End Sub

  • Jetzt braucht man noch eine Prozedur, die das Formular erscheinen lässt

    Sub UserFormAnzeigen()
    'Formulat anzeigen
    UserForm1.Show
    End Sub







Ausführliches Beispiel

Beispiel so soll es aussehen (geht nur mit Windows Excel)



Eingabeformular mit Hilfe von VBA.
Abb.: Eingabeformular mit Hilfe von VBA.



Aufgabe

Bitte leeres Tabellenblatt öffnen und folgende Anweisung durchführen.

1. Tabelle einrichten

  • Tabelle in Tabellenblatt "Tabelle1" so wie in Abbildung (s.o.) eingeben.

2. UserForm erstellen

  • Visual Basic Editor öffnen Alt+F11

  • Einfügen UserForm

  • TextBoxen, ComboBox, Bezeichnungen und Überschriften. Layout nach belieben.
    wert1 - Textbox
    wert2 -ComboBox, RowSource - $g$2:$g$4, ListRows - 3
    wert3- TextBox

  • Schaltfläche "WertEingeben"

  • Doppelklick auf die Schaltfläche und folgenden Code eingeben


    Private Sub WertEingeben_Click()
    'Werte werden in die Tabelle übertragen
    'Frm als Abkürzung für UserForm1
    Set Frm = UserForm1
    'Tabellenblatt "Tabelle1" wird aktiviert
    Sheets("Tabelle1").Activate
    'End ermittelt die letzte Position am Ende
    Range("B65536").End(xlUp).Offset(1, 0).Select
    With Frm
    'Wert wird in die Tabelle eingetragen
    ActiveCell.Value = .wert1.Value
    'Um eine Position nach rechts
    ActiveCell.Offset(0, 1).Value = .wert2.Value
    ActiveCell.Offset(0, 2).Value = .wert3.Value
    End With
    End Sub


  • Schaltfläche "Abrechen" mit folgendem Code


    Private Sub Abrechen_Click()
    'Eingabemaske ausblenden
    UserForm1.Hide
    End Sub


  • Schaltfläche "Reset" mit folgendem Code

    Private Sub FelderLoeschen_Click()
    Dim felder As Object
    For Each felder In UserForm1.Controls
    If TypeName(felder) = "TextBox" Then felder.Text = ""
    If TypeName(felder) = "ComboBox" Then felder.Text = ""
    Next felder
    End Sub

  • Modul 1 mit folgendem Code


    Sub UserFormAnzeigen()
    'Formular anzeigen
    UserForm1.Show
    End Sub



3. Schaltfläche in das Tabelleblatt Tabelle1 einfügen und mit dem Makro UserFormAnzeigen verbinden.

Das Formular kann mit F5 aus dem VBA-Editor heraus getestet werden.

Reihenfolge der Felder im Formular kann mit Menü Ansicht : Aktivierungsreihenfolge festgelegt werden.





4.4 Zugriff auf andere Dateien - Projekt Spalten aus anderen Dateien einfügen

Beispieldatei mit Makro (nur Windows Computer)

Textdatei1

Textdatei2



4.5. Beispiel Projekt

Beispiel für den Strandurlaub

Ein Sudoku mit 81 Feldern erstellen

Quelle: Jörg Lorenz http://www.vba-beispiele.de/office.php?was=1311#anker552

Sub sudoku()
Dim intS As Integer, intS1 As Integer
Dim lngZ As Long, lngZ1 As Long
Dim intZaehler As Integer
Dim bolVorhanden As Boolean
Dim bytZ As Byte, bytI As Byte
Dim lngBeginn As Long, lngEnde As Long
Dim intBeginn As Integer, intEnde As Integer
Dim strRange As String
Dim objZelle As Object
Dim bytMaximum As Byte
Dim intVersuche As Integer, intVersuchsZaehler As Integer

bytMaximum = 9
intVersuche = 600

lngBeginn = 10
lngEnde = 18
intBeginn = 2
intEnde = 10
Cells(3, 11).ClearContents
Cells(2, 11) = Now
Cells(2, 11).NumberFormat = "h:mm:ss"
intVersuchsZaehler = 0
VONVORN:
intVersuchsZaehler = intVersuchsZaehler + 1
Cells(1, 11) = intVersuchsZaehler
intZaehler = 0
Range(Cells(10, 2), Cells(18, 10)).Clear
'Alle Zeilen durchlaufen:
For lngZ = lngBeginn To lngEnde
    'In der Zeile jede Zelle ausfüllen
    For intS = intBeginn To intEnde
        'So lange Zufallszahl bilden, bis bolVorhanden False bleibt:
        Do
            intZaehler = intZaehler + 1
            bytZ = Int((bytMaximum * Rnd) + 1)
            bolVorhanden = False
            'Prüfung des kleinen Quadrates:
            strRange = ""
            If lngZ >= 10 And lngZ <= 12 And intS >= 2 And intS <= 4 Then
                strRange = Range(Cells(10, 2), Cells(12, 4)).Address(False, False)
            ElseIf lngZ >= 10 And lngZ <= 12 And intS >= 5 And intS <= 7 Then
                strRange = Range(Cells(10, 5), Cells(12, 7)).Address(False, False)
            ElseIf lngZ >= 10 And lngZ <= 12 And intS >= 8 And intS <= 10 Then
                strRange = Range(Cells(10, 8), Cells(12, 10)).Address(False, False)
            
            ElseIf lngZ >= 13 And lngZ <= 15 And intS >= 2 And intS <= 4 Then
                strRange = Range(Cells(13, 2), Cells(15, 4)).Address(False, False)
            ElseIf lngZ >= 13 And lngZ <= 15 And intS >= 5 And intS <= 7 Then
                strRange = Range(Cells(13, 5), Cells(15, 7)).Address(False, False)
            ElseIf lngZ >= 13 And lngZ <= 15 And intS >= 8 And intS <= 10 Then
                strRange = Range(Cells(13, 8), Cells(15, 10)).Address(False, False)

            ElseIf lngZ >= 16 And lngZ <= 18 And intS >= 2 And intS <= 4 Then
                strRange = Range(Cells(16, 2), Cells(18, 4)).Address(False, False)
            ElseIf lngZ >= 16 And lngZ <= 18 And intS >= 5 And intS <= 7 Then
                strRange = Range(Cells(16, 5), Cells(18, 7)).Address(False, False)
            ElseIf lngZ >= 16 And lngZ <= 18 And intS >= 8 And intS <= 10 Then
                strRange = Range(Cells(16, 8), Cells(18, 10)).Address(False, False)
            
            End If
            If strRange <> "" Then
                For Each objZelle In Range(strRange)
                    If objZelle = bytZ Then bolVorhanden = True
                Next
            End If
            'Zeile prüfen:
            For intS1 = intBeginn To intS - 1
                If Cells(lngZ, intS1) = bytZ Then bolVorhanden = True
            Next
            'Spalte prüfen:
            For lngZ1 = lngBeginn To lngZ - 1
                If Cells(lngZ1, intS) = bytZ Then bolVorhanden = True
            Next
            If intZaehler = intVersuche Then GoTo VONVORN
        Loop Until bolVorhanden = False
        Cells(lngZ, intS) = bytZ
    Next
Next
For bytI = 2 To 10: Columns(bytI).ColumnWidth = 2.86: Next
Rows("10:18").RowHeight = 18.75
With Range(Cells(lngBeginn, intBeginn), Cells(lngEnde, intEnde))
    .HorizontalAlignment = xlCenter
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideHorizontal).Weight = xlThin
End With
With Range(Cells(10, 2), Cells(12, 4))
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideHorizontal).Weight = xlThin
End With
With Range(Cells(10, 5), Cells(12, 7))
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideHorizontal).Weight = xlThin
End With
With Range(Cells(10, 8), Cells(12, 10))
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideHorizontal).Weight = xlThin
End With
With Range(Cells(13, 2), Cells(15, 4))
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideHorizontal).Weight = xlThin
End With
With Range(Cells(13, 5), Cells(15, 7))
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideHorizontal).Weight = xlThin
End With
With Range(Cells(13, 8), Cells(15, 10))
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideHorizontal).Weight = xlThin
End With
With Range(Cells(16, 2), Cells(18, 4))
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideHorizontal).Weight = xlThin
End With
With Range(Cells(16, 5), Cells(18, 7))
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideHorizontal).Weight = xlThin
End With
With Range(Cells(16, 8), Cells(18, 10))
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideHorizontal).Weight = xlThin
End With
Cells(3, 11) = Now
Cells(4, 11) = Cells(3, 11) - Cells(2, 11)
Cells(3, 11).NumberFormat = "h:mm:ss"
Cells(4, 11).NumberFormat = "h:mm:ss"
End Sub







Dieter Steinmetz, Universität Tübingen, ZMBP - Kursübersicht - Im Skript suchen