Ich möchte hier ein paar Funktionen und Prozeduren vorstellen, die mir beim Programmieren in Excel hilfreich sind:
Folgende Prozedur bewirkt, dass alle Zellen im Bereich quadratisch sind. Ist HöheBeibehalten False, wird die Breite beibehalten.
Folgende Funktion liefert die Mappe, die unter Dateipfad gespeichert ist. Ist die Mappe bereits offen, wird die geöffnete Mappe zurückgegeben.
Folgende Prozedur schließt die Mappe, die unter Dateipfad gespeichert ist, sofern sie geöffnet ist.
Folgende Prozedur speichert die Mappe unter Dateipfad, wobei eine evtl. bestehende Datei überschrieben wird. Sollte so eine Mappe bereits geöffnet sein, wird sie vorher mit DateiSchließen (s. o.) geschlossen. Bei Excel 2007 speichere ich meistens als „.xlsm“-Datei also als XML-Mappe mit Makros. Für ältere Excel-Versionen empfiehlt sich statt xlOpenXMLWorkbookMacroEnabled xlNormal.
Folgende Funktion erstellt eine neue Mappe und speichert sie mit obiger Prozedur DateiNeuSpeichern. Diese neue Mappe ist der Rückgabewert der Funktion.
Folgende Funktion liefert einen String, der die Werte im Bereich enthält.
Bei folgender Funktion wird in der UsedRange der Tabelle in der Suchspalte der Suchtext gesucht. Wird er gefunden, werden die Werte der entsprechenden Zeile als Array zurückgegeben. Wenn nicht, wird ein Array zurückgegeben, das nur das Element 0 enthält, welches leer ist.
Folgende Funktion fügt der Tabelle die Werte hinzu. Die Schlüsselspalte gibt dabei die Spalte mit dem Primärschlüssel an, also die Spalte, in der ein Wert nur einmal vorkommen darf. Gibt es keine solche Spalte, muss man 0 angeben.
Folgende Prozedur sortiert die UsedRange der Tabelle nach den Spalten. Ob auf- oder absteigend sortiert werden soll, wird durch das Vorzeichen der Spalte bestimmt.
Beispiel: sortiert die UsedRange von Tabelle1, die keine Überschriften hat, erst nach Spalte 3 aufsteigend, dann nach Spalte 1 aufsteigend, dann nach Spalte 5 absteigend und dann nach Spalte 4 aufsteigend.
Zu erwähnen wäre noch, dass ich es so eingestellt habe, dass Zahlen, die als Text formatiert sind, als Text behandelt werden (DataOption:=xlSortNormal) und dass Groß-/Kleinschreibung nicht beachtet wird (MatchCase:=False).
Version für Excel 2007:
Version für ältere Excel-Versionen:
Folgende Prozedur bewirkt, dass alle Zellen im Bereich quadratisch sind. Ist HöheBeibehalten False, wird die Breite beibehalten.
Visual Basic-Quellcode
- Sub QuadratischeZellen(Bereich As Range, Optional HöheBeibehalten As Boolean = True)
- Dim Zelle As Range
- For Each Zelle In Bereich
- If HöheBeibehalten Then
- While Zelle.Width <> Zelle.Height
- Zelle.ColumnWidth = Zelle.ColumnWidth / Zelle.Width * Zelle.Height
- Wend
- Else
- While Zelle.Width <> Zelle.Height
- Zelle.RowHeight = Zelle.RowHeight / Zelle.Height * Zelle.Width
- Wend
- End If
- Next
- End Sub
Folgende Funktion liefert die Mappe, die unter Dateipfad gespeichert ist. Ist die Mappe bereits offen, wird die geöffnete Mappe zurückgegeben.
Folgende Prozedur schließt die Mappe, die unter Dateipfad gespeichert ist, sofern sie geöffnet ist.
Folgende Prozedur speichert die Mappe unter Dateipfad, wobei eine evtl. bestehende Datei überschrieben wird. Sollte so eine Mappe bereits geöffnet sein, wird sie vorher mit DateiSchließen (s. o.) geschlossen. Bei Excel 2007 speichere ich meistens als „.xlsm“-Datei also als XML-Mappe mit Makros. Für ältere Excel-Versionen empfiehlt sich statt xlOpenXMLWorkbookMacroEnabled xlNormal.
Folgende Funktion erstellt eine neue Mappe und speichert sie mit obiger Prozedur DateiNeuSpeichern. Diese neue Mappe ist der Rückgabewert der Funktion.
Folgende Funktion liefert einen String, der die Werte im Bereich enthält.
Visual Basic-Quellcode
- Function RangeString(Bereich As Range, Zeilentrennung As String, Spaltentrennung As String, Optional Rahmen As Boolean = True) As String
- Dim z As Long, s As Integer
- With Bereich
- For z = 1 To .Rows.Count
- If z > 1 Then RangeString = RangeString & Zeilentrennung
- For s = 1 To .Columns.Count
- If s > 1 Then RangeString = RangeString & Spaltentrennung
- RangeString = RangeString & .Cells(z, s)
- Next
- Next
- End With
- If Rahmen Then RangeString = Zeilentrennung & RangeString & Zeilentrennung
- End Function
Bei folgender Funktion wird in der UsedRange der Tabelle in der Suchspalte der Suchtext gesucht. Wird er gefunden, werden die Werte der entsprechenden Zeile als Array zurückgegeben. Wenn nicht, wird ein Array zurückgegeben, das nur das Element 0 enthält, welches leer ist.
Visual Basic-Quellcode
- Function SuchArray(Tabelle As Worksheet, Suchtext As String, Optional Suchspalte As Integer = 1) As Variant()
- Dim Erg() As Variant, Bereich As Range, Zeile As Long, i As Integer
- ReDim Erg(0)
- Set Bereich = Tabelle.UsedRange
- If WorksheetFunction.CountIf(Bereich.Columns(Suchspalte), Suchtext) > 0 Then
- Zeile = WorksheetFunction.Match(Suchtext, Bereich.Columns(Suchspalte), 0)
- ReDim Erg(Bereich.Columns.Count)
- For i = 1 To Bereich.Columns.Count
- Erg(i) = Bereich.Cells(Zeile, i)
- Next
- End If
- SuchArray = Erg
- End Function
Folgende Funktion fügt der Tabelle die Werte hinzu. Die Schlüsselspalte gibt dabei die Spalte mit dem Primärschlüssel an, also die Spalte, in der ein Wert nur einmal vorkommen darf. Gibt es keine solche Spalte, muss man 0 angeben.
Visual Basic-Quellcode
- Function WerteHinzufügen(Tabelle As Worksheet, Schlüsselspalte As Integer, ParamArray Werte() As Variant) As Long
- Dim hinzufügen As Boolean, Zeile As Long, i As Integer
- If Schlüsselspalte = 0 Then
- hinzufügen = True
- Else
- hinzufügen = (WorksheetFunction.CountIf(Tabelle.Columns(Schlüsselspalte), Werte(Schlüsselspalte - 1)) = 0)
- End If
- If hinzufügen Then
- Zeile = Tabelle.UsedRange.Rows.Count + 1
- If Zeile = 2 Then If Tabelle.UsedRange.Cells.Count = 1 Then Zeile = 1
- For i = 0 To UBound(Werte)
- Tabelle.Cells(Zeile, i + 1) = Werte(i)
- Next
- WerteHinzufügen = Zeile
- End If
- End Function
Folgende Prozedur sortiert die UsedRange der Tabelle nach den Spalten. Ob auf- oder absteigend sortiert werden soll, wird durch das Vorzeichen der Spalte bestimmt.
Beispiel: sortiert die UsedRange von Tabelle1, die keine Überschriften hat, erst nach Spalte 3 aufsteigend, dann nach Spalte 1 aufsteigend, dann nach Spalte 5 absteigend und dann nach Spalte 4 aufsteigend.
Zu erwähnen wäre noch, dass ich es so eingestellt habe, dass Zahlen, die als Text formatiert sind, als Text behandelt werden (DataOption:=xlSortNormal) und dass Groß-/Kleinschreibung nicht beachtet wird (MatchCase:=False).
Version für Excel 2007:
Visual Basic-Quellcode
- Sub UsedRangeSort(Tabelle As Worksheet, Überschriften As Boolean, ParamArray Spalten() As Variant)
- Dim i As Integer, Bereich As Range
- Set Bereich = Tabelle.UsedRange
- With Tabelle.Sort
- .SortFields.Clear
- For i = 0 To UBound(Spalten)
- .SortFields.Add Key:=Bereich.Columns(Abs(Spalten(i))), SortOn:=xlSortOnValues, Order:=1.5 - Sgn(Spalten(i)) / 2, DataOption:=xlSortNormal
- Next
- .SetRange Bereich
- .Header = IIf(Überschriften, xlYes, xlNo)
- .MatchCase = False
- .Orientation = xlTopToBottom
- .SortMethod = xlPinYin
- .Apply
- End With
- End Sub
Version für ältere Excel-Versionen:
Visual Basic-Quellcode
- Sub UsedRangeSortAlt(Tabelle As Worksheet, Überschriften As Boolean, ParamArray Spalten() As Variant)
- Dim i As Integer, StartNr As Integer, Bereich As Range
- Set Bereich = Tabelle.UsedRange
- Select Case UBound(Spalten)
- Case 0
- Bereich.Sort _
- Key1:=Bereich.Cells(1 - Überschriften, Spalten(0)), Order1:=1.5 - Sgn(Spalten(0)) / 2, DataOption1:=xlSortNormal, _
- Header:=IIf(Überschriften, xlYes, xlNo), OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
- Case 1
- Bereich.Sort _
- Key1:=Bereich.Cells(1 - Überschriften, Spalten(0)), Order1:=1.5 - Sgn(Spalten(0)) / 2, DataOption1:=xlSortNormal, _
- Key2:=Bereich.Cells(1 - Überschriften, Spalten(1)), Order2:=1.5 - Sgn(Spalten(1)) / 2, DataOption2:=xlSortNormal, _
- Header:=IIf(Überschriften, xlYes, xlNo), OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
- Case 2
- Bereich.Sort _
- Key1:=Bereich.Cells(1 - Überschriften, Spalten(0)), Order1:=1.5 - Sgn(Spalten(0)) / 2, DataOption1:=xlSortNormal, _
- Key2:=Bereich.Cells(1 - Überschriften, Spalten(1)), Order2:=1.5 - Sgn(Spalten(1)) / 2, DataOption2:=xlSortNormal, _
- Key3:=Bereich.Cells(1 - Überschriften, Spalten(2)), Order3:=1.5 - Sgn(Spalten(2)) / 2, DataOption3:=xlSortNormal, _
- Header:=IIf(Überschriften, xlYes, xlNo), OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
- Case Else
- StartNr = (UBound(Spalten) \ 3) * 3
- Select Case UBound(Spalten) Mod 3
- Case 0: UsedRangeSortAlt Tabelle, Überschriften, Spalten(StartNr)
- Case 1: UsedRangeSortAlt Tabelle, Überschriften, Spalten(StartNr), Spalten(StartNr + 1)
- Case 2: UsedRangeSortAlt Tabelle, Überschriften, Spalten(StartNr), Spalten(StartNr + 1), Spalten(StartNr + 2)
- End Select
- For i = 0 To StartNr - 3 Step 3
- UsedRangeSortAlt Tabelle, Überschriften, Spalten(i), Spalten(i + 1), Spalten(i + 2)
- Next
- End Select
- End Sub