Solved

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

Posted on 2005-04-08
Medium Priority
4,083 Views
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.

0
Question by:luigicamporesi
• 2

LVL 9

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, _
SearchOrder:=xlByRows).Row
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))
Else
tmpTotal = tmpTotal + CInt(tmpStr)
End If
tmpStr = ""
f = 0
Else
tmpTotal = tmpTotal + CInt(Mid\$(sNumber, i, 1))
End If
Next i
If Right\$(CStr(tmpTotal), 1) = "0" Then
tmpTotal = 0
Else
tmpTotal = ((tmpTotal + 10) - CInt(Right\$(CStr(tmpTotal), _
1))) - tmpTotal
End If
calcModulus10 = tmpTotal
End Function
0

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!!!!!
0

LVL 9

Expert Comment

ID: 13737187
You are most welcome!
0

Featured Post

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