German VAT Number validation

[MrHans]

Hi guys,

I have a need to validate German VAT numbers using the check digit formula.
The German VAT number starts with DE followed by 8 digits and 1 check digit.
There is a formula that you can use the calculate the check digit and with that validate if the number is correct.

The formula is described here:
http://zylla.wipos.p.lodz.pl/ut/translation.html
(look for the German version)

DE136695976 VALID
DE136695978 NOT VALID

I really don't understand how the formula works and also no idea how the function should work.
Is there anyone around that could get me going with this?

Thanks in advance.

 

[MrHans]

Ok... that was easier then expected.
I created the function below and it seems to work as expected.
Any feedback is always welcome, but for now it seems to be solved.

Public Function CalcGermanVAT(strVatNr As String) As Boolean

    'Declare variables
    Dim IntCnt As Integer
    Dim intProduct As Integer
    Dim intSum As Integer
    Dim intCheckDigit As Integer
    Dim intResult As Integer

    'Remove any spaces from the VAT Nr.
    strVatNr = Trim(strVatNr)

    'Initialize variables
    IntCnt = 1
    intProduct = 10
    intSum = 0
    intCheckDigit = Right(strVatNr, 1)

    'Remove the leading country code when required
    If Left(strVatNr, 2) = "DE" Then
        strVatNr = Mid(strVatNr, 3, 8)
        Debug.Print strVatNr
    End If

    'Loop through each digit of the VAT Nr, excluding the Check Digit
    While IntCnt < 9
        intSum = (Mid(strVatNr, IntCnt, 1) + intProduct) Mod 10
        
        'If sum = 0 then apply 10 for sum
        If intSum = 0 Then intSum = 10
        intProduct = (2 * intSum) Mod 11
        IntCnt = IntCnt + 1
    Wend

    'The check digit arises as a result of 11 - product
    intResult = 11 - intProduct

    'If the difference is 10, apply 0 for result
    If intResult = 10 Then intResult = 0

    'Check if the result matches the digit
    If intResult = intCheckDigit Then
        CalcGermanVAT = True
    End If

End Function