Übersicht aller Excel-Makros

Aus technischen Gründen haben wir alle Excel-Makros in dieser Liste vereinigt. Sie können diese somit komplett ausdrucken und nach und nach ausprobieren. Sie können den Quellcode auch einfach markieren, kopieren und in ein Excel-Modul einfügen.

Allgemeine Hinweise zu den Makros


 
Um die erstellten Makros in EXCEL schnell ausführen zu können, ist es möglich eine Tastenkombination zu hinterlegen: Über das Menü Extras /Makro/Makros... wählen Sie in der sich öffnenden Dialogbox die Schaltfläche Optionen... und geben unter Tastenkombination: Strg + eine noch nicht belegte Taste an.
 
Um Ihr Makro in allen EXCEL-Tabellen ausführen zu können ist es notwendig, eine Arbeitsmappe "PERSONL.XLS" anzulegen und in dem EXCEL-Startverzeichnis XLSTART zu speichern. Diese Datei wird bei jedem EXCEL-Programmstart mitgeladen, kann aber im Menü Fenster/Ausblenden in den Hintergrund verabschiedet werden. Beim Anlegen eines Makros muß dann nur noch in dem Dialogfeld Makro die Datei PERSONL.XLS als Speicherort ("Makro in:") gewählt werden.
 
Um ein Makro über eine Schaltfläche aufrufen zu können, muss eine neue Schaltfläche definiert und das Makro dieser zugewiesen werden. Ab EXCEL 2000 kann über das Menü Ansicht/Symbolleisten/Anpassen... die Dialogbox Anpassen gewählt und im Register Befehle eine beliebige Schaltfläche durch ziehen mit der Maus plaziert werden. Mit der rechten Maustaste kann im Kontextmenü ein Name vergeben, das Schaltflächensymbol geändert, selbst definiert und das Makro zugewiesen werden.
 
Ab Excel 2010 und Excel 2007 müssen Sie die Option, um Makros aufzurufen und zu bearbeiten erst im Programm einschalten. Excel bekommt dann ein neues Register in der Multifunktionsleiste. Das heißt ENTWICKLERTOOLS und wird rechts neben ANSICHT dargestellt. Um dieses Register einzublenden, rufen Sie das Register DATEI auf, wenn Sie Excel 2010 verwenden oder klicken Sie die Office-Schaltfläche an, wenn Sie Excel 2007 einsetzen. Wählen Sie die Schaltfläche OPTIONEN (Excel 2010) oder EXCEL-OPTIONEN (Excel 2007) und aktivieren Sie in der Liste links den Eintrag HÄUFIG VERWENDET. Schalten Sie nun rechts die Einstellung ENTWICKLERREGISTERKARTE IN DER MULTIFUNKTIONSLEISTE ANZEIGEN ein und schließen Sie das Dialogfenster wieder. Die Registerkarte bleibt so lange eingeblendet, bis Sie diese wieder in dem Menü abschalten.
 
Bei Fragen, Kritik oder Anregungen zu diesen Hinweisen schreiben Sie uns: [Formular]



 
Makro 1: Seite einrichten und Fußzeile mit Pfadangabe, Datum und Namen versehen

Dieses Makro läßt sich prima in die Menüleiste einbinden, um mit einem Klick jede neue Tabelle mit den persönlichen Einstellungen und dem eigenen Namen einzurichten. In Zeile 11 den eigenen Namen ersetzen und Makro in der persönlichen Arbeitsmappe im Hintergrund speichern. Alle Angaben wie z.B. die Seitenränder können direkt im Quelltext angepaßt werden.
 
Sub Fußzeile()
With ActiveSheet.PageSetup
 .PrintTitleRows = ""
 .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$2:$G$24"
With ActiveSheet.PageSetup
 .LeftHeader = ""
 .CenterHeader = ""
 .RightHeader = ""
 .LeftFooter = "&8 Name / &N / &B / &D"
 .CenterFooter = ""
 .RightFooter = ""
 .LeftMargin = Application.InchesToPoints(0)
 .RightMargin = Application.InchesToPoints(0)
 .TopMargin = Application.InchesToPoints(0)
 .BottomMargin = Application.InchesToPoints(0.4)
 .HeaderMargin = Application.InchesToPoints(0)
 .FooterMargin = Application.InchesToPoints(0)
 .PrintHeadings = False
 .PrintGridlines = False
 .PrintNotes = False
 .CenterHorizontally = True
 .CenterVertically = True
 .Orientation = xlLandscape
 .Draft = False
 .PaperSize = xlPaperA4
 .FirstPageNumber = xlAutomatic
 .Order = xlDownThenOver
 .BlackAndWhite = False
 .Zoom = False
 .FitToPagesWide = 1
 .FitToPagesTall = 1
End With
End Sub



 
Makro 2: Inhalte markierter Zellen unsichtbar machen

Dieses Makro läßt sich prima in die Menüleiste oder in das Kontextmenü (rechte Maustaste) einbinden, um mit einem Klick den Inhalt der zuvor markierten Zellen unsichtbar zu machen (z.B. Berechnungen/Hilfszellen bei Präsentationen). Makro in der persönlichen Arbeitsmappe im Hintergrund speichern und zuweisen.
 
Sub Unsichtbar()
  Selection.NumberFormat = ";;;"
End Sub



 
Makro 3: Format der aktuellen Zellen ändern in "#,##0"

Dieses Makro ist gut im Kontextmenü (rechte Maustaste) oder auch mit Icon versehen in der Iconleiste untergebracht, um mit einem Klick den Inhalt der aktiven Zellen mit dem definierten Zahlenformat zu formatieren. Makro in der persönlichen Arbeitsmappe im Hintergrund speichern und zuweisen.
 
Sub Tausender()
  Selection.NumberFormat = "#,##0"
End Sub



 
Makro 4: Format der aktuellen Zellen ändern in "#,##0.00" (im Wechsel zu 3.)

Dieses Makro ist im Wechselspiel mit Makro 3 ebenfalls gut im Kontextmenü (rechte Maustaste) oder auch mit Icon versehen in der Iconleiste untergebracht, um mit einem Klick den Inhalt der aktiven Zellen mit dem definierten Zahlenformat zu formatieren. Makro in der persönlichen Arbeitsmappe im Hintergrund speichern und zuweisen.
 
Sub TausenderNachkomma()
  Selection.NumberFormat = "#,##0.00"
End Sub



 
Makro 5: Externes Programm aus EXCEL heraus starten

Mit diesem Makro können Sie jede beliebige Anwendung aus EXCEL heraus starten; der Pfad ist beliebig abänderbar; voreingestellt ist die Zeichentabelle. Weitere Beispiele können der Taschenrechner (calc.exe), der Explorer (explorer.exe), der Editor (notepad.exe) oder auch Solitär (sol.exe) im selben Windows-Verzeichnis sein.
 
Sub ext_Prog_oeffnen()
 Status = Shell("C:\Windows\charmap.exe", 1)
End Sub



 
Makro 6: Anzahl der EXCEL-Dateien in einem bestimmten Verzeichnis anzeigen

Diese Makro zählt die Dateien (voreingestellt xls-Dateien unter Filename) im angegebenen Pfad (voreingestellt "C:\excel\dateien"). Es öffnet sich eine Dialogbox mit der Anzahl der entsprechenden Dateien.
 
Sub Anzahl_Dateien_in_Verzeichnis()
 With Application.FileSearch
  .NewSearch
  .LookIn = "C:\excel\dateien"
  .Filename = "*.xls"
  .Execute
  MsgBox .FoundFiles.Count
 End With
End Sub



 
Makro 7: Aktuelles Tagesdatum und Dateiname inkl. Speicherpfad in aktiver Zelle anzeigen

Diese Makro zeigt in der gewählten Zelle das aktuelle Tagesdatum und den Dateinamen inkl. Speicherpfad an und hilft so z.B. ausgedruckte Excel-Sheets in den Niederungen des Netzwerkes wiederzufinden. (eingesandt von Sven Maier)
 
Sub Dateiname_anzeigen()
 wunsch = InputBox("Wie groß soll der Pfad angezeigt werden? (in Punkten)")
 ActiveCell.FormulaR1C1 =
  "=TEXT(TODAY(),""TT.MM.JJJJ"")&"" ""&CELL(""dateiname"",R1C1)"
 With Selection
  .HorizontalAlignment = xlLeft
  .VerticalAlignment = xlBottom
  .WrapText = False
  .Orientation = 0
  .ShrinkToFit = False
  .MergeCells = False
 End With
 With Selection.Font
  .Name = "Arial"
  .Size = wunsch
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ColorIndex = xlAutomatic
 End With
End Sub



 
Makro 8: Anzeigen einer Dialogbox, wenn eine Datei geöffnet wird

Diese Makro zeigt beim Öffnen der Datei eine Dialogbox mit einem freiwählbaren Informationstext für z.B. andere Anwender an. Die Prozedur Auto_Open wird automatisch bei jedem Start ausgeführt (wenn Makros aktiviert sind).
 
Sub Auto_open()
   MsgBox ("Info-Text: Letztes Änderungsdatum, Telefonnummer für Rückfragen o.ä.")
End Sub



 
Makro 9: Datei per Makro öffnen und Dialogbox "Speichern unter..." anzeigen

Dieses Makro öffnet die Datei "Formular.xls" und blendet sodann die Dialogbox "Speichern unter..." ein. Der (fremde) Nutzer kann sich somit eine Kopie des Originals abspeichern und diese bearbeiten. Sehr nützlich bei Original-Formularen, welche allen Mitarbeitern zugänglich sind. (Kann man auch schick mit der Dialogbox aus Tipp 8 kombinieren.) Wichtig: Name und Pfad der Datei sind unbedingt anzupassen. (Idee von mehreren Mitgliedern der CS eingesandt)
 
Sub Speichern_unter()
    Workbooks.Open Filename:="C:\Formular.xls"
    Application.GetSaveAsFilename
End Sub



 
Makro 10: Alle Dateien aus einem Verzeichnis in EXCEL ausgeben

Dieses Makro gibt alle Dateien eines bestimmten Verzeichnisses in einer Exceltabelle aus. In der Zelle A1 kann der gewünschte Verzeichnis-Pfad eingegeben werden, ansonsten wird das aktuelle Verzeichnis durchsucht. Die Ausgabe kann in der aktuellen Zelle oder auch wahlweise in einer bestimmten Zelle vorgesehen werden. (von Martin Haecki)
 
Sub Dateiverzeichnis()
' Listet alle Dateien aus einem Verzeichnis
   Pfad = Range("A1")   ' Zelle mit dem Start-Pfad (hier A1)
    With Application.FileSearch   ' Alle Dateien suchen
     .NewSearch
     .LookIn = Pfad
     .FileName = "*.*"
     .Execute
     For i = 1 To .FoundFiles.Count
      ' Dateien ab aktuellem Cursor in Spalten einfügen
      ActiveCell.Offset(1, 0).Activate
      ActiveCell = .FoundFiles(i)
      ' oder wahlweise in spezifizierte Zellen
      ' Cells(1 + i, 1) = .FoundFiles(i)
     Next
     ' Wahlweise: Ersetzt den Pfad durch nichts (im ganzen Sheet)
      Cells.Replace What:=Pfad, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows,
         MatchCase:=False
    End With
End Sub



 
Makro 11: Farbe von Zelle und/oder Schrift ändern (z.B. per ShortCut)

Dieses Makro ändert in allen makierten Zellen eines Tabellenblattes die Farbe der Zelle sowie die Farbe der Schrift. In dem Beispielcode wird die Zelle gelb (6) und die Schrift rot (3) gefärbt. Mit der Hinterlegung einer Tastenkombination kommt man so bei häufigem Färben schnell voran.
 
Sub FarbigeZellen()
' ändert die Farbe von Zelle und Schrift
     Selection.Interior.ColorIndex = 6
     Selection.Font.ColorIndex = 3
End Sub



 
Makro 12: Formeln per Shortcut als Werte einfügen (Inhalte einfügen ... / Werte)

Mit diesem Makro können innerhalb der markierten Zellen eines Tabellenblattes die Formeln in den Zellen in Werte umgewandelt werden. Analog zum Kontext-Menüpunkt "Inhalte einfügen... / Werte" kann hierbei mit der Hinterlegung einer Tastenkombination auf z.B. Strg+b per Tastatur schnell das gewünschte Ergebnis erzielt werden.
 
Sub InhalteEinfügen()
' Inhalte Einfügen Werte per Shortcut
' Tastenkombination: Strg+b
     Selection.Copy
     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
        SkipBlanks:=False, Transpose:=False
End Sub



 
Makro 13: Zoom mit Intelli-Mouse umschalten

Hier ein Makro, wie es für Controller sehr nützlich sein kann: Den Zoom mit einer Intelli-Mouse ein- und ausschalten - per Button in einer Symbolleiste. So kann man, ohne umständlich über Menü und Optionen gehen zu müssen zwischen Scroll- und Zoom-Modus hin- und herschalten; praktisch bei großen Charts mit Text, bei denen die Präsentation mitberücksichtigt werden soll oder auch zum schnellen Springen im Sheet. Der Button zeigt den aktuellen Status der Funktion an. Man muss bloß eine neue Symbolleiste mit einem neuen Button versehen, die Namen im Makro entsprechend anpassen (2. Zeile) und das Makro dieser Schaltfläche zuweisen. (von: Markus DeInka)
 
Sub Zoom_mit_Intelli()
 Set zibtn = CommandBars("Name_Symbolleiste").Controls("Name_Button")
 If Application.RollZoom = True Then
    Application.RollZoom = False
   Else: Application.RollZoom = True
 End If
 With zibtn
   If Application.RollZoom = True Then
     .State = msoButtonDown
    Else: .State = msoButtonUp
   End If
 End With
End Sub



 
Makro 14: Formeln durch Ergebnisse ersetzen

Bei langen Formelreihen kann das Neuberechnen der bereits eingegebenen Formeln recht lange dauern. Bei z.B. monatlich aktualisierten Tabellenblättern hat man die Daten auch oft nicht mehr an der Originalstelle auf die sich die Formeln beziehen. Dies führt zu einem Verlust dieser Werte. Die Formeln kann man durch Werte ersetzen --> Formeln markieren, Bearbeiten - Kopieren, dann Bearbeiten - Inhalte einfügen - Optionen: "Werte". Dies kann man auch bequem durch ein Makro erledigen:
 
Sub ChangeInValue()
' Ersetzt Formeln durch ihre Werte
' Voraussetzung: Formelzellen markiert
' Auswahl kopieren
Selection.Copy
' Kopierte Zellen einfügen, jedoch nur die Werte (xlValues) Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
   False, Transpose:=False
' Kopieren-Modus ausschalten
Application.CutCopyMode = False
' Abschluss-Meldung (optional)
MsgBox "Formeln in " & Selection.Cells.Count & " Zellen durch ihren Wert ersetzt!"
End Sub
 
Den Code kann man in die jeweilige Arbeitsmappe, in die persönliche Arbeitsmappe (PERSONL.XLS) oder in ein Add-In (*.XLA) als Modul speichern. (von: Christian Schühly)



 
Makro 15: Inhalte einfügen.../nur Werte

Die Anwendung des häufig verwendeten Menübefehls “Inhalte einfügen.../Werte“ kann durch ein Makro noch vereinfacht werden: Durch den einfachen Befehl “kopieren“ wird ja bekanntlich nicht nur der Zellwert, sondern auch die Formatierung der Zelle übernommen. Mit dem Menübefehl “Bearbeiten / Inhalte einfügen“ und der Menüoption “Werte“, werden jedoch lediglich die Werte eingetragen, aber die Formatierung nicht übernommen. Um diesen Ablauf zu beschleunigen können Sie sich ein Makro erzeugen, das mit einer Tastenkombination aufgerufen werden kann. Dabei gehen Sie so vor:
1. Schreiben Sie eine beliebige Zahl in eine Zelle und geben Sie dieser z.B. ein Währungsformat.
 
2. Markieren Sie diese Zahl und kopieren sie in die Zwischenablage, z. B. mit Bearbeiten/Kopieren.
3. Nun klicken Sie die Zielzelle an, in die der Wert eingefügt werden soll.
4. Starten Sie den Makrorecorder mit dem Menüpunkt “Extras/Makro/Aufzeichnen...“
5. Im nun erscheinenden Fenster nehmen Sie folgende Einstellungen vor: Vergeben Sie einen Namen für das Makro und eine Tastenkombination (z.B. die V-Taste). Wenn Sie bei Makro speichern in die Option Persönliche Makroarbeitsmappe wählen, steht Ihnen der Befehl für alle Mappen zur Verfügung.
6. Schließen Sie Ihre Eingaben mit [OK] ab.
7. Jetzt fügen Sie den Wert (siehe oben) mit dem Menübefehl: “Bearbeiten/Inhalte einfügen“ und der Option "Werte" ein.
8. Die Makroaufzeichnung beenden Sie mit der Schaltfläche Aufzeichnung beenden in der gleichnamigen Symbolleiste.
 
Wenn Sie künftig nun eine Zelle oder einen Zellbereich kopiert haben, können Sie im Zielbereich den Wert ohne die Formatierung mit der Tastenkombination Strg + Umschalttaste + V einfügen. Das geht viel schneller als den Menübefehl zu benutzen. (von: Christian Schühly)



 
Makro 16: Text in Zahlenwerte umwandeln

Nach einem Textimport tritt oft (eigentlich fast immer) das Problem auf, dass Zahlen nicht als Zahlen sondern als Text erkannt werden. Ist dies der Fall, kann mit diesen Zahlen nicht gerechnet werden. Abhilfe schafft, wenn man die Zelle mit F2 aufruft und mit Enter wieder bestätigt. Das folgende Makro löst das Problem und automatisiert es:
 
Public Sub Test_f2()
'Alle Werte in Zahlenwerte umwandeln
Dim zelle2 as Object
Sheets(1).Range("A1:C50").select
For Each zelle2 In Selection
SendKeys "{F2}", True
SendKeys "{ENTER}", True
Next zelle2
End Sub
(von: Christian Schühly)



 
Makro 17: Fenster per Makro anordnen (um Werte zu vergleichen)

Immer wieder kommt es vor, dass man Werte innerhalb einer Datei miteinander vergleichen möchte und dann zwischen zwei Tabellenblättern hin- und herspringt. Über das Fenster-Menü kann man ein "Neues Fenster" öffnen und beide dann zum Beispiel horizontal "anordnen". Das folgende Makro automatisiert dies. Fenster können dann durch Zuweisung einer Tastenkombination schnell angeordnet werden:
 
Sub FensterAnordnen()
ActiveWindow.NewWindow
Windows.Arrange ArrangeStyle:=xlHorizontal
End Sub
 
(von: Jan B.)



 
Makro 18: Alle ausgeblendeten Tabellenblätter einblenden

Manchmal ist es sinnvoll, Tabellenblätter aus Gründen der Übersichtlichkeit auszublenden oder auch bestimmte Daten anderen Usern der Datei erst gar nicht zu zeigen. Sind dann Änderungen an den Tabellenblättern von Nöten, muss man diese wieder einblenden. Das geht sicherlich für jedes Blatt einzeln recht schnell auch mit Maus und Menüführung, kann aber für eine große Anzahl Blätter schnell zur Qual werden. Das folgende Makro blendet alle ausgeblendeten Blätter aufeinmal wieder ein. Hinterlegen Sie für das Makro einen Tastatur-shortcut oder weisen Sie es einem Icon zu, geht das dann mit nur einem Klick.
 
Sub Einblenden()
Dim Blatt As Object
For Each Blatt In Worksheets
Blatt.Visible = True
Next Blatt
End Sub
 
(von: Sandra W.)



 
Makro 19: Neuberechnung von Zellen per Makro ein- und ausschalten

Die Neuberechnung von Zellen erledigt Excel standardmäßig automatisch nach jedem Verlassen einer Zelle. In manchen Situationen, z.B. bei vielen sehr komplexen Formeln, kann es jedoch ratsam sein, die automatische Neuberechnung auf eine manuelle umzustellen und dann erst zu einem bestimmten Zeitpunkt sämtliche Zellen neu berechnen zu lassen. Hierzu kann man sicherlich jeweils in die Excel-Optionen navigieren und auf die manuelle Berechnung umstellen. Schneller geht es jedoch mit einem Makro, insbesondere wenn man eine Tastenkombination für den schnellen Zugriff hinterlegt. Mit dem folgenden Makro schaltet man die Berechnung aus:
 
Sub NeuberechnungAusschalten()
With Application
.CalculateBeforeSave = True
.Calculation = xlCalculationManual
End With
End Sub
 
Das Makro schaltet die Neuberechnung in den manuellen Modus. Anschließend kann man mit Hilfe der Taste F9 eine Neuberechnung gezielt zu einem gewünschten Zeitpunkt starten. Das Makro sorgt auch dafür, dass vor dem Speichern der Arbeitsmappe immer eine Neuberechnung erfolgt. Dies erledigt die Zeile .CalculateBeforeSave = True
Die Neuberechnung kann durch das folgende Makro wieder in den automatischen Modus zurückgeschaltet werden:
 
Sub NeuberechnungEinschalten()
With Application
.CalculateBeforeSave = True
.Calculation = xlCalculationAutomatic
End With
End Sub
 
(von: Josh S.)



Schauen Sie auch in unseren Excel-Tipps und -Tricks nach arbeitsvereinfachenden Möglichkeiten für Ihr Controlling.

© 2001/2017 ControllerSpielwiese.de powered by Joachim Becker WebSolutions
Gerne können Sie bei uns werben.