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

Der folgende Code legt eine neue Arbeitsmappe an und erstellt im Klassenmodul der Arbeitsmappe eine kleine VBA-Routine.

Sub OpenProzedurAnlegen() Dim nWB As Workbook Dim mdlWB As Object Set nWB = Workbooks.Add Set mdlWB = nWB.VBProject.VBComponents("DieseArbeitsmappe") With mdlWB.CodeModule .InsertLines 3, "Private Sub Workbook_Open()" .InsertLines 4, " Msgbox ""Bin jetzt da!""" .InsertLines 5, "End Sub" End With End Sub

Falls die Meldung Der programmatische Zugriff auf das Visual Basic-Projekt ist nicht sicher erscheint, müssen folgende Einstellungen vorgenommen werden:

In PHP kann man sehr übersichtlich mit assoziativen Arrays arbeiten, wie zum Beispiel

$MeinArray['Vorname'] = 'Manfred';

In Excel geht das auch:

Setzen Sie im VBA-Editor unter Extras ▸ Verweise ein Häkchen vor Microsoft Scripting Runtime oder verwenden Sie Late Binding wie in den folgenden Beispielen. Nun können Sie wie folgt mit assoziativen Arrays arbeiten:

Basis ist das Dictionary oder die Collection. Dabei spielen immer Paare eine Rolle: Key=Item, also ein Schlüsselname und der Eintrag dazu. In den folgenden Beispielen wird der Einfachheit halber (und weil Collection ein paar Nachteile hat) nur das Dictionary verwendet.

Im Code sind hier die einzelnen Inhalte (Keys, Items) zum besseren Verständnis hardcodiert, also fest eingetragen. An dieser Stelle können natürlich auch Variablen oder Zellen verwendet werden, zum Beispiel Spalten- oder Zeilenüberschriften und Zellen mit Daten.

Eine Ebene

Im Beispiel sind die Key=Item-Paare auf einer Ebene. Das heiÃ?t, es darf auch keine zwei gleichnamigen Keys geben, sonst wird eine Fehlermeldung ausgegeben.

Sub AssozArray_Einfach() Dim dict, varKey, varKeys, varItems Dim intI As Integer Set dict = CreateObject("Scripting.Dictionary") dict.Add "name", "Müller" dict.Add "vorname", "Johann" dict.Add "wohnort", "Berlin" MsgBox dict("vorname") & " " & dict("name") & " wohnt in " & vbNewLine & dict("wohnort") & "." 'For Each varKey In dict.keys ' MsgBox varKey & vbNewLine & dict(varKey) 'Next varKeys = dict.Keys varItems = dict.Items For intI = 0 To dict.Count - 1 MsgBox varKeys(intI) & ": " & vbNewLine & varItems(intI) Next Set dict = Nothing End Sub

Die MsgBox zeigt, wie die Syntax Dictionary(Key) verwendet werden kann, um auf die einzelnen Elemente zuzugreifen. Darunter sind zwei Schleifen. Mit ihnen wird demonstriert, wie alle Elemente des Dictionarys ausgegeben werden können.

Verschachtelungen, mehrere Ebenen

Elemente von Arrays können wiederum Arrays sein. Dies geht auch bei den assoziativen Arrays bzw. Dictionarys in VBA - auch hier kann verschachtelt werden. Dabei trifft genau das Prinzip der Paare zu, nur dass an der Stelle des Items eine weitere Sammlung (Dictionary oder Collection) ist. Hier wird ein temporäres Dictionary verwendet, das vor jeder Verschachtelung neu erzeugt und danach wieder auf Nothing gesetzt wird. Vorbelegen oder �berschreiben der Items in den einzelnen Schritten funktioniert nicht, da sonst nur auf die letzten Einträge referenziert wird.

Wir wollen im Beispiel ein Dictionary aus sechs Personen erzeugen, das sind also sechs verschiedene Keys. Diesen sechs Personen können nun Details (Nachname, Vorname, Wohnort) zugeordnet werden, müssen aber nicht. So kann der Platz für eine Person auch nur reserviert sein, ohne dass Details enthalten sind.

Die Personen insgesamt sind die Einträge in dicPersonen mit den Keys Person x. Werden Details zugeordnet, erfolgt das Speichern zunächst im temporären Dictionary dictDetails, das dann als Item der Person zugeordnet wird. Gibt es keine Details, wird als Item einfach ein String verwendet.

Sub AssozArray_Levels() Dim dicPersonen, dictDetails, varKey, varKey1 Dim strAusg As String Set dicPersonen = CreateObject("Scripting.Dictionary") Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Müller" dictDetails.Add "vorname", "Klaus" dictDetails.Add "wohnort", "Berlin" dicPersonen.Add "Person 1", dictDetails Set dictDetails = Nothing Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Beispielfrau" dictDetails.Add "vorname", "Bärbel" dictDetails.Add "wohnort", "Hamburg" dicPersonen.Add "Person 2", dictDetails Set dictDetails = Nothing Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Mustermann" dictDetails.Add "vorname", "Franz" dictDetails.Add "wohnort", "Leipzig" dicPersonen.Add "Person 3", dictDetails Set dictDetails = Nothing dicPersonen.Add "Person 4", "Nicht vergeben." Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Sonne" dictDetails.Add "vorname", "Klara" dictDetails.Add "wohnort", "München" dicPersonen.Add "Person 5", dictDetails Set dictDetails = Nothing dicPersonen.Add "Person 6", "Auch nicht vergeben." MsgBox "Testausgabe: " & vbNewLine & dicPersonen("Person 1")("wohnort") & vbNewLine & dicPersonen("Person 2")("wohnort") strAusg = "" For Each varKey In dicPersonen.keys strAusg = strAusg & vbNewLine & "Person: " & varKey & vbNewLine On Error Resume Next For Each varKey1 In dicPersonen(varKey).keys strAusg = strAusg & "key: " & varKey1 & vbTab & "item: " & dicPersonen(varKey)(varKey1) & vbNewLine Next If Err.Number = 92 Then strAusg = strAusg & vbTab & vbTab & "item: " & dicPersonen(varKey) & vbNewLine Err.Clear End If Next MsgBox strAusg Set dicPersonen = Nothing End Sub

In der MsgBox mit der Testausgabe ist zu sehen, wie die Items ausgegeben werden können. Wir haben hier also nicht nur den Key einer Ebene, sondern hier wird mit Klammerpaaren bis zur unteren Ebene gearbeitet.

Schwieriger ist es mit der kompletten Ausgabe per Schleife. Hätten wir konsequent bei jeder Person eine Verschachtelung mit den Details, würde so etwas reichen:

For Each varKey In dicPersonen.keys strAusg = strAusg & vbNewLine & "Person: " & varKey & vbNewLine For Each varKey1 In dicPersonen(varKey).keys strAusg = strAusg & "key: " & varKey1 & vbTab & "item: " & dicPersonen(varKey)(varKey1) & vbNewLine Next Next

Da aber zwischendurch unverschachtelte Items direkt auf der ersten Ebene sind (Person 4 + 6), käme es hier zu einer Fehlermeldung, dem Fehler 92. Der wird abgefangen und separat behandelt; hier wird der Eintrag direkt ausgegeben. Der Schlüssel, also der Key, ist hier ja die oberste Ebene, die Person selbst.

Dynamische Arrayformel

Zum Verdeutlichen wird hier die zweite Variante als Array ausgegeben, so dass sie als Formel in einer Zelle verwendet werden kann (funktioniert ab Excel 365).

In die Zelle kann einfach =AssozArray_Array() eingegeben werden; als Ergebnis müssten die Key-Item-Paare zu jeder Person erscheinen.

Zum praktischen Einsatz ist dies sicher eher weniger geeignet, da dann eine andere Datenstruktur erforderlich wäre. Sollte das gebraucht werden, lässt sich aber der Array, der erstellt wird, einfach anpassen.

Function AssozArray_Array() Dim dicPersonen, dictDetails, varKey, varKey1 Dim strAusg As String Dim arrRet(1 To 14, 1 To 3), lngRet As LongPtr, intS As Integer Set dicPersonen = CreateObject("Scripting.Dictionary") Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Müller" dictDetails.Add "vorname", "Klaus" dictDetails.Add "wohnort", "Berlin" dicPersonen.Add "Person 1", dictDetails Set dictDetails = Nothing Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Beispielfrau" dictDetails.Add "vorname", "Bärbel" dictDetails.Add "wohnort", "Hamburg" dicPersonen.Add "Person 2", dictDetails Set dictDetails = Nothing Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Mustermann" dictDetails.Add "vorname", "Franz" dictDetails.Add "wohnort", "Leipzig" dicPersonen.Add "Person 3", dictDetails Set dictDetails = Nothing dicPersonen.Add "Person 4", "Nicht vergeben." Set dictDetails = CreateObject("Scripting.Dictionary") dictDetails.Add "nachname", "Sonne" dictDetails.Add "vorname", "Klara" dictDetails.Add "wohnort", "München" dicPersonen.Add "Person 5", dictDetails Set dictDetails = Nothing dicPersonen.Add "Person 6", "Auch nicht vergeben." lngRet = 0 For Each varKey In dicPersonen.keys On Error Resume Next For Each varKey1 In dicPersonen(varKey).keys If varKey1 <> "" Then lngRet = lngRet + 1 arrRet(lngRet, 1) = varKey arrRet(lngRet, 2) = varKey1 arrRet(lngRet, 3) = dicPersonen(varKey)(varKey1) End If Next If Err.Number = 92 Then If dicPersonen(varKey) <> "" Then lngRet = lngRet + 1 arrRet(lngRet, 1) = varKey arrRet(lngRet, 2) = "" arrRet(lngRet, 3) = dicPersonen(varKey) End If Err.Clear End If Next Set dicPersonen = Nothing AssozArray_Array = arrRet End Function

Website-Tipp

Hier gibt es gute Erklärungen von Paul Kelly (englisch): Excel VBA Dictionary â?? A Complete Guide

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

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

Makros werden in der Regel in der Mappe gespeichert, in der sie benötigt werden. Aber es gibt auch Makros, die immer zur Verfügung stehen sollen, die also sofort beim Aufruf von Excel bereit sein sollen.

Häufig liest man hierzu die Empfehlung, man solle die Makros in der sogenannten Persönlichen Makroarbeitsmappe speichern. Diese Methode hat jedoch gravierende Nachteile. Besser ist es, wenn man sich Add-Ins erstellt und diese über den Add-Ins-Manager einblendet.

Erstellen eines Add-Ins

1. Zuerst erstellt man ganz normal seine Makros/VBA-Routinen, indem man sie aufzeichnet oder selbst schreibt (siehe hierzu auch Wo gibt man nun die Makros ein?).
2. Anschlie�end wählt man in Excel in der Mappe, in der sich die Makros befinden, den Befehl Datei - Speichern unter und gibt einen aussagekräftigen Dateinamen ein.
3. Dann wählt man im Dialogfeld ganz unten den Dateityp Microsoft Excel-Add-In (*.xlam). Daraufhin wechselt Excel automatisch in den Pfad, in dem sich standardmä�ig die Add-Ins befinden - man kann den Ordner auch wechseln. Hauptsache ist natürlich, dass man später wei�, wohin man gespeichert hat.
4. Mit einem Klick auf Speichern steht das Add-In sofort zur Verfügung. Excel speichert es und ergänzt den Namen mit der Endung .xlam.

Nun ist das Add-In gespeichert und steht zur Verfügung, arbeiten kann man damit aber noch nicht. Um das Add-In zu aktivieren, geht man wie folgt vor:

Einbinden des Add-Ins

Aktuell

1. Datei ▸ Optionen
2. Add-Ins
3. Verwalten: Excel-Add-Ins ▸ Los
4. Durchsuchen
5. Add-In-Datei suchen
6. OK
7. Prüfen, dass das Add-In in der Liste enthalten und das Häkchen gesetzt ist
8. OK

Ab Excel 2007

1. Office-Schaltfläche (oben links)
2. Excel-Optionen
3. Add-Ins
4. Verwalten: Add-Ins > Gehe zu ...
5. Durchsuchen
6. Add-In-Datei suchen
7. OK
8. Prüfen, dass das Add-In in der Liste enthalten und das Häkchen gesetzt ist
9. OK

Frühere Excel-Versionen

1. Extras
2. Add-Ins-Manager
3. Durchsuchen
4. Add-In-Datei suchen
5. OK
6. Prüfen, dass das Add-In in der Liste enthalten und das Häkchen gesetzt ist
7. OK

Fertig - nun stehen alle Makros dieses Add-Ins immer zur Verfügung, auch, wenn man Excel beendet und neu startet.

Nachträgliches Bearbeiten der Makros

Möchte man die Makros nachträglich ändern oder ergänzen, ist dies auch kein Problem. Man wechselt mit der Tastenkombination Alt + F11 in den VBA-Editor, in dem man nun (standardmä�ig) links oben im Projektexplorer den Namen des Add-Ins findet. Doppelklickt man darauf, werden die einzelnen Elemente (Tabellen, Module, usw.) sichtbar und durch einen Doppelklick auf das entsprechende Element sieht man den Code der/des Makros bereits vor sich und kann ihn bearbeiten. Nicht vergessen, zu speichern!

Add-In in normale Excel-Datei umwandeln

Normalerweise sieht man das Add-In nur im VBA-Editor, nicht aber in Excel. Manchmal möchte man aber aus dem Add-In wieder eine "normale" Mappe erstellen, damit man die Tabellen in Excel bearbeiten kann. Das ist auch kein Problem. Man gibt dazu einfach irgendwo diesen Code ein und führt ihn aus. Wechselt man nun nach Excel, hat man als Mappe das Add-In vor sich und kann wie in einer normalen Excelmappe arbeiten:

Sub Test() Workbooks("Name_des_Add-ins.xla").Isaddin = False End Sub

personl.xls

Auch mit einer Datei mit dem Namen PERSONAL.XLSB kann man Makros allgemein verfügbar machen. Dazu wählt man folgende Schritte:

1. Menü Extras - Makro - Aufzeichnen
2. Namen des Makros eingeben
3. Bei Makro speichern in wählen: Persönliche Makroarbeitsmappe
4. Schritte durchführen, die das Makro später ausführen soll
5. Aufzeichnung beenden
6. Excel beenden, Frage nach Speichern mit Ja beantworten
7. Excel neu starten

Nun ist die Datei PERSONAL.XLSB automatisch gespeichert und wird bei jedem Excelstart mitgeöffnet. Sie kann auch bearbeitet werden, indem man mit Alt + F11 in den Editor wechselt.

Auch wenn oft empfohlen wird, zentrale Makros in dieser Datei zu speichern, rate ich davon ab. Im Unterschied zu einem Add-In ist sie eine Mappe, die beim Excelstart mit aufgerufen wird und kann über das Menü Fenster - Einblenden eingeblendet werden. Daraus könnten sich Probleme ergeben, wenn mit der Workbooks-Auflistung gearbeitet wird. So wirkt sich z. B. folgender Code auch auf die PERSONAL.XLSB aus:

For each x in Workbooks ... Next

Auf ein Add-In wirkt es sich nicht aus.

Der folgende Code speichert den gesamten Text des in der Variablen eingetragenen VBA-Moduls in eine Textdatei:

Sub Modulspeichern() Dim varZiel, strKomponente As String, intI As Integer, objX As Object Dim lngDNr As LongPtr strKomponente = "Modul1" varZiel = Application.GetSaveAsFilename("test", "Textdateien (*.txt), *.txt") If varZiel = False Then Exit Sub lngDNr = FreeFile Open varZiel For Output As #lngDNr Set objX = ThisWorkbook.VBProject.VBComponents(strKomponente).CodeModule With objX For intI = 1 To .countofLines Print #lngDNr, .Lines(intI, 1) Next End With Close #lngDNr End Sub

Natürlich muss das nicht in eine Textdatei gespeichert werden; statt Print #lngDNr, .Lines(intI, 1) kann die Ausgabe auch woanders erfolgen oder in einen Array eingetragen werden.

Bitte den Hinweis auf der Startseite beachten, wenn die Meldung Der programmatische Zugriff auf das Visual-Basic-Projekt ist nicht sicher. kommt:

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().

Im VBA-Editor die UserForm anklicken, dann im Eigenschaftenfenster hinter Caption klicken und den Text eingeben, der in der Titelleiste erscheinen soll. Oder:

Private Sub CommandButton2_Click() Me.Caption = "Mein Titel" End Sub

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

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

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

Ein Kriterium

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

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

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

Mehrere Kriterien

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

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

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

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

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

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

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

Projekt-Explorer

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

Userform

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

Klassenmodule für Tabellen oder die Mappe

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

Allgemeines Modul

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

Zusammenfassung

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

Dieses kann zum Beispiel aus folgenden Elementen bestehen:

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

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

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

In die Zelle muss dann nur noch:

=Zeitraum(Jahr;KW)

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

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

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

Im VBA-Editor auf die Tabelle doppelklicken, auf der das Makro wirksam werden soll, und folgendes Makro eingeben:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Range("E2") = Cells(Target.Row, 2) End Sub