Datenschutzerklärung


Direktnachricht



Ihre Software

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Aktuelles Datum suchenMakro/Sub/Prozedur

Kategorien: Suchen/Ersetzen und Datum/Zeit ▸ Suchen

(Tipp 68) Nachricht zum Beitrag an Autor Nach oben

In der Mappe sind Blätter mit den Monaten (lang) benannt. Das Blatt des aktuellen Monats und dort die Zelle mit dem aktuellen Datum sollen aktiviert werden.

Zuerst wird das Blatt mit dem Namen des aktuellen Monats aktiviert, dann auf dem gesamten Blatt nach dem Datum gesucht:

Dim rngZelle As Range Sheets(Format(Date, "MMMM")).Select Set rngZelle = Cells.Find(What:=Date, LookAt:=xlWhole, LookIn:=xlValues) If Not rngZelle Is Nothing Then rngZelle.Activate

U. U. wäre es noch sinnvoll, Fehler abzufangen - wenn z. B. das Blatt nicht existiert.

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.

BeispieleMakro/Sub/ProzedurTipp

Kategorie: Basics ▸ OOP

(Tipp 219) Nachricht zum Beitrag an Autor Nach oben

Zuerst drehen wir in der aktiven Mappe auf dem Blatt Tabelle1 den Text:

Worksheets("Tabelle1").Rows("1:1").Orientation = 90

Dann drehen wir in der xyz.xls, die natürlich offen sein muss, den Text:

Workbooks("xyz.xls").Worksheets("Tabelle1").Rows("1:1").Orientation = 90

Und jetzt weisen wir an, dass A1 von der Tabelle1 der aktiven Mappe in die xyz.xls in die Tabelle2 in A1 kopiert werden soll (in einer Zeile):

Sheets("Tabelle1").Range("A1").Copy _ Workbooks("xyz.xls").Worksheets("Tabelle2").Range("A1")

Das geht alles ohne Select und Activate.

Besonderheit bei den EigenschaftenTipp

Kategorie: Basics ▸ OOP

(Tipp 218) Nachricht zum Beitrag an Autor Nach oben

Der Punkt spielt bei Objekten immer eine zentrale Rolle. Bei den Eigenschaften kommt noch das Gleichheitszeichen hinzu.

Man nennt das Objekt, danach hinter einem Punkt die Eigenschaft und dann nach einem Gleichheitszeichen, wie die Eigenschaft sein soll.

Beispiel:

Auto.Farbe = Rot

Dies gilt in VBA sowohl für Abfragen als auch für Zuweisungen. Man kann also mit dieser Syntax abfragen, ob ein Auto rot ist; man kann aber auch dem Objekt Auto die Farbe Rot zuweisen. Wenn man von anderen Sprachen, wie zum Beispiel JavaScript oder PHP kommt, muss man dies besonders beachten.

Bildschirmaktualisierung aus- und einschaltenMakro/Sub/ProzedurTipp

Kategorien: Programmiertechnik ▸ Darstellung und Tabelle ▸ Selection

(Tipp 109) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich erreichen, daÃ? ein Makro nicht alle einzelnen Schritte anzeigt?

Dazu kann die Bildschirmaktualisierung (das ScreenUpdating) ausgeschaltet werden:

Application.ScreenUpdating = False

Bildschirmaktualisierung einschalten:

Application.ScreenUpdating = True

Das Ausschalten der Bildschirmaktualisierung hat auch immer ein paar Risiken, denn das Excelfenster ist ja dann â??eingefrorenâ??. Kommt es zu einem Fehler durch den Code, bleibt das Fenster auch eingefroren - der Anwender sieht dann schlicht keine Veränderungen mehr. Deshalb ist empfehlenswert, durch objektorientiertes Arbeiten (also Elemente direkt ansprechen, Verzicht auf .Select und .Activate) dafür zu sorgen, dass der Bildschirm nicht zappelt.

Soll die Aktualisierung trotzdem ausgeschaltet werden (weil vielleicht die Laufzeit kürzer wird), sollten Fehler abgefangen und per Sprungmarke am Ende des Codes die Bildschirmaktualisierung wieder eingeschaltet werden. Im einfachsten Fall wäre das so möglich:

On Error GoTo FEHLER â?¦ Code â?¦ FEHLER: Application.ScreenUpdating = True

Günstig ist auch, am Anfang den Status der Eigenschaft abzufragen und sie am Ende wieder so zu setzen, wie sie am Anfang war:

Sub MeinMakro() Dim bolAktScrUpd As Boolean bolAktScrUpd = Application.ScreenUpdating â?¦ Code â?¦ Application.ScreenUpdating = bolAktScrUpd End Sub

ChartereignisseMakro/Sub/ProzedurTipp

Kategorien: Basics ▸ Ereignisse und Ereignisse ▸ Basics

(Tipp 101) Nachricht zum Beitrag an Autor Nach oben

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

Anlegen des Klassenmoduls:

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

Public WithEvents Diagramm As Chart

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

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

Parameter:

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

Chartereignisse:

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


Code per Makro ersetzenMakro/Sub/Prozedur

Kategorie: VBA

(Tipp 103) Nachricht zum Beitrag an Autor Nach oben

Wie kann man per Makro den Code ändern? Im Modul2 soll die Zeile Windows("Formulare").Activate ersetzt werden durch Windows("Formulare.xlsx").Activate.

Im Modul2 soll die Zeile Windows("Formulare").Activate ersetzt werden durch Windows("Formulare.xls").Activate.

Sub ZeileInCodeErsetzen() Dim strSuchtext As String, strNeuertext As String Dim intI As Integer strSuchtext = "Windows(" & """" & "Formulare" & """" & ").Activate" strNeuertext = "Windows(" & """" & "Formulare.xlsx" & """" & ").Activate" 'Jede Zeile in Modul2 wird durchsucht: With ActiveWorkbook.VBProject.VBComponents("Modul2").CodeModule For intI = 1 To .CountOfLines 'Wenn die Zeile gleich dem Suchtext ist, ... If .Lines(intI, 1) = strSuchtext Then '... Zeile löschen: .DeleteLines intI '... neue Zeile einfügen: .InsertLines intI, strNeuertext End If Next 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:

Datei verdeckt öffnenMakro/Sub/Prozedur

Kategorie: Dateien und Ordner ▸ Dateioperation

(Tipp 16) Nachricht zum Beitrag an Autor Nach oben

Wie öffnet man eine Exeldatei ohne Bildschirmflackern?

Mit Application.ScreenUpdating = False kann ausgeschaltet werden, dass der Excel-Bildschirm aktualisiert wird. Wenn oft automatisiert mit Zellen gearbeitet wird, kann das sinnvoll sein - einerseits flackert es dann nicht so, andererseits wird die Laufzeit teilweise erheblich verkürzt, wenn nicht sofort alle �nderungen angezeigt werden.

Allerdings sollte nicht vergessen werden, diese Aktualisierung wieder einzuschalten. Vergisst man dies, ist der Bildschirm eventuell an wichtiger Stelle â??eingefrorenâ??. Man kann das zwar einfach mit Application.ScreenUpdating = True beheben - wenn es jedoch einem anderen Anwender passiert, kann das mächtig irritieren.


Dim strDateiPfad As String strDateiPfad = "C:\eigene dateien\test.xlsx" Application.ScreenUpdating = False Workbooks.Open Filename:=strDateiPfad ThisWorkbook.Activate Application.ScreenUpdating = True

Sinnvoll kann es auch sein, vor der Ausführung des gewünschten Codes die Eigenschaft in einer Variablen zu speichern, wie z. B. bolScrUpd = Application.ScreenUpdating. Nach dem relevanten Code setzt man das dann auf den Ausgangszustand mit Application.ScreenUpdating = bolScrUpd zurück.

Integrierte Dialogfelder aufrufenMakro/Sub/Prozedur

Kategorie: Interaktion ▸ Dialoge

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

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

Application.Dialogs(Konstante).Show

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

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

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

Download: integrierte_dialogfelder.xlsm

Menüleiste aktivierenMakro/Sub/Prozedur

Kategorie: Menü ▸ Veraltet

(Tipp 49) Nachricht zum Beitrag an Autor Nach oben

Wie kann man die Menüleiste mit dem Namen "MeineLeiste" aktivieren?

Sub MenueleisteAnzeigen() MenuBars("MeineLeiste").Activate End Sub