vba 在 Excel 中验证 IBAN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8377306/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Validate IBAN in Excel
提问by user1080320
Is there any ready excel sheet to validate the IBAN? I will enter the IBAN and it will show valid or invalid.
是否有任何现成的 excel 表来验证 IBAN？我将输入 IBAN，它会显示有效或无效。
I have searched for a number of Adds In and found this one
我搜索了许多 Adds In 并找到了这个
But I don't know how to open it. Can anyone help?
但是不知道怎么打开。任何人都可以帮忙吗？
采纳答案by bonsvr
It's easy, just use the below function.
这很简单，只需使用以下功能即可。
'' 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}|CZ\d{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
How to use:
如何使用：
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.
Run the function:
运行函数：
Now you have a user defined function in Excel, just like the built in SUM, AVG functions.
Let's say you want to validate IBAN in cell A1, just write in any cell =VALIDATEIBAN(A1).It will return TRUE or FALSE.
现在您在 Excel 中有一个用户定义的函数，就像内置的 SUM、AVG 函数一样。假设您想在单元格 A1 中验证 IBAN，只需在任何单元格中写入=VALIDATEIBAN(A1).它将返回 TRUE 或 FALSE。
Besides, it will apply to both:
此外，它将适用于：
ES65 0800 0000 1920 0014 5399
and
和
ES6508000000192000145399
But NOT:
但不是：
ES65-0800-0000-1920-0014-5399
回答by Koen Rijnsent
What about this, has the formatting issue solved and carries out the "97 check":
怎么样，格式化问题解决了并执行“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
回答by selami sahin
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
source :http://www.aswinvanwoudenberg.com/2013/07/18/vba-iban-validator/
来源：http: //www.aswinvanwoudenberg.com/2013/07/18/vba-iban-validator/
回答by ConnDublin
I found the answer from bonsvr helpful, thank you. From my reading of the code, it seems this was specific to the CZ range of accounts.
我发现 bonsvr 的答案很有帮助，谢谢。从我对代码的阅读来看，这似乎是特定于 CZ 范围的帐户。
As I deal mostly with Irish, UK and German IBAN codes I developed this regular expression to replace the line with objRegExp.Pattern =... with ...
因为我主要处理爱尔兰、英国和德国的 IBAN 代码，所以我开发了这个正则表达式来用objRegExp.Pattern =...替换该行...
objRegExp.Pattern = "^[GB|IE]{2}\d{2}[a-zA-Z]{4}\d{14}|[DE]\d{20}$"
I hope this helps someone else, as the initial code helped me. If you wish to add your own countries, extend the above.
我希望这对其他人有帮助，因为最初的代码帮助了我。如果您想添加自己的国家/地区，请扩展上述内容。
Note: I removed the provision of spaces ([ ]) as the text I am testing has none. If you wish to add these back every 4 characters, this is easy to do - or simpler still, replace the first line of the code above with:
注意：我删除了空格 ( [ ])的规定，因为我正在测试的文本没有。如果您希望每 4 个字符添加一次，这很容易 - 或者更简单，将上面代码的第一行替换为：
IBAN = Trim(Ucase(Replace(IBAN, " ", "")))
which will eliminate the spaces, trim off any extra spaces at the front and back and convert to upper case. (Trim may be redundant, but belts and braces...)
这将消除空格，修剪前后任何多余的空格并转换为大写。（修剪可能是多余的，但腰带和背带......）
The regex expression is made up of [GB|IE] (GB or IE) allows for either ISO country code followed by the same format of the 2-digit checksum, the 4 character bank code and 14 digits thereafter, as is the case for these two country IBAN formats. The |[DE] allows for another "or" for Germany, followed by 22 digits. To add another country, simply put your text before the $ sign, starting with |.
正则表达式由 [GB|IE]（GB 或 IE）组成，允许 ISO 国家代码后跟相同格式的 2 位校验和、4 个字符的银行代码和其后的 14 位数字，就像这两种国家IBAN格式。|[DE] 允许另一个“或”代表德国，后跟 22 位数字。要添加另一个国家/地区，只需将文本放在 $ 符号之前，以 | 开头。
Find other country formats here. (Wikipedia)
在此处查找其他国家/地区格式。（维基百科）
