Excel-Tipps und Tricks

Arbeiten mit verbundenen Zellen (VBA)

Excel Tipp #018 | 7.2.2018

Verbundene Zellen sind in Excel oft sehr praktisch. Über VBA ist es möglich, diese über die Adresse der Zelle links oben anzusprechen. So kann man den Wert der Zelle auslesen oder eintragen. Auch Hintergrund- und Textfarbe und andere Formatierungen können so gesetzt werden. Jedoch das Aktivieren oder Deaktivieren des Zellschutzes (Range().Locked) bringt auf diese Weise einen Laufzeitfehler. Offensichtlich muss der Zellschutz bei allen verbundenen Zellen gleichzeitig gesetzt werden.

Wenn nun von Vornherein nicht bekannt ist, über wie viele Spalten und Zeilen sich der Zellverbund erstreckt, kann man sich über die Selection helfen: Man selektiert über die Referenz ersten Zelle links oben den verbundenen Bereich und setzt die Eigenschaft über Selection.Locked = false

Es gibt aber einen eleganteren Weg, der zudem noch performanter ist: Die Eigenschaft MergeArea des Range-Objekts erlaubt es, den gesamten Zellverbund anzusprechen.

Beispiel: Die Zellen B2 bis E3 sind verbunden.

Die Zeile

Range("B2").Locked = false

verursacht einen Laufzeitfehler. Mit der eingefügten Eigenschaft MergeArea funktioniert der Aufruf:

Range("B2").MergeArea.Locked = false

MergeArea liefert ein gewöhnliches Range-Objekt zurück. In unserem Besipiel verhält sich

Range("B2").MergeArea...

genauso wie

Range("B2:E3")...

Bereich als Tabelle formatieren

Excel Tipp #017 | 2.1.2018

Bereiche lassen sich in Excel als Tabelle formatieren. Dieses Feature gibt es schon seit langem und wurde mit Excel 2007 wesentlich verbessert. Dennoch wird es nur selten verwendet. Die Formatierung als „Tabelle“ eröffnet zahlreiche Möglichkeiten, zum Beispiel:

Um nun einen Bereich als Tabelle zu formatieren, markieren Sie die Zellen, die umgewandelt werden sollen und klicken Sie auf „Tabelle einfügen“ (Registerkarte „Start“) und danach auf das gewünschte Layout. Danach bestätigen Sie den Bereich, der umgewandelt werden soll. Wenn die Einträge in der ersten markierten Zeile als Tabellenköpfe verwendet werden sollen, setzen Sie das Häkchen „Tabelle hat Überschriften“. Mehrfach vorkommende Einträge werden durchnummeriert, um eindeutige Bezeichnungen zu erhalten. Wenn Sie das Häkchen nicht setzen, wird eine Zeile mit Spaltenüberschriften eingefügt (Spalte1, Spalte2, …).

Das Menüband enthält im Reiter „Tabellentools / Entwurf“ weitere Optionen wie zum Beispiel die Möglichkeit, die erste oder letzte Spalte hervorzuheben, die Ergebniszeile einzublenden oder mit einem Klick die Farben zu ändern oder Duplikate zu entfernen. Probieren Sie’s aus!

Hinweis: Der Reiter „Tabellentools / Entwurf“ wird erst sichtbar, wenn die selektierte Zelle innerhalb der Tabelle liegt.

Neben allen Vorzügen, die die Tabellenformatierung mit sich bringt, will ich Ihnen eine Einschränkung nicht vorenthalten. Das Verbinden von Zellen ist in einem als Tabelle formatierten Bereich nicht möglich, weder über mehrere Spalten noch über mehrere Zeilen hinweg.

(VBA): Seitenlayout mit PAGE.SETUP festlegen

Excel Tipp #016 | 5.12.2017

Sie haben sicher schon die Erfahrung gemacht, dass das Einrichten des Seitenlayouts per VBA lange dauert. Besonders dann, wenn Sie die Einrichtung als Makro aufzeichnen, erzeugt der Makro-Rekorder sehr viel VBA-Code, dessen Abarbeitung mitunter verhältnismäßig lange dauert. Es gibt aber eine wenig bekannte Lösung, sämtliche Einstellungen mit einem einzelnen Befehl vorzunehmen: PAGE.SETUP.

Hierbei handelt es sich um ein sogenanntes Excel4-Makro. Dieser Aufruf stammt aus einer frühen Excel-Version, funktioniert aber bis heute (Excel 2016) noch immer einwandfrei – und schnell!

Ein Beispiel:

ExecuteExcel4Macro "PAGE.SETUP(""Meine Kopfzeile"",""&D"",0.4,0.4,0.8,0.4,,TRUE,TRUE,,2,9,,,,,,,,,)"

Der vorangegangene Aufruf stellt folgendes Seitenlayout ein:

• Ränder: links, rechts, unten auf 1 cm; oben 2 cm
• A4 Querformat
• Kopfzeile = „Meine Kopfzeile“
• Fußzeile = aktuelles Datum
• Gitternetzlinien drucken = ja
• Horizontal zentrieren = ja

Syntax im Detail:

ExecuteExcel4Macro "PAGE.SETUP(head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, orient, paper_size, scale, pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft)"

PAGE.SETUP erwartet für Tabellenblätter folgende Parameter:

Eine ausführliche Syntax-Beschreibung samt aller Formatcodes finden sie hier: Page.Setup Excel Tipp 16.

Hinweise:

• Alle Angaben sind optional. Erfolgt für einen Parameter keine Angabe, wird die betreffende Einstellung nicht verändert.
• Texte müssen mit jeweils zwei Anführungszeichen vorne und hinten angegeben werden, weil wir ja eine Zeichenkette in einer Zeichenkette übergeben (siehe Beispiel).
• Für die Kontrolle des Seitenlayouts kann es hilfreich sein, sich die Schaltfläche „Seitenansicht und Drucken“ in die Symbolleiste für den Schnellzugriff zu holen.

Einzelne Zellinhalte verstecken

Excel Tipp #015 | 8.11.2017

Oft kommt es vor, dass in Ausdrucken oder auf dem Bildschirm einzelne Zellinhalte nicht dargestellt werden sollen. Eine ganze Spalte oder Zeile auszublenden ist nicht immer möglich. Alternativ könnten Sie die Textfarbe der Zelle auf die Hintergrundfarbe setzen, dann wäre die Zelle am Bildschirm nicht sichtbar. Aber sobald Sie die Tabelle im Schwarzweiß-Modus drucken, erscheint der ausgeblendete Wert im Ausdruck.

Eine Möglichkeit, den Inhalt auch im Ausdruck zu verstecken ist die Verwendung eines benutzerdefinierten Zahlenformats: „;;;“

In Excel können Zahlenformate für vier verschiedene Inhalte festgelegt werden (in dieser Reihenfolge, getrennt durch einen Strichpunkt „;“):

• Positive Zahl
• Negative Zahl
• Nullwert
• Text

Mit „;;;“ definieren Sie folglich für jeglichen Zellinhalt eine leere Ausgabe.

Bitte beachten Sie, dass der jeweilige Wert in oben der Eingabezeile dennoch sichtbar bleibt.

Tabellenfunktionen (VBA)

Excel Tipp #014 | 18.10.2017

Oft ist es notwendig, im VBA-Code die Anzahl der Zellen in einem Bereich zu zählen oder Summen über einen Bereich zu bilden, um mit diesen weiterzuarbeiten. Oder Sie benötigen einen Wert aus einer Liste. Um dies zu bewerkstelligen sieht man häufig Schleifen, bei denen die Bereiche zellenweise abgearbeitet werden. Einfacher und schneller geht mit WorksheetFunctions.

Die Tabellenfunktionen, die für die Verwendung in Zellen kennt, kann man auch im VBA-Code verwenden. Und zwar als Function des WorksheetFunction-Objekts. Die Funktionsnamen entsprechen den englischsprachigen Funktionsnamen in Formeln.

Wichtige oder häufig benötigte WorksheetFunctions

Wie Sie sehen, erwarten die WorksheetFunctions die gleichen Argumente wie ihre Pendants in den Tabellen. Beachten Sie bitte, dass Zellbezüge als VBA-Objekte referenziert werden müssen. Um also die Zelle A1 in Blatt „Budget“ zu adressieren, müssen Sie in VBA Sheets("Budget").Range("A1") verwenden. Selbstverständlich können Sie dort, wo eine Function einen einzelnen Wert als Argument erwartet, anstatt eines Bezugs auch den Wert einer Variablen oder einen fixen Wert als Argument angeben. Variation vom Beispiel oben zu VLookup:

WorksheetFunction.VLookup(2, Sheets("Tabelle2").Range("A1:B7"), 2, false) liefert den Inhalt der Zelle in Blatt „Tabelle2“, Spalte B aus jener Zeile, in der in Spalte A der Wert 2 steht.

Relative und absolute Bezüge

Excel Tipp #013 | 29.09.2017

In Excel-Formeln können absolute und relative Bezüge verwendet werden. Für die Formel in einer Zelle selbst ist die Unterscheidung irrelevant. Erst beim Kopieren einer Formel in eine andere Zelle macht sich der Unterschied bemerkbar. Bei relativen Bezügen bleibt der Versatz zwischen der Formelzelle und der Bezugszelle jeweils gleich. Absolute Bezüge sind jeweils mit einem $-Zeichen vor dem Spalten- oder Zeilenindex oder beiden markiert. Solche Zeilen oder Spaltenindizes werden nach dem Kopieren einer Formel in eine andere Zelle gleichbehalten.

Das $-Zeichen kann direkt über die Tastatur eingegeben werden. Alternativ kann man in einer Formel wenn die Einfügemarke bei einem Zellbezug steht, durch (mehrmaliges) Drücken der F4-Taste umschalten:

Relativer Bezug (z.B. B4) > fixe Zeile und Spalte (z.B. $B$4) > fixe Spalte ($B4) > fixe Zeile (z.B. B$4)

Noch zwei Hinweise:

1. Beim Einfügen oder Löschen von Zeilen werden Zellbezüge unabhängig davon angepasst, ob die betroffenen Formeln relative oder absolute Bezüge enthalten. So ist gewährleistet, dass das Einfügen oder Löschen von Zeilen oder Spalten per se keine Ergebnisse verändert.
2. Bezüge zu benannten Bereichen, die unter ihrem Namen angesprochen werden, behandelt Excel wie absolute Bezüge.

VBA: Beliebigen Ribbon-Control ausführen

Excel Tipp #012 | 18.08.2017

Steuerelemente im Ribbon lassen sich auch per VBA-aktivieren oder deren Status auslesen. Das ist dann nützlich, wenn es dafür keinen Befehl im VBA-Objektmodell gibt.

Beispiele:

Aktion ausführen, die sich hinter einem Steuerelement befindet:

Application.CommandBars.ExecuteMso(idMso)

Achtung! Wenn das Steuerelement nicht aktiv ist, gibt es einen Automatisierungsfehler.

Dieser lässt sich verhindern, indem ich vorher den Status abfrage:

Application.CommandBars.GetEnabledMso(idMso)

… liefert True zurück, wenn das Steuerelement aktiv ist.

Als Parameter idMso wird der interne Name des Steuerelements erwartet. Die komplette Liste der Steuerelemente kann bei Microsoft heruntergeladen werden. Stichwort: „Office Fluent User Interface Control Identifiers“.

Ein paar Beispiele:

Konkret:
Application.CommandBars.ExecuteMso "VisualBasic"

... öffnet die VBA Entwicklungsumgebung

Doppelklick

Excel Tipp #011 | 25.07.2017

1. Ans Ende springen: Wussten Sie, dass Sie mit der Maus per Doppelklick auf den jeweiligen Rand der Markierung der aktiven Zelle schnell an das jeweilige „Ende“ springen können? Damit ist folgendes gemeint: Bei einer leeren Zelle springen Sie in der jeweiligen Richtung zur letzten leeren Zelle, bei einer Zelle mit Inhalt zur letzten Zelle mit Inhalt (siehe Grafik).



2. Mit einem Doppelklick auf das kleine Quadrat rechts unten, das sogenannte „Ausfüllkästchen“, erzielen Sie den gleichen Effekt als würden Sie den Bereich unterhalb der Zelle bis zum unteren Rand ziehen: Der Bereich wird automatisch ausgefüllt.
3. Die optimale Breite einer Spalte und optimale Höhe einer Zeile können Sie automatisch einstellen, wenn Sie jeweils auf den Trennstrich zwischen zwei Spalten- oder Zeilenüberschriften doppelklicken.
4. Der Doppelklick in eine Zelle erlaubt Ihnen, den Inhalt einer Zelle zu bearbeiten.
5. Doppelklicken Sie auf den Namen einer Tabelle im unteren Bereich des Excel-Fensters, um diesen zu ändern.

VBA: Range.Copy

Excel Tipp #010 | 21.06.2017

In der Praxis kommt es oft vor, dass in Makros einzelne Zellen oder ganze Zellbereiche in andere Blätter kopiert werden. Die übliche Vorgehensweise ist:

1. Bereich markieren: Worksheets("Tabelle 1").Range("C4:D6").Select
2. Markierte Zellen kopieren: Copy
3. Ziel Markieren: Worksheets("Tabelle 2").Range("B2").Select
4. Einfügen: Paste

Eine wenig bekannte Möglichkeit ist, der Range.Copy-Methode einen Parameter mit dem Zielbereich mitzugeben, in den der Zellinhalt der Zelle kopiert wird. Zum Beispiel:

Workbooks("Mappe1").Sheets("Tabelle1").Range("B1").Copy destination:=Workbooks("Mappe2").Sheets("Tabelle1").Range("B6")

Vorteil ist, dass der Code kürzer und übersichtlicher wird. Nachteil: Es steht kein zweiter Parameter zur Verfügung, mit dem man steuern kann, welche Zelleigenschaften kopiert werden sollen. Wenn beispielsweise nur Zahlenwerte oder Formeln ohne Formatierung kopiert werden sollen, muss das in separaten Schritten erledigt werden (Copy – Paste).

Excel Tipps: Excel Schaltjahrfehler 29.02.1900

Excel Tipp #009 | 29.05.2017

In einem Schaltjahr hat bekanntlich der Monat Februar einen Tag mehr, also 29 Tage statt wie in den anderen Jahren 28. Welche Jahre Schaltjahre sind, ist im Gregorianischen Kalender in den folgenden drei Regeln definiert:

1. Jahre, die sich ganzzahlig durch 4 teilen lassen, sind Schaltjahre
2. Davon ausgenommen sind Jahre, die ein Jahrhundert abschließen (z.B. 1700, 1800, 1900, 2100). Diese sogenannten Säkularjahre sind keine Schaltjahre.
3. Davon wiederum ausgenommen sind die durch 400 teilbaren Säkularjahre (z.B. 1600, 2000, 2400). Diese bilden sozusagen die „Ausnahme von der Ausnahme“ und sind Schaltjahre.

Microsoft Excel rechnet grundsätzlich im Gregorianischen Kalender. Es gibt jedoch eine Ausnahme: Anders als im Gregorianischen Kalender wird in Microsoft Excel das Jahr 1900 als Schaltjahr betrachtet und der 29.02.1900 ist ein gültiges Datum. Microsoft hat diesen Fehler seinerzeit in die erste Version von Excel bewusst eingebaut, um vollständig kompatibel mit anderen, damaligen Tabellenkalkulationsprogrammen zu sein, die damals auf dem Markt waren (z.B. Lotus 1-2-3).

In Excel ist das Datum mit Uhrzeit intern als Dezimalzahl gespeichert. Der ganzzahlige Anteil bildet das Datum, die Nachkommastellen die Uhrzeit. Die Tage werden beginnend mit 01.01.1900 hochgezählt. Somit entspricht die Zahl 43538 dem 14. März 2019. Microsoft hat dieses Verhalten über die Jahre beibehalten, weil eine Änderung auch Implikationen auf Datums- und Wochentagsfunktionen hätte.

VBA: Makros beschleunigen

Excel Tipp #008 | 20.04.2017

Oft kommt es vor, dass mit Hilfe eines Makros umfangreiche Bearbeitungen vorgenommen werden. Es werden Berechnungen durchgeführt, Zellen formatiert, Zeilen oder Spalten eingefügt.

Alle diese Operationen benötigen Zeit, weil nach jedem Schritt die Ansicht aktualisiert wird, bevor die nächste Anweisung ausgeführt wird.

Um solche Makros zu beschleunigen, ist es ratsam, am Anfang die Aktualisierung der Ausgabe abzuschalten. Fügen Sie dazu Application.ScreenUpdating = False am Anfang und Application.ScreenUpdating = True am Ende Ihres Makros ein.

Bei umfangreichen Tabellen kann es darüber hinaus hilfreich sein, zwischenzeitlich die automatische Berechnung abzuschalten. Fügen Sie dazu Application.Calculation = xlCalculationManual ein.

Beachten Sie bitte, dass zwischenzeitlich Formelergebnisse falsch sein könnten. Wenn in Ihrem Makro von Formelergebnissen abhängige Bearbeitungsschritte enthalten sind, führen Sie vor der Überprüfung der gegebenenfalls eine Zwischenberechnung mittels Application.Calculate durch. Unter Umständen ist auch eine vollständige Neuberechnung mittels Application.CalculateFull oder Application.CalculateFullRebuild notwendig. Am Ende des Makros sollten Sie die Berechnung unbedingt wieder auf automatisch zurückstellen.

Das beschleunigte Makro könnte zum Beispiel so aussehen:

Excel Tipps: Datenüberprüfung mit Formel

Excel Tipp #007 | 14.03.2017

Im Tipp Tipp #003: Datenüberprüfung haben wir Ihnen gezeigt, wie Sie einschränken können, was in eine Zelle eingegeben werden kann und haben auch darauf hingewiesen, dass diese Einschränkung einfach ausgehebelt werden kann. In diesem Tipp wollen wir Ihnen zeigen, wie Sie eine Datenüberprüfung realisieren können, die trotz Kopieren und Einfügen auf ungültige Werte aufmerksam macht.

Wenn Sie nun beispielsweise ein Formular haben, in dem ein Prozentsatz innerhalb eines bestimmten Intervalls eingegeben werden muss, können Sie mit folgender Formel die Richtigkeit der Eingabe überprüfen:

=WENN(ISTLEER(Eingabefeld);"Wert fehlt";WENN(ISTZAHL(Eingabefeld);WENN(Eingabefeld<0,1;"zu niedrig";WENN(Eingabefeld>0,25;"zu hoch";"Wert ist OK")

Mit Eingabefeld haben wir die Zelle benannt, in die die Eingabe erfolgen soll.

Im folgenden Beispiel sehen Sie die verschiedenen Ausgaben je nach Eingabe:

Zur Erklärung: Die Überprüfung erfolgt mittels verschachtelter WENN()-Abfragen.

Wir überprüfen der Reihe nach:

• ISLEER(Eingabefeld): Ist die Zelle leer
• ISTZAHL(Eingabefeld): Ist die Eingabe eine Zahl? Achtung, auch ein Datum erfüllt diese Bedingung!
• Eingabefeld<0,1: Ist der eingegebene Wert kleiner als 0,1 (also 10%)?
• Eingabefeld>0,25: Ist der eingegebene Wert größer als 0,25 (also 25%)?

Kurz zur Erklärung der WENN()-Funktion am Beispiel der letzten Prüfung: 

                = WENN(Eingabefeld>0,25;"zu hoch";"Wert ist OK")

 Eingabefeld>0,25            … zu überprüfende Bedingung, muss WAHR oder FALSCH zurückliefern

"zu hoch"                           ... Ergebnis, wenn Bedingung erfüllt ist

"Wert ist OK"                    … Ergebnis, wenn Bedingung nicht erfüllt ist

Die Ergebnis-Ausgaben können Zahlen, Texte oder selbst wieder Formeln sein.

In unserem Eingabeformular fragen wir sozusagen die Fehlerfälle der Reihe nach ab und geben eine entsprechende Warnung aus (z.B. „Wert fehlt“ etc.), wenn die Prüfung ein positives Ergebnis liefert (also ein Fehler erkannt wurde). Wenn die Bedingung nicht erfüllt ist (also kein Fehler gefunden wurde), prüfen wir den nächsten Fehler. Erst wenn die letzte Prüfung bestanden wurde, liefern wir „Wert ist OK“ zurück.

Excel Tipps: VBA: Tabellenfunktionen in VBA verwenden

Excel Tipp #006 | 09.02.2017

Sie können Funktionen, die Sie in einem Tabellenblatt verwenden, auch in ihre VBA-Prozeduren und Funktionen einbauen.

Zum Beispiel: Anstatt eine Reihe von untereinanderstehenden Zahlen in einer Schleife durchzulaufen und einzeln zu addieren, können Sie wie im Tabellenblatt direkt die Summe bilden. Verwenden Sie hierfür

WorkheetFunction.Sum(Bereich1 [, Bereich2, ...])

Bereich1, Bereich2 etc. wären in diesem Fall Variablen vom Typ „Range“.

Andere praktische und häufig genutzte Tabellenfunktionen sind beispielsweise:

• Min / Max: Liefert den kleinsten / größten Wert
• Avg: Liefert dem Mittelwert
• Count: Liefert die Anzahl von Zellen im Bereich mit numerischem Inhalt /
• CountA: Liefert die Anzahl von nicht leeren Zellen im Bereich
• CountBlank: Liefert die Anzahl von leeren Zellen im Bereich
• SumIfs / CountIfs: Wie SUMMEWENNS und ZAEHLENWENNS im Tabellenblatt
• v.a.m.

Vorteile: Die Ausführung ist schneller, der Code ist übersichtlicher und weniger fehleranfällig.

ZELLE()

Excel Tipp #005 | 13.01.2017

Mit der Funktion ZELLE(Infotyp; [Bezug]) können Sie verschiedene Informationen, die Sie sonst nur über VBA-Programmierung ermitteln können, in Ihre Kalkulationstabelle integrieren. In Infotyp geben Sie an, welche Information ausgegeben werden soll, mit Bezug die Zelle, deren Information abgefragt werden soll. Wenn Sie Bezug weglassen, wird die Zelle ausgewertet, in die die Formel eingetragen ist. Wenn Bezug ein Bereich aus mehreren Zellen ist, wird jeweils nur die erste Zelle (links oben) ausgewertet.

Arbeiten Sie mit unterschiedlichen Sprachversionen von Excel, sollten Sie anstatt der deutschen Bezeichnung die englischen verwenden.

Die nachfolgende Tabelle gibt einen kurzen Überblick über die Möglichkeiten für Infotyp (in Klammer jeweils die englische Version):

adresse (address): Adresse der ersten Zelle (links oben) in Bezug

zeile (row), spalte (column): Nummer der ersten Zeile / Spalte in Bezug

breite (width): Breite der Zelle (gerundet auf eine ganze Zahl).

dateiname (filename): Dateiname mit vollständigem Pfad und Blattname (ist leer, wenn die Datei noch nicht gespeichert wurde), z.B. S:\Angebote\[Angebot_012.xlsx]Übersicht

farbe (color): Ist 1, wenn die Zelle für negative Werte andersfarbig formatiert ist, ansonsten 0.

klammern (parentheses): Ist 1, wenn die Zelle für positive oder alle Werte mit Klammern formatiert ist, ansonsten 0.

schutz (protect): Liefert 1, wenn die Zelle gesperrt ist, ansonsten 0 (Null).

präfix (prefix): Liefert das sogenannte „Beschriftungspräfix“ der Zelle in Bezug:

• (') Einfaches Anführungszeichen: linksbündiger Text
• (") Doppeltes Anführungszeichen: rechtsbündiger Text
• Zirkumflexzeichen (^): zentrierter Text
• Umgekehrter Schrägstrich (\): ausgefüllter Text
• Leere Textzeichenfolge: alle anderen Inhalte

typ (type): Liefert den Textwert des Datentyps in der Zelle: 

• „b“: Zelle ist leer (blank)
• „l“: Beschriftung (label), für Zellen mit Textkonstanten (z.B. benannter Bereich)
• „w“: Wert, für Zellen mit anderen Inhalten als den beiden oben genannten

Hinweis:Die Funktion ZELLE wird nach einer Formatänderung nicht sofort neu ausgewertet, sondern erst wenn der Inhalt einer Zelle geändert oder eine manuelle Berechnung mit F9 angestoßen wurde.

VBA: Application.Volatile

Excel Tipp #004 | 02.11.2016

Benutzerdefinierte Excel-Funktionen berechnen sich nur dann neu, wenn sich die Eingangsvariablen ändern. Ein Beispiel: Sie haben eine einfache VBA-Funktion mit einer Eingangsvariablen, die den übergebenen Wert mit einem fixen Faktor multipliziert. Der fixe Faktor ist in der Funktion aber nicht als Zahl angegeben, sondern holt sich den Wert aus einer fixen Zelle, in diesem Fall A2:

Mit dieser Funktion berechnen Sie die Zelle C1 und geben B1 als Eingangsvariable an:

Nach Eingabe der Formel in C1 und auch bei einer Änderung in B1 wird in C1 stets das richtige Ergebnis angezeigt. Wenn Sie allerdings A1 ändern, ändert sich C1 nicht! Erst wenn Sie Application.Volatile (am besten gleich unter dem Prozedurkopf) eintragen, wird auch bei einer Änderung von A1 die Zelle C1 neu berechnet.

Aber Vorsicht: Die Zellen, in denen diese Funktion in der Formel enthalten ist, wird bei jeder Eingabe neu berechnet. Bei einigen hundert Zellen können die Berechnungszeiten dann schon spürbar werden. Daher wird empfohlen, in so einem Fall auch die fixe Referenz als Eingangsvariable zu definieren.

Excel Tipps: Datenüberprüfung

Excel Tipp #003 | 12.10.2016

Wenn Sie beispielsweise in einem Formular einschränken wollen, was in eine Zelle eingegeben werden darf, können Sie dafür die sogenannte Datenüberprüfung verwenden (im Menüband unter „Daten“, Gruppe „Datentools“):

Hier können Sie für einzelne Zellen oder einen ganzen Bereich die Gültigkeitskriterien einstellen. Sie können den Datentyp und Wertebereich überprüfen (ganze Zahl, Dezimalzahl, Datum, Uhrzeit), Listen zulässiger Werte angeben oder eine eigene Formel für die Gültigkeitsprüfung definieren. Besonders hilfreich ist bei der Liste zulässiger Werte, diese als Dropdown unterhalb der Zelle anzeigen zu lassen:

Zusätzlich ist es möglich, dem Benutzer bei der Auswahl der Zelle oder nach einer falschen Eingabe eigene Hilfetexte anzuzeigen (Reiter Eingabemeldung und Fehlermeldung im Dialogfeld Datenüberprüfung).

Bitte beachten Sie jedoch, dass die Datenüberprüfung keine hundertprozentige Sicherheit bietet! Wenn nämlich ein Benutzer einen Wert aus der Zwischenablage einfügt, wird damit die Datenüberprüfung ausgehebelt. Daher empfiehlt es sich, vor der Verwendung die Daten zu überprüfen und ungültige Eingaben hervorzuheben. Für die Kennzeichnung gibt es verschiedene Möglichkeiten, zum Beispiel:

Ungültige Daten einkreisen		Befehl in Menüband „Daten“, Gruppe „Datentools“
Bedingte Formatierung		Mit einer formelbasierten bedingten Formatierung falsche Eingaben hervorheben.
Hinweis in anderer Zelle		Abhängig vom Zellinhalt wird in einer benachbarten Zelle eine Warnung angezeigt.

Das Einkreisen ungültiger Daten ist recht übersichtlich, es werden aber nur die ersten 255 Fehler angezeigt. Bei jeder Eingabe wird die Markierung entfernt – auch dann, wenn es sich um eine ungültige Eingabe handelt. Die beiden anderen Lösungen arbeiten zuverlässiger. Wie diese realisiert werden, zeigen wir Ihnen in einem der nächsten Tipps.

Excel Tipps: VBA: Application.ThisCell

Excel Tipp #002 | 16.09.2016

Sind Sie schon einmal in der Situation gewesen, dass sie in einer benutzerdefinierten Funktion wissen mussten, aus welcher Zelle sie aufgerufen wurde?

Das können Sie ganz einfach mit Application.ThisCell ermitteln. Diese Eigenschaft des Application-Objekts liefert die aufrufende Zelle als Range-Objekt zurück. Daraus können Sie u.a. die Zell-Adresse, den Wert oder die Formatierung auslesen.

Ein Beispiel:

In Zelle D2 eingetragen liefert die Funktion die eigene Adresse als Text:

Excel Tipps: Tabellenformatierung

Excel Tipp #001 | 26.08.2016

In Excel lassen sich Bereiche als Tabelle formatieren.

Vorteile: Formeln sind leichter lesbar, weil statt den Bezügen mit Spalte und Zeilen der Feldname angezeigt werden.

Um die Tabellenformatierung zu aktivieren, klicken Sie auf „Als Tabelle formatieren“ (im Menüband „Start“) und wählen die gewünschte Formatvorlage aus.

Außerdem können Sie mit einem Klick auf das Kontextmenü eine Ergebniszeile hinzufügen:

In der Ergebniszeile stehen im Dropdown neben der Summe auch andere Funktionen zur Verfügung.