Le nettoyage et la maintenance des bases de données (BD) nécessite parfois des fonctions spécifiques. Si vous possédez une BD avec des informations sur vos clients ou vos fournisseurs, vous aurez probablement un jour besoin de vérifier IBAN de ces derniers. Cela peut vous éviter de mauvaises surprises. Il en va de même pour la vérification des entrées dans des formulaires.
IBAN: La structure du numéro de compte et la vérification de son validité
IBAN – le numéro de compte international (International Bank Account Number), qui devient le standard dans le monde bancaire, a un format spécifique qui permet de vérifier s'il s'agit d'un numéro valide ou pas. La fonction que je vous propose dans cet article peut faire cette vérification pour vous de manière rapide et fiable. En l'utilisant, vous éviterez "l'erreur humaine".
Voici le schéma de la structure d'un numéro IBAN. Sachez que la longueur de la dernière partie (le BBAN)peut varier selon le pays où le compte est domicilié… La longueur de l'IBAN entier est donc également variable!
Format du numéro de compte IBAN (source: Wikipedia)
Plus d'informations sur le format des comptes IBAN ainsi que sur le calcul de vérification: IBAN sur Wikipédia
Fonction VBA pour vérifier le numéro de compte IBAN
La fonction suivante peut être utilisé dans les Feuilles Excel en tant que UDF (User Defined Function) ou dans vos projets VBA. Elle utilise un argument – le numéro IBAN, et retourne VRAI ou FAUX en tant que Boolean selon que le numéro de compte est valide ou pas.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
Public Function CorrectIBAN(NumeroIBAN As String) As Boolean ' --> Function pour vérifier si le numéro de compte IBAN est correct 'par: Excel-Malin.com ( https://excel-malin.com ) On Error GoTo IBANErreur Dim IBAN_chaine As String Dim IBAN_nettoye As String Dim IBAN_char, IBAN_char2 As Integer Dim IBAN_char_test, IBAN_char_test2 As Variant Dim IBAN_position As String Dim IBAN_numeric As String Dim IBAN_reduit Dim IBAN_reduit_modulo Dim IBAN_modulo Dim IBAN_final IBAN_chaine = CStr(NumeroIBAN) IBAN_nettoye = "" 'supprimer les caractères indésirables (garde: 0-9 et A-Z, transforme minuscules en majuscules) For IBAN_char = 1 To Len(IBAN_chaine) IBAN_char_test = Mid(IBAN_chaine, IBAN_char, 1) Select Case IBAN_char_test Case 0 To 9, "a" To "z", "A" To "Z" IBAN_nettoye = IBAN_nettoye & UCase(IBAN_char_test) Case Else End Select Next IBAN_char 'replacer les 4 premiers caractères à la fin IBAN_position = Right(IBAN_nettoye, Len(IBAN_nettoye) - 4) & Left(IBAN_nettoye, 4) 'remplacer les lettres par chiffres IBAN_numeric = "" For IBAN_char2 = 1 To Len(IBAN_position) IBAN_char_test2 = Mid(IBAN_position, IBAN_char2, 1) Select Case IBAN_char_test2 Case "A" To "Z" IBAN_numeric = IBAN_numeric & CStr(Asc(IBAN_char_test2) - 55) Case Else IBAN_numeric = IBAN_numeric & IBAN_char_test2 End Select Next IBAN_char2 'vérifie le modulo 97 (doit être égal à 1) If Len(IBAN_numeric) >= 28 Then IBAN_reduit = CDec(Left(IBAN_numeric, 10)) IBAN_reduit_modulo = IBAN_reduit - Fix(IBAN_reduit / 97) * 97 IBAN_final = CDec(IBAN_reduit_modulo & (Right(IBAN_numeric, Len(IBAN_numeric) - 10))) Else IBAN_final = CDec(IBAN_numeric) End If IBAN_modulo = IBAN_final - Fix(IBAN_final / 97) * 97 ' résultat de la vérification If IBAN_modulo = 1 Then CorrectIBAN = True Else CorrectIBAN = False End If Exit Function IBANErreur: 'CorrectIBAN = CVErr(xlErrValue) CorrectIBAN = False End Function |
Exemple de vérification de validité de l'IBAN en VBA
Dans cet exemple, imaginons que le numéro de compte IBAN se trouve dans la cellule A2. La procédure suivante le vérifie à l'aide de la fonction CorrectIBAN() et affiche un message pour indiquer si le numéro IBAN est valide ou pas.
Ceci n'est qu'un exemple. Vous pouvez évidemment modifier ce code VBA selon vos besoins. (n'oubliez pas que pour faire fonctionner ce code, vous devez copier dans votre Module le code de cet exemple mais aussi le code de la fonction CorrectIBAN() )
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub Exemple_verification_IBAN() 'par: https://excel-malin.com IBAN_a_verifier = Range("A2").Value Resultat_verification = CorrectIBAN(IBAN_a_verifier) If Resultat_verification = True Then MsgBox "Le numéro IBAN est correct" Else MsgBox "Le numéro IBAN n'est pas correct" End If End Sub |
Pour aller plus loin en VBA
Voici quelques autres articles qui pourraient vous intéresser…
- Si vous avez besoin des fonctions VBA pour vérifier les informations dans une base de données (de clients, de fournisseurs etc.), cette fonction pourrait également vous intéresser: Vérifier la validité d'une adresse e-mail
- Vous avez des affinités avec le monde financier ou comptable? Essayez notre Calculatrice de Gestion. Vos remarques sont bienvenues!
- Liste de toutes les fonctions disponibles en VBA
- Utilisation de RECHERCHEV en VBA
- Manipulation des fichiers en VBA – ouverture, copie, suppression,…
14 commentaires sur “Vérifier IBAN en Excel / en VBA”
Bonjour,
9a fonctionne très bien sauf sur les iban de la caisse des dépots
Bonjour. Vous pourriez mentionner un exemple de n°IBAN pour lequel le code VBA ne fonctionne pas?
Merci,
Martin
le voici : FR10 4003 1000 0100 0017 0871 D53
Ceci explique pourquoi cet IBAN ne marche pas.
En fait, ce IBAN est trop long pour être vérifié en tant que numéro (ligne 36 du code).
Le type
Decimalen VBA peut travailler avec les nombres allant jusqu'à 28 chiffres. Or, la forme numérique de cet IBAN a 30 chiffres…Je vais voir si je peux trouver une solution.
Si cela dépasse les 30 chiffre il est possible de découper le modulo :
"S'il y a trop de chiffres (plus de 30) il est possible que votre machine ne puisse pas faire un si gros calcul. Dans ce cas, prendre les x premiers (disons les 10 premiers chiffres par exemple). Calculer ce nombre modulo 97 et le remplacer par le reste au début des autres chiffres. Refaire le modulo du nouveau nombre obtenu. "
Ce qui peut donner ceci dans le code :
'vérifie le modulo 97 (doit être égal à 1)
If Len(IBAN_numeric) >= 30 Then
IBAN_réduit = CDec(Left(IBAN_numeric, 10))
IBAN_réduit_modulo = IBAN_réduit – Fix(IBAN_réduit / 97) * 97
IBAN_final = CDec(IBAN_réduit_modulo & (Right(IBAN_numeric, Len(IBAN_numeric) – 10)))
Else
IBAN_final = CDec(IBAN_numeric)
End If
IBAN_modulo = IBAN_final – Fix(IBAN_final / 97) * 97
Excellent!!!
Merci pour cette remarque Raphael.
Je viens de l'intégrer dans le code de la fonction. Elle en devient beaucoup plus pratique…
Cordialement, Martin
La macro fonctionne sauf pour les IBAN CREDIT LYONNAIS qui ont une lettre en 25 èm position
Et au total, cet IBAN (de Crédit Lyonnais) a combien de caractères?
>>> Attention, un chiffre compte pour 1 caractère tandis que les lettres comptent pour 2 caractères!
Si c'est plus de 28, alors la réponse est dans mon commentaire plus haut – l'IBAN est trop long pour être géré en VBA.
Martin
Merci de votre réponse rapide.
J'avais pas compris la manière de compter. En effet, il y a 27 caractères dont 3 lettres ce qui fait donc 30 caractères (les 2 lettres du début et une troisième en 25 éme position.
Y a-t-il une autre macro pour pouvoir surveillez les éventuelles fautes de frappes lorsque l'on rentre un rib dans la base de donnée clients?
Bonjour,
J'ai un soucis au niveau du remplacement des lettres par des chiffres. Cela fait une erreur. Auriez-vous une autre techniques pour changer les lettres en chiffres (A = 10, …) ?
Merci de votre aide
Bonjour,
pourriez-vous me dire quel type d'erreur exactement vous rencontrez? Et à quel endroit du code?
C'est étonnant, car ce code ne devrait pas générer d"erreurs..
Cordialement, Martin
Bonjour,
Super idée de code mais serait-il possible que la vérification s'effectue automatiquement quand on valide la saisie de l'IBAN dans la cellule correspondante et non pas à l'aide d'un bouton
Bonjour Michel,
Je me permets de vous répondre.
Vous trouverez ci-dessous le code qui permet de le faire. Colonne A : les IBAN, colonne B : test automatiquement si l'IBAN est correct ou non :
Excellent! Merci Monsieur Hudec