Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Anleitung: Rechnung erstellenFormellösung

Kategorie: Tabelle ▸ Zellen

(Tipp 207) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich mit Formeln eine Rechnung erstellen?

Du hast eine Tabelle namens "Rechnung" und eine Tabelle namens "Artikel". Auf der Tabelle "Artikel" befinden sich ab A2 die Artikelnummern (hier fortlaufend numeriert), ab B2 die Artikel und ab C2 die Einzelpreise. Auf dem Blatt "Rechnung" wird die Rechnung erstellt.:

  1. In A20 bis A30 sollen die Nummern der gekauften Artikel eingetragen werden.
  2. In B20 bis B30 wird die Anzahl der gekauften Artikel eingetragen.
  3. In C20 bis C30 sollen automatisch die Artikel erscheinen. Dazu kannst Du die Formel verwenden (in einer Zeile): =WENN(ISTNV(SVERWEIS($A20;Artikel!$A$2:$C$28;2));""; SVERWEIS($A20;Artikel!$A$2:$C$28;2))
  4. In D20 bis D30 sollen die dazugehörigen Einzelpreise eingelesen werden. Die Formel dazu (in einer Zeile): =WENN(ISTNV(SVERWEIS($A20;Artikel!$A$2:$C$28;3));""; SVERWEIS($A20;Artikel!$A$2:$C$28;3))
  5. In E20 bis E30 sollen die Preise der gekauften Artikel errechnet werden, die Formel: =WENN(D20="";"";B20*D20)
  6. In E31 soll Netto ausgerechnet werden: =SUMME(E20:E30)
  7. MwSt. in E32: =E31*16%
  8. Brutto in E33: =E31+E32

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.

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

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

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

(Tipp 227) Nachricht zum Beitrag an Autor Nach oben

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

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

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

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

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

Datum aus Kalenderwoche errechnenUDF - benutzerdefinierte Funktion

Kategorie: Datum/Zeit ▸ Datum

(Tipp 174) Nachricht zum Beitrag an Autor Nach oben

Wie kann man aus einer angegebenen Kalenderwoche das Datum bestimmen? Folgende Daten sind gegeben: 1999 (Jahr) 42 (Kalenderwoche) 1 (Tag)

In die Zelle muss dann eingegeben werden: =TagAusKW(Jahr;KW;Tag).

Function TagAusKW(Jahr As Integer, KW As Integer, Tag As Integer) As Date Dim lngT As LongPtr lngT = DateSerial(Jahr, 1, 4) lngT = lngT - Weekday(lngT, 2) + 7 * KW - 7 If (Year(lngT + 4) = Jahr) Then TagAusKW = lngT + Tag End Function



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

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

Kalenderwoche nach DIN (VBA + Formel)UDF - benutzerdefinierte FunktionFormellösung

Kategorie: Datum/Zeit ▸ Datum

(Tipp 169) Nachricht zum Beitrag an Autor Nach oben

Wie kann die Kalenderwoche eines Datums nach DIN berechnet werden?

Formeln

Am einfachsten gehte s sicher so:

=ISOKALENDERWOCHE(A2)

=KALENDERWOCHE(A2;2)

Übrigens können (ab Excel 365) mit nur einer einzigen Formel alle Tage eines Jahres mit Kalenderwochen und Wochentagen (und was auch immer) angezeigt werden:

=TEXT(SEQUENZ(TAGE("31.12."&F1;"1.1."&F1)+1;1;"1.1."&F1;1);"TT.MM.JJJJ")&" (KW "&TEXT(ISOKALENDERWOCHE(SEQUENZ(TAGE("31.12."&F1;"1.1."&F1)+1;1;"1.1."&F1;1));"00")&" "&TEXT(SEQUENZ(TAGE("31.12."&F1;"1.1."&F1)+1;1;"1.1."&F1;1);"TTT")&")"


UDF - benutzerdefinierte Funktionen

Früher, als es die schönen Excelfunktionen noch nicht gab, musste man sich damit behelfen:

Diese Funktion stammt von Christoph Kremer:

Function DINKwoche(Datum) Dim tmp tmp = DateSerial(Year(Datum + (8 - WeekDay(Datum)) Mod 7 - 3), 1, 1) DINKwoche = ((Datum - tmp - 3 + (WeekDay(tmp) + 1) Mod 7)) \ 7 + 1 End Function

Zweite Möglichkeit:

Function kw(Datum As Date) As Single kw = Format(Datum, "ww", , vbFirstFourDays) - IIf(Weekday(Datum) = 1, 1, 0) End Function

Einige alte Beispiele auf diesen Seiten basieren noch auf diesen Berechnungen.

Mit negativen Zeiten rechnenFormellösung

Kategorie: Datum/Zeit ▸ Zeit

(Tipp 192) Nachricht zum Beitrag an Autor Nach oben

Ich benötige für unsere Zeiterfassung die Möglichkeit, auch negative Zeiten (in Stunden und Minuten) berechnen zu können. Beispiel: A1 = 100:00 A2 = 120:00 A3 = (A1-A2) Daraufhin werden nur ####... angezeigt.

Das Vorgehen ist hier immer etwas von den konkreten Gegebenheiten abhängig, denn die Rauten (####) werden nur angezeigt. Dahinter stecken trotzdem die Ergebnisse, man kann also mit diesen Rauten also sogar weiterrechnen. Zum Testen:

  • In A8 14:00 eintragen.
  • In B8 13:00 eintragen.
  • In C8 die Formel =B8-A8 eintragen. Da die Formel ein negatives Ergebnis ergibt, erscheinen die Rauten.
  • In C9 die Formel =C8*-1 eintragen. Es erscheint das korrekte Ergebnis, nur als positive Zahl.

Dieses Prinzip kann also verwendet werden, so dass mit der WENN-Funktion auf ein negatives Ergebnis geprüft wird und eine entsprechende Ausgabe erfolgt.

Alternativ kann die Prüfung bereits bei der Berechnung erfolgen:

=WENN(A8<B8;(A8-B8)*-1;A8-B8)

Nur ist hier der Nachteil, dass immer ein positives Ergebnis angezeigt wird. Ggf. könnte man per bedingter Formatierung darauf aufmerksam machen, aber die positive Zahl bleibt.

Kommt es auf weitere Berechnungen nicht an, sondern nur auf diese Anzeige, kann das Minuszeichen hinzugefügt werden:

=WENN(A8<B8;"-"&TEXT((A8-B8)*-1;"[hh]:mm");A8-B8)

Das negative Ergebnis liegt dann jedoch als Text vor - bei Berechnungen kann es nun Fehlermeldungen geben.

Wie vorgegangen wird, muss also im Einzelfall entschieden werden.

Auf das 1904-Datumsformat wird hier nicht eingegangen, weil das Probleme bereiten kann.

Pause nur berechnen, wenn anwesendUDF - benutzerdefinierte Funktion

Kategorie: Datum/Zeit ▸ Zeit

(Tipp 170) Nachricht zum Beitrag an Autor Nach oben

Pausenregelungen von 9:00 - 9:15 Uhr und 13:00 - 13:30 Uhr. Pausen dürfen nur von der Arbeitszeit abgezogen werden wenn derjenige anwesend ist. Das Pause-Feld muss sich also nach den Komm- u. Geht-Feldern richten.

Das sind verschiedene Konstellationen, die ausgewertet werden müssen. Dazu gibt es natürlich verschiedene Möglichkeiten, hier wird mal der Einsatz von Select Case demonstriert.

Das Problem ist, dass es hier keine Variable gibt, deren Wert ausgewertet werden kann. Es sind immer Bedingungen, die in Kombination zutreffen oder eben nicht. Das heißt, statt der Variablen wird True im Select verwendet:

Function Pausenzeit(kommt, geht, P1Beginn, P1Ende, P2Beginn, P2Ende) Dim datErsteZeit As Date, datZweiteZeit As Date Select Case True Case geht < P1Beginn Or kommt > P2Ende Pausenzeit = 0: Exit Function 'geht vor erster Pause oder kommt nach zweiter Pause Case kommt >= P1Beginn And kommt <= P1Ende And geht > P1Ende datErsteZeit = P1Ende - kommt 'kommt in erster Pause, geht nach erster Pause Case kommt < P1Beginn And geht > P1Ende datErsteZeit = P1Ende - P1Beginn 'kommt vor erster Pause, geht nach erster Pause Case kommt >= P2Beginn And kommt <= P2Ende And geht > P2Ende datErsteZeit = 0: datZweiteZeit = P2Ende - kommt 'kommt in zweiter Pause, geht nach zweiter Pause Case kommt < P1Beginn And geht < P1Ende datErsteZeit = geht - P1Beginn: datZweiteZeit = 0 'kommt vor erster Pause, geht in erster Pause Case kommt >= P1Beginn And geht <= P1Ende datErsteZeit = geht - kommt: datZweiteZeit = 0 'kommt und geht in erster Pause End Select Select Case True Case geht < P2Beginn: datZweiteZeit = 0 'geht vor zweiter Pause Case geht >= P2Beginn And geht < P2Ende: datZweiteZeit = geht - P2Beginn 'geht in zweiter Pause Case Else: datZweiteZeit = P2Ende - P2Beginn End Select Pausenzeit = datErsteZeit + datZweiteZeit End Function

In die Zelle muss dann nur noch:

=Pausenzeit(A4;B4;B1;C1;D1;E1)

Rechnen mit negativen ZeitenFormellösungTipp

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

(Tipp 228) Nachricht zum Beitrag an Autor Nach oben

Immer wieder erscheint die Frage: Wie kann ich in Excel mit negativen Zeiten rechnen?, da man oftmals bei der Verwaltung von Arbeitszeiten ein Problem mit den Fehlzeiten, sprich Minusstunden, hat.

Auf den ersten Blick scheint es, als könne Excel nicht mit negativen Zeiten rechnen, aber weit gefehlt.

Im Standard-Datumssystem ergibt =1:00-5:00 den Fehlerwert #######. Sofern die Anzeige ####### nicht wichtig ist, kann man damit allerdings weiterechnen. So ergibt die Summe der obigen Formel mit 5:00 den korrekten Wert 1:00. Ist also die Anzeige nicht wichtig, kann man es problemlos verwenden.

Eine andere und im Grunde bessere Methode bietet die Formel =(A1-B1) * 24. Dadurch werden die Stunden zwar in Industriestunden umgewandelt. Allerdings bietet dies eine erleichterte Weiterberechnung, gerade in Bezug auf die Berechnung von Löhnen.

Man kann auch zur Berechnung negativer Zeiten ins 1904-Datumssystem wechseln, allerdings beachte man dort die Kompatbilitätsprobleme.

Siehe auch:

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

Kategorien: Ereignisse ▸ Tabellen und Tabelle ▸ Formeln

(Tipp 417) Nachricht zum Beitrag an Autor Nach oben

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

Worksheet_Change-Ereignis

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

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

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

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

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


UDF - benutzerdefinierte Funktion

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

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

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

=Evaluate_String(C10)

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

=Evaluate_String(C10;1)

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



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.

Textzahl zu ZahlMakro/Sub/ProzedurTipp

Kategorien: Format ▸ Text und Tabelle ▸ Zellen

(Tipp 65) Nachricht zum Beitrag an Autor Nach oben

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

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

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

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

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

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

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

Kategorien: Basics ▸ UDF und UDF ▸ Basics

(Tipp 164) Nachricht zum Beitrag an Autor Nach oben

Grundsätzliches

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

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

Function Datum_14Tage() Datum_14Tage = Date + 14 End Function

Darauf können wir einfach per Sub zugreifen:

Sub Datumstest() MsgBox Datum_14Tage End Sub

Oder in die Zelle eingetragen:

=Datum_14Tage()


Ergebnisse/Rückgaben

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


Dynamische Arrayformeln/Matrixfunktionen

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

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

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

=Monatstabelle(D1;E1)

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


Formel übergelaufen

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

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

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

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


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

Application.Volatile

Uhrzeiten - Ergebnis als DezimalzahlFormellösung

Kategorie: Datum/Zeit ▸ Zeit

(Tipp 188) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich als Ergebnis einer Uhrzeitenberechnung eine Dezimalzahl erhalten?

=(A2-A1)*24

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

Wenn Quellzelle leer dann Zielzelle leerFormellösung

Kategorie: Tabelle ▸ Datenüberprüfung

(Tipp 186) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich erreichen, daß bei einer Berechnung eine Zahl nur dann erscheint, wenn eine andere Zelle eine Zahl enthält?

Je nachdem, was den Gegebenheiten am besten entspricht:

=WENN(UND(ISTZAHL(A1);ISTZAHL(A2));A1+A2;"")

=WENN(A1="";"";WENN(A2="";"";A1+A2))

=WENN(A1+A2>0;A1+A2;"")

Zeitdifferenz in SekundenTipp

Kategorie: Datum/Zeit ▸ Zeit

(Tipp 199) Nachricht zum Beitrag an Autor Nach oben

Ich benötige die Differenz in Sekunden, um mit dieser Zahl weitere Berechnungen durchführen zu können. Die Zeitdifferenz habe ich über die Formatierung (Benutzerdefiniert [s]) in Sekunden vorliegen.

Durch die Zellformatierung hat sich an der Zahl selbst nichts geändert. In Excel entspricht der Wert 1,0 einem Tag. Um nun die Sekunden herauszukriegen muss man den Zeitwert mit 24*60*60 multiplizieren.



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.