在Excel中验证IBAN

Question

是否有现成的excel表格来验证IBAN?我将进入IBAN，它将显示有效或无效。

我已经搜索了许多add In并找到了这个

但是我不知道怎么打开它。有人能帮忙吗?

Answer 1

这个怎么样，格式问题解决了吗，进行了"97检查":

Public Function VALIDATEIBAN(ByVal IBAN As String) As String
' Created by : Koen Rijnsent (www.castoro.nl)
' Inspired by : Chris Fannin (AbbydonKrafts)
' Inspired by : bonsvr (http://stackoverflow.com/users/872583/bonsvr)
On Error GoTo CatchError
Dim objRegExp As Object
Dim IBANformat As Boolean
Dim IBANNR As String
Dim ReplaceChr As String
Dim ReplaceBy As String
'Check format
IBAN = UCase(IBAN)
Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "[a-zA-Z]{2}[0-9]{2}[a-zA-Z0-9]{4}[0-9]{7}([a-zA-Z0-9]?){0,16}"
IBANformat = objRegExp.Test(IBAN)
'Validity of country code will not be checked!
If IBANformat = False Then
    VALIDATEIBAN = "FORMAT NOT RECOGNIZED"
Else
    'Flip first 4 characters to the back
    IBANNR = Right(IBAN, Len(IBAN) - 4) & Left(IBAN, 4)
    'Replace letters by the right numbers
    For Nr = 10 To 35
        ReplaceChr = Chr(Nr + 55)
        ReplaceBy = Trim(Str(Nr))
        IBANNR = Replace(IBANNR, ReplaceChr, ReplaceBy)
    Next Nr
    'Loop through the IBAN, as it is too long to calculate at one go
    CurrPart = ""
    Answer = ""
    For CurrDigit = 1 To Len(IBANNR)
        CurrPart = CurrPart & Mid$(IBANNR, CurrDigit, 1)
        CurrNumber = CLng(CurrPart)
        'If the number can be divided
        If 97 <= CurrNumber Then
            LeftOver = CurrNumber Mod 97
            WorkValue = (CurrNumber - LeftOver) / 97
            Answer = Answer & CStr(WorkValue)
            CurrPart = CStr(LeftOver)
        Else
            'If no division occurred, add a trailing zero
            If Len(Answer) > 0 Then
                Answer = Answer & "0"
                'Exception for the last number
                If CurrDigit = Len(IBANNR) Then
                    LeftOver = CurrNumber Mod 97
                Else
                End If
            Else
            End If
        End If
    Next
    If LeftOver = 1 Then
        VALIDATEIBAN = "IBAN OK"
    Else
        VALIDATEIBAN = "97 CHECK FAILED"
    End If
End If
Exit Function
CatchError:
    VALIDATEIBAN = "ERROR: " & Err.Description
    MsgBox "Module: " & MODULE_NAME & " - VALIDATEIBAN function" & vbCrLf & vbCrLf _
    & "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

Answer 2

Option Compare Database
Option Explicit
   ' http://en.wikipedia.org/wiki/International_Bank_Account_Number
   Private Const IbanCountryLengths As String =    "AL28AD24AT20AZ28BH22BE16BA20BR29BG22CR21HR21CY28CZ24DK18DO28EE20FO18" & _
                                                "FI18FR27GE22DE22GI23GR27GL18GT28HU28IS26IE22IL23IT27KZ20KW30LV21LB28" & _
                                                "LI21LT20LU20MK19MT31MR27MU30MC27MD24ME22NL18NO15PK24PS29PL28PT25RO24" & _
                                                "SM27SA24RS22SK24SI19ES24SE24CH21TN24TR26AE23GB22VG24QA29"
   Private Function ValidateIbanCountryLength(CountryCode As String, IbanLength    As Integer) As Boolean
    Dim i As Integer
    For i = 0 To Len(IbanCountryLengths) / 4 - 1
        If Mid(IbanCountryLengths, i * 4 + 1, 2) = CountryCode And _
                    CInt(Mid(IbanCountryLengths, i * 4 + 3, 2)) = IbanLength Then
            ValidateIbanCountryLength = True
            Exit Function
        End If
    Next i
    ValidateIbanCountryLength = False
End Function
Private Function Mod97(Num As String) As Integer
    Dim lngTemp As Long
    Dim strTemp As String
    Do While Val(Num) >= 97
        If Len(Num) > 5 Then
            strTemp = Left(Num, 5)
            Num = Right(Num, Len(Num) - 5)
        Else
            strTemp = Num
            Num = ""
        End If
        lngTemp = CLng(strTemp)
        lngTemp = lngTemp Mod 97
        strTemp = CStr(lngTemp)
        Num = strTemp & Num
    Loop
    Mod97 = CInt(Num)
End Function
Public Function ValidateIban(IBAN As String) As Boolean
    Dim strIban As String
    Dim i As Integer
    strIban = UCase(IBAN)
    ' Remove spaces
    strIban = Replace(strIban, " ", "")
    ' Check if IBAN contains only uppercase characters and numbers
    For i = 1 To Len(strIban)
        If Not ((Asc(Mid(strIban, i, 1)) <= Asc("9") And Asc(Mid(strIban, i, 1)) >= Asc("0")) Or _
                (Asc(Mid(strIban, i, 1)) <= Asc("Z") And Asc(Mid(strIban, i, 1)) >= Asc("A"))) Then
            ValidateIban = False
            Exit Function
        End If
    Next i
    ' Check if length of IBAN equals expected length for country
    If Not ValidateIbanCountryLength(Left(strIban, 2), Len(strIban)) Then
        ValidateIban = False
        Exit Function
    End If
    ' Rearrange
    strIban = Right(strIban, Len(strIban) - 4) & Left(strIban, 4)
    ' Replace characters
    For i = 0 To 25
        strIban = Replace(strIban, Chr(i + Asc("A")), i + 10)
    Next i
    ' Check remainder
    ValidateIban = Mod97(strIban) = 1
End Function

来源:http://www.aswinvanwoudenberg.com/2013/07/18/vba-iban-validator/

Answer 3

我发现bonsvr的答案很有帮助，谢谢。从我对代码的阅读来看，这似乎是特定于CZ范围的帐户。

由于我主要处理爱尔兰，英国和德国的IBAN代码，我开发了这个正则表达式来替换objRegExp.Pattern =…与…

objRegExp.Pattern = "^[GB|IE]{2}d{2}[a-zA-Z]{4}d{14}|[DE]d{20}$"

我希望这能帮助别人，因为最初的代码帮助了我。如果您希望添加自己的国家，请扩展上述内容。

注意:我删除了空格([ ])的规定，因为我正在测试的文本没有空格。如果您希望每4个字符重新添加这些，这很容易做到—或者更简单，将上面的第一行代码替换为:

IBAN = Trim(Ucase(Replace(IBAN, " ", "")))

，它将消除空格，修剪掉前面和后面的任何额外空格，并转换为大写。(装饰可能是多余的，但皮带和背带…)

正则表达式由[GB|IE] (GB或IE)组成，允许ISO国家代码后跟相同格式的2位校验和，4个字符的银行代码和之后的14位数字，就像这两个国家IBAN格式的情况一样。[DE]允许另一个"或"表示德国，后跟22位数字。要添加另一个国家，只需将文本放在$符号之前，以|开头。

在这里查找其他国家/地区的格式。(维基百科)

Answer 4

这很简单，只需使用下面的函数。

'' Validate IBAN
Public Function VALIDATEIBAN(ByVal IBAN As string) As Boolean
On Error GoTo Catch
Dim objRegExp As Object
Dim blnIsValidIBAN As Boolean
Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "^[a-zA-Z]{2}d{2}[ ]d{4}[ ]d{4}[ ]d{4}[ ]d{4}[ ]d{4}|CZd{22}$"

blnIsValidIBAN = objRegExp.Test(IBAN)
VALIDATEIBAN = blnIsValidIBAN
Exit Function
Catch:
VALIDATEIBAN = False
MsgBox "Module: " & MODULE_NAME & " - VALIDATEIBAN function" & vbCrLf & vbCrLf _
    & "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

如何使用:

Copy the code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Insert -> Module.
Paste code.
Save and Exit VBE.

运行函数:

现在你在Excel中有一个用户定义的函数，就像内置的SUM, AVG函数一样。假设您想在单元格A1中验证IBAN，只需在任意单元格=VALIDATEIBAN(A1).中写入它将返回TRUE或FALSE

此外，它将同时适用于:

ES65 0800 0000 1920 0014 5399

和

ES6508000000192000145399

But NOT:

ES65-0800-0000-1920-0014-5399