An Engineer in Atlanta

Credit Card Number Validation in VBA

August 23, 2012

In my last post, I mentioned that I created a way to validate that a credit card number was valid. I use valid here in the sense that it conforms to actual numbers issued by the credit card companies. It does not actually check to see if the card has been issued to someone. The majority of this was adapted from here where Ady Wicaksono explains in detail how it works, and a javascript implementation of it. The below code was my adaptation of that logic to VBA to validate a card number entered into an Excel dialog box.

Function isValidCard(inCC)
   Dim intStartDb As Integer
   Dim intStartNon As Integer

   ' remove any non-numeric characters, such as dashes

   If Mid(inCC, 1, 1) Like "[0-2,7-8]" Then GoTo ValidCardFalse

   For i = 1 To Len(inCC)
     If Mid(inCC, i, 1) Like "[0-9]" Then inCCClean = inCCClean & Mid(inCC, i, 1)
   Next

   If Len(inCCClean) < 11 Then
     GoTo ValidCardFalse
   End If

   intDouble = 0

   If CInt(Len(inCCClean) / 2) = Len(inCCClean) / 2 Then
     intStartDb = 1
     intStartNon = 2
   Else
     intStartDb = 2
     intStartNon = 1
   End If

   i = 0
   For i = intStartDb To Len(inCCClean) Step 2
     intCheck = CInt(Mid(inCCClean, i, 1)) * 2
     If intCheck > 9 Then intCheck = intCheck - 9
     intDouble = intDouble + intCheck
   Next

   i = 0
   intNonDoub = 0
   For i = intStartNon To Len(inCCClean) Step 2
     intNonDoub = intNonDoub + CInt(Mid(inCCClean, i, 1))
   Next

   intAddCheck = intDouble + intNonDoub

   If (intAddCheck / 10) = CInt(intAddCheck / 10) Then
     isValidCard = inCCClean
   Else
     GoTo ValidCardFalse
   End If

 If isValidCard <> "" Then GoTo ValidCardTrue

 ValidCardFalse:
 isValidCard = ""
 Exit Function

 ValidCardTrue:
 Exit Function

 End Function

Dan Smith

Written by Dan Smith who lives and works in Atlanta doing random stuff. You should follow him on Twitter