Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# how do I find macro Excel formula to generate numbers per Luhn formula

Posted on 2011-09-08
Medium Priority
3,369 Views
Hi, I'm looking for a macro in Excel to generate numbers per the luhn algorithm
0
Question by:Seidmich

LVL 50

Assisted Solution

barry houdini earned 248 total points
ID: 36507267
Not a macro.......buhis formula in B1 will give the required check digit given a number of any length in A1

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(MOD(ROW(INDIRECT("1:"&LEN(A1)))+LEN(A1)+1,2)+1),"00"),{1,2},1)),10)

If your numbers have a fixed specific length then that all-purpose formula could probably be shortened to suit your exact requirements

regards, barry
0

LVL 19

Accepted Solution

Arno Koster earned 252 total points
ID: 36509214
For small numbers you could use
``````Sub generate_luhn_numbers()

Dim database()
Dim luhn_format As String

'-- initialise
luhn_length = 4
ReDim database(10 ^ (luhn_length), luhn_length)
luhn_format = ""
For pos = 1 To luhn_length
luhn_format = luhn_format & "0"
Next pos

For candidate = 0 To 10 ^ (luhn_length) - 1
Application.StatusBar = "Processing : " & Int(100 * candidate / (10 ^ luhn_length))

'-- fill with all possible numbers
rest = 0
For digit = 0 To luhn_length - 1
database(candidate, digit) = Val(Mid(Format(candidate, luhn_format), digit + 1, 1))
Next digit

'-- calculate
luhn_value = 0
For digit = 0 To luhn_length - 1
If isOdd(digit) Then
luhn_value = luhn_value + 2 * database(candidate, digit)
Else
luhn_value = luhn_value + database(candidate, digit)
End If
Next digit
database(candidate, luhn_length) = Val(Right(10 - luhn_value Mod 10, 1))

'-- export to excel
Cells(candidate + 1, 1) = 0
For digit = 0 To luhn_length
Cells(candidate + 1, 1) = Cells(candidate + 1, 1) + database(candidate, digit) * 10 ^ (luhn_length - digit)
Next digit

Next candidate

End Sub

Function isOdd(value) As Boolean
isOdd = ((value Mod 2) = 1)
End Function
``````
0

LVL 50

Expert Comment

ID: 37087210
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

## Featured Post

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month12 days, 5 hours left to enroll