Re: macro function ko, how to debug - Qlik Community

Anonymous · ‎2016-10-06

Hello,

i have this function how doesn't work :

Function ean13(chaine)  'V 1.0
  'Paramètres : une chaine de 12 chiffres
  'Retour : * une chaine qui, affichée avec la police EAN13.TTF, donne le code barre
  '         * une chaine vide si paramètre fourni incorrect
  MsgBox(chaine)
  Dim i, checksum, first, CodeBarre, tableA
  ean13 = ""
  'Vérifier qu il y a 12 caractères
  If Len(chaine) = 12 Then
    'Et que ce sont bien des chiffres
    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
      'Calcul de la clé de contrôle
      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
      'Le premier chiffre est pris tel quel, le deuxième vient de la 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, 1, 2, 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 & "*"   'Ajout séparateur central
      For i = 8 To 13
        CodeBarre = CodeBarre & Chr(97 + Val(Mid(chaine, i, 1)))
      Next
      CodeBarre = CodeBarre & "+"   'Ajout de la marque de fin
      ean13 = CodeBarre
    End If
  End If
End Function

Someone could help me to debug.

Message box doesn't work to ....

Sophie

sfatoux72 · ‎2016-10-06

Hi Sophie,

I copy your function in Qlikview and the msgbox works.well

This macro is in VBScript, did you select VBScript?

If JScript is selected this function will not work.

I don't understand why you test a size of 12 figures? EAN13 didn't have 13 figures?

And after you loop from 1 to 12 to test if all character are figures, you need to loop from 1 to 13 to check all and to be right with the next if "If i = 13 Then" otherwise this condition will never be true.

Regards

Anonymous · ‎2016-10-07

Hello,

thanks for your answer, after more test the good fonction is :

Function ean13(chaine13)  'V 1.0
'Function ean13  'V 1.0
  'Paramètres : une chaine de 12 chiffres
  'Retour : * une chaine qui, affichée avec la police EAN13.TTF, donne le code barre
  '         * une chaine vide si paramètre fourni incorrect
  Dim i, checksum, first, CodeBarre, tableA
'  chaine13=208800100645
'  ean13=chaine13  
  if len(chaine13) = 12 then
  chaine=chaine13
  else
  chaine=Mid(chaine13, 1, 12)  
  end if      
'  MsgBox(chaine)     
  ean13 = ""
  'Vérifier qu'il y a 12 caractères
  If Len(chaine) = 12 Then
    'Et que ce sont bien des chiffres
    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
      'Calcul de la clé de contrôle
      For i = 2 To 12 Step 2
        checksum = checksum + CInt(Mid(chaine, i, 1))
      Next
      checksum = checksum * 3
      For i = 1 To 11 Step 2
        checksum = checksum + CInt(Mid(chaine, i, 1))
      Next
      chaine = chaine & (10 - checksum Mod 10) Mod 10
      'Le premier chiffre est pris tel quel, le deuxième vient de la table A
      CodeBarre = Left(chaine, 1) & Chr(65 + CInt(Mid(chaine, 2, 1)))
      first = CInt(Left(chaine, 1))
      For i = 3 To 7
        tableA = False
         Select Case i
         Case 3
           Select Case first
           Case 0, 1, 2, 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 + CInt(Mid(chaine, i, 1)))
       Else
         CodeBarre = CodeBarre & Chr(75 + CInt(Mid(chaine, i, 1)))
       End If
     Next
      CodeBarre = CodeBarre & "*"   'Ajout séparateur central
      For i = 8 To 13
        CodeBarre = CodeBarre & Chr(97 + CInt(Mid(chaine, i, 1)))
      Next
      CodeBarre = CodeBarre & "+"   'Ajout de la marque de fin
      ean13 = CodeBarre
    End If
  End If
'  MsgBox(ean13)    
End Function

The principal problem is the function val() doesn't exist in vbs, i replace it by CInt() all is numeric ; and the expression case 0 to 3 doesn't exist too in vbs.

So this function work !

Sophie