Beispiele mit VBA

Animiertes GIF1

Bei Öffnen der Arbeitsmappe wird eine animierte .GIF-Datei für 10 Sekunden angezeigt. Dafür wird das Steuerelement Webbrowser aus der Symbolleiste Steuerelement-Toolbox im Zusammenhang mit einem Timer verwendet.

Hinweis: bitte ZIP-Datei vor Öffnen der Excel-Arbeitsmappe entpacken.

Änderungen:

1. 30.04.07 Code überarbeitet

2. 30.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Animiertes GIF2

Bei Öffnen der Arbeitsmappe wird eine animierte .GIF-Datei angezeigt. Dafür wird das Steuerelement Webbrowser aus der Symbolleiste Steuerelement-Toolbox verwendet. Bei Doppelklick in eine beliebige Zelle der Tabelle wird die Anzeige unterbrochen bzw. fortgesetzt.

Hinweis: bitte ZIP-Datei vor Öffnen der Excel-Arbeitsmappe entpacken.

Änderungen:

1. 30.04.07 Code überarbeitet

2. 30.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Array dynamisch

Unter Verwendung eines Arrays mit dynamischer Obergrenze werden Adresse und Füllfarbe der nicht gesperrten Zellen aller Tabellen ausgelesen und die Zellen mit einer im Code festgelegten einheitlichen Füllfarbe formatiert.

Änderungen:

1. 14.04.07 Code für das Zurücksetzen der Füllfarbe ergänzt

2. 14.04.07 Kommentare im Code ergänzt

3. 24.06.07 Beispiel für Version 2007 ergänzt

Stand 24.06.07

Autofilter dynamisch

Die Begriffe für die Filterauswahl werden in einer Zeile oberhalb des Filterbereichs eingegeben. Mittels Worksheet_Change Ereignis wird der Autofilter automatisch gesetzt.  Es können Spalten eingefügt/gelöscht werden, wobei sich der Code an den Filterbereich anpasst.

Änderungen:

1. 11.03.08 Code für das Filtern nach Zahlen ergänzt

2. 31.03.08 Code für das Filtern nach Datum ergänzt

Stand 31.03.08

Bedingte Formatierung Zelladresse

Beispiele zum Ermitteln der Adressen aller Zellen des benutzten Bereichs, die mittels bedingter Formatierung formatiert sind.

Makro1: Zelladressen werden in ein Array eingelesen und dann übertragen

Makro2: Zelladressen werden direkt übertragen

Änderungen:

1. 15.04.07 Kommentare im Code ergänzt

2. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Benutzter Bereich Eckzellen

Mit der UsedRange-Eigenschaft wird der benutzte Bereich insgesamt wiedergegeben. Zwei Makros zeigen, wie die Eckzellen aus der Gesamtadresse des benutzten Bereichs abgeleitet werden können. Das Ergebnis wird in einer MsgBox angezeigt.

Änderungen:

1. 15.04.07 Code überarbeitet

2. 15.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Benutzerdefinierte Freigabe

Bei Öffnen der Arbeitsmappe wird der Benutzername mittels Environ("Username") ausgelesen. In Abhängigkeit davon werden in Tabelle1 bestimmte, im Code festgelegte Tabellenbereiche mit unterschiedlichen Zugriffsberechtigungen versehen.

Änderungen:

1. 29.04.07 Kommentare im Code ergänzt

2. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Buchstabentausch

Zeichenfolge der aktiven Zelle wird mittels Makro in umgekehrter Reihenfolge in die darunter liegende Zelle geschrieben.

Änderungen:

1. 15.04.07 Kommentare im Code ergänzt

2. 15.04.07 Hinweis für Versionen ab 2000 ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

CheckBox vergrößert

Die Kästchen/Häkchen von Kontrollkästchen (CheckBoxen) lassen sich nicht vergrößert darstellen. Mithilfe eines Textfeldes aus der Zeichnen-Symbolleiste wird ein vergrößertes Kontrollkästchen simuliert.

Stand 03.02.10

ContolTipText

nicht für Version ab 2007

Die Steuerelemente in der Tabelle besitzen keinen ControlTipText wie z.B. Elemente in der Symbolleiste oder im UserForm.

In der Beispielarbeitsmappe wird eine Lösung für CommandButtons aus der Steuerelement-Toolbox unter Verwendung des Office-Assistenten vorgestellt.

Dieses Beispiel ist nicht in Version 2007 anwendbar, da der Office-Assistent in Excel2007 nicht mehr unterstützt wird.

Stand 08.09.07

Datenübernahme

Daten aus Tabelle1 aller Arbeitsmappen eines definierten Verzeichnisses werden in die aktuelle Tabelle übernommen. Die Übernahme erfolgt dabei aus den geschlossenen Arbeitsmappen, indem Verknüpfungen zu den betreffenden Zellen erstellt werden, welche anschließend in Werte umgewandelt werden.

Änderungen

1. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Doppelte löschen

Mehrfacheinträge werden gelöscht, wenn alle Werte in den Spalten A, B und C mit der vorhergehenden Zeile übereinstimmen. Vorausgesetzt wird, dass die Tabelle sortiert ist.

Änderungen:

1. 20.04.07 Code überarbeitet

2. 20.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

DropDown Zoom

Die Schriftgröße und die Anzahl der angezeigten Listeneinträge eines DropDown-Listenfeldes aus Daten Gültigkeit sind nicht veränderbar. Mit Hilfe einer ComboBox aus der Steuerelemente-Toolbox wird ein DropDown simuliert, wobei die Schriftgröße auf 12 pkt und die Anzahl der Einträge auf 14 eingestellt werden. Die ComboBox wird bei Klick in die Zelle zur Laufzeit erstellt und bei Verlassen wieder gelöscht.

Stand 20.09.10

DropDown Zoom mehrere

Die Schriftgröße und die Anzahl der angezeigten Listeneinträge eines DropDown-Listenfeldes aus Daten Gültigkeit sind nicht veränderbar. Mit Hilfe einer ComboBox aus der Steuerelemente-Toolbox wird ein DropDown simuliert, wobei die Schriftgröße auf 12 pkt und die Anzahl der Einträge auf 14 eingestellt werden. Die ComboBox wird bei Klick auf eine Zelle im Bereich C5:C35 zur Laufzeit erstellt und bei Verlassen wieder gelöscht.

Stand 15.11.12

Druckbereiche anzeigen

In einer MsgBox werden für alle Tabelle die festgelegten Druckbereiche angezeigt.

Änderungen:

1. 21.04.07 Code überarbeitet

2. 21.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Drucken ohne Farbe

In einem Tabellenblatt sind Zellen mit einer Füllfarbe formatiert. Für den Druck der Tabelle wird die Füllfarbe aufgehoben und anschließend wieder zurück geschrieben.

Änderungen:

1. 07.05.08 Code korrigiert für das Zurücklesen

2. 07.05.08 Code für Version 2007 auf Interior.Color geändert

Stand 07.05.08

Einfügen Zellen mit Summe

In einer zweispaltigen Liste wird eine Leerzelle jeweils vor den Wertegruppen > 10, > 20, > 30 und > 40 eingefügt. Die Summe der dadurch entstehenden Zahlenblöcke wird gebildet, in die Leerzelle eingetragen und mit Schriftschnitt Fett formatiert. Alle Werte > 50 werden zu einer Gesamtsumme zusammengefasst. Die Wertetabelle muss aufsteigend sortiert sein.

Änderungen:

1. 22.04.07 Code überarbeitet

2. 22.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

E-Mail versenden

Beispiele zum Versenden von Arbeitsmappen als E-Mail-Anhang mit dem Standard-Mailprogramm

Makro1: eine mittels Inputbox abgefragte frei wählbare Tabelle wird direkt verschickt

Makro2: der Druckbereich einer im Code festgelegten Tabelle wird als Mail-Anhang vorbereitet und die Betreffzeile mittels Inputbox abgefragt. Anschließend wird der Dialog des Mailprogramms aufgerufen, sodass weiterer Text ergänzt werden kann.

Änderungen:

1. 22.04.07 Tabellenaufbau überarbeitet

2. 22.04.07 Code überarbeitet

3. 22.04.07 Kommentare im Code ergänzt

4. 12.01.07 Bespiel für Version 2007 ergänzt

5. 12.01.07 Code für Makro2 überarbeitet

Stand 12.01.08

E-Mail versenden Outlook

Beispiele zum Versenden von E-Mails mit MS Outlook

Makro1: ein markierter Bereich wird als Mailbody eingefügt

Makro2: Mail über Dialog (UserForm) versenden mit Einstellmöglichkeiten

Diese Beispielmappe wurde von Rene Holtz zur Verfügung gestellt.

Stand 09.03.08

Fortschrittbalken

In einer Spalte wird ein Fortschrittbalken angezeigt, wenn in der Nachbarzelle ein Wert zwischen 0 und 100% eingegeben wird.

Stand 30.09.07

Geburtstag einfärben

In einer Arbeitsmappe mit Geburtstagsdaten erhalten Datum und Name beim Öffnen die Füllfarbe Grün, wenn der Geburtstag auf das aktuelle Datum fällt. Die Anzeige der Namen erfolgt in einer MsgBox. Alle Geburtstage, die 10 Tage vor dem aktuellen Datum liegen, erhalten die Füllfarbe Gelb. Wird die Liste geändert, erfolgt die Formatierung ebenfalls.

Änderungen:

1. 27.04.07 Code überarbeitet

2. 27.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Geburtstag m. Bild

In einer Arbeitsmappe mit Geburtstagsdaten erhalten Datum und Name die Füllfarbe Grün, wenn der Geburtstag auf das aktuelle Datum fällt. Alle Geburtstage, die 10 Tage vor dem aktuellen Datum liegen, erhalten die Füllfarbe Gelb. Wird die Liste geändert, erfolgt die Formatierung ebenfalls. Beim Öffnen der Arbeitsmappe und per Schaltfläche aufrufbar wird ein UserForm angezeigt mit Name, Vorname, Alter und Bild für die Geburtstage am aktuellen Tag. Runde Geburtstage (10er) erhalten die Hintergrundfarbe Grün und (5er) die Hintergrundfarbe Hellgrün.

Hinweis: bitte ZIP-Datei vor Öffnen der Excel-Arbeitsmappe entpacken.

Stand 22.02.09

Hyperlinks

Verschiedene Makros zum Auslesen von Hyperlinkinformationen.

Makro1: stellt fest, ob in der aktiven Zelle ein Hyperlink enthalten ist

Marko2: prüft, ob in einer im Code definierten Zelle ein Hyperlink enthalten ist

Makro3: alle Zelladressen im Bereich A1:A20, in denen sich ein Hyperlink befindet, werden angezeigt

Makro4: im Bereich A1:A20 wird die Anzahl der Hyperlinks gezählt

Makro5: im Bereich A1:A20 enthaltene Hyperlinkadressen werden angezeigt

Makro6: der Anzeigetext der Hyperlinks im Bereich A1:A20 wird angezeigt

Änderungen:

1. 28.04.07 Code angepasst, MsgBox

2. 28.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Kalender ein Monat

2 Makros zum Auflisten der Tage für einen Monat, wobei Jahr und Monat vorgegeben werden.

Makro1: Berechnung des Monatsendes erfolgt anhand der Nummer des Monats, Berechnung des Schaltjahrs durch Umsetzung einer Tabellenformel

Makro2: Anzahl der Tage wird mittels DataSerial und dem 1. Tag des Folgemonats berechnet

Änderungen:

1. 29.04.07 Code überarbeitet, Eingabeprüfung ergänzt, Berechnung des Schaltjahres im Makro1 korrigiert

2. 29.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Kommentar mit Bild

In Spalte B wird ab Zeile 8 ein Kommentarfeld mit einem Bild erstellt, welches als Vorschau dient. Gleichzeitig werden die Namen der Bilddateien in die Zelle eingetragen und mit einem Link zur betreffenden Datei versehen.  Die Bildnamen setzen sich aus einer im Code festgelegten Sequenz und einer 5stelligen Zahl zusammen, wobei keine fortlaufende Nummerierung erforderlich ist.

Änderungen:

1. 20.02.08 Code überarbeitet, Auslesen der Namen der Bilddateien optimiert

Stand 20.02.08

Kombinationsfelder abhängig

Formular-Symbolleiste

Die Auswahl in einem Kombinationsfeld bewirkt die Zuweisung von Eingabebereich, Zellverknüpfung und Anzahl der Dropdownzeilen für ein zweites Kombinationsfeld. Die unterschiedlichen Eingabebereiche sind mittels Namen definiert. Die Anzahl der Dropdownzeilen ist in Abhängigkeit von der Auswahl unterschiedlich festgelegt. Da mit einem Kombinationsfeld aus der Symbolleiste Formular nur der Index zurückgegeben wird, erfolgt die Ausgabe des Zielwertes mittels der Arbeitsblattfunktion INDEX.

Änderungen:

1. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Kontextmenü erweitern

Das Kontextmenü wird um den Menüpunkt "Zellen nach Rechts" erweitert. Dieser Menüpunkt erlaubt es, bei Rechtsklick auf eine Zelle ihren Inhalt (und den aller Zellen rechts daneben) um eine Zelle nach rechts zu verschieben. Der für diese Beispiel angepasste Ausgangscode ist unter diesem Link Hajo-Excel.de, Seite VBA-Beispiele, Unterseite "Blinkender Zellinhalt" zu finden.

Änderungen:

1. 29.04.07 Kommentare im Code ergänzt

2. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Kopieren nichtleere

Aus einer Spalte werden die ersten 3 nichtleeren Zellen kopiert. Dazu wird der Autofilter mit dem Kriterium "<>" verwendet.

Änderungen:

1. 15.04.07 Ausschalten des Autofilters im Code geändert

2. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Letzter Benutzer

Bei Öffnen der Arbeitsmappe wird in Spalte A der Benutzer lt. Anmeldung, in Spalte B Datum und Uhrzeit des Öffnens eingetragen. Nach 9 Einträgen wird die Liste gelöscht und wieder in Zelle A2 begonnen. Die Arbeitsmappe muss spätestens beim Schließen gespeichert werden.

Änderungen:

1. 15.04.07 Code überarbeitet

2. 15.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Musikbox

Das Abspielen von 6 verschiedenen Sounddateien wird mithilfe von CommandButtons in einem UserForm gesteuert. Die Hintergrundfarbe der CommandButtons ändert sich in Abhängigkeit vom Abspielzustand: nicht gespielt - Schaltflächen-Standardfarbe, Abspielen gestartet - Hellgrün,  bereits gespielt: Orange.

Als Abspielprogramm wird der Windows Mediaplayer im ausgeblendeten Zustand benutzt. Als Dateityp ist .mid im Code festgelegt, es sind jedoch alle Sounddateitypen möglich, die mit dem Windows Mediaplayer abgespielt werden können.

Hinweis: bitte ZIP-Datei vor Öffnen der Excel-Arbeitsmappen entpacken.

Stand 19.03.08

Shapes einfärben Bundesländer

Shapes, die die Bundesländer der BRD repräsentieren, werden in Abhängigkeit von einem Zahlenwert in der Tabelle in verschiedenen Graustufen gefärbt. Es sind nur Werte zwischen 1 und 10 zugelassen.

Änderungen:

1. 15.04.07 Code überarbeitet

2. 15.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Sounddatei

Eine in die Arbeitsmappe eingebettete Sounddatei wird abgespielt.

Änderungen:

1. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Spiegeln

Die Zellen aus 3 Zeilen werden in umgekehrter Reihenfolge der Spalten wieder eingetragen.

Änderungen:

1. 16.04.07 Code überarbeitet

2. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Suchen Zellinhalt

Alle Tabellen der Arbeitsmappe werden nach einem mittels InputBox abgefragten Begriff durchsucht. Die Zelle mit dem gefundenen Begriff wird in der linken oberen Ecke des Bildschirms positioniert. Eine MsgBox mit den Schaltflächen Ja/Nein erlaubt ein Fortsetzen/Beenden der Suche.

Änderungen:

1. 26.04.07 Code überarbeitet, mehrmalige Suche je Tabelle ergänzt

2. 26.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

Transponieren

Für mehrfach vorkommende Werte in Spalte A werden die Werte aus Spalte B in eine Zeile übertragen. Dazu wird eine Kombination aus Autofilter und Transponieren verwendet. Die überflüssig gewordenen Zeilen werden anschließend gelöscht. Voraussetzung ist, dass vorher nach Spalte A sortiert wurde und damit alle Mehrfacheinträge fortlaufend stehen.

Änderungen:

1. 17.04.07 Code überarbeitet

2. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

UserForm blinkende Anzeige

Ein UserForm wird beim Öffnen der Arbeitsmappe aufgerufen, wobei in Spalte D der Tabelle1 geprüft wird, ob ein im Code definierter Wert vorhanden ist. Wird der Wert gefunden, blinkt ein roter Punkt, andernfalls blinkt ein grüner Text. Wird der Wert durch Eingabe in der Tabelle erreicht, wird das UserForm automatisch gestartet und der blinkende Punkt angezeigt.

Änderungen:

1. 30.04.07 Code überarbeitet

2. 30.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

UserForm CommandButton

Auf einem  UserForm wird in Abhängigkeit von der Anzahl der Einträge in einer Zeile dieselbe Anzahl an CommandButton erstellt. Mithilfe eines Klassenmoduls werden unterschiedliche Makros zur Ausführung gebracht. Dafür wird die Tag-Eigenschaft der CommandButton verwendet. Es ist Code für 12 CommandButton hinterlegt, deren Anzahl jedoch beliebig erweitert werden kann.

Änderungen:

1. 29.04.07 Code überarbeitet

2. 29.04.07 Kommentare im Code ergänzt

3. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

UserForm Symbolleiste abdecken

nicht für Version ab 2007

Bei Öffnen der Arbeitsmappe wird ein UserForm gestartet, welches den oberen Symbolleistenbereich komplett abdeckt, sodass darauf kein Zugriff mehr möglich ist. Das hat gegenüber dem Aus- und wieder Einblenden der Symbolleisten mittels Code den Vorteil, dass bei einem Excel-Absturz der Ursprungszustand nicht wieder hergestellt werden muss.

Stand 03.10.07

UserForm TextBox-Anzahl variabel

 

Auf einem UserForm befinden sich 100 TextBoxen, angeordnet in 10 Spalten á 10 Reihen. Entsprechend vorheriger Auswahl Spalten/Zeilen werden nur die betreffenden TextBoxen angezeigt. Die Größe des UserForms passt sich automatisch an die gewählte Anzahl an. Die Inhalte der TextBoxen werden in die Tabelle beginnend ab Spalte A, Zeile 10 übernommen.

Stand 27.10.07

Zeitstempel

 

Eine Spalte mit Formelergebnis wird überwacht, ob ein in einer separaten Spalte vorgegebener Grenzwert überschritten ist. Die Prüfung erfolgt sowohl bei Eingabe eines Wertes als auch im Sekundentakt. Nach einer im Code festgelegten Zeitspanne erfolgt eine Anzeige mittels UserForm, dass der Grenzwert immer noch überschritten ist. Die Zeitüberprüfung kann abgeschaltet werden.

Beispiel kann auch bei Verbindung zu externen Datenquellen verwendet werden.

Änderungen:

1. 01.05.07 Beschreibung in der Tabelle präzisiert

2. 01.05.07 Code komplett überarbeitet

3. 01.05.07 Kommentare im Code ergänzt

4. 25.06.07 Beispiel für Version 2007 ergänzt

Stand 25.06.07

 

letzte Bearbeitung 15.11.12

Diese Seite ist Bestandteil eines Framesets von excel-inn.de.

Falls ihr keine Navigationsleiste seht, klickt bitte auf den Link.