How do I do a MOD-10 Credit Card check in Excel?

[Stephanie]

Does anyone know how to do a MOD-10 credit card number check in Excel 2000?
Is there a way to do it using a macro and Visual Basic?

 

[Bob Phillips]

Here is a simple function


Function ccCheck(rng) As Boolean
Dim i As Long
Dim tmp

With rng
For i = 1 To Len(.Value) - 1
tmp = tmp + Val(Mid(.Value, i, 1))
Next i
ccCheck = tmp Mod 10 = Val(Right(.Value, 1))
End With

End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dana DeLouis]

Hello. I might be wrong, but it looks like a credit card check multiplies
each digit by an alternating series of 1's or 2's. You then Sum the
individual digits. If the Total divides by 10, the number is valid. If
this is correct, here is one idea. If you wanted, you can check for leading
characters that are valid for MasterCard, Visa, etc. I used a string
variable since entering a 16 digit number would have to be done as a string.
My credit card showed "Valid" as a check.
I'm not an expert here, so hopefully some ideas here will help.

Function CC_Check(n As String) As Boolean
'// Dana DeLouis
Dim P As Long 'Pointer
Dim Tot As Long
Dim digit As Long
Dim s As Long

Select Case Len(n)
Case 13 To 16
'Valid Length
'// Make Even Length
If Len(n) Mod 2 = 1 Then n = "0" & n

For P = Len(n) To 1 Step -1
'// = = = = = = = = =
'Option #1
'// = = = = = = = = =
' Get Character
s = Mid$(n, P, 1)
' Multiply by 1 or 2
digit = s * (1 + (P Mod 2))
' Sum the individual digits (ie 12 = 1+2=3)
Tot = Tot + ((digit - 1) Mod 9) + 1

'Option #2 -> Combines the 3 lines above...
'// = = = = = = = = =
' Tot = Tot + ((Mid$(n, P, 1) * (1 + (P Mod 2)) - 1) Mod 9) + 1
'// = = = = = = = = =

Next P
CC_Check = (Tot Mod 10) = 0

Case Else
'Invalid
CC_Check = "Invalid Length"
End Select

End Function

 

[Bob Phillips]

Hi Dana,

I am no expert either, and if I recall now (now that you have prompted me
;-)), the only mod checking I ever did multiplied each digit by a weighting
(I don't recall it being as straight-forward as 1 or 2, but my memory may be
failing).

I hope the OP knows the algorithm at the very least, my intent was to show
him how to do a mod check. I think (hope) that with that, and your helpful
advice, he should be on the road.

Regards

Bob