Здравствуйте! Нужно создать штрих-код EAN-8 с помощью формулы. Есть шрифт EAN-13 и два разных кода которые преобразуют цифры в штрих-код и выдают одинаковый результат, нужна помощь в написании формулы для штрих-кода EAN-8. Помогите, не могу разобраться!
Здравствуйте! Нужно создать штрих-код EAN-8 с помощью формулы. Есть шрифт EAN-13 и два разных кода которые преобразуют цифры в штрих-код и выдают одинаковый результат, нужна помощь в написании формулы для штрих-кода EAN-8. Помогите, не могу разобраться!
DrMini, макрос работает, но он создает много графических линий, что не очень удобно во время редактирования таблицы в дальнейшем. Есть такой макрос, но не могу найти аналогичный для EAN-8:
[vba]
Код
Public Function ean13$(chaine$) 'V 1.0 'Parameters: a 12-digit string 'Return: * a chain which, displayed with the EAN13.TTF font, gives the barcode ' * an empty string if parameter supplied incorrect Dim i%, checksum%, first%, CodeBarre$, tableA As Boolean ean13$ = "" 'Check that there are 12 characters If Len(chaine$) = 12 Then 'And that these are many figures For i% = 1 To 12 If Asc(Mid$(chaine$, i%, 1)) < 48 Or Asc(Mid$(chaine$, i%, 1)) > 57 Then i% = 0 Exit For End If Next If i% = 13 Then 'Calculation of the control key For i% = 2 To 12 Step 2 checksum% = checksum% + Val(Mid$(chaine$, i%, 1)) Next checksum% = checksum% * 3 For i% = 1 To 11 Step 2 checksum% = checksum% + Val(Mid$(chaine$, i%, 1)) Next chaine$ = chaine$ & (10 - checksum% Mod 10) Mod 10 'The first number is taken as is, the second comes from table A CodeBarre$ = Left$(chaine$, 1) & Chr$(65 + Val(Mid$(chaine$, 2, 1))) first% = Val(Left$(chaine$, 1)) For i% = 3 To 7 tableA = False Select Case i% Case 3 Select Case first% Case 0 To 3 tableA = True End Select Case 4 Select Case first% Case 0, 4, 7, 8 tableA = True End Select Case 5 Select Case first% Case 0, 1, 4, 5, 9 tableA = True End Select Case 6 Select Case first% Case 0, 2, 5, 6, 7 tableA = True End Select Case 7 Select Case first% Case 0, 3, 6, 8, 9 tableA = True End Select End Select If tableA Then CodeBarre$ = CodeBarre$ & Chr$(65 + Val(Mid$(chaine$, i%, 1))) Else CodeBarre$ = CodeBarre$ & Chr$(75 + Val(Mid$(chaine$, i%, 1))) End If Next CodeBarre$ = CodeBarre$ & "*" 'Adding central divider For i% = 8 To 13 CodeBarre$ = CodeBarre$ & Chr$(97 + Val(Mid$(chaine$, i%, 1))) Next CodeBarre$ = CodeBarre$ & "+" 'Addition of end mark ean13$ = CodeBarre$ End If End If End Function
[/vba]
DrMini, макрос работает, но он создает много графических линий, что не очень удобно во время редактирования таблицы в дальнейшем. Есть такой макрос, но не могу найти аналогичный для EAN-8:
[vba]
Код
Public Function ean13$(chaine$) 'V 1.0 'Parameters: a 12-digit string 'Return: * a chain which, displayed with the EAN13.TTF font, gives the barcode ' * an empty string if parameter supplied incorrect Dim i%, checksum%, first%, CodeBarre$, tableA As Boolean ean13$ = "" 'Check that there are 12 characters If Len(chaine$) = 12 Then 'And that these are many figures For i% = 1 To 12 If Asc(Mid$(chaine$, i%, 1)) < 48 Or Asc(Mid$(chaine$, i%, 1)) > 57 Then i% = 0 Exit For End If Next If i% = 13 Then 'Calculation of the control key For i% = 2 To 12 Step 2 checksum% = checksum% + Val(Mid$(chaine$, i%, 1)) Next checksum% = checksum% * 3 For i% = 1 To 11 Step 2 checksum% = checksum% + Val(Mid$(chaine$, i%, 1)) Next chaine$ = chaine$ & (10 - checksum% Mod 10) Mod 10 'The first number is taken as is, the second comes from table A CodeBarre$ = Left$(chaine$, 1) & Chr$(65 + Val(Mid$(chaine$, 2, 1))) first% = Val(Left$(chaine$, 1)) For i% = 3 To 7 tableA = False Select Case i% Case 3 Select Case first% Case 0 To 3 tableA = True End Select Case 4 Select Case first% Case 0, 4, 7, 8 tableA = True End Select Case 5 Select Case first% Case 0, 1, 4, 5, 9 tableA = True End Select Case 6 Select Case first% Case 0, 2, 5, 6, 7 tableA = True End Select Case 7 Select Case first% Case 0, 3, 6, 8, 9 tableA = True End Select End Select If tableA Then CodeBarre$ = CodeBarre$ & Chr$(65 + Val(Mid$(chaine$, i%, 1))) Else CodeBarre$ = CodeBarre$ & Chr$(75 + Val(Mid$(chaine$, i%, 1))) End If Next CodeBarre$ = CodeBarre$ & "*" 'Adding central divider For i% = 8 To 13 CodeBarre$ = CodeBarre$ & Chr$(97 + Val(Mid$(chaine$, i%, 1))) Next CodeBarre$ = CodeBarre$ & "+" 'Addition of end mark ean13$ = CodeBarre$ End If End If End Function
Собрал вот такой файл, может кому пригодится, но в идеале хочу исключить макрос и оставить создание кода формулой. Попробую разобраться сам, по аналогии с EAN-13. Но если есть готовый буду рад!
[vba]
Код
Public Function EAN_8$(chaine$) 'V 1.0.0 'Parametres : une chaine de 7 chiffres 'Parameters : a 7 digits length string 'Retour : * une chaine qui, affichee avec la police EAN13.TTF, donne le code barre ' * une chaine vide si parametre fourni incorrect 'Return : * a string which give the bar code when it is dispayed with EAN13.TTF font ' * an empty string if the supplied parameter is no good Dim i%, checksum%, first%, CodeBarre$, tableA As Boolean EAN_8$ = "" 'Verifier qu'il y a 7 caracteres 'Check for 7 characters If Len(chaine$) = 7 Then 'Et que ce sont bien des chiffres 'And they are really digits For i% = 1 To 7 If Asc(Mid$(chaine$, i%, 1)) < 48 Or Asc(Mid$(chaine$, i%, 1)) > 57 Then i% = 0 Exit For End If Next If i% = 8 Then 'Calcul de la cle de controle 'Calculation of the checksum For i% = 7 To 1 Step -2 checksum% = checksum% + Val(Mid$(chaine$, i%, 1)) Next checksum% = checksum% * 3 For i% = 6 To 1 Step -2 checksum% = checksum% + Val(Mid$(chaine$, i%, 1)) Next chaine$ = chaine$ & (10 - checksum% Mod 10) Mod 10 'Les 4 premier chiffre viennent de la table A 'The first 4 digits come from table A CodeBarre$ = ":" 'Ajout marque de debut / Add start mark For i% = 1 To 4 CodeBarre$ = CodeBarre$ & Chr$(65 + Val(Mid$(chaine$, i%, 1))) Next CodeBarre$ = CodeBarre$ & "*" 'Ajout separateur central / Add middle separator For i% = 5 To 8 CodeBarre$ = CodeBarre$ & Chr$(97 + Val(Mid$(chaine$, i%, 1))) Next CodeBarre$ = CodeBarre$ & "+" 'Ajout de la marque de fin / Add end mark EAN_8$ = CodeBarre$ End If End If End Function Public Function EAN_13$(chaine$) 'V 1.0 'Parameters: a 12-digit string 'Return: * a chain which, displayed with the EAN13.TTF font, gives the barcode ' * an empty string if parameter supplied incorrect Dim i%, checksum%, first%, CodeBarre$, tableA As Boolean EAN_13$ = "" 'Check that there are 12 characters If Len(chaine$) = 12 Then 'And that these are many figures For i% = 1 To 12 If Asc(Mid$(chaine$, i%, 1)) < 48 Or Asc(Mid$(chaine$, i%, 1)) > 57 Then i% = 0 Exit For End If Next If i% = 13 Then 'Calculation of the control key For i% = 2 To 12 Step 2 checksum% = checksum% + Val(Mid$(chaine$, i%, 1)) Next checksum% = checksum% * 3 For i% = 1 To 11 Step 2 checksum% = checksum% + Val(Mid$(chaine$, i%, 1)) Next chaine$ = chaine$ & (10 - checksum% Mod 10) Mod 10 'The first number is taken as is, the second comes from table A CodeBarre$ = Left$(chaine$, 1) & Chr$(65 + Val(Mid$(chaine$, 2, 1))) first% = Val(Left$(chaine$, 1)) For i% = 3 To 7 tableA = False Select Case i% Case 3 Select Case first% Case 0 To 3 tableA = True End Select Case 4 Select Case first% Case 0, 4, 7, 8 tableA = True End Select Case 5 Select Case first% Case 0, 1, 4, 5, 9 tableA = True End Select Case 6 Select Case first% Case 0, 2, 5, 6, 7 tableA = True End Select Case 7 Select Case first% Case 0, 3, 6, 8, 9 tableA = True End Select End Select If tableA Then CodeBarre$ = CodeBarre$ & Chr$(65 + Val(Mid$(chaine$, i%, 1))) Else CodeBarre$ = CodeBarre$ & Chr$(75 + Val(Mid$(chaine$, i%, 1))) End If Next CodeBarre$ = CodeBarre$ & "*" 'Adding central divider For i% = 8 To 13 CodeBarre$ = CodeBarre$ & Chr$(97 + Val(Mid$(chaine$, i%, 1))) Next CodeBarre$ = CodeBarre$ & "+" 'Addition of end mark EAN_13$ = CodeBarre$ End If End If End Function
[/vba]
Собрал вот такой файл, может кому пригодится, но в идеале хочу исключить макрос и оставить создание кода формулой. Попробую разобраться сам, по аналогии с EAN-13. Но если есть готовый буду рад!
[vba]
Код
Public Function EAN_8$(chaine$) 'V 1.0.0 'Parametres : une chaine de 7 chiffres 'Parameters : a 7 digits length string 'Retour : * une chaine qui, affichee avec la police EAN13.TTF, donne le code barre ' * une chaine vide si parametre fourni incorrect 'Return : * a string which give the bar code when it is dispayed with EAN13.TTF font ' * an empty string if the supplied parameter is no good Dim i%, checksum%, first%, CodeBarre$, tableA As Boolean EAN_8$ = "" 'Verifier qu'il y a 7 caracteres 'Check for 7 characters If Len(chaine$) = 7 Then 'Et que ce sont bien des chiffres 'And they are really digits For i% = 1 To 7 If Asc(Mid$(chaine$, i%, 1)) < 48 Or Asc(Mid$(chaine$, i%, 1)) > 57 Then i% = 0 Exit For End If Next If i% = 8 Then 'Calcul de la cle de controle 'Calculation of the checksum For i% = 7 To 1 Step -2 checksum% = checksum% + Val(Mid$(chaine$, i%, 1)) Next checksum% = checksum% * 3 For i% = 6 To 1 Step -2 checksum% = checksum% + Val(Mid$(chaine$, i%, 1)) Next chaine$ = chaine$ & (10 - checksum% Mod 10) Mod 10 'Les 4 premier chiffre viennent de la table A 'The first 4 digits come from table A CodeBarre$ = ":" 'Ajout marque de debut / Add start mark For i% = 1 To 4 CodeBarre$ = CodeBarre$ & Chr$(65 + Val(Mid$(chaine$, i%, 1))) Next CodeBarre$ = CodeBarre$ & "*" 'Ajout separateur central / Add middle separator For i% = 5 To 8 CodeBarre$ = CodeBarre$ & Chr$(97 + Val(Mid$(chaine$, i%, 1))) Next CodeBarre$ = CodeBarre$ & "+" 'Ajout de la marque de fin / Add end mark EAN_8$ = CodeBarre$ End If End If End Function Public Function EAN_13$(chaine$) 'V 1.0 'Parameters: a 12-digit string 'Return: * a chain which, displayed with the EAN13.TTF font, gives the barcode ' * an empty string if parameter supplied incorrect Dim i%, checksum%, first%, CodeBarre$, tableA As Boolean EAN_13$ = "" 'Check that there are 12 characters If Len(chaine$) = 12 Then 'And that these are many figures For i% = 1 To 12 If Asc(Mid$(chaine$, i%, 1)) < 48 Or Asc(Mid$(chaine$, i%, 1)) > 57 Then i% = 0 Exit For End If Next If i% = 13 Then 'Calculation of the control key For i% = 2 To 12 Step 2 checksum% = checksum% + Val(Mid$(chaine$, i%, 1)) Next checksum% = checksum% * 3 For i% = 1 To 11 Step 2 checksum% = checksum% + Val(Mid$(chaine$, i%, 1)) Next chaine$ = chaine$ & (10 - checksum% Mod 10) Mod 10 'The first number is taken as is, the second comes from table A CodeBarre$ = Left$(chaine$, 1) & Chr$(65 + Val(Mid$(chaine$, 2, 1))) first% = Val(Left$(chaine$, 1)) For i% = 3 To 7 tableA = False Select Case i% Case 3 Select Case first% Case 0 To 3 tableA = True End Select Case 4 Select Case first% Case 0, 4, 7, 8 tableA = True End Select Case 5 Select Case first% Case 0, 1, 4, 5, 9 tableA = True End Select Case 6 Select Case first% Case 0, 2, 5, 6, 7 tableA = True End Select Case 7 Select Case first% Case 0, 3, 6, 8, 9 tableA = True End Select End Select If tableA Then CodeBarre$ = CodeBarre$ & Chr$(65 + Val(Mid$(chaine$, i%, 1))) Else CodeBarre$ = CodeBarre$ & Chr$(75 + Val(Mid$(chaine$, i%, 1))) End If Next CodeBarre$ = CodeBarre$ & "*" 'Adding central divider For i% = 8 To 13 CodeBarre$ = CodeBarre$ & Chr$(97 + Val(Mid$(chaine$, i%, 1))) Next CodeBarre$ = CodeBarre$ & "+" 'Addition of end mark EAN_13$ = CodeBarre$ End If End If End Function