Die Aufgabe

In einem bes­timmten Bere­ich ein­er Tabelle sollen Zahlen dergestalt eingegeben wer­den, dass ein Kom­ma immer an nach der ersten Zif­fer geset­zt wird. Also: Eingabe „123” ergibt „1,23”, „05” ergibt „0,5” und „005” als Eingabe gibt das Ergeb­nis „0,05”. Alphanu­merische Texte, wenn also Buch­staben in der Zeichen­folge enthal­ten sind, wer­den nicht umge­wan­delt. Da dich dieses Ver­hal­ten nicht auf die gesamte Tabelle son­dern nur auf definierte Bere­ichen auswirken soll, kommt die Ein­stel­lung mit fes­ten Dez­i­mal­stellen nicht zum Zuge.

▲ nach oben …

Die Lösung(en)

Eine wichtige Voraus­set­zung: Der Eingabebere­ich bzw. die entsprechen­den Bere­iche müssen im Vor­wege als Text for­matiert sein. Das geht typ­is­cher­weise über das Zahlen­for­mat. Die Alter­na­tive wäre, jede Zahl mit einem führen­den ’ als Text zu markieren, was jedoch keineswegs hil­fre­ich wäre. Der Hin­ter­grund dieser Maß­nahme: Wenn eine Zelle als Stan­dard for­matiert ist, so wer­den bei Eingabe ein­er Zif­fer­n­folge (also ein­er Zahl) alle führen­den Nullen vor der Über­gabe an das Makro abgeschnit­ten und statt „005” wird „5” (als Zahl, nicht als Text) übergeben. Die Lösun­gen in VBA sind recht über­sichtlich, darum wird hier auch der Code aufge­führt. Zusät­zlich find­en Sie in ein­er getren­nten Datei eine Excel-Arbeitsmappe mit den ver­schiede­nen Vari­anten ein­er Lösung.

▲ nach oben …

Eine komplette Spalte

Die ein­fach­ste Voraus­set­zung wäre, wenn eine kom­plette Spalte so „behan­delt” wer­den 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 Hin­weise zum Code kön­nen vielle­icht ganz hil­fre­ich sein.  🙂

• Option Explicit: Gehört prinzip­iell über jede Sub oder Func­tion. Hier werde ich allerd­ings bei den fol­gen­den Code-Darstel­lun­gen darauf verzicht­en, weil es ja eigentlich nur Abwand­lun­gen des hierüber gezeigten Codes sind.
• Private Sub WorkSheet_Change …: Diese Zeile wird automa­tisch genau so erzeugt, wenn Sie das WorkSheet_Change – Ereig­nis als „Aus­lös­er” wählen. In der Vari­ablen Target ste­ht dann stets der Inhalt, der Wert der geän­derten Zelle.
• If Target.Column = 1: Ein­gren­zung auf die gesamte Spalte A.
• If IsNumeric(Target): Prü­fung, ob es sich um einen numerischen Wert han­delt, unab­hängig von der For­matierung.
• Application.EnableEvents = False: Wichtig, damit Excel nicht bei jed­er Änderung an der Zelle meint, das Makro müsse nun noch ein­mal aus­ge­führt wer­den. Denn: Durch das Set­zen des Kom­mas wird der Inhalt, der Wert der Zelle ja verän­dert und das würde dann wiederum den Aus­lös­er für das Makro bedeuten.
• Target = Trim(Left(Target, 1)…: Die Berech­nung, an welche Stelle das Kom­ma geset­zt wer­den soll und anschließende Zuweisung an die Ursprungszelle.
• Application.EnableEvents = True: Ursprungszu­s­tand in Sachen Reak­tion auf Änderun­gen wieder her­stellen, denn die näch­ste Änderung an ein­er Zelle soll ja bei ein­er Verän­derung ein­er Zelle des benan­nten Bere­ichs das Makro wieder aus­lösen.
• Soll eine ganze Zeile statt ein­er Spalte aus­gew­ertet wer­den, dann würde statt If Target.Column = 1 beispiel­sweise If Target.Row = 2 (für Zeile 2) ver­wen­det wer­den.

Kurz zusam­menge­fasst: Wird in irgend eine Zelle der Spalte A ei beliebiger Wert eingegeben, dann wird automa­tisch das Makro aktiv, prüft, ob es sich um eine Zahl han­delt und wird dann die Zahl entsprechend for­matieren. Die Rück­gabe ist eben­falls ein Text. Den­noch kann mit den Zahlen­werten gerech­net wer­den, Excel erken­nt von alleine, dass es sich hier um einen numerischen Text han­delt. Wenn Sie das Ganze aus­pro­bieren wollen, dann find­en Sie in dieser Datei, Tabelle1 die richtige „Spiel­wiese”. Der Code ist bere­its an der richti­gen Stelle und wird natür­lich auch kor­rekt aus­ge­führt.

▲ nach oben …

Ein zusammenhängender Bereich

Soll ein zusam­men­hän­gen­der Bere­ich eines Tabel­len­blattes aus­gew­ertet wer­den, dann ist der Code doch eine Idee aufwendi­ger und auf den ersten Blick vielle­icht doch nicht so ganz leicht zu ver­ste­hen. Angenom­men, diese Funk­tion­al­ität soll nur die Zellen A3:A10 betr­e­f­fen. 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 entschei­dende Änderung am Code:

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

Mit dieser etwas ver­schrobe­nen, ver­schachtel­ten For­mulierung (Not … Is Nothing) wird fest­gestellt, ob eine Schnittmenge zwis­chen Tar­get (die Zel­ladresse) und dem angegebe­nen Bere­ich existiert. Falls ja, wird der weit­ere Code aus­ge­führt. Dieses Beispiel find­en Sie in Tabelle2 der Mus­ter­datei. Der Test-Bere­ich ist hier grün hin­ter­legt, damit Sie rasch sehen, wo die Zahlen entsprechend umge­wan­delt wer­den und wo nicht.

▲ nach oben …

Mehrere Bereiche

Prinzip­iell ist das Vorge­hen gle­ich, wenn Sie mehrere Bere­iche ver­wen­den. Da aber die Funk­tion Intersect immer nur zwei Argu­mente akzep­tiert, muss im Vor­wege dafür Sorge getra­gen wer­den, dass die ver­schiede­nen getren­nten Bere­iche zu einem logis­chen Bere­ich zusam­menge­fasst wer­den. Sehen Sie sich diesen Code an oder schauen Sie auch gerne in Tabelle3 der Muster-Mappe nach. Hier sind noch so einige Dinge einge­baut, die sich auch in den vorheri­gen Ver­sio­nen gut gemacht hät­ten:

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 wirk­lich neuen bzw. geän­derten Code habe ich hier aufgezeigt:

• Dim …: Dekla­ra­tion der Vari­ablen.
• With … End With: Anweisungs­block, um den Code kürz­er und sicher­er zu gestal­ten
• Set rng1 … : Zuweisung eines Bere­ichs an eine Vari­able
• Set rngAll = Union(rng1 …: Zusam­men­fas­sung aller drei Einzel­bere­iche zu einem einzi­gen logis­chen Bere­ich, damit <code>Intersect</code> kor­rekt arbeit­en kann.
• If rng1.NumberFormat …: Prü­fung, ob das Zahlen­for­mat in dem entsprechen­den Bere­ich Text ist, wenn nicht, dann set­zen.
• If Target = "," Then Target = "": Wenn der vorhan­dene Inhalt ein­er Zelle im betrof­fe­nen gelöscht wird, verbleibt son­st ein Kom­ma.

Acht­en Sie bei den Set rng_ – Anweisun­gen ein­mal auf die ver­schiede­nen Möglichkeit­en. Sie wer­den, nein Sie soll­ten der Über­sichtlichkeit und der Ein­heitlichkeit wegen eine einzige Schreib­weise ver­wen­den. Vielle­icht auch ein­mal eine weit­ere, aber bitte nicht so einen „Gemis­cht­waren­laden” wie im obi­gen Code. Dort war es die Inten­tion ein­mal aufzuzeigen, dass vieles möglich ist und zum Ziel führt. Bitte sehen Sie in der VBA-Hil­fe nach, wie die ver­schiede­nen Begriffe genau ver­wen­det wer­den. Hier soll nur angestoßen wer­den, wie solch ein Pro­gramm sin­nvoll aufge­baut sein kann. Ich gebe zu, dass da noch so einiges möglich ist. Es lässt sich kom­prim­iert­er schreiben, vielle­icht auch eine Idee schneller machen. Aber hier geht es in erster Lin­ie um die Basics und darum, wie Sie sich­er zum Ziel kom­men.

▲ nach oben …

Eine Alter­na­tive möchte ich Ihnen doch noch anbi­eten. Sie stellt eher die umgekehrte Vari­ante dar: Das Kom­ma soll so geset­zt wer­den, dass unab­hängig von der Länge der Eingabe min­destens eine Zif­fer vor dem zu set­zen­den Kom­ma und 2 Zif­fern danach angezeigt wer­den. Wird beispiel­sweise nur eine einzelne Zif­fer eingegeben, dann muss 0,0 davor geset­zt wer­den. In Tabelle4 der bekan­nten Mappe sehen Sie, wie wir das gelöst haben. Um das Ganze nicht zu sehr aufzublähen, ist die Tabelle2 als Basis ver­wen­det wor­den.

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än­dert, und schon kommt ein vol­lkom­men anderes Ergeb­nis her­aus.