Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Anzahl im gefilterten BereichFormellösungArrayfunktion/Matrixfunktion

Kategorien: Filter/Sortieren und Tabelle ▸ Matrix

(Tipp 193) Nachricht zum Beitrag an Autor Nach oben

Ich möchte in einer Spalte die Anzahl eines bestimmten Begriffes. Dies funktioniert mit "Zählenwenn". Wenn ich aber Filter setze, ändert sich die Anzahl nicht.

Die 3 steht für Anzahl2, in C2 bis C6 befinden sich die zu zählenden Daten:

=TEILERGEBNIS(3;C2:C6)

Application-EreignisseMakro/Sub/ProzedurTipp

Kategorien: Basics ▸ Ereignisse und Ereignisse ▸ Basics

(Tipp 97) Nachricht zum Beitrag an Autor Nach oben

Application-Ereignisse gelten für die gesamte Anwendung, für alle Fenster, Arbeitsmappen und Tabellenblätter. Um mit Ereignissen des Application-Objekts zu arbeiten, muß man zuvor eine öffentliche Variable der Objektklasse in einem Klassenmodul definieren und danach ein Objekt der neuen Klasse und darin wieder ein Objekt der Klasse. Letzteres wird wiederum in einem einfachen Modul erstellt.

Was sich zunächst ein wenig kompliziert anhört, ist im Grunde recht einfach zu verwirklichen.

  1. Man wechselt in den Visual-Basic-Editor und geht auf Einfügen/Klassenmodul. Das Klassenmodul trägt den Namen Klasse1. Diesen wechselt man unter Eigenschaften/Namen in Anwendungsklasse. Dies bietet später eine bessere Übersicht, sollte man noch weitere Klassen definieren.
  2. Die öffentliche Variable wird definiert. Dazu gibt man folgenden Code ein: Public WithEvents Anwendung As Application. Danach stehen schon die Application-Ereignisse im rechten Listenfeld zur Verfügung. Diese erhält man, wenn man im linken Listenfeld auf "Anwendung" wechselt.
  3. Nun wird ein Objekt der neuen Klasse definiert. Dies geschieht in einem allgemeinen Modul (Einfügen/Modul): Dim Anwendungsobjekt As New Anwendungsklasse.
  4. Nun wird unter der Deklaration des Objekts "Anwendungsobjekt" eine Prozedur erstellt, die der Variablen Anwendung der Anwendungsklasse einen Verweis auf das Anwendungsobjekt zuweist:

Sub ObjektZuordnen() Set Anwendungsobjekt.Anwendung = Application End Sub

Ab sofort können alle Ereignisse des Application-Objektes empfangen werden, wenn diese Routine ausgeführt wurde. Damit das neue Objekt immer zur Verfügung steht, sollte der letzte Code in Workbook_Open() der Mappe.

Wir können nun zum Besipiel jeden Blattwechsel in jeder offenen Mappe abfangen (also nicht nur in der mit dem Code), indem wir in das Klassenmodul eintragen:

Private Sub Anwendung_SheetActivate(ByVal Sh As Object) MsgBox Sh.Name End Sub

Hinweis:
Um Application-Ereignisse zu deaktivieren, setzt man einfach den Verweis auf das "Anwendungsobjekt" auf Nothing, also Set Anwendungsobjekt.Anwendung = Nothing

Parameter:

Die Application-Ereignisse haben feste Parameter, die mit übergeben werden (im vorigen Code-Beispiel ist das schon an ByVal Sh As Object zu sehen):

Wb:Stellt die aktive Arbeitsmappe dar.
Sh:Steht für das aktive Tabellenblatt.
Target:Bezieht sich auf den aktiven Zellenbereich.
Cancel:Hat den Wert False. Wird er in der Ereignisprozedur (z. B. bei BeforeSave) auf True gesetzt, wird das Ereignis nicht ausgeführt, sprich es wird nicht gespeichert. U. a. lässt sich so auch gut das Schließen einer Mappe abfangen.

Ereignisse:

Am einfachsten wählt man die natürlich über das Dropdown im Klassenmodul, wie es in der obigen Abbildung dargestellt ist. Hier eine kleine Übersicht:

Anwendung_NewWorkbook(ByVal Wb As Excel.Workbook)
Eine neue Arbeitsmappe wurde eingefügt.
Anwendung_SheetActivate(ByVal Sh As Object)
Ein anderes Blatt wurde aktiviert (Blattwechsel).
Anwendung_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
Doppelklick wurde ausgeführt.
Anwendung_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
Klick mit der rechten Maustaste.
Anwendung_SheetCalculate(ByVal Sh As Object)
Neuberechnung eines Tabellenblattes.
Anwendung_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Zelleninhalt eines Tabellenblattes wurde verändert.
Anwendung_SheetDeactivate(ByVal Sh As Object)
Ein Tabellenblatt wurde verlassen (Blattwechsel).
Anwendung_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Zellenmarkierung eines Tabellenblattes wurde geändert.
Anwendung_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)
Ein neues Fenster wurde aktiviert (Fensterwechsel).
Anwendung_WindowDeactivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)
Ein Fenster wurde verlassen (Fensterwechsel).
Anwendung_WindowResize(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)
Die Größe eines Fensters wurde verändert.
Anwendung_WorkbookActivate(ByVal Wb As Excel.Workbook)
Eine neue Arbeitsmappe wurde aktiviert (Arbeitsmappenwechsel).
Anwendung_WorkbookAddinInstall(ByVal Wb As Excel.Workbook)
Eine Arbeitsmappe wurde als Add-In installiert.
Anwendung_WorkbookAddinUninstall(ByVal Wb As Excel.Workbook)
Eine Arbeitsmappe wurde als Add-In deinstalliert.
Anwendung_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel As Boolean)
Eine Arbeitsmappe soll geschlossen werden.
Anwendung_WorkbookBeforePrint(ByVal Wb As Excel.Workbook, Cancel As Boolean)
Eine Arbeitsmappe soll ausgedruckt werden.
Anwendung_WorkbookBeforeSave(ByVal Wb As Excel.Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
Eine Arbeitsmappe soll geschlossen werden.
Anwendung_WorkbookDeactivate(ByVal Wb As Excel.Workbook)
Eine Arbeitsmappe wurde verlassen (Arbeitsmappenwechsel).
Anwendung_WorkbookNewSheet(ByVal Wb As Excel.Workbook, ByVal Sh As Object)
In einer Arbeitsmappe wurde ein neues Tabellenblatt eingefügt.
Anwendung_WorkbookOpen(ByVal Wb As Excel.Workbook)
Eine neue Arbeitsmappe wurde geöffnet.

Arbeit mit Kombinationsfeld in der TabelleMakro/Sub/ProzedurTipp

Kategorie: Steuerelemente ▸ ActiveX

(Tipp 403) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich ein Kombinationsfeld füllen und den gewählten Wert an eine Zelle übergeben?

Es geht um das ActiveX-Kombinationsfeld auf einem Tabellenblatt:

  1. In den Entwurfsmodus schalten
  2. mit rechts auf das Kombinationsfeld klicken
  3. Eigenschaften auswählen
  4. bei ListFillRange den Bereich eintragen, z. B. F5:F10
  5. Doppelklick auf die Box
  6. Im Modul den folgenden Code einfügen, den danach anpassen, speichern und den Editor schließen.

Private Sub ComboBox1_Change() Range("A1") = ComboBox1.Text 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;"")

Arrayformeln (03): Arrayformeln in VBA, EVALUATEMakro/Sub/ProzedurUDF - benutzerdefinierte FunktionArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 91) Nachricht zum Beitrag an Autor Nach oben

Können Ergebnisse von Arrayformeln in VBA verwendet werden?

Integrierte Möglichkeiten von Excel (Funktionen, Methoden) können um ein Vielfaches schneller sein, als wenn man diese selbst per VBA erstellt bzw. nachgestaltet. So war es schon immer ein großer Unterschied, ob man die Find-Methode von Excel nutzte oder eine Schleife über viele Zellen laufen ließ, um einen bestimmten Wert zu finden. Insofern kann auch in Betracht gezogen werden, mit VBA Arrayformeln (bzw. integrierte Arrayfunktionen) zu nutzen und deren Ergebnisse zu verarbeiten.

Evaluate()

Vor den Erläuterungen zu den Arrayfunktionen in VBA eine Vorbemerkung zu EVALUATE(), einer Methode, die recht unbekannt, hier aber oft nützlich ist.

Von anderen Sprachen kennt man eval() is evil - in Excel/VBA macht die Methode genau das, was hinter dem Spruch steckt, ist aber bei richtiger Verwendung ungefährlich. Eine Beschreibung ist bei Microsoft zu finden; wir nutzen diese Methode hier, um Formeln/Funktionen interpretieren zu lassen und zu Ergebnissen zu führen.

Dieser Hinweis war wichtig, weil hier in der Folge eine Formel ohne Funktion (also auch ohne Application.WorksheetFunction) verwendet wird, um Arrayformeln in VBA möglichst einfach zu demonstrieren:

Rückgabe: Array

Basis zum Verwenden von Arrayformeln in VBA ist, dass sie Arrays zurückgeben. Schließlich ist dies auch beim Einsatz auf dem Tabellenblatt der Fall, nur dass wir dort die Ergebnisse in Zellen sehen. Entsprechend ist auch wichtig zu wissen, dass Bereiche in Arrayergebnissen zwei Dimensionen haben können, strukturiert in Zeilen und Spalten sind.

Die Bereiche in den Arrays aus Arrayformeln sind grundsätzlich in Zeilen gegliedert, sie sind die erste Dimension. Wenn wir nun also in C2:C7 Einträge haben und mit arr = Evaluate("=C2:C7") (statt Evaluate wäre auch Range("C2:C7") möglich) darauf zugreifen, wird als Ubound folgerichtig die 6 ausgegeben. Entsprechend kann mit einer Schleife For intI = 1 To UBound(arr) über die einzelnen Elemente des Arrays gegangen werden.

Allerdings muss jetzt beachtet werden, dass die einzelnen Werte in der ersten Spalte des Arrays stehen - das erste (und in diesem Beispiel einzige) Element in der zweiten Dimension. Ein Zugriff auf arr(intI) würde einen Fehler erzeugen, da hier die Angabe der Spalte fehlt. Erst mit dieser Angabe wird der gewünschte Eintrag geliefert: arr(intI, 1).

Der Aufbau des Codes wäre also:

Sub Test() Dim arr, intI As Integer, strTemp As String arr = Evaluate("=C2:C7") MsgBox "Einträge: " & UBound(arr) For intI = 1 To UBound(arr) strTemp = arr(intI, 1) ' Einträge in Zeilen! MsgBox "Eintrag " & intI & ": " & strTemp Next End Sub

Im Beispiel standen die Werte untereinander in den Zellen. Was nun aber, wenn die Werte nebeneinander stehen, zum Beispiel in D1:I1?

In diesem Fall besteht der Array nur aus einer Zeile, der Ubound wird somit 1 sein.

Um an die einzelnen Elemente zu gelangen, gibt es nun die erste Möglichkeit, per Schleife die Elemente dieser einen Zeile zu liefern, wobei allerdings bekannt sein muss, wie viele Elemente die Zeile hat:

For intI = 1 To 6 strTemp = arr(1, intI) ' Einträge in Spalten! MsgBox "Eintrag " & intI & ": " & strTemp Next

Eine Alternative dazu ist, den Array zu transponieren. Dies geht mit der Funktion MTRANS() in der englischen Schreibweise bereits beim Übergeben an die Variable:

arr = Application.WorksheetFunction.Transpose(Evaluate("=D1:I1"))

Damit sind die ehemaligen Spalten nun die Zeilen und der Array kann wie vorher mit Schleife bis zum Ubound (der jetzt im Beispiel wieder 6 ist) durchlaufen werden.

Der Code zum Testen:

Sub Test1() Dim arr, intI As Integer, strTemp As String arr = Evaluate("=D1:I1") MsgBox "Einträge: " & UBound(arr) ' 1, weil nur 1 Zeile For intI = 1 To 6 strTemp = arr(1, intI) ' Einträge in Spalten! MsgBox "Eintrag " & intI & ": " & strTemp Next End Sub Sub Test2() Dim arr, intI As Integer, strTemp As String arr = Application.WorksheetFunction.Transpose(Evaluate("=D1:I1")) MsgBox "Einträge: " & UBound(arr) For intI = 1 To 6 strTemp = arr(intI, 1) ' Einträge in Spalten! MsgBox "Eintrag " & intI & ": " & strTemp Next End Sub

Arrayformeln (04): EINDEUTIG/UNIQUE (Formel + VBA)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 116) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Funktion EINDEUTIG() (in VBA) nutzen?

Ab Excel 365 gibt es neben der Möglichkeit, Duplikate zu entfernen, auch eine Funktion zum Einsatz in einer Formel: EINDEUTIG(). Die Funktion sucht in einer Tabelle nach doppelten Datensätzen und gibt in der einfachen Variante jeden nur einmal aus. Weitere Informationen zu Parametern der Formel gibt es bei Microsoft: EINDEUTIG-Funktion.

Im Beispiel ist zu sehen, dass die Monate Februar und zweimal Mai im Ergebnis nur jeweils einmal erscheinen, weil diese Monate auch jeweils die gleichen Zahlen haben. Der Juni ist jedoch zweimal im Ergebnis enthalten, weil diese Datensätze unterschiedliche Zahlen haben und somit insgesamt unterschiedlich sind.

Verwendung in VBA

Auch mit VBA kann diese Funktion doppelte Datensätze ausfiltern, indem die englische Schreibweise zum Einsatz kommt:

Application.WorksheetFunction.Unique(Array)

Im Beispiel wird die Tabelle aus der Abbildung im Bereich A2:E15 verwendet. Hier sind die Datensätze bei Frau Linz identisch und zwei Datensätze bei Frau Herzig. Aus dieser Tabelle erstellen wir den Array:

arr = Range("A2:E15")

Zum Herausfiltern der doppelten Datensätze wird die Funktion eingesetzt:

arr = Application.WorksheetFunction.Unique(arr)

Weiterverarbeitung des Ergebnisarrays

Das Ergebnis ist nun in der Variablen arr der Array mit den eindeutigen Datensätzen. Dabei gibt es jedoch zwei verschiedene mögliche Fälle:

Es können (wie im Beispiel) mehrere Zeilen sein. Dann kann der Array von 1 bis zum Ubound (der hier die Anzahl der Zeilen im Ergebnis ist) mit arr(Zeile, Spalte) durchlaufen werden:

For intI = 1 To UBound(arr) MsgBox arr(intI, 1) & " " & arr(intI, 2) & ", " & arr(intI, 3) Next

Hier würde für jede Zeile eine MsgBox mit Anrede Name, Vorname erscheinen.

Es kann aber auch der Fall eintreten, dass im Ergebnis nur eine Zeile übrig bleibt, die nun als Array vorliegt. Hier enthält der Array jedoch nicht die einzelne Zeile als Arrayelement der ersten Dimension, sondern bereits die einzelnen Elemente in der ersten Ebene. In dem Fall würde ein Zugriff mit arr(Zeile, Spalte) zu einem Fehler führen, weil das Auslesen nur mit arr(Spalte) erfolgen darf.

Wenn wir im VBA-Code also beide Fälle berücksichtigen wollen, müssen wir prüfen, ob der Array aus mehreren Zeilen zu mehreren Spalten oder nur aus mehreren Spalten ohne Zeile besteht. Dazu bietet sich an, die Anzahl aller Elemente des Arrays festzustellen:

intAnzahlEl = Application.WorksheetFunction.CountA(arr)

Wenn diese Zahl gleich dem Ubound des Arrays ist, muss es sich um einen eindimensionalen handeln, weil das dann die einzelnen Spalten sind. Wenn nicht, handelt es sich um einen mehrzeiligen Array, weil es dann immer mehr Elemente als der Ubound sind (Zeilen * Spalten = intAnzahlEl).

Und so können wir in unserem Code gut die Weiche stellen - an der Stelle der MsgBoxen müsste die eigentliche Verarbeitung der Daten rein:

Sub Eindeutig_vba() Dim arr, intI As Integer, intAnzahlEl As Integer arr = Range("A2:E15") 'Zur Ausgabe von mehreren Zeilen 'arr = Range("A2:E2") 'Zur Testausgabe einer Zeile arr = Application.WorksheetFunction.Unique(arr) 'Anzahl aller(!) Elemente im Array: intAnzahlEl = Application.WorksheetFunction.CountA(arr) MsgBox "Ubound: " & UBound(arr) & vbNewLine & "Anzahl: " & intAnzahlEl If intAnzahlEl = UBound(arr) Then ' Es gibt nur eine Zeile MsgBox arr(1) & " " & arr(2) & ", " & arr(3) Else ' Mehrere Zeilen For intI = 1 To UBound(arr) MsgBox arr(intI, 1) & " " & arr(intI, 2) & ", " & arr(intI, 3) Next End If End Sub

Tipp - Sortieren:

Wenn das Ganze sortiert werden soll, kann das auch gleich am Anfang mit der integrierten Funktion erledigt werden:

arr = Application.WorksheetFunction.Unique(arr) arr = Application.WorksheetFunction.Sort(arr, 2)

In dem Beispiel wäre der frische Array nach den Namen sortiert.



Arrayformeln (05): SORTIEREN/SORTIERENNACH (Formel + VBA)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 117) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Funktionen SORTIEREN()/SOERIERENNCH() (in VBA) nutzen?

Sortieren

Die Funktion SORTIEREN(), die ebenfalls ab Excel 365 zu Verfügung steht, ist vom Prinzip her identisch mit EINDEUTIG(), nur dass sie nicht filtert, sondern sortiert. In der Funktion übergibt man den Bereich und als zweiten Parameter den Index der Spalte, ausgegeben wird der sortierte Bereich.

Beispiel für eine Formel mit dieser Funktion, die nach der zweiten Spalte sortiert:

=SORTIEREN(A2:E15;2)

Mehr Informationen dazu gibt es bei Microsoft: SORTIEREN-Funktion

Auch die Verwendung in VBA ist identisch; die Rückgaben der Arrays und deren Auswertung unterscheiden sich nicht.


Sortierennach

Verfeinerte Sortiermöglichkeiten bietet diese Funktion, weil sie nach verschiedenen Spalten sortieren kann und auf diese Spalten auch andere Funktionen angewendet werden können (siehe auch SORTIERENNACH-Funktion). So sortiert diese Formel den Bereich A2:E15:

=SORTIERENNACH(A2:E15;JAHR(D2:D15);1;E2:E15;1)

Sortiert wird dabei zunächst nach dem Jahr in D2:D15 und zwar aufsteigend: JAHR(D2:D15);1. Anschließend wird nach den Beträgen in E2:E15 sortiert, ebenfalls aufsteigend: E2:E15;1.

Diese Formel sortiert nach den Wochentagen in D2:D15 aufsteigend und anschließend ebenfalls nach den Beträgen:

=SORTIERENNACH(A2:E15;WOCHENTAG(D2:D15);1;E2:E15;1)


VBA

In VBA kann es beim Einsatz leicht zu Fehlermeldungen kommen, wenn Funktionen auf die Bereiche angwendet werden, nach denen sortiert werden soll. Soll z. B. einfach nur nach den Datumsangaben in D2:D15 sortiert werden, reicht folgende Anwendung:

Dim a, b a = Range("A2:E15") b = Range("D2:D15") arr = Application.WorksheetFunction.SortBy(a, b, 1)

Wird jedoch bei b eine Funktion verwendet, schimpft Excel schnell. Abhilfe schafft hier, die Formel als String zusammenzustellen und diesen mit EVALUATE() berechnen zu lassen:

Dim strFormel As String strFormel = "=sortby(A2:E15, year(D2:D15), 1, E2:E15,1)" arr = Evaluate(strFormel)

Dieses Beispiel entspricht dem ersten Formelbeispiel. Die Auswertung bzw. weitere Verarbeitung des Ergebnisarrays würde dann wie bei EINDEUTIG() erfolgen; an die Stellen der MsgBoxen müsste der Code, der das Weitere erledigen soll:

Sub Sortierennach_vba() Dim arr, intI As Integer, intAnzahlEl As Integer Dim strFormel As String strFormel = "=sortby(A2:E15, year(D2:D15), 1, E2:E15,1)" arr = Evaluate(strFormel) intAnzahlEl = Application.WorksheetFunction.CountA(arr) MsgBox "Ubound: " & UBound(arr) & vbNewLine & "Anzahl: " & intAnzahlEl If intAnzahlEl = UBound(arr) Then ' Es gibt nur eine Zeile MsgBox arr(2) & ", " & arr(3) & ": " & Format(arr(4), "DD.MM.YYYY") Else ' Mehrere Zeilen For intI = 1 To UBound(arr) MsgBox arr(intI, 2) & ", " & arr(intI, 3) & ": " & Format(arr(intI, 4), "DD.MM.YYYY") Next End If End Sub

Arrayformeln (06): FILTER (Formel + VBA)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 127) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich Daten per Funktion filtern (und mit VBA auf Ergebnisse zugreifen)?

Datenfilter, Autofilter und seit Excel 365 nun auch eine Funktion FILTER() - verschiedene Möglichkeiten gibt es ja. Was konkret verwendet wird, wird sich immer nach den konkreten Umständen richten müssen. Mit der Funktion haben wir nun eine schnelle und sehr flexible Variante, die vor allem auch bei der Nutzung durch VBA dank reduzierter Ergebnismengen zu besseren Laufzeiten führen kann. Ausführliche Informationen gibt es natürlich bei Microsoft: FILTER-Funktion.

Der Einsatz in einer Formel ist sehr einfach: Das folgende Beispiel nimmt den Bereich A2:F15, sieht dort in D2:D15 nach, wo "Berlin" enthalten ist und gibt diese Zeilen als Bereich aus:

=FILTER(A2:F15;D2:D15="Berlin";"Nichts gefunden")

Mehrere Suchkriterien können durch das +-Zeichen für Oder bzw. das *-Zeichen für Und verknüpft werden. So sucht die folgende Formel nach Einträgen, die "Berlin" oder "Hamburg" enthalten:

=FILTER(A2:F15;(D2:D15="Berlin")+(D2:D15="Hamburg");"Nix gefunden!")

Diese Formel sucht nach Zeilen, die als Ort "Berlin" enthalten und ein Datum aus dem Jahr 2015 haben und deren Beträge größer als 200 sind:

=FILTER(A2:F15;(D2:D15="Berlin")*(JAHR(E2:E15)=2015)*(F2:F15>200);"Nix gefunden!")

Verknüpfungen von Und und Oder sind natürlich auch möglich. Dabei muss allerdings auf die richtige Klammersetzung geachtet werden. Die nächste Formel sucht Einträge mit ("Berlin oder "Hamburg") und aus dem Jahr 2015 und mit Beträgen größer als 200:

=FILTER(A2:F15;((D2:D15="Berlin")+(D2:D15="Hamburg"))*(JAHR(E2:E15)=2015)*(F2:F15>200);"Nix gefunden!")


VBA

Der Einsatz in VBA ist nahezu identisch, wenn mit EVALUATE() gearbeitet wird. Die Formel wird dazu als String zusammengesetzt und mit Evaluate wird diese Berechnung durchgeführt:

strFormel = "=filter(A2:E15, D2:D15=""Berlin"", """")" arr = Evaluate(strFormel)

Die weitere Verarbeitung des Ergebnisarrays erfolgt wie bei der Funktion EINDEUTIG() unter Weiterverarbeitung des Ergebnisarrays beschrieben. Hier ein Beispiel im Ganzen, das die letzte Formel nutzt:

Sub Filter_vba() Dim arr, intI As Integer, intAnzahlEl As Integer Dim strFormel As String strFormel = "=FILTER(A2:F15,((D2:D15=""Berlin"")+(D2:D15=""Hamburg""))*(year(E2:E15)=2015)*(F2:F15>200),""Nix gefunden!"")" arr = Evaluate(strFormel) intAnzahlEl = Application.WorksheetFunction.CountA(arr) MsgBox "Ubound: " & UBound(arr) & vbNewLine & "Anzahl: " & intAnzahlEl If intAnzahlEl = UBound(arr) Then ' Es gibt nur eine Zeile MsgBox arr(2) & ", " & arr(3) & ": " & Format(arr(4), "DD.MM.YYYY") Else ' Mehrere Zeilen For intI = 1 To UBound(arr) MsgBox arr(intI, 2) & ", " & arr(intI, 3) & ": " & Format(arr(intI, 4), "DD.MM.YYYY") Next End If End Sub

Arrayformeln (10): Raute # - Verwenden des ErgebnisarraysMakro/Sub/ProzedurFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 144) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich (auch per VBA) auf das Ergebnis einer Arrayformel auf dem Tabellenblatt zugreifen?

Das Ergebnis einer Arrayformel (seit Excel 365) auf dem Blatt wird rechts und unterhalb der Zelle mit der Formel eingetragen. Es fließt also um diese eine Zelle, während die Formel nur in einer Zelle steht.

Soll nun bei weiteren Berechnungen auf das komplette Ergebnis der Arrayformel zugegriffen werden, reicht es nicht, sich auf die Zelle mit der Formel zu beziehen. Die Folge wäre, dass nur der Wert (also das Ergebnis) aus dieser einen Zelle - der mit der Formel - verwendet würde, nicht aber das gesamte Ergebnis.

Das Beziehen auf den Bereich mit dem Ergebnis, zum Beispiel F3:G14, würde zunächst einen Erfolg liefern. Allerdings wird das Problem ersichtlich, wenn sich im Ergebnis, das man verwenden will, die Ergebnismenge ändert, indem zum Beispiel eine Zeile mehr in der Basistabelle plötzlich das Filterkriterium erfüllt. In dem Fall bliebe der Bezug auf den Bereich F3:G14 bestehen - das zusätzliche Ergebnis aus der Berechnung bliebe unberücksichtigt.

Aus diesem Grund arbeitet Excel mit Referenzen auf komplette Ergebnisse. Eine solche Adresse sieht man, wenn man irgendwo ein Gleichheitszeichen eingibt und anschließend über den Bereich mit dem Ergebnis der Berechnung zieht - Excel trägt dann in die Formel einen Bezug wie E3# ein. Dabei handelt es sich um die Zelle mit der Formel und das Rautezeichen signalisiert, dass das Ergebnis dieser Berechnung für weitere Berechnungen gewünscht ist.

Im Beispiel auf der Grafik ist die Basistabelle in A3:C14. In E3 befindet sich die Formel zum Sortieren dieser Tabelle:

=SORTIEREN(A3:C14)

Das Ergebnis fließt nun als Ergebnis um die Zelle E3.

Nun soll diese sortierte Ergebnismenge In I3 nach dem Monat Juni gefiltert werden. Es soll sich dabei also auf das vorherige Ergebnis bezogen werden. In die FILTER-Funktion wird deshalb nicht E3 als Matrix eingetragen, auch nicht E3:G14, wo sich das komplette Ergebnis befindet. Verwendet wird als Matrix E3#, damit die Filter-Funktion weiß, dass es sich um ein dynamisches Ergebnis handelt. Der Rest bleibt wie gehabt:

=FILTER(E3#;F3:F14="Juni")

Wenn nun in der Basistabelle ein weiterer Monat auf Juni geändert wird, passt sich das Ergebnis auch beim Filtern dynamisch an und wird erweitert.


Verwenden eines Arrayergebnisses in VBA

In VBA kann dieser Bezug ebenfalls verwendet werden, indem er im Range-Objekt als Adresse angegeben wird:

Dim arr, intI As Integer arr = Range("I3#") For intI = 1 To UBound(arr) MsgBox intI & ": " & arr(intI, 1) & vbTab & arr(intI, 2) & vbTab & arr(intI, 3) Next

Auch mit EVALUATE ist es möglich:

Dim arr, intI As Integer arr = Evaluate("I3#") For intI = 1 To UBound(arr) MsgBox intI & ": " & arr(intI, 1) & vbTab & arr(intI, 2) & vbTab & arr(intI, 3) Next

Auf Monatsblatt beim Datum eingetragene Namen zählenFormellösungArrayfunktion/Matrixfunktion

Kategorien: Datum/Zeit ▸ Datum und Tabelle ▸ Matrix

(Tipp 333) Nachricht zum Beitrag an Autor Nach oben

Auf Blättern, die mit Monatsnamen benannt sind, befinden sich in Zeile 1 Ab Spalte A nebeneinander die Datumsangaben des Monats (bis AE). Unter diesen Angaben sind ab Zeile 2 die anwesenden Mitarbeiter eingetragen. Wie kann ich diese zählen?

Ab Excel 365

Zunächst muss das Blatt mit dem Monatsnamen des aktuellen Datums ermittelt werden. Das geht mit Indirekt:

=INDIREKT(TEXT(HEUTE();"MMMM")&"!A1:AE1")

Diese Formel liefert die komplette erste Zeile mit den Datumsangaben des aktuellen Monats. Sie kann nun in der Funktion FILTER() als Suchbereich verwendet werden. Damit können alle Mitarbeiter des Tages ermittelt werden (hier bis Zeile 30):

=FILTER(INDIREKT(TEXT(HEUTE();"MMMM")&"!A2:AE30");INDIREKT(TEXT(HEUTE();"MMMM")&"!A1:AE1")=HEUTE())

Die Formel gibt aus der Datumsspalte alle Einträge zurück, die bis Zeile 30 liegen.

Da aber mehr Zeilen (wie hier bis 30) verwendet werden müssen (die maximale Anwesenheit muss ja berücksichtigt werden), liefert diese Formel für die leeren Zellen, in denen also kein Mitarbeiter eingetragen ist, jeweils eine 0. Das kann also weder mit ANZAHL() noch mit ANZAHL2() gezählt werden, da sonst die 0 immer einfließen würde.

Wir verwenden im Beispiel SUMME(Wenn( und lassen die Einträge zählen, die <> 0 sind:

=SUMME(WENN(FILTER(INDIREKT(TEXT(HEUTE();"MMMM")&"!A2:AE30");INDIREKT(TEXT(HEUTE();"MMMM")&"!A1:AE1")=HEUTE())<>0;1;0))

Ältere Versionen

Hier ist die Formel etwas länger:

=ANZAHL2(INDIREKT(TEXT(HEUTE();"MMMM")&"!"&LINKS(ADRESSE(1;VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"));4);1+(VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"))>26))&"2:"&LINKS(ADRESSE(1;VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"));4);1+(VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"))>26))&"1000"))

Auswahlmenü an ZelleTipp

Kategorie: Tabelle ▸ Datenüberprüfung

(Tipp 246) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich erreichen, daß an einer Zelle ein Auswahlmenü erscheint?

Voraussetzung: Was im Auswahlmenü erscheinen soll, befindet sich in einem Bereich untereinander.

Den Bereich markieren, in dem die Zellen dieses Menü haben sollen.

Anschließend im Ribbon Daten den Button Datenüberprüfung betätigen und im Dialogfeld bei Zulassen den Eintrag Liste wählen. Nun muss nur noch im Feld Quelle der Datenbereich angegeben werden, in dem sich die Angaben befinden.

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



Bereich als HTML-Mail versendenMakro/Sub/Prozedur

Kategorien: Netz ▸ Mail und Übergreifend

(Tipp 565) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich einen Bereich als HTML-Mail versenden?

Wie kann ich einen ausgewählten Bereich einer Exceltabelle als HTML-Mail via Outlook versenden?

Const strTempOrdner As String = "C:\Eigene Dateien\" Sub Aufruf() Mail_erstellen "info@example.org" End Sub Sub Mail_erstellen(strAdresse As String) Dim strQuelle As String Dim OutApp Dim OutMail Dim olMailItem Dim strSubject strQuelle = "$B$1:$R$58" strSubject = Range("B1") Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(olMailItem) With OutMail .To = strAdresse .CC = "" .BCC = "" .Subject = strSubject .HTMLBody = Uebersetzung(strQuelle) '.Send .Display End With Set OutMail = Nothing Set OutApp = Nothing End Sub Public Function Uebersetzung(strQuelle As String) Dim objFSO As Object Dim objInhalt As Object Dim strTempDatei As String strTempDatei = strTempOrdner & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" With ActiveWorkbook.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=strTempDatei, _ Sheet:=ActiveSheet.Name, _ Source:=strQuelle, _ HtmlType:=xlHtmlStatic) .Publish (True) End With Set objFSO = CreateObject("Scripting.FileSystemObject") Set objInhalt = objFSO.GetFile(strTempDatei).OpenAsTextStream(1, -2) Uebersetzung = objInhalt.ReadAll objInhalt.Close Set objInhalt = Nothing Set objFSO = Nothing Kill strTempDatei End Function

Bei neueren Outlookversionen kann es zu einer Fehlermeldung kommen:

Ein Programm versucht, Nachrichten mit Item.Send zu senden. Ein Programm versucht, mit dem Befehl Item.Send der Anwendung Microsoft Outlook Visual Basic automatisch E-Mail-Nachrichten zu senden. Wenn Sie möchten, dass das Programm diese E-Mail-Nachricht sendet, klicken Sie auf Ja. Klicken Sie auf Nein, um das Programm zu beenden. Wenn Sie sich nicht sicher sind, welches Programm die E-Mail-Nachricht sendet oder warum, sollten Sie auf Nein klicken, um ein mögliches Verbreiten von Viren zu vermeiden.

Bei Anzeige dieser Meldung steht die Schaltfläche Ja 5 Sekunden lang nicht zur Verfügung.

Vielleicht ist dieser Tipp besser geeignet: Mails mit VBA und PHP versenden (mehrere Empfänger, mehrere Anhänge).

ChartereignisseMakro/Sub/ProzedurTipp

Kategorien: Basics ▸ Ereignisse und Ereignisse ▸ Basics

(Tipp 101) Nachricht zum Beitrag an Autor Nach oben

Um Chartereignisse nutzen zu können, muss man vorher ein Klassenmodul erstellen. Dieses ist im Gegensatz zu den Application-Ereignissen jedoch recht einfach zu bewerkstelligen.

Anlegen des Klassenmoduls:

Gehen Sie im Visual-Basic-Editor auf Einfügen ▸ Klassenmodul und geben folgendes ein:

Public WithEvents Diagramm As Chart

Um alles übersichtlicher zu gestalten, sollte man der Klasse den Namen Diagrammklasse geben (unter Eigenschaften/Namen). Nun stehen in der linken Drop-Down-Liste die neue Klasse und in der rechten die dazugehörigen Eigenschaften zur Verfügung. Damit die Ereignisprozeduren ausgeführt werden, muss man nun das Diagramm der Diagrammklasse zuordnen. Dies geschieht in einem beliebigen Modul durch folgenden Code:

Dim MeinDiagramm As New Diagrammklasse Sub DiagrammZuordnen() Set MeinDiagramm.Diagramm = Worksheets(1).ChartObjects(1).Chart End Sub

Parameter:

Wie man erkennen kann, übergeben auch die Chartereignisse Parameter. Da man nun allerdings eine Klasse definiert hat, sind diese derart vielfältig, dass sie hier nicht aufgeführt werden. Sie sind aber in der Online-Hilfe ausreichend erklärt.

Chartereignisse:

Diagramm_Activate
Tritt ein, wenn das Diagramm aktiviert wird. War das Diagramm vor einem Blattwechsel aktiv und wird wieder zu diesem Blatt gewechselt, tritt dieses Ereignis auch ein.
Diagramm_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Tritt bei einem Doppelklick auf.
Diagramm_BeforeRightClick(Cancel As Boolean)
Tritt beim drücken der rechten Maustaste ein.
Diagramm_Calculate
Tritt auf, wenn das Diagramm neu gezeichnet wird, z. B. neue Daten.
Diagramm_Deactivate
Tritt bei einem Blatt- oder Diagrammwechsel auf.
Diagramm_DragOver
Tritt ein, wenn ein Zellbereich über ein Diagramm gezogen wird, aber noch nicht losgelassen ist.
Diagramm_DragPlot
Tritt ein, wenn ein Zellbereich über ein Diagramm gezogen wurde und losgelassen wurde.
Diagramm_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)
Tritt ein, wenn die Maustaste gedrückt wird.
Diagramm_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)
Tritt ein, wenn die Maus bewegt wird.
Diagramm_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)
Tritt ein, wenn die Maustaste losgelassen wurde.
Diagramm_Resize
Tritt ein, wenn die Diagrammgröße verändert wurde.
Diagramm_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
Tritt ein, wenn das Diagramm ausgewählt wurde.
Diagramm_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long)
Tritt bei einer Veränderung der ausgewählten Datenreihe ein.

Daten exportieren

Kategorie: Add-In ▸ Dateien und Ordner

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

Mit dem Add-In können Daten aus einer Mappe in separate Textdateien exportiert werden. Diese stehen anschließend für einen Import zur Verfügung (sind also auch gleichzeitig Sicherunsgkopie), wobei sie dann in die gleichen Zellen geschrieben werden. So können verschiedene Szenarios erstellt werden oder die Daten stehen auch anderen Arbeitsplätzen zur Verfügung.

Hinweis:
Mit dem Add-In »Excel-Kommunikator« können Sie ebenfalls komplette Tabellenbereiche exportieren und zwar so, dass sie sofort anderen Nutzern zur Verfügung stehen. Sie brauchen sich dort nur in einer Gruppe zu befinden, damit Sie sofort Daten austauschen können.

Download: datenexport.xlam

Daten verketten, z. B. Text, Datumswert, Zeilenumbruch (VBA + Formel)Makro/Sub/ProzedurFormellösung

Kategorie: Stringoperationen ▸ Verketten

(Tipp 178) Nachricht zum Beitrag an Autor Nach oben

Wie kann man Zeichenfolgen aus Zellen, Datumsangaben oder anderen Zeichenfolgen miteinander kombinieren?

Zum Verketten von Zeichenfolgen bieten sich zum Beispiel diese Möglichkeiten an:


Formeln

="abc " & A1 & "def"

=A20&" "&TEXT(C2; "MM/JJ")

=VERKETTEN(A20;" ";TEXT(C2; "MM/JJ"))

Vorausgesetzt wird, das in C2 das komplette Datum steht. Ansonsten kann die Textformatierung in den Funktionen auch entfallen.


Soll ein Datum formatiert verwendet werden, ist das so möglich:

=TEXT(C2;"TT.MM.JJ ")&A1


Ein Zeilenumbruch kann so in die Zelle eingefügt werden:

=A1&ZEICHEN(10)&B1&" "&C1&ZEICHEN(10)&D1&ZEICHEN(10)&ZEICHEN(10)&E1&" "&F1

Allerdings ist dies ein zusätzliches Zeichen, was bei etwaigen Textvergleichen berücksichtigt werden muss. In Rohdaten sollte das deshalb vermieden und nur bei der reinen Ausgabe verwendet werden.


Ein HTML-Link kann aus den Angaben in Zellen so erstellt werden:

="<a href="&ZEICHEN(34)&B1&ZEICHEN(34)&">"&A1&"</a>"


VBA

Zum Ergänzen mehrerer Zellen in einem Bereich ist diese Schleife möglich:

Sub Ergaenzen() Dim rngZelle As Range For Each rngZelle In Range("A1:A10").Cells rngZelle.Value = "abc" & rngZelle.Value & "def" Next End Sub


Excel 365: Funktion TEXTVERKETTEN()

Mit dieser Funktion können ganz einfach Texte mit Trennzeichen verkettet werden, wobei die Trennzeichen komplette Strings sein können.

Gibt es zum Beispiel in A1:C5 eine Tabelle, aus der eine HTML-Tabelle erstellt werden soll, kann man einfach in jede Zeile diese Formel eingeben:

="<tr><td>"&TEXTVERKETTEN("</td><td>";;A1:C1)&"</td></tr>"

Zum Schluss gibt man irgendwo diese Formel ein:

="<table>"&TEXTVERKETTEN("";;E1:E5)&"</table>"

Fertig ist die komplette HTML-Tabelle.

Daten von mehreren Blättern zusammenfassenMakro/Sub/Prozedur

Kategorie: Mappe ▸ Tabellen

(Tipp 115) Nachricht zum Beitrag an Autor Nach oben

Ich habe mehrere Blätter mit Daten. Alle Blätter haben unterschiedlich viele Datensätze (Datenzeilen). Alle Daten sollen nacheinander auf einem Blatt zusammengefaßt werden.

Der Code löscht zunächst das Blatt Zusammenfassung, falls es existiert. Alternativ können natürlich auch Zellen oder Zellinhalte dieses Blattes gelöscht werden.

Anschließend läuft im Beispiel eine Schleife über die ersten drei Tabellenblätter. Von denen werden die Zellen in den Spalten 1 bis 5 bis zur letzten (in Spalte 1) ausgefüllten Zeile genommen und untereinander auf das Blatt Zusammenfassung kopiert.

Sub speichern() Dim lngZeile As Long, lngLetzteZ As Long, bytI As Byte Dim strBereich As String On Error Resume Next Worksheets("Zusammenfassung").Delete 'Auswertungsblatt löschen On Error GoTo 0: Err.Clear Worksheets.Add after:=Sheets(Sheets.Count) 'Auswertungsblatt einfügen ActiveSheet.Name = "Zusammenfassung" 'Variable, die dafür sorgt, daß die zusammengefaßten Daten untereinander stehen lngZeile = 1 'hier ab Zeile 1 For bytI = 1 To 3 'Von Blatt 1 bis Blatt 3 zusammenfassen lngLetzteZ = Worksheets(bytI).Cells(Worksheets(bytI).Rows.Count, 1).End(xlUp).Row strBereich = Range(Cells(1, 1), Cells(lngLetzteZ, 5)).Address 'Datenbereich (hier bis Spalte 5) auswählen Worksheets(bytI).Range(strBereich).Copy Sheets("Zusammenfassung").Cells(lngZeile, 1) 'Variable Zeile erhöhen lngZeile = lngZeile + lngLetzteZ Next End Sub

Datentypen - Deklaration (Beispiele: Excel)Makro/Sub/ProzedurTipp

Kategorie: Basics ▸ Variablen

(Tipp 211) Nachricht zum Beitrag an Autor Nach oben

Variablennamen müssen mit einem Zeichen des Alphabets beginnen, innerhalb des Gültigkeitsbereichs eindeutig sein, und dürfen nicht länger als 255 Zeichen lang sein.

Jede Variable beansprucht Speicherplatz, was zur Verlängerung der Laufzeit eines Makros (einer Prozedur) führt. Damit sich dies in Grenzen hält, kann man einer Variablen zuweisen, wieviel Speicherplatz sie in Anspruch nimmt, indem man der Variablen einen Datentyp zuweist.

Sie können u. a. als einer der folgenden Datentypen deklariert werden:

  • Boolean
  • Byte
  • Integer
  • LongPtr
  • String
  • Range

Wird kein Datentyp angegeben, so ist der Datentyp Variant standardmäßig zugewiesen.

Variablen werden gewöhnlich mit der DIM-Anweisung deklariert.


Long für 32- und 64-Bit

Statt Long sehen Sie hier LongPtr. Der Grund ist, dass es bei Verwendung von Code mit Long-Variablen in der 64-Bit-Version des Microsoft Office (standardmäßig wird die 32-Bit-Version installiert) zu Problemen kommen kann: vba-tutorial.de: Datentypen.

LongPtr ist also, um den Kern der Aussagen im verlinkten Text zusammenzufassen, eine Art Weiche, die bei 32-Bit-Versionen auf Long und bei 64-Bit-Versionen auf LongLong „umschaltet“.

Kennzeichnend für das Problem ist zum Beispiel diese Fehlermeldung:

Der Code in diesem Projekt muss für die Verwendung auf 64-Bit-Systemen aktualisiert werden. Überarbeiten und aktualisieren Sie Declare-Anweisungen, und markieren Sie sie mit dem PtrSafe-Attribut.

Deshalb die Empfehlung: Immer davon ausgehen, dass der Code in beiden Versionen laufen soll und deshalb LongPtr verwenden sowie API-Deklarationen am Anfang des Moduls immer mit einer solchen „Weiche“ vorzunehmen:

#If VBA7 Then Private Declare PtrSafe Function … (ByRef … As LongPtr, ByVal … As LongPtr) As LongPtr #Else Private Declare Function … (ByRef … As Long, ByVal … As Long) As Long #End If

Sie sehen hier einmal das PtrSafe und dass jeder Long-Typ in der aktuellen Version als LongPtr deklariert wurde.

Weitere Beispele:


Boolean

Datentypen Boolean werden als 16-Bit-Zahlen (2 Bytes) gespeichert, die nur die Werte True oder False annehmen können.

Bsp:

Sub PruefeZeileOK() Dim bolPositionOK As Boolean If Selection.Row < 10 Then bolPositionOK = False Else bolPositionOK = True If bolPositionOK = False Then MsgBox ("Aktion an dieser Position nicht zulässig!") End Sub

Die folgende Funktion bekommt aus Prozeduren die Zeilenposition übergeben und prüft, ob die Aktion zulässig ist. Rückgabewerte: TRUE oder FALSE

Function PositionOK(lngZeilenposition As Long) As Boolean PositionOK = True If lngZeilenposition < 10 Then PositionOK = False End Function

Die Verwendung in einer Sub könnte dann so aussehen:

Sub Aufruf() Dim lngZeile As LongPtr lngZeile = ActiveCell.Row If PositionOK(lngZeile) = False Then MsgBox "Geht hier nicht, Zeile " & lngZeile & " zu niedrig.", vbOKOnly + vbExclamation, "Fehler" Exit Sub End If End Sub

Byte

Byte werden als einzelne 8-Bit-Zahlen (1 Byte) ohne Vorzeichen gespeichert und haben einen Wert im Bereich von 0 bis 255.

Integer

Integer werden als 16-Bit-Zahlen (2 Bytes) in einem Bereich von -32.768 bis 32.767 gespeichert.

String

Datentyp String kann Buchstaben, Zahlen, Leerzeichen und Satzzeichen enthalten.

Sub Meldung() Dim strText As String Dim strTitel As String strText = "Hallo 12345" strTitel = "*******Titel ******" MsgBox strText, , strTitel End Sub

Range

Datentyp Range gibt eine Zelle oder einen Zellbereich aus.

Sub ZeilenMarkieren() Dim rngBereich As Range Set rngBereich = Sheets("Tabelle1").Range("A1:C5") If rngBereich.Interior.ColorIndex = 3 Then rngBereich.Interior.ColorIndex = 5 Else rngBereich.Interior.ColorIndex = 3 End Sub

Datum als Zufallszahl FormellösungArrayfunktion/Matrixfunktion

Kategorie: Datum/Zeit ▸ Datum

(Tipp 189) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich eine Zufallszahl zwischen dem 01.01.60 und dem 01.01.80 erzeugen?

Dazu kann man die Funktion ZUFALLSZAHL nutzen. Allerdings liefert sie Zahlen von 0 bis 1, so dass man sich damit behelfen muss:

=GANZZAHL(ZUFALLSZAHL()*(49-1)+1)

Dabei ist 1 die Untergrenze und 49 die Obergrenze.

Für das Datum gibt es nun verschiedene Möglichkeiten. Es kann die Zahl jedes Datums verwendet werden, Bezüge zu Zellen mit den Datumsangaben sind möglich, aber auch die Datumsangaben in Anführungszeichen sind anwendbar:

  • =GANZZAHL(ZUFALLSZAHL()*(29221-21916)+21916)
  • =GANZZAHL(ZUFALLSZAHL()*(G9-G8)+G8)
  • =GANZZAHL(ZUFALLSZAHL()*("01.01.1980"-"01.01.1960")+"01.01.1960")

Ab Excel 365

Hier geht es wesentlich einfacher:

  • =ZUFALLSBEREICH("1.1.60";"1.1.80")
  • =ZUFALLSBEREICH(I1;I2)
  • =ZUFALLSMATRIX(1;1;"1.1.60";"1.1.80";1)
  • =ZUFALLSMATRIX(1;1;I1;I2;1)


Datum nach Tag, Monat oder Jahr sortierenFormellösungArrayfunktion/Matrixfunktion

Kategorien: Datum/Zeit ▸ Datum und Filter/Sortieren

(Tipp 332) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich Datumsangaben in einem Bereich nicht nach Datum, sondern wahlweise auch nach Monat oder Tag sortieren?

Angenommen die Datumsangaben stehen in A2:A8:

Datum aufteilen

Die Datumsangaben können mit Formeln auf mehrere Spalten aufgeteilt werden:

  • Jahr: =WENN(ISTZAHL(A2);JAHR(A2);"")
  • Monat: =WENN(ISTZAHL(A2);MONAT(A2);"")
  • Tag: =WENN(ISTZAHL(A2);TAG(A2);"")

Anschließend kann ganz normal nach einer der Spalten sortiert werden.


Dynamische Arrayfunktion SORTIERENNACH() (ab Excel 365)

Mit dieser Funktion kann das Sortieren in einem Schritt erledigt werden, sie gibt alle Daten auf einmal sortiert aus.

Nach Monat aufsteigend (die 1 steht für aufsteigend):

=SORTIERENNACH(A2:A8;MONAT(A2:A8);1)

Nach Monat und Tag aufsteigend:

=SORTIERENNACH(A2:A8;MONAT(A2:A8);1;TAG(A2:A8);1)

Falls ein größerer Bereich in die Funktion eingetragen werden soll, falls zum Beispiel abzusehen ist, dass es mehr Datumsangaben werden, werden auch die noch leeren Zellen ausgegeben. Das sieht nicht schön aus. Dafür kann die Adresse der letzten Zelle mit INDIREKT() „zusammengebastelt“ werden. Hier als Beispiel über die Funktion ANZAHL():

=SORTIERENNACH(INDIREKT("A2:A"&ANZAHL(A2:A15)+1);MONAT(INDIREKT("A2:A"&ANZAHL(A2:A15)+1));1;TAG(INDIREKT("A2:A"&ANZAHL(A2:A15)+1));1)

Deklaration erzwingenMakro/Sub/ProzedurTipp

Kategorie: Basics ▸ Variablen

(Tipp 212) Nachricht zum Beitrag an Autor Nach oben

Visual Basic kann bei einem neuen Variablennamen nicht erkennen, ob es sich um eine neue oder um eine falsch geschriebene bestehende Variable handelt und erzeugt daher eine neue Variable. Probleme mit falsch benannten Variablen lassen sich jedoch vermeiden. Man kann einstellen, dass bei jeder Variablen, die nicht vorher explizit deklariert wurde, eine Fehlermeldung angezeigt wird.

Schreibt man zum Beispiel: Meldung = ... ist die Variable eine andere, als wenn man schreibt Meltung = ... Wenn man nun die Variable wieder einliest, ist der Wert der Variable Meltung leer, wenn der Wert vorher in die Variable Meldung gespeichert wurde.

Um solche Tippfehler zu vermeiden, fügt man am Anfang des Moduls folgende Anweisung an:

Option Explicit

Jetzt müssen alle Variablen explizit mit DIM deklariert werden.

Mit einer Moduloption kann man Visual Basic veranlassen, jedem erstellten Modul die Option Explizit hinzuzufügen.

  1. Im Visual Basic Editor Menü Extras > ▸ Optionen
  2. Register Editor
  3. Aktivieren des Kontrollkästchen Variablendeklaration erforderlich
  4. OK

Druckbereich festlegen und druckenMakro/Sub/Prozedur

Kategorie: Drucken/Seite

(Tipp 557) Nachricht zum Beitrag an Autor Nach oben

Wie kann man per VBA den Druckbereich festlegen und das Blatt drucken?

With Sheets("Tabelle1") 'Druckbereich festlegen: .PageSetup.PrintArea = "$A$1:$B$10" 'Drucken: .PrintOut 'Druckbereich aufheben: .PageSetup.PrintArea = "" End With

Drucken: Leere Zeilen ausblendenMakro/Sub/Prozedur

Kategorie: Drucken/Seite

(Tipp 162) Nachricht zum Beitrag an Autor Nach oben

Wie kann man vor dem Druck leere Zeilen ausblenden und Druckbereich festlegen?

Der Druckbereich wird von der Zelle A10 bis zur Spalte E festgelegt. Die letzte Zeile des Druckbereichs ist hierbei die letzte Zeile, in der in Spalte A ein Eintrag vorhanden ist.

Sub druck() Dim intS As Integer, lngE As Long, lngZ As Long 'Spalte A: intS = 1 'Letzte Zeile mit Eintrag suchen: lngE = Cells(Rows.Count, intS).End(xlUp).Row 'Zeilen ausblenden: For lngZ = 10 To lngE If Cells(lngZ, 5) = "" Then Rows(lngZ).EntireRow.Hidden = True Next 'Druckbereich festlegen: ActiveSheet.PageSetup.PrintArea = "$A$10:$E$" & lngE 'Drucken: ActiveSheet.PrintOut 'Druckbereich aufheben: ActiveSheet.PageSetup.PrintArea = "" 'Zeilen einblenden: Rows.Hidden = False End Sub

Oder:

Sub ausblenden() Dim intI As Long, lngE As Long, lngL As Long, intS As Integer 'e = Erste Zeile mit Formel 'l = Zeile, in der Summe steht 's = Spalte, in der die Zellen auf "" überprüft werden sollen lngE = 1 lngL = 20 intS = 3 'Leere Zeilen ausblenden For intI = lngE To lngL If Cells(intI, intS).Text = "" Then Rows(intI).EntireRow.Hidden = True Next 'Drucken ActiveWindow.SelectedSheets.PrintOut Copies:=1 'Zeilen einblenden For intI = lngE To lngL Rows(intI).EntireRow.Hidden = False Next End Sub

Erste leere Zelle (VBA + Formel)Makro/Sub/ProzedurFormellösung

Kategorie: Tabelle ▸ Zellen

(Tipp 13) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich einen Zellwert in die erste leere Zelle eines Bereiches eintragen lassen?

Die Beispiele zeigen, wie die letzte ausgefüllte Zelle zu ermitteln ist. In jedem Fall muss dann nur Zeile oder Spalte um 1 erhöht werden, um auf die erste leere Zelle zu kommen.

VBA

Grundsätzlich:

Mit Cells(Rows.Count, 1).End(xlUp).Row können wir abfragen, welche die letzte ausgefüllte Zelle in Spalte A (also 1) ist.

Brauchen wir die letzte ausgefüllte Zelle in einer Zeile, also die Spaltenangabe dieser Zelle, können wir Cells(1, Columns.Count).End(xlToLeft).Column verwenden (hier für Zeile 1).


Diese beiden Codestücke funktionieren auch zuverlässig, wenn nicht gerade die erste Zelle in der Spalte bzw. in der Zeile leer ist. In dem Fall würde auch die 1 zurückgegeben - was hieße, dass diese erste Zelle Inhalt hätte. Aus diesem Grund muss, wenn dies eine Rolle spielen kann, vorher geprüft werden, ob die erste Zelle der Spalte/Zeile auch leer ist. Damit wird dies berücksichtigt:

Dim strAddr As String 'Spalte A, entspricht 1: If IsEmpty(Cells(1, 1)) Then strAddr = Cells(1, 1).Address Else strAddr = Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Address End If Range(strAddr).Value = "Erste leere Zelle"

Stehen in der ersten Zeile grundsätzlich Spaltenüberschriften, spielt das bei der ersten ausgefüllten Zelle in einer Spalte natürlich keine Rolle.


Das Eintragen erfolgt dann einfach in die nächste Zelle. Entweder:

Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Select

oder:

a = Cells(Rows.Count, 2).End(xlUp).Row + 1

Entsprechend ist es auch bei der ersten leeren Zelle in einer Zeile:

a = Cells(2, Columns.Count).End(xlToLeft).Column + 1

Wenn etwas in die erste freie Zelle in einer Zeile kopiert werden soll, kann man z. B. so vorgehen:

Sub NaechstfreieSpalte() Selection.Copy Destination:=Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1) End Sub


Formel

Mit der Funktion ADRESSE() in Verbindung mit ANZAHL2() kann die letzte ausgefüllte Zelle ermittelt werden, wenn sich keine leeren Zellen im Bereich befinden:

=ADRESSE(ANZAHL2(B1:B34)+2;2;4)

Das +2 bedeutet, dass der zu prüfende Bereich erst in B3 beginnt.

Oder:

=INDIREKT("B"&ANZAHL2(B2:B10000)+1)

Das +1 heißt, dass der erste Wert in B in Zeile 2 steht. Steht der erste Wert in Zeile 1, kann es weggelassen werden. Steht der erste Wert aber in Zeile 3, wäre es +2 usw.

Eine andere Variante, die aber nur bis Spalte Z funktioniert, ist:

=LINKS(ADRESSE(ZEILE($B$6);SPALTE($B$6));3)&ANZAHL2($B$3:$B$65536)+ZEILE($B$3)-1

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

Farbnummern anzeigen (Color + ColorIndex)Makro/Sub/Prozedur

Kategorie: Format ▸ Farben

(Tipp 158) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Excel-Farbnummern auflisten lassen?

Dieses Makro fügt die Nummern in Spalte E ein und "färbt" in der Spalte F.

Sub Farben() Dim intZ As Integer For intZ = 1 To 56 Cells(intZ, 5) = intZ Cells(intZ, 6).Interior.ColorIndex = intZ Next End Sub

Excel kann jedoch mit wesentlich mehr Farben umgehen, nämlich auch den RGB-Farben. Die kann man natürlich nicht alle auflisten, weil Excel in einer Mappe nur begrenzt Zellformate haben kann. Aber man kann Bereiche darstellen, zum Beispiel so:

Sub Farben1() Dim lngZ As LongPtr, intS As Integer, lngF As Long Dim lngStart As LongPtr Dim datBeg As Date Workbooks.Add ActiveSheet.Columns.ColumnWidth = 5 ActiveWindow.Zoom = 10 intS = 2 lngZ = 0 lngStart = 9895936 On Error GoTo FEHLER For lngF = lngStart To lngStart + 65279 lngZ = lngZ + 1 Cells(lngZ, intS) = lngF Cells(lngZ, intS).Interior.Color = lngF '65430 If lngZ Mod 256 = 0 Then intS = intS + 1 lngZ = 0 End If Next MsgBox Format(Now - datBeg, "mm:ss") Exit Sub FEHLER: MsgBox "Abbruch bei " & lngF & vbNewLine & "Fehler: " & Err.Number & vbNewLine & Err.Description End Sub

Mit der Zahl in lngStart kann einfach experimentiert werden.

Möglich ist auch, mit den RGB-Werten direkt zu arbeiten. Alle auf einmal verkraftet Exel nicht, also muss hier entweder mit Step oder durch kleinere Abbruchwerte reduziert werden:

Sub Farben2() Dim lngZ As LongPtr, intS As Integer, strRGB As String Dim datBeg As Date Dim r As Integer, g As Integer, b As Integer Workbooks.Add ActiveSheet.Columns.ColumnWidth = 5 ActiveWindow.Zoom = 10 intS = 2 lngZ = 0 On Error GoTo FEHLER For r = 0 To 255 Step 10 For g = 0 To 255 Step 7 For b = 0 To 255 Step 5 lngZ = lngZ + 1 strRGB = r & "|" & g & "|" & b 'Cells(lngZ, intS) = strRGB Cells(lngZ, intS).Interior.Color = RGB(r, g, b) If lngZ Mod 256 = 0 Then intS = intS + 1 lngZ = 0 End If Next Next Next MsgBox Format(Now - datBeg, "mm:ss") Exit Sub FEHLER: MsgBox "Abbruch bei " & strRGB & vbNewLine & "Fehler: " & Err.Number & vbNewLine & Err.Description End Sub

Die zweite Variante ist wahrscheinlich die optisch schönste, die ergibt solche Verläufe:



Geburtstagskinder des heutigen Tages in Userform anzeigen (FILTER())Makro/Sub/ProzedurArrayfunktion/Matrixfunktion

Kategorien: Datum/Zeit ▸ Datum und Tabelle ▸ Matrix

(Tipp 376) Nachricht zum Beitrag an Autor Nach oben

Auf verschiedenen Blättern einer Mappe befinden sich in Spalte E Namen, in Spalte F Geburtstage und in Spalte J Telefonnummern. Wie kann ich die Geburtstagskinder des heutigen Tages in einer UserForm-Listbox anzeigen lassen?

Dafür gibt es verschiedene Möglichkeiten - wobei es mit der Find-Methode Probleme gibt, da die nach dem Datum sucht, nicht aber nach einem Teil davon. Gezeigt werden zwei Möglichkeiten - die erste funktioniert ab Excel 365, die zweite generell:


Einsatz der Tabellenfunktion FILTER() ab Excel 365

Das grundlegende Vorgehen ist hier wie bei der älteren Variante (siehe unten), nur beim Sammeln der Daten wird die Tabellenblattfunktion FILTER() verwendet. Die Blätter werden also auch einzeln abgearbeitet, pro Blatt arbeitet aber diese Funktion. Wir gehen hier davon aus, dass der Datenbereich ab Zeile 3 und ab Spalte C beginnt.

Die Funktion sorgt dafür, dass nicht jede Zeile auf dem Blatt durchlaufen werden muss, sondern dass bereits eine Ergebnismenge vorhanden ist - es müssen also weniger Daten ausgewertet werden. Sie liefert einen Array, in dem die Zeilen enthalten sind, in denen sich die gefundenen Geburtstage befinden. Die einzelnen Daten sind Elemente jeder Zeile, der Geburtstag ist das vierte Element (ab Spalte C). Die Datumsangaben liegen in diesem Array als Long-Zahlen vor, deshalb sind sie hier noch formatiert.

Für den praktischen Einsatz kann es notwendig sein, die Blätter noch etwas einzugrenzen, weil sonst Fehler auftreten könnten. Hier wird mit IsArray() geprüft, ob ein Array vorliegt, fürs Grobe reicht das erst mal. Die Routine:

Sub Geburtstage1() Dim lngLZ As LongPtr Dim intN As Integer Dim strEintrag As String Dim wksBlatt As Worksheet Dim arrS(), lngArrS As LongPtr Dim varT lngArrS = -1 For Each wksBlatt In ActiveWorkbook.Sheets lngLZ = wksBlatt.Cells(wksBlatt.Rows.Count, 4).End(xlUp).Row varT = Application.Evaluate("=FILTER(" & wksBlatt.Name & "!C3:J" & lngLZ & ",(DAY(" & wksBlatt.Name & "!F3:F" & lngLZ & ")=DAY(TODAY()))*(MONTH(" & wksBlatt.Name & "!F3:F" & lngLZ & ")=MONTH(TODAY())),0)") If IsArray(varT) Then For intN = 1 To UBound(varT) lngArrS = lngArrS + 1 ReDim Preserve arrS(lngArrS) strEintrag = Format(varT(intN, 4), "DD.MM.YYYY") & " " strEintrag = strEintrag & Format(Year(Date) - Year(varT(intN, 4)), "00") & " " strEintrag = strEintrag & varT(intN, 3) & ", " & varT(intN, 2) strEintrag = strEintrag & ", Telefon: " & varT(intN, 8) strEintrag = strEintrag & " (Blatt: " & wksBlatt.Name & ")" arrS(lngArrS) = strEintrag Next End If Next UserForm1.ListBox1.List = arrS UserForm1.Show End Sub


Alte Variante, generell funktionshähig

Hier wird das Verwenden einer Schleife aufgezeigt.

Dazu wird natürlich eine Userform benötigt, hier ist es UserForm1. Auf ihr sind die ListBox1 und ein Commandbutton zum Schließen der Userform.

Die folgende Routine, die hier in einem Standardmodul sein muss, durchläuft alle Blätter und prüft in Spalte 6 (F) auf Datumsangaben. Wenn es sich um ein Datum handelt, wird auf Übereinstimmung mit dem heutigen Tag und dem heutigen Monat geprüft. Gibt es die, wird in diesem Beispiel einfach ein String aus den Angaben gebastelt und in einen Array aufgenommen.

Am Ende wird der Array mit UserForm1.ListBox1.List an die Listbox übergeben und die Userform aufgerufen.

Sub Geburtstage() Dim lngEZ As LongPtr, lngLZ As LongPtr, lngZ As LongPtr, lngS As LongPtr Dim strEintrag As String Dim wksBlatt As Worksheet Dim arrS(), lngArrS As LongPtr lngS = 6 lngArrS = -1 For Each wksBlatt In ActiveWorkbook.Sheets lngEZ = wksBlatt.UsedRange.Row lngLZ = lngEZ + wksBlatt.UsedRange.Rows.Count If lngLZ > lngEZ Then With wksBlatt For lngZ = lngEZ To lngLZ If IsDate(.Cells(lngZ, lngS)) Then If Day(.Cells(lngZ, lngS)) = Day(Date) And Month(.Cells(lngZ, lngS)) = Month(Date) Then lngArrS = lngArrS + 1 ReDim Preserve arrS(lngArrS) strEintrag = .Cells(lngZ, 6).Text & " " strEintrag = strEintrag & Format(Year(Date) - Year(.Cells(lngZ, 6)), "00") & " " strEintrag = strEintrag & .Cells(lngZ, 5) & ", " & .Cells(lngZ, 4) strEintrag = strEintrag & ", Telefon: " & .Cells(lngZ, 10) strEintrag = strEintrag & " (Blatt: " & .Name & ")" arrS(lngArrS) = strEintrag End If End If Next End With End If Next UserForm1.ListBox1.List = arrS UserForm1.Show End Sub

Es gibt natürlich auch Alternativen. So kann z. B. der Array mehrspaltig verwendet und das Ganze zu einer Funktion umgeschrieben werden, die den Array zurückgibt und beliebig eingesetzt werden kann. Oder die Listbox wird mit ColumnCount mehrspaltig erstellt und die einzelnen Daten werden auf mehrere Spalten verteilt. Oder die Daten werden sofort in die Listbox geschrieben. Oder, oder, oder …

Soll die Geburtstagsprüfung beim Dateiaufruf erfolgen, wird im Klassenmodul DieseArbeitsmappe (doppelt anklicken) dieser Code eingefügt:

Private Sub Workbook_Open() Geburtstage End Sub

Damit der Commandbutton wirkt: Doppelklick auf den Button und diesen Code eintragen:

Private Sub CommandButton1_Click() Unload Me End Sub

Hochkomma (') entfernenMakro/Sub/ProzedurTipp

Kategorien: Tabelle ▸ Zellen und Format ▸ Text

(Tipp 234) Nachricht zum Beitrag an Autor Nach oben

Wie kann das führende Hochkomma entfernt werden?

Zum Verständnis: Das führende Hochkomma gehört zum Format der Zelle, nicht zum Inhalt.

Am einfachsten ist sicher, das Format einer anderen Zelle zu übertragen.

Dazu wird eine leere Zelle benötigt, die genau so formatiert wird, wie es bei den Zellen mit den Hochkommas der Fall ist. Damit sind also Farben, Rahmen, Schriftformat und ggf. Zahlenformat gemeint. Aber Vorsicht: Nicht das Format einer Zelle mit Hochkomma auf diese Zelle übertragen, sondern diese Zelle manuell formatieren!

Das Formatieren kann natürlich entfallen, wenn es bei den Zellen mit den Hochkommas nur um die Inhalte geht, wenn es sich also um reine Daten handelt, die nicht zur Ansicht oder zum Druck vorgesehen sind.

Nun die so formatierte Zelle aktivieren (anklicken), anschließend im Ribbon Start auf den Pinsel zum Format übertragen klicken und sofort über die Zellen ziehen, in denen die zu entfernenden Hochkommas sind.

Alternativ geht das auch per VBA. M2 ist die manuell formatierte Zelle, in A2:A20 sind die zu entfernenden Hochkommas:

Range("M2").Copy Range("A2:A20").PasteSpecial Paste:=xlPasteFormats

Sollten dann noch Formeln angezeigt werden oder Zahlen als Text, kann dieser Code mit dem Code in Textzahl zu Zahl kombiniert werden.


Ähnlich ist eine Alternative, bei der Excel zum Rechnen gezwungen wird. Dazu wird eine Zelle mit einer 1 kopiert und der zu ändernde Bereich per Inhalte einfügen ▸ Multiplizieren damit multipliziert:

Range("IV10000") = "1" Range("IV10000").Copy 'Bereich anpassen: Range("A2:A16").PasteSpecial Paste:=xlAll, Operation:=xlMultiply Range("IV10000").ClearContents


Außerdem können die Hochkommas so entfernt werden:

  1. Eine Hilfsspalte anlegen, die später wieder gelöscht werden kann.
  2. In die erste Zeile dieser Hilfsspalte einen Bezug auf die Zelle mit dem Hochkomma schreiben, z. B. =A1.
  3. Diese Formel nach unten kopieren, soweit, wie sich Einträge mit Hochkommas in den Zellen befinden. Jetzt sind die Einträge doppelt - einmal mit Hochkomma und einmal als Ergebnis des Bezugs.
  4. Die Zellen mit den Bezügen markieren.
  5. Kopieren und gleich Bearbeiten - Inhalte einfügen - Werte wählen.
  6. Da die Zellen noch markiert sind, noch einmal kopieren.
  7. Die erste Zelle mit einem Hochkomma aktivieren und einfügen. Jetzt müßten die Hochkommas verschwunden sein und die Hilfsspalte kann nun wieder gelöscht werden.

Integrierte Dialogfelder aufrufenMakro/Sub/Prozedur

Kategorie: Interaktion ▸ Dialoge

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

Die integrierten Dialogfelder von Excel können auch mit VBA aufgerufen werden. Dies geschieht einfach mit:

Application.Dialogs(Konstante).Show

Bei den Dialogfeldern können verschiedene Argumente mitgegeben werden, die natürlich bei jedem Element anders sind. Hierzu am besten einfach in die Hilfe sehen.

Manchmal muss man sich entscheiden, ob ein integriertes Dialogfeld oder ein herkömmliches Dialogfeld für die Aufgabe besser geeignet ist. Mit z. B. Application.GetOpenFilename kann schön der Pfad abgefragt werden, was mit xlDialogOpen schon nicht mehr so einfach ist.

lfd. Nr.KonstanteName
1xlDialogActivateAktivieren
2xlDialogActiveCellFontSchrift
3xlDialogAddinManagerAdd-In-Manager
4xlDialogAlignmentAusrichtung
5xlDialogApplyStyleFormatvorlage
6xlDialogArrangeAllFenster anordnen
7xlDialogAutoCorrectAutokorrektur
8xlDialogBorderRahmen
9xlDialogCalculationBeschriftungsoptionen
10xlDialogCellProtectionZellschutz
11xlDialogClearInhalte löschen
12xlDialogColorPaletteFarboptionen
13xlDialogColumnWidthSpaltenbreite
14xlDialogConditionalFormattingBedingte Formatierung
15xlDialogConsolidateKonsolidierung
16xlDialogCopyPictureBild kopieren
17xlDialogCreateNamesNamen erstellen
18xlDialogCustomizeToolbarAnpassen
19xlDialogCustomViewsAnsichten
20xlDialogDataSeriesReihe
21xlDialogDefineNameNamen definieren
22xlDialogDefineStyleFormatvorlage
23xlDialogDeleteFormatZahlenformat
24xlDialogDeleteNameNamen definieren
25xlDialogDemoteGruppierung
26xlDialogDisplayBildschirmanzeigeoptionen
27xlDialogEditDeleteZellen löschen
28xlDialogFileDeleteDatei löschen
29xlDialogFileSharingArbeitsmappe freigeben
30xlDialogFilterAdvancedSpezialfilter
31xlDialogFindFileDatei suchen/öffnen
32xlDialogFormatAutoAutoformat
33xlDialogFormatNumberZahlenformat
34xlDialogFormulaFindSuchen
35xlDialogFormulaGotoGehe zu
36xlDialogFormulaReplaceErsetzen
37xlDialogGoalSeekZielwertsuche
38xlDialogImportTextFileTextdatei importieren
39xlDialogInsertZellen einfügen
40xlDialogInsertHyperlinkHyperlink einfügen
41xlDialogInsertNameLabelBeschriftungsbereiche
42xlDialogInsertObjectObjekt einfügen
43xlDialogInsertPictureBild einfügen
44xlDialogNewDatei - Neu
45xlDialogOpenDatei öffnen
46xlDialogOptionsCalculationOptionen: Berechnung
47xlDialogOptionsEditOptionen: Bearbeitung
48xlDialogOptionsGeneralOptionen: Allgemein
49xlDialogOptionsListsAddOptionen: Liste
50xlDialogOptionsTransitionOptionen: Umsteigen
51xlDialogOptionsViewOtionen: Ansicht
52xlDialogPageSetupSeite einrichten
53xlDialogPasteSpecialInhalte einfügen
54xlDialogPatternsFormat: Muster
55xlDialogPrintDrucken
56xlDialogPrinterSetupDruckereinrichtung
57xlDialogPropertiesDateieigenschaften
58xlDialogProtectDocumentBlatt schützen
59xlDialogRoutingSlipMailverteiler
60xlDialogRowHeightZeilenhöhe
61xlDialogRunMakro
62xlDialogSaveAsSpeichern unter
63xlDialogSelectSpecialInhalte auswählen
64xlDialogSendMailMappe als Mail
65xlDialogSetBackgroundPictureHintergrundbild
66xlDialogSetPrintTitlesDrucktitel
67xlDialogSortSortieren
68xlDialogUnhideTabelle einblenden
69xlDialogWorkbookAddBlatt verschieben/kopieren
70xlDialogWorkbookNameBlatt umbenennen
71xlDialogWorkbookNewTabelle usw. einfügen
72xlDialogWorkbookProtectArbeitsmappe schützen
73xlDialogZoomZoom

Download: integrierte_dialogfelder.xlsm

Kommagetrennte Vornamen zählen (mit Arrayfunktionen)UDF - benutzerdefinierte FunktionArrayfunktion/Matrixfunktion

Kategorien: Tabelle ▸ Zellen und Stringoperationen ▸ Teile

(Tipp 138) Nachricht zum Beitrag an Autor Nach oben

In einigen Zellen stehen mehrere Vornamen, durch ein Komma getrennt. Wie kann ich die Vornamen unter dem Datenbereich auswerten lassen? Beispiel: Tobias, Jens Ingo Frank, Tobias Ingrid, Sabine

Drei Varianten:

Namen zählen ab Excel 365: dynamische Arrayformeln

Sub Namen_Zaehlen() Dim intN As Integer Dim rngZelle As Range Dim arrTemp Dim arrSammler(), lngArrSammler As Long intN = 0 lngArrSammler = 0 For Each rngZelle In Range("A1:B4").Cells arrTemp = Split(rngZelle, ", ") If UBound(arrTemp) > -1 Then For intN = 0 To UBound(arrTemp) lngArrSammler = lngArrSammler + 1 ReDim Preserve arrSammler(1 To lngArrSammler) arrSammler(lngArrSammler) = Trim(arrTemp(intN)) Next End If Next MsgBox UBound(Application.WorksheetFunction.Unique(arrSammler, 1)) End Sub


Namen ausgeben ab Excel 365: dynamische Arrayformeln als Matrixfunktion

Function Einzelnamen(ByRef rngRange As Range) Dim intN As Integer Dim rngZelle As Range Dim arrTemp Dim arrSammler(), lngArrSammler As Long intN = 0 lngArrSammler = 0 For Each rngZelle In rngRange.Cells arrTemp = Split(rngZelle, ", ") If UBound(arrTemp) > -1 Then For intN = 0 To UBound(arrTemp) lngArrSammler = lngArrSammler + 1 ReDim Preserve arrSammler(1 To lngArrSammler) arrSammler(lngArrSammler) = Trim(arrTemp(intN)) Next End If Next Einzelnamen = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Unique(arrSammler, 1)) End Function

In die Zelle kann dann einfach die Formel:

=Einzelnamen(A1:B8)


Variante für ältere Versionen

'An den Anfang des Moduls: Dim arrSammler(), lngArrSammler As Long Sub Auswerten() Dim intN As Integer, intZ As Integer Dim rngZelle As Range Dim arrTemp intN = 0 lngArrSammler = -1 For Each rngZelle In Range("A1:B4").Cells arrTemp = Split(rngZelle, ",") If UBound(arrTemp) > -1 Then For intN = 0 To UBound(arrTemp) Sammler arrTemp(intN) Next End If Next lngArrSammler = lngArrSammler + 1 MsgBox lngArrSammler End Sub Function Sammler(ByVal strName As String) Dim lngZ As Long, strTemp As String strTemp = LCase(Trim(strName)) If lngArrSammler >= 0 Then For lngZ = 0 To lngArrSammler If LCase(arrSammler(lngZ)) = strTemp Then Exit Function Next End If lngArrSammler = lngArrSammler + 1 ReDim Preserve arrSammler(lngArrSammler) arrSammler(lngArrSammler) = Trim(strName) End Function

Die Funktion prüft nur, ob der aktuelle Name bereits im Array ist und erweitert den Array. Geschmacksache - das kann natürlich auch in die eigentliche Routine.

Kreuzung aus Spalten- und Zeilenüberschrift ermittelnFormellösungArrayfunktion/Matrixfunktion

Kategorie: Tabelle ▸ Matrix

(Tipp 328) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich mit einer Formel den Schnittpunkt aus Zeilen- und Spaltenüberschrift ermitteln?

In einer Tabelle gibt es Zeilen- und Spaltenüberschriften. Per Formel soll der Schnittpunkt ermittelt werden bzw. die Zahl, die sich dort befindet.<7P>

Im Beispiel ist die Tabelle im Bereich A1:M6. Spalte A enthält Namen, Zeile 1 Monate.

In A10 soll der zu suchende Name eingetragen werden, in B10 der zu suchende Monat.

SVERWEIS

MIt SVERWEIS lassen wir in Spalte A nach dem Namen suchen, als Spaltenindex nehmen wir die Funktion VERGLEICH():

=SVERWEIS(A10;A2:M5;VERGLEICH(B10;B1:M1;0)+1)

Damit erhalten wir die Zahl im Schnittpunkt des ersten Treffers in Zeile 3, was hier die Zahl 22 ist.

Dynamische Arrayfunktion FILTER() (ab Excel 365)

Allerdings gibt es noch weitere mögliche Schnittpunkte, denn sowohl der Name als auch der Monat sind zweimal enthalten. Das wäre insgesamt vier Schnittpunkte.

Mit FILTER(B2:M6;A2:A6=A10;"") erhalten wir alle Daten der Tabelle, bei denen in Spalte A der zu suchende Name steht - allerdings nur die beiden Zeilen. Wir haben also eine Matrix bzw. einen Array, die/der aus zwei Zeilen zu jeweils 12 Zahlen besteht.

Aus diesem Array lassen wir mit einem zusätzlichen Filter die Spalten filtern, die auf den zu suchenden Monat zutreffen. Dazu nehmen wir wieder die Filter-Funktion und verwenden dort als Matrix das Ergebnis der ersten Filter-Funktion:

=FILTER(FILTER(B2:M6;A2:A6=A10;"");B1:M1=B10;"")

Als Ergebnis dieser Formel haben wir nun die vier Schnittpunkte, wie es im Beispiel zu sehen ist.

Mit dem Schnittmengenoperator @ können wir - wenn gewünscht - festlegen, dass das Verhalten wie beim SVERWEIS ist, dass also nur das erste Ergebnis angezeigt wird. Dazu fügen wir das @-Zeichen nach dem Gleichheitszeichen ein:

=@FILTER(FILTER(B2:M6;A2:A6=A10;"");B1:M1=B10;"")

Löschen verhindern (Worksheet_Change)Makro/Sub/Prozedur

Kategorien: Tabelle ▸ Schutz und Ereignisse ▸ Tabellen

(Tipp 412) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich ohne Blattschutz das Löschen verhindern?

Möglichkeit mit Application.Undo:

Wichtig ist, den Bereich festzulegen, in dem das Löschen verhindert werden soll und um welche Daten es sich handelt, die nicht gelöscht werden sollen. Im Beispiel geht es um die Spalte 7 (G), in der sich Formeln befinden.

Dazu eignet sich Worksheet_Change, was Änderungen auf der Tabelle überwacht. Wenn dort in Spalte 7 etwas gelöscht oder durch einen konstanten Wert überschrieben wurde, soll rückgängig gemacht werden:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 7 And Target.HasFormula = False Then Application.Undo End Sub

Letzten Wert in einem Bereich ermittelnUDF - benutzerdefinierte Funktion

Kategorie: Tabelle ▸ Zellen

(Tipp 431) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich den letzten Wert in einem Bereich ermitteln?

Folgenden Code in ein Standardmodul kopieren:

Function LetzterWert(Bereich As Range) As Variant Dim Zelle As Range Application.Volatile For Each Zelle In Bereich If Zelle <> "" Then LetzterWert = Zelle Next End Function

Um den letzten Wert im Bereich A1:J2 zu suchen kommt dann in die Zelle:

=Letzterwert(A1:J2)



Markierten Bereich als CSV speichernMakro/Sub/Prozedur

Kategorien: Dateien und Ordner ▸ Dateioperation und Stringoperationen ▸ Verketten

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

Wie kann ich einen markierten Bereich als speichern?

Sub Range2CSV() Dim varPfad As Variant Dim strPfad As String, strText As String, strTemp As String Dim objZelle As Object Dim lngZeile As Long, lngI As Long, lngDnr As Long Dim intFrage As Integer If Workbooks.Count = 0 Then MsgBox "Keine Mappe offen.", vbOKOnly + vbInformation, "Keine Mappe" Exit Sub End If If ActiveSheet.Type <> -4167 Then MsgBox "Das aktive Blatt ist kein Tabellenblatt.", vbOKOnly + vbInformation, "Keine Tabelle" Exit Sub End If If Selection.Cells.Count = 1 Then MsgBox "Es ist kein Bereich markiert.", vbOKOnly + vbInformation, "Keine Markierung" Exit Sub End If varPfad = Application.GetSaveAsFilename("", "CSV-Dateien (*.csv), *.csv") If varPfad = False Then Exit Sub If Dir(varPfad) <> "" Then intFrage = MsgBox("Die Datei existiert bereits. Soll sie überschrieben werden?", vbYesNo + vbExclamation, "Datei existiert") If intFrage = vbNo Then MsgBox "Datei nicht erzeugt.", vbOKOnly + vbInformation, "Abbruch" Exit Sub End If End If strPfad = varPfad lngI = 0 strText = "" For Each objZelle In Selection lngI = lngI + 1 If lngI = 1 Then lngZeile = objZelle.Row If objZelle.Row = lngZeile Then strTemp = strTemp & objZelle.Text & ";" Else strTemp = Left(strTemp, Len(strTemp) - 1) strText = strText & strTemp & vbNewLine strTemp = objZelle.Text & ";" End If lngZeile = objZelle.Row Next strTemp = Left(strTemp, Len(strTemp) - 1) strText = strText & strTemp & vbNewLine lngDnr = FreeFile Open strPfad For Output As #lngDnr Print #lngDnr, strText Close #lngDnr MsgBox "Datei erzeugt.", vbOKOnly + vbInformation, "Fertig" End Sub

Download: range2csv.xlam

Matrixformel: Zahlen in Datumsspanne addierenFormellösungArrayfunktion/Matrixfunktion

Kategorien: Datum/Zeit ▸ Datum und Tabelle ▸ Matrix

(Tipp 327) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich Zahlen zu Datumsangaben, die in einem bestimmten Bereich liegen, addieren?

Ab Excel 365: Funktion FILTER()

Gegeben:

  • Spalte A im Datumsformat. Daten von 01.01.99 bis 31.12.99
  • Spalte B im Zahlenformat. Zahlen wie 2.340,00
  • Spalte C im Textformat. Immer vierstellige Zahlen wie 9000

Anforderung:

Summe der Zahlen in B, wenn das Datum in A in einem bestimmten Zeitraum liegt und in C eine bestimmte Zahl steht.

Beispiel: In E1 und E2 stehen Beginn und Ende der gewünschten Zeitspanne, in E3 die geforderte Zahl, die in C enthalten sein soll.

Formel:

Die Arrayfunktion FILTER(B1:B35;(A1:A35>=E1)*(A1:A35<=E2)*(C1:C35=E3);0) liefert die Zahlen zu den Zeilen, die den Kriterien entsprechen. Das heißt, dass diese nur addiert werden müssen:

=SUMME(FILTER(B1:B35;(A1:A35>=E1)*(A1:A35<=E2)*(C1:C35=E3);0))

Nicht geschützte Zellen suchenMakro/Sub/Prozedur

Kategorien: Tabelle ▸ Zellen und Tabelle ▸ Schutz

(Tipp 147) Nachricht zum Beitrag an Autor Nach oben

Ich habe die Arbeitsblätter geschützt. Teilweise sind die Zellen aber ungeschützt. Ich würde gerne einfach die Tabelle markieren und dann im VBA die Werte aller ungeschützten Zellen löschen ohne genaue Bezüge angeben zu müssen.

Die gesamte Tabelle nach ungeschützten Zellen durchsuchen zu lassen würde sicher zu lange dauern. Besser ist es, den Bereich, der durchsucht werden soll, einzugrenzen und dann in einer verschachtelten Schleife alle Inhalte ungeschützter Zellen zu löschen. Das Makro wäre dann:

Sub UngeschuetztLoeschen() Dim lngZ As Long, lngZ1 As Long, lngZ2 As Long, lngS As Long, lngS1 As Long, lngS2 As Long lngZ1 = 1 'Erste Zeile mit zu löschenden Inhalten lngZ2 = 100 'Letzte Zeile mit zu löschenden Inhalten lngS1 = 1 'Erste Spalte mit zu löschenden Inhalten lngS2 = 5 'Letzte Spalte mit zu löschenden Inhalten For lngZ = lngZ1 To lngZ2 For lngS = lngS1 To lngS2 Application.StatusBar = Cells(lngZ, lngS).Address If Cells(lngZ, lngS).Locked = False Then Cells(lngZ, lngS).ClearContents Next Next Application.StatusBar = False End Sub

Oder einfach per UsedRange:

Dim rngZelle As Range For Each rngZelle In ActiveSheet.UsedRange If rngZelle.Locked = False Then rngZelle.ClearContents Next

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

SVERWEIS: Suchkriterium als Teil (VBA + Formel)UDF - benutzerdefinierte FunktionFormellösungArrayfunktion/Matrixfunktion

Kategorie: Tabelle ▸ Matrix

(Tipp 167) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich wie mit dem SVERWEIS nach einer Zeichenfolge suchen, von der jedoch nur der erste Teil bekannt ist?

UDF - benutzerdefinierte Funktion

Die Syntax ist die gleiche wie bei der integrierten Funktion SVERWEIS:

=TeilSverweis(Suchkriterium;Matrix;Spaltenindex)

Function TeilSverweis(Suchkriterium As Variant, Bereich As Range, Spaltenindex As Integer) As Variant Dim rngZelle As Range Application.Volatile TeilSverweis = "" If Suchkriterium <> "" Then For Each rngZelle In Bereich If rngZelle.Column = Bereich.Column Then If CStr(Left(rngZelle.Value, Len(Suchkriterium))) = CStr(Suchkriterium) Then TeilSverweis = Cells(rngZelle.Row, rngZelle.Column + Spaltenindex - 1) Exit Function End If End If Next TeilSverweis = "Nicht gefunden" End If End Function


Dynamische Arrayfunktion FILTER() (ab Excel 365)

Interessante Möglichkeiten bietet die Funktion FILTER().

Diese Funktion liefert - je nachdem, welche Bereiche als Parameter verwendet werden - komplette Datensätze, die an der Formel überlaufen. Zum Beispiel diese Formel:

=FILTER(A2:C26;ISTFEHLER(FINDEN(KLEIN(E2);KLEIN(A2:A26)))=FALSCH;"")

In E2 steht die in A:A26 zu suchende Zeichenfolge. Damit Groß-/Kleinschreibung egal sind, wird alles mit KLEIN() umgewandelt. Die Formel gibt dann alle Datensätze zurück, bei denen in Spalte A die zu suchende Zeichenfolge irgendwo enthalten ist.

Anders ist es bei dieser Formel:

=FILTER(A2:C26;LINKS(A2:A26;LÄNGE(E2))=E2;"")

Sie gibt nur die Datensätze zurück, bei denen die Einträge in Spalte A mit der Zeichenfolge in E2 beginnen. Das sind sicher weniger, die Ergebnismenge ist hier kleiner.

Werden nicht die kompletten Datensätze benötigt, sondern z. B. nur eine Spalte, kann das im ersten Parameter der FILTER()-Funktion im Bereich eingegrenzt werden. So gibt diese Formel nur die Daten aus Spalte B zurück:

=FILTER((B2:B26);ISTFEHLER(FINDEN(KLEIN(E2);KLEIN(A2:A26)))=FALSCH;"")

Wird nur die erste Zelle aus den insgesamt gefundenen gebraucht, kann der Schnittmengenoperator @ eingesetzt werden:

=@FILTER((B2:B26);ISTFEHLER(FINDEN(KLEIN(E2);KLEIN(A2:A26)))=FALSCH;"")

Diese Funktion kann also u. U. ein besserer Ersatz für den SVERWEIS() sein, weil wir hiermit sehr flexibel sein können.

SVERWEIS: Zellen rechts addieren (auch mit Arrayfunktion)FormellösungArrayfunktion/Matrixfunktion

Kategorie: Tabelle ▸ Matrix

(Tipp 198) Nachricht zum Beitrag an Autor Nach oben

Per SVERWEIS lasse ich in Spalte A einen Begriff suchen. Wie ist es möglich, alle sich rechts vom gefundenen Begriff enthaltenen Zahlen zu addieren?

In E1 befindet sich der Suchbegriff.

=SUMME(INDIREKT("B"&VERGLEICH(E1;A1:A15;0)):INDIREKT("D"&VERGLEICH(E1;A1:A15;0)))


Dynamische Arrayfunktion (ab Excel 365)

Hier hat man natürlich leichtes Spiel, indem man die Funktion FILTER() verwendet. Im ersten Parameter, der Matrix, wählt man einfach den Bereich, in dem die Zahlen stehen. Da die dann auch ausgegeben werden, können die auch gleich addiert werden:

=SUMME(FILTER(B1:C5;A1:A5=E1;0))

Symbolleistenschaltfläche hinzufügenMakro/Sub/Prozedur

Kategorie: Menü ▸ Veraltet

(Tipp 60) Nachricht zum Beitrag an Autor Nach oben

Wie kann man der Standardsymbolleiste beim Laden einer bestimmten Arbeitsmappe ein Schaltflächensymbol mit Makrozuweisung und Kurznotiz hinzufügen und beim Schließen der Arbeitsmappe wieder entfernen?

Hinweis zu Excel 2007: In diesem Fall wird im Ribbon Add-Ins ein eigener Bereich erstellt..

Sub Erstellen() Toolbars(1).ToolbarButtons.Add Button:=211 With Toolbars(1).ToolbarButtons(Toolbars(1).ToolbarButtons.Count) .OnAction = "MeinMakro" .Name = "Mein Makro ausführen!" End With End Sub Sub Loeschen() Toolbars(1).ToolbarButtons(Toolbars(1).ToolbarButtons.Count).Delete End Sub



Textdatei erstellen und Text wieder in Excel einlesenMakro/Sub/ProzedurUDF - benutzerdefinierte FunktionArrayfunktion/Matrixfunktion

Kategorie: Dateien und Ordner ▸ Dateioperation

(Tipp 33) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus einem Tabellenbereich eine Textdatei erstellen und diese Textdatei wieder in Excel einlesen?

In Spalte A wird solange gesucht, bis eine leere Zelle gefunden wird. Natürlich wäre auch eine For-Schleife mit Application.Cells(Rows.Count, 1).End(xlUp).Row möglich.

Die Daten aus A, B und C werden mit einem Semikolon als Trennzeichen in eine Textdatei eingelesen.

Einlesen in eine Textdatei mit immer gleichem Pfad:

Sub AlsTextSpeichern() Dim intI As Integer, lngDNr As LongPtr lngDNr = FreeFile 'Pfad anpassen Open "C:\Eigene Dateien\aus Tabelle.txt" For Output As #lngDNr intI = 2 'erste Zeile mit Angaben Do While Cells(intI, 1).Value <> "" 'Schleife, solange die Zelle nicht leer ist 'Übernehmen der Daten in die Textdatei Print #lngDNr, Cells(intI, 1) & ";" & Cells(intI, 2) & ";" & Cells(intI, 3) intI = intI + 1 Loop Close #lngDNr End Sub

Einlesen in eine Textdatei mit wählbarem Pfad:

Sub AlsTextSpeichern1() Dim intI As Integer, lngDNr As LongPtr Dim varPfad varPfad = Application.GetSaveAsFilename(InitialFileName:="Test", fileFilter:="Textdateien (*.txt), *.txt") If varPfad = False Then Exit Sub lngDNr = FreeFile Open varPfad For Output As #lngDNr intI = 2 Do While Cells(intI, 1).Value <> "" Print #lngDNr, Cells(intI, 1) & ";" & Cells(intI, 2) & ";" & Cells(intI, 3) intI = intI + 1 Loop Close #lngDNr End Sub

Textdatei in Exceldatei einlesen, immer gleicher Pfad:

Da das Semikolon als Trennzeichen verwendet wurde, brauchen wir die Textdatei als solche nicht aus- und in Excel einzulesen, sondern wir können die Datei direkt öffnen:

Sub AusTextAufrufen() On Error Resume Next 'falls Datei nicht existiert 'hier nur den Pfad ändern Workbooks.OpenText Filename:="C:\Eigene Dateien\aus Tabelle.txt", DataType:=xlDelimited, semicolon:=True End Sub

Textdatei in Exceldatei einlesen, wählbarer Pfad:

Sub AusTextAufrufen1() Dim varPfad varPfad = Application.GetOpenFilename(fileFilter:="Textdateien (*.txt), *.txt") If varPfad = False Then Exit Sub Workbooks.OpenText Filename:=varPfad, DataType:=xlDelimited, semicolon:=True End Sub

Gibt es andere Trennzeichen, erfolgt das Aufteilen auf die Zellen natürlich nicht unbedingt. Dann kann entweder mit Split() gearbeitet werden oder es kann mit der integrierten Methode Text in Spalten aufgeteilt werden.


Dynamische Arrayformel mit Matrixfunktion

Möglich ist natürlich auch ab Excel 365, die Textdatei mittels benutzerdefinierter Matrixfunktion auszulesen und die Ergebnisse als Array zu übergeben:

Function DateiEinlesen(strDatei, strTrenner, intSpalten) Dim intS As Integer, lngZ As LongPtr Dim lngDNr As Long, strZeile As String, arrTemp Dim arrS() lngDNr = FreeFile lngZ = 0 Open strDatei For Input As #lngDNr Do While Not EOF(lngDNr) Line Input #lngDNr, strZeile If strZeile <> "" Then arrTemp = Split(strZeile, strTrenner) lngZ = lngZ + 1 ReDim Preserve arrS(1 To intSpalten, 1 To lngZ) For intS = 1 To intSpalten If UBound(arrTemp) >= intS Then arrS(intS, lngZ) = arrTemp(intS) Else arrS(intS, lngZ) = "" End If Next End If Loop Close #lngDNr DateiEinlesen = Application.WorksheetFunction.Transpose(arrS) End Function

In die Zelle kommt dann nur noch die Formel:

=DateiEinlesen(Pfad zur Datei;Trennzeichen;Anzahl der Spalten)

=DateiEinlesen(A1;";";4)

Allerdings sollten die Dateien natürlich nicht zu groß sein, weil die Berechnung dieser Formel sonst alles verzögern würde.

Umlaute ersetzen lassenMakro/Sub/Prozedur

Kategorie: Suchen/Ersetzen

(Tipp 69) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich im Bereich A5 bis C10 alle Umlaute in Selbstlaute umwandeln und umgekehrt?

Es gibt verschiedene Möglichkeiten. Ein zweidimensionaler Array würde nahe liegen, aber mit dieser Variante ist es etwas kürzer.

Wandelt Umlaute und das ß in Selbstlaute um:

Sub InSelbstlaute() Dim arrSuchen(), arrErsetzen(), intI As Integer arrSuchen = Array("Ö", "Ä", "Ü", "ö", "ä", "ü", "ß") arrErsetzen = Array("Oe", "Ae", "Ue", "oe", "ae", "ue", "ss") For intI = 0 To UBound(arrSuchen) Range("A5:C10").Replace What:=arrSuchen(intI), Replacement:=arrErsetzen(intI), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True Next End Sub

Die Schleife läuft über den ersten Array und ersetzt im Range das gerade durchlaufene Zeichen durch den String, der im zweiten Array an der gleichen Stelle ist.

Wandelt Selbstlaute und ss in Umlaute um:

Sub InUmlaute() Dim arrSuchen(), arrErsetzen(), intI As Integer arrSuchen = Array("Oe", "Ae", "Ue", "oe", "ae", "ue", "ss") arrErsetzen = Array("Ö", "Ä", "Ü", "ö", "ä", "ü", "ß") For intI = 0 To UBound(arrSuchen) Range("A5:C10").Replace What:=arrSuchen(intI), Replacement:=arrErsetzen(intI), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True Next End Sub

Mit dem „ss“ muss man natürlich aufpassen, das darf nicht mehr generell ersetzt werden. Hier muss also je nach Situation bzw. Anforderungen verfahren werden.

Variablen Bereich markierenMakro/Sub/Prozedur

Kategorie: Tabelle ▸ Selection

(Tipp 145) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die nächste leere Spalte nach rechts in der Höhe einer Datenbank markieren?

Hier gibt es viele Möglichkeiten.

Der folgende Code stellt die Zeile der letzten ausgefüllten Zelle in Spalte A (1) und die Spalte der letzten ausgefüllten Zelle in Zeile 1 fest. Anschließend wird genau eine Spalte in der Höhe dieser Daten markiert:

Dim lngZ As LongPtr, lngS As LongPtr lngZ = Cells(Rows.Count, 1).End(xlUp).Row lngS = Cells(1, Columns.Count).End(xlToLeft).Column Range(Cells(1, lngS + 1), Cells(lngZ, lngS + 1)).Select

Auch UsedRange kann ggf. gut eingesetzt werden. Hier wird neben dem UsedRange ein Bereich markiert, der genau so breit ist wie der UsedRange:

Dim lngS As LongPtr lngS = ActiveSheet.UsedRange.Columns.Count Range(ActiveSheet.UsedRange.Address).Offset(0, lngS).Select

Die Frage ist natürlich, warum markiert werden soll. Man kann die Elemente auch direkt ansprechen, dann zappelt auch der Bildschirm nicht so.

Verfügbarkeit von VariablenMakro/Sub/ProzedurTipp

Kategorie: Basics ▸ Variablen

(Tipp 213) Nachricht zum Beitrag an Autor Nach oben

Wird eine Variable innerhalb einer Prozedur deklariert, kann nur der Code innerhalb dieser Prozedur auf den Wert zugreifen oder ändern. Manchmal soll die Variable jedoch in allen Prozeduren oder in allen Prozeduren in einer Arbeitsmappe zur Verfügung stehen. Die Art der Verfügbarkeit einer Variablen wird bei der Deklaration festgelegt. Es gibt drei mögliche Gültigkeitsbereiche für eine Variable:

  • Innerhalb einer Prozedur Dim - Lokal
  • Am Anfang des Modules Dim oder Private - Modulebene
  • Am Anfang des Moduls Public - Öffentlich

Lokal

Die Variable wird nur innerhalb der umgebenen Prozedur erkannt und eignet sich für temporäre Berechnungen. Wird ein Variablen-Name in mehreren Prozeduren benutzt, ist die Variable nur lokal gültig. Gleiche Variablen-Namen in anderen Prozeduren bleiben unverändert vom Wert. Jede Prozedur erkennt nur ihre eigene Variablen.

Sub Test() Dim MeineVariable ... ... End Sub Sub Test2() Dim MeineVariable '(Lokale Variable mit gleichem Namen wie in Prozedur Test, 'entspricht nicht der Variablen von Prozedur Test) ... ... End Sub

Soll im Beispiel in der Routine Test2 auch die Variable MeineVariable verwendet werden, kann sie bei dieser Art der Deklaration übergeben werden, wenn z. B. die zweite Routine durch die erste aufgerufen werden soll:

Sub Test() Dim MeineVariable ... ... Test2 MeineVariable End Sub Sub Test2(ByVal MeineVariable As String) 'Hier kann MeineVariable weiterverwendet werden. End Sub

Modulebene

Variablen auf Modulebene haben aus allen Prozeduren innerhalb eines Moduls Gültigkeit. Variablen aus anderen Modulen haben keine Gültigkeit.

Dim MeineVariable '(Variable auf Modulebene) Private MeineVariable1 Sub Test() ... ... ... End Sub

Öffentlich

Variablen, die mit Public deklariert wurden, haben die umfassendste Gültigkeit. Öffentliche Variablen stehen jeder Prozedur, die sich in einem beliebigen Modul der jeweiligen Arbeitsmappe befinden kann, zur Verfügung. Öffentliche Variablen werden genau wie Variablen auf Modulebene allgemeiner Module vor den Prozedurdefinitionen deklariert.

Public MeineVariable 'Öffentliche Variable Public MeineVariable1 Sub Test() ... ... ... End Sub

Tipp

Wer viel mit Variablen, die immer zur Verfügung stehen müssen, arbeitet, sollte sich mit Klassenmodulen beschäftigen. Damit kann man eigene Objekte erstellen, denen man Eigenschaften zuweist, die dann immer zur Verfügung stehen. So könnte ein Autohändler zum Beispiel das Objekt Auto erstellen, für dieses Objekt die Eigenschaft Farbe. Im Code braucht man dann nur noch zu schreiben:

Auto.Farbe = "rot" ... ... MsgBox Auto.Farbe

Von Spalten zu ZeilenFormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Tabelle ▸ Matrix

(Tipp 238) Nachricht zum Beitrag an Autor Nach oben

Wie können Daten in Spalten zu Daten in Zeilen umgewandelt werden?

  1. Die ganze Tabelle markieren,
  2. Kopieren,
  3. neue Tabelle aktivieren,
  4. A1 aktivieren,
  5. Inhalte einfügen,
  6. Transponieren ankreuzen.

Dynamische Arrayformel

Ab Excel 365 kann die Tabelle auch einfach per Formel gedreht werden. Dazu die Funktion MTRANS() verwenden und den Bereich der Tabelle angeben:

=MTRANS(E1:I3)

Vornamen und Nachnamen trennenMakro/Sub/ProzedurUDF - benutzerdefinierte FunktionTipp

Kategorie: Stringoperationen ▸ Teile

(Tipp 124) Nachricht zum Beitrag an Autor Nach oben

In einem markierten Bereich befinden sich in jeweils einer Zelle Vornamen und Nachnamen, die durch Leerstellen getrennt sind. Wie kann ich Vornamen und Nachnamen in die Nachbarzellen einlesen lassen?

Hier wird an den Leerzeichen getrennt, ggf. müssen noch weitere Schreibweisen beachtet werden.

Schleife über die Zellen

Variante 1:

Sub Namen_trennen() Dim rngZelle As Range Dim intS As Integer Dim strV As String Dim arrTemp 'Bereich muß markiert sein, für jede Zelle in der Markierung: For Each Zelle In Selection With Zelle If .Value <> "" Then arrTemp = Split(.Value, " ") Select Case UBound(arrTemp) Case 0: Cells(.Row, .Column + 1) = .Value Case Else strV = "" For intS = 0 To UBound(arrTemp) - 1 strV = strV & IIf(strV <> "", " ", "") & arrTemp(intS) Next Cells(.Row, .Column + 1) = strV Cells(.Row, .Column + 2) = arrTemp(UBound(arrTemp)) End Select End If End With Next End Sub

Variante 2:

Sub Namen_trennen1() Dim intA As Integer, intB As Integer, intI As Integer Dim Zelle As Object 'Bereich muß markiert sein, 'für jede Zelle in der Markierung: For Each Zelle In Selection With Zelle If .Value <> "" Then 'Suche nach der ersten Leerstelle intA = InStr(.Value, " ") 'Schleife, falls mehrere durch leer getrennte Vornamen 'vorhanden sind, z. B. Ute Elke Meier For intI = 0 To Len(.Value) intB = InStr(Right(.Value, Len(.Value) - intA), " ") intA = InStr(Right(.Value, Len(.Value) - intA), " ") + intA Next 'Aufteilen auf die 1. Zelle rechts und die 2. Zelle rechts 'Vorname Cells(.Row, .Column + 1).Value = Left(.Value, intA - 1) 'Name Cells(.Row, .Column + 2).Value = Right(.Value, Len(.Value) - intA) End If End With Next End Sub

Text in Spalten

Variante 3:

Sub Namen_trennen2() Dim lngZeile As Long, lngSpalte As Long, strZiel As String lngZeile = ActiveCell.Row: lngSpalte = ActiveCell.Column strZiel = Cells(lngZeile, lngSpalte + 2).Address Selection.TextToColumns Destination:=Range(strZiel), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, _ FieldInfo:=Array(Array(1, 1), Array(2, 1)) End Sub

Es ist mit dieser Methode auch möglich, mehr als 2 Wörter, die mit Leerzeichen getrennt sind, in die Nachbarzellen zu übertragen. Sollten in nebenstehenden Zellen Daten stehen, muss man vor der Ausführung des Befehls darauf achten, entsprechend viele Spalten einzufügen.


Dynamische Arrayformel mit Matrixfunktion

Je nach Situation kann auch eine Arrayformel in Betracht gezogen werden:

Function NamenTrennen(ByVal strName As String, Optional intAnzahl As Integer = 5) Dim arrTemp, intS As Integer ReDim arrNamen(1 To intAnzahl) NamenTrennen = "" For intS = 1 To intAnzahl arrNamen(intS) = "" Next If strName <> "" Then arrTemp = Split(strName, " ") For intS = 0 To UBound(arrTemp) If intS < intAnzahl Then arrNamen(intS + 1) = arrTemp(intS) Next End If NamenTrennen = arrNamen End Function

In die Zelle kommt dazu diese überlaufende Formel:

=NamenTrennen(A1)

Da die Anzahl der Namensteile variieren kann, ein Array aber (in diesem Fall) immer gleich breit ist, ist eine Breite von fünf Zellen voreingetragen. Der Array wird dabei von links gefüllt, so dass die einzelnen Teile in der linken Zelle beginnen. Mit einem zweiten Parameter in der Funktion kann diese Breite geändert werden, zum Beispiel auf vier Zellen Breite:

=NamenTrennen(A1;4)

Besteht der Name dann aus mehr Teilen, werden die restlichen rechten Teile nicht angezeigt.

Wert kopieren - variabler ZielbereichMakro/Sub/Prozedur

Kategorie: Tabelle ▸ Zellen

(Tipp 390) Nachricht zum Beitrag an Autor Nach oben

Der Wert aus A1 soll in den Bereich kopiert werden, der in C1 enthalten ist.

In C1 steht A3:B10, also soll der Wert aus A1 in die Zellen im Bereich A3:B10 kopiert werden.

Beispiel 1:

Range(Range("C1")) = Range("A1")

Beispiel 2:

Range("A1").Copy Range(Range("C1")).PasteSpecial Paste:=xlPasteValues



Werte in einem Bereich zählen (VBA + Formel)UDF - benutzerdefinierte FunktionFormellösungArrayfunktion/Matrixfunktion

Kategorie: Tabelle ▸ Matrix

(Tipp 168) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich in einem Bereich die Zahlen zählen, die größer/gleich einer Zahl und kleiner gleich einer anderen Zahl sind?

UDF - benutzerdefinierte Funktion

Wenn der Bereich nicht zu groß ist, kann folgende benutzerdefinierte Funktion verwendet werden:

Function Zaehlen(Bereich As Range, Minimum, Maximum) Dim objZelle As Object, intI As Integer intI = 0 For Each objZelle In Bereich If objZelle.Value >= Minimum And objZelle.Value <= Maximum Then intI = intI + 1 End If Next Zaehlen = intI End Function

In die Zelle muß dann eingegeben werden:

=Zählen(Bereich;kleinste Zahl;größte Zahl)

=Zählen(A1:A100;10;20)


Formel

Am einfachsten ist jedoch, wenn die integrierte Funktion ZÄHLENWENNS() verwendet wird:

=ZÄHLENWENNS(A1:A10;">2";A1:A10;"<7")


Dynamische Arrayfunktion (ab Excel 365)

Sollen die Zahlen, die zu den Kriterien passen, auch gleich ausgegeben werden, kann das mit der Funktion FILTER() erfolgen:

=FILTER(A1:A10;(A1:A10>2)*(A1:A10<7);"")

Die Zahlen stehen dann untereinander an der Zelle mit der Formel und können auch mit ANZAHL() gezählt werden. Dabei gibt es jedoch eine Besonderheit.

Gibt man =ANZAHL( ein und zieht dann über den Bereich mit den gefundenen Zahlen, wird in die Funktion nicht der Bereich eingetragen, sondern eine ID wie zum Beispiel C3#:

=ANZAHL(C3#)

Die vergibt Excel selbst. Dabei handelt es sich um eine Referenz auf das Ergebnis der Funktion FILTER(), denn die Größe der Ergebnismenge dieser Funktion kann sich ja ändern. Durch diese Referenz wird immer richtig gezählt - egal, ob FILTER() 0, 5 oder sonst wie viele Ergebnisse liefert.

Natürlich ist auch sowas möglich:

=ANZAHL(FILTER(A1:A10;(A1:A10>2)*(A1:A10<7);""))

Workbook-EreignisseMakro/Sub/ProzedurTipp

Kategorien: Basics ▸ Ereignisse und Ereignisse ▸ Basics

(Tipp 98) Nachricht zum Beitrag an Autor Nach oben

Bei den Workbook-Ereignissen trifft eingeschränkt das zu, was bei den Application-Ereignissen steht. Der markanteste Unterschied ist, dass Workbook-Ereignisse - wie der Name schon sagt - nur die Elemente der Mappe mit dem Code betreffen und dass wir hier kein Klassenmodul einfügen müssen.

Im Visual-Basic-Editor (Alt & F11) reicht es, im Projektfenster auf Diese Arbeitsmappe doppelzuklicken und dann von Allgemein auf Workbook zu wechseln. Nun stehen im rechten Drop-Down-Feld die Ereignisse zur Verfügung:

Parameterinfo

Bei verschiedenen Prozeduren werden auch Parameter übergeben. Diese verhalten sich wie folgt:

Cancel:Die Boolsche Variable steht standardmäßig auf False. Setzt man sie auf True, wird das Ereignis nicht mehr ausgeführt. So kann man z. B. das Schließen der Arbeitsmappe verhindern, indem man Cancel = True innerhalb der Prozedur BeforeClose setzt.
Sh:Sh steht für das aktive Tabellenblatt. Man beachte auch die Eigenschaften und Methoden, die Sh zur Verfügung stehen. So erhält man z.B. über Sh.Name den Namen des aktiven Blattes.
Target:Target steht für den aktiven Bereich und wird häufig dazu benutzt, um den Bereich zum Ausführen eines bestimmten Makros zu bestimmen. So kann man mit: If Target.Address = $A$1 erreichen, daß das Makro nur dann ausgeführt wird, wenn die Zelle A1 aktiv ist.
Wn:Stellt das aktive Fenster dar.

Ereignisse:

Workbook_Activate
Tritt ein, nachdem die Arbeitsmappe aktiviert wurde.
Workbook_AddinInstall
Tritt ein, wenn die Arbeitsmappe als Add-In installiert wurde.
Workbook_AddinUninstall
Tritt ein, wenn die Arbeitsmappe als Add-In deinstalliert wurde.
Workbook_BeforeClose(Cancel As Boolean)
Tritt ein, bevor die Arbeitsmappe geschlossen werden soll.
Workbook_BeforePrint(Cancel As Boolean)
Tritt ein, wenn die Arbeitsmappe ausgedruckt werden soll. Man benutzt diese Prozedur häufig zum Aktualisieren der Daten vor dem Drucken.
Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Tritt ein, wenn die Arbeitsmappe gespeichert werden soll. Man benutzt diese Prozedur häufig zum Aktualisieren der Daten vor dem Speichern. SaveAsUI hat dabei den Wert True, wenn das Dialogfeld Save As angezeigt wird.
Workbook_Deactivate
Tritt ein, wenn die Arbeitsmappe deaktiviert wird, zum Beispiel beim Wechsel in eine andere Arbeitsmappe.
Workbook_NewSheet(ByVal Sh As Object)
Tritt ein, wenn ein neues Blatt eingefügt wird.
Workbook_Open
Tritt ein, wenn die Arbeitsmappe geöffnet wurde. Diese Prozedur wird häufig dazu verwendet, um Werte zu initialisieren, welche später in der BeforeClose-Prozedur wieder entfernt werden sollten.
Workbook_SheetActivate(ByVal Sh As Object)
Tritt ein, wenn ein Blatt aktiviert wird, also bei einem Blattwechsel.
Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
Tritt bei einem Doppelklick auf einem Tabellenblatt ein.
Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
Tritt bei einem Klick mit der rechten Maustaste in einem Tabellenblatt ein.
Workbook_SheetCalculate(ByVal Sh As Object)
Tritt ein, wenn Zellen eines Blattes neu berechnet werden. Dieses Ereigniss tritt auch dann ein, wenn sich ein Bezug verändert. Z. B. in B1 steht "= A1" und A1 wird verändert, so wird die Prozedur ausgeführt, da B1 neu berechnet wird.
Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Tritt ein, wenn mindestens eine Zelle in einem Blatt geändert wurde. Leider gibt es immer wieder Probleme bei externen Bezügen, wenn z.B. Daten per DDE geholt werden. Man sollte dann prüfen, ob man nicht mit Calculate zum Ergebnis kommt, indem man einen Bezug zur DDE-Zelle herstellt (=A1).
Workbook_SheetDeactivate(ByVal Sh As Object)
Tritt ein, wenn ein Blattwechsel stattgefunden hat.
Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Tritt ein, wenn sich die Markierung in einem Blatt ändert.
Workbook_WindowActivate(ByVal Wn As Excel.Window)
Tritt bei einem Fensterwechsel ein.
Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
Tritt bei einem Fensterwechsel ein.
Workbook_WindowResize(ByVal Wn As Excel.Window)
Tritt bei einer Veränderung der Fenstergröße ein.

Worksheet-EreignisseMakro/Sub/ProzedurTipp

Kategorien: Basics ▸ Ereignisse und Ereignisse ▸ Basics

(Tipp 99) Nachricht zum Beitrag an Autor Nach oben

Wie der Name schon sagt, geht es bei den Worksheet-Ereignissen um Aktivitäten in Bezug auf das einzelne Tabellenblatt.

Im Visual-Basic-Editor (Alt & F11) wird dazu im Projektfenster auf die jeweilige Tabelle doppelt geklickt und dann von "Allgemein auf "Worksheet gewechselt. Nun stehen im rechten Drop-Down-Feld die Ereignisse zur Verfügung.

Im Gegensatz zu den Workbook-Ereignissen, welche bei allen Tabellenblättern auftreten, sind die Worksheet-Ereignisse an das Tabellenblatt gebunden, indem sie stehen.

Parameterinfo

Bei verschiedenen Prozeduren werden auch Parameter übergeben. Diese verhalten sich wie folgt:

Cancel:Die Boolsche Variabel steht standardmäßig auf False. Setzt man sie auf True, wird das Ereignis nicht mehr ausgeführt. So kann man z. B. das Öffnen des Auswahlmenüs verhindern, indem man Cancel = True innerhalb der Prozedur "BeforeRightClick setzt.
Target:Target steht für den aktiven Bereich und wird häufig dazu benutzt, um den Bereich zum Ausführen eines bestimmten Makros zu bestimmen. So kann man mit: If Target.Address = $A$1 erreichen, daß das Makro nur dann ausgeführt wird, wenn die Zelle A1 aktiv ist.

Ereignisse:

Worksheet_Activate
Tritt ein, wenn das Tabellenblatt aktiviert wird, also Blattwechsel zu diesem Blatt hin.
Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Tritt bei einem Doppelklick innerhalb der Tabelle ein.
Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)
Tritt bei einem Klick mit der rechten Maustaste innerhalb der Tabelle ein.
Worksheet_Calculate
Tritt ein, wenn Zellen dieses Blattes neu berechnet werden. Dieses Ereignis tritt auch dann ein, wenn sich ein Bezug verändert. Z. B. in B1 steht "= A1" und A1 wird verändert, so wird die Prozedur ausgeführt, da B1 neu berechnet wird.
Worksheet_Change (ByVal Target As Excel.Range)
Tritt ein, wenn mindestens eine Zelle im Tabellenblatt geändert wurde. Leider gibt es immer wieder Probleme bei externen Bezügen, wenn z. B. Daten per DDE geholt werden. Man sollte dann prüfen, ob man nicht mit dem Calculate zum Ergebnis kommt, indem man einen Bezug zur DDE-Zelle herstellt (=A1).
Worksheet_Deactivate
Tritt ein, wenn man von diesem Blatt aus zu einem anderem wechselt.
Worksheet_SelectionChange(ByVal Target As Excel.Range)
Tritt ein, wenn sich die Markierung im Tabellenblatt ändert.

Zelleinträge trennenTipp

Kategorie: Stringoperationen ▸ Teile

(Tipp 244) Nachricht zum Beitrag an Autor Nach oben

In einem Zellbereich stehen in den einzelnen Zellen Vor und Nachnamen. Wie kann ich diese Einträge ohne VBA trennen, so dass in einer Zelle der Vor- und in der Nachbarzelle der Nachname erscheint?

Zunächst sollte man hinter der Spalte, welche die Daten (also Vor- und Nachname) enthält, eine neue Spalte einfügen. Danach ist folgendes Vorgehen möglich:

  1. Entsprechenden Bereich (oder auch die gesamte Spalte) markieren.
  2. Ribbon Daten aufrufen.
  3. Eintrag Text in Spalten wählen, ein Assistent wird gestartet.
  4. Der Eintrag Getrennt muss aktiviert sein.
  5. Mit der Schaltfläche Weiter gelangt man in den zweiten Schritt des Assistenten.
  6. Leerzeichen wählen.
  7. Mit der Schaltfläche Weiter erhält man eine Vorschau.
  8. Mit Fertigstellen die Aktion beenden.

Es ist mit dieser Methode auch möglich, mehr als 2 Wörter, die mit Leerzeichen getrennt sind, in die Nachbarzellen zu übertragen. Sollten in nebenstehenden Zellen Daten stehen, muss man darauf achten, vor der Ausführung des Befehls entsprechend viele Spalten einzufügen.

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.

Zufallszahlen sortiert in einem bestimmten Bereich generieren (mit Arrayfunktion)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionArrayfunktion/Matrixfunktion

Kategorien: Tabelle ▸ Zellen und Filter/Sortieren

(Tipp 139) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich zwischen 6 und 15 Zufallszahlen zwischen 1 und 49 generieren? Die Zahlen sollen auf dem Blatt Tab1 in der Zeile 1 ab A1 stehen. Keine Zahl darf sich wiederholen.

Dazu gibt es verschiedene Möglichkeiten.

Direkt in Zellen eintragen

In der ersten Variante erfolgt die Arbeit direkt an den Zellen, weil hier die Funktionen Finden und Sortieren von Excel genutzt werden. Funktionen, die Excel zur Verfügung stellt, sind i. d. R. recht schnell, so dass das in diesem Fall sicher die kürzere und schnellere Variante ist:

Sub Zufall() Dim intWert As Integer, varWieviele, intI As Integer Dim bolVorhanden As Boolean, rngGef As Range varWieviele = InputBox("Wieviele Zahlen sollen erzeugt werden?", "Anzahl", 6) If Not IsNumeric(varWieviele) Then Exit Sub If varWieviele > 15 Then Exit Sub Sheets("Tab1").Range("A1:O1").ClearContents For intI = 1 To varWieviele intWert = Int((49 * Rnd) + 1) If intI = 1 Then Sheets("Tab1").Cells(1, intI) = intWert Else Do bolVorhanden = False Set rngGef = Range(Cells(1, 1), Cells(1, intI - 1)).Find(intWert) If Not rngGef Is Nothing Then bolVorhanden = True intWert = Int((49 * Rnd) + 1) End If Loop While bolVorhanden = True Sheets("Tab1").Cells(1, intI) = intWert End If Next Sheets("Tab1").Range("A1:O1").Sort Key1:=Sheets("Tab1").Range("A1"), Order1:=xlAscending, Orientation:=xlLeftToRight End Sub

Kern des Codes ist eine Schleife, die so lange läuft, wie eine Zufallszahl nicht mehr in den bisherigen Zufallszahlen gefunden wird. Erst dann wird sie als neue Zufallszahl verwendet.

Weiteres Beispiel - Schleife statt Find:

Sub Zufall() Dim intAnzahl As Integer, intMax As Integer, intMin As Integer Dim intWert As Integer, intI As Integer, intN As Integer Dim bolVorhanden As Boolean intAnzahl = 10 intMax = 49 intMin = 1 'alte Zahlen löschen Range(Cells(1, 2), Cells(intAnzahl, 2)).ClearContents Cells(1, 2) = Int((intMax * Rnd) + intMin) '1. Zahl erzeugen For intI = 2 To intAnzahl 'nächste Zahlen erzeugen Do bolVorhanden = False intWert = Int((intMax * Rnd) + intMin) For intN = 1 To intI 'Kontrolle ob schon vorhanden If Cells(intN, 2) = intWert Then bolVorhanden = True Exit For End If Next Loop While bolVorhanden = True Cells(intI, 2) = intWert 'Zahl eintragen Next End Sub


Erst Array, zum Schluss in Zellen

Manchmal kann die Arbeit mit Zellen aber auch von Nachteil sein. Deshalb ist hier der Vollständigkeit halber noch ein Beispiel, in dem Herangehensweisen mit einem Array aufgezeigt werden:

Sub Zufall1() Dim intWert As Integer, intI As Integer, intN As Integer, varWieviele Dim bolVorhanden As Boolean, bolSortiert As Boolean Dim arrZahlen() varWieviele = InputBox("Wieviele Zahlen sollen erzeugt werden?", "Anzahl", 6) If Not IsNumeric(varWieviele) Then Exit Sub If varWieviele > 15 Then Exit Sub ReDim Preserve arrZahlen(varWieviele) For intN = 0 To varWieviele - 1 intWert = Int((49 * Rnd) + 1) If intN = 0 Then arrZahlen(intN) = intWert Else Do bolVorhanden = False For intI = 0 To intN If arrZahlen(intI) = intWert Then bolVorhanden = True intWert = Int((49 * Rnd) + 1) Exit For End If Next Loop While bolVorhanden = True arrZahlen(intN) = intWert End If Next Do bolSortiert = True For intN = 0 To varWieviele - 2 If arrZahlen(intN + 1) < arrZahlen(intN) Then bolSortiert = False intWert = arrZahlen(intN + 1) arrZahlen(intN + 1) = arrZahlen(intN) arrZahlen(intN) = intWert End If Next Loop While bolSortiert = False Sheets("Tab1").Range("A1:O1").ClearContents For intN = 0 To varWieviele - 1 Sheets("Tab1").Cells(1, intN + 1) = arrZahlen(intN) Next End Sub

Das gesamte Erstellen der Zufallszahlen - bis hin zum Sortieren - erfolgt zunächst in einem Array. Auch hier erfolgt die Prüfung auf Doppelungen. Aber es ist zu sehen, dass dafür eine weitere Schleife eingebaut ist. Eine Funktion wie in PHP in_array() wäre da natürlich günstiger.

Auch zum Sortieren wird die innere Schleife so lange durchlaufen, bis im Array das nächste Element nicht kleiner als das gerade durchlaufene Element ist.

Erst ganz zum Schluss wird der fertige Array in die Zellen übernommen.

Die zweite Variante hat den Vorteil, dass sie beliebig - und unabhängig von Zellen - eingesetzt werden kann. Sie kann auch als eigenständige Funktion erstellt werden, die dann den Array mit den Zufallszahlen zurückgibt. So könnte die Funktion von überall aufgerufen werden; in die Zellen würde dann das Ergebnis eingetragen.

Oft stößt man bei Schleifen, die Zellen lesen und schreiben, auf das Problem, dass die Laufzeit enorm steigt. Deshalb sollte zumindest in Betracht gezogen werden, das Ganze mit Arrays zu erledigen und die Zellzugriffe auf ein Minimum zu beschränken.


Dynamische Arrayformel mit Matrixfunktion (ab Excel 365)

Natürlich kann das auch als Matrixfunktion für eine dynamische Arrayformel erstellt werden. Die Funktion erstellt einen Array mit eindeutigen Zahlen. Zum Schluss wird mit der Arrayfunktion SORTIEREN() sortiert und der Array ausgegeben.

Function Zufallszahlen_Eindeutig(ByVal intAnzahl As Integer, ByVal intMin As Integer, ByVal intMax As Integer) Dim intWert As Integer, intI As Integer, intN As Integer Dim bolVorhanden As Boolean, arrZahlen() Application.Volatile ReDim Preserve arrZahlen(1 To intAnzahl) For intN = 1 To intAnzahl intWert = Int((intMax * Rnd) + intMin) If intN = 1 Then arrZahlen(intN) = intWert Else Do bolVorhanden = False For intI = 1 To intN If arrZahlen(intI) = intWert Then bolVorhanden = True intWert = Int((intMax * Rnd) + intMin) Exit For End If Next Loop While bolVorhanden = True arrZahlen(intN) = intWert End If Next Zufallszahlen_Eindeutig = Application.WorksheetFunction.Sort(arrZahlen, 1, 1, 1) End Function

In die Zelle kommt einfach:

=Zufallszahlen_Eindeutig(Anzahl;Minimum;Maximum)

=Zufallszahlen_Eindeutig(5;1;49)

Sollen die Zahlen untereinander erscheinen, kann die Funktion in MTRANS() gesetzt werden:

=MTRANS(Zufallszahlen_Eindeutig(5;1;49))

Die Funktion ZUFALLSMATRIX() klingt danach, als ob sie die Aufgabe auch erfüllen könnte. Jedoch hat sie bei dieser Aufgabenstellung den großen Nachteil, dass sie sehr häufig mehrfach vorhandene Zahlen erzeugt. Das heißt, dass man so oft berechnen lassen müsste, bis jede Zahl eindeutig ist - und das kann etwas dauern.

Apropos eindeutig: Mit der Funktion EINDEUTIG() könnte natürlich geprüft werden, ob eindeutige Zufallszahlen vorliegen. Allerdings ist die Funktion manchmal schneller als die Sortieren-Funktion, so dass letztere Funktion noch rechnet, wenn EINDEUTIG() schon fertig ist. Das führt dann zum bekannten Fehler ÜBERLAUF.