Custom Modulo 11 check digit script
Hello,
So far in my company we used standard checkdigit function which are availble in bartender, but one customer requare specific check digit calculation.
Problem is that nobody before made any VB scripting.
I prepared VBA script in Excel with hope that it will be usable with small modyfication, but so far I didnt had any luck.
VBA script:
'collecting the data from excel sheet
mProd = Range("B11") 'product number
mColor = Range("C10") ' color variant
msupplier = Range("B12") ' supplier number
myy = Range("B9") ' production year formt YY
mday = Range("B7") ' production day format DDD
mserialN = Range("B10") ' serial counting number
'color variant code contain letter X or Z so it must be translated to number X=7,Z=9
mColor = Replace(mColor, "x", "7")
mColor = Replace(mColor, "z", "9")
'I created one string to gather all information in it (hoping to start from this in Bartender VB)
mkod = mProd & mColor & msupplier & myy & mday & mserialN
'each number should be multiplied by weight from 2 to 9 starting from first character on the right.
mp1 = Left(mkod, 1) * 2 '01 -10 Part number
mp2 = Mid(mkod, 2, 1) * 9
mp3 = Mid(mkod, 3, 1) * 8
mp4 = Mid(mkod, 4, 1) * 7
mp5 = Mid(mkod, 5, 1) * 6
mp6 = Mid(mkod, 6, 1) * 5
mp7 = Mid(mkod, 7, 1) * 4
mp8 = Mid(mkod, 8, 1) * 3
mp9 = Mid(mkod, 9, 1) * 2
mp10 = Mid(mkod, 10, 1) * 9
me1 = Mid(mkod, 11, 1) * 8 '11 -14 Extension key
me2 = Mid(mkod, 12, 1) * 7
me3 = Mid(mkod, 13, 1) * 6
me4 = Mid(mkod, 14, 1) * 5
ms1 = Mid(mkod, 15, 1) * 4 '15-16 Supplier ID
ms2 = Mid(mkod, 16, 1) * 3
my1 = Mid(mkod, 17, 1) * 2 '17-18production year "YY"
my2 = Mid(mkod, 18, 1) * 9
md1 = Mid(mkod, 19, 1) * 8 '19-21 Day of production "DDD"
md2 = Mid(mkod, 20, 1) * 7
md3 = Mid(mkod, 21, 1) * 6
mn1 = Mid(mkod, 22, 1) * 5 '22-25 Counting number
mn2 = Mid(mkod, 23, 1) * 4
mn3 = Mid(mkod, 24, 1) * 3
mn4 = Mid(mkod, 25, 1) * 2
'character 26 Check digit aac. Modulo 11
'sum of all characters multiplied by weight
sum = mp1 + mp2 + mp3 + mp4 + mp5 + mp6 + mp7 + mp8 + mp9 + mp10 + me1 + me2 + me3 + me4 + ms1 + ms2 + my1 + my2 + md1 + md2 + md3 + mn1 + mn2 + mn3 + mn4
'check digit calculation
checkdigit = sum Mod 11
this code worked in Excel but I cannot make it run in Bartender
I tried to use some code blocks from previous threads on this page but still I cannot finish script compilation.
I ended up with this:
'QRText object contains data: 23677006001X77301223390702
Value = Format.NamedSubStrings("QRText").Value
Value = Replace(Value, "X", "7")
Value = Replace(Value, "X", "9")
Value1 = Mid(Value, 1, 1)
Value2 = Mid(Value, 2, 1)
Value3 = Mid(Value, 3, 1)
Value4 = Mid(Value, 4, 1)
Value5 = Mid(Value, 5, 1)
Value6 = Mid(Value, 6, 1)
Value7 = Mid(Value, 7, 1)
Value8 = Mid(Value, 8, 1)
Value9 = Mid(Value, 9, 1)
Value10 = Mid(Value, 10, 1)
Value11 = Mid(Value, 11, 1)
Value12 = Mid(Value, 12, 1)
Value13 = Mid(Value, 13, 1)
Value14 = Mid(Value, 14, 1)
Value15 = Mid(Value, 15, 1)
Value16 = Mid(Value, 16, 1)
Value17 = Mid(Value, 17, 1)
Value18 = Mid(Value, 18, 1)
Value19 = Mid(Value, 19, 1)
Value20 = Mid(Value, 20, 1)
Value21 = Mid(Value, 21, 1)
Value22 = Mid(Value, 22, 1)
Value23 = Mid(Value, 23, 1)
Value24 = Mid(Value, 24, 1)
Value1 = Value1 * 2
Value2 = Value2 * 9
Value3 = Value3 * 8
Value4 = Value4 * 7
Value5 = Value5 * 6
Value6 = Value6 * 5
Value7 = Value7 * 4
Value8 = Value8 * 3
Value9 = Value9 * 2
Value10 = Value10 * 9
Value11 = Value11 * 8
Value12 = Value12 * 7
Value13 = Value13 * 6
Value14 = Value14 * 5
Value15 = Value15 * 4
Value16 = Value16 * 3
Value17 = Value17 * 2
Value18 = Value18 * 9
Value19 = Value19 * 8
Value20 = Value20 * 7
Value21 = Value21 * 6
Value22 = Value22 * 5
Value23 = Value23 * 4
Value24 = Value24 * 3
Value25 = Value25 * 2
Value = Value1 + Value2 + Value3 + Value4 + Value5 + Value6 + Value7 + Value8 + Value9 + Value10 + Value11 + Value12 + Value13 + Value14 + Value15 + Value16 + Value17 + Value18 + Value19 + Value20 + Value21 + Value22 + Value23 + Value24 + Value25
Value = Value Mod 11
I dont know if problem is with my script, or maybe I placed it in wrong place of Bartender software...
Is there a way to make this script work in Bartender or Is there posibility to use data string from my excel file in Bartender?
Any help is appreciated.
-
Other than adjusting the second replace command to Z and not X and linking to the value as a Named field your code worked for me and gave a result of 2
Is the data in your QR code from variable sources and so consists of multiple substrings? In which case you may need to add a extra field that is an Object Value and linked to the Human Readable or else reference and concatenate the substrings in your VB first before calculating the check digit
1 -
Hi, thanks for comment,
Problem was with proper connection of Object Value with my script. Knowing that code works and problem is somwhere else helped me a lot.
Big thanks.
Final version of code:
Value = Format.Objects("Modulo11").Value
Value = Replace(Value, "X", "7")
Value = Replace(Value, "Z", "9")Value1 = Mid(Value, 1, 1)
Value2 = Mid(Value, 2, 1)
Value3 = Mid(Value, 3, 1)
Value4 = Mid(Value, 4, 1)
Value5 = Mid(Value, 5, 1)
Value6 = Mid(Value, 6, 1)
Value7 = Mid(Value, 7, 1)
Value8 = Mid(Value, 8, 1)
Value9 = Mid(Value, 9, 1)
Value10 = Mid(Value, 10, 1)
Value11 = Mid(Value, 11, 1)
Value12 = Mid(Value, 12, 1)
Value13 = Mid(Value, 13, 1)
Value14 = Mid(Value, 14, 1)
Value15 = Mid(Value, 15, 1)
Value16 = Mid(Value, 16, 1)
Value17 = Mid(Value, 17, 1)
Value18 = Mid(Value, 18, 1)
Value19 = Mid(Value, 19, 1)
Value20 = Mid(Value, 20, 1)
Value21 = Mid(Value, 21, 1)
Value22 = Mid(Value, 22, 1)
Value23 = Mid(Value, 23, 1)
Value24 = Mid(Value, 24, 1)Value25 = Mid(Value, 25, 1)
Value1 = Value1 * 2
Value2 = Value2 * 9
Value3 = Value3 * 8
Value4 = Value4 * 7
Value5 = Value5 * 6
Value6 = Value6 * 5
Value7 = Value7 * 4
Value8 = Value8 * 3
Value9 = Value9 * 2
Value10 = Value10 * 9
Value11 = Value11 * 8
Value12 = Value12 * 7
Value13 = Value13 * 6
Value14 = Value14 * 5
Value15 = Value15 * 4
Value16 = Value16 * 3
Value17 = Value17 * 2
Value18 = Value18 * 9
Value19 = Value19 * 8
Value20 = Value20 * 7
Value21 = Value21 * 6
Value22 = Value22 * 5
Value23 = Value23 * 4
Value24 = Value24 * 3
Value25 = Value25 * 2Value = Value1 + Value2 + Value3 + Value4 + Value5 + Value6 + Value7 + Value8 + Value9 + Value10 + Value11 + Value12 + Value13 + Value14 + Value15 + Value16 + Value17 + Value18 + Value19 + Value20 + Value21 + Value22 + Value23 + Value24 + Value25
Value = Value Mod 11If Value = 10 then Value = "X"
0
Please sign in to leave a comment.
Comments
2 comments