Excel spezifische Informationen: Unterschied zwischen den Versionen

Aus VBA-wiki
Zur Navigation springen Zur Suche springen
Keine Bearbeitungszusammenfassung
 
(5 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 1: Zeile 1:
[[Category:vba-wiki]]
== Range ==
== Range ==
In Excel besteht eine Datei (bzw. Arbeitsmappe) aus mindestens einer Tabelle (bzw. Arbeitsblatt), welche Zellen enthält, die in Zeilen und Spalten aufgeteilt sind.
Jede einzelne Zelle, aber auch eine beliebige Gruppe von Zellen (die gesamte Arbeitsmappe, eine Zeile, alle markierte Zellen usw.) wird als Bereich (Range) begriffen.
Bereiche können auf folgende Weisen addressiert werden:
=== Zugriff auf markierte Zellen ===
    MsgBox Selection<span style="Color:gray">.</span>Value              <span style="Color:green">' Greift auf die Auswahl zu. Wenn diese eine Zelle ist,</span>
                                        <span style="Color:green">' kann hiermit auf den Inhalt zugegriffen werden.</span>
                                        <span style="Color:green">' ABER: Wenn eine Form oder ein Diagramm ausgewählt ist,</span>
                                        <span style="Color:green">' entsteht hier ein Fehler!</span>
    MsgBox ActiveCell<span style="Color:gray">.</span>Value            <span style="Color:green">' Greift auf die markierte Zelle zu</span>
                                        <span style="Color:green">' Wenn die Markierung mehrere Zellen enthält,</span>
                                        <span style="Color:green">' wird die zuerst markierte zurückgegeben</span>
=== Adressierung mithilfe Zeilen- und Spaltenindex ===
    ActiveSheet<span style="Color:gray">.</span>Rows<span style="Color:gray">(2).</span>Select              <span style="Color:green">' 2. Zeile</span>
    ActiveSheet<span style="Color:gray">.</span>Columns<span style="Color:gray">(3).</span>Select          <span style="Color:green">' 3. Spalte</span>
    ActiveSheet<span style="Color:gray">.</span>Cells<span style="Color:gray">(2,</span> <span style="Color:gray">3).</span>Select          <span style="Color:green">' Zelle "C2"</span>
=== Addressierung mithilfe des Spaltenkenners ===
Für den Anwender werden die Spalten in der Regel nicht mit ihrem Index, sondern mit einer Buchstabenkombination gekennzeichet. Diese kann auch in der Programmierung verwendet werden:
    ActiveSheet<span style="Color:gray">.</span>Columns<span style="Color:gray">("C").</span>Select        <span style="Color:green">' 3. Spalte</span>
    <span style="Color:green">' aber:</span>
    ActiveSheet<span style="Color:gray">.</span>Range<span style="Color:gray">("C2").</span>Select          <span style="Color:green">' Zelle "C2"</span>
'''Beachte:''' Statt 'Cells' muss in diesem Beispiel 'Range' verwendet werden!
=== Zugriff auf einen Zellbereich ===
Um auf mehrere Zellen innerhalb eines Bereiches zuzugreifen, stehen uns folgende Möglichkeiten der 'Range'-Eigenschaft zur Verfügung. In beiden Beispielen wird auf den Bereich 'C2:E4' verwiesen:
    ActiveSheet<span style="Color:gray">.</span>Range<span style="Color:gray">(</span>Cells<span style="Color:gray">(2,</span> <span style="Color:gray">3),</span> Cells<span style="Color:gray">(4,</span> <span style="Color:gray">5)).</span>Select              <span style="Color:green">' Spricht die Zellen über ihre Zeilen- und Spaltenindizes an</span>
    ActiveSheet<span style="Color:gray">.</span>Range<span style="Color:gray">("C2:E4").</span>Select                              <span style="Color:green">' Spricht die Zellen über ihre Adressen an</span>


== Zelle ==
== Zelle ==
Zeile 9: Zeile 51:
Für die Formel einer Zelle stehen vier Eigenschaften zur Verfügung, welche die Formel in einem jeweils anderen Format bzw. einer anderen Sprache darstellt.
Für die Formel einer Zelle stehen vier Eigenschaften zur Verfügung, welche die Formel in einem jeweils anderen Format bzw. einer anderen Sprache darstellt.


  Private Sub ExcelCellValues()
  <span style="Color:blue"> Private Sub </span>ExcelCellValues<span style="Color:gray">()</span>
     Dim rngCell As Range                        ' Nur mithilfe einer Variablen vom Typ 'Range' können wir im
     <span style="Color:blue"> Dim </span>rngCell<span style="Color:blue"> As </span>Range                        <span style="Color:green">' Nur mithilfe einer Variablen vom Typ 'Range' können wir im</span>
                                                ' VBA-Editor mit der Auto-Vervollständigungs-Hilfe rechnen
                                                  <span style="Color:green">' VBA-Editor mit der Auto-Vervollständigungs-Hilfe rechnen</span>
                                   
                                     
     Set rngCell = Cells(1, 1)                  ' Instanziiert auf die Zelle 'A1' (erste Zeile, erste Spalte)
     <span style="Color:blue"> Set </span>rngCell <span style="Color:gray">=</span> Cells<span style="Color:gray">(1,</span> <span style="Color:gray">1)</span>                   <span style="Color:green">' Instanziiert auf die Zelle 'A1' (erste Zeile, erste Spalte)</span>
   
     
    Debug.Print rngCell.Formula                ' Die Formel in englisch:              '=SUM(C2:C7)'
      Debug<span style="Color:gray">.</span>Print rngCell<span style="Color:gray">.</span>Formula                <span style="Color:green">' Die Formel in englisch:              '=SUM(C2:C7)'</span>
    Debug.Print rngCell.FormulaLocal            ' Die Formel in Landessprache:          '=SUMME(C2:C7)'
      Debug<span style="Color:gray">.</span>Print rngCell<span style="Color:gray">.</span>FormulaLocal            <span style="Color:green">' Die Formel in Landessprache:          '=SUMME(C2:C7)'</span>
    Debug.Print rngCell.FormulaR1C1             ' Relative Formel in englisch:          '=SUM(R[-7]C:R[-2]C)'
      Debug<span style="Color:gray">.</span>Print rngCell<span style="Color:gray">.</span>FormulaR<span style="Color:gray">1</span>C<span style="Color:gray">1</span>             <span style="Color:green">' Relative Formel in englisch:          '=SUM(R[-7]C:R[-2]C)'</span>
    Debug.Print rngCell.FormulaR1C1Local       ' Relative Formel in Landessprache:    '=SUMME(Z[-7]S:Z[-2]S)'
      Debug<span style="Color:gray">.</span>Print rngCell<span style="Color:gray">.</span>FormulaR<span style="Color:gray">1</span>C<span style="Color:gray">1</span>Local       <span style="Color:green">' Relative Formel in Landessprache:    '=SUMME(Z[-7]S:Z[-2]S)'</span>
  End Sub
  <span style="Color:blue"> End Sub</span>


==== Relative Bezüge in FormulaR1C1 ====
==== Relative Bezüge in FormulaR1C1 ====
Zeile 36: Zeile 78:
Der Anwender kann ausschließlich angeben, von welcher Zelle bis welcher Zelle die Summe gebildet werden soll.
Der Anwender kann ausschließlich angeben, von welcher Zelle bis welcher Zelle die Summe gebildet werden soll.
|
|
  Dim strStart As String, strEnd As String
  <span style="Color:blue"> Dim </span>strStart<span style="Color:blue"> As String</span><span style="Color:gray">,</span> strEnd<span style="Color:blue"> As String
Dim rngCell As Range
  Dim </span>rngCell<span style="Color:blue"> As </span>Range
   
   
  Set rngCell = ActiveCell
  <span style="Color:blue"> Set </span>rngCell <span style="Color:gray">=</span> ActiveCell
strStart = Cells(2, rngCell.Column).Address
  strStart <span style="Color:gray">=</span> Cells<span style="Color:gray">(2,</span> rngCell<span style="Color:gray">.</span>Column<span style="Color:gray">).</span>Address
strEnd = Cells(rngCell.Row - 2, rngCell.Column).Address
  strEnd <span style="Color:gray">=</span> Cells<span style="Color:gray">(</span>rngCell<span style="Color:gray">.</span>Row <span style="Color:gray">-</span> <span style="Color:gray">2,</span> rngCell<span style="Color:gray">.</span>Column<span style="Color:gray">).</span>Address
rngCell.Formula = "=SUM(" & strStart & ":" & strEnd & ")"
  rngCell<span style="Color:gray">.</span>Formula <span style="Color:gray">=</span> <span style="Color:gray">"=SUM("</span> & strStart & <span style="Color:gray">":"</span> & strEnd & <span style="Color:gray">")"</span>
Per Programmierung müssen die Adressen der Anfangs- und Endzelle erst ermittelt und dann in die Formel eingebaut werden.
Per Programmierung müssen die Adressen der Anfangs- und Endzelle erst ermittelt und dann in die Formel eingebaut werden.
|  
|  
  Dim rngCell As Range
  <span style="Color:blue"> Dim </span>rngCell<span style="Color:blue"> As </span>Range
   
   
  Set rngCell = ActiveCell
  <span style="Color:blue"> Set </span>rngCell <span style="Color:gray">=</span> ActiveCell
rngCell.FormulaR1C1 = "=SUM(R2C:R[-2]C)"
  rngCell<span style="Color:gray">.</span>FormulaR<span style="Color:gray">1</span>C<span style="Color:gray">1</span> <span style="Color:gray">=</span> <span style="Color:gray">"=SUM(R2C:R[-2]C)"</span>
Bei dieser Variante wird der dynamische Bezug in der Formel im 'R1C1'-Format angegeben:  
Bei dieser Variante wird der dynamische Bezug in der Formel im 'R1C1'-Format angegeben:  
* 'R2' bedeutet: Zweite Zeile (absolut)
* 'R2' bedeutet: Zweite Zeile (absolut)
Zeile 59: Zeile 101:
Mit folgenden Eigenschaften kann der Wert einer Zelle ermittelt werden:
Mit folgenden Eigenschaften kann der Wert einer Zelle ermittelt werden:


    Debug.Print rngCell.Value                  ' Wert in korrekter Einheit            '23.03.2005'
      Debug<span style="Color:gray">.</span>Print rngCell<span style="Color:gray">.</span>Value                  <span style="Color:green">' Wert in korrekter Einheit            '23.03.2005'</span>
    Debug.Print rngCell.Value2                 ' Wie Value, aber Datum als Double      '38434'
      Debug<span style="Color:gray">.</span>Print rngCell<span style="Color:gray">.</span>Value<span style="Color:gray">2</span>                 <span style="Color:green">' Wie Value, aber Datum als Double      '38434'</span>
    Debug.Print rngCell.Text                    ' Angezeigter, formatierter Text        '2,00'
      Debug<span style="Color:gray">.</span>Print rngCell<span style="Color:gray">.</span>Text                    <span style="Color:green">' Angezeigter, formatierter Text        '2,00'</span>


'''Bitte beachten:''' Mit der 'Text'-Eigenschaft kann der angezeigte Text nur ausgelesen, aber nicht geändert werden. Hierzu stehen die beiden 'Value'-Eigenschaften zur Verfügung.
'''Bitte beachten:''' Mit der 'Text'-Eigenschaft kann der angezeigte Text nur ausgelesen, aber nicht geändert werden. Hierzu stehen die beiden 'Value'-Eigenschaften zur Verfügung.
Zeile 81: Zeile 123:
Die Standard-Eigenschaft einer Zelle (bzw. einer einfachen Range) ist die 'Formula'-Eigenschaft.
Die Standard-Eigenschaft einer Zelle (bzw. einer einfachen Range) ist die 'Formula'-Eigenschaft.


Cells(7, 5) = "=SUMME(E1:E5)"    ' Wird als Formel interpretiert
  Cells<span style="Color:gray">(7,</span> <span style="Color:gray">5)</span> <span style="Color:gray">=</span> <span style="Color:gray">"=SUMME(E1:E5)"</span>     <span style="Color:green">' Wird als Formel interpretiert</span>
Cells(7, 5) = 56                  ' Wird als Wert interpretiert
  Cells<span style="Color:gray">(7,</span> <span style="Color:gray">5)</span> <span style="Color:gray">=</span> <span style="Color:gray">56</span>                 <span style="Color:green">' Wird als Wert interpretiert</span>


== Application.DisplayAlerts ==
== Application.DisplayAlerts ==

Aktuelle Version vom 28. Januar 2023, 00:31 Uhr

Range

In Excel besteht eine Datei (bzw. Arbeitsmappe) aus mindestens einer Tabelle (bzw. Arbeitsblatt), welche Zellen enthält, die in Zeilen und Spalten aufgeteilt sind.

Jede einzelne Zelle, aber auch eine beliebige Gruppe von Zellen (die gesamte Arbeitsmappe, eine Zeile, alle markierte Zellen usw.) wird als Bereich (Range) begriffen.

Bereiche können auf folgende Weisen addressiert werden:

Zugriff auf markierte Zellen

    MsgBox Selection.Value              ' Greift auf die Auswahl zu. Wenn diese eine Zelle ist,
                                        ' kann hiermit auf den Inhalt zugegriffen werden.
                                        ' ABER: Wenn eine Form oder ein Diagramm ausgewählt ist,
                                        ' entsteht hier ein Fehler!
    MsgBox ActiveCell.Value             ' Greift auf die markierte Zelle zu
                                        ' Wenn die Markierung mehrere Zellen enthält,
                                        ' wird die zuerst markierte zurückgegeben

Adressierung mithilfe Zeilen- und Spaltenindex

    ActiveSheet.Rows(2).Select              ' 2. Zeile
    ActiveSheet.Columns(3).Select           ' 3. Spalte
    ActiveSheet.Cells(2, 3).Select          ' Zelle "C2"

Addressierung mithilfe des Spaltenkenners

Für den Anwender werden die Spalten in der Regel nicht mit ihrem Index, sondern mit einer Buchstabenkombination gekennzeichet. Diese kann auch in der Programmierung verwendet werden:

    ActiveSheet.Columns("C").Select         ' 3. Spalte
    ' aber:
    ActiveSheet.Range("C2").Select          ' Zelle "C2"

Beachte: Statt 'Cells' muss in diesem Beispiel 'Range' verwendet werden!


Zugriff auf einen Zellbereich

Um auf mehrere Zellen innerhalb eines Bereiches zuzugreifen, stehen uns folgende Möglichkeiten der 'Range'-Eigenschaft zur Verfügung. In beiden Beispielen wird auf den Bereich 'C2:E4' verwiesen:

    ActiveSheet.Range(Cells(2, 3), Cells(4, 5)).Select              ' Spricht die Zellen über ihre Zeilen- und Spaltenindizes an
    ActiveSheet.Range("C2:E4").Select                               ' Spricht die Zellen über ihre Adressen an

Zelle

Eine Excel Zelle hat keinen eigenen Datentyp, sondern gilt als einfacher Bereich ('Range'). Bereiche können also auch aus mehreren Zellen bestehen. Wenn ein Bereich aus mehreren Zellen besteht, wird bei vielen Eigenschaften (zum Beispiel 'Value', 'Formula' etc.) die erste enthaltene Zelle verwendet.

Formeln

Für die Formel einer Zelle stehen vier Eigenschaften zur Verfügung, welche die Formel in einem jeweils anderen Format bzw. einer anderen Sprache darstellt.

 Private Sub ExcelCellValues()
     Dim rngCell As Range                        ' Nur mithilfe einer Variablen vom Typ 'Range' können wir im
                                                 ' VBA-Editor mit der Auto-Vervollständigungs-Hilfe rechnen
                                     
     Set rngCell = Cells(1, 1)                   ' Instanziiert auf die Zelle 'A1' (erste Zeile, erste Spalte)
     
     Debug.Print rngCell.Formula                 ' Die Formel in englisch:               '=SUM(C2:C7)'
     Debug.Print rngCell.FormulaLocal            ' Die Formel in Landessprache:          '=SUMME(C2:C7)'
     Debug.Print rngCell.FormulaR1C1             ' Relative Formel in englisch:          '=SUM(R[-7]C:R[-2]C)'
     Debug.Print rngCell.FormulaR1C1Local        ' Relative Formel in Landessprache:     '=SUMME(Z[-7]S:Z[-2]S)'
 End Sub

Relative Bezüge in FormulaR1C1

Diese Art der Formel ist relativ formuliert. Das bedeutet, dass sie sich auf die Zelle (bzw. deren Position auf dem Arbeitsblatt) beziehen. Diese Eigenschaft Steht dem Anwender nicht zur Verfügung, vereinfacht in vielen Situationen jedoch die Formulierung von Bezügen für den Programmierer:

Ziel Formel Formula (VBA-Code) FormulaR1C1 (VBA-Code)
In der Zelle C9 soll die Summe der darüber stehenden Werte gebildet werden.
=SUM(C2:C7)

Der Anwender kann ausschließlich angeben, von welcher Zelle bis welcher Zelle die Summe gebildet werden soll.

 Dim strStart As String, strEnd As String
 Dim rngCell As Range
    
 Set rngCell = ActiveCell
 strStart = Cells(2, rngCell.Column).Address
 strEnd = Cells(rngCell.Row - 2, rngCell.Column).Address
 rngCell.Formula = "=SUM(" & strStart & ":" & strEnd & ")"

Per Programmierung müssen die Adressen der Anfangs- und Endzelle erst ermittelt und dann in die Formel eingebaut werden.

 Dim rngCell As Range
    
 Set rngCell = ActiveCell
 rngCell.FormulaR1C1 = "=SUM(R2C:R[-2]C)"

Bei dieser Variante wird der dynamische Bezug in der Formel im 'R1C1'-Format angegeben:

  • 'R2' bedeutet: Zweite Zeile (absolut)
  • 'C' bedeutet: In dieser Spalte
  • 'R[-2]' bedeutet: Von dieser Zeile aus betrachtet zwei nach oben (relativ)

Wert

Mit folgenden Eigenschaften kann der Wert einer Zelle ermittelt werden:

     Debug.Print rngCell.Value                   ' Wert in korrekter Einheit             '23.03.2005'
     Debug.Print rngCell.Value2                  ' Wie Value, aber Datum als Double      '38434'
     Debug.Print rngCell.Text                    ' Angezeigter, formatierter Text        '2,00'

Bitte beachten: Mit der 'Text'-Eigenschaft kann der angezeigte Text nur ausgelesen, aber nicht geändert werden. Hierzu stehen die beiden 'Value'-Eigenschaften zur Verfügung.

Wichtig: Der Makro-Rekorder verwendet bei seinen Aufnahmen meist für das Lesen und Beschreiben einer Zelle die 'FormulaR1C1'-Eigenschaft, welche aber in der Programmierung nicht verwendet werden sollte, wenn dies nicht der Aufgabe entspricht. Beim Lesen der 'FormulaR1C1'-Eigenschaft wird hierbei zum Beispiel die Formel, nicht der Wert zurückgegeben.

Empfehlung
  • Werte auslesen
    • Wenn Sie Texte auslesen, verwenden Sie die 'Text'-Eigenschaft
    • Wenn Sie Zahlenwerte zur Berechnung benötigen, verwenden Sie die 'Value'-Eigenschaft
    • Wenn Sie eine formatierte Zahl (zum Beispiel '2,00' statt '2') auslesen möchten, verwenden Sie die 'Text'-Eigenschaft
    • Wenn Sie von einem Datum den Zahlenwert benötigen, verwenden Sie die 'Value2'-Eigenschaft (zum Beispiel '42291' statt '2015-10-14')
  • Werte zuweisen
    • Generell sollten Sie hierfür die 'Value'-Eigenschaft verwenden
    • Wenn Sie ein Datum als Zahlenwert einsetzen möchten, verwenden Sie die 'Value2'-Eigenschaft (zum Beispiel '42291' statt '2015-10-14')

Standard-Eigenschaft

Die Standard-Eigenschaft einer Zelle (bzw. einer einfachen Range) ist die 'Formula'-Eigenschaft.

 Cells(7, 5) = "=SUMME(E1:E5)"     ' Wird als Formel interpretiert
 Cells(7, 5) = 56                  ' Wird als Wert interpretiert

Application.DisplayAlerts

Datenaustausch mit Dialogen

Lösungsbeispiele

Letzte Zeile der ersten Spalte ermitteln

Sub FindLastRow()

    ' Ursprüngliche Aufzeichnung:
    ' Strg + Pfeiltaste nach unten: Springt ans Ende der Tabelle
    ' Strg + Pfeiltaste nach oben: Springt an das Ende des Datenblocks
    ' Voraussetzung: Der erste Sprung erfolgt aus einer Zeile, die unterhalb des letzten
    ' Datenblocks liegt
    ' -> Der erste Sprung nach unten ist unsicher
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    
    ' Erste Verbesserung:
    ' Statt an das Ende der Tabelle zu springen,
    ' wird die letzte Zelle (Rows.Count) als Ausgangspunkt gewählt
    Cells(Rows.Count, 1).Select
    Selection.End(xlUp).Select

    ' Zweite Verbesserung:
    ' Verzicht auf die Auswahl
    Cells(Rows.Count, 1).End(xlUp).Select
    
    ' Dritte Verbesserung:
    ' Nicht die gefundene Zelle markieren, sondern deren Zeile ermitteln
    ' Den entsprechenden Befehl mithilfe der 'ActiveCell' suchen
    Debug.Print ActiveCell.Row
    ' und dann im schon ermittelten Befehl anhängen:
    Debug.Print Cells(Rows.Count, 1).End(xlUp).Row

End Sub