Hier finden Sie Makros und Tipps für Excel, zusammengetragen aus den Rubriken verschiedener Fach-Zeitschriften
» [1][2][3]

vereinfachte Eingabe am Nummernblock bei Uhrzeiten
Alternative zum Doppelpunkt
'Alternative zum Doppelpunkt, im Beispiel wird die Differenz der Uhrzeiten aus Spalte A+B berechnet
entweder mittels Autokorrektur anstelle eines Doppelpunktes z.B. zwei Kommas eingeben.
oder
mit mittels benutzerdefinierten Zellenformatierung - ##":"## für die Anzeige. Die Eingabe erfolgt dann fortlaufend mit 800 für 8:00Uhr.
Für die Weiterberechung muß jedoch der Text noch in Werte umgewandelt werden =WERT(TEXT(B1;"00"":""00"))-WERT(TEXT(A1;"00"":""00")).
Bilder in Zell-Kommentaren einfügen
kommentierte Texte mit einer Grafik unterlegen
wenn Sie exakt auf den markierten Rand des Kommentars klicken (und nur dann!), erscheint im Kontextmenü der Punkt "Kommentar formatieren". Anschließend kann auf der Registerbox Farben und Linien in der Auswahlbox Ausfüllen - Farbe über Fülleffekte eine Grafik eingefügt werden.
Zellinhalte aufteilen mit Textkonvertierungsassistent
'zuerst rechts neben der Spalte genügend Platz schaffen zum Aufteilen. Anschließend die Quelldaten markieren und in der Menüleiste unter Daten - Text in Spalten - den Assistent aufrufen. Hier geben Sie die vorhandenen Trennzeichen ein.
Achtung: Excel überschreibt die Quelldaten!
Gross- und Kleinschreibung im markierten Bereich ändern
per Makroaufruf wählen: alles Kleinbuchstaben - alles Großbuchstaben oder nur Anfangsbuchstaben groß
'Bei jedem Aufruf des Makroses wird der Text im markierten Bereich erneut geändert - alles Kleinbuchstaben - alles Großbuchstaben - nur Anfangsbuchstaben groß.

Sub GrossKleinSchalter()
Dim objZelle As Range
For Each objZelle In Selection.Cells
Select Case True
Case objZelle = LCase(objZelle)
objZelle = UCase(objZelle)
Case objZelle = UCase(objZelle)
objZelle = Application.Proper(objZelle)
Case Else
objZelle = LCase(objZelle)
End Select
Next objZelle
End Sub

Benannte Bereiche dynamisch anpassen

'in der Menüzeile unter Einfügen - Namen - Definieren wählen. Hier geben Sie Ihren gewünschten Bereichsnamen ein. Unter 'Bereich bezieht sich auf' nachfolgende Funktion eingeben.

=Bereich.Verschieben($D$100;;;Anzahl2($D:$D);Anzahl2($100:$100))

Für diese Funktion die linke obere Ecke des Bereichs eingeben. Im Beispiel beginnt der Datenbereich in Zelle D100 und wird in beide Richtungen angepaßt.

Farben von Excel auflisten
und entsprechenden Farbindex ermitteln
'mit folgendem Makro werden die installierten Farben in Excel gelistet mit dem dazugehörenden Farbindex:

Sub Farbtabelle()
Dim bfarbe As Byte
For bfarbe = 2 To 57
If bfarbe < 30 Then
Cells(bfarbe, 1) = bfarbe - 1
Cells(bfarbe, 2).Interior.ColorIndex = bfarbe - 1
Cells(bfarbe, 3).Font.ColorIndex = bfarbe - 1
Cells(bfarbe, 3) = bfarbe - 1
Else
Cells(bfarbe - 28, 5) = bfarbe - 1
Cells(bfarbe - 28, 6).Interior.ColorIndex = bfarbe - 1
Cells(bfarbe - 28, 7).Font.ColorIndex = bfarbe - 1
Cells(bfarbe - 28, 7) = bfarbe - 1
End If
Next bfarbe
End Sub

Sortierreihenfolge nach eigener Liste
z.B. nach Größenangaben S M L XL XXL
'wenn Sie z.B. nach Konfektionsgrößen sortieren möchten, legen Sie zuerst eine entsprechende Tabelle an:
Öffnen Sie ein leeres Tabellenblatt und geben Sie die gewünschte Wertereihe in untereinander stehenden Zellen in passender Reihenfolge ein, markieren anschließend die gesamte Wertereihe, wählen im Menü Extras-Optionen auf der Registerkarte AutoAusfüllen (Excel 97/2000) bzw. Benutzerdefinierte Listen (Excel 2002/XP und 2003) die Schaltfläche importieren. Excel nimmt die neue Liste daraufhin in das linke Listenfeld des Dialogfelds auf und zeigt die einzelnen Elemente in der rechten Liste. Sie brauchen das Ergebnis nur noch mit Ok zu bestätigen.

Zum sortieren wählen Sie den gewünschten Bereich, im Menü Daten-Sortierung wählen Sie die gewünschte Spalte, unter Optionen kann per Dropdownfeld die gewüschte Sortierreihenfolge eingestellt werden.

Doppelte Einträge automatisch verhindern

die zu überwachende Spalte (im Beispiel "A") markieren, im Menü unter Daten-Gültigkeit in der Dropdown-Liste Zulassen den Eintrag "benutzerdefiniert" wählen und im Formelfeld folgenden Ausdruck eingeben:
=Vergleich(A1;$A:$A;0)=Zeile(A1)
anschließend im Register Fehlermeldung das gewünsche Aussehen der Dialogbox angeben, die bei einem doppelten Eintrag erscheinen soll.

Dies funktioniert nur, wenn die neu eingegebene Zahl unten eingegeben wird.
Bei Änderungen der bestehenden Eingabe oder an beliebigen Positionen folgende Formel verwenden:
=Zählenwenn($A:$A;A1)<2

Eingaben in festgelegten Bereichen nicht möglich
'eine Markierung bzw. Eintragung in Spalte 3 ist in folgendem Beispiel nur dann möglich, wenn in D1 "OK" steht.

Private Sub Worksheet_SelectionChange(ByVal Target _
As Range)
Application.EnableEvents = False
If Target.Column = 3 Then
If Range("D1").Value <> "OK" Then
Range("A1").Activate
End If
End If
Application.EnableEvents = True
End Sub

Scrollbereich festlegen
'mit folgendem Autostart-Makro wird ein ungeschützter Bereich festgelegt. Außerhalb des Bereichs sind keine Änderungen oder Markierungen mehr möglich.

Private Sub Workbook_Open()
Worksheets(1).ScrollArea = "A1:D25"
End Sub

Leerzellen einer Liste nachträglich ausfüllen

Den Bereich markieren, dessen Leerzellen ausgefüllt werden sollen. In der Menüleiste "Bearbeiten - Gehe zu" wählen, auf die Schaltfläche "Inhalte" klicken und die Option "Leerzellen" auswählen. Jetzt das Gleichheitszeichen eingeben, die Zelladresse oberhalb der ersten Leerzelle eingeben und mit STRG und Return abschließen. Excel füllt nun alle Leerzellen mit dem jeweils darüberliegenden Zellinhalt aus.

Tabellenblätter nach Farben sortieren
ab Excel 2002 können Blattregister farbig unterlegt werden. Per Makro nach Farbe sortieren
Sub Farbsortierung()
Dim i As Long, j As Long
For i = 1 To Worksheets.Count - 1
For j = i To Worksheets.Count
If Worksheets(j).Tab.ColorIndex = _
Worksheets(i).Tab.ColorIndex Then
Worksheets(j).Move After:=Worksheets(i)
End If
Next j
Next i
End Sub
Kombinationsfelder mit Inhalt anzeigen
mit zusätzlicher Formel den ausgewählten Listeneintrag und nicht die Position des Listeneintrags anzeigen. Per Makro gehts auch ohne Umweg.
'Den ausgewählten Listeneintrag (und nicht die Position des Listeneintrags) erhalten Sie mit der zusätzlichen Formel =Index(A1:A10;B1). Soll der Eintrag direkt ausgegeben werden, dem Kombifeld zusätzlich folgendes Makro zuweisen:

Sub dropdown1_BeiÄnderung()
If IsNumeric(Cells(1, 2).Value) = True Then
listwert = Tabelle1.Cells(Cells(1, 2).Value, 1).Value
Tabelle1.Cells(1, 2).Value = listwert
End If
End Sub

Suchen und ersetzen
Dieses Tool aus der PC-Welt sucht und ersetzt angegebene Wörter in allen Excel-Tabellen (per Doppelklick) eines Ordners. Ab Excel-Version 2000
(5KB)
Kontextmenü nach eigenen Angaben erweitern
'folgende Makros in Sub Auto_open bzw. Sub Auto_close umbenennen, wenn das angepaßte Menü nur für die vorliegende Datei gelten soll und nicht als Add-In eingebunden wird.

Sub AddContextCmd()
Dim cb As CommandBar
Dim ctl As CommandBarControl
Set cb = CommandBars("Cell")
Set ctl = cb.Controls.Add()
With ctl
.Caption = "<angezeigter Befehlsname>"
.OnAction = "<Name des auszuführenden Makros>"
End With
End Sub

Sub DelContextCmd()
Dim cb As CommandBar
Dim ctl As CommandBarControl
On Error Resume Next
Set cb = CommandBars("Cell")
Set ctl = cb.Controls("<angezeigter Befehlsname>")
ctl.Delete
End Sub

Zellinhalt nach dem Komma ausrichten
'mit folgendem Format werden verschieden lange Anzeigen am Komma ausgerichtet. Sind mehr als 3 Stellen nach dem Komma möglich, weitere Fragezeichen einfügen.

??0,0??

Bruch einfügen
ohne die ungewollte Formatierung als Datum erreichen Sie die korrekte Anzeige mit einer 0 davor (z.B. 0 2/3)
Zelle einfärben per Doppelklick
'Im Visual-Basic-Editor wählen Sie das gewünschte Tabellenblatt aus, für welches das Ereignis gelten soll und fügen nachfolgendes Makro ein:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
End With
End Sub

Zieldatum errechnen
aus einem Datum und einer Anzahl von Tagen. Fügt das Ergebnis im langen oder kurzen Datumsformat in das Dokument oder die Zwischenablage ein.
Schaltflächentext von Zellinhalt übernehmen
Mittels VBA die Beschriftung einer Befehlsschaltfläche variabel gestalten
'Die Beschriftung der Schaltfläche ändert sich abhängig vom Inhalt der angegebenen Zelle, wenn die Schaltfläche mit Hilfe der Steuerelement-Toolbox eingefügt wird. Den Code der Schaltfläche anzeigen lassen, im linken Kombifeld =Worksheets auswählen und die eingefügten Zeilen durch folgende ersetzen:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
CommandButton1.caption=cells(1,1).value
End Sub
Markierte Zellbereiche in Euro umrechnen
'mit Änderung des Währungszeichens
Sub DM2Euro()
Dim C As Range
kurs = 1.95583
For Each C In Selection.Cells
If IsNumeric(C) Then
C = Application.Round(C / kurs, 2)
C.NumberFormat = "#,##0.00 [$€-1]"
End If
Next C
End Sub
Sonderzeichen auch in Excel anzeigen
Sub ZeichenTabelle()
Shell "charmap", 1
End Sub
jede 2.Zeile farbig unterlegen
mit der bedingten Formatierung
'kompletten Bereich markieren, in der Menüleiste unter Format die bedingte Formatierung aufrufen. Das gewünschte Format einstellen, unter Bedingung die Auswahl auf "Formel ist" ändern und folgende Formel eingeben (z.B. A1= ist die linke obere Ecke des markierten Bereichs):
=Zeile(A1)-Gerade(Zeile(A1))=0
Währungsformat linksbündig anzeigen
'mit folgendem Format wird das Währungsformat linksbündig und der Betrag rechtsbündig in der Zelle angezeigt:
das Währungssymbol in Anführungszeichen setzen, ein Sternchen, ein Leerzeichen und das gewünschte Zahlenformat, z.B.
"€"* #.##0,00
geschützte Zellen nicht mehr anwählbar
Cursorbewegung bei aktiviertem Blattschutz mit einem Makro einschränken
'Cursorbewegung bei aktiviertem Blattschutz auf ungeschützte Zellen beschränken
Private Sub Workbook_Open()
Worksheets("Tabelle1").EnableSelection = xlUnlockedCells
End Sub
oder für alle Tabellen in der Datei:
Sub auto_open()
Dim Sh As Worksheet
For Each Sh In Worksheets
Sh.Activate
With ActiveSheet
.EnableSelection = xlUnlockedCells
.Protect
End With
Next Sh
End Sub
Seitenanzahl anzeigen
mit einem Makro die Seitenanzahl in einer MSG-Box anzeigen lassen
Sub seitenanzahl()
Dim i As Integer
i = ExecuteExcel4Macro("get.document(50)")
MsgBox "Anzahl der Seiten = " & i
End Sub
Zelle mit aktuellen Datum
beim Start springt der Cursor automatisch in die Zelle des aktuellen Datums
'im Beispiel steht das Datum in Spalte A, durchsucht wird die Zeile 1 bis 500
Sub auto_open()
spalte = "A"
For Each a In ActiveSheet.Range(spalte & "1:" & spalte & "500")
If a = Date Then
zeile = a.Row
Exit For
End If
Next
On Error Resume Next
Application.Goto ActiveSheet.Cells(zeile, spalte), True
If Err.Number <> 0 Then
MsgBox ("Das aktuelle Datum wurde nicht gefunden.")
End If
End Sub
» [1][2][3]