Software für Sie.

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

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

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

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

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

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

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

Private Sub CommandButton2_Click() Unload Me End Sub

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

Sub Aufruf() UserForm1.Show End Sub

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

Zusammenfassung:

In Deiner Mappe hast Du jetzt:

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

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

Der Code zum Kopieren:

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

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.

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):

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.

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

Vor Excel 365 konnten natürlich auch einfach benutzerdefinierte Funktionen (UDF) erstellt werden. Wurden sie als Tabellenblattformeln verwendet, galt natürlich das, was generell für Formeln zählte: Es konnte ein Ergebnis pro Zelle erscheinen. Brauchte man mehr Ergebnisse, musste eine Sub() ran, die die Eintragungen in die Zellen vornahm.

Mit den Arrayformeln ab Excel 365 gab und gibt es nun weitaus mehr und vor allem vielfältige Möglichkeiten. So kann eine eigene Funktion einen kompletten Array als Ergebnis liefern - dieser wird dann als übergelaufene Formel in die Zellen neben und unterhalb der Zelle mit der Formel eingetragen.

Als Beispiel eine Funktion, die einen einfachen Monatskalender erstellt:

Function MonatsKalender(ByVal intMonat As Integer, ByVal intJahr As Integer) Dim intArr As Integer, intI As Integer, datDatum As Date Dim arrRet() MonatsKalender = "" intArr = 0 datDatum = CDate("1." & intMonat & "." & intJahr) Do intArr = intArr + 1 ReDim Preserve arrRet(1 To 3, 1 To intArr) arrRet(1, intArr) = Format(datDatum, "DD.MM.YYYY") arrRet(2, intArr) = Format(datDatum, "DDDD") arrRet(3, intArr) = IIf(Weekday(datDatum, vbMonday) = 7, "Frei!", "Arbeiten!") datDatum = datDatum + 1 Loop While Month(datDatum) = intMonat MonatsKalender = arrRet End Function

An die Funktion werden als Parameter die Zahlen für Monat und Jahr übergeben. Die Funktion erhöht dann das Datum so lange, wie der Monat des aktuellen Datums gleich dem übergebenen Monat ist. Bei jedem Datum werden Datumsangabe, Wochentag und ein Eintrag ("Frei!" oder "Arbeiten!") in einen Array eingetragen.

Da wir im Voraus nicht wissen, wie viele Tage der Monat hat, wird der Array â??unterwegsâ?? immer neu dimensioniert. Dies ist nur für die letzte Dimension möglich, also erfolgt dies auch hier so. Die einzelnen Tage stecken somit in der zweiten Dimension (1 To intArr), während die drei Angaben zum Datum in der ersten Dimension sind (1 To 3). Für Tabellenblattfunktionen sollte das Zählen mit 1 statt der 0 beginnen, wozu Option Base 1 gesetzt oder - wie hier - die Deklaration entsprechend erfolgen kann.

In die Zelle kann nun eingetragen werden, wobei in B1 die Monatszahl und in B2 die Jahreszahl steht:

=monatskalender(B1;B2)

Das Ergebnis ist sofort sichtbar: An der Zelle erscheint der Kalender. Nun können einfach in B1 bzw. B2 Monat oder Jahr geändert werden - der Kalender passt sich sofort an.

Transponieren: MTRANS oder TRANSPOSE

Allerdings wird es in vielen Fällen so sein, dass die Richtung der Ergebnisse nicht wie gewünscht ist. Wir haben im Array die Spalten redimensioniert und die Datumsangaben dort eingetragen, also erscheinen die Datumsangaben auch auf die Spalten verteilt.

Dies ist jedoch kein Problem - das Verhalten des Eintragens der Arrayelemente kann mit der integrierten Tabellenblattfunktion MTRANS() geändert werden. Dazu wird die eigene Funktion (oder bei Bedarf auch andere Funktionen) in Mtrans gesetzt:

=MTRANS(monatskalender(B1;B2))

Schon haben wir den Kalender so, wie wir ihn wahrscheinlich erwartet haben.

Eine Alternative ist, diese Tabellenblattfunktion MTRANS() gleich in der Funktion einzusetzen und den Array bereits vor der Ausgabe zu drehen:

MonatsKalender = Application.WorksheetFunction.Transpose(arrRet)

Zu sehen ist, dass im VBA-Code die englischsprachige Variante genutzt werden muss, die hier Transpose ist.

Eigentlich gar nicht.

Man kann aber mit einer Formel Bezug auf eine geschlossene Mappe nehmen; deren Ergebnis erscheint dann. Dies kann man dann mit VBA verwenden. Also kann man auch mit VBA die Formel eintragen und dann das Ergebnis abgreifen. Die Formel kann zum Schluss wieder gelöscht wreden.

Beispiel:
Im Ordner C:\Eigene Dateien befindet sich die Datei DBAdressen.xlsx. Aus dieser Datei sollen vom Blatt Allgemein die Daten aus A1 bis A25 in eine Listbox eingelesen werden, ohne diese Datei zu öffnen.

Const strBezug As String = "='C:\Eigene Dateien\[DBAdressen.xlsx]Allgemein'!$A$" 'Folgende Scheife schreibt nacheinander die Formel in B2 und fügt der Listbox 'dann das Ergebnis der Formel hinzu. Private Sub CommandButton1_Click() Dim intI As Integer ListBox1.Clear Application.DisplayAlerts = False For intI = 1 To 25 Range("B1").Formula = strBezug & intI If Not IsError(Range("B1")) Then ListBox1.AddItem Range("B1").Text Next Range("B1") = "" Application.DisplayAlerts = True End Sub

Das Ganze kann (und sollte) natürlich mit Errorhandlings verfeinert werden.

=INDEX(A1:A13;E1)

In E1 steht die Zellverknüpfung, A1:A13 ist die Liste.

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

Basis: In Spalte A (im Code also 1) stehen aufsteigend sortiert und ohne leere Zellen dazwischen die Zeiten.

Zum Umsetzen der Aufgabenstellung benötigen wir vier einzelne Routinen. Diesen müssen wir zwei Variablen zur Verfügung stellen, die deshalb ganz am Anfang des Moduls deklariert werden müssen:

Private lngZ As LongPtr Private bolC As Boolean

In der ersten Routine Start() löschen wir die Zellinhalte neben den Uhrzeiten und legen in lngZ die erste Zeile mit einer Uhrzeit fest. Dazu setzen wir bolC auf True. Das Makro, das später die Eintragungen vornimmt, wird diese Variable prüfen und den Vorgang nur fortsetzen, wenn diese auf True gesetzt ist. Anschlie�end starten wir den Vorgang mit der Sub ZeitFestLegen():

Sub Start() lngZ = 2 'erste Zeile mit Uhrzeit Range(Cells(lngZ, 2), Cells(Rows.Count, 3)).ClearContents bolC = True ZeitFestLegen End Sub

Die Sub ZeitFestLegen() hat nur folgende Aufgaben:

• prüfen, ob in Spalte A der Zeile lngZ eine Zeit steht,
• prüfen, ob bolC noch True ist,
• wenn zweimal ja: festlegen, dass bei der Zeit in Zeile lngZ etwas passiert (das Makro Eintragen ausführen) - zunächst ist das hier die Zeit in Zeile 2, weil wir die bereits in lngZ festgelegt haben:

Sub ZeitFestLegen() Dim datZeitAngabe As Date If Cells(lngZ, 1).Value = "" Then Exit Sub If bolC = False Then MsgBox "Makro wurde angehalten" Exit Sub End If datZeitAngabe = Cells(lngZ, 1).Value Application.OnTime datZeitAngabe, "Eintragen" End Sub

Nun wei� der Code, wann etwas passieren soll, nämlich bei der ersten Zeit. Mit der Sub Eintragen() legen wir fest, was passieren soll. Hier werden einfache Texte in die Nachbarzellen eingetragen.

Nach dem Eintragen wird die Zeile lngZ um 1 erhöht und wieder die Sub ZeitFestLegen aufgerufen, die nun dafür sorgt, dass das Ganze bei der nun in der nächsten Zeile gefundenen Zeit von vorn losgeht - wenn eine Zeit in dieser neuen Zelle steht und bolC immer noch True ist:

Sub Eintragen() Cells(lngZ, 2).Value = "Hallo, Spalte B" Cells(lngZ, 3).Value = "Hallo, Spalte C" lngZ = lngZ + 1 ZeitFestLegen End Sub

Das sollte schon mal funktionieren. Aber: Es gibt für den Anwender keine Möglichkeit, das vorzeitig abzubrechen. Bei der nächsten Zeit wird eingetragen und sofort die neue Zeit festgelegt.

Deshalb sollten wir eine Möglichkeit einbauen, den Wert der Variablen bolC auf False zu setzen, damit die Sub ZeitFestLegen den Vorgang abbricht. Dazu reicht das Stück Code, das man vielleicht an einen Button o. ä. binden kann:

Sub Stoppen() bolC = False End Sub

• Menüband Entwicklertools einblenden (Rechtsklick in leere Stelle des Menübands und Menüband anpassen)
• Einfügen ▸ ActiveX-Steuerelemente ▸ Kombinationsfeld
• Kombinationsfeld zeichnen
• Doppelklick auf das Feld, dadurch wird der VBA-Editor geöffnet
• folgenden Code eingeben (Zelle anpassen):

Private Sub ComboBox1_Change() Range("A1") = ComboBox1.Text End Sub[/vbacode]

• Menü Einfügen - Modul
• folgendes Makro eingeben (dient zum Füllen des Kombinationsfeldes):

Sub Fuellen() Dim intI As Integer Sheets("Tabelle1").ComboBox1.Clear For intI = 1 To Sheets.Count Sheets("Tabelle1").ComboBox1.AddItem (Sheets(intI).Name) Next End Sub

Durch den Aufruf des Makros Fuellen wird das Kombinationsfeld gefüllt; nach der Auswahl eines Blattes erscheint dessen Name im Beispiel in A1.

Eigentlich gar nicht.

Man kann aber mit einer Formel Bezug auf eine geschlossene Mappe nehmen; deren Ergebnis erscheint dann. Dies kann man dann mit VBA verwenden. Also kann man auch mit VBA die Formel eintragen und dann das Ergebnis abgreifen. Die Formel kann zum Schluss wieder gelöscht wreden.

Beispiel:
Im Ordner C:\Eigene Dateien befindet sich die Datei DBAdressen.xlsx. Aus dieser Datei sollen vom Blatt Allgemein die Daten aus A1 bis A25 in eine Listbox eingelesen werden, ohne diese Datei zu öffnen.

Const strBezug As String = "='C:\Eigene Dateien\[DBAdressen.xlsx]Allgemein'!$A$" 'Folgende Scheife schreibt nacheinander die Formel in B2 und fügt der Listbox 'dann das Ergebnis der Formel hinzu. Private Sub CommandButton1_Click() Dim intI As Integer ListBox1.Clear Application.DisplayAlerts = False For intI = 1 To 25 Range("B1").Formula = strBezug & intI If Not IsError(Range("B1")) Then ListBox1.AddItem Range("B1").Text Next Range("B1") = "" Application.DisplayAlerts = True End Sub

Das Ganze kann (und sollte) natürlich mit Errorhandlings verfeinert werden.

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: 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.

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.

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.

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.

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.

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

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

Dazu nutzen wir drei verschiedene Routinen. Da der Vorgang aber immer weiter läuft, müssen wir dafür sorgen, dass wir den notfalls abbrechen können. Dazu deklarieren wir eine Variable am Anfang des Moduls:

Private bolC As Boolean

Die erste Sub ZeitFestLegen setzt diese Variable auf True und sorgt dafür, dass es in einer Sekunde losgeht, dass dann nämlich die Routine Eintragen gestartet wird:

Sub ZeitFestLegen() Dim datZeitAngabe As Date bolC = True datZeitAngabe = Time + TimeSerial(0, 0, 1) Application.OnTime datZeitAngabe, "Eintragen" End Sub

Diese Routine Eintragen trägt die Zeit in B2 ein und ruft, wenn bolC immer noch True ist, wieder ZeitFestLegen auf, die dafür sorgt, dass sich das Ganze nach einer Sekunde wiederholt:

Sub Eintragen() Range("B1") = Time If bolC = True Then ZeitFestLegen End Sub

Und so wird die Zeit eingetragen und eingetragen und eingetragen und â?¦

Was aber, wenn wir das nicht mehr wollen, also den Vorgang anhalten möchten? Dafür nehmen wir eine kleine Sub, die die Variable bolC auf False setzt. Damit wird dann keine neue Zeit festgelegt, der Vorgang ist beendet.

Sub Notstopp() bolC = False End Sub

Die letzte Routine kann zum Beispiel an einen Button gebunden werden.

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.

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

Ereignisse

Application.OnKey "Taste", "Makro"

bewirkt, das beim Drücken einer Taste das Makro aufgerufen wird. Z. B. ruft Application.OnKey "N", "Test", beim Drücken Taste N das Makro Test auf.

Application.OnRepeat "Wiederholung: xxx", "Makro"

bewirkt, das bei dem Kommando Bearbeiten/Wiederholen das Makro ausgeführt wird. Dabei mu� man für xxx den Text eintragen, der im Menü angezeigt wird, also z. B. "Zellen einfügen"

Application.OnUnDo "Rückgängig: xxx", "Makro"

bewirkt, das bei dem Kommando Bearbeiten/Rückgängig das Makro ausgeführt wird. Dabei mu� man für xxx den Text eintragen, der im Menü angezeigt wird, also z. B. "Zellen einfügen"

Application.OnTime Zeitangabe, "Makro", Endzeit

bewirkt, das zum Zeitpunkt der Zeitangabe das Makro gestartet wird. Gibt man die Endzeit (dieser Prameter ist optional) mit an, so versucht Excel bis zum Verstreichen dieser Zeit das Makro zu starten. Dies kann z. B. der Fall sein, wenn ein Dialog geöffnet ist, oder ein anderes Makro läuft. Kann Excel das Makro nicht innerhalb dieser Zeit starten, wird darauf verzichtet.

Application.OnTime Zeitangabe, "Makro", , False

setzt man den vierten Parameter auf False, wird diese OnTime-Methode deaktiviert.

Application.OnEntry = "Makro"

wird bei der Eingabe in eine Zelle ausgeführt, nicht aber, wenn ein Makro den Zellwert verändert. Dies kann gegenüber Worksheet_Change ein Vorteil sein.

Um den (zweistelligen) Tag mit dem Punkt aus dem Datum zu extrahieren gibt es verschiedene Möglichkeiten. Beispiele:

Formel

=TEXT(TAG(A1);"TT")&"."

=LINKS(TEXT(A1;"TT.MM.JJ");3)

Format

Einfach die Formel =A1 eintragen und die Zelle benutzerdefiniert mit TT. formatieren.

UDF - benutzerdefinierte Funktion

Natürlich geht es auch mit einer UDF, zum Beispiel indem gesplittet wird. Oder mit dieser Regex:

Function Tagausdatum(ByVal strDatum) Dim Regex As Object, regMatches If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "([0-9]{1,2})\..*" Set regMatches = Regex.Execute(strDatum) Tagausdatum = regMatches(0).SubMatches(0) & "." Set Regex = Nothing End Function

In die Zelle müsste dann:

=Tagausdatum(C1)

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

Indem man einfach die Schriftfarbe auf die Hintergrundfarbe setzt. Diese Routine setzt die Schriftfarbe der festgelegten Zellen auf die Hintergrundfarbe, druckt und setzt die Schriftfarbe wieder zurück:

Sub Drucken() Dim arrZellen(), arrSammler(), intI As Integer 'Hier die Zellen eintragen, deren Inhalte nicht gedruckt werden sollen: arrZellen = Array("B5", "C10", "C15", "D20", "E4", "E6", "E12") ReDim Preserve arrSammler(2, UBound(arrZellen)) For intI = 0 To UBound(arrZellen) arrSammler(0, intI) = arrZellen(intI) arrSammler(1, intI) = Range(arrZellen(intI)).Font.Color arrSammler(2, intI) = IIf(Range(arrZellen(intI)).Interior.ColorIndex = -4142, -4142, Range(arrZellen(intI)).Interior.Color) Range(arrZellen(intI)).Font.Color = Range(arrZellen(intI)).Interior.Color Next ActiveSheet.PrintOut For intI = 0 To UBound(arrZellen) Range(arrSammler(0, intI)).Font.Color = arrSammler(1, intI) If arrSammler(2, intI) = -4142 Then Range(arrSammler(0, intI)).Interior.ColorIndex = -4142 Else Range(arrSammler(0, intI)).Interior.Color = arrSammler(2, intI) End If Next End Sub

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.