Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Inhalt dieser Seite

Selection, muß das sein?

Die nachfolgende Abhandlung mag manchem in der Entschiedenheit übertrieben erscheinen, dennoch hält der Autor eine klare Position in diesem Thema für angebracht, da das Selektieren und Aktivieren von Trainern und Dozenten auch nach einigen Jahren VBA weiter unterstützt wird und sie in der Regel selbst zu eifrigen Selektierern gehören. Ein kleiner Teil hebt sich wohltuend von der Mehrheit ab. Auch in der Literatur wird aus der Angst heraus, sich Laien gegenüber nicht verständlich machen zu können, das Thema falsch behandelt.

Worum geht es hier?

Es gibt in MS Office wie auch im wirklichen Office mehrere Möglichkeiten, ein Objekt (MS Office) oder einen Mitarbeiter (Office) anzusprechen oder ihm Anweisungen zu erteilen. Um einem Mitarbeiter in einer Abteilung eines anderen Werkes die freudige Mitteilung einer Gehaltserhöhung - über die sich sein danebenstehender Kollege gelb ärgert - zu übermitteln, kann man ihm das entweder über die Hauspost mitteilen lassen oder ihn in dem anderen Werk besuchen.

In VBA wäre die erste Vorgehensweise Referenzieren und die zweite Selektieren. Als Code sieht die erste Variante so aus:

Sub Referenzieren()
   With Workbooks("Factory.xls").Worksheets("Abteilung").Range("A1")
      .Value = "Gehaltserhöhung"
      .Interior.ColorIndex = 3
      .Font.Bold = True
      With .Offset(0, 1)
         .Interior.ColorIndex = 6
         .Font.Bold = False
      End With
   End With
End Sub

Der Selektierer hat, um zum gleichen Ergebnis zu kommen, schon etwas mehr Arbeit:

Sub Hingehen()
   Dim wkb As Workbook
   Application.ScreenUpdating = False
   Set wkb = ActiveWorkbook
   Workbooks("Factory.xls").Activate
   Worksheets("Abteilung").Select
   Range("A1").Select
   With Selection
      .Value = "Gehaltserhöhung"
      .Interior.ColorIndex = 3
      .Font.Bold = True
   End With
   Range("A2").Select
   With Selection
      .Interior.ColorIndex = 6
      .Font.Bold = False
   End With
   wkb.Activate
   Application.ScreenUpdating = True
End Sub

Im Bürobeispiel bekommt er für seine Mehrleistung den Zusatznutzen, die Freude des Gehaltserhöhten und den Neid dessen Kollegen live mitzuerleben, bei VBA bleibt es bei der Mehrarbeit.

Wieso ist das Selektieren so verbreitet?

Dass man kaum Code ohne Selektiererei sieht - hiervon sind viele Code-Beispiele aus dem Hause Microsoft nicht ausgeschlossen - ist vor allen in folgenden Dingen begründet:

  • Fast jeder in MS Excel mit VBA Programmierende hat seine ersten VBA-Schritte mit dem Makrorecorder gemacht. Der Recorder ist der Meister des Selektierens und des überflüssigen Codes. Es sei ihm gestattet; er hat keine andere Chance.
  • Es erleichtert die Flucht vor abstraktem Denken, indem in die Objekte eine Begrifflichkeit gelegt wird, die nur fiktiv ist.
  • Es wird von denen, die VBA vermitteln sollen, eingesetzt, um den Lernenden einen Bezug zu den Objekten zu vermitteln. Dies erleichtert zugegebenermassen die ersten Schritte in diese Programmiersprache, wirkt sich jedoch später eher als Fluch aus.
  • In wesentlich stärkerem Maße als bei anderen Programmiersprachen kommen die Programmierenden aus dem Anwenderbereich und/oder dem der autodidaktisch Lernenden und besitzen in der Regel keine umfassende Ausbildung in den Grundlagen der Programmierung.

Selektieren und Referenzieren aufgrund unterschiedlichen Denkens?

Der typischer Gedankengang eines Selektierers:

Wenn ich jetzt in das Arbeitsblatt Tabelle1 der Arbeitsmappe Test1 und dort in Zelle F10 gehe, den dortigen Zellinhalt kopiere, ihn dann in Arbeitsblatt Tabelle2 von Arbeitsmappe Test2 trage und in Zelle B5 ablade, habe ich das Ergebnis, was ich haben möchte. Jetzt kann ich wieder in die Arbeitsmappe zurückgehen, von der aus ich losgegangen bin.

Diese Überlegung schlägt sich bei ihm in folgendem Code nieder:

Sub SelektiertKopieren()
   Dim wkb As Workbook
   Set wkb = ActiveWorkbook
   Workbooks("Test1").Activate
   Worksheets("Tabelle1").Select
   Range("F10").Select
   Selection.Copy
   Workbooks("Test2").Activate
   Worksheets("Tabelle2").Select
   Range("B5").Select
   ActiveSheet.Paste Destination:=ActiveCell
   wkb.Activate
   Application.CutCopyMode = False
End Sub

Wäre er kein Selektierer, würde er sich sagen, ich kopiere aus Arbeitsmappe Test1, Tabelle1, Zelle F10 nach Arbeitsmappe Test2, Tabelle2, Zelle B5.

So sähe dann sein Code auch aus:

Sub ReferenziertKopieren()
   Workbooks("Test1").Worksheets("Tabelle1").Range("F10").Copy _
      Workbooks("Test2").Worksheets("Tabelle2").Range("B5")
   Application.CutCopyMode = False
End Sub

Warum soll nicht selektiert werden?

Neben der bekannten Tatsache, daß es sich beim Cursor um keinen Auslauf benötigenden Dackel handelt, eher um einen augesprochen faulen Hund, der nichts mehr als seine Ruhe liebt spricht noch folgendes gegen das Selektieren:

  • Selektieren macht den Code unübersichtlich. Da an jeder Ecke von Selection gesprochen wird, verliert man leicht den Überblick, was denn nun gerade selektiert ist. Besonders gravierend fällt dies bei der VBA-Bearbeitung von Diagrammen auf.
  • Werden Programme von Dritten weiterbearbeitet, sollte man den nachfolgend damit Beschäftigten die Herumirrerei im Selection-Dschungel ersparen.
  • Es wird erheblich mehr Code benötigt. Jede zusätzliche Codezeile ist eine zusätzliche potentielle Fehlerquelle und wirkt sich negativ auf die Performance aus. Die Dateigröße verändert sich nicht entscheidend.
  • Der Programmablauf wird unruhig und flackernd. Dies kann nicht in jedem Fall durch Setzen des ScreenUpdating-Modus auf False verhindert werden.

In welchen Fällen sollte selektiert werden?

Es gibt einige Situationen, in denen Selectieren entweder notwendig oder sinnvoll ist. Verlangt wird es von Excel nur in einer verschwindend geringen Anzahl von Fällen. Um einen zu nennen: Das Fenster ist nur zu fixieren, wenn die Tabelle, für die die Fixierung gelten soll, aktiviert ist. Sinnvoll kann es sein, wenn umfangreicher Code mit Arbeiten an und mit Objekten in zwei Arbeitsblättern befasst ist - beispielsweise einem Quell- und einem Zielblatt, zum Programmstart aber ein drittes das Aktive ist. Um den Code übersichtlich und die Schreibarbeit in Grenzen zu halten, kann man jetzt eines der beiden Blätter aktivieren und das andere in einen With-Rahmen einbinden. Man erspart sich dadurch die beidseitige Referenzierung.

Wie kann ich das Selektieren verhindern?

Die Selektiererei läßt sich verhindern durch eine exakte Variablendeklaration und -dimensionierung sowie einer darauf aufbauenden genauen Referenzierung der Objekte.

Im Nachfolgenden einige Beispiele:

  • Kopieren eines Zellbereiches von einer zur anderen Arbeitsmappe, aufgerufen aus einer dritten
    Sub Kopieren()
       Dim rngSource As Range, rngTarget As Range
       Set rngSource = Workbooks("Test1.xls").Worksheets(1).Range("A1:F14")
       Set rngTarget = Workbooks("Test2.xls").Worksheets(2).Range("C16")
       rngSource.Copy rngTarget
    End Sub
    
  • Einfügen einer Grafik in eine zweite Arbeitsmappe
    Sub BildEinfuegenPositionieren()
       Dim wks As Worksheet
       Dim pct As Picture
       Set wks = Workbooks("Test1.xls").Worksheets(1)
       Set pct = wks.Pictures.Insert("c:\excel\zelle.gif")
       pct.Left = 120
       pct.Top = 150
    End Sub
    
  • In Arbeitsblättern 3 bis 12 je einer Serie von 8 Diagrammen in jedem 2. Diagramm den ersten drei SeriesCollections Trendlinien hinzufügen
    Sub Aufruf()
       Dim wks As Worksheet
       Dim intCounter As Integer
       For intCounter = 3 To 12
          Call Trendlinie(wks)
       Next intCounter
    End Sub
    
    Private Sub Trendlinie(wksTarget As Worksheet)
       Dim trdLine As Trendline
       Dim intChart As Integer, intCll As Integer
       For intChart = 1 To 7 Step 2
          With wksTarget.ChartObjects(intChart).Chart
             For intCll = 1 To 3
                Set trdLine = .SeriesCollection(intCll).Trendlines.Add(Type:=xlLinear)
                With trdLine.Border
                   Select Case intCll
                      Case 1
                         .ColorIndex = 5
                         .LineStyle = xlDot
                         .Weight = xlThin
                      Case 2
                         .ColorIndex = 7
                         .LineStyle = xlDot
                         .Weight = xlThin
                      Case 3
                         .ColorIndex = 6
                         .LineStyle = xlDot
                         .Weight = xlThin
                   End Select
                End With
             Next intCll
          End With
       Next intChart
    End Sub
    
  • Bereich im aktiven Blatt filtern und die gefilterten Daten in eine neue Arbeitsmappe kopieren. Am Ende wird die aktive Zelle selektiert, um die Filterauswahl aufzuheben.
    Sub FilternKopieren()
       Dim wkb As Workbook
       Set wkb = ActiveWorkbook
       Application.ScreenUpdating = False
       Range("A1").AutoFilter field:=3, Criteria1:="*2*"
       Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
       Workbooks.Add
       ActiveSheet.Paste Destination:=Range("A1")
       Columns.AutoFit
       wkb.Activate
       ActiveSheet.AutoFilterMode = False
       Application.CutCopyMode = False
       ActiveCell.Select
    End Sub