Discussion :

[Rémy A.]

Bonjour les développeurs,

Voici mon soucis du jour :

J'ai un tableau sous Excel avec plusieurs lignes d'en-tête et pas mal de kilometres de données en-dessous, dans le tableau. Parmis les lignes d'en-tête, l'un d'elle précise le type et la longueur de la donnée présente dans chaque colonne.
Par exemple, j'ai des colonnes qui indiquent "20 Alphanumérique", "3 Alphanumérique", "Booléen", "13 Numérique", "Date"...

Je dois établir une boucle de contrôle sur les données pour vérifier qu'elles répondent bien à la condition imposée. Les cellules qui ne sont pas du bon type seront surlignées en rouge.

Voici un exemple de mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Sub controle_qualite_alphanum3()
 
    Dim Col As Integer
    Dim Lin As Integer
    Dim Err As Integer
 
    Sheets("Qualité des données").Select
 
    Err = 0
    Col = 2
    Lin = 15
 
    For Col = 2 To Sheets("Structure").Range("F5")
        If Cells(12, Col) = "3 Alphanumérique" Then
            For Lin = 15 To 46
            Cells(Lin, Col).Select
 
                ' Ne doit contenir que des caractères alphanumériques
                If (Cells(Lin, Col)) <> [0-9a-zA-Z] Then
                    mettre_en_rouge
                    Err = Err + 1
                End If
 
                ' Doit être de longueur 3 ou moins
                If Len(Cells(Lin, Col)) > 3 Then
                    mettre_en_rouge
                    Err = Err + 1
                End If
 
            Next Lin
        End If
    Next Col
 
End Sub

J'ai DEUX questions (eh oui !).

1°) La plus grave. Ca me bloque.
En exécutant ma macro pas-à-pas, je m'apperçois que je ne rentre jamais dans ma première condition if ("If Cells(12, Col) = "3 Alphanumérique" Then..."), alors que j'ai bel et bien des données qui ont ce type.
Je pense savoir pourquoi mais je ne sais pas comment m'y prendre. Dans mon tableau, les types et longueurs sont définis avec un retour à la ligne entre le "3" et le "Alphanumérique", tout en restant dans une seule cellule.
Y a-t-il un moyen pour forcer ma condition à considérer TOUT le contenu d'une cellule, même s'il y a un retour chariot dans cette cellule ?
Bien sur, je ne peux pas éviter d'avoir ce retour à la ligne. Je charge cet en-tête déjà tout prêt avec une autre macro.

2°) Un peu plus auxiliaire. Mais quand même.
Je n'ai pas trouvé mieux que de copier-coller mon code en changeant "3 Alphanumérique" par "5 Alphanumérique", "10 Alphanumérique"...
N'y a-t-il pas un moyen de rajouter une boucle pour rendre tout ça plus simple et plus propre ?

Merci d'avance (et désolé pour le gros pavé ! Si j'explique lentement, c'est pour qu'on comprenne vite...) !

R.

[parmi]

Bonjour,

Pour la question 1, il y a différentes possibilités.
Tu peux vérifier le texte en incluant le saut de ligne, du genre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If Cells(12, Col) = "3" & vbLf & "Alphanumérique" Then

si c'est bien vbLf qui a été utilisé pour éviter d'afficher le petit carré blanc. Et là, c'est toi qui sait comment tu as chargé ces valeurs.

Autrement, tu pourrais utiliser la fonction Split() pour séparer la valeur du texte en 2 parties, mais ça risque d'être plus compliqué et pas nécessairement rapide non plus... ou encore Instr() pour retrouver les 2 portions de texte...

Pour la question 2, tu pourrais avoir une variable qui change selon la colonne, du genre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
If Col = 5 then Valeur = 3
If Cells(12, Col) = Valeur & vbLf & "Alphanumérique" Then

Par contre, au niveau des vérifications faites sur chaque type de données, je ne suis pas certain que ça puisse vraiment fonctionner... Ça dépend de ce que tu as à vérifier.

Petit conseil: évite les Select lorsque ce n'est pas nécessaire.
Dans le cas de ta Sub mettre_en_rouge, tu pourrais y passer 2 paramètres pour la ligne et la colonne. De cette façon, sans l'avoir vue, tu n'es pas obligé d'utiliser de Select...

[BlueMonkey]

Salut,

Ci-dessous une solution permettant de :
- mixer des types et nombre de caractères.
- ne pas filtrer le nom des colonnes sur les caractères <espace>, <CR>, <LF>
(basé sur une autre piste que celles présentées par Parmi : la fonction Replace)
Les types de colonne sont identifiés par une énumération.
Le test de contenu est basé suivant le cas sur des expressions rationnelles, ou des fonctions pré-existante en VBA.

L'exemple par du même principe que celui énoncés dans le post #1. (test date, puis booléan, puis alphanum3 ....)
Mais suivant l'utilisation, il est peut être plus simple de décoder la cellule contenant le type et le nombre de caractère,
puis de faire la vérification sur la colonne.
(il y aurait moins d'itérations pour traiter tout le document, et le code ne dépendrait pas de l'apparition d'une nouvelle taille de type)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
Enum COL_TYPE
    T_ALPHA_NUM
    T_BOOLEAN
    T_ALPHA
    T_NUM
    T_DATE
End Enum
 
Sub controle_qualite_date()
    controle_qualite T_DATE, Sheets("Structure").Range("F5")
End Sub
 
Sub controle_qualite_boolean()
    controle_qualite T_BOOLEAN, Sheets("Structure").Range("F5")
End Sub
 
Sub controle_qualite_num10()
    controle_qualite T_NUM, Sheets("Structure").Range("F5"), 10
End Sub
 
Sub controle_qualite_alphanum3()
    controle_qualite T_ALPHA_NUM, Sheets("Structure").Range("F5"), 3
End Sub
 
Sub controle_qualite_alphanum10()
    controle_qualite T_ALPHA_NUM, Sheets("Structure").Range("F5"), 10
End Sub
 
Private Function IsTitleFromType(ByVal titleToCheck As String, typ As COL_TYPE, Optional NbChar As Integer = -1) As Boolean
 
    IsTitleFromType = False
    Dim title As String
      Select Case typ
        Case T_ALPHA_NUM:  title = "Alphanumérique"
        Case T_BOOLEAN: title = "Booléen"
        Case T_ALPHA: title = "Aplha"
        Case T_NUM: title = "Numérique"
        Case T_DATE: title = "Date"
        Case Else
            MsgBox "IsTitleType Error : Type de colonne nom défini :" & typ
    End Select
 
    If Len(title) > 0 Then
        If NbChar > 0 Then
            title = NbChar & title
        End If
        titleToCheck = Replace(Replace(Replace(titleToCheck, " ", ""), vbLf, ""), vbCr, "")
        IsTitleFromType = (titleToCheck = title)
    End If
 
End Function
 
Private Function IsCellFromType(val As String, typ As COL_TYPE) As Boolean
    Dim RegEx As Variant
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .IgnoreCase = True
        '.Global = false
    End With
 
    Select Case typ
        Case T_ALPHA_NUM:  RegEx.Pattern = "[^0-9a-z]": IsCellFromType = Not (RegEx.Test(val))
        Case T_BOOLEAN: IsCellFromType = (LCase(val) = "vrai") Or (LCase(val) = "faux")
        Case T_ALPHA: RegEx.Pattern = "[^a-z]": IsCellFromType = Not (RegEx.Test(val))
        Case T_NUM: IsCellFromType = IsNumeric(val)
        Case T_DATE: IsCellFromType = IsDate(val)
        Case Else
            MsgBox "IsCellFromType Error : Type non défini :" & typ
    End Select
 
    Set RegEx = Nothing
 
End Function
 
 
Sub controle_qualite(typ As COL_TYPE, lastCol As Integer, Optional NbChar As Integer = -1)
    Dim Col As Integer
    Dim Lin As Integer
    Dim Err As Integer: Err = 0
 
    With Sheets("Qualité des données")
       For Col = 2 To lastCol
           If IsTitleFromType(.Cells(12, Col), typ, NbChar) Then
                For Lin = 15 To 46
                    With .Cells(Lin, Col)
                        '.Select
                        If Not (IsCellFromType(.Value, typ)) Then
                            .Font.Color = vbRed 'mettre_en_rouge
                            Err = Err + 1
                        End If
 
                        ' Doit être de longueur <NbChar> ou moins
                        If NbChar >= 0 And Len(.Value) > NbChar Then
                            .Font.Color = vbRed 'mettre_en_rouge
                            Err = Err + 1
                        End If
                    End With
               Next Lin
           End If
       Next Col
    End With
End Sub

[Rémy A.]

Bonjour Parmi et BlueMonkey. Merci pour vos réponses ! =)

Parmi, pour répondre a ta remarque en fin de message, si je fais pas de Select sur les cellules que je balaye, les vérifications sont faites sans que les cellules concernées soient actives, et c'est la cellule active qui se surligne en rouge. Mieux : elle se surligne en rouge autant de fois qu'il y a d'erreurs...
Comme je me sers de ma macro mettre_en_rouge à plusieurs endroit, je n'ai pas envie de la modifier en y ajoutant des paramètres d'entrées... Elle est déjà fonctionnelle partout ailleurs...
Du coup, ce Select est un contournement simple. Mais je suis d'accord, y a surement mieux...

Je vais tester vos réponses ce matin. Je vais commencer par celle de Parmi, qui me semble la plus légère à mettre en oeuvre et la plus facile d'accès. Si je n'y arrive pas, je tenterai celle de BlueMonkey...
Je vous tiens au courant si le problème évolue. Merci encore ! =)

[EDIT]
La solution de Parmi fonctionne. Franchement, j'y croyais pas trop à ce vbLf, parce que j'avais essayé avec chr(13) et ça ne marchait pas...
Enfin, quand je dis "ça fonctionne"... Ca rentre dans la boucle quand ma colonne indique bel et bien "3 [retour chariot] Alphanumérique" ; et ma première donnée se sélectionne bien... (Un autre intérêt à avoir mis ce Select ! ^^' Je peux contrôler que je suis bien dans la boucle, non pas à l'échelle du code VB mais à l'échelle de la feuille Excel !)

Par contre, ça chie dans la colle à l'intérieur de ma boucle.
J'ai une erreur 13 (incompatibilité de type) au niveau de cette ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If (Cells(Lin, Col)) <> [0-9a-zA-Z] Then

J'avais utilisé ça parce qu'il me semblait qu'il n'existait pas d'équivalent à la fonction IsNumeric pour les alphanumériques (du type IsAlphanum, ou IsAlphanumeric, ou je sais pas...). Ca ne fonctionne pas avec IsAlphanumeric, la fonction n'est pas reconnue, et ça ne marche pas non plus avec mon contournement...

Une idée ?
Merci d'avance ! =)

[/EDIT]

re-[EDIT]

J'ai cherché sur le net et il semble que ça n'existe pas. Plusieurs topics sur plusieurs forums conseillent de créer sa propre fonction. Ne trouvant pas, je me suis donc bricolé un truc, en m'inspirant d'un code trouvé sur un autre forum et en l'adaptant.
Pour info, ça donne ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Function IsAlphanum(str As String) As Boolean
' Méthode équivalente à "IsNumeric" pour des alphanumériques...
 
    Dim i As Integer
 
    IsAlphanum = True
        For i = 1 To Len(Trim(str))
            Select Case Mid$(Trim(str), i, 1)
            Case "A" To "Z", "a" To "z", "0" To "9"
            Case Else
                IsAlphanum = False
        Exit For
        End Select
    Next i
 
End Function

Puis plus loin, dans mon Sub controle_alphanum_3 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If IsAlphanum(Cells(Lin, Col)) = False Then

Et ça marche nickel !

Allez cette fois je met le post en résolu et j'arrête mes multi-edits... Si j'ai un autre soucis j'ouvrirais un autre post ! ^^'

Merci encore ! =)

Re-[/EDIT]

R.