Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: calculating checksums
I need to be able to calculate checksums (CRC32 algorighm) in Excel VBA
using Excel 2003. I have encrypted passwords and security groups from another application, and they will have to login to an Excel form. I cannot export the encrypted info into Excel since I have no way to translate it back to its original value. Instead, I thought if I first decrypted the data and replaced it with checksums, the password wouldn't be brought into Excel, which would add another measure of security for the application, since even if an enterprising user went hunting and found both userlist files, the password and security group would be different in each file, and there would be no obvious key that would hint at how they are related. I cannot, however, find any code that shows how to calculate checksums. I'd be very grateful if someone could point me in the right direction. Thanks! Henry DPM Mellon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: calculating checksums
Hello Henry, Here is the code for Luhn CRC. I know it isn't the 32 bit CRC alogrithm. If you lnow the math for the 32 bit CRC then you can use the Luhn CRC algorithm as a VBA model to construct the 32 bit CRC. Hope this helps some. Code: -------------------- 'This is the Luhn Formula as described in ISO/IEC 7812-1:1993. 'Its primary purpose is to ensure accurate entries of the credit card number 'during transactions. You can apply the same technique to other applications 'such as employee numbers or patient numbers. Using check digits for these 'numbers also ensures more accurate data entries. Public Function CheckDigit(strNum As String) As Integer Dim i As Integer Dim iEven As Integer Dim iOdd As Integer Dim iTotal As Integer Dim strOneChar As String Dim iTemp As Integer ' Add digits in even ordinal positions ' starting from rightmost For i = Len(strNum) - 1 To 2 Step -2 strOneChar = Mid$(strNum, i, 1) If IsNumeric(strOneChar) Then iEven = iEven + CInt(strOneChar) End If Next i ' Process digits in odd ordinal positions ' starting from rightmost For i = Len(strNum) To 1 Step -2 strOneChar = Mid$(strNum, i, 1) If IsNumeric(strOneChar) Then ' Double it iTemp = CInt(strOneChar) * 2 If iTemp 9 Then ' Break the digits (e.g., 19 becomes 1+9) iOdd = iOdd + (iTemp \ 10) + (iTemp - 10) Else iOdd = iOdd + iTemp End If End If Next i ' Add even and odd iTotal = iEven + iOdd ' Return the 10's complement CheckDigit = 10 - (iTotal Mod 10) End Function -------------------- Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=482390 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: calculating checksums
Thanks, Leith- that was fast.
Unfortunately, I don't know the math for the 32-bit version, and also unfortunately, I don't have the luxury of being able to do a lot of research, either. I've just had a project assigned to me that has a very short deadline, and I have to pull a lot of things together to enable different modules to draw info from each other. It now seems that I'll have to resort to another plan - one that I can easily implement in VBA that can be trusted to accurately decrypt VBA input so I know that it matches the similarly encrypted data, coming from FoxPro. Thanks again. I appreciate it. Henry Leith Ross wrote: Hello Henry, Here is the code for Luhn CRC. I know it isn't the 32 bit CRC alogrithm. If you lnow the math for the 32 bit CRC then you can use the Luhn CRC algorithm as a VBA model to construct the 32 bit CRC. Hope this helps some. Code: -------------------- 'This is the Luhn Formula as described in ISO/IEC 7812-1:1993. 'Its primary purpose is to ensure accurate entries of the credit card number 'during transactions. You can apply the same technique to other applications 'such as employee numbers or patient numbers. Using check digits for these 'numbers also ensures more accurate data entries. Public Function CheckDigit(strNum As String) As Integer Dim i As Integer Dim iEven As Integer Dim iOdd As Integer Dim iTotal As Integer Dim strOneChar As String Dim iTemp As Integer ' Add digits in even ordinal positions ' starting from rightmost For i = Len(strNum) - 1 To 2 Step -2 strOneChar = Mid$(strNum, i, 1) If IsNumeric(strOneChar) Then iEven = iEven + CInt(strOneChar) End If Next i ' Process digits in odd ordinal positions ' starting from rightmost For i = Len(strNum) To 1 Step -2 strOneChar = Mid$(strNum, i, 1) If IsNumeric(strOneChar) Then ' Double it iTemp = CInt(strOneChar) * 2 If iTemp 9 Then ' Break the digits (e.g., 19 becomes 1+9) iOdd = iOdd + (iTemp \ 10) + (iTemp - 10) Else iOdd = iOdd + iTemp End If End If Next i ' Add even and odd iTotal = iEven + iOdd ' Return the 10's complement CheckDigit = 10 - (iTotal Mod 10) End Function -------------------- Sincerely, Leith Ross |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: calculating checksums
Leith Ross Wrote: Hello Henry, Here is the code for Luhn CRC. I know it isn't the 32 bit CRC alogrithm. If you lnow the math for the 32 bit CRC then you can use the Luhn CRC algorithm as a VBA model to construct the 32 bit CRC. Hope this helps some. Code: -------------------- 'This is the Luhn Formula as described in ISO/IEC 7812-1:1993. 'Its primary purpose is to ensure accurate entries of the credit card number 'during transactions. You can apply the same technique to other applications 'such as employee numbers or patient numbers. Using check digits for these 'numbers also ensures more accurate data entries. Public Function CheckDigit(strNum As String) As Integer Dim i As Integer Dim iEven As Integer Dim iOdd As Integer Dim iTotal As Integer Dim strOneChar As String Dim iTemp As Integer ' Add digits in even ordinal positions ' starting from rightmost For i = Len(strNum) - 1 To 2 Step -2 strOneChar = Mid$(strNum, i, 1) If IsNumeric(strOneChar) Then iEven = iEven + CInt(strOneChar) End If Next i ' Process digits in odd ordinal positions ' starting from rightmost For i = Len(strNum) To 1 Step -2 strOneChar = Mid$(strNum, i, 1) If IsNumeric(strOneChar) Then ' Double it iTemp = CInt(strOneChar) * 2 If iTemp 9 Then ' Break the digits (e.g., 19 becomes 1+9) iOdd = iOdd + (iTemp \ 10) + (iTemp - 10) Else iOdd = iOdd + iTemp End If End If Next i ' Add even and odd iTotal = iEven + iOdd ' Return the 10's complement CheckDigit = 10 - (iTotal Mod 10) End Function -------------------- Sincerely, Leith Ross Hi Leith Ross I hope you are still around, i needed your Luhn digit checksum urgently but it did not calculate the way i wanted it to when i loaded it to Excel. I think there are a few ways of doing the sum. I found the formula that calculates the way i wanted although it looks to be the same as the one you done a whle ago: Luhn Check Digit Calculation Step 1 Double the values of the odd labelled digits of the IMEI. Step 2a Add together all the individual digits obtaind in step 1. Step 2b Add the sum obtained in Step 2a together with the sum of all even labelled digits of the IMEI Step 3 If the number obtained in Step 2b end with '0' then the Luhn Check Digit will be 0, otherwise the Luhn Check Digit will be the substraction of the result from Step 2b with the next higher number ending with a zero. Example Enter IMEI : D14 D13 D12 D11 D10 D09 D08 D07 D06 D05 D04 D03 D02 D01 4 9 0 1 5 4 2 0 3 2 3 7 5 1 Calculation Step 1: 18 2 8 0 4 14 2 Step 2a+b ( 1+8 + 2 + 8 + 0 + 4 + 1+4 + 2 ) + ( 4 + 0 + 5 + 2 + 3 + 3 + 5 ) = 52 Step 3: Luhn Digit : 8 IMEI : 490154-20-323751-8 Please help me as i am not very good with computer stuff. -- mrjames ------------------------------------------------------------------------ mrjames's Profile: http://www.excelforum.com/member.php...o&userid=35732 View this thread: http://www.excelforum.com/showthread...hreadid=482390 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: calculating checksums
Hello Mrjames, Here are 2 macros that should help you out. One will verify if a checksum sequence is correct, and the other will generate a checksum digit for a sequence, and if the sequence is valid, no checksum digit needed, it will return a -1. Using the number you provided we will get the following result: Cell A1 formula: =IsLuhnChecksumOK("49015420323751") Returns TRUE. This sum of the digits equals 50, not 52. How the Luhn algorithm functions: 1) Starting with the second to last digit, multiply every other digit by 2. 2) If the product is greater than 10 then subtract 9 from the product. This produces the same result as adding the digits of number greater than 9. Sum these values. 3) Sum all the unmultiplied digits and add the sum from step 2. The result is valid if it is an even mutliple of 10. 4 9 0 1 5 4 2 0 3 2 3 7 5 1 Step 1 - double the digits 5(2) 3(2) 3(2) 2(2) 5(2) 0(2) 4(2) Step 2 - sum and adjust the doubled digits (10 - 9) + 6 + 6 + 4 + (10 - 9) + 0 + 8 = 26 Step 3 - sum the digits not doubled and the doubled digits. Test is total is evenly divided by 10 1 + 7 + 2 + 0 + 4 + 1 + 9 = 24 24 + 26 = 50 10 Mod 50 = 0 Code: -------------------- Public Function IsLuhnChecksumOK(Number_String As String) As Boolean Dim Digit As Integer Dim i As Integer Dim N As Integer Dim Result As Integer Dim SumDigits As Integer Dim nDigits As Integer SumDigits = 0 nDigits = Len(Number_String) For i = nDigits To 1 Step -1 Digit = CInt(Mid(Number_String, i, 1)) N = N + 1 If N Mod 2 = 0 Then Digit = Digit * 2 If Digit 9 Then Digit = Digit - 9 SumDigits = SumDigits + Digit Next i Result = SumDigits Mod 10 If Result = 0 Then IsLuhnChecksumOK = True End If End Function Public Function GetLuhnCheckDigit(Number_To_Check As String) Dim J As Integer Dim X X = IsLuhnChecksumOK(Number_To_Check) If X = False Then For J = 0 To 9 X = IsLuhnChecksumOK(Number_To_Check & J) If X = True Then 'Check digit found GetLuhnCheckDigit = J Exit Function End If Next J Else 'No check digit needed returns -1 GetLuhnCheckDigit = -1 End If End Function -------------------- If you have any further questions, you can emial me at . Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=482390 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating In Excel | Excel Discussion (Misc queries) | |||
Excel not Calculating | Excel Discussion (Misc queries) | |||
calculating age in excel | New Users to Excel | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
CALCULATING WORKSHEETS (INCLUDING AGE CALCULATING SHEETS) FOR DOWNLOADING, GREAT FOR PENSIONS/LIFE INSURANCE CALCULATIONS! | Excel Programming |