[Solved] Help converting an Excel VBA function

[Dolphin975]

Hi to all,
I need your help translating this Excel VBA function into StarOffice VBA.
Basically, it checks if an IBAN code (banking account number used in Europe and elsewhere) is valid.
Examples are:

DE89370400440532013000
IT23Q0542801602000000009390

A problem I know is that StarOffice VBA doesn't have the LIKE operator. Is there a way to "simulate" it?
Apart from this, I also have problems with the rest of the function...

Any help would be highly appreciated, thank you very much!

Code: Select all

Function IsFormatIBAN(ByVal S As String) As Boolean
  Dim X As Long, DigitValue As Long, Total As Long
  If Not S Like "*[0-9A-Z ]*" Then Exit Function
  S = Replace(S, " ", "")
  S = Mid(S, 5) & Left(S, 4)
  For X = 65 To 90 'A to Z
    S = Replace(S, Chr(X), X - 55)
  Next
  S = StrReverse(S)
  DigitValue = 1
  Total = Left(S, 1)
  For X = 2 To Len(S)
    DigitValue = 10 * DigitValue Mod 97
    Total = Total + Mid(S, X, 1) * DigitValue
  Next
  IsFormatIBAN = (Total Mod 97) = 1
End Function

[Dolphin975]

The problem is that in my office I must use StarOffice 8... I know it's stupid but that's what we have...

[MrProgrammer]

If Not S Like "*[0-9A-Z ]*" Then Exit Function

Do you really want to duplicate this bogus code? I presume the intent is to validate that the string contains only digits and upper case letters. But the code actually validates that the string contains at least one digit or upper case letter! So the string below matches the LIKE test and is accepted as a valid IBAN code. The first asterisk in the pattern matches the leftmost two characters, the bracketed range matches the X, and the last asterisk matches the rightmost four characters. Are you a programmer? This forum is not a good way to learn programming. Just loop through the string and validate the characters one at a time by testing their character code. If you don't know how to do that, just delete the statement. It didn't do proper validataion anyway and no one complained.
⌘⤵X⬟☠⠫☑

The problem is that in my office I must use StarOffice 8

So get the clown who made that rule to help you with this problem.

[Zizi64]

Like "*[0-9A-Z ]*"

Try to use a cycle and the InStr() function inside the cycle. The InStr() can examine if a string is containing a character (or a substring) - or is not.

[Villeroy]

menu:Tools>Options>Calc>Calculation
[X] Regular expressions in formulas = ON [this is a per-document setting]

Test if the string in A1, with spaces removed, starts with 2 letters between A and Z, followed by 9 digits at least, followed by nothing else (end of string):
=SEARCH("^[A-Z]{2}[0-9]{9,}$";SUBSTITUTE(A1;" ";""))=1

I'm not sure if this is a valid pattern for all countries but it may be a starting point.

[Dolphin975]

Hi Villeroy,
thank you for your hint.

I get "unknown symbol" error for ^

[Villeroy]

Sorry, the regex needs to be in double-quotes.
=SEARCH("^[A-Z]{2}[0-9]{9,}$";SUBSTITUTE(A1;" ";""))=1

[Dolphin975]

Hi and thanks to all.

I found a code for Excel that works perfectly for Star Office.
It just check the Italian IBAN but it's sufficient for my needs.

Public Function ControlloIBAN(IBAN As String) As Boolean
ControlloIBAN = False
Dim CheckStr As String
Dim NewIBAN As String
Dim nASCII As Integer
Dim Resto As Variant
Dim Cifra As Double
Dim Quoziente As Double
Dim Intero As Double
Dim n As Integer
If Len(IBAN) <> 27 Then
MsgBox "Lunghezza IBAN errata!", , "Errore"
Exit Function
End If
NewIBAN = Right(IBAN, 23) & Left(IBAN, 4)
CheckStr = ""
For i = 1 To 27
nASCII = Asc(Mid(NewIBAN, i, 1))
Select Case nASCII
Case 48 To 57 ' da 0 a 9
CheckStr = CheckStr & Mid(NewIBAN, i, 1)
Case 65 To 90
CheckStr = CheckStr & CStr(nASCII - 55)
Case Else
'MsgBox "Carattere non valido!", , "Errore"
Exit Function
End Select
Next
n = 1
Do While n <= Len(CheckStr)
Cifra = CDbl(CStr(Resto) & Mid(CheckStr, n, 8))
Quoziente = Cifra / 97
Intero = 97 * Int(Quoziente)
Resto = Cifra - Intero
n = n + 8
Loop
If Resto = 1 Then ControlloIBAN = True
End Function

[Lupp]

I found a code for Excel that works perfectly for Star Office.

How do you know?

[Villeroy]

It does not recognize my German IBAN numbers nor does it recognize most of the IBANs that are described here: https://en.wikipedia.org/wiki/Internati ... by_country

https://codereview.stackexchange.com/qu ... validation

[Dolphin975]

Hi, as I wrote, the function just checks Italian IBANs and it works for them. I know because I checked several.

[Villeroy]

To the casual reader,
the following Python code from https://codereview.stackexchange.com/qu ... validation checks all IBAN codes. This works with Python3 only, so it can be used with LibreOffice but not OpenOffice. With Python2 (OpenOffice) the translate function does not work. I think you need a slightly less effective approach with Python2

Code: Select all

import string
from itertools import chain

_LETTERS = chain(enumerate(string.digits + string.ascii_uppercase),
                 enumerate(string.ascii_lowercase, 10))
LETTERS = {ord(d): str(i) for i, d in _LETTERS}

def _number_iban(iban):
    return (iban[4:] + iban[:4]).translate(LETTERS)


def generate_iban_check_digits(iban):
    number_iban = _number_iban(iban[:2] + '00' + iban[4:])
    return '{:0>2}'.format(98 - (int(number_iban) % 97))

def valid_iban(iban):
    return int(_number_iban(iban)) % 97 == 1


if __name__ == '__main__':
    my_iban = 'ro13 rzbr 0000 0600 0713 4800'.replace(' ', '')
    if generate_iban_check_digits(my_iban) == my_iban[2:4] and valid_iban(my_iban):
        print('IBAN ok!\n')
    else:
        print('IBAN not ok!\n')