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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Dana DeLouis (delouis_at_bellsouth.net)
Date: 02/17/05 

Date: Wed, 16 Feb 2005 20:23:23 -0500

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 

-- 
Dana DeLouis
Win XP & Office 2003
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:%23%23dO4NIFFHA.2876@TK2MSFTNGP12.phx.gbl...
> 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)
>
>
> "Stephanie" <Stephanie@discussions.microsoft.com> wrote in message
> news:C6EE37AF-5A1D-4D87-9ACF-38F7AC057208@microsoft.com...
>> 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?
>
>

Relevant Pages

  • Re: How do I do a MOD-10 Credit Card check in Excel?
    ... and if I recall now (now that you have prompted me ... the only mod checking I ever did multiplied each digit by a weighting ... but it looks like a credit card check multiplies ... > Dim Tot As Long ...
    (microsoft.public.excel.worksheet.functions)
  • Re: What does this Code Actually do Please
    ... Dim LastDigit As Integer ... Dim nDupl As Integer ... ' Formula finds the last digit without needing to loop; ...
    (microsoft.public.excel.programming)
  • Re: Converting ISBNs: 13 to 10
    ... Dim CheckDigit As Long ... Dim CheckDigit As String ... Rick ... Also does this put the 10 digit output in column B. I am not ...
    (microsoft.public.excel.programming)
  • Problem with Function to Convert Number To Words
    ... It has following problem displaying for Unit and Tens Only for eg. ... Dim Rupees, Paisa As String ... Dim Hundred, Words As String ... ' Do we have a Hundred place digit to convert? ...
    (microsoft.public.excel)
  • Re: Selecting Only One Record In A Merge
    ... Plus a 5 digit number is easier to insert than a 10 digit telephone number ... Michael Koerner ... Dim bDone As Boolean ... Dim iLetter As Integer ...
    (microsoft.public.word.mailmerge.fields)