Automatische Kommasetzung

Zahlen-Eingabe mit automatischer Kommasetzung

Die Aufgabe

In einem bestimmten Bereich einer Tabelle sollen Zahlen dergestalt eingegeben werden, dass ein Komma immer an nach der ersten Ziffer gesetzt wird. Also: Eingabe „123″ ergibt „1,23″, „05″ ergibt „0,5″ und „005″ als Eingabe gibt das Ergebnis „0,05″. Alphanumerische Texte, wenn also Buchstaben in der Zeichenfolge enthalten sind, werden nicht umgewandelt. Da dich dieses Verhalten nicht auf die gesamte Tabelle sondern nur auf definierte Bereichen auswirken soll, kommt die Einstellung mit festen Dezimalstellen nicht zum Zuge.

▲ nach oben …

Die Lösung(en)

Eine wichtige Voraussetzung: Der Eingabebereich bzw. die entsprechenden Bereiche müssen im Vorwege als Text formatiert sein. Das geht typischerweise über das Zahlenformat. Die Alternative wäre, jede Zahl mit einem führenden als Text zu markieren, was jedoch keineswegs hilfreich wäre. Der Hintergrund dieser Maßnahme: Wenn eine Zelle als Standard formatiert ist, so werden bei Eingabe einer Ziffernfolge (also einer Zahl) alle führenden Nullen vor der Übergabe an das Makro abgeschnitten und statt „005″ wird „5″ (als Zahl, nicht als Text) übergeben. Die Lösungen in VBA sind recht übersichtlich, darum wird hier auch der Code aufgeführt. Zusätzlich finden Sie in einer getrennten Datei eine Excel-Arbeitsmappe mit den verschiedenen Varianten einer Lösung.

▲ nach oben …

Eine komplette Spalte

Die einfachste Voraussetzung wäre, wenn eine komplette Spalte so „behandelt“ werden soll. Der Code würde sich dann so darstellen:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column = 1 Then
      If IsNumeric(Target) Then
         Application.EnableEvents = False 
         Target = Trim(Left(Target, 1) & "," & Mid(Target, 2, 99)) 
         Application.EnableEvents = True
      End If
   End If
End Sub

Einige Hinweise zum Code können vielleicht ganz hilfreich sein.  🙂

  • Option Explicit: Gehört prinzipiell über jede Sub oder Function. Hier werde ich allerdings bei den folgenden Code-Darstellungen darauf verzichten, weil es ja eigentlich nur Abwandlungen des hierüber gezeigten Codes sind.
  • Private Sub Worksheet_Change …: Diese Zeile wird automatisch genau so erzeugt, wenn Sie das Worksheet_Change – Ereignis als „Auslöser“ wählen. In der Variablen Target steht dann stets der Inhalt, der Wert der geänderten Zelle.
  • If Target.Column = 1: Eingrenzung auf die gesamte Spalte A.
  • If IsNumeric(Target): Prüfung, ob es sich um einen numerischen Wert handelt, unabhängig von der Formatierung.
  • Application.EnableEvents = False: Wichtig, damit Excel nicht bei jeder Änderung an der Zelle meint, das Makro müsse nun noch einmal ausgeführt werden. Denn: Durch das Setzen des Kommas wird der Inhalt, der Wert der Zelle ja verändert und das würde dann wiederum den Auslöser für das Makro bedeuten.
  • Target = Trim(Left(Target, 1)…: Die Berechnung, an welche Stelle das Komma gesetzt werden soll und anschließende Zuweisung an die Ursprungszelle.
  • Application.EnableEvents = True: Ursprungszustand in Sachen Reaktion auf Änderungen wieder herstellen, denn die nächste Änderung an einer Zelle soll ja bei einer Veränderung einer Zelle des benannten Bereichs das Makro wieder auslösen.
  • Soll eine ganze Zeile statt einer Spalte ausgewertet werden, dann würde statt If Target.Column = 1 beispielsweise If Target.Row = 2 (für Zeile 2) verwendet werden.

Kurz zusammengefasst: Wird in irgend eine Zelle der Spalte A ei beliebiger Wert eingegeben, dann wird automatisch das Makro aktiv, prüft, ob es sich um eine Zahl handelt und wird dann die Zahl entsprechend formatieren. Die Rückgabe ist ebenfalls ein Text. Dennoch kann mit den Zahlenwerten gerechnet werden, Excel erkennt von alleine, dass es sich hier um einen numerischen Text handelt. Wenn Sie das Ganze ausprobieren wollen, dann finden Sie in dieser Datei, Tabelle1 die richtige „Spielwiese“. Der Code ist bereits an der richtigen Stelle und wird natürlich auch korrekt ausgeführt.

▲ nach oben …

Ein zusammenhängender Bereich

Soll ein zusammenhängender Bereich eines Tabellenblattes ausgewertet werden, dann ist der Code doch eine Idee aufwendiger und auf den ersten Blick vielleicht doch nicht so ganz leicht zu verstehen. Angenommen, diese Funktionalität soll nur die Zellen A3:A10 betreffen. Dann würde ich den Code so schreiben:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Application.Intersect(Target, _ Range("A3:A10")) Is Nothing Then
      If IsNumeric(Target) Then
         Application.EnableEvents = False
         Target = Trim(Left(Target, 1) & "," & Mid(Target, 2, 99))
         Application.EnableEvents = True
      End If
   End If
End Sub

Die entscheidende Änderung am Code:

  • If Not Application.Intersect(Target, Range("A3:A10")) Is Nothing

Mit dieser etwas verschrobenen, verschachtelten Formulierung (Not … Is Nothing) wird festgestellt, ob eine Schnittmenge zwischen Target (die Zelladresse) und dem angegebenen Bereich existiert. Falls ja, wird der weitere Code ausgeführt. Dieses Beispiel finden Sie in Tabelle2 der Musterdatei. Der Test-Bereich ist hier grün hinterlegt, damit Sie rasch sehen, wo die Zahlen entsprechend umgewandelt werden und wo nicht.

▲ nach oben …

Mehrere Bereiche

Prinzipiell ist das Vorgehen gleich, wenn Sie mehrere Bereiche verwenden. Da aber die Funktion Intersect immer nur zwei Argumente akzeptiert, muss im Vorwege dafür Sorge getragen werden, dass die verschiedenen getrennten Bereiche zu einem logischen Bereich zusammengefasst werden. Sehen Sie sich diesen Code an oder schauen Sie auch gerne in Tabelle3 der Muster-Mappe nach. Hier sind noch so einige Dinge eingebaut, die sich auch in den vorherigen Versionen gut gemacht hätten:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rng1 As Range, rng2 As Range, rng3 As Range
   Dim rngAll As Range
   With ActiveSheet
      Set rng1 = .Range("B3:B9″)
      Set rng2 = .Range(.Cells(9, 4), .Cells(14, 4))
      Set rng3 = .Range(.Cells(4, 7), "H6″)
      Set rngAll = Union(rng1, rng2, rng3)
   End With

   If rng1.NumberFormat <> "@" Then rng1.NumberFormat = "@"
   If rng2.NumberFormat <> "@" Then rng2.NumberFormat = "@"
   If rng3.NumberFormat <> "@" Then rng3.NumberFormat = "@"

   With Application
      If Not .Intersect(Target, rngAll) Is Nothing Then
         If IsNumeric(Target) Then
            .EnableEvents = False
            Target = Trim(Left(Target, 1) & "," & Mid(Target, 2, 99))
            If Target = "," Then Target = ""
            .EnableEvents = True
         End If
      End If
   End With
End Sub

OK, das sieht nach viel mehr aus als gehabt, aber das scheint nur so. Den wirklich neuen bzw. geänderten Code habe ich hier aufgezeigt:

  • Dim …: Deklaration der Variablen.
  • With … End With: Anweisungsblock, um den Code kürzer und sicherer zu gestalten
  • Set rng1 … : Zuweisung eines Bereichs an eine Variable
  • Set rngAll = Union(rng1 …: Zusammenfassung aller drei Einzelbereiche zu einem einzigen logischen Bereich, damit <code>Intersect</code> korrekt arbeiten kann.
  • If rng1.NumberFormat …: Prüfung, ob das Zahlenformat in dem entsprechenden Bereich Text ist, wenn nicht, dann setzen.
  • If Target = "," Then Target = "": Wenn der vorhandene Inhalt einer Zelle im betroffenen gelöscht wird, verbleibt sonst ein Komma.

Achten Sie bei den Set rng_ – Anweisungen einmal auf die verschiedenen Möglichkeiten. Sie werden, nein Sie sollten der Übersichtlichkeit und der Einheitlichkeit wegen eine einzige Schreibweise verwenden. Vielleicht auch einmal eine weitere, aber bitte nicht so einen „Gemischtwarenladen“ wie im obigen Code. Dort war es die Intention einmal aufzuzeigen, dass vieles möglich ist und zum Ziel führt. Bitte sehen Sie in der VBA-Hilfe nach, wie die verschiedenen Begriffe genau verwendet werden. Hier soll nur angestoßen werden, wie solch ein Programm sinnvoll aufgebaut sein kann. Ich gebe zu, dass da noch so einiges möglich ist. Es lässt sich komprimierter schreiben, vielleicht auch eine Idee schneller machen. Aber hier geht es in erster Linie um die Basics und darum, wie Sie sicher zum Ziel kommen.

▲ nach oben …

Eine Alternative möchte ich Ihnen doch noch anbieten. Sie stellt eher die umgekehrte Variante dar: Das Komma soll so gesetzt werden, dass unabhängig von der Länge der Eingabe mindestens eine Ziffer vor dem zu setzenden Komma und 2 Ziffern danach angezeigt werden. Wird beispielsweise nur eine einzelne Ziffer eingegeben, dann muss 0,0 davor gesetzt werden. In Tabelle4 der bekannten Mappe sehen Sie, wie wir das gelöst haben. Um das Ganze nicht zu sehr aufzublähen, ist die Tabelle2 als Basis verwendet worden.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Application.Intersect(Target, Range("A3:A10")) Is Nothing Then
      If IsNumeric(Target) Then
         Application.EnableEvents = False 
         Target = Format(("000" & Target) * 0.01, "#0.00") 
         Application.EnableEvents = True
      End If
   End If
End Sub

Sie sehen, es wurde nur eine einzige Zeile des Codes geändert, und schon kommt ein vollkommen anderes Ergebnis heraus.

▲ nach oben …

Dieser Beitrag wurde unter Mit VBA/Makro, Musterlösungen veröffentlicht. Setze ein Lesezeichen auf den Permalink.