Bei Änderung einer Zelle: Makro starten

Excel 2003 (und frühere Ver­sio­nen), 2007 ff

Recht oft wird für eine Excel-Tabelle fol­gende Forderung aufgestellt:

• „Immer, wenn sich der Inhalt ein­er bes­timmten Zelle ändert, dann soll eine definierte Aktion aus­ge­führt wer­den.

Was ganz harm­los klingt, ist nicht immer so ohne weit­eres mit „reinem” Excel zu bewerk­stel­li­gen. Dazu bedarf es in bes­timmten Fällen der Pro­gram­mierung, vorzugsweise mit VBA. An einem Beispiel möchte ich Ihnen verdeut­lichen, was ohne Pro­gram­mierung möglich ist und wo Sie dann doch noch den Code erstellen müssen.

▲ nach oben …

Ohne VBA möglich …

Von ein­er an den Rech­n­er angeschlosse­nen Wet­ter­sta­tion wird automa­tisch alle 10 Minu­ten die Tem­per­atur aus­gegeben und an den Com­put­er gesendet. Es ist nicht vorge­se­hen, in Excel eine lange Liste zu erstellen, wo die einzel­nen Werte zeilen­weise aufge­führt sind. Es soll nur aber stets aktuell in A1 die von dem Mess­füh­ler übergebene Tem­per­atur angezeigt wer­den und in B1 soll der Text unter 20°C, genau 20°C oder über 20°C ste­hen.

Das lässt sich rel­a­tiv prob­lem­los lösen, indem Sie in B1 diese Formel schreiben:

=WENN(A1<20; „unter”; WENN(A1=20;„genau”; „über”)) & ” 20°C”

Hin­weis: Das Grad-Zeichen ist das erste Zeichen der Tas­tatur unter­halb der Rei­he mit den Funk­tions-Tas­ten, Shift^. Sie kön­nen auch die oben ste­hende Befehlszeile kopieren und dann bei Bedarf in Excel an entsprechen­der Stelle ein­fü­gen.

▲ nach oben …

Nur mit VBA machbar …

Wenn nun aber die zusät­zliche Forderung kommt, dass in den Zellen B4:B6 die aktuelle (addierte) Anzahl der Werte ste­ht, dann sieht das schon etwas anders aus. Noch ein­mal zur Verdeut­lichung: Wenn in A1 ein neuer Wert einge­tra­gen wird, dann soll nicht nur
B1 aktu­al­isiert wer­den son­dern automa­tisch auch die Werte in A4:B6. Hier ein Beispiel:

Ansicht des Tabel­lenauf­baus

Angenom­men, der näch­ste Wert in A1 wird 23° sein. Dann soll automa­tisch der Wert in Zelle B6 um den Wert 1 erhöht wer­den, weil die Tem­per­atur ja über 20° lag. Die bei­den anderen Ergeb­niszellen sollen selb­stver­ständlich unverän­dert bleiben.

Hier ist ein Makro, also VBA gefragt. Ich stelle Ihnen an dieser Stelle ein­fach ein­mal einen denkbaren (meinen) Pro­gramm­code vor. Der eigentliche Lern­ef­fekt ist hier nicht der dur­chaus ein­fach gestal­tete Pro­gramm­code son­dern

1. Der Name der Proze­dur (Sub) selb­st sowie der Auf­bau des Proze­dur­na­mens und
2. Wie Sie solch einen Code in ein Arbeits­blatt ein­binden.

Hier die Pro­gram­mzeilen:

Option Explicit

Private Sub WorkSheet_Change(ByVal Target As Range)
   Dim Grad As Single
   
   If Target = Range("A1") Then
      Grad = Target.Value
      If Grad < 20 Then
         Range("B4").Value = Range("B4").Value + 1
      ElseIf Grad = 20 Then
         Range("B5").Value = Range("B5").Value + 1
      Else
         Range("B6").Value = Range("B6").Value + 1
      End If
   End If
End Sub

Diesen nicht unbe­d­ingt schö­nen aber funk­tionellen Code müssen Sie nun noch in die entsprechende Arbeitsmappe inte­gri­eren. Der Code soll nicht in der gesamten Mappe son­dern nur in der einen Tabelle aus­ge­führt wer­den, wo auch die Werte hinein geschrie­ben wer­den. Sie müssen nun darauf acht­en, dass die richtige Tabelle aus­gewählt wird. Dazu gehen Sie bei geöffneter Arbeitsmappe so vor:

• AltF11, damit gelan­gen Sie in den VBA-Edi­tor.
• Führen Sie in diesem Fen­ster nun links oben einen Dop­pelk­lick auf die entsprechende Tabelle aus, hier ist die Posi­tion bei Tabelle1 entsprechend markiert:

Das „jungfräuliche” Fen­ster des VBA-Edi­tors ohne Schreib­bere­ich

Sofort wird sich im recht­en Teil des Fen­sters der Hin­ter­grund auf weiß ändern und ein Schreibcur­sor zeigt an, dass in den Edi­tor Dat­en eingegeben wer­den kön­nen:

Und jet­zt mit der Möglichkeit, Code zu schreiben

• Wenn Sie dieses Beispiel nachvol­lziehen wollen, dann kopieren Sie ein­fach den weit­er oben gezeigten Code in den Bere­ich hinein. Das wird dann (mit Aus­nahme der Fen­ster­größe) etwa so ausse­hen:

Hier ist der Pro­gramm­code einge­fügt

• Wahrschein­lich haben Sie reich­lich über­flüs­sige Leerzeilen nach dem Ein­fü­gen. Die kön­nen Sie gerne löschen, das erhöht die Über­sichtlichkeit.
• Schließen Sie das Fen­ster des Edi­tors und pro­bieren
  aus, ob alles wie gewollt und vorge­se­hen läuft.

▲ nach oben …

Ob Sie nun vorgegebe­nen Code (abgetippt oder per copy and paste) oder Eigenkreatio­nen per Hand eingeben, das bleibt sich gle­ich. Das Vorge­hen ist immer das gle­iche. Es bleiben noch zwei Fra­gen: Warum läuft das alles wie gewün­scht bei Änderun­gen in der Tabelle und wie kann ich mir das Leben bei selb­st erstell­tem Code erle­ichtern?

Der erste Teil der Frage ist ganz ein­fach zu beant­worten: Der Name der Proze­dur, das

Sub WorkSheet_Change(ByVal Target As Range)

bewirkt, dass diese kom­plette Proze­dur (die Sub) von Anfang bis Ende aus­ge­führt wird, wenn sich irgen­det­was im Arbeits­blatt (Work­Sheet) ändert (change). Welche Zelle ger­ade geän­dert wor­den ist, das ste­ht in Tar­get drin­nen. Im restlichen Code wird dann abge­fragt, ob es die Zelle, wo Änderun­gen vorgenom­men wor­den sind, A1 ist. Und wenn dieses der zutrifft, wird die dort geän­derte bzw. neu geschriebene Zahl aus­gew­ertet.

Wenn Sie sel­ber den Code schreiben wollen und nicht ganz genau die Syn­tax der Namen­szeile ken­nen, dann gehen Sie so vor:

• Im Edi­tor­fen­ster Dop­pelk­lick auf die gewün­schte Arbeitsmappe.
• Wählen Sie nun an der markierten Stelle den Ein­trag Work­Sheet aus:

Erster Schritt zur Auswahl bzw. Erstel­lung des Proze­dur­na­mens

Umge­hend wird eine leere Proze­dur erstellt. Diese ist für uns nicht rel­e­vant, weil der Name nicht stimmt. Darum …

• Im näch­sten Schritt wählen Sie im recht­en Drop­Down, bei den Dekla­ra­tio­nen das Ereig­nis Change aus:

Auswahl des Change – Ereigniss­es

• Löschen Sie nun noch die über­flüs­sige, zu Beginn automa­tisch einge­fügte leere Proze­dur WorkSheet_SelectionChange und füllen Sie die verbliebene Sub mit Ihrem Code.
• Vergessen Sie nicht das Spe­ich­ern (StrgS), bevor Sie Ihren Code testen! Selb­st Profis machen das so, denn es schle­icht sich zu schnell doch ein­mal ein Fehler ein, der das Pro­gramm zum „aufhän­gen” bringt oder ein­fach einen Absturz her­beiführt. In solchen Fällen ist der Code „gerettet” und kann nach einem erneuten Aufruf des Edi­tors bear­beit­et wer­den.

Wenn Sie Excel 2007 oder neuer ver­wen­den, dann ist es denkbar, dass das Makro nicht aus­ge­führt wird. Das hängt dann wahrschein­lich mit den Sicher­heit­se­in­stel­lun­gen zusam­men. Nähere Hin­weise und Hil­fe erhal­ten Sie hier.