Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Allgemeine Angaben zum Umgang mit ZeitwertenTipp

Kategorien: Basics ▸ Datum/Zeit und Datum/Zeit ▸ Basics

(Tipp 226) Nachricht zum Beitrag an Autor Nach oben

Bei der Eingabe von Zeitwerten wird von Excel sofort das entsprechende Format zugewiesen. Dabei geht Excel wie folgt vor:

Im Standard-System (1900-Datumssystem) wird dem Zeitpunkt 01.01.1900 der Zeitwert 1 zugewiesen. Für jeden weiteren Tag wird um 1 aufaddiert. So hat der 2. Januar 1900 den Wert 2.

Stunden/Minutenangaben folgen hinter dem Komma, eine Stunde hat also den Wert 1 Tag geteilt durch 24 Stunden = 0,0416666. Der Zeitpunkt 01.01.1900 01:00:00 hat z.Bsp. den Wert 1,04166666666667. Eine Minute wäre entsprechend 1 Tag/24 Stunden/60 Minuten.

Wichtig: trotz Eingabe eines Datumwertes (z. B. 01.01.1999) wandelt Excel dies sofort in eine Zahl (36161) um und es erscheint nur durch die Formatzuweisung als Datum. Der Unterschied zwischen Inhalt bzw. Formel der Zelle (Value/Formula) und Aussehen der Zelle (Text) ist hier also besonders wichtig - vor allem auch, wenn es um Zeiten und die berüchtigten #-Zeichen geht. Letztere werden nur angezeigt, während in der Zelle in diesem Fall der korrekte Inhalt steht.

Anleitung: Übergabe von einer Userform-Textbox in eine ZelleMakro/Sub/ProzedurTipp

Kategorie: Steuerelemente ▸ Userform

(Tipp 86) Nachricht zum Beitrag an Autor Nach oben

Wie kann man Werte von einer Userform-Textbox an eine Zelle übergeben?

So soll die Userform am Ende aussehen.

Hier ist eine kleine Anleitung zum Probieren.
Nimm Dir am besten eine neue Mappe und vollziehe die folgenden Schritte nach.

  • Wechsle mit Alt + F11 in den VBA-Editor und klicke dort im Projektexplorer (normalerweise oben links) in Deine Mappe.
  • Wähle jetzt Einfügen - Userform. Zeichne in diese Userform oben eine Textbox (Textfeld). Die Eingaben in dieses Textfeld sollen dann sofort in die Zelle A2 der aktiven Tabelle übernommen werden.
  • Klicke doppelt auf die Textbox. Daraufhin erscheint das Klassenmodul der Userform, in das die Prozeduren eingetragen werden, die bei der Arbeit mit den Userform-Elementen ausgeführt werden sollen.
  • Jetzt siehst Du auch schon zwei Zeilen, die Du so vervollständigst (Eingaben in die Textbox werden damit sofort in A2 der aktiven Tabelle übernommen):

Private Sub TextBox1_Change() range("A2") = TextBox1.Text End Sub

  • Wechsle nun über das Menü Fenster oder mit der Tastenkombination Strg + F6 wieder zur Userform. Zeichne dort eine weitere Textbox und eine Schaltfläche (Übernehmen) unter dieser Textbox.
  • Doppelklicke jetzt auf die Schaltfläche und Du landest wieder im Klassenmodul.
  • Es sind zwei neue Zeilen hinzugekommen, die Du so ergänzt:

Private Sub CommandButton1_Click() Range("A3") = TextBox2.Text End Sub

Der zweiten Textbox ist diesmal keine Prozedur zugeordnet, also passiert beim Eintragen erstmal nichts. Der Inhalt dieser Textbox wird erst beim Klick auf die Schaltfläche in A3 übernommen.

  • Eigentlich ist die Userform jetzt schon zur Arbeit bereit. Was wäre aber ein Dialogfeld ohne Schließen-Schaltfläche? Also basteln wir schnell noch eine. Deshalb - wieder zurück zur Userform (Fenster oder Strg + F6).
  • Male nun unten in die Userform eine Schaltfläche und doppelklicke darauf.
  • Die Prozedur müßte diesmal so vervollständigt werden, sie ist dafür zuständig, daß die Userform geschlossen wird:

Private Sub CommandButton2_Click() Unload Me End Sub

  • Die Beschriftungen der Schaltflächen kannst Du noch ändern, indem Du wieder zur Userform zurückkehrst und zweimal auf die Schaltfläche klickst (kein Doppelklick!).
  • So, jetzt müssen wir noch für den Aufruf sorgen, denn ein Anwender soll ja nicht jedesmal in den Editor wechseln, um die Userform aufzurufen. Die Prozedur dafür schreibt man normalerweise in ein allgemeines Modul - also wähle Einfügen - Modul.
  • In das leere Modul schreibst Du:

Sub Aufruf() UserForm1.Show End Sub

  • Wechsle nun wieder zu Excel und zeichne aus der Formular-Symbolleiste eine Schaltfläche auf das Blatt (moderner wäre eine Schaltfläche aus der Steuerelemente-Toolbox, aber wir wollen es ja am Anfang nicht so schwer machen). Jetzt öffnet sich ein Dialogfeld, in dem Du der Schaltfläche ein Makro zuweisen kannst - also Doppelklick auf Aufruf - denn so hatten wir das Makro ja benannt.
  • Das wars. Jetzt kannst Du auf die Schaltfläche klicken, die Userform erscheint und Du wirst sehen, daß Eintragungen in die erste Textbox sofort übernommen werden, Eintragungen in die zweite Textbox erst beim Klick auf die Schaltfläche.

Zusammenfassung:

In Deiner Mappe hast Du jetzt:

  • ein Modul, auf dem die Prozedur zum Aufruf der Userform enthalten ist,
  • eine Userform mit zwei Textboxen und zwei Schaltflächen (Commandbuttons),
  • das Klassenmodul der Userform, auf dem die Routinen enthalten sind, die bei der Arbeit mit den Userform-Elementen aufgerufen werden.

Und nochmal der Tip: Im Editor kann man ganz einfach mit Strg + F6 zwischen den einzelnen Komponenten wechseln. Mit Strg + Tab ist es zwar auch möglich, bereitet aber Probleme, wenn Multipage-Elemente enthalten sind.

Der Code zum Kopieren:

Private Sub TextBox1_Change() Range("A2") = TextBox1.Text End Sub Private Sub CommandButton1_Click() Range("A3") = TextBox2.Text End Sub Private Sub CommandButton2_Click() Unload Me End Sub Sub Aufruf() UserForm1.Show End Sub

Arrayformeln (01)FormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 18) Nachricht zum Beitrag an Autor Nach oben

Was sind die Arrayformeln, die ab Excel 365 eingeführt wurden?

Mit Excel 365 wurde das System der Formeln neu erarbeitet, wie mehrfach berichtet wurde. Allerdings handelt es sich dabei eigentlich nur um eine Ergänzung, wenn auch eine sehr sinnvolle und nützliche. Die alten Formeln arbeiten ganz normal weiter - hier muss nicht befürchtet werden, dass irgendetwas nicht mehr funktioniert. Neu sind hingegen die Arrayformeln. Zum Vergleich: Bisher konnte in Formeln mit kompletten Bereichen gearbeitet werden, zum Beispiel mit Summewenn. Die Eingabe der Formeln musste dazu mit Strg + Umschalt + Enter abgeschlossen werden, woraufhin Excel um die Formel geschweifte Klammern {} legte. Die Formel wurde somit als Matrixformel erkannt und es wurde ein kompletter Bereich verarbeitet. Das ERgebnis hingegen stand als ein Wert in einer Zelle.
Mit den Arrayformeln wurde dies erweitert und auch vereinfacht. Sollen komplette Bereiche verarbeitet werden, wird die Eingabe der Formel ganz normal beendet; die geschweiften Klammern werden nicht mehr benötigt. So zum Beispiel diese Formel, die in B2:B13 Nach "Juni" sucht und aus C2:C13 die Werte addiert:

=SUMMEWENN(B2:B13;"Juni";C2:C13)

Auch hier haben wir in einer Zelle ein Ergebnis, nämlich die Summe. Der Unterschied bis hierher liegt nur in den fehlenden geschweiften Klammern.


Überlaufende bzw. verschüttete Formeln

Der größte Unterschied wird jedoch deutlich, wenn in eine Zelle folgende einfache Formel eingegeben wird:

=A2:C13

Geschieht dies bei einer Excelinstallation zum ersten Mal, erscheint eine Meldung:

Formel übergelaufen: Ihre Formel hat mehrere Werte zurückgegeben, weshalb wir sie in die benachbarten leeren Zellen haben überlaufen lassen.

Das Ergebnis wird dann auch deutlich: Die Formel liefert alle Werte, die sich im Bereich befinden, der in der Formel angegeben ist - von der 1 in A2 bis zur 600 in C13. Der erste Wert steht dabei in der Zelle mit der Formel, die anderen Werte rechts neben und unter dieser Zelle - die Formel ist übergelaufen. Das Verhalten ist vergleichbar mit der CSS-Eigenschaft float: left;. Allerdings steht die Formel tatsächlich nur in der einen Zelle; sie kann auch nur dort bearbeitet werden. Sichtbar wird das, wenn im Ergebnis der Inhalt einer anderen Zelle gelöscht werden soll - es geht nicht.

Dieses Verhalten, dass mehrere Ergebnisse ausgegeben werden sollen, kann vielfältig genutzt werden. Beispiel für eine weitere einfache Formel:

=WENN(C2:C13>400;B2:B13;"")

In Zeile 2 neben der Basistabelle liefert sie in den entsprechenden Zeilen die Monate, bei denen die Beträge größer als 400 sind. Das Gleiche passiert natürlich auch, wenn die Formel in eine andere Zeile eingetragen wird - nur hat man dann den Offset, weil die Ergebnisse ab der Zelle mit der Formel angezeigt werden.

Diese Formel würde die Zahlen in den entsprechenden Zeilen anzeigen, die zum "Juni" gehören:

=WENN(B2:B13="Juni";C2:C13;"")

Natürlich kann die als Arrayfunktion verwendete Wenn-Funktion auch in anderen Funktionen eingesetzt werden. Hier werden alle Zahlen aus C addiert, wenn sie größer als 400 sind:

=SUMME(WENN(C2:C13>400;C2:C13))

In dem Fall haben wir natürlich wieder nur ein Ergebnis, nicht eine Matrix aus mehreren Werten


Fehler: #ÜBERLAUF! bzw. #SPILL!

Dieser Fehler erscheint, wenn Excel eine Arrayformel nicht berechnen oder deren Ergebnisse nicht darstellen kann.

Meist wird dies der Fall sein, wenn schlicht zu wenig Platz für die Ausgabe ist. Dann erscheint in der Zelle mit der Formel die Meldung und es wird mit einem Rahmen dargestellt, wie viel Platz benötigt würde. Im Weg können dabei Zellinhalte sein, aber auch das Ende der Tabelle, verbundene Zellen usw.

Auch wenn in einer Arrayfunktion Zufallszahlen oder andere Ergebnisse verwendet werden, kann der Fehler erscheinen. Dann ist die innere Funktion (zum Beispiel eine für Zufallszahlen) noch nicht fertig, während die äußere aber schon rechnen möchte. Da aber die Ergebnisse der inneren Funktion fehlen bzw. unvollständig sind, kommt diese Meldung.


Schnittmengenoperator @

Möglicherweise haben Sie schon die Meldung bekommen:

Warum ist der @-Operator hier? Wir haben ein Upgrade der Formelsprache von Excel durchgeführt. Hieraus resultiert, dass Ihnen vielleicht in manchen Formeln der @-Operator auffallen wird. Ihre Formeln verhalten sich auf dieselbe Weise wie immer.

Gleichzeitig kann es sein, dass sich in Ihren Formeln auf einmal @-Zeichen nach den Gleichheitszeichen befinden, die Sie gar nicht eingetragen haben.

Dieses Verhalten hängt unmittelbar mit den Arrayformeln bzw. Arrayfunktionen zusammen. Nehmen wir obige Formel:

=WENN(C2:C13>400;C2:C13;"")

Sie liefert, wie wir gesehen haben, alle Werte, die größer als 400 sind, in und unter der Zelle mit der Formel. Was nun aber, wenn wir nur ein Ergebnis benötigen, das erste? Dieser Fall kann auftreten, wenn Formeln über mehrere Zellen gezogen oder wenn Funktionen verschachtelt werden sollen. In diesem Fall setzen wir direkt hinter das Gleichheitszeichen das @-Zeichen:

=@WENN(C2:C13>400;C2:C13;"")

Nun haben wir nur noch ein Ergebnis; in diesem Fall das erste, die 500.

Um bei diesem Beispiel zu bleiben: Setzen wir in der Formel die letzte Zeile absolut (durch das $-Zeichen), kann die Formel nach unten gezogen werden und wir haben als Ergebnis immer den Wert aus den Zellen ab der Zeile mit der Formel:

=@WENN(C2:C$13>400;C2:C$13;"")

Bedingte Formatierung: Drei Preise - günstigsten farbig kennzeichnenFormellösungArrayfunktion/MatrixfunktionTipp

Kategorien: Format ▸ Bedingt und Tabelle ▸ Matrix

(Tipp 326) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die günstigsten Preise hervorheben?

In einer Tabelle stehen in einer Spalte (hier: D) untereinander verschiedene Produktnamen. Bei jedem Produkt stehen rechts daneben (hier: E, F und G) die Preise für drei verschiedene Länder.

Die Preise bei jedem Produkt sollen automatisch farbig markiert werden: der günstigste grün, der höchste rot und ansonsten gelb.

Lösung


Die Aufgabe wird mit Bedingte Formatierung im Ribbon Start erfüllt.

Dazu müssen für jede der drei Preisspalten jeweils drei Regeln erstellt werden - eben eine für den günstigsten, eine für den höchsten und eine für den restlichen Preis. Wir fangen an mit der ersten Spalte, in der Preise enthalten sind, hier also E2:E19 und markieren diese. Anschließend rufen wir die bedingte Formatierung auf und wählen dort Neue Regel.

In der Auswahlliste wählen wir den Punkt Nur Zellen formatieren, die enthalten. Dies ist die Basis für die weiteren Formatierungen.

Grüne Formatierung

Die grüne Formatierung soll erscheinen, wenn der Preis am niedrigsten ist. Hier bietet sich also die Funktion MIN zum Vergleich mit den anderen beiden Preisen an. Wir stellen also beim Zellwert auf kleiner als und tragen rechts ein: =MIN(F2:G2). Mit dem Button Formatieren legen wir die grüne Hintergrundfarbe fest.

Mit OK übernehmen wir die Regel.

Rote Formatierung

Mit rotem Hintergrund soll gekennzeichnet werden, wenn der Preis am höchsten ist. Dazu ist die Funktion MAX sinnvoll.

Wir bleiben also im Dialog und wählen Neue Regel. Anschließend führen wir die gleichen Schritte wie bei der grünen Formatierung durch, nur eben mit größer als, MAX und der roten Formatierung.

Gelbe Formatierung

Einen gelben Hintergrund soll die Zelle bekommen, wenn der Preis nicht am höchsten und nicht am niedrigsten ist, wenn er also zwischen den beiden anderen Preisen liegt.

Wir bleiben weiterhin im Dialog und wählen wieder Neue Regel. Wenn wir nun wieder Nur Zellen formatieren, die enthalten anklicken, müsste beim Zellwert schon zwischen ausgewählt sein - wenn nicht, dies nachholen.

In die Felder schreiben wir jeweils =F2 und =G2. Gelben Hintergrund auswählen und bestätigen. Nun müssten die drei Regeln im Manager angezeigt werden.

Wenn nun Zahlen eingetragen werden, sollte das beim ersten Land funktionieren, die Hintergründe müssten automatisch entsprechend der Zahlen formatiert werden. Allerdings müssen die Schritte noch für die anderen beiden Länder wiederholt werden. Beim mittleren Land aufpassen; die Zellen in MIN und MAX müssen hier mit Semikolon getrennt werden, weil es keine Bis-Bereiche sind, sondern auseinanderliegende Zellen.


Auswertung: Matrixformel

Unter der Tabelle sollen nun noch zu jedem Land die grünen, roten und gelben Zellen gezählt werden. Das Problem: Mit einer reinen Formellösung können keine farbigen Zellen gezählt werden.

Ein Lösungsansatz ist, zu zählen, wie viele Zellen in der jeweiligen Spalte größer bzw. kleiner als die Zellen daneben sind. Damit wir nicht jede Zeile einzeln berücksichtigen müssen, verwenden wir dazu eine Matrixformel, die den Bereich einer Spalte über alle Zeilen hinweg erfasst.

Hinweis: Die geschweiften Klammern nicht eingeben, sondern die Eingabe der Formel mit Strg + Umschalt + Enter abschließen. Damit erscheinen die geschweiften Klammern automatisch. Ab Excel 365 sind die geschweiften Klammern nicht mehr notwendig.

Wir beginnen beim ersten Land, hier mit der Formel in E24. Es sollen die Zellen gezählt werden, die in den Zeilen die niedrigsten Preise haben. Dabei zählen wir aber nicht, sondern wir addieren für jede dieser niedrigsten Zellen die 1. Wir bilden also die Summe, hier das Grundgerüst:

=SUMME( wenn Zahl in der Zeile am niedrigsten ; dann addiere 1; sonst addiere 0))

Wir verwenden hier diese Logik (andere Varianten gibt es natürlich auch):

Wenn die Zahl beim Land 1 (E) kleiner als die Zahl beim Land 2 (F) ist, dann wenn die Zahl beim Land 1 (E) auch kleiner als die beim Land 3 (G) ist, dann addiere 1, sonst 0, sonst 0.

Wir addieren hier also zweimal 0 - einmal für die erste Bedingung (Land 1 nicht kleiner als Land 2) und einmal für die zweite Bedingung (Land 1 nicht kleiner als Land 3).

Diese Struktur bauen wir in die Formel ein, so dass die (an Strg + Umschalt + Enter denken!) nun so aussieht:

{=SUMME(WENN(E2:E19<F2:F19;WENN(E2:E19<G2:G19;1;0);0))}

Damit haben wir die Anzahl der grünen Zellen beim Land 1 in E24. Die gleiche Formel kommt zu den anderen Ländern. Vorsicht, die kann aber nicht gezogen werden, weil die Zellen in den Formeln angepasst werden müssen.

Ebenfalls die gleiche Formel, nur mit dem größer als >, kommt in die Zeile 26, wo die roten Zellen gezählt werden:

{=SUMME(WENN(E2:E19>F2:F19;WENN(E2:E19>G2:G19;1;0)))}

Bei den gelben Zellen geht es einfacher - einfach alle Zellen in der Spalte zählen und die grünen und roten subtrahieren:

=ANZAHL(E2:E19)-E24-E26

Eine Beispieldatei mit dieser Lösung: 326_preisvergleiche.xlsx

Bei Eingabe in eine Zelle (Worksheet_Change)Makro/Sub/Prozedur

Kategorien: Tabelle ▸ Zellen und Ereignisse ▸ Tabellen

(Tipp 153) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich erreichen, daß bei einer Eingabe in eine Zelle andere Eintragungen automatisch erfolgen?

Grundsätzlich

  • Mit Alt und F11 in den VBA-Editor wechseln,
  • im Projekt-Explorer auf die Tabelle doppelklicken, in der die Eingaben vorgenommen werden,
  • dort entsprechenden Code eingeben.

Fragen treten oft auf, wenn es Endlosschleifen gibt, wenn Code etwas in einen Bereich einträgt, der durch einen Eventhandler überwacht wird. Die Änderung löst das Event aus, wodurch Code aufgerufen wird, der dann wieder etwas einträgt, was dann wieder das Event auslöst usw.

In solchen Fällen bietet sich an, vor dem Vornehmen von Änderungen die Events/Ereignisse auszuschalten und danach gleich wieder zu aktivieren:

Application.EnableEvents = False '… Code … Application.EnableEvents = True


Datum über der Eingabezelle eintragen

Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Row = 2 Then Cells(Target.Row - 1, Target.Column) = Date End Sub


Wert auf anderes Blatt in erste leere Zelle eintragen

Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim lngZ As LongPtr If Target.Address <> "$B$1" Then Exit Sub lngZ = Sheets(2).Cells(Rows.Count, 4).End(xlUp).Row Sheets(2).Cells(lngZ + 1, 4) = Range("b1") End Sub

Es wird zunächst geprüft, ob die Eingabezelle wirklich B1 ist.

Anschließend wird die Zeile der letzten ausgefüllten Zelle auf Blatt 2 in Spalte 4 (D) festgestellt. Dort wird die Eingabe in die nächste Zeile (lngZ + 1) eingetragen.


Bei Änderungen ungerade Spalten ausschließen

Frage: Wie kann ich bei der Änderung einer Auswahl in den Spalten 3 bis 49 abfragen, ob es sich um eine ungerade Spalte handelt?

Die Routine fragt ab, ob es sich bei der ausgewählten Spalte um eine Spalte handelt, bei deren Division durch 2 der Rest 1 ergibt.

Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Column > 2 And .Column < 50 And .Column Mod 2 = 1 Then Exit Sub End With 'Hier geht's weiter End Sub

Das 1904-Datumssystem oder Wie kann ich mit negativen Zeiten rechnen?Tipp

Kategorien: Basics ▸ Datum/Zeit und Datum/Zeit ▸ Zeit

(Tipp 227) Nachricht zum Beitrag an Autor Nach oben

Oftmals bekommt man auf die Frage Wie kann ich negative Zeiten darstellen? die Antwort Stelle unter Optionen/Berechnen auf das 1904-Datumssystem um.

Diese Umstellung hat jedoch gravierende Folgen, denn sämtliche Daten von Arbeitsmappen, die unter dem Standard-Datumssystem erfasst worden sind, sind nicht kompatibel dazu.

Im Standard wird dem Datum 01.01.1900 der Wert 1 zugewiesen, im 1904-System bekommt der 01.01.1904 diesen Wert. Das bedeutet, dass alle Daten des Standarddatumssystems um 4 Jahre verschoben werden und somit dieses System sich im Grunde nur für reine Stundenberechnungen eignet.

Zweitens kann man immer noch nicht negative Zeiten eingeben, sondern diese nur als Ergebnis einer Berechnung erhalten. So ist die Eingabe -04:00 nicht erlaubt, aber =01:00-05:00 liefert das gewünschte Ergebnis.

Bevor man also auf das 1904-Datumssystem umstellt, sollte man sich Gedanken darüber machen, ob man dieses wirklich benötigt oder einen anderen Weg wählt. Siehe auch:



Datum per Drehfeld auswählenMakro/Sub/Prozedur

Kategorien: Steuerelemente ▸ Userform und Datum/Zeit ▸ Steuerelemente

(Tipp 79) Nachricht zum Beitrag an Autor Nach oben

Wie kann man ein Datum per Drehfeld in einer Userform auswählen?

Der Code benötigt eine Userform1, darauf eine TextBox1 und einen SpinButton1 (Drehfeld).

Für den Aufruf der Userform legen wir fest, dass das Maximum des SpinButtons gesetzt wird, hier 401768 für den 31.12.2999. Außerdem soll in der TextBox das aktuelle Datum angezeigt und beim SpinButton der Wert des aktuellen Datums eingestellt werden:

Private Sub UserForm_Initialize() SpinButton1.Max = 401768 TextBox1.Value = Format(Now, "dd.mm.yyyy") SpinButton1.Value = CLng(CDate(TextBox1.Value)) End Sub

Dem SpinButton weisen wir zu, dass beim Betätigen dessen Wert im Datumsformat in die Textbox eingetragen wird:

Private Sub SpinButton1_Change() TextBox1.Text = Format(SpinButton1.Value, "dd.mm.yyyy") End Sub

Fehlt noch die TextBox - wenn dort ein gültiges Datum (Angaben durch Punkt oder Minus getrennt) eingetragen wird, soll der Wert dieses Datums dem SpinButton zugewiesen werden, damit bei dessen Betätigen in der Textbox das vorherige oder nächste Datum eingetragen wird:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not IsDate(TextBox1.Text) Then MsgBox "Die Eingabe ist kein gültiges Datum." TextBox1.Text = Format(SpinButton1.Value, "dd.mm.yyyy") Exit Sub End If SpinButton1.Value = CLng(CDate(TextBox1.Text)) End Sub

Der Code zum Kopieren:

Private Sub UserForm_Initialize() SpinButton1.Max = 401768 TextBox1.Value = Format(Now, "dd.mm.yyyy") SpinButton1.Value = CLng(CDate(TextBox1.Value)) End Sub Private Sub SpinButton1_Change() TextBox1.Text = Format(SpinButton1.Value, "dd.mm.yyyy") End Sub Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not IsDate(TextBox1.Text) Then MsgBox "Die Eingabe ist kein gültiges Datum." TextBox1.Text = Format(SpinButton1.Value, "dd.mm.yyyy") Exit Sub End If SpinButton1.Value = CLng(CDate(TextBox1.Text)) End Sub

Entweder in C1 oder in D1 Eingabe möglichTipp

Kategorie: Tabelle ▸ Datenüberprüfung

(Tipp 247) Nachricht zum Beitrag an Autor Nach oben

Wenn in C1 etwas steht, soll in D1 eine Eingabe unmöglich sein und umgekehrt. Wie kann man das realisieren?

  1. D1 aktivieren,
  2. Ribbon Daten ▸ Datenüberprüfung,
  3. Zulassen ▸ Benutzerdefiniert,
  4. eingeben: =(C1=""),
  5. evtl. Fehlermeldung festlegen,
  6. Vorgang für C1 mit =(D1="") wiederholen.

Excel-Kommunikator: Mails versenden und Chat mit Excel

Kategorie: Add-In ▸ Kommunikation

(Tipp 590) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus Excel heraus schnell Informationen und Daten mit anderen Excel-Usern austauschen?

Excel-Kommunikator: Chat und E-Mail per Excel

Das Add-In ist der Nachfolger des Mail-Add-Ins, an dem immer wieder Interesse bestand. Allerdings war es mit der Zeit veraltet, so dass eine komplette Neuentwicklung langsam überfällig war. Das hier vorgestellte Add-In sollte ab der Version 2007 funktionieren - Nutzer des alten Add-Ins finden hier nur eine andere (und umfangreichere) Bedienung; die alten Daten können mit der gleichen Syntax übernommen werden.


Mit diesem Chat- und Mail-Add-In ist es möglich, Gespräche zu führen, Bereiche aus Exceltabellen auszutauschen und Mails mit Anhängen zu senden. Es ist also ein schneller Informationsaustausch gewährleistet, ohne zu einem anderen Programm wechseln zu müssen. Der Austausch erfolgt dabei überwiegend im Menüband in eigenen Ribbon-Tabs. Bei den Informationen kann es sich um einfache Postings handeln, um Inhalte von Zellen oder Bereichen (Werte oder Formeln) oder um Mails mit Anhängen. So ist es zum Beispiel möglich, die aktuelle Mappe per Mausklick an mehrere Empfänger zu senden oder Tabellenbereiche an andere Nutzer zu übermitteln.

Basis ist ein eigener Nick, der frei gewählt werden kann, jedoch insgesamt nur einmal vorkommen darf. Nach Groß-/Kleinschreibung wird dabei unterschieden. Dieser Nick muss einmalig im System registriert werden, danach erfolgt die Kommunikation pro Add-In immer mit diesem Nick. Jeder Teilnehmer sieht Sie dann also unter diesem Namen. Die Absenderangaben bei Mails können natürlich unabhängig davon verwendet werden, so dass ein Empfänger Ihren richtigen Namen sieht, wenn Sie diesen beim Mailversand angeben.


Nach oben Chat

Eigener Nickname

Mit diesem sind Sie überall sichtbar. Eintragen, Frage nach Registrierung mit »Ja« beantworten, fertig. Pro Add-In wird ein Name vergeben; Sie können also die Add-In-Datei auch an einem anderen Arbeitsplatz verwenden.

Gruppen

Sie haben die Möglichkeit, sich in verschiedenen Gruppen zu unterhalten. Die Gruppe »offen« ist voreingetragen, hier kann jeder ohne Kennwort lesen und schreiben.

Möchten Sie eine Gruppe hinzufügen, klicken Sie auf »Gruppe hinzufügen« und tragen Sie anschließend im Dialog den gewünschten Gruppennamen ein. Hier gibt es zwei Möglichkeiten:

Existiert der Gruppenname bereits, können Sie das Kennwort eintragen und so der Gruppe beitreten.
Existiert der Gruppenname noch nicht, können Sie ein Kennwort vergeben und so Ihre eigene Gruppe erstellen. Den Namen und das Kennwort können Sie dann ausgewählten Personen geben, damit diese auf diesem Weg dieser Gruppe beitreten können. So können Sie sich mit einem begrenzten Personenkreis unterhalten, zum Beispiel einer Mitarbeitergruppe.

Im Gruppe-Auswahlfeld wählen Sie ganz einfach, welche Postings Sie lesen und wo Sie schreiben möchten.

Posten und Listenanzeige

Ihren Text geben Sie nach der Wahl der gewünschten Gruppe einfach in »Ihre Msg« ein und senden ihn mit »Enter« ab. In der Liste »Msgs« sehen Sie die aktuellen Beiträge chronologisch rückwärts, also den aktuellsten Beitrag ganz oben.

Zellen und Bereiche posten

Sie können nicht nur eigene Postings schreiben, sondern auch Inhalte aus der Tabelle posten.

Was dabei übermittelt wird, hängt vom Kästchen »Formel« ab - ist es deaktiviert, werden die Werte der Zellen übermittelt (im Beispiel „-06:30“), sonst die Formeln, falls welche eingetragen sind (sonst auch die Werte).

Klicken Sie auf »Zelle posten«, wenn Sie den Inhalt der gerade aktiven Zelle posten möchten. Dieser erscheint dann wie gehabt in der Liste der Postings.

Markieren Sie einen Bereich und wählen Sie »Bereich posten«, wenn Sie einen kompletten Tabellenbereich übermitteln möchten. In diesem Fall erscheint in den Postings nicht der Inhalt des Bereiches, sondern der Hinweis, dass es sich um einen Tabellenbereich handelt und um welchen Bereich. Dies ist wichtig, um diesen Inhalt später wieder an der richtigen Stelle einfügen zu können.

Ausgaben

Postings können auch in Tabellen übernommen werden. Wichtig ist dafür, dass das gewünschte Posting in der Liste gewählt und der Zielbereich ab der aktiven Zelle frei ist - bereits vorhandene Zellinhalte werden sonst überschrieben.

Mit »Posting in Zelle« wird das gewählte Posting in die aktive Zelle eingetragen, wie es in der Liste zu sehen ist. Ausnahme ist dabei ein Tabellenbereich - handelt es sich beim gewählten Posting um einen solchen, wird der Bereich entsprechend des Quellbereiches ab der aktiven Zelle eingetragen. Sind Formeln enthalten, ist es sinnvoll, dass die aktive Zelle die ist, die im Beginn des Tabellenbereiches angegeben ist, damit die Bezüge in den Formeln stimmen. Im Beispiel aus dem Screenshot sollte also die aktive Zelle E1 sein, wenn das Posting mit dem Bereich eingefügt wird.

Formatierungen werden nicht übernommen, eingetragene Bereiche müssen also ggf. nachformatiert werden. Dies betrifft insbesondere Datums- und Zeitformate.

Mit »Postingliste in Tabelle« werden die Postings der gewählten Gruppe einfach in eine neu erstellte Tabelle eingetragen.

Neue Nachrichten

Aus Performancegründen werden neue Postings von anderen Nutzern nicht sofort angezeigt.

Die Aktualisierung der Beiträge in einer Gruppe erfolgt über den Button »Refresh«.

Sollen die Beiträge einer Gruppe automatisch aktualisiert werden, aktivieren Sie »Autofrefresh [an|aus]«. Damit werden die Beiträge alle 15 Sekunden abgerufen und in der Liste aktualisiert. Im Falle eines neuen Postings wird der Chat-Tab automatisch aktiviert und es erscheint ein Hinweissymbol »Neues Posting!«.
Haben Sie »Sprachnachricht« aktiviert, wird das neue Posting vorgelesen.


Nach oben Mail

Voraussetzung für die Nutzung ist, dass in »Chat« der Nickname registriert wurde.

Absender

Die Absenderangaben werden einfach eingetragen und bleiben bis zur nächsten Änderung gespeichert. Da das Add-In keine Mails empfangen kann, sollte die Absenderadresse eine solche sein, an die der Empfänger antworten kann.

Empfänger

Zur Eingabe der Empfänger gibt es mehrere Möglichkeiten.

Die Mailadressen können direkt in das Eingabefeld eingetragen werden, mehrere durch Semikolon getrennt.

Im Bereich »Zusammenstellen« kann zwischen Add-In- und Outlookkontakten umgeschaltet werden. Im Anschluss wird der entsprechende Eintrag gewählt und mit »Zu Empfängern hinzufügen« in das Feld für die Empfänger übernommen.

Die Liste der Add-In-Kontakte wird über den kleinen Dialoglauncher unten rechts zur Bearbeitung aufgerufen.

Mailtext, Anhang

Mailtext und Anhang beim Versand einer externen Datei werden in den jeweiligen Dialogen eingegeben bzw. gewählt, die nach Klick auf »Mailtext« bzw. »Datei« erscheinen. Wichtig beim Versand des aktiven Blattes ist, dass das Add-In dort gespeichert ist, wohin das Add-In eine temporäre Datei speichern kann.

Die Dateigröße des Anhangs ist derzeit auf 500 KB begrenzt.

Versand

Mit Klick auf die entsprechende Schaltfläche erfolgt der Versand, bei Erfolg erscheint eine Meldung. Bei größeren Dateien kann dies natürlich auch einen Moment dauern.


Nach oben Backup und Restore

Backup

Natürlich kann der Fall eintreten, dass das Add-In einmal neu installiert werden muss - sei es wegen eines Updates oder wegen eines Datenverlusts. Handelt es sich dann um eine neue Add-In-Datei, wäre der Nick nicht mehr zugänglich, weil ja u. a. die Daten dazu weg wären.

Für diesen Fall (und beabsichtigte Erweiterungen) ist es möglich, ein Backup auf dem Server zu erstellen. Dazu dient der Button »Sicherung«, der dann sichtbar ist, wenn ein Nick eingerichtet wurde. Damit werden alle Einstellungen des Add-Ins gesichert.

Beim ersten Erstellen eines Backups erhalten Sie ein Kennwort, das unbedingt aufbewahrt werden muss. Ansonsten können Sie nach Belieben Backups erstellen - einfach auf den Button klicken und bestätigen.

Restore

Bei einer leeren Add-In-Datei, wenn also noch kein Nick vergeben ist, ist der Button »Restore« statt des Buttons zur Sicherung sichtbar. Nach dem Anklicken erscheint ein Dialog zum Eingeben des Nicknamens und des Kennworts, das Sie beim ersten Erstellen vergeben haben. Stimmen diese Daten, werden die Daten aus dem letzten Backup in das Add-In eingetragen und es stehen alle Funktionen/Daten wie beim Zeitpunkt des Erstellens des Backups zur Verfügung.


Nach oben Technik, Ausschlüsse und Datenschutz

Da die Daten entfernten Empfängern zur Verfügung gestellt werden, müssen sie natürlich auch irgendwo gespeichert werden. Dies erfolgt auf dem Server joerglorenz.de in einer MySQL-Datenbank, die auf dem aktuellen Stand der Technik abgesichert ist. Außer mir, dem Autor des Add-Ins, hat niemand Zugriff darauf.

Gespeichert werden nur die Daten, die den Absendern und den Empfängern vorgehalten werden müssen - also all das, was Sie über die Felder im Menüband eingeben. Dateien als Mailanhänge werden auf dem Server nur bis zum letztendlichen Versand der Mail gespeichert und anschließend gleich wieder gelöscht. Da das ganze System über Nicknamen läuft, werden auch keine sonstigen personenbezogenen Daten gespeichert - natürlich mit Ausnahme derer, die Sie selbst in den Eingabefeldern/Messages eintragen.

Es wird zugesichert, dass Ihre Daten nur zum eigentlichen Zweck Ihrer Kommunikation gespeichert werden und sonst in keiner Weise verwendet oder überwacht werden. Außer natürlich, wenn ich selbst davon nichts weiß - ausschließen kann man ja leider nichts.

Sollen bestimmte Daten gelöscht werden, wenden Sie sich bitte einfach mit Angabe des Nicknamens an mich.

Verboten sind generell Texte, die gegen Recht und Gesetz verstoßen, aber auch fanatische und insbesondere rechte bzw. fremdenfeindliche Inhalte. Sollten solche Texte bemerkt werden, wenden Sie sich bitte an mich. Diese werden dann durch mich unverzüglich gelöscht.


Nach oben Kosten

Dieses Add-In kann leider nicht kostenlos angeboten werden. Sie haben die Möglichkeit, 50 Postings abzusetzen und 50 Mails (an mehrere Empfänger gilt als eine Mail) zu versenden. Eine weitere Nutzung ist dann für 10,00 € pro Add-In (also pro Nick) möglich. Bei Nutzung in Gruppen (Unternehmen, Vereine usw.) kann es verschiedene Ermäßigungen geben, wenn mir eine Liste der zugehörigen Nicks überlassen wird.

Bei Angabe einer Rechnungsanschrift erhalten Sie natürlich in jedem Fall eine absetzbare Rechnung.

Perspektivisch wird das Add-In um weitere Funktionen ergänzt. Diese Erweiterungen sind dann natürlich im Preis bereits enthalten.

Download: excelkomm.xlam

Fehler abfangen und behandelnMakro/Sub/Prozedur

Kategorie: Interaktion ▸ Fehler

(Tipp 8) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich eine VBA-Fehlermeldung durch eine eigene ersetzen?

Beispiel 1:

Manchmal ist bei Eingaben wichtig, dass es sich wirklich um eine Zahl handelt. Deshalb wird in diesem Beispiel mittels einer Schleife solange abgefragt, bis wirklich eine Zahl eingegeben wurde:

Sub Fehlermakro() Dim varI varI = "" Do While Not IsNumeric(varI) varI = InputBox("Bitte geben Sie eine Zahl ein:", "Zahl eingeben") Loop MsgBox varI End Sub

Beispiel 2:

Eingabeaufforderung: da intI als Zahl deklariert ist, dürfen auch nur Zahlen eingegeben werden. Gibt man einen Text ein, tritt der Fehler 13 auf und es wird zur Sprungmarke Fehler: gesprungen.

Wird korrekt eine Zahl eingegeben, erscheint die Zahl als Meldung und das Makro wird verlassen.

Sub Fehlermakro1() Dim intI As Integer On Error GoTo Fehler intI = InputBox("Bitte geben Sie eine Zahl ein:", "Zahl eingeben") MsgBox intI Exit Sub 'Fehlerbehandlung: Fehler: 'Wenn der Fehler 13 aufgetreten ist ... If Err.Number = 13 Then '... eine Meldung bringen ... MsgBox "Sie haben keine gültige Zahl eingegeben.", vbOKOnly + vbExclamation, "Fehler!" 'bei einem anderen Fehler eine Meldung bringen Else MsgBox "Ein unerwarteter Fehler ist aufgetreten. Das Makro wird beendet.", vbOKOnly + vbCritical, "Unerwarteter Fehler" End If Err.Clear End Sub

Download: fehlerbehandlung.xlsm

On-Event-Ereignisse des Application-ObjektsMakro/Sub/ProzedurTipp

Kategorien: Basics ▸ Ereignisse und Ereignisse ▸ Basics

(Tipp 100) Nachricht zum Beitrag an Autor Nach oben

Die OnEvent-Methoden können in jeder beliebigen Prozedur stehen. Normalerweise stehen sie jedoch im Workbook_Open-Ereignis und sollten vor dem Beenden der Mappe mit z. B. Application.OnKey "" wieder entschärft werden. Durch die Zeichenfolge "" werden die OnEvents deaktiviert.

Ereignisse

Application.OnKey "Taste", "Makro"
bewirkt, das beim Drücken einer Taste das Makro aufgerufen wird. Z. B. ruft Application.OnKey "N", "Test", beim Drücken Taste N das Makro Test auf.
Application.OnRepeat "Wiederholung: xxx", "Makro"
bewirkt, das bei dem Kommando Bearbeiten/Wiederholen das Makro ausgeführt wird. Dabei muß man für xxx den Text eintragen, der im Menü angezeigt wird, also z. B. "Zellen einfügen"
Application.OnUnDo "Rückgängig: xxx", "Makro"
bewirkt, das bei dem Kommando Bearbeiten/Rückgängig das Makro ausgeführt wird. Dabei muß man für xxx den Text eintragen, der im Menü angezeigt wird, also z. B. "Zellen einfügen"
Application.OnTime Zeitangabe, "Makro", Endzeit
bewirkt, das zum Zeitpunkt der Zeitangabe das Makro gestartet wird. Gibt man die Endzeit (dieser Prameter ist optional) mit an, so versucht Excel bis zum Verstreichen dieser Zeit das Makro zu starten. Dies kann z. B. der Fall sein, wenn ein Dialog geöffnet ist, oder ein anderes Makro läuft. Kann Excel das Makro nicht innerhalb dieser Zeit starten, wird darauf verzichtet.
Application.OnTime Zeitangabe, "Makro", , False
setzt man den vierten Parameter auf False, wird diese OnTime-Methode deaktiviert.
Application.OnEntry = "Makro"
wird bei der Eingabe in eine Zelle ausgeführt, nicht aber, wenn ein Makro den Zellwert verändert. Dies kann gegenüber Worksheet_Change ein Vorteil sein.

Rechnen mit Variablen

Kategorie: Add-In ▸ Variablen

(Tipp 584) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich mit Variablen rechnen lassen?

Das Add-In überwacht die Eingaben und stellt fest, wenn es Einträge mit Gleichheitszeichen am Ende gibt. So können Variablen Werte zugewiesen werden und mit diesen wird gerechnet. Die Ergebnisse werden fünf Spalten neben der Eingabe eingetragen.

Download: variablenzuweisung.xlam

Rechnen ohne Gleichheitszeichen (Worksheet_Change)Makro/Sub/ProzedurUDF - benutzerdefinierte Funktion

Kategorien: Ereignisse ▸ Tabellen und Tabelle ▸ Formeln

(Tipp 417) Nachricht zum Beitrag an Autor Nach oben

In Spalte A werden Berechnungen ohne Gleichheitszeichen eigetragen. Wie erhalte ich in B die Ergebnisse?

Worksheet_Change-Ereignis

Die Routine wird im VBA-Editor in das Modul eingetragen, das durch Doppelklick auf die Tabelle, in der der Code wirken soll, geöffnet wird. Es werden hier zwei Varianten aufgezeigt: In Spalte B wird eine Formel eingetragen, die das Ergebnis liefert. Falls ein Ergebnis ohne Formel gewünscht wird, wird dies noch in Spalte C eingetragen.

Die Routine wird nur ausgeführt, wenn die Eingabezelle in Spalte 1 (A) ist. Dann werden zunächst die Zielzellen daneben in B und C geleert.

Da intern mit Punkt statt Komma als Dezimaltrenner gerechnet wird, wird ein eventuell vorhandenes Komma zuerst ersetzt. Anschließend wird mit Evaluate versucht, zu berechnen. Wird die Berechnung erkannt, wird ein Ergebnis geliefert, sonst der Fehler #NAME?. Letzteres kommt zum Beispiel vor, wenn ein Text in A eingetragen wurde.

Tritt kein Fehler auf, wird in B die entsprechende Formel eingetragen, in C direkt das Ergebnis.

Private Sub Worksheet_Change(ByVal Target As Range) Dim varTemp, varErg If Target.Column > 1 Then Exit Sub Range("B" & Target.Row & ":C" & Target.Row).ClearContents varTemp = Replace(Target, ",", ".") varErg = Application.Evaluate(varTemp) If Not IsError(varErg) Then Cells(Target.Row, 2).Formula = "" & "=" & varTemp & "" Cells(Target.Row, 3) = varErg End If End Sub


UDF - benutzerdefinierte Funktion

Es ist (in diesem Fall ab Excel 365) auch möglich, das Ergebnis der Berechnung ohne Gleichheitszeichen per Formel zu erhalten. Notwendig ist dazu eine solche benutzerdefinierte Funktion in einem Standardmodul:

Function Evaluate_String(ByVal strString As String, Optional intWas As Integer = 0) Dim varTemp, varErg Evaluate_String = "" varTemp = Replace(strString, ",", ".") varErg = Application.Evaluate(varTemp) If Not IsError(varErg) Then Evaluate_String = IIf(intWas <> 0, "=" & varTemp & "", varErg) End Function

In die Zelle, in der das Ergebnis der Formel ohne Gleichheitszeichen erscheinen soll, muss dann nur:

=Evaluate_String(C10)

Wenn die Formel nicht das Ergebnis, sondern die Formel (also mit Gleichheitszeichen) anzeigen soll, kann als zweiter Parameter etwas anderes als 0 verwendet werden, zum Beispiel:

=Evaluate_String(C10;1)

Sie hat dann ein vergleichbares Verhalten wie die integrierte Funktion FORMELTEXT().



Ribbonkalender

Kategorie: Add-In ▸ Datum und Zeit

(Tipp 591) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann man die Arbeit mit Datumsangaben und Uhrzeiten vereinfachen?

Ribbonkalender

Oft wird viel Zeit in Excel mit Datumsangaben und Uhrzeiten verbracht. Datumsangaben müssen über Zeiträume hinweg eingetragen werden, Kalender werden erstellt, Zeitspannen werden berechnet usw.

Oft sind es die vielen Kleinigkeiten wie über das Ziel hinausschießende scrollende Fenster oder Wechsel von einem Tastaturbereich zum anderen zum anderen, die aufhalten. Hier soll das (auch um Ihre Wünsche erweiterbare) Add-In helfen.

Das Add-In stellt im Menüband (Ribbon) einen Kalender zur Verfügung, über den verschiedene Funktionen aufrufbar sind. Gleichzeitig kann eingestellt werden, dass ein kleiner Kalender immer an der aktiven Zelle ist, so dass Datumsangaben einfach in die Zelle geklickt werden können. Für Uhrzeit-Eingaben ist es möglich, ein alternatives Zeichen als den Doppelpunkt zu verwenden, zum Beispiel das Plus auf dem Numblock. Usw. usf. - die Funktionen sind im Video dargestellt:
 

Das Add-In sollte ab Excel 2007 funktionieren. Nach dem Installieren muss Excel neu aufgerufen werden. Es kann frei genutzt, darf aber nicht geändert werden.

Download: ribbonkalender.xlam

Summewenn mit mehreren KriterienFormellösungArrayfunktion/Matrixfunktion

Kategorien: Tabelle ▸ Zellen und Berechnungen

(Tipp 181) Nachricht zum Beitrag an Autor Nach oben

Wie kann man mit der Funktion SUMMEWENN auch mehrere Kriterien bestimmen?

Dazu bietet sich eine Array-Funktion an. Die erreicht man, indem man erst die normale Formel eingibt und die Eingabe mit Strg + Umschalttaste + Enter beendet. Dann werden automatisch um die Formel geschweifte Klammern ({}) gesetzt, woran man die Array-Funktion erkennt.

Beispiel: Folgende Formel summiert, wenn in Spalte A ein a und in derselben Zeile in Spalte B ein h ist, die entsprechenden Werte der Spalte C. Die geschweiften Klammern signalisieren, dass es ein Array ist - diese nicht mit kopieren/eingeben, sondern im Bearbeitungsmodus mit Strg + Umschalttaste + Enter erzeugen.

{=SUMME(WENN(A1:A11="a";WENN(B1:B11="h";C1:C11;"")))}

Ab Excel 365

Ab Excel 365 sind die geschweiften Klammern nicht mehr notwendig, die Formel kann ganz normal eingetragen und die Eingabe einfach mit Enter abgeschlossen werden:

=SUMME(WENN(A1:A11="a";WENN(B1:B11="h";C1:C11;"")))

Hier geht allerdings auch die Funktion SUMMEWENNS(), in die mehrere Kriterien als Parameter eingetragen werden können. Im Beispiel wäre das:

=SUMMEWENNS(C1:C11;A1:A11;"a";B1:B11;"h")

Textzahl zu ZahlMakro/Sub/ProzedurTipp

Kategorien: Format ▸ Text und Tabelle ▸ Zellen

(Tipp 65) Nachricht zum Beitrag an Autor Nach oben

Manchmal stehen Zahlen nach dem Import aus anderen Programmen links in der Zelle und werden auch nicht als Zahl interpretiert. Wie kann man das ändern?

Es ist das Problem, das häufig auftritt, wenn die Daten aus Textdateien oder aus dem Internet kommen. Dann sind scheinbar Zahlen enthalten, die aber in den Zellen links stehen - ein Indiz dafür, dass die Daten nicht als Zahlen, sondern als Texte (String) erkannt wurden. Man kann natürlich die Zellen rechtsbündig formatieren, was aber das grundlegende Problem nicht löst. Da es sich immer noch um Texte handelt, können bei Berechnungen Fehler auftreten.

Nun könnte man nacheinander jede Zelle aktivieren, mit F2 den Eingabemodus aufrufen und gleich wieder Enter drücken. Dadurch würde Excel i. d. R. die Zahlen als solche erkennen. Bei sehr vielen Zellen dürfte das aber eine zeitraubende Angelegenheit sein.

Hier ist ein Beispiel, das für die Lösung den Excel-internen Befehl Inhalte einfügen - Multiplikation verwendet. Nehmen wir an, in A1 steht die Ziffer 1. Dann kann man die Textzahlen in B1:B21 umwandeln mit:

Range("A1").Copy Range("B1:B21").PasteSpecial Operation:=xlMultiply, SkipBlanks:=True

Sind Hochkommas im Spiel, sehen Sie sich bitte Hochkomma (') entfernen an.

UDF - Benutzerdefinierte Funktionen (auch Matrixfunktionen)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionTipp

Kategorien: Basics ▸ UDF und UDF ▸ Basics

(Tipp 164) Nachricht zum Beitrag an Autor Nach oben

Grundsätzliches

Benutzerdefinierte Funktionen, also UDF, sind Funktionen, die man sich selbst im VBA-Editor erstellt. Dazu benennt man sie nicht mit Sub, sondern mit Function. Der Name der Funktion liefert dabei den Rückgabewert. Heißt also eine Funktion "MeineFunktion(...)", schreibt man in die Zelle "=MeineFunktion(...)". Ebenfalls kann man Funktionen einsetzen, um durch Subs bestimmte Berechnungen durchführen zu lassen.

Beispiel: Eine Funktion, die immer das Datum zurückgibt, das in 14 Tagen liegt, Die Funktion braucht keine Parameter, sie wäre schlicht und einfach:

Function Datum_14Tage() Datum_14Tage = Date + 14 End Function

Darauf können wir einfach per Sub zugreifen:

Sub Datumstest() MsgBox Datum_14Tage End Sub

Oder in die Zelle eingetragen:

=Datum_14Tage()


Ergebnisse/Rückgaben

Prinzipiell können benutzerdefinierte Funktionen alle möglichen Datentypen als Rückgabewerte haben: Strings, Zahlen, Datumsangaben usw. Selbst Arrays können Ergebnisse sein, die dann von anderen Subs verarbeitet werden.


Dynamische Arrayformeln/Matrixfunktionen

Seit Excel 365 ist es sogar möglich, Arrays als Ergebnisse solcher (dann Matrix-) Funktionen in Zellen eintragen zu lassen. Nehmen wir folgende Funktion als Beispiel, die für einen Monat eine kleine Liste mit Wochentagen und Kalenderwochen erstellt:

Function Monatstabelle(ByVal intMonatszahl As Integer, ByVal intJahr As Integer) Dim datDatum As Date, arrS(), lngArr As LongPtr datDatum = CDate("1." & intMonatszahl & "." & intJahr) lngArr = 0 Do lngArr = lngArr + 1 ReDim Preserve arrS(1 To 3, 1 To lngArr) arrS(1, lngArr) = datDatum arrS(2, lngArr) = Format(datDatum, "DDD") arrS(3, lngArr) = Application.WorksheetFunction.IsoWeekNum(datDatum) datDatum = datDatum + 1 Loop While Month(datDatum) = intMonatszahl Monatstabelle = Application.WorksheetFunction.Transpose(arrS) End Function

Sie erwartet als Parameter die Zahl des Monats und das Jahr. Wenn diese Angaben in D1 und in E1 stehen, können wir in der Tabelle diese Arrayformel eintragen:

=Monatstabelle(D1;E1)

Tipp nebenbei: Mit Transpose oder in deutscher Syntax MTRANS kann eingestellt werden, in welcher Richtung ein Array ausgegeben wird. Diese (eigentlich Tabellenblatt-) Funktion transponiert den Array, so dass dieser wahlweise über Spalten oder über Zeilen ausgegeben wird.


Formel übergelaufen

Beim ersten Verwenden einer Matrixfunktion (also nicht nur einer eigenen) erscheint die Meldung:

Formel übergelaufen - Ihre Formel hat mehrere Werte zurückgegeben, weshalb wir sie in die benachbarten leeren Zellen haben überlaufen lassen.

Diese Meldung besagt nichts anderes, als dass sich die Zelle mit der Formel nun verhält, wie man es in CSS mit float: left; beschreiben würde. Die Ergebnisse der Formel fließen rechts und unterhalb von der Eingabezelle.

Mit dem Schnittmengenoperator @ nach dem Gleichheitszeichen können Sie übrigens einstellen, dass nur der erste Wert des Arrays in der Zelle erscheint.


Um zu gewährleisten, dass eine in einem Tabellenblatt eingesetzte UDF immer rechnet, sollte man an den Anfang der Funktion schreiben:

Application.Volatile

Werte in Zeile 2 eingeben, alle Zeilen mit anderem Wert ausblenden (Worksheet_Change)Makro/Sub/Prozedur

Kategorien: Tabelle ▸ Matrix und Filter/Sortieren

(Tipp 389) Nachricht zum Beitrag an Autor Nach oben

Wenn ich in Zeile 2 Werte eingebe, sollen die Zeilen mit anderen Werten ausgeblendet werden.

Hier werden zwei Varianten vorgestellt, bei denen Werte in mehrere Zellen in einer Zeile eingegeben werden können und diese als Filterkriterium dienen. Die Zellen, in die die Filterkriterien eingegeben werden können, sind hier A2:G2, also die ersten sieben Zellen in Zeile 2. Die Tabelle mit den zu filternden Werten ist darunter von A5:Gx.

In beiden Fällen im VBA-Editor auf die entsprechende Tabelle doppelklicken und den Code dort einfügen.

Ein Kriterium

Im ersten Beispiel richtet sich der Filter nach nur einem Kriterium, also einer Zelle in A2:G2. Wenn also z. B. in B2 etwas eingegeben wird, soll die Tabelle nach dem Eintrag in B2 gefiltert werden - etwaige Eintragungen in anderen Zellen in Zeile 2 werden ignoriert bzw. gelöscht:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim lngLZ As LongPtr, intS As Integer, intSAkt As Integer, bolEvent As Boolean If Target.Row <> 2 Or Target.Column > 7 Then Exit Sub lngLZ = Cells(Rows.Count, 1).End(xlUp).Row If ActiveSheet.AutoFilterMode = True Then If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData ActiveSheet.Range("A4:G" & lngLZ).AutoFilter End If bolEvent = Application.EnableEvents Application.EnableEvents = False For intS = 1 To 7 If intS <> Target.Column Then Cells(2, intS).ClearContents Next Application.EnableEvents = bolEvent If Target <> "" Then ActiveSheet.Range("A4:G" & lngLZ).AutoFilter Field:=Target.Column, Criteria1:=Range(Target.Address) End Sub

Nach der Eingabe werden zunächst alle Zeilen eingeblendet und der Autofilter ausgeschaltet, falls er gesetzt ist. Anschließend wird ausgeschaltet, dass die Tabelle auf Ereignisse reagiert (da es sonst zu Endlosschleifen kommen könnte) und die Inhalte der anderen Zellen in Zeile 2 werden gelöscht. Danach wird der Autofilter auf der Basis der aktuellen Eingabe gesetzt.


Mehrere Kriterien

In der zweiten Variante sind in Zeile 2 mehrere Einträge möglich und der Autofilter wird auf der Basis dieser Einträge gesetzt. Das Vorgehen entspricht ansonsten der ersten Variante.

Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim lngLZ As LongPtr, intS As Integer If Target.Row <> 2 Or Target.Column > 7 Then Exit Sub lngLZ = Cells(Rows.Count, 1).End(xlUp).Row With ActiveSheet If .AutoFilterMode = True Then If .FilterMode = True Then .ShowAllData .Range("A4:G" & lngLZ).AutoFilter End If For intS = 1 To 7 If .Cells(2, intS) <> "" Then .Range("A4:G" & lngLZ).AutoFilter Field:=intS, Criteria1:=.Cells(2, intS).Value End If Next End With End Sub


Eingabe nur in A2 - ältere Variante mit weniger Funktionalität

Im VBA-Editor auf die Tabelle doppelt klicken, in der es funktionieren soll. Anschließend in das Modul einfügen:

Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address <> "$A$2" Then Exit Sub ActiveSheet.Range("A4:B" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter If Range("A2") <> "" Then ActiveSheet.Range("A4:B" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=Range("a2") End If End Sub

Wo gibt man die Makros ein?

Kategorie: VBE

(Tipp 93) Nachricht zum Beitrag an Autor Nach oben

Wo gibt man die Makros ein?

Das herausstechendste Merkmal ist, dass die Programmiersprache VBA bei den Office-Anwendungen fast einheitlich ist; das Vorgehen ist hier also bei Excel, Word, usw. gleich.

Die Eingabe der Makros erfolgt im sogenannten VBA-Editor, den man mit Alt + F11 aufruft. Nach dem Aufruf erscheint normalerweise erst ein leeres Fenster, in dem sich oben links der Projekt-Explorer befindet (wenn nicht, im Menü Ansicht einblenden).

Projekt-Explorer

In diesem Projekt-Explorer sind alle offenen Excel-Dateien aufgelistet. Das heißt, wenn man in einer bestimmten Datei arbeiten möchte, muss sie vorher dort aktiviert werden. Durch einen Doppelklick auf eine Datei oder einen Klick auf das Pluszeichen vor der Datei werden die einzelnen Elemente der Datei - wie im Windows-Explorer - sichtbar. Bei einer sonst leeren Datei dürften das nur die Einträge für DieseArbeitsmappe und für die einzelnen Tabellen sein. Das heißt, dass die einzelnen Module für die Makros erst erzeugt werden müssen.

Userform

Manchmal bietet es sich an, eigene Masken oder Bedienungselemente zu erstellen. Dies erfolgt in VBA mit Userformen, die man über das Menü Einfügen - Userform erstellt. Dort kann man seine eigenen Elemente zeichnen und diese programmieren.

Klassenmodule für Tabellen oder die Mappe

Soll ein Makro direkt einer Tabelle, der Mappe oder einer Userform zugeordnet werden, erreicht man das Erstellen des entsprechenden Klassenmoduls, indem man im Projekt-Explorer einen Doppelklick auf das gewünschte Element ausführt. Beachten Sie dabei aber, dass diese Klassenmodule normalerweise nur dann gebraucht werden, wenn bestimmte Ereignisse des Elements Öffnen, Speichern, Eingaben, Markieren, usw.) abgefangen, also darauf reagiert werden sollen. Im Normalfall ist es besser, allgemeine Module zu verwenden.

Allgemeines Modul

Normalerweise beginnt man mit Makros in allgemeinen Modulen, wo auch der Makrorecorder seine Makros aufzeichnet. Diese allgemeinen Module sind - wie der Name besagt - allgemein verfügbar und es kann von überall aus darauf zugegriffen werden. Ein solches Modul erzeugt man mit dem Menü Einfügen - Modul. Dadurch hat man ein leeres weißes Blatt, in dem man mit dem Makros beginnen kann. Im Projekt-Explorer ist nun auch der neue Eintrag Modul1 unter dem Sammeleintrag Module enthalten. Dies sollte für den Anfang der wichtigste Ausgangspunkt sein, die anderen Module werden dort benannt, wo sie gebraucht werden.

Zusammenfassung

Bei der Gesamtheit der Elemente einer Mappe oder eines Dokumentes spricht man vom VBA-Projekt.

Dieses kann zum Beispiel aus folgenden Elementen bestehen:

  • Excel- bzw. Word-Objekte (Arbeitsmappe, einzelne Tabellen, Document) und die zugehörigen Ereignisprozeduren,
  • Userformen und die zugehörigen Prozeduren,
  • Module,
  • Klassenmodule (siehe Ereignisprozeduren)

Wochendaten zur Zahl einer Kalenderwoche bestimmenMakro/Sub/ProzedurUDF - benutzerdefinierte Funktion

Kategorie: Datum/Zeit ▸ Datum

(Tipp 430) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus der Zahl einer Kalenderwoche die entsprechenden Daten der Woche errechnen?

Die folgende Funktion stellt ersten und letzten Tag der Kalenderwoche fest und gibt auf dieser Basis den Zeitraum aus:

Function Zeitraum(ByVal Jahr As Integer, ByVal KW As Variant) As String Dim datBeg As Date, lngT As LongPtr Application.Volatile Zeitraum = "" If IsNumeric(Jahr) And IsNumeric(KW) Then lngT = DateSerial(Jahr, 1, 4) lngT = lngT - Weekday(lngT, 2) + 7 * KW - 7 If (Year(lngT + 4) = Jahr) Then datBeg = lngT + 1 Zeitraum = Format(datBeg, "DD.MM.YYYY") & " - " & Format(datBeg + 6, "DD.MM.YYYY") End If End Function

In die Zelle muss dann nur noch:

=Zeitraum(Jahr;KW)

Soll es ohne Funktion in der Zelle funktionieren und der Zeitraum sofort eingetragen werden, kann folgender Code in das Klassenmodul der jeweiligen Tabelle (Doppelklick auf Tabelle im VBA-Editor):

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row >= 4 And Target.Column = 1 Then Application.EnableEvents = False Target = Zeitraum(Range("B1"), Target) Application.EnableEvents = True End If End Sub

Der Code wird ausgeführt, wenn die Eingabezelle in Spalte 1 und Zeile >= 4 liegt; verwendet wird das Jahr aus Zelle B1. Application.EnableEvents = False ist hier wichtig, weil die eingegebene KW durch den Zeitraum überschrieben wird und das Change-Ereignis sonst immer wieder aufgerufen würde.



Zeilenumbrüche aus Excel-Zellen entfernenMakro/Sub/Prozedur

Kategorien: Suchen/Ersetzen und Stringoperationen ▸ Ersetzen

(Tipp 67) Nachricht zum Beitrag an Autor Nach oben

Wie kann man die ALT-Eingabetaste (Zeilenumbruch) entfernen?

Dieser Code ersetzt das unsichtbare Zeichen mit einem Mal in allen Zellen der aktiven Tabelle:

Sub ZeilenumbruecheErsetzen() ActiveSheet.Cells.Replace Chr(10), " " End Sub

Zeit ohne Doppelpunkt eingeben

Kategorie: Add-In ▸ Datum und Zeit

(Tipp 586) Beispieldatei Nachricht zum Beitrag an Autor Nach oben

Wie kann ich viele Uhrzeiten eingeben, ohne immer den entfernten Doppelpunkt nutzen zu müssen?

Mit diesem Add-In können Zeiten mit dem „+“-Zeichen statt des Doppelpunktes eingegeben werden. So kann die Hand beim Num-Block bleiben.

Hinweis:
Mit dem Add-In »Ribbonkalender“ kann auch ein anderes Zeichen eingestellt werden; außerdem stehen noch weitere Datums- und Zeitfunktionen zur Verfügung.

Download: zeit_ohne_doppelpunkt.xlam

Zeitwerte über 10 000 StundenTipp

Kategorien: Basics ▸ Datum/Zeit und Datum/Zeit ▸ Zeit

(Tipp 230) Nachricht zum Beitrag an Autor Nach oben

Bei der Eingabe einer Zeit >= 10 000 wird diese als Text behandelt, man kann damit auch nicht weiterrechnen. Ist das Ergebnis einer Formel aber >= 10 000, ist das für Excel eine Zeit.

Um mit Zeitwerten über 10 000 Stunden rechnen zu können, müssen diese also nur auf mehrere Zellen verteilt und addiert werden. Mit dieser Summe kann problemlos weitergerechnet werden.

Zellbearbeitung aktivieren (SendKeys)Makro/Sub/ProzedurTipp

Kategorie: Tabelle ▸ Zellen

(Tipp 155) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich per Makro die Zellbearbeitung aktivieren, so daß der Cursor in der Zelle blinkt?

Soll das wirklich geschehen, geht es mit dem folgenden Code:

Application.SendKeys ("{F2}")

Allerdings wird bei SendKeys dummerweise der Numblock ausgeschaltet, so dass diese Tasten dann als Richtungstasten statt der Zahleneingaben funktionieren.

Generell ist es besser, Zellen Inhalte direkt zuzuweisen. Dann funktionieren etwaige Makros auch weiter, was im Bearbeitungsmodus unterbrochen wird. Bei automatisierten Eintragungen oder Änderungen ist es eigentlich nie notwendig, in den Bearbeitungsmodus zu wechseln.

Zellen mit Farbe zählenMakro/Sub/ProzedurUDF - benutzerdefinierte Funktion

Kategorien: Format ▸ Farben und Tabelle ▸ Zellen

(Tipp 429) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich per Formel Zellen mit Hintergrundfarben zählen?

In ein Standardmodul folgende benutzerdefinierte Funktion:

Function Farbenzaehlen(Bereich) Dim Zelle As Object Application.Volatile Farbenzaehlen = 0 For Each Zelle In Bereich If Zelle.Interior.ColorIndex <> xlNone Then Farbenzaehlen = Farbenzaehlen + 1 Next End Function

Allerdings muss beachtet werden, dass eine Neuberechnung der Formel nicht erfolgt, wenn eine Hintergrundfarbe geändert wird. Hierzu muss irgendwo eine Eingabe erfolgen oder F9 gedrückt werden.

Sollte eine sofortige Neuberechnung wichtig sein, kann im Klassenmodul der Tabelle oder im Klassenmodul DieseArbeitsmappe das SelectionChange-Ereignis abgefangen werden:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.Calculate End Sub

Oder für die Mappe:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.Calculate End Sub

Allerdings wird dann wirklich bei jeder Bewegung neu berechnet, was sich bei vielen Formeln mit einer gewissen Trägheit bemerkbar machen kann.