Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Abfrage bei SchließenMakro/Sub/Prozedur

Kategorie: Ereignisse ▸ Mappe

(Tipp 11) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich das Schließen der Arbeitsmappe abfangen, um darauf zu reagieren?

Im folgenden Beispiel erscheint beim Schließen der Arbeitsmappe (egal, wie) eine Meldung mit Ja- und Nein-Button.

Wird der Button Ja betätigt, wird die Mappe gespeichert und geschlossen.

Beim Button Nein wird der Vorgang mit Cancel = True abgebrochen, die Mappe wird auch nicht geschlossen.

In das Modul DieseArbeitsmappe:

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim strMsg As String strMsg = "Soll die Arbeitsmappe geschlossen werden?" Select Case MsgBox(strMsg, vbInformation + vbYesNo) Case vbYes: ThisWorkbook.Save Case vbNo: Cancel = True End Select End Sub

Abfrage, ob ein Menü in einer Menüleiste vorhanden istMakro/Sub/Prozedur

Kategorie: Menü ▸ Veraltet

(Tipp 14) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich mit VBA abfragen, ob ein bestimmtes Menü in einer bestimmten Menüleiste vorhanden ist?

Dieser Code funktioniert zwar, wird aber sicher in der Praxis keine Rolle mehr spielen.

Zur Arbeit mit Ribbons (dem Menüband, das Symbolleisten und Menüleisten ersetzt hat) empfehle ich die Website von René Holtz: RibbonX-Workshop

Zur alten Menüleiste:

Sub IstVorhanden() Dim Ctrl As CommandBarControl Dim strTxt As String Dim bolSchalter As Boolean strTxt = "E&xtras" 'das & steht für die Unterstreichung For Each Ctrl In Application.CommandBars("Worksheet Menu Bar").Controls If Ctrl.Caption = strTxt Then bolSchalter = True End If Next Ctrl If bolSchalter = True Then MsgBox "Control gefunden" Else MsgBox "Control nicht gefunden" End If End Sub

Adresse der aktiven Zelle feststellenMakro/Sub/Prozedur

Kategorie: Tabelle ▸ Zellen

(Tipp 12) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Zelladresse bzw. die Zeilen-/Spaltennummer der aktiven Zelle über Makro feststellen lassen?

Hier sind ein paar Beispiele zur Arbeit mit den Zellangaben der gerade aktiven Zelle:

Sub Zelladresse() With ActiveCell MsgBox .Address MsgBox .Address(False) MsgBox .Address(, False) MsgBox .Address(False, False) MsgBox .Row MsgBox .Column MsgBox "Zeile: " & .Row & " - Spalte:" & .Column End With End Sub

Anzahl der Einträge in einer Spalte (VBA + Formel)Makro/Sub/ProzedurFormellösung

Kategorie: Tabelle ▸ Zellen

(Tipp 136) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Anzahl der Einträge in einer Spalte feststellen?

Wenn nur die letzte Zeile festgestellt werden soll:

Sub letzteZeile() MsgBox Cells(Rows.Count, 1).End(xlUp).Row End Sub

Zellen mit Inhalt:

Sub ZellenZaehlen() Dim intI As Integer, rngZelle As Range intI = 0 For Each rngZelle In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Cells If rngZelle.Value <> "" Then intI = intI + 1 Next MsgBox intI End Sub

Formel:

=ANZAHL2(C1:C8)

VBA mit Nutzung der der Worksheetfunction:

MsgBox Application.WorksheetFunction.CountA(Range("C2:C20"))

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.

Arbeitsmappe anlegen und Workbook_Open-Prozedur schreibenMakro/Sub/ProzedurTipp

Kategorie: VBE

(Tipp 104) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich eine Arbeitsmappe anlegen und im Klassenmodul der Arbeitsmappe eine Workbook_Open-Prozedur schreiben?

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:



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

Kategorie: Arrays ▸ Arrayformeln

(Tipp 91) Nachricht zum Beitrag an Autor Nach oben

Können Ergebnisse von Arrayformeln in VBA verwendet werden?

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

Evaluate()

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

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

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

Rückgabe: Array

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

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

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

Der Aufbau des Codes wäre also:

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

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

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

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

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

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

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

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

Der Code zum Testen:

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

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

Kategorie: Arrays ▸ Arrayformeln

(Tipp 116) Nachricht zum Beitrag an Autor Nach oben

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

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

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

Verwendung in VBA

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

Application.WorksheetFunction.Unique(Array)

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

arr = Range("A2:E15")

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

arr = Application.WorksheetFunction.Unique(arr)

Weiterverarbeitung des Ergebnisarrays

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

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

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

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

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

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

intAnzahlEl = Application.WorksheetFunction.CountA(arr)

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

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

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

Tipp - Sortieren:

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

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

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

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

Kategorie: Arrays ▸ Arrayformeln

(Tipp 117) Nachricht zum Beitrag an Autor Nach oben

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

Sortieren

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

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

=SORTIEREN(A2:E15;2)

Mehr Informationen dazu gibt es bei Microsoft: SORTIEREN-Funktion

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


Sortierennach

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

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

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

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

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


VBA

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

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

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

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

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

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

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

Kategorie: Arrays ▸ Arrayformeln

(Tipp 127) Nachricht zum Beitrag an Autor Nach oben

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

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

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

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

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

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

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

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

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

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


VBA

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

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

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

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

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

Kategorie: Arrays ▸ Arrayformeln

(Tipp 130) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich eine Reihe von Zahlen mit bestimmten Abständen erhalten?

Das Erstellen von Zahlenreihen mit bestimmten Abständen ist seit Excel 365 denkbar einfach - es gibt ja die Funktion SEQUENZ(). Die folgende Formel liefert eine Liste über 15 Zeilen in einer Spalte, beginnend mit 1 und mit einem Abstand von 1:

=SEQUENZ(15;1;1;1)

Benötigen Sie eine Reihe von Datumsangaben für Ihren 14-tägigen Urlaub, können Sie das verwenden:

=SEQUENZ(14;1;"05.02.2019";1)

Natürlich müssen die Zahlen als Datum formatiert werden.

Auch Uhrzeiten sind möglich - hier ab 08:00 mit einem Abstand von einer halben Stunde (als Zeit formatieren):

=SEQUENZ(17;1;"08:00";"00:30")


Verwendung in VBA

In VBA wird die Funktion am besten mit Application.WorksheetFunction.Sequence genutzt, um den Array mit den Zahlen zu erhalten. Beispiele:

  • arr = Application.WorksheetFunction.Sequence(10, 1, "08:00", "00:30")
  • arr = Application.WorksheetFunction.Sequence(10, 1, CDate("05.02.2019"), 1)

Verwendung/Verarbeitung des Ergebnisarrays

Bei der Verarbeitung des Ergebnisarrays ist darauf zu achten, dass die einzelnen Zahlen als Zeilen vorliegen - Ubound ist also die Zeilenzahl. Wird nur eine Spalte verwendet (immer die 1 in der Funktion), ist der Ubound auch gleichzeitig die Anzahl der Zahlen. In diesem Fall muss also auf die erste Spalte jeder Zeile zugegriffen werden:

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

Hinweis: Bei Ausgaben von Datumsangaben und Zeiten müssen diese noch formatiert werden, zum Beispiel Format(arr(intI, 1), "hh:nn") für die Zeitangaben.

Etwas anders verhält es sich, wenn die Sequenz mehrspaltig erstellt wurde, zum Beispiel mit zwei Spalten:

arr = Application.WorksheetFunction.Sequence(10, 2, "08:00", "00:30")

Dann wird mit arr(intI, 1) nur die erste Spalte verarbeitet; bei einer Ausgabe wären das nur die vollen Stunden. In dem Fall muss dann auch auf die zweite Spalte zugegriffen werden:

MsgBox intI & ": " & Format(arr(intI, 1), "hh:nn") & vbTab & Format(arr(intI, 2), "hh:nn")

Das letzte Beispiel im Ganzen zum Testen:

Sub Sequenz_vba() Dim arr, intI As Integer arr = Application.WorksheetFunction.Sequence(10, 2, "08:00", "00:30") For intI = 1 To UBound(arr) MsgBox intI & ": " & Format(arr(intI, 1), "hh:nn") & vbTab & Format(arr(intI, 2), "hh:nn") Next End Sub

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

Kategorie: Arrays ▸ Arrayformeln

(Tipp 135) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich zufällige Zahlen erzeugen (und in VBA verarbeiten)?

Funktionen für Zufallszahlen bzw. Möglichkeiten, sie errechnen zu lassen, gab es ja schon vor Excel 365. Seit dieser Excelversion gibt es nun auch ZUFALLSMATRIX (), die den Umgang damit etwas vereinfacht. Statt mit Rnd() und * Obergrenze und + Obergrenze zu rechnen, notiert man diese Anforderungen einfach als Parameter in der Funktion selbst. Eine Beschreibung im Detail gibt es bei Microsoft: ZUFALLSMATRIX-Funktion.

Bei dieser Funktion ist man nicht nur auf Zahlen beschränkt, die als solche aussehen, sondern es können auch zufällige Datumsangaben und Uhrzeiten erstellt werden:

  • ZUFALLSMATRIX(15;1;"1.1.2020";"15.06.2020";WAHR)
  • ZUFALLSMATRIX(15;1;"08:00";"16:00")

Zwei Dinge müssen hier jedoch beachtet werden:

Der letzte Parameter [ganze_zahl] muss bei Uhrzeiten weggelassen oder auf FALSCH bzw. 0 gesetzt werden, da die reinen Zahlen bei Uhrzeiten immer zwischen 0 und 1 sind.

Wie bei allen Arrayformeln nimmt Excel dabei keine Formate mit. Datumsangaben und Zeiten müssen also i. d. R. im Ergebnis neu formatiert werden. Hilfreich kann da der Pinsel Format übertragen sein.

Das Sortieren der Zahlen geht einfach, indem die Funktion SORTIEREN() verwendet wird:

=SORTIEREN(ZUFALLSMATRIX(15;1;"1.1.2020";"15.06.2020";1))


Verwendung in VBA

In VBA wird ein Array durch Nutzung der Funktion so erzeugt, wie es bei der reinen Formel ist. Beispiele:

  • arr = Application.WorksheetFunction.RandArray(10, 1, CDate("1.1.2020"), CDate("15.06.2020"))
  • arr = Application.WorksheetFunction.RandArray(10, 1, CDate("08:00"), CDate("16:00"))

Die Verarbeitung des Ergebnisarrays erfolgt so, wie es bei SEQUENZ() beschrieben ist. Die einzelnen Daten liegen also in Zeilen vor; bei einer Spalte ist dies der Ubound. Also muss auch auf die erste Spalte zugegriffen werden:

For intI = 1 To UBound(arr) MsgBox intI & ": " & Format(arr(intI, 1), "DD:MM.YYYY") Next

Im Beispiel ist auch schon zu sehen, dass etwaige Ausgaben formatiert werden müssen.

Werden mehrere Spalten erzeugt, muss das beim Zugreifen auf die Arrayelemente beachtet werden:

For intI = 1 To UBound(arr) MsgBox intI & ": " & Format(arr(intI, 1), "hh:nn") & vbTab & Format(arr(intI, 2), "hh:nn") Next

Wie bei der Formellösung kann der Array auch sortiert werden:

arr = Application.WorksheetFunction.Sort(arr)

Auch hier muss natürlich beachtet werden, dass die Daten zweispaltig sein können, wenn der zweite Parameter in der Funktion entsprechend gesetzt ist. Nach der zweiten Spalte würde dann so sortiert:

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

Ob das Sinn macht, ist allerdings eine andere Frage.

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



Assoziative Arrays in VBAMakro/Sub/ProzedurUDF - benutzerdefinierte FunktionArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Assoziativ

(Tipp 573) Nachricht zum Beitrag an Autor Nach oben

Kann man in Excel auch mit assoziativen Arrays arbeiten?

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

Auf einer Tabelle jede Seite beim Druck beeinflussen (Seitenumbrüche)Makro/Sub/Prozedur

Kategorie: Drucken/Seite

(Tipp 161) Nachricht zum Beitrag an Autor Nach oben

Wie kann man beim Drucken jede Seite beeinflussen?

Hier ist eine flexible Version, die es ermöglicht, beim Druck jede Seite einer Tabelle zu beeinflussen. Einzig und allein die Zelle muß immer die gleiche sein, d. h. z. B., die achte von oben und siebte von links. Mit diesem Code ließen sich dann auch die Fragen beantworten, wie man einen Übertrag auf die nächste Seite bringt.

Sub Druckseiteneinrichten() Dim intI As Integer, intJ As Integer, intK As Integer Dim intSeitenZaehler As Integer Dim intWievielteZeile As Integer, intWievielteSpalte As Integer ReDim arrSammler(1) 'Standardmäßig wird bei jeder Seite der Inhalt der linken oberen Zelle 'zurückgegeben. Soll das eine andere sein, müssen die folgenden Werte 'geändert werden. 'Soll z. B. auf der ersten Seite der Inhalt von B4 zurückgegeben werden, 'muß die erste Variable auf 3, die zweite auf 1 gesetzt werden '(vierte Zeile von oben, zweite Spalte von links). intWievielteZeile = 0 intWievielteSpalte = 0 ActiveWindow.View = xlPageBreakPreview ActiveSheet.ResetAllPageBreaks arrSammler(0) = 1 For intI = 1 To ActiveSheet.HPageBreaks.Count ReDim Preserve arrSammler(intI) arrSammler(intI) = ActiveSheet.HPageBreaks.Item(intI).Location.Row Next intSeitenZaehler = 0 For intJ = 0 To intI - 1 intSeitenZaehler = intSeitenZaehler + 1 Ausfuehren CInt(arrSammler(intJ)) + intWievielteZeile, _ 1 + intWievielteSpalte, _ intSeitenZaehler Next For intJ = 1 To ActiveSheet.VPageBreaks.Count intSeitenZaehler = intSeitenZaehler + 1 Ausfuehren CInt(arrSammler(0)) + intWievielteZeile, _ ActiveSheet.VPageBreaks.Item(intJ).Location.Column + intWievielteSpalte, _ intSeitenZaehler For intK = 1 To intI - 1 intSeitenZaehler = intSeitenZaehler + 1 Ausfuehren CInt(arrSammler(intK)) + intWievielteZeile, _ ActiveSheet.VPageBreaks.Item(intJ).Location.Column + intWievielteSpalte, _ intSeitenZaehler Next Next ActiveWindow.View = xlNormalView End Sub Sub Ausfuehren(Zeile As Integer, Spalte As Integer, SeitenZaehler As Integer) Dim strAnzeige$ strAnzeige = Cells(Zeile, Spalte) & " Seite: " & SeitenZaehler MsgBox strAnzeige ActiveSheet.PageSetup.LeftHeader = strAnzeige ActiveSheet.PrintOut from:=SeitenZaehler, To:=SeitenZaehler End Sub

Bei Uhrzeiten in Spalte A Makro ausführen (OnTime)Makro/Sub/Prozedur

Kategorien: Ereignisse ▸ Zeit und Tabelle ▸ Zellen

(Tipp 125) Nachricht zum Beitrag an Autor Nach oben

In Spalte A stehen Uhrzeiten. Zu jeder dieser Zeit soll ein Text in die Nachbarzellen eingelesen werden. Wie lautet das Makro?

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

Blatt einfügenMakro/Sub/Prozedur

Kategorie: Mappe ▸ Tabellen

(Tipp 110) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich ein neues Blatt einfügen? Dabei soll überprüft werden, ob ein Blatt mit dem Namen schon existiert und der linke und rechte Seitenrand auf einen Zentimeter festgelegt werden.

In eine Inputbox kann der Name des zu erzeugenden Blattes eingetragen werden. Anschließend wird mit einer Schleife geprüft, ob der Name schon vergeben ist. Wenn nicht, wird das Blatt eingefügt und die Seitenränder werden festgelegt.

Sub Blatterstellen() Dim wksBlatt As Object Dim strNeu As String strNeu = InputBox("Bitte Namen des neuen Arbeitsblattes eingeben:") For Each wksBlatt In ActiveWorkbook.Sheets If wksBlatt.Name = strNeu Then MsgBox "Blattname existiert bereits!", vbOKOnly + vbExclamation, "Blatt hinzufügen" Exit Sub End If Next Sheets.Add ActiveSheet.Name = strNeu With Sheets(strNeu).PageSetup .LeftMargin = Application.CentimetersToPoints(1) .RightMargin = Application.CentimetersToPoints(1) End With End Sub

Eine andere Möglichkeit, bei der auch gleich festgelegt wird, wo das Blatt eingefügt werden soll:

Sub Blatterstellen1() Dim wksBlatt As Object Dim strNeu As String strNeu = InputBox("Bitte Namen des neuen Arbeitsblattes eingeben:") For Each wksBlatt In ActiveWorkbook.Sheets If wksBlatt.Name = strNeu Then MsgBox "Blattname existiert bereits!", vbOKOnly + vbExclamation, "Blatt hinzufügen" Exit Sub End If Next Set wksBlatt = Sheets.Add(before:=Sheets(1)) wksBlatt.Name = strNeu With wksBlatt.PageSetup .LeftMargin = Application.CentimetersToPoints(1) .RightMargin = Application.CentimetersToPoints(1) End With End Sub

Blatt in eine andere Mappe kopierenMakro/Sub/Prozedur

Kategorien: Dateien und Ordner ▸ Dateioperation und Mappe ▸ Tabellen

(Tipp 112) Nachricht zum Beitrag an Autor Nach oben

Auf dem Blatt "Huber" steht in A10 der Pfad und der Name der Datei "Ablage.xls". In diese Datei möchte ich das Blatt "Huber" kopieren. Das Blatt soll in der Datei als letztes erscheinen; diese soll anschließend geschlossen werden.

Eigentlich wäre es mit diesem (sicher selbst erklärendem) Code getan:

Dim strPfad As String, strDatei As String strPfad = ThisWorkbook.Sheets("Huber").Range("A10") strDatei = Dir(strPfad) Workbooks.Open Filename:=strPfad ThisWorkbook.Sheets("Huber").Copy after:=Workbooks(strDatei).Sheets(Sheets.Count) Workbooks(strDatei).Close True

Wenn es da nicht ein paar Fehleranfälligkeiten geben würde:

  • Existiert die Datei überhaupt?
  • Ist die Zieldatei bereits geöffnet?
  • Gibt es in der Zieldatei bereits ein Blatt mit dem Namen?

Wenigstens diese Fragen sollten im Code noch geklärt werden, damit es keine Fehlermeldungen durch Excel und Abbrüche gibt. Anhand der Variablennamen sollte deutlich werden, was im Code passiert:

Sub Blatt_kopieren() Dim strPfad As String, strDatei As String Dim bolOffen As Boolean Dim objMappe As Object, objBlatt As Object strPfad = ThisWorkbook.Sheets("Huber").Range("A10") strDatei = Dir(strPfad) If strDatei = "" Then MsgBox "Datei existiert nicht" Exit Sub End If bolOffen = False For Each objMappe In Workbooks If objMappe.Name = strDatei Then bolOffen = True Exit For End If Next If bolOffen = False Then Workbooks.Open Filename:=strPfad For Each objBlatt In Workbooks(strDatei).Sheets If objBlatt.Name = "Huber" Then MsgBox "In der Zieldatei existiert bereits ein Blatt mit dem Namen 'Huber'.", vbOKOnly + vbExclamation, "Blatt existiert" If bolOffen = False Then Workbooks(strDatei).Close False Exit Sub End If Next ThisWorkbook.Sheets("Huber").Copy after:=Workbooks(strDatei).Sheets(Sheets.Count) Workbooks(strDatei).Close True End Sub

Blätter sortierenMakro/Sub/Prozedur

Kategorie: Mappe ▸ Tabellen

(Tipp 108) Nachricht zum Beitrag an Autor Nach oben

Die Tabellenblätter einer Mappe sind mit Tab1, Tab2, ..., Tab50, Tab51, usw. durchnummeriert. Wie kann ich sie sortieren?

Zum Sortieren der Blätter gibt es verschiedene Möglichkeiten, was auch etwas von den Rahmenbedingungen abhängig ist. Was soll zum Beispiel mit Blättern passieren, deren Namen nicht der Syntax der zu sortierenden Blattnamen entsprechen? Hier sind drei Beispiele, die ggf. noch angepasst werden müssen.

In dem Fall werden unterwegs (also nicht am Anfang) Blätter mit Namen, die nicht der Syntax entsprechen, nach hinten verschoben - ansonsten wird so lange sortiert, bis die Zahl im folgenden Blattnamen nicht mehr gößer ist:

Sub BlaetterSortieren() Dim bolSortiert As Boolean Dim intBlatt As Integer, intBlatt1 As Integer Dim varAkt, varNaechst bolSortiert = False Do While bolSortiert = False bolSortiert = True For intBlatt = 1 To Sheets.Count varAkt = Replace(Sheets(intBlatt).Name, "Tab", "") If IsNumeric(varAkt) Then For intBlatt1 = intBlatt To Sheets.Count varNaechst = Replace(Sheets(intBlatt1).Name, "Tab", "") If IsNumeric(varNaechst) Then If varNaechst * 1 < varAkt * 1 Then Sheets(intBlatt1).Move Before:=Sheets(intBlatt) bolSortiert = False End If Else Sheets(intBlatt1).Move after:=Sheets(Sheets.Count) End If Next End If Next Loop End Sub

Ein Beispiel mit Sprungmarken, Reihenfolge Tab1, Tab2, Tab11:

Sub Blattsort() Dim intAnzahl As Integer, intN As Integer, intM As Integer, intI As Integer, intZahlM As Integer, intZahlN As Integer Dim WS As Worksheet intAnzahl = ActiveWorkbook.Worksheets.Count For intM = 1 To intAnzahl For intN = intM To intAnzahl On Error Resume Next For intI = 1 To Len(Worksheets(intN).Name) If IsNumeric(Right(Worksheets(intN).Name, intI)) = False Then intI = intI - 1 If intI = 0 Then GoTo TEXT intZahlN = Right(Worksheets(intN).Name, intI) Exit For End If Next intI For intI = 1 To Len(Worksheets(intM).Name) If IsNumeric(Right(Worksheets(intM).Name, intI)) = False Then intI = intI - 1 If intI = 0 Then GoTo TEXT intZahlM = Right(Worksheets(intM).Name, intI) Exit For End If Next intI If CInt(intZahlN) < CInt(intZahlM) Then Worksheets(intN).Move Before:=Worksheets(intM) GoTo NAECHSTE TEXT: If Worksheets(intN).Name < Worksheets(intM).Name Then Worksheets(intN).Move Before:=Worksheets(intM) NAECHSTE: Next intN Next intM MsgBox "Anzahl der Tabellen: " & intAnzahl End Sub

Ein kurzes Beispiel, aber die Reihenfolge ist Tab1, Tab11, Tab2:

Sub Blattsort1() Dim intX As Integer Dim bolY As Boolean Do bolY = True For intX = 1 To Sheets.Count - 1 If Sheets(intX).Name > Sheets(intX + 1).Name Then Sheets(intX + 1).Move Before:=Sheets(intX) bolY = False End If Next intX Loop Until bolY = True End Sub

Computernamen auslesenMakro/Sub/ProzedurUDF - benutzerdefinierte FunktionTipp

Kategorie: System ▸ Windows

(Tipp 569) Nachricht zum Beitrag an Autor Nach oben

Wie kann man den Namen des Computers, also des Systems, auf dem die Anwendung läuft, auslesen?

Die verschiedenen Deklarationen sind wichtig, um das Funktionieren sowohl in der 64-Bit-Version des Microsoft Office (also nicht von Windows) als auch in der 32-Bit-Version zu gewährleisten.

#If VBA7 Then Private Declare PtrSafe Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As LongPtr) As LongPtr #Else Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long #End If Sub ComputerName() Dim lngTemp As LongPtr, strPCName As String strPCName = Space(256) lngTemp = GetComputerName(strPCName, Len(strPCName)) MsgBox strPCName End Sub

Das kann man natürlich auch als UDF erstellen:

Function ComputerName1() Dim lngTemp As LongPtr, strPCName As String strPCName = Space(256) lngTemp = GetComputerName(strPCName, Len(strPCName)) ComputerName1 = strPCName End Function

In die Zelle wird einfach =computername1() eingetragen.



Datei öffnen, Datum im NamenMakro/Sub/Prozedur

Kategorien: Dateien und Ordner ▸ Dateioperation und Format ▸ Datum

(Tipp 21) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich Dateien, die Datumsnamen im Format JJJJMMDD (z. B. 19980518.XLSX) haben, über VBA öffnen, wenn das jeweilige Datum über ein InputBox abgefragt wird?

Im folgenden Beispiel muss natürlich noch der Ordner/Pfad beachtet und ggf. dem Dateinamen vorangestellt werden. Der kann in einer Zelle stehen oder im Code ergänzt werden.

Sub DatumEingeben() Dim varDatum varDatum = InputBox("Bitte Datum eingeben:") If Not IsDate(varDatum) Then MsgBox "Kein Datum!" Else varDatum = Format(varDatum, "yyyymmdd") & ".xls" End If Workbooks.Open varDatum End Sub

Datei mit fortlaufender Nummer speichernMakro/Sub/Prozedur

Kategorien: Dateien und Ordner ▸ Dateioperation und Dateien und Ordner ▸ Dokumenteigenschaften

(Tipp 19) Nachricht zum Beitrag an Autor Nach oben

Wie kann man in einer Datei eine fortlaufende Nummer speichern?

In einer Zelle

Die Nummer in eine Zelle schreiben, die auch ausgeblendet werden kann. Dann bei jedem Speichern die Nummer mit z. B. Range("A1") = Range("A1") + 1 erhöhen.


In den Dokumenteigenschaften

Die CustomDocumentProperties der Datei können auch mit VBA-Code verwendet werden. Dann wird die Nummer versteckt in dieser Datei gespeichert:

Dazu einmalig die Eigenschaft mit der folgenden Zeile erstellen:

ThisWorkbook.CustomDocumentProperties.Add Name:="lfdNr", LinkToContent:=False, Type:=msoPropertyTypeNumber, Value:=0

Anzeigen kann man den Wert, der neu 0 ist, mit der Zeile:

MsgBox ThisWorkbook.CustomDocumentProperties("lfdNr").Value

Erhöht wird der der Wert mit

ThisWorkbook.CustomDocumentProperties("lfdNr").Value = ThisWorkbook.CustomDocumentProperties("lfdNr").Value + 1

Nach dem Erhöhen des Wertes nicht vergessen, die Datei zu speichern. Auch wenn diese Eigenschaften nicht sichtbar sind, sind sie doch in der Datei enthalten.


Separate Textdatei

Oder die Nummer in eine Datei auslagern, zum Beispiel so:

Sub lfdNr() Dim lngDNr As Long, intNr As Integer Dim strDName As String, strZielordner As String, strDateiname As String strZielordner = ThisWorkbook.Path & "\" 'Hier den Pfad verändern strDateiname = "Excel_lfdNr" 'Hier den Dateinamen verändern strDName = strZielordner & strDateiname & ".ini" intNr = 0 lngDNr = FreeFile If Dir(strDName) <> "" Then Open strDName For Input As #lngDNr Input #lngDNr, intNr Close #lngDNr End If intNr = intNr + 1 lngDNr = FreeFile Open strDName For Output As #lngDNr Print #lngDNr, intNr; Close #lngDNr ActiveCell.Value = intNr End Sub

Datei per Makro öffnenMakro/Sub/Prozedur

Kategorie: Dateien und Ordner ▸ Dateioperation

(Tipp 17) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich mit einem Makro eine Datei aufrufen?

Damit wird die Datei einfach geöffnet, wenn sie existiert:

Workbooks.Open FileName:="C:\Eigene Dateien\Test.xlsx"

Sicherheitshalber sollte vorher geprüft werden, ob die Datei existiert, um Fehlermeldungen zu vermeiden:

Dim strPfad As String strPfad = "C:\Temp\Test.xlsx" If Dir(strPfad) = "" Then MsgBox "Die Datei " & vbNewLine & strPfad & vbNewLine & "existiert nicht.", vbOKOnly + vbCritical, "Fehler" Else Workbooks.Open Filename:=strPfad End If

Ggf. sollte noch geprüft werden, ob eine Datei mit dem Namen bereits offen ist. Dazu kann man den Fehler abfangen oder vorher mit For Each objMappe in Workbooks prüfen, ob objMappe.name wie der Name der aufzurufenden Datei ist.

Dateiname in Zelle

Wenn der Dateiname in einer Zelle (hier A1) steht, kann so vorgegangen werden:

Sub Aufruf() Workbooks.Open FileName:=Range("A1") End Sub

Dabei darf natürlich nicht der Ordnerpfad vergessen werden, in dem sich diese Datei befindet. Der kann komplett mit in A1 stehen oder noch vorangestellt werden.

Auch hier sollte vorher noch mit Dir() geprüft werden, ob die Datei überhaupt existiert.

Datei suchenMakro/Sub/Prozedur

Kategorien: Dateien und Ordner ▸ Ordner und Dateien und Ordner ▸ Dateien

(Tipp 36) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich auf der Festplatte nach einer Datei suchen?

Vorsicht: Die Laufzeit dieser Schleifen kann sehr lang sein. Es ist deshalb mehr als ratsam, die zu durchsuchenden Ordner so stark wie möglich einzugrenzen.

Erste Möglichkeit:

Dim strPfad As String, bolWeiter As Boolean 'Aufruf Sub Suchen() strPfad = "": bolWeiter = True: Dateisuche "C:\", "test.txt" Range("A1") = strPfad End Sub Function Dateisuche(strLaufwerk, strDatei) Dim strTemp As String, strWdhlg As String On Error Resume Next strLaufwerk = strLaufwerk & IIf(Right(strLaufwerk, 1) <> "\", "\", "") strTemp = Dir(strLaufwerk & strDatei) Do While Len(strTemp) strPfad = strLaufwerk & strTemp If UCase(strTemp) = UCase(strDatei) Then Dateisuche = strPfad bolWeiter = False: Exit Function End If strTemp = Dir() Loop strTemp = Dir(strLaufwerk, vbDirectory) Do While Len(strTemp) If (strTemp <> ".") And (strTemp <> "..") Then If (GetAttr(strLaufwerk & strTemp) And vbDirectory) = vbDirectory Then If bolWeiter = True Then Dateisuche strLaufwerk & strTemp, strDatei Else Exit Function strWdhlg = Dir(strLaufwerk, vbDirectory) Do While strWdhlg <> strTemp: strWdhlg = Dir(): Loop End If End If strTemp = Dir() Loop End Function

Zweite Möglichkeit:

Sub suchen1() Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") Dateisuche fso.GetFolder("C:"), "test.txt" Set fso = Nothing End Sub Sub Dateisuche(objErstOrdner As Object, strDatei As String) Dim objDatei As Object, objOrdner As Object, sf As Object For Each objDatei In objErstOrdner.Files If LCase(objDatei.Name) = strDatei Then MsgBox objDatei.Path End If Next For Each objOrdner In objErstOrdner.SubFolders Dateisuche objOrdner, strDatei Next End Sub

Dateiname aus Pfad (Dir(), Regulärer Ausdruck, Arrayformel)Makro/Sub/ProzedurUDF - benutzerdefinierte FunktionFormellösungArrayfunktion/Matrixfunktion

Kategorien: Dateien und Ordner ▸ Dateien und Stringoperationen ▸ Teile

(Tipp 24) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich aus einem Pfad (z. B. bei GetOpenFileName) den Dateinamen (bzw. Ordner) filtern?

Natürlich kann man den gesamten Pfad am Backslash splitten oder andere Stringoperationen anwenden. Am einfachsten ist es aber sicher, wenn man sich mit Dir() den Dateinamen zurückgeben lässt - das geht auch mit allen Dateien, nicht nur mit Exceldateien.

Dir()

Rein für den Dateinamen wäre dies eine einfache Möglichkeit:

Sub DateinamenExtrahieren() Dim varDName varDName = Application.GetOpenFilename If varDName = False Then MsgBox "Nichts gewählt." Else varDName = Dir(varDName) MsgBox varDName End If End Sub

Für alle Angaben aus dem Pfad, also Ordner und Datei, könnte folgende Variante genutzt werden:

Sub DateiPfad() Dim strGesamt As String, strDatei As String, strOrdner As String strGesamt = Application.GetOpenFilename strDatei = Dir(strGesamt) strOrdner = Left(strGesamt, Len(strGesamt) - Len(Dir(strGesamt))) MsgBox strDatei & vbNewLine & strOrdner & vbNewLine & strGesamt End Sub

Wenn davon ausgegangen werden kann, dass die Zeichenfolge des Dateinamens einmalig im Pfad ist, kann auch einfach ersetzt werden:

Sub DateiAusPfad2() Dim varPfad, strOrdner As String, strDatei As String varPfad = Application.GetOpenFilename If varPfad <> False Then strDatei = Dir(varPfad) strOrdner = Replace(varPfad, strDatei, "") MsgBox strDatei & vbNewLine & strOrdner End If End Sub


Regulärer Ausdruck

Noch eine Variante für die Freunde regulärer Ausdrücke:

Sub DateiAusPfad3() Dim varDName, Regex As Object, regMatches, regMatch varDName = Application.GetOpenFilename If varDName = False Then MsgBox "Nichts gewählt." Else If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "^(.+[\\\/])(.*)$" Set regMatches = Regex.Execute(varDName) MsgBox regMatches(0).SubMatches(1) Set Regex = Nothing End If End Sub

Der Schrägstrich wurde aufgenommen, weil Pfade in Onedrive gespeicherter Dateien mit Schrägstrich geliefert werden.

Sollen Ordner und Dateiname zurückgegeben werden, wäre dies möglich:

Sub DateiAusPfad4() Dim varPfad, strOrdner As String, strDatei As String Dim Regex As Object, regMatches, regMatch varPfad = Application.GetOpenFilename If varPfad <> False Then If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "^(.+[\\\/])(.*)$" Set regMatches = Regex.Execute(varPfad) strOrdner = regMatches(0).SubMatches(0) strDatei = regMatches(0).SubMatches(1) Set Regex = Nothing MsgBox strDatei & vbNewLine & vbNewLine & strOrdner End If End Sub

Die integrierte Funktion =ZELLE("Dateiname";A1) liefert den kompletten Pfad bis zum Tabellenblatt. Der Dateiname ist dabei in eckige Klammern eingeschlossen: Pfad[Dateiname]Blattname. Mit einem regulären Ausdruck können die einzelnen Bestandteile ausgegeben werden (ggf. noch Fehlerbehandlung einbauen):

Sub DateiAusZellFunktion() Dim strPfad, strOrdner As String, strDatei As String, strBlatt As String Dim Regex As Object, regMatches, regMatch strPfad = Evaluate("=cell(""filename"",A1)") If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "^(.*)\[(.*)\](.*)$" Set regMatches = Regex.Execute(strPfad) strOrdner = regMatches(0).SubMatches(0) strDatei = regMatches(0).SubMatches(1) strBlatt = regMatches(0).SubMatches(2) Set Regex = Nothing MsgBox strBlatt & vbNewLine & strDatei & vbNewLine & strOrdner End Sub


Dynamische Matrixformel (Arrayformel) und verschütteter Array

Per benutzerdefinierter Funktion können seit Excel 365 auch die einzelnen Ordner bzw. Bestandteile eines Pfades in Zellen ausgegeben werden. Dazu diese Funktion als Beispiel:

Function PfadDetails(ByVal strPfad As String) PfadDetails = "" If strPfad <> "" Then Select Case True Case InStr(1, strPfad, "\") > 0: PfadDetails = Split(Replace(strPfad, "\\", "\"), "\") Case InStr(1, strPfad, "/") > 0: PfadDetails = Split(Replace(strPfad, "//", "/"), "/") End Select End If End Function

In die Zelle kommt dann diese Formel:

=PfadDetails(A5)

Wenn wie hier im Beispiel in A5 ein Pfad steht, werden an der Zelle mit der Formel die einzelnen Elemente des Pfades ausgegeben. Das letzte Element sollte bei einem kompletten Pfad zu einer Datei der Dateiname sein.

Das Beispiel mit der Funktion =ZELLE("Dateiname";A1) kann auch als Arrayformel verwendet werden:

Function DateiAusZellFunktion(strFktPfad) Dim arrTemp(1 To 3) Dim Regex As Object, regMatches, regMatch DateiAusZellFunktion = "" If strFktPfad <> "" Then If Regex Is Nothing Then Set Regex = CreateObject("VBScript.RegExp") Regex.Pattern = "^(.*)\[(.*)\](.*)$" Set regMatches = Regex.Execute(strFktPfad) If regMatches.Count = 1 Then arrTemp(1) = regMatches(0).SubMatches(2) arrTemp(2) = regMatches(0).SubMatches(1) arrTemp(3) = regMatches(0).SubMatches(0) DateiAusZellFunktion = arrTemp End If Set Regex = Nothing End If End Function

In die Zelle kommt dann =DateiAusZellFunktion(ZELLE("Dateiname";A1)) und in ihr sowie den Nachbarzellen werden Blattname, Dateiname und Ordnerpfad erscheinen.


Formeln/integrierte Funktionen

Den aktuellen Ordner gibt diese Funktion zurück:

=INFO("Verzeichnis")


In anderen Sprachen geht übrigens auch einfach Basename(Pfad).

Dateiupload mit Excel und PHPMakro/Sub/Prozedur

Kategorie: Netz ▸ Serverkommunikation

(Tipp 599) Nachricht zum Beitrag an Autor Nach oben

Wie können mit VBA Dateien auf einen Server im Internet geladen werden?

Voraussetzung für dieses Beispiel ist, dass auf dem Server ein eigenes PHP-Script erstellt werden kann, das dann natürlich auch eine Adresse (URL) hat.

In diesem Beispiel sind die hochzuladenden Dateien als Konstanten angegeben:

Private Const strDatei1 As String = "C:\…\test1.jpg" Private Const strDatei2 As String = "C:\…\test2.jpg" Private Const strDatei3 As String = "C:\…\test3.jpg"

Natürlich können die - wie ebenfalls die Script-URL - dem Script auch dynamisch übergeben werden, zum Beispiel als Array. Anpassungen müssen nur an der Sub DateiUpload() vorgenommen werden, der Rest kann so bleiben.

Dies ist der VBA-Code:

Private Const strURL As String = "https://…Ihre URL…/…Ihre PHP-Datei….php" Private Const strDatei1 As String = "C:\…\test1.jpg" Private Const strDatei2 As String = "C:\…\test2.jpg" Private Const strDatei3 As String = "C:\…\test3.jpg" Sub DateiUpload() Dim strPostDaten As String, strBoundary As String strBoundary = "---------------------------7da24f2e50046" strPostDaten = "" strPostDaten = strPostDaten & Header_Datei(strDatei1, strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei2, strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei3, strBoundary) strPostDaten = strPostDaten & "--" & strBoundary & "--" With CreateObject("Microsoft.XMLHTTP") .Open "POST", strURL, False .SetRequestHeader "Content-Type", "multipart/form-data; boundary=" & strBoundary .Send SendDaten(strPostDaten) MsgBox .ResponseText End With End Sub Function Header_Datei(strDateiPfad As String, strBoundary As String) Dim strDateiKonv As String, strDateiName As String Header_Datei = "" If strDateiPfad <> "" Then strDateiName = DateiName(strDateiPfad) strDateiKonv = DateiKonv(strDateiPfad) Header_Datei = "--" & strBoundary & vbCrLf & _ "Content-Disposition: form-data; name=""file[]""; filename=""" & strDateiName & """" & vbCrLf & _ "Content-Type: application/octet-stream" & vbCrLf & vbCrLf & strDateiKonv & vbCrLf End If End Function Function DateiName(strpfad As String) DateiName = "" If strpfad <> "" Then DateiName = Mid$(strpfad, InStrRev(strpfad, "\") + 1) End Function Function DateiKonv(strpfad As String) Dim lngDNr As LongPtr, bytBuffer() As Byte DateiKonv = "" lngDNr = FreeFile Open strpfad For Binary Access Read As lngDNr If LOF(lngDNr) > 0 Then ReDim bytBuffer(0 To LOF(lngDNr) - 1) As Byte Get lngDNr, , bytBuffer DateiKonv = StrConv(bytBuffer, vbUnicode) End If Close lngDNr End Function Private Function SendDaten(strText As String) As Byte() SendDaten = StrConv(strText, vbFromUnicode) End Function

Der VBA-Code bereitet die Dateien für den Upload vor, erstellt die richtige Syntax und sendet sie an das PHP-Script:

// Verzeichnis, in dem die Anhänge zwischengespeichert werden: $uploadverz = 'uploadtest/'; // Vorsicht: Ggf. prüfen, ob vorhanden und leer. // File[]-Array sinnvoll sortieren: $dateiarray = array(); foreach($_FILES['file'] as $k1 => $v1) { foreach($v1 as $k2 => $v2) { $dateiarray[$k2][$k1] = $v2; } } // Anhänge speichern, Daten für Versand aufbereiten $a = 0; $ausg = ""; foreach ($dateiarray as $file) { // Ggf. prüfen, ob Dateiname schon im Verzeichnis vorhanden ist und // ggf. umbenennen: $datname = basename($file['name']); // Vorbereitung, falls Dateiname im Verzeichnis existiert: $t = explode(".", $datname); // an Dateiendung splitten $endung = $t[count($t)-1]; $datname_pref = substr($datname, 0, strlen($datname) - strlen($endung) - 1); $uploadpfad = $uploadverz . $datname; // Prüfen, ob Datei existiert: $n = 0; while (file_exists($uploadpfad)) { $n++; $uploadpfad = $uploadverz . $datname_pref . "(" . $n . ")." . $endung; } if (move_uploaded_file($file['tmp_name'], $uploadpfad)) { $a++; $ausg .= $uploadpfad . "\n"; } } // Meldung, die in VBA in der MsgBox als .ResponseText erscheint: echo $a . " Datei(en) erfolgreich hochgeladen." . "\n\n" . $ausg;

Daten per POST an PHP-Script, Response empfangenMakro/Sub/Prozedur

Kategorie: Netz ▸ Serverkommunikation

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

Kommunikation mit PHP
Wie kann ich Daten an ein PHP-Script senden oder Daten von dort empfangen?

Dazu gibt es verschiedene Möglichkeiten. Manche haben Nachteile, zum Beispiel Probleme mit UTF-8 oder dass gesendete Daten in den Server-Logs erscheinen.

Empfehlenswert ist das MSXML2.XMLHTTP-Objekt, mit dem per POST Formulardaten ganz einfach gesendet und Antworten empfangen werden können. Die Daten werden einfach als key=value-Kombinationen an die Adresse des Server-Scripts (Beispiel: PHP) übergeben. Ein einfaches Beispiel dazu:

Sub Post_mit_Response() Dim strURL As String, strPostDaten As String strURL = "https://example.org/phptest.php" strPostDaten = "" strPostDaten = strPostDaten & "name=Müller" strPostDaten = strPostDaten & "&vorname=Hans" strPostDaten = strPostDaten & "&strasse=Blumenweg 22" strPostDaten = strPostDaten & "&ort=Musterort" With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) If .ResponseText <> "" Then 'Response je nach Anforderung prüfen MsgBox .ResponseText 'Antwort des Servers ausgeben Else MsgBox "Schiefgegangen.", vbOKOnly + vbCritical, "Fehler" End If End With End Sub

Ein Beispiel für ein zugehöriges PHP-Script:

$adresse = "Die Adresse ist:\n\n"; $adresse .= $_POST['vorname'] . " " . $_POST['name'] . "\n"; $adresse .= $_POST['strasse'] . "\n"; $adresse .= $_POST['plz'] . " " . $_POST['ort'] . "\n"; echo $adresse;

In diesem Beispiel würde PHP aus den einzelnen Angaben eine formatierte Adresse gestalten und diese an VBA senden.

Dadurch können u. a. Daten aus Zellen oder Userformen ganz einfach per Mausklick, Tastendruck oder auch per Event an ein Script auf dem Server gesendet werden, das dann die weitere Verarbeitung erledigt. Somit können Mails versendet oder Eintragungen in Datenbanken (z. B. mySQL) vorgenommen werden usw.

Die Antwort des Servers ist hier natürlich nur reiner Text. Für komplexere Vorgänge sollten Sie sich ansehen, was in der Zusammenarbeit von VBA mit JSON möglich ist.

Download: excel_php_json.xlsm



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

Kategorie: Basics ▸ Variablen

(Tipp 211) Nachricht zum Beitrag an Autor Nach oben

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

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

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

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

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

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


Long für 32- und 64-Bit

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

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

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

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

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

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

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

Weitere Beispele:


Boolean

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

Bsp:

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

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

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

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

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

Byte

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

Integer

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

String

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

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

Range

Datentyp Range gibt eine Zelle oder einen Zellbereich aus.

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

Datum in einer Textbox mit denen einer Spalte vergleichenMakro/Sub/Prozedur

Kategorien: Steuerelemente ▸ Userform und Suchen/Ersetzen

(Tipp 78) Nachricht zum Beitrag an Autor Nach oben

Wie kann man das Datum aus dem Textfeld einer Userform mit einem Datum in der Spalte A eines Tabellenblattes vergleichen? Stimmt das Datum einer Zeile in Spalte A mit dem Textfeld in der Userform überein, sollen in dieser Zeile in den nächsten Spalten Werte aus einem zweitem Textfeld der Userform eingetragen werden.

Der Code prüft zunächst, ob in Textbox1 ein Datum enthalten ist. Wenn nicht, wird die Prozedur verlassen. Übrigens: Das Datum kann auch mit dem Minuszeichen auf dem Numblock eingegeben werden, so muss man nicht immer auf der Tastatur zum Punkt wechseln.

Anschließend wird das Datum, das in der Textbox steht, auf Tabelle1 in Spalte A gesucht. Wurde es nicht gefunden, erscheint die Meldung, ansonsten werden die Inhalte der Textboxen 2 und 3 in die Spalten B und C geschrieben.

Das Blatt Tabelle1 muss übrigens nicht aktiv sein.

Sub DatumSuchen() Dim rngZelle As Range, datDatum As Date If IsDate(UserForm1.TextBox1.Text) = False Then Exit Sub datDatum = CDate(UserForm1.TextBox1.Text) With Worksheets("Tabelle1") Set rngZelle = .Columns(1).Find(datDatum, lookat:=xlWhole) If rngZelle Is Nothing Then MsgBox "Nicht gefunden!" Else .Cells(rngZelle.Row, 2).Value = UserForm1.TextBox2.Text .Cells(rngZelle.Row, 3).Value = UserForm1.TextBox3.Text End If End With End Sub

Datum per Drehfeld auswählenMakro/Sub/Prozedur

Kategorien: Steuerelemente ▸ Userform und Datum/Zeit ▸ Steuerelemente

(Tipp 79) Nachricht zum Beitrag an Autor Nach oben

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

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

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

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

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

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

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

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

Der Code zum Kopieren:

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

Dialoge: Datei öffnen + Speichern unterMakro/Sub/Prozedur

Kategorie: Interaktion ▸ Dialoge

(Tipp 37) Nachricht zum Beitrag an Autor Nach oben

Wie ruft man den Öffnen- und den Speichern-unter-Dialog auf?

Datei ▸ Öffnen

Die wohl bekannteste Variante für den Öffnen-Dialog, mit der man die Auswahl komfortabel abfragen kann:

Dim varPfad varPfad = Application.GetOpenFilename("CSV-Dateien (*.csv), *.csv") If varPfad <> False Then Workbooks.Open Filename:=varPfad

Oder so:

With Application.FileDialog(msoFileDialogOpen) .Filters.Clear .Filters.Add "CSV-Dateien", "*.csv" .InitialFileName = "C:\Temp\" 'Ordner vorbelegen If .Show = -1 Then MsgBox .SelectedItems(1) End With

Hier wird eine gewählte Datei geöffnet:

Select Case Application.Dialogs(xlDialogOpen).Show("test.xls") Case -1: MsgBox "Geöffnet" Case 0: MsgBox "Abgebrochen" End Select


Datei ▸ Speichern unter

1. Möglichkeit (In A1 steht der Dateiname.):

Dim varPfad varPfad = Application.GetSaveAsFilename(Range("A1")) If varPfad = False Then Exit Sub ActiveWorkbook.SaveAs varPfad

2. Möglichkeit:

Dim varPfad varPfad = Application.Dialogs(xlDialogSaveAs).Show("test.xls") Select Case varPfad Case -1 'Gesichert Case 0 'Abgebrochen End Select


Eine Übersicht zu integrierten Dialogfeldern finden Sie in Integrierte Dialogfelder aufrufen

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

Kategorie: Format ▸ Farben

(Tipp 158) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Excel-Farbnummern auflisten lassen?

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

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

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

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

Mit der Zahl in lngStart kann einfach experimentiert werden.

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

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

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

Fehler abfangen und behandelnMakro/Sub/Prozedur

Kategorie: Interaktion ▸ Fehler

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

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

Beispiel 1:

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

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

Beispiel 2:

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

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

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

Download: fehlerbehandlung.xlsm

JSON empfangen und verarbeitenMakro/Sub/Prozedur

Kategorien: Netz ▸ Serverkommunikation und Stringoperationen ▸ JSON

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

Wie kann ich im Json-Format empfangene Daten mit VBA auswerten?

In Zeiten zunehmender Vernetzung spielt natürlich auch das Json-Format eine große Rolle, zunehmend auch für Excel. Das Senden und Empfangen der Daten ist kein Problem, während die Analyse dieser Daten mit VBA vergleichsweise umständlich ist. Einfache Möglichkeiten, wie beispielsweise mit jQuery, geben die Bordmittel von VBA nicht her.

Wesentlich vereinfacht wird dies jedoch mit dem JsonConverter von Tim Hall, den es hier zum Download gibt: VBA-tools/VBA-JSON. Zur Installation müssen (alles für Windows) nur zwei Schritte erfolgen:

  • Modul JsonConverter.bas in das VBA-Projekt einfügen
  • im Editor unter Extras ▸ Verweise ein Häkchen bei Microsoft Scripting Runtime setzen

Damit steht schon die Funktion JsonConverter.ParseJson() zur Verfügung.

Eine Datenebene

Das Verwenden soll hier zunächst an einem einfachen Beispiel gezeigt werden. Dazu nutzen wir dieses PHP-Script, das einen Adressdatensatz als Json-Objekt zur Verfügung stellt, den wir mit VBA abrufen:

$a['vorname'] = "Max"; $a['nachname'] = "Mütze"; $a['strasse'] = "Mützenweg 55"; $a['plz'] = "01234"; $a['ort'] = "Mützenhausen"; echo json_encode($a);

Zunächst rufen wir das Ganze vom Server ab:

strURL = "https://example.org/jsonabfrage.php" strPostDaten = "" 'Hier käme etwas rein, wenn was an den Server übermittelt werden sollte With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten)

Damit empfangen wir .ResponseText. Zum Vermeiden von Fehlern durch den JsonConverter ist hier schon wichtig, diese Rückgabe zu prüfen. Im einfachsten Fall mit If InStr(1, .ResponseText, "{") > 0 Then.

In .ResponseText steckt nun dieses Objekt:

Das weisen wir mit der Funktion JsonConverter.ParseJson(.ResponseText) einer Variablen zu, die als Dictionary-Objekt deklariert ist:

Set dicParsed = JsonConverter.ParseJson(.ResponseText)

In der Variablen sind nun die einzelnen Einträge aus dem ResponseText als Key=Value-Paare enthalten. Da die Variable dicParsed ein Objekt ist, verfügt sie über verschiedene Eigenschaften, mit denen wir arbeiten können. So können wir z. B. einfach eine Schleife über die Keys laufen lassen und haben somit deren Werte:

strAusgabe = strAusgabe & dicParsed.Count & " Einträge:" & vbNewLine & vbNewLine For Each varWert In dicParsed.Keys strAusgabe = strAusgabe & varWert & ": " & dicParsed(varWert) & vbNewLine Next varWert MsgBox strAusgabe

Das komplette VBA-Script zum Testen:

Sub Post_JSONAusgabe_1() Dim strURL As String, strPostDaten As String Dim dicParsed As Dictionary Dim varWert As Variant, strAusgabe As String strURL = "https://example.org/jsonabfrage.php" strPostDaten = "" 'Hier käme etwas rein, wenn was an den Server übermittelt werden sollte With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) 'Die Antwort muss geprüft werden, da sonst ein Fehler beim JsonConverter auftritt. 'Hier einfach auf das Vorhandensein der geschweiften Klammer: If InStr(1, .ResponseText, "{") > 0 Then strAusgabe = "" Set dicParsed = JsonConverter.ParseJson(.ResponseText) strAusgabe = strAusgabe & dicParsed.Count & " Einträge:" & vbNewLine & vbNewLine For Each varWert In dicParsed.Keys strAusgabe = strAusgabe & varWert & ": " & dicParsed(varWert) & vbNewLine Next varWert MsgBox strAusgabe Else MsgBox "Schiefgegangen: " & .ResponseText, vbOKOnly + vbCritical, "Fehler" End If End With End Sub

 

Mehrere Datenebenen

Häufig haben wir es jedoch mit Verschachtelungen zu tun, dass also in einem Array-Element ein weiterer Array (bzw. in einem Json-Element ein weiteres Json-Objekt) steckt. im vorigen Beispiel waren alle Elemente auf einer Ebene, aber genau so könnten die Elemente insgesamt ein Wert eines übergeordneten Elements sein:

  • values
    • vorname="Max"
    • nachname="Mütze"
    • strasse="Mützenweg 55"
    • plz="01234"
    • ort="Mützenhausen"

Wir hätten hier also den Key „values“, dem als Wert ein weiterer Array, bestehend aus Key-Value-Paaren, zugeordnet ist. Ein Beispiel, wie dies mit PHP möglich wäre:

$a['vorname'] = "Max"; $a['nachname'] = "Mütze"; $a['strasse'] = "Mützenweg 55"; $a['plz'] = "01234"; $a['ort'] = "Mützenhausen"; $b['values'] = $a; echo json_encode($b);

In VBA würde somit dieses Json-Objekt ankommen:

Zum Dekodieren verwenden wir wieder die bekannte Funktion und weisen das Ganze der Dictionary-Variablen zu:

Set dicParsed = JsonConverter.ParseJson(.ResponseText)

Wenn wir nun an die relevanten Inhalte wollen, müssen wir beachten, dass die als Array (bzw. genauer als Dictionary) im übergeordneten Schlüssel „values“ stecken. Das heißt, dass wir den ansprechen müssen. Wir durchlaufen also die einzelnen Elemente in dicParsed("values"):

'Hier werden nur die Einträge aus „values“ verwendet, also das, was in PHP in $a steckt: For Each varWert In dicParsed("values").Keys strAusgabe = strAusgabe & varWert & ": " & dicParsed("values")(varWert) & vbNewLine Next varWert

Hier ist das gesamte Script zum Testen, zum Schluss ergänzt um ein paar Ausgaben zur Verdeutlichung:

Sub Post_JSONAusgabe_2() 'PHP: 'Beachten: Hier gibt es eine Verschachtelung. 'Im Array $a sind die eigentlichen Daten enthalten. '$a['vorname'] = "Max"; '$a['nachname'] = "Mütze"; '$a['strasse'] = "Mützenweg 55"; '$a['plz'] = "01234"; '$a['ort'] = "Mützenhausen"; 'Zuletzt wird $a jedoch dem Array $b zugewiesen, hier dem Key „values“: '$b['values'] = $a; 'Im Json ist also diese Verschachtelung auch enthalten und wird so ausgegeben: 'echo json_encode($b); Dim strURL As String, strPostDaten As String Dim dicParsed As Dictionary Dim varWert As Variant, strAusgabe As String strURL = "https://example.org/jsonabfrage.php" strPostDaten = "" With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) If .ResponseText <> "" Then 'Anpassen, auf Fehler prüfen! strAusgabe = "" 'dicParsed ist der komplette(!) verschachtelte Array: Set dicParsed = JsonConverter.ParseJson(.ResponseText) 'Hier werden nur die Einträge aus „values“ verwendet, also das, was in PHP in $a steckt: For Each varWert In dicParsed("values").Keys strAusgabe = strAusgabe & varWert & ": " & dicParsed("values")(varWert) & vbNewLine Next varWert MsgBox "In PHP in $a enthalten:" & vbNewLine & vbNewLine & strAusgabe 'Nun wird der Array um einen Eintrag erweitert (entspräche in PHP $a['alter'] = 32;): 'dicParsed("values")("alter") = 32 dicParsed("values").Add "alter", 32 strAusgabe = "Das gesamte Json-Objekt ist nun:" & vbNewLine & vbNewLine strAusgabe = strAusgabe & JsonConverter.ConvertToJson(dicParsed) 'komplettes Json-Objekt strAusgabe = strAusgabe & vbNewLine & vbNewLine strAusgabe = strAusgabe & "während im Key „values“ (ehemals $b) nur das enthalten ist:" & vbNewLine & vbNewLine strAusgabe = strAusgabe & JsonConverter.ConvertToJson(dicParsed("values")) 'Inhalt nur von "values" MsgBox strAusgabe Else MsgBox "Schiefgegangen.", vbOKOnly + vbCritical, "Fehler" End If End With End Sub

Download: excel_php_json.xlsm



JSON mit VBA erstellen und an den Server schickenMakro/Sub/Prozedur

Kategorien: Netz ▸ Serverkommunikation und Stringoperationen ▸ JSON

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

Wie kann ich mit VBA das Json-Format erstellen und z. B. an PHP senden?

Am einfachsten und schnellsten geht es sicher mit dem JsonConverter von Tim Hall. Die Installation geht schnell und ist hier beschrieben: Json verarbeiten. Auf dieser Basis erfolgt auch die Beschreibung an dieser Stelle.

Die Erklärungen erfolgen am Beispiel der abgegbildeten Tabelle mit IDs, Nachnamen und Vornamen. Im ersten Beispiel werden die Spalten A bis C genutzt, im zweiten A bis D.

Für das Verständnis ist ggf. die Gliederung wichtig: Das kleinste Element ist das Paar aus Key und Value. Mehrere dieser (aber zusammengehörigen) Paare werden in einem Dictionary gesammelt. Verschiedene Dictionaries wiederum werden in einer Collection zusammengefasst. Gibt es davon mehrere, werden die in einem Dictionary gebündelt usw. Am Ende habe wir dann die verschiedenen Elemente der obersten Hierarchieebene; in diesen Beispielen sind das die jsonItems, die dann in Json umgewandelt werden.

Hinweis zur Syntax: Ob eine Zuweisung zu einem Element per Element(Key) = Value oder per Element.Add Key, Value erfolgt, ist hier egal. Im ersten Beispiel wird die erste Variante verwendet, im zweiten die zweite.

Eine Ebene

Im ersten Beispiel wollen wir die drei Zeilen als gleichrangige Elemente, die jeweils aus ID, Namen und (erstem) Vornamen bestehen, in eine Json-Struktur bringen. Dazu lassen wir einfach eine Schleife über die drei Zeilen laufen und erzeugen mit jeder Zeile ein Dictionary, hier jsonDictionary, das aus den drei Elementen id, nachname und vorname besteht. Jedes dieser drei Dictionaries weisen wir der Collection jsonItems zu, so dass diese am Ende aus den drei Dictionaries für die einzelnen Zeilen besteht:

For i = 2 To 4 jsonDictionary("id") = Cells(i, 1) jsonDictionary("nachname") = Cells(i, 2) jsonDictionary("vorname") = Cells(i, 3) jsonItems.Add jsonDictionary Set jsonDictionary = Nothing Next i

Nun führen wir zwei Schritte gleichzeitig durch: Wir konvertieren die Collection in das Json-Format und weisen das Ergebnis gleich der Variablen zu, die wir brauchen, um das Ganze per POST an den Server zu schicken. Diese Variable besteht aus einem Key (hier jsonobjekt) und einem Value, der hier der erzeugte Json-Code ist:

strPostDaten = "jsonobjekt=" & JsonConverter.ConvertToJson(jsonItems)

So sieht das dann als String aus, der an den Server geht:

Nun können wir das Ganze losschicken:

With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten)

Zur Kontrolle können wir diesen PHP-Code verwenden, der das Json-Objekt in einen Array umwandelt und ausgibt:

$jso = json_decode($_POST['jsonobjekt'], true); $a = print_r($jso, true); echo "Anzahl: " . count($jso) . "\n\n" . $a;

Wenn wir dann in VBA .ResponseText ausgeben lassen, sehen wir das Ergebnis, wie es auf dem Server vorliegt:

Hier der gesamte Code zum Testen:

Sub VBA2JSON() 'PHP: '$jso = json_decode($_POST['jsonobjekt'], true); '$a = print_r($jso, true); 'echo "Anzahl: " . count($jso) . "\n\n" . $a; Dim strURL As String, strPostDaten As String Dim jsonItems As New Collection Dim jsonDictionary As New Dictionary Dim i As LongPtr For i = 2 To 4 jsonDictionary("id") = Cells(i, 1) jsonDictionary("nachname") = Cells(i, 2) jsonDictionary("vorname") = Cells(i, 3) jsonItems.Add jsonDictionary Set jsonDictionary = Nothing Next i strPostDaten = "jsonobjekt=" & JsonConverter.ConvertToJson(jsonItems, 3) strURL = "https://example.org/vba2json.php" With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) If .ResponseText <> "" Then MsgBox .ResponseText Else MsgBox "Schiefgegangen.", vbOKOnly + vbCritical, "Fehler" End If End With End Sub

 

Verschachtelungen, mehrere Ebenen

Das Vorgehen bei mehreren Levels, also Ebenen, ist genau das gleiche. Die Besonderheit besteht nur darin, dass dem Dictionary beim Key=Value-Paar dem Key statt eines z. B. Strings eine Collection zugewiesen wird, also Key=Collection. Dieser Collection werden vorher die einzelnen Dictionary-Einträge mitgegeben, also die Key=Value-Paare der tieferen Ebene.

Wir verwenden das gleiche Beispiel wie vorher, nur dass die jeweils beiden Vornamen gesammelt in einem Array unter "vorname" eine Ebene tiefer sein sollen:

Dafür müssen wir nur die Zeile ändern, in der dem jsonDictionary bisher der Vorname zugewiesen wurde. Dafür legen wir ein neues Dictionary an, hier dicVorname. Dem weisen wir die beiden Key-Value-Paare zu (Hinweis: zum Verdeutlichen wurde hier die .Add-Schreibweise verwendet). Daraus erstellen wir dann die Collection colVornamen. Fertig ist der Value für die erste Ebene, dort weisen wir den dem Dictionary jsonDictionary zum Key vorname zu.

Der Rest bleibt. Mit PHP können wir .ResponseText wieder ansehen:

$jso = json_decode($_POST['jsonobjekt'], true); $a = print_r($jso, true); echo $a;

Oder etwas komprimierter, indem wir den Array analysieren:

$jso = json_decode($_POST['jsonobjekt'], true); foreach ($jso as $key => $value) { foreach ($value as $key1 => $value1) { if ($key1 == "vorname") { $jso1 = $value1; foreach ($jso1[0] as $key_v => $value_v) { echo $key . " => " . $key_v . ": " . $value_v . "\n"; } } else { echo $key . " => " . $key1 . ": " . $value1 . "\n"; } } echo "\n-----------------------------------\n"; }

Der gesamte Code zum Testen:

Sub VBA2JSON_Level() 'PHP: '$jso = json_decode($_POST['jsonobjekt'], true); '$a = print_r($jso, true); 'echo $a; 'Oder: '$jso = json_decode($_POST['jsonobjekt'], true); 'foreach ($jso as $key => $value) { ' foreach ($value as $key1 => $value1) { ' ' if ($key1 == "vorname") { ' $jso1 = $value1; ' foreach ($jso1[0] as $key_v => $value_v) { ' echo $key . " => " . $key_v . ": " . $value_v . "\n"; ' } ' } else { ' echo $key . " => " . $key1 . ": " . $value1 . "\n"; ' } ' ' } ' echo "\n-----------------------------------\n"; '} Dim strURL As String, strPostDaten As String Dim jsonItems As New Collection, colVornamen As New Collection Dim jsonDictionary As New Dictionary, dicVorname As Dictionary Dim i As LongPtr For i = 2 To 4 jsonDictionary.Add "id", Cells(i, 1) jsonDictionary.Add "nachname", Cells(i, 2) Set dicVorname = New Dictionary dicVorname.Add "vorname1", Cells(i, 3) dicVorname.Add "vorname2", Cells(i, 4) colVornamen.Add dicVorname jsonDictionary.Add "vorname", colVornamen Set colVornamen = Nothing jsonItems.Add jsonDictionary Set jsonDictionary = Nothing Next i strPostDaten = "jsonobjekt=" & JsonConverter.ConvertToJson(jsonItems) strURL = "https://example.org/vba2json.php" With CreateObject("MSXML2.XMLHTTP") .Open "POST", strURL, False .setRequestHeader "Content-Type", "application/x-www-form-urlencoded" .Send (strPostDaten) If .ResponseText <> "" Then MsgBox .ResponseText Else MsgBox "Schiefgegangen.", vbOKOnly + vbCritical, "Fehler" End If End With End Sub

Download: excel_php_json.xlsm

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

Kategorien: Tabelle ▸ Zellen und Stringoperationen ▸ Teile

(Tipp 138) Nachricht zum Beitrag an Autor Nach oben

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

Drei Varianten:

Namen zählen ab Excel 365: dynamische Arrayformeln

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


Namen ausgeben ab Excel 365: dynamische Arrayformeln als Matrixfunktion

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

In die Zelle kann dann einfach die Formel:

=Einzelnamen(A1:B8)


Variante für ältere Versionen

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

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

Laufwerk über InputBox auswählenMakro/Sub/Prozedur

Kategorie: Dateien und Ordner ▸ Laufwerk

(Tipp 28) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich den Anwender über eine Input-Box ein bestimmtes Laufwerk auswählen lassen (die Laufwerke sind nicht bekannt, sie müssen eingelesen werden)?

Eine Lösung, die noch aus den alten Diskettenlaufwerk stammt - aus Nostalgiegründen ist sie noch hier:

Sub LaufwerkAuswahl() Dim intI As Integer Dim strLWs As String, strLW As String, strStart As String strStart = Left(CurDir, 1) On Error Resume Next For intI = 97 To 122 'Laufwerke für den Inputbox-Text zusammenstellen Err.Clear ChDrive Chr(intI) If Err = 0 Then strLWs = strLWs & Chr(intI) & "," Next intI strLWs = Left(strLWs, Len(strLWs) - 1) strLW = InputBox("Bitte Laufwerk wählen (" & strLWs & "):", Right(strLW, 1)) If strLW = "" Then ChDrive strStart Else ChDrive strLW End If MsgBox CurDir End Sub

Löschen überflüssiger SymbolleistenMakro/Sub/Prozedur

Kategorie: Menü ▸ Veraltet

(Tipp 64) Nachricht zum Beitrag an Autor Nach oben

Wie kann man überflüssige, benutzerdefinierte Symbolleisten löschen (die Symbolleisten sollen abgefragt und angezeigt werden)?

Sub SymLoeschen() Dim Cdb As CommandBar Dim Status As Boolean For Each Cdb In Application.CommandBars If Cdb.BuiltIn = False Then If MsgBox(Cdb.Name & Chr(13) & "Symbolleiste löschen?", vbQuestion + vbYesNo) = vbYes Then Cdb.Delete End If Next Cdb End Sub

Mails mit VBA und PHP versenden (mehrere Empfänger, mehrere Anhänge)Makro/Sub/ProzedurTipp

Kategorien: Netz ▸ Serverkommunikation und Netz ▸ Mail

(Tipp 600) Nachricht zum Beitrag an Autor Nach oben

Wie können mit VBA Mails mit mehreren Dateianhängen an mehrere Empfänger gesendet werden?

Die Aufgabenstellung, per Mausklick oder automatisiert Mails zu versenden, ist sehr weit verbreitet. Es gibt auch viele Tipps, wie das mit Outlook erfolgen kann. Jedoch gibt es immer wieder Probleme, sei es durch Rechtebeschränkungen, wegzuklickende Meldungen von Outlook usw.

Am sinnvollsten ist deshalb eine Lösung, mit der man weitestgehend unabhängig von Officeprogrammen ist. Hier besteht die Möglichkeit, VBA nur zum Versand der Maildaten an einen Server zu benötigen, der dann den Rest des Erstellens und Versands der Mail übernimmt.

Voraussetzung für dieses Beispiel ist, dass auf dem Server ein eigenes PHP-Script erstellt werden kann, das dann natürlich auch eine Adresse (URL) hat.

Basis ist der Beitrag Dateiupload. Der VBA-Code dazu wurde hier um die Möglichkeit, Text zu versenden, erweitert. Prinzipiell trifft hier aber das Gleiche zu - vor allem, dass die oben verwendeten Konstanten auch dynamisch verwendet werden können. Hier wird nur das Prinzip aufgezeigt, die Anpassungen müssen dann entsprechend der eigenen Gegebenheiten erfolgen. Dies betrifft insbesondere auch die Sicherheit. Denken Sie daran, die manuell zu ändernden Daten vorher zu prüfen. Diese Scripte sind auch leicht zum Versand von Massenspam zu verwenden.

Dies ist der VBA-Code, der um die Text-Funktion erweitert wurde:

Private Const strURL As String = "https://…Ihre URL…/…Ihre PHP-Datei….php" Private Const strDatei1 As String = "C:\…\test1.jpg" Private Const strDatei2 As String = "C:\…\test2.jpg" Private Const strDatei3 As String = "C:\…\test3.jpg" Private Const strMailEmpfaenger As String = "info1@example.org;info2@example.org;info3@example.org" Private Const strAbsText As String = "Max Mütze" Private Const strAbsMail As String = "xyz@example.org" Private Const strBoundary As String = "---------------------------7da24f2e50046" Sub DateiUploadUndMail() Dim strPostDaten As String strPostDaten = "" strPostDaten = strPostDaten & Header_Text("empfaenger", strMailEmpfaenger, "text/plain", strBoundary) strPostDaten = strPostDaten & Header_Text("abstext", strAbsText, "text/plain", strBoundary) strPostDaten = strPostDaten & Header_Text("absmail", strAbsMail, "text/plain", strBoundary) strPostDaten = strPostDaten & Header_Text("betreff", "Das ist der Betreff.", "text/plain", strBoundary) strPostDaten = strPostDaten & Header_Text("mailtext", "Hallo!" & vbNewLine & vbNewLine & "Ich bin der Mailtext.", "text/html", strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei1, strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei2, strBoundary) strPostDaten = strPostDaten & Header_Datei(strDatei3, strBoundary) strPostDaten = strPostDaten & "--" & strBoundary & "--" With CreateObject("Microsoft.XMLHTTP") .Open "POST", strURL, False .SetRequestHeader "Content-Type", "multipart/form-data; boundary=" & strBoundary .Send SendDaten(strPostDaten) MsgBox .ResponseText End With End Sub Function Header_Text(strKey As String, strValue As String, strMime As String, strBoundary) Header_Text = "--" & strBoundary & vbCrLf & _ "Content-Disposition: form-data; name=""" & strKey & """;" & vbCrLf & _ "Content-Type: " & strMime & "; charset=UTF-8" & vbCrLf & vbCrLf & strValue & vbCrLf End Function Function Header_Datei(strDateiPfad As String, strBoundary As String) Dim strDateiKonv As String, strDateiName As String Header_Datei = "" If strDateiPfad <> "" Then strDateiName = DateiName(strDateiPfad) strDateiKonv = DateiKonv(strDateiPfad) Header_Datei = "--" & strBoundary & vbCrLf & _ "Content-Disposition: form-data; name=""file[]""; filename=""" & strDateiName & """" & vbCrLf & _ "Content-Type: application/octet-stream" & vbCrLf & vbCrLf & strDateiKonv & vbCrLf End If End Function Function DateiName(strpfad As String) DateiName = "" If strpfad <> "" Then DateiName = Mid$(strpfad, InStrRev(strpfad, "\") + 1) End Function Function DateiKonv(strpfad As String) Dim lngDNr As LongPtr, bytBuffer() As Byte DateiKonv = "" lngDNr = FreeFile Open strpfad For Binary Access Read As lngDNr If LOF(lngDNr) > 0 Then ReDim bytBuffer(0 To LOF(lngDNr) - 1) As Byte Get lngDNr, , bytBuffer DateiKonv = StrConv(bytBuffer, vbUnicode) End If Close lngDNr End Function Private Function SendDaten(strText As String) As Byte() SendDaten = StrConv(strText, vbFromUnicode) End Function

Damit werden die Daten an das PHP-Script gesendet, das diese empfängt und daraus die Mails erstellt sowie diese versendet. Auch hier unbedingt daran denken, die Daten noch auf Gültigkeit zu prüfen!

Das komplette PHP-Script:

/* Wichtig: Da das Script prinzipiell auch zu Versand von Massenspam geeignet ist, sollten ganz am Anfang noch Kennwort und Nutzername (oder eine andere Absicherung) abgefragt werden. Diese Angaben können von VBA aus ganz einfach als POST-Paare mitgeschickt werden. */ // Verzeichnis, in dem die Anhänge zwischengespeichert werden: $anhangverz = 'uploadtest/'; // Vorsicht: Ggf. prüfen, ob vorhanden und leer. // Mailadressen durch Semikolon getrennt $empfstrings = $_POST['empfaenger']; // Text, der als Absender erscheint: $abstext = $_POST['abstext']; // Mailadresse des Absenders: $absmail = $_POST['absmail']; $betreff = utf8_encode($_POST['betreff']); $mailtext = utf8_encode($_POST['mailtext']); // File[]-Array sinnvoll sortieren: $dateiarray = array(); foreach($_FILES['file'] as $k1 => $v1) { foreach($v1 as $k2 => $v2) { $dateiarray[$k2][$k1] = $v2; } } // Anhänge speichern, Daten für Versand aufbereiten $anhangarray = array(); $a = 0; foreach ($dateiarray as $file) { // Ggf. prüfen, ob Dateiname schon im Verzeichnis vorhanden ist und // ggf. umbenennen: $datname = basename($file['name']); $uploadpfad = $anhangverz . $datname; if (move_uploaded_file($file['tmp_name'], $uploadpfad)) { $a++; $anhangarray[$a]['pfad'] = $uploadpfad; $anhangarray[$a]['dateiname'] = $datname; $anhangarray[$a]['groesse'] = filesize($uploadpfad); $dateiinhalt = fread(fopen($uploadpfad, "r"), filesize($uploadpfad)); $anhangarray[$a]['data'] = chunk_split(base64_encode($dateiinhalt)); } } $empf_array = explode(";",$empfstrings); $encoding = mb_detect_encoding($mailtext, "utf-8, iso-8859-1, cp-1252"); $mime_boundary = "-----=" . md5(uniqid(microtime(), true)); $versendet = 0; // Mail an jeden Empfänger schicken: foreach($empf_array as $einzelmail) { if (trim($einzelmail) != "") { $header ="From:" . $abstext . "<" . $absmail . ">\n"; $header.= "MIME-Version: 1.0\r\n"; $header.= "Content-Type: multipart/mixed;\r\n"; $header.= " boundary=\"" . $mime_boundary . "\"\r\n"; $content = "This is a multi-part message in MIME format.\r\n\r\n"; $content.= "--".$mime_boundary."\r\n"; $content.= "Content-Type: text/plain; charset=\"$encoding\"\r\n"; $content.= "Content-Transfer-Encoding: 8bit\r\n\r\n"; $content.= $mailtext . "\r\n"; if ($a > 0) { // Ggf. Mailanhänge anfügen for ( $n = 1; $n <= $a; $n++) { $content.= "--" . $mime_boundary . "\r\n"; $content.= "Content-Disposition: attachment;\r\n"; $content.= "\tfilename=\"" . $anhangarray[$n]['dateiname'] . "\";\r\n"; $content.= "Content-Length: " . $anhangarray[$n]['groesse'] . ";\r\n"; $content.= "Content-Type: application/pdf; name=\"" . $anhangarray[$n]['dateiname'] . "\"\r\n"; $content.= "Content-Transfer-Encoding: base64\r\n\r\n"; $content.= $anhangarray[$n]['data'] . "\r\n"; } } $content .= "--" . $mime_boundary . "--"; if (mail($einzelmail, $betreff, $content, $header)) { $versendet++; } } } // Ggf. Mailanhänge löschen if ($a > 0) { for ( $n = 1; $n <= $a; $n++) { if ($anhangarray[$n]['pfad'] != "") {unlink($anhangarray[$n]['pfad']);} } } // Meldung, die in VBA in der MsgBox als .ResponseText erscheint: echo $versendet . " Mail(s) erfolgreich gesendet.";

Makro beim Speichern ausführenMakro/Sub/Prozedur

Kategorie: Ereignisse ▸ Mappe

(Tipp 113) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich erreichen, daß ein Makro beim Speichern ausgeführt wird?

Der Code muss in das Klassenmodul der Arbeitsmappe. Also Doppelklick auf DieseArbeitsmappe und dann einfügen.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim intFrage As Integer intFrage = MsgBox(" Möchten Sie die Arbeitsmappe wirklich speichern?", vbYesNo) If intFrage = vbNo Then Cancel = True End Sub

Markierten Bereich als CSV speichernMakro/Sub/Prozedur

Kategorien: Dateien und Ordner ▸ Dateioperation und Stringoperationen ▸ Verketten

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

Wie kann ich einen markierten Bereich als speichern?

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

Download: range2csv.xlam



Onedrive-Pfad zu lokalem Pfad (regulärer Ausdruck)UDF - benutzerdefinierte Funktion

Kategorien: Netz ▸ OneDrive und Stringoperationen ▸ Ersetzen

(Tipp 604) Nachricht zum Beitrag an Autor Nach oben

Mit z. B. ThisWorkbook.Fullname wird der OneDrive-Pfad (https://d.docs.live.net/…) zurückgegeben. Wie kann der in den lokalen Pfad umgewandelt werden?

Die folgende Funktion speichert mit Environ() den lokalen OneDrive-Ordner in eine Variable. Anschließend ersetzt sie im Pfad der Datei diverse mögliche Zeichenfolgen, die OneDrive selbst vergibt. Mit einem regulären Ausdruck wird dann der OneDrive-Teil im Pfad der Datei durch den lokalen Pfad ersetzt.

Ggf. können noch ein paar Fehlerbehandlungen eingebaut werden.

Function OneDrive2Lokal(ByVal strPfad As String) As String Dim strOnedrivePfad As String, RegEx As Object, regMatches OneDrive2Lokal = "" If RegEx Is Nothing Then Set RegEx = CreateObject("VBScript.RegExp") If Left(strPfad, 6) = "https:" Then strOnedrivePfad = Environ("OneDrive") & "\" ' oder "OneDriveConsumer" strPfad = Replace(strPfad, "^J", ",") strPfad = Replace(strPfad, "^N", "#") strPfad = Replace(strPfad, "^0", "&") RegEx.Pattern = "^(https:/{2}[A-Za-z0-9./]+/)" Set regMatches = RegEx.Execute(strPfad) strPfad = Replace(RegEx.Replace(strPfad, strOnedrivePfad), "/", "\") End If OneDrive2Lokal = strPfad Set RegEx = Nothing End Function

Die Funktion kann beliebig verwendet werden, zum Beispiel:

MsgBox OneDrive2Lokal(ThisWorkbook.FullName)

Ordner erstellen, wenn nicht vorhandenMakro/Sub/Prozedur

Kategorie: Dateien und Ordner ▸ Ordner

(Tipp 15) Nachricht zum Beitrag an Autor Nach oben

Wie erstellt man einen Ordner, wenn dieser noch nicht existiert?

Es wird geprüft, ob der Ordner existiert. Wenn nicht, wird er erstellt. Ggf. noch daran denken, etwaige Fehler abzufangen.

Dim strPfad As String strPfad = "\\COMPUTER\Ordner\Unterordner\" 'Beispiel für Netzwerk strPfad = "C:\Temp\" 'Beispiel lokal If Dir(strPfad, vbDirectory) <> "" Then MsgBox "Verzeichnis existiert bereits" Else MkDir strPfad End If

Per Makro Vorlagenverzeichnis abfragenMakro/Sub/Prozedur

Kategorie: System ▸ Windows

(Tipp 10) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich abfragen, in welchem Verzeichnis sich das Vorlagenverzeichnis von Microsoft Office befindet?

Das Vorlagenverzeichnis kann mit folgendem Code abgefragt werden:

Sub VorlagenVerzeichnis() MsgBox Application.TemplatesPath End Sub

Rückgabewert aus MsgBoxMakro/Sub/Prozedur

Kategorie: Interaktion ▸ Meldungen

(Tipp 9) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich abfragen, welche Schaltfläche in einer MsgBox gewählt wurde?

Bemerkungen:

Soll die MsgBox einen Wert zurückgeben, schreibt man die Argumente hinter MsgBox in Klammern, sonst nicht.

Die If-Abfrage ist nur zur besseren Übersichtlichkeit untereinander geschrieben, sie kann auch einfacher geschrieben werden:
If ... Then ... Else

Sub Meldung() Dim intFrage As Integer intFrage = MsgBox("Bitte eine Schaltfläche wählen.", vbYesNo + vbExclamation, "Schaltfläche") If intFrage = vbYes Then MsgBox "Sie haben 'Ja' gewählt.", vbOKOnly + vbInformation, "Ja" Else MsgBox "Sie haben 'Nein' gewählt.", vbOKOnly + vbInformation, "Nein" End If End Sub

SchleifeMakro/Sub/ProzedurTipp

Kategorien: Basics ▸ Schleifen und Programmiertechnik ▸ Schleife

(Tipp 95) Nachricht zum Beitrag an Autor Nach oben

Was ist eine Schleife und wie funktioniert sie?

Eine Schleife ist das wiederholte Abarbeiten eines Befehls oder einer Folge von Befehlen, bis eine bestimmte Bedingung erfüllt ist. Wird keine oder eine falsche Bedingung vorgegeben, wird die Schleife nicht abgebrochen. Dann spricht man von einer Endlosschleife. In VBA kann sie dafür sorgen, dass die ausführende Anwendung (hier also z. B. Excel) einfriert, also nicht mehr reagiert.

1. For-Schleife

In dieser Schleife verändert die Variable i ihren Wert von 1 bis 5 in Einerschritten, der letzte Wert ist also vorgegeben (Abbruchbedingung). Für jeden Wert wird eine Meldung ausgegeben.

Sub Schleife() For i = 1 To 5 MsgBox i Next i End Sub

2. While-Schleife:

Auch in dieser Schleife verändert die Variable i ihren Wert, der zuerst mit 1 festgelegt ist. Aber mit Do While i <= 5 wird festgelegt, daß die Schleife so lange zu durchlaufen ist, wie der Wert von i kleiner oder gleich 5 ist. Bei jedem Durchlauf soll die Meldung i ausgegeben und i um den Wert 1 erhöht werden.

Sub Schleife2() i = 1 Do While i <= 5 MsgBox i i = i + 1 Loop End Sub

In VBA gibt es bei While noch einige andere Varianten. So kann die Ausführungsdauer am Ende definiert werden (Loop While …) oder es kann auch eine Bis-Bedingung festgelegt werden (Do Until … oder Loop Until).

3. Sprungmarke:

Hier hat die Variable i auch den Startwert 1. Aber im Unterschied zu den anderen Beispielen wird hier mit einer If-Then-Else-Abfrage gearbeitet. Wenn i kleiner oder gleich 5 ist, soll die Meldung i erscheinen, anschließend zu i der Wert 1 addiert und bei der Sprungmarke nochmal: fortgesetzt werden. Wenn i im Beispiel den Wert 6 hat, ist die Bedingung i <= 5 nicht erfüllt, die Abfragebedingung ist nicht mehr erfüllt; somit werden die Schritte zwischen If und End If nicht mehr durchgeführt.

Sub Schleife3() i = 1 nochmal: If i <= 5 Then MsgBox i i = i + 1 GoTo nochmal End If End Sub

Hinweis: Die Arbeit mit Sprungmarken ist veraltet und kein guter Stil. Außerdem handelt es sich dabei eigentlich nicht um eine wirkliche Schleife.

4. Endlosschleife:

Bei dieser Schleife handelt es sich um eine Endlosschleife. Es wurde eine falsche Abbruchbedingung festgelegt (i >= 1); i wird in diesem Beispiel immer größer oder gleich 1 sein. Deshalb wird die Schleife ohne Ende weitergeführt. Hier kann die Schleife nur mit Esc abgebrochen werden (wenn kein Dialogfeld aktiv ist) bzw. mit Strg + Pause. Oder im Ernstfall mit Strg + Alt + Entf.

Sub Schleife4() i = 1 Do While i >= 1 i = i + 1 Loop End Sub

Schließen-Button entschärfenMakro/Sub/Prozedur

Kategorien: Steuerelemente ▸ Userform und Ereignisse ▸ Userform

(Tipp 85) Nachricht zum Beitrag an Autor Nach oben

Ist es möglich, bei einer UserForm den oberen rechten X-Button zu deaktivieren?

Dazu wird der X-Button (also Schließen-Button) zwar nicht ausgeblendet, aber das Dialogfeld läßt sich darüber nicht schließen. Damit ist auch Alt + F4 entschärft und die Userform lässt sich nur mit einer eigenen Schaltfläche schließen, die zweckmäßigerweise erst eingebaut werden sollte. Das funktioniert auch, wenn beim eigenen Button Cancel und/oder Default auf True gesetzt sind.

Das Makro bei den Userform-Ereignissen einfügen (Doppelklick auf Userform im Editor):

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = 0 Then Cancel = 1 MsgBox "Bitte verlassen Sie das Dialogfeld mit den Schaltflächen.", vbOKOnly + vbInformation, "Bitte Schaltfläche betätigen." End If End Sub

String aufteilenUDF - benutzerdefinierte Funktion

Kategorie: Stringoperationen ▸ Teile

(Tipp 567) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich einen String aufgrund eines Trennzeichens aufteilen?

Split()

Die folgende Funktion splittet den String am Trenner und gibt - wenn vorhanden - das Element intWelcher zurück:

Function SplitString(strString, intWelcher, strTrenner) Dim arrTemp SplitString = "" arrTemp = Split(strString, strTrenner) If UBound(arrTemp) >= intWelcher - 1 Then SplitString = arrTemp(intWelcher - 1) End Function

Eingesetzt wird sie so: MsgBox SplitString("abc;cde;890;321", 3, ";"). Oder als Formel in eine Zelle.


Array, implizite Schnittmenge, impliziter Schnittpunktoperator @

Ab Excel 365 ist es auch möglich, die aufgeteilte Zeichenfolge insgesamt ausgeben zu lassen. Für das Beispiel die folgende Funktion:

Function Stringteile(strString, strTrenner) Dim arrTemp arrTemp = Split(strString, strTrenner) Stringteile = IIf(UBound(arrTemp) > 0, arrTemp, "") End Function

In Zelle B1 steht der String Januar;Februar;März, in C1 steht die Formel =Stringteile(B1;";"). Die Monate werden durch die Funktion also am Semikolon gesplittet und die Funktion gibt das Ganze als Array zurück. Da es sich aber um drei Elemente handelt (eben die drei Monate), werden diese auf die Nachbarzellen ausgeweitet; die Zelle mit der Formel fließt über.

Wird jedoch der implizite Schnittpunktoperator @ verwendet (also =@Stringteile(B1;";")), wird nur das erste Element des Arrays zurückgegeben, also der Januar.

In jedem Fall kann aus einer anderen Formel heraus Bezug auf eine Ergebniszelle genommen werden. Im Beispiel auch auf E1, wo das Element März steht.


Für sehr alte Excelversionen

Die hier dargestellte Funktion stellt eine Alternative zur Funktion Split() dar, die es in niedrigeren Excelversionen noch nicht gab. Alternativ kann die Funktion auch als Tabellenblattfunktion verwendet werden, wenn man Daten - Text in Spalten nicht verwenden kann.

An die Funktion wird übergeben, welcher String aufgeteilt werden soll (strString), der wievielte Eintrag zurückgegeben werden soll (intWelcher) und um welches Trennzeichen es sich handelt (strTrenner).

Function SemiTrenner(strString, intWelcher, strTrenner) Dim intI As Integer Dim intZaehler As Integer Dim intBeginn As Integer, intEnde As Integer intBeginn = 0 intEnde = 0 intZaehler = 1 If Right(strString, 1) <> strTrenner Then strString = strString & strTrenner For intI = 1 To Len(strString) + 2 If Mid(strString, intI, 1) = strTrenner Then If intZaehler = 1 And intWelcher = 1 Then intBeginn = 1 intEnde = intI Exit For ElseIf intZaehler = intWelcher Then intBeginn = intEnde + 1 intEnde = intI Exit For End If intZaehler = intZaehler + 1 intEnde = intI End If Next If intBeginn > 0 And intEnde > 0 Then SemiTrenner = Mid(strString, intBeginn, intEnde - intBeginn) Else SemiTrenner = "" End Function

So könnte die Funktion wie folgt eingesetzt werden:

strString = "1;456;78,9bb;543;" MsgBox SemiTrenner(strString, 3, ";")

Das Ergebnis wäre in diesem Fall 78,9bb.



Summe aus A1 von allen BlätternMakro/Sub/Prozedur

Kategorie: Mappe ▸ Tabellen

(Tipp 114) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Summe von A1 aus jedem Blatt bilden?

Beispielsweise durch eine Schleife über die Blätter der Mappe:

Sub Addieren() Dim dblSummen As Double, intI As Integer dblSummen = 0 For intI = 1 To Worksheets.Count dblSummen = dblSummen + Worksheets(intI).Range("A1") Next MsgBox dblSummen End Sub

Tabellenblatt durchsuchen und bestätigen lassenMakro/Sub/Prozedur

Kategorie: Suchen/Ersetzen

(Tipp 72) Nachricht zum Beitrag an Autor Nach oben

Wie kann man ein Tabellenblatt nach einem vorgegebenen Begriff absuchen und bei jeder gefundenen Zelle bis zur Bestätigung verharren?

Sub Auswahl() Dim rngZelle As Range, strBegriff As String strBegriff = InputBox("Bitte Suchbegriff eingeben:", Application.UserName) If strBegriff = "" Then Exit Sub Set rngZelle = ActiveSheet.Columns("A:F").Find(strBegriff) If rngZelle Is Nothing Then Beep MsgBox "Suchbegriff nicht gefunden!", , Application.UserName Exit Sub End If rngZelle.Select MsgBox rngZelle.Address(False, False) rngZelle.Offset(1).Select While ActiveCell.Address <> rngZelle.Address Cells.FindNext(After:=ActiveCell).Activate If ActiveCell.Address = rngZelle.Address Then Exit Sub MsgBox ActiveCell.Address(False, False) Wend End Sub

Tabellenblattnamen auslesen (VBA + Formel)Makro/Sub/ProzedurFormellösung

Kategorie: Tabelle ▸ Eigenschaften

(Tipp 134) Nachricht zum Beitrag an Autor Nach oben

Wie erhalte ich den Tabellenblatt-Namen?

VBA

Sub TabellenblattName() MsgBox ActiveSheet.Name End Sub


Formel

Die Funktion =ZELLE("Dateiname";A1) gibt den Pfad der Mappe bis zur Tabelle zurück. Der Dateiname steht dabei in eckigen Klammern, danach kommt der Tabellenname. Also kann der Teil von der letzten eckigen Klammer bis zum Schluss extrahiert werden:

=RECHTS(ZELLE("Dateiname");LÄNGE(ZELLE("Dateiname"))-FINDEN("]";ZELLE("Dateiname")))

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

UserForm drei Sekunden zeigenMakro/Sub/Prozedur

Kategorien: Steuerelemente ▸ Userform und Ereignisse ▸ Zeit

(Tipp 81) Nachricht zum Beitrag an Autor Nach oben

Ist es möglich, sowas wie eine MsgBox zu erzeugen, die für eine bestimmte Zeit sichtbar ist und dann ohne Klicken wieder verschwindet?

Wahrscheinlich ist es am sinnvollsten, dazu eine Userform zu nutzen. Die kann auch etwas gestaltet werden, so dass ein mögliches Intro (Meldung/Information beim Programmstart) etwas ansprechender aussieht.

Dazu ein Beispiel, wie das beim Aktivieren der Userform geschieht - sie wird drei Sekunden angezeigt:

Private Sub UserForm_Activate() Application.Wait Now + TimeSerial(0, 0, 3) Unload Me End Sub

VariableMakro/Sub/ProzedurTipp

Kategorie: Basics ▸ Variablen

(Tipp 210) Nachricht zum Beitrag an Autor Nach oben

Eine Variable ist ein frei wählbarer Text, der zur Laufzeit einer Prozedur seinen Wert verändert. Die Deklaration von Variablen wird in diesen Erläuterungen vernachlässigt.

1. Beispiel:

In diesem Beispiel ist intI („int“ für »Integer« - für den Anfang soll dies keine Rolle spielen) die Variable. Sie hat den Anfangswert 1. In der nächsten Zeile wird zu diesem Wert i der Wert 1 addiert. Deshalb zeigt die Meldung die Zahl 2.

Sub Werteaenderung() intI = 1 intI = intI + 1 MsgBox intI End Sub

2. Beispiel:

Hier ist die Variable die Zeichenfolge DasIstEinTest. Zuerst nimmt sie eine leere Zeichenfolge als Wert an (""). In der nächsten Zeile wird das Wort "Max" hinzugefügt (Wert: Max). Dann wird an diesen Wert eine Leerstelle gehangen und in der letzten Zeile das Wort "Mütze" hinzugefügt. So ist während der Laufzeit des Makros aus einer leeren Zeichenfolge der Name "Max Mütze" entstanden, der auch in der Meldung ausgegeben wird.

Sub Werteaenderung2() strDasIstEinTest = "" strDasIstEinTest = strDasIstEinTest & "Max" strDasIstEinTest = strDasIstEinTest & " " strDasIstEinTest = strDasIstEinTest & "Mütze" MsgBox strDasIstEinTest End Sub

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



Während der Laufzeit einer Userform für jeden Tag eines Monats eine Checkbox hinzufügenMakro/Sub/Prozedur

Kategorie: Steuerelemente ▸ Userform

(Tipp 568) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich während der Laufzeit einer Userform für jeden Tag eines Monats eine Checkbox hinzufügen?

Ausschlaggebend ist hier intTopPosition. Damit wird die erste Position auf 20 gesetzt und anschließend für jede Box erhöht - damit gibt man also die Position der Box vor. Wenn es zweispaltig werden soll, muss entsprechend bei einem bestimmten Schleifendurchlauf wieder auf 20, aber auch die Left-Position nach rechts gesetzt werden.

Private Sub CommandButton1_Click() Dim ctrCheck As Control Dim datStartDatum As Date Dim datZaehler As Date Dim intTopPosition As Integer Dim strMonat As String strMonat = "Februar" datStartDatum = CDate("1." & strMonat & ".2010") intTopPosition = 20 datZaehler = datStartDatum Do While Month(datZaehler) = Month(datStartDatum) Set ctrCheck = Me.Controls.Add("forms.CheckBox.1") ctrCheck.Top = intTopPosition ctrCheck.Caption = Format(datZaehler, "dd.MM.yy") Set ctrCheck = Nothing intTopPosition = intTopPosition + 15 datZaehler = datZaehler + 1 Loop End Sub

Die Eigenschaften der neu erstellten Steuerelemente kann man z. B. wie folgt aufrufen:

Private Sub CommandButton2_Click() MsgBox UserForm1.Controls("CheckBox5").Value End Sub