Macro for Excel calculating the checkdigit of a number using Luhn's algorithm

Posted on 2005-04-08
Medium Priority
Last Modified: 2012-08-13
I am searching a Macro for Excel to calculate the checkdigit of a series of 20K numbers. The numbers are like this: 35140513000637 and the checkdigit is to be calculated using the Luhn's algorithm. The result for the sample is 351405130006378. The Macro of course implements the Luhn's algorithm.

Question by:luigicamporesi
  • 2

Accepted Solution

dmang earned 500 total points
ID: 13736448

try the following

on the worksheet ..
column a has the original number
column b will hold the result

mod10 sub is the driver
lastrow is a function that returns the last row on the worksheet
calcModulus10 returns the check digit

Sub Mod10()

    Dim strCD As String
    For r = 1 To LastRow(ActiveSheet)
        With ActiveSheet
            strCD = calcModulus10(ActiveSheet.Cells(r, 1))
            ActiveSheet.Cells(r, 2).NumberFormat = "@"
            ActiveSheet.Cells(r, 2) = ActiveSheet.Cells(r, 1) & strCD
        End With
    Next r
End Sub
Function LastRow(ws As Worksheet) As Single

    'uses worksheet object
    'returns last used row
    On Error Resume Next
    With ws
      LastRow = .Cells.Find(What:="*", _
        SearchDirection:=xlPrevious, _
    End With
End Function

Function calcModulus10(ByVal sNumber As String) As Integer
    Dim tmpTotal As Integer
    Dim i As Integer, f As Byte, tmpStr As String
    Dim mdNums() As Integer
    For i = 1 To Len(sNumber)
      f = f + 1
      If f = 2 Then
        tmpStr = CInt(Mid$(sNumber, i, 1)) * 2
        If Len(tmpStr) > 1 Then
          tmpTotal = tmpTotal + CInt(Mid$(tmpStr, 1, 1)) + CInt(Mid$(tmpStr, 2, 1))
          tmpTotal = tmpTotal + CInt(tmpStr)
        End If
        tmpStr = ""
        f = 0
        tmpTotal = tmpTotal + CInt(Mid$(sNumber, i, 1))
      End If
    Next i
    If Right$(CStr(tmpTotal), 1) = "0" Then
      tmpTotal = 0
      tmpTotal = ((tmpTotal + 10) - CInt(Right$(CStr(tmpTotal), _
    1))) - tmpTotal
    End If
    calcModulus10 = tmpTotal
End Function

Author Comment

ID: 13736778
Dear dmanq, thank you very mch for your help. I am really ignorant in this field, and you have saved me at least 20 days of hard work. Thanks again!!!!!

Expert Comment

ID: 13737187
You are most welcome!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month16 days, 9 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question