Die xlTools

Microsoft Excel Herbers Excel-Tools
Beispielanwendungen zu Microsoft Excel

Die xlTools
Die xlTools werden ausschließlich als Bestandteil von Herbers Excel/VBA-Beispielen vertrieben und sind damit auch Bestandteil des Gesamtpaketes der Excel-Lehr- und Lernmaterialien.

Hier klicken für Informationen zu den Excel-Lehr- und Lernmaterialien

Weitere Anwendungen, die in den Excel/VBA-Beispielen neben über 2.000 Beispielarbeitsmappen, einer Einführung in die VBA-Programmierung und einer Einführung in Microsoft Excel enthalten und im Preis enthalten sind:

Fehlzeitenplaner
Fehlzeitenplaner

Kassenprogramm

Kassenprogramm


Herbers Exce-Tools

Übersicht
Bei den Excel-Tools handelt es sich um ein Zusammenfassung der vom Verfasser erstellten Tools und Utilities zum Einsatz bei der täglichen Arbeit. Der Verfasser hat den Code nicht geschützt bzw. das Passwort offengelegt, um dem Anwender Anregungen für seine eigenen VBA-Programmierungen zu geben. Der Code oder Code-Sequenzen können frei verwendet werden.

  • Installation

    Installieren Sie die Arbeitsmappe als AddIn. Ein Anleitung zur Installation von AddIns finden Sie hier:

    AddIn installieren

    Nach der Installation finden Sie unterhalb Ihrer Symbolleisten eine neue Symbolleiste mit Menüs und Menüpunkten:

    Die Symbolleiste

  • Zu den Tools:

    Zum Aufruf der Hilfe zum Menüpunkt klicken Sie bitte auf ein Menü-Element.

    • Utilities
      Konvertierung zu HTML-Tabellen Zufallszahlen eintragen Zeilen und Spalten ausblenden Absolute und relative Zellbezüge Mit Kopie-Wasserzeichen drucken Gedankenleser

    • Dialoge
      Anzeigen von Symbolen und Sonderzeichen Codesequenz für Inputbox kreieren Kalender mit KW nach DIN 1355 Farbdialog anzeigen In Excel verfügbare Icons (FaceIDs) anzeigen

    • Löschen
      Alle Formatvorlagen und benutzerdefinierten Zahlenformate löschen Alle Bereichsnamen löschen

    • Namenssplitting

    • Quittung
      Quittungen schreiben und verwalten

    • Verknüpfungen

    • Formatierung
      Formatierungen und Ansichten

    • Info
      Information und Version

    • Hilfe


HTML-Tabelle

HTML-Tabelle
Es wird eine HTML-Tabelle des ausgewählten Bereiches erstellt, wobei über eine InputBox-Abfrage gewählt werden kann, ob man ausschließlich den Tabellenteil oder auch den HTML-Rahmen um die Tabelle konvertiert haben möchte. Der HTML-Text wird in die Zwischenablage übernommen, so dass er in einen Editor eingefügt werden kann. Wird auch der Rahmen erstellt, erfolgt zusätzlich eine Anzeige der HTML-Seite.

Excel-Features zur HTML-Konvertierung werden nicht genutzt, um den HTML-Code schlank zu halten.

Die Ausgangstabelle in Excel:

Excel-Tabelle

Zieltabelle in HTML:

HTML-Tabelle


Zufallszahlen

Zufallszahlen
In den ausgewählten Zellbereich werden Ganzzahlen als Zufallszahlen eingetragen, wobei die Unter- und Obergrenze über InputBoxes abgefragt werden. Beispiel einer Zufallszahlen-Tabelle:

Zufallszahlen


Zeilen und Spalten ein- und ausblenden

Zeilen und Spalten ein- und ausblenden
Alle Zeilen und Spalten außerhalb des ausgewählten Bereiches werden ausgeblendet, so dass sie mit dem Cursor nicht mehr anwählbar sind. Beispiel:

Zeilen und Spalten ausblenden

Sind Zeilen und Spalten bereits aus-, werden sie bei einem erneuten Aufruf wieder eingeblendet.


Absolute und relative Bezüge bereichsweise wetzen

Absolute und relative Bezüge bereichsweise wetzen
Informationen über absolute und relative Bezüge finden Sie hier

In Excel lassen sich mit F4 absolute und relative Bezüge wählen. Leider steht dieses Feature nur für einzelne Zellen, nicht für ganze Zellbereiche zur Verfügung.

Über den Menüpunkt Bezüge ein und aus wird dem Zellkontextmenü ein neues Menü Bezüge hinzugefügt:

Zellkontextmenü

Das Menü:

  • Menüpunkt $A$1
    Im ausgewählten Bereich werden alle Zeilen- und Spaltenbezüge in absolute Bezüge umgewandelt.

    Das Ergebnis sieht so aus:

    Die Umwandlung führt dazu, dass beim Kopieren der Formel der exakt vorgegebene Bereich in die Zielposition übernommen wird.

  • Menüpunkt $A1
    Im ausgewählten Bereich werden alle Spaltenbezüge in absolute Bezüge umgewandelt.

    Das Ergebnis sieht so aus:

    Die Umwandlung führt dazu, dass beim Kopieren der Formel der Spaltenbereich der Quellformel, nicht aber der Zeilenbereich übernommen wird.

  • Menüpunkt A$1
    Im ausgewählten Bereich werden alle Zeilenbezüge in absolute Bezüge umgewandelt.

    Das Ergebnis sieht so aus:

    Die Umwandlung führt dazu, dass beim Kopieren der Formel der Zeilenbereich der Quellformel, nicht aber der Spaltenbereich übernommen wird.

  • Menüpunkt A1
    Im ausgewählten Bereich werden alle Zeilen- und Spaltenbezüge in relative Bezüge umgewandelt.

    Das Ergebnis sieht so aus:

    Die Umwandlung führt dazu, dass beim Kopieren der Formel Zeilen und Spalten relativ übernommen werden.

  • Menüpunkt Zurück
    Die jeweils letzte Aktion wird wiederholt.


Mit Kopie drucken

Mit Kopie drucken
Das aktive Tabellenblatt wird mit einem Kopie-Exemplar gedruckt, wobei dieses ein Copy als Wasserzeichen enthält:


Gedankenleser

Gedankenleser
Zur Abwechslung bei der Arbeit wird ein Tabellenblatt mit einer Gedankenleserfunktion eingeblendet:

Ein zweites Anklicken blendet das Blat wieder aus.


Symbole und Sonderzeichen

Symbole und Sonderzeichen
Das Feature erleichtert das Einfügen von Symbolen und Sonderzeichen in Tabellenblätter. Der Dialog:

Symbole und Sonderzeichen

Beim Überestreichen der Symbole mit der Maus wird das jeweils unter dem Cursor liegende Symbol vergrößert in einem Labelfeld dargestellt. Weitere Labelfelder zeigen den Tastaturcode (Nummernblock) und die Excel-Formel für das Einfügen des Zeichens an.

Über eine ComboBox können Sie die gewünschte Schriftart vorgeben.

Über Doppelklick und Klick werden die jeweiligen Zeichen in ein Textfeld eingetragen. Die Übernahme kann entweder über die Schaltfläche Zwischenablage - zum Einfügen mit dem ShortCut Strg+v und nachfolgender Schriftartenformatierung - oder über die Schaltfläche in die aktive Zelle erfolgen. Im letzteren Fall erfolgt die Schriftformatierung automatisch.


InputBox

InputBox
Es wird sowohl eine Excel- wie auch eine VBA-InputBox angeboten, wobei die erstere Eingabebeschränkungen bietet. Es kann festgelegt werden, ob z.B. Ganzzahlen, Texte oder Bezüge eingegeben werden sollen. Insgesamt bieten beide InputBoxes eine Reihe von Möglichkeiten der Beeinflussung von Aussehen, Positionierung und Funktion. Der Dialog zum Kreieren von InputBoxes bietet die Möglichkeit einer Vorschau und der Übernahme des VBA-Codes in die Zwischenablage.

Der Dialog:

InputBox-Creator

Über die Schaltfläche Vorschau kann man sich die Ansicht der InputBox gemäß Vorgaben anzeigen lassen:

InputBox

Die Schaltfläche Zwischenablage übernimmt den VBA-Code für die InputBox in die Zwischenablage, so dass er einfach in ein VBA-Modul eingefügt werden kann:


Kalender

Kalender
Der Kalender-Dialog:

Der Kalender

  • Schaltfläche Kalenderwochenformel
    Ein neuer Dialog mit Optionen zur Kalenderwochenformel wird aufgerufen:

    Kalenderwoche

    • Optionsfeld Heute
      Die Formel zur Kalenderwochenermittlung des aktuellen Tages (Voreinstellung)
    • Optionsfeld Datum eingeben
      Ein Textfeld zur Datumseingabe öffnet sich
    • Optionsfeld Zellbezug
      Ein Steuerelement zur Eingabe eines Zellbezugs öffnet sich
    • Schaltfläche OK
      Die Kalenderwochenformel wird in die aktive Zelle eingetragen. Ist die aktive Zelle nicht leer, erfolgt eine Sicherheitsabfrage.

      Hinweis:
      Die deutschen Kalenderwochen berechnen sich wie folgt:

      Der 1. Januar eines Jahres gehört erst dann zur ersten Kalenderwoche, wenn dieser Tag auf einen Montag, Dienstag, Mittwoch oder Donnerstag fällt. Falls der 1.Januar ein Freitag, Samstag oder Sonntag ist, zählt er, und ggf. auch der 2. und 3. Januar, noch zur letzten Kalenderwoche des vorherigen Jahres.

      Weiterhin können der 29., 30. und 31.12. eines Jahres schon zur Kalenderwoche 1 des neuen Jahres gehören. Das ist genau dann der Fall, wenn der 31.12. auf einen Montag, Dienstag oder Mittwoch fällt.

      Die Formel zur Ermittlung der Kalenderwoche für den aktuellen Tag nach DIN 1355 lautet:

      =KÜRZEN((HEUTE()-WOCHENTAG(HEUTE();2)-DATUM(JAHR(HEUTE()+4-WOCHENTAG(HEUTE();2));1;-10))/7)
    • Schaltfläche Abbrechen
      Der Dialog wird geschlossen

  • Schaltfläche Eintragen
    Das im Kalenderdialog ausgewählte Datum wird in die aktive Zelle eingetragen. Ist die aktive Zelle nicht leer, erfolgt eine Sicherheitsabfrage.

  • Schaltfläche Zwischenablage
    Das im Kalenderdialog ausgewählte Datum wird in die Zwischenablage übernommen und steht für den Eintrag mit dem ShortCut Strg+v zur Verfügung.

  • Schaltfläche Abbrechen
    Der Dialog wird ohne Aktion geschlossen.


Farbdialog

Farbdialog
Der Dialog:

Farbdialog

Gemäß den Optionsfeldern kann eingestellt werden, ob die angecklickten Farben nicht, als Zellhintergrund oder als Schriftfarbe übernommen werden sollen. Von den Excel-eigenen Farbdialogen unterscheidet sich dieser insbesondere dadurch, dass es hier möglich ist, über eine Variable (ixlcColor) die vom Anwender angeklicke Farbe zu speichern.

Ein Klick auf die Schaltfläche Palette als Blatt listet alle 56 Index-Farben in einer neuen Arbeitsmappe auf:


Icon-Picker

Icon-Picker
Excel stellt über 2.000 Icons zur Verfügung, die in eigene Anwendungen eingebunden werden können. Im Icon-Picker kann man die Symbole durchblättern:

Icon-Picker

Beim Überstreichen mit der Maus wird als QuickInfo die FaceId-Nummer des jeweiligen Elements angezeigt. Diese wird benötigt, wenn man über VBA-Code auf das Icon zugreifen möchte.

Bei Klick auf das Tabellenblattsymbol werden die danach ausgewählten Symbole in das Tabellenblatt kopiert. Beim Klick auf die danebenliegende Schaltfläche wird dieses Feature ausgeschaltet. Der Dialog wird über das Schließkreuz geschlossen.


Formatvorlagen und benutzerdefinierte Zahlenformate löschen

Formatvorlagen und benutzerdefinierte Zahlenformate löschen
Excel stellt keine Funktion zur Verfügung, mit der Formatvorlagen oder benutzerdefinierte Zahlenformate in Serie gelöscht werden können; hier ist mühsame Handarbeit mit Einzelauswahl der Elemente erforderlich.

Dieses Tool ermöglicht es, die Elemente in Serie zu löschen:

Formate löschen

  • Kontrollkästchen Vorlagen
    Bei Aktivierung werden die Formatvorlagen einbezogen

  • Kontrollkästchen Zahlenformate
    Bei Aktivierung werden die benutzerdefinierten Formate einbezogen

  • Optionsfeld Alle Formatvorlagen löschen
    Bei Aktivierung werden alle benutzerdefinierten Formatvorlagen gelöscht, andernfalls nur die nicht verwendeten.

  • Optionsfeld Alle Zahlenformate löschen
    Bei Aktivierung werden alle benutzerdefinierten Formate gelöscht, andernfalls nur die nicht verwendeten.

  • Schaltfläche OK
    Die ausgewählten Elemente werden gemäß den Vorgaben gelöscht.

  • Schaltfläche Abbrechen
    Der Dialog wird geschlossen.


Bereichsnamen löschen

Bereichsnamen löschen
Excel stellt keine Funktion zur Verfügung, mit der Bereichsnamen in Serie gelöscht werden können; hier ist mühsame Handarbeit mit Einzelauswahl der Elemente erforderlich.

Dieses Tool löscht Bereichsnamen in Serie:

Der Dialog
  • Listenfeld Namen
    Beim Dialogstart werden die Bereichsnamen der in der ComboBox ausgewählten Arbeitsmappe zur Auswahl aufgelistet.

  • ComboBox Arbeitsmappen
    Listet alle offenen Arbeitsmappen mit Bereichsnamen zur Auswahl auf.

  • Schaltfläche OK
    Die ausgewählten Bereichsnamen in der ausgewählten Arbeitsmappe werden gelöscht.

  • Schaltfläche Abbrechen
    Der Dialog wird geschlossen.


Namen splitten und zusammenführen

Namen splitten und zusammenführen
Das Splitten oder Zusammenfügen von Namen über die Excel-Features hat so seine Tücken. Es kann beispielsweise nicht erkannt werden, ob es sich um Vornamen oder Namenszusätze handelt. Dieses Tool versucht, die größten Probleme zu beseitigen.

Ausgangstabelle:

Ausgangstabelle

Nachdem der Namensbereich selektiert und die Schaltfläche Splitten angeklickt wurde, wird eine neue Tabelle mit den gesplitteten Namen erstellt:

Gesplittete namen

Nach Auswahl der gesplitteten Namen und dem Anklicken der Schaltfläche Zusammenführen werden die Namen wieder zusammengefügt:

Über einen Dialog können Namensteile hinterlegt werden, die nicht als Vornamen erkennbar sein sollen:

Ausschlüsse


Quittung

Quittung
Das Tool schreibt Quittungen und archiviert sie in einer Quittungsliste. Eine Funktion zum Schreiben von Zahlwörtern ist implementiert.

Der Dialog:

Quittung

Über die Schaltfläche Einstellungen werden Voreinstellungen festgelegt:

Diese werden dauerhaft gespeichert, wenn die Schaltfläche Liste speichern angeklickt wird.

Nach Ausfüllen des Dialogs wird eine Tabelle als Quittungsblatt im erstellt:

Quittungsblatt

Die Daten werden in die Quittungsliste eingetragen. Diese wird über die Schaltfläche Liste ein und aus angezeigt und ausgeblendet:

Quittungsliste

Diese wird dauerhaft gespeichert, wenn die Schaltfläche Liste speichern angeklickt wird.


Verknüpfungen umleiten

Verknüpfungen umleiten
Das umleiten von Verknüpfungen von der einen zur anderen Arbeitsmappe kann in Excel eine mühsame Angelegenheit darstellen. Dieses Tool ermöglicht die Umleitung von ganzen Serien von Verknüpfungen unter Erkennung unterschiedlicher Verknüpfungsziele.

Alle Verknüpfungen der aktiven Arbeitsmappe werden in ein Tabellenblatt eingelesen:

Links

Tragen Sie die neuen Verknüpfungsziele ein:

Neue Ziele

Nach dem Schaltflächenklick wird die Linkliste geschlossen und die Verknüpfungen werden geändert.


Formate und Ansichten

Formate und Ansichten
  • Menüpunkt Grau ein und aus
    Im gesamten Arbeitsblatt werden alle Zellhintergründe grau (ColorIndex 15) formatiert. Wurde diese Formatierung bereits vorgenommen, wird sie aufgehoben.

    Bitte beachten: Es erfolgt keine Sicherheitsabfrage, falls bereits eine Hintergrundformatierung besteht.

  • Menüpunkt Header ein und aus
    Die Zeilen- und Spaltenköpfe werden aus- und eingeblendet.

  • Menüpunkt 3D positiv
    Der ausgewählte Bereich wird positiv 3D-formatiert:

    positiv

    Besteht die Formatierung bereits, wird die Hintergrundfarbe zurückgesetzt.

  • Menüpunkt 3D negativ
    Der ausgewählte Bereich wird negativ 3D-formatiert:

    negativ

    Besteht die Formatierung bereits, wird die Hintergrundfarbe zurückgesetzt.


Die Installation eines AddIns

Anleitung zur Installation und Deinstallation eines AddIns
1 - Den AddIn-Manager aufrufen
Wählen Sie Menü Extras / AddIns-Manager...

2 - Der AddIn-Manager
Wählen Sie in dem sich jetzt öffnenden AddIn-Manager die Schaltfläche Durchsuchen.

3 - Ein AddIn auswählen
Wählen Sie in dem sich jetzt öffnenden AddIn-Manager das zu installierende AddIn aus.

4 - Überprüfen der Aktivierung
Überprüfen Sie im AddIn-Manager, ob das AddIn aktiviert wurde. Beachten Sie eventuelle Informationen am unteren Bildschirmrand. Verlassen Sie den Anpassen-Dialog über die OK-Schaltfläche.

5 - Start des AddIns
In aller Regel hinterlassen AddIns Spuren in Menü- oder Symbolleisten oder erstellen eigenständige CommandBars. Wenn Sie im Anpassendialog keine Information über den Programmstart gefunden haben und keine neue Menü- oder Symbolleiste generiert wurde, prüfen Sie, ob in der Arbeitsblatt-Menüleiste ein neues Menü oder Menüpunkt aufgetaucht ist oder ob diese(r) dem Extras-Menü hinzugefügt wurden.

6 - De-Installation eines AddIn
Der Eintrag im AddIn-Manager bleibt auch nach dem Deaktivieren, also dem Entfernen des Häkchens weiter erhalten. Wenn Sie den Eintrag entfernen wollen, löschen oder verschiebenben Sie das AddIn oder benennen Sie es um. Wenn Sie jetzt versuchen, das AddIn zu aktivieren, erscheint folgender Dialog:


Information

Information

Verantwortlich für den Inhalt:
Hans W. Herber
Im Schönblick 5
D-53567 Asbach
Germany, Old Europe
Telefon: +49 2683 949047
URL: http://www.herber.de/
Email: hans@herber.de
Texte, Gestaltung, Programmierung: Hans W. Herber
USt-ID: DE 149 450 590
Visitenkarte Visitenkarte in Outlook einfügen
Copyright:
Das Copyright liegt ausschließlich bei Hans W. Herber.


Unterschiede zwischen relativen und absoluten Bezügen

Unterschiede zwischen relativen und absoluten Bezügen

Relative Bezüge Wenn Sie eine Formel erstellen, werden die Bezüge auf Zellen bzw. auf Bereiche in der Regel relativ zu der Position der Zelle dargestellt, die die Formel enthält. In dem folgenden Beispiel enthält die Zelle B6 die Formel =A5; Microsoft Excel sucht den Wert in der Zelle, die sich eine Zelle oberhalb und eine Zelle links von B6 befindet. Bezüge in diesem Format werden als relative Bezüge bezeichnet.

Formel mit einem relativen Bezug

Wenn Sie eine Formel kopieren, in der relative Bezüge verwendet werden, werden die Bezüge von Excel automatisch nach dem Einfügen der Formel angepasst, um auf andere Zellen relativ zur Position der Formel zu verweisen. In dem folgenden Beispiel wurde die Formel aus der Zelle B6, =A5, die sich eine Zelle oberhalb und eine Zelle links von der Zelle B6 befindet, in die Zelle B7 kopiert. Die Formel in der Zelle B7 wurde von Excel folglich in =A6 geändert und verweist damit auf die Zelle, die sich eine Zelle oberhalb und eine Zelle links von der Zelle B7 befindet.

Kopierte Formel mit einem relativen Bezug

Absolute Bezüge Wenn Sie verhindern möchten, dass Excel die Bezüge beim Kopieren einer Formel in eine andere Zelle anpasst, verwenden Sie einen absoluten Bezug. Wenn eine Formel beispielsweise die Zelle A5 mit der Zelle C1 multipliziert (=A5*C1), und die Formel dann in eine andere Zelle kopiert wird, wird Excel beide Bezüge anpassen. Sie können einen absoluten Bezug auf die Zelle C1 erstellen, indem Sie vor den Teilen des Bezugs, die nicht geändert werden sollen, ein Dollarzeichen ($) eingeben. Um beispielsweise einen absoluten Bezug auf die Zelle C1 zu erstellen, geben Sie in der Formel wie folgt Dollarzeichen ein:

=A5*$C$1

Umschalten zwischen relativen und absoluten Bezügen Wenn Sie eine Formel erstellt haben und einen relativen Bezug in einen absoluten Bezug ändern möchten (bzw. umgekehrt), markieren Sie die Zelle, in der die Formel enthalten ist. Wählen Sie in der Bearbeitungsleiste den Bezug aus, den Sie ändern möchten, und drücken Sie anschließend F4. Jedes Mal, wenn Sie F4 drücken, schaltet Excel zwischen den folgenden Kombinationen: absoluter Spalten- und Zeilenbezug (z. B. $C$1); relativer Spalten- und absoluter Zeilenbezug (C$1); absoluter Spalten- und und relativer Zeilenbezug ($C1) sowie relativer Spalten- und Zeilenbezug (C1) um. Beispielsweise, wenn Sie in einer Formel die Adresse $A$1 auswählen und F4 drücken, ändert sich der Bezug in A$1. Nach erneutem Drücken von F4 ändert sich der Bezug wieder in $A1 und so weiter.