Auflistung aller Excel-Makros der ControllerSpielwiese

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

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

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

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

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

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

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

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

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

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

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

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

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

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)

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)

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)

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

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

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

  zurück zur Ãœbersicht der Excel-Makros

nach oben