Excel spezifische Informationen: Unterschied zwischen den Versionen

Aus VBA-wiki
Zur Navigation springen Zur Suche springen
Zeile 9: Zeile 9:
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 36:
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)

Version vom 21. Juni 2019, 09:35 Uhr

Range

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