Office: IBAN automatisch erkennen und in 4er-Blöcken darstellen

WS-53 · 31. Januar 2018

Hallo,

für ISBN und Sozialversicherungsnummern gibt es ja Sonderformate. Da aber die IBAN mit einer 2-stelligen Buchstabenkombination beginnt, ist diese keine Zahl und kann somit auch nicht mit einem benutzerdefiniertem Zahlenformat, formatiert werden.

Da ich aber der Meinung war, dass die Darstellung der IBAN, in der gängigen Form der 4er-Blöcke, eine gute VBA-Übungsaufgabe ist, habe ich mich seit gestern mit dieser Fragestellung beschäftigt.

Ziel war es dabei, dass nach der Eingabe einer IBAN, diese automatisch erkannt, in der gwünschten Form aufbereitet und ausgegeben wird.

Da ich nicht rausgefunden habe, wie ich in Excel Letter-Spacing (Anpassen Zeichenabstand) anwenden könnte, habe ich Leerzeichen eingefügt.

Dies hat halt den Nachteil, das die aufbereitetet IBAN nicht mehr per Copy und Paste in ein Überweisungsformular eingefügt werden kann.

Wenn der Inhalt der Zelle nicht umgewandelt wird kann dies folgende Gründe haben:

- Der Inhalt ist alles andere, aber keine IBAN
- Der inhalt wurde bereits als IBAN erkannt und umgewandelt
- Die erfasste Prüfziffer (3. und Stelle der IBAN) ist nicht identsich mit der errechneten Prüfziffer

Hier der Code, der bei mir tut was er soll.
Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

' IBAN zwecks besserer Lesbarkeit in 4er-Blöcken darstellen,
' die jeweils durch ein Leerzeichen getrennt werden
'
' Der Inhalt einer Zelle wird als IBAN interpretiert,
' wenn folgende Bedingungen erfüllt sind
' 1. Stelle 1 und 2 muss Großbuchstaben enthalten
' 2. Stelle 3 und 4 muss numerisch sein (Prüfziffer)
' 3. Stelle 5 bis 12 muss numerisch sein (BLZ)
' 4. Stelle 13 bis 34 muss numerisch sein (Kto-Nr.)
' 5. Die Prüfziffer aus Stelle 3-4 muss idetisch mit der Prüfziffer sein,
' die sich aus Berechnung der Prüfziffer ergibt.
' Feb. 2018 / WS-53

Dim IBAN As String
Dim x As Integer

' Hier kann eine Eingrenzung auf eine Spalte, sowie Zeile ab, sinnvoll sein

' 1. Prüfung
If Not Mid(Cells(Target.Row, Target.Column), 1, 1) >= "A" And _
Mid(Cells(Target.Row, Target.Column), 1, 1) = "A" And _
Mid(Cells(Target.Row, Target.Column), 2, 1)

:)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

lupo1 · 2. Februar 2018

Da aber die IBAN mit einer 2-stelligen Buchstabenkombination beginnt, ist diese keine Zahl
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Selbst, wenn nicht: 18 Zahlen-Stellen sind bei IBAN außerdem genauso zu lang, wie 16 Stellen bei Kreditkartennummern. Auch deshalb scheiterst Du mit Zahlenformaten, da Kreditkartennummern nicht zwangsläufig mit 0 enden und IBANs nicht mit 000.

Tipp: Gib Deine IBAN einmal gespaced und einmal ungespaced aus. Dann kann man sich die passende selbst greifen. Nicht jeder Anwender ist dumm.

WS-53 · 2. Februar 2018

Hallo Lupo,

ich habe das Problem, dass die IBAN nun nicht einfach kopiert werden kann, durch ein Doppelklick-Makro gelöst.

Bei einem Doppelklick auf eine Zelle, die eine IBAN enthält, werden die Leerzeichen entfernt und dieses Ergebnis in die Zwischenablage kopiert.

Dabei ist allerdings zu beachten, dass die Zwischenablage aus einem Makro heraus nur angesprochen werden kann, wenn vorher aus dem VBA-Editor, über den Weg: Extras > Verweise, die "Microsoft Forms 2.0 Object Library" eingebunden wurde.

Weiterhin musste ich ich beide Codes noch um die Prüfung:

Code:

If Target.HasFormula Then
Exit Sub
End If
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

ergänzen.
Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

' Enthält die Zelle, die per Doppelklick angesprochen wurde eine
' IBAN, dann wird diese in die Zwischenablage übernommmen.
' Damit die Zwischenablage angesprochen werden kann,
' muss vorher die "Microsoft Forms 2.0 Object Library" eingebunden werden.
' Dies geht aus dem VBA-Editor über den Weg: Extras > Verweise
' Feb. 2018 / WS-53

Dim IBAN As String
Dim objClip As dataobject

' keine Aktion, wenn die Zelle eine Formel enthält
If Target.HasFormula Then
Exit Sub
End If

IBAN = Cells(Target.Row, Target.Column)

'Leerzeichen entfernen
IBAN = Replace(IBAN, " ", "")

'Enthalten die Ersten 2 Stellen eine Buchstabenkombination
If Not Mid(IBAN, 1, 1) >= "A" And _
Mid(IBAN, 1, 1) = "A" And _
Mid(IBAN, 2, 1)
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Office: IBAN automatisch erkennen und in 4er-Blöcken darstellen

IBAN automatisch erkennen und in 4er-Blöcken darstellen

IBAN automatisch erkennen und in 4er-Blöcken darstellen

IBAN automatisch erkennen und in 4er-Blöcken darstellen - Similar Threads - IBAN automatisch erkennen

IBAN kontrollieren

IBAN in Vierblöcke aufteilen

IBAN Berechnen

AT IBAN berechnen mit Formel

Automatische Platzhalter IBAN

Eingabeformat IBAN für deutsche IBAN Nummern

IBAN in Excel automatisch berechnen