Credit Card Numbers - Validation / Checksum

[yytsunamiyy]

I have a userform capturing Credit Card Numbers for Visa, Master, Amex and Diners as part of a payment tracking system and would like to check that the entered Card Number is valid before processing the payment through a third-party website to minimize to number of error / invalid card messages.

I suspect there is some kind of checksum built into the cardnr. Any tips on how to check for the validity would be appreciated, including links to relevant sites.

this question is related to an earlier question of mine: http://www.mrexcel.com/forum/showthread.php?t=309862

thank you all

Stephan

 

[Norie]

Stephan

This isn't really an Excel issue.

Sure if there is a checksum of some sort you could use Excel/VBA to calculate it.

But the most important thing is, what is this checksum and what's the logic/algorithim to calculate it?

 

[mvptomlinson]

The checksum used by almost every major credit card company and bank is the Luhn algorithm. Googling it will provide you with the proper method to validate cards (and may even provide you with some scripts, although maybe not vba..)

 

[yytsunamiyy]

Thank you mvptomlinson. Thanks to your tip I found a great source:

http://www.beachnet.com/~hstiles/cardtype.html

 

[mvptomlinson]

yyt, please note that the Excel file offered for download on that site does not calculate correctly because the card number entry field is formatted as numeric. Numeric fields in Excel cannot store numbers with greater than 15 digits, so the 16th digit in any credit card you enter will change to a 0 and almost certainly fail the test.

It can be altered to work properly, or you can take a look at the file I created to do validation: http://www.mediafire.com/?ygyyn1cynlm

 

[yytsunamiyy]

Thank you mvp, I had noted that there is a problem but did not see where - I will test my code again against your program (and some of my own cards ) and will then post it here.

 

[Colby]

Don't let your bank / merchant account provider know that you are using VBA / Excel / Access to validate / store credit cards. It doesn't sound PCI compliant at all, and they could revoke your merchant account because of it.

I would recommend you evaluate what level merchant you are and read up on PCI or CISP.

 

[yytsunamiyy]

Hi Colby,

I'm sitting in an southamerican country in a small travelagency. Believe me - storing the numbers and info password protected in excel is a big step forward in data security here. So far all the information is on paper and goes unshreddered into the bin on the street after payment has been received... - horrifying but true.
The actual processing of the cards happens through a 3rd party secure certified website. The agency itself is not a direct merchant. I have written the data retrieval sub in the way that the CC-info is deleted after payment has been confirmed - after that only the transaction Nr of the 3rd party site remains in excel - sufficient for all tracking purposes.

anyway - as promised earlier I am just putting the validation code here - I'm sure it could be much simplified but quite frankly - it is working, and there are more battles for me to fight than to have one super flexile routine that handles all possible cardtypes with the different stringlenghts (Visa 13- 16, Master 16, AmEx 15, Diners 14) in one tight code.

If Me.ComboBoxCardType.Text = "MA" And wert Like "5*" Then
    If Len(wert) = 19 Then
        Me.TextBoxCardNr2.Value = VBA.Left(wert, 4) & VBA.Mid(wert, 6, 4) & VBA.Mid(wert, 11, 4) & VBA.Right(wert, 4)
        '-------read digits in Cardnumber
        CheckNr(1) = VBA.Left(Me.TextBoxCardNr2.Value, 1)
        For i = 2 To 15
            CheckNr(i) = VBA.Mid(Me.TextBoxCardNr2.Value, i, 1)
        Next i
        CheckNr(16) = VBA.Right(Me.TextBoxCardNr2.Value, 1)
        
        '----double alternate digits starting with right - 1
        For j = 15 To 1 Step -2
            CheckNr(j) = CheckNr(j) * 2
            If CheckNr(j) > 8 Then ' if doubled is 2-digit number calculate Sum of digits
                CheckString = CheckNr(j)
                CheckPart(1) = VBA.Left(CheckString, 1)
                CheckPart(2) = VBA.Right(CheckString, 1)
                CheckNr(j) = CheckPart(1) + CheckPart(2)
            End If
        Next j
        
        '------add all CheckNr's to make CheckSum
        For k = 1 To 16
            CheckSum = CheckSum + CheckNr(k)
        Next k
        
        '------check that Checksum / 10 does not leave remainder - if so CardNr is invalid
        If Not CheckSum Mod 10 = 0 Then
            GoTo ErrorMsgCardInvalid
        Else
            Me.TextBoxCardNr2.SelStart = 0
            Me.TextBoxCardNr2.SelLength = 16
            TextBoxCardNr2.Copy
            Me.TextBoxCardSecCode.SetFocus
            Exit Sub
        End If
    Else
        GoTo ErrorMsgCardInvalid
    End If
end if

when reading the code please keep in mind that I have some dashes in the cardnr. typed by the user which I remove with

Me.TextBoxCardNr2.Value = VBA.Left(wert, 4) & VBA.Mid(wert, 6, 4) & VBA.Mid(wert, 11, 4) & VBA.Right(wert, 4)

Therefore Me.TextBoxCardNr2.Value is the straight CC Nr without any spaces, dashes or such things, e.g. 4321098765432109.

 

[Norie]

To replace the dashes use the Replace function.

Me.TextBoxCardNr2.Value = Replace(wert, "-", "")

 

[Colby]

yytsunamiyy - Good to hear you are taking steps towards a more secure operation. I only raised the issue because I used to work at a credit card gateway, and we ran in to several developers who were not aware of PCI / CISP regulations.

Down in South America, I think you would be hard pressed to get in any trouble for what your doing.

BTW, your Luhn algorithm looks to do the trick just fine.

-Colby