Techniques avancées de validation des données, Excel VBA

Techniques avancées de validation des données, Excel VBA

Vous trouverez ci-dessous un exemple détaillé et complet de techniques avancées de validation de données dans Excel avec VBA. Nous détaillerons chaque partie du code avec des explications détaillées.
Objectif:
Nous allons créer un code VBA qui implémente des techniques de validation de données complexes telles que :

• Listes de validation personnalisées qui sont dynamiques et dépendent d’autres valeurs de cellules.
• Validation de la plage de dates garantissant que les données se situent dans une plage de dates spécifique.
• Validation de la longueur du texte pour limiter le nombre de caractères saisis dans une cellule.
• Validation basée sur une formule qui valide en fonction d’une formule personnalisée.

Exemple de code VBA étape par étape

Sub implémentationAdvancedDataValidation( )
Dim ws As
Dim rng As Range
« Définissez la feuille de calcul cible et la plage où la validation sera appliquée
Set ws = ThisWorkbook.Sheets ("Sheet1")
‘Exemple 1 : Validation de liste dynamique personnalisée
« La validation dépendra de la valeur de la cellule A1
« Si A1 est « Fruits », la liste doit contenir « Pomme », « Banane », « Orange »
« Si A1 est « Légumes », la liste doit contenir « Carotte », « Pomme de terre », « Tomate »
Set rng = ws.Range ("B2:B10") ' Plage où la validation sera appliquée
' Effacer les validations existantes
    rng.Validation.Delete
' Ajouter une liste de validation dynamique
If ws.Range ("A1").Value = "Fruits" Then
        rng.Validation.Add Type:= xlValidateList , AlertStyle := xlValidAlertStop , _
                           Operator := xlBetween , Formule 1 :=" Pomme, Banane, Orange "
    Else If ws.Range ("A1").Value = "Légumes" Then
        rng.Validation.Add Type:= xlValidateList , AlertStyle := xlValidAlertStop , _
                           Opérateur := xlBetween , Formule 1 :=" Carotte, Pomme de terre, Tomate "
End If
    rng.Validation.IgnoreBlank = True
    rng.Validation.InCellDropdown = True
‘Exemple 2 : Validation de la plage de dates
« Assure que la date saisie est comprise entre le 01-janv.-2020 et le 31-déc.-2025
Set rng = ws.Range ("C2:C10")
' Effacer les validations existantes
    rng.Validation.Delete
' Ajouter une validation de plage de dates
    rng.Validation.Add Type:= xlValidateDate , AlertStyle := xlValidAlertStop , _
                       Operator := xlBetween , Formule1 :="01/01/2020", Formule2 :="31/12/2025"
    rng.Validation.IgnoreBlank = True
    rng.Validation.InCellDropdown = False
‘Exemple 3 : Validation de la longueur du texte
« Limitez la longueur du texte entre 5 et 15 caractères
    Set rng = ws.Range ("D2:D10")
' Effacer les validations existantes
    rng.Validation.Delete
' Ajouter une validation de la longueur du texte
    rng.Validation.Add Type:= xlValidateTextLength , AlertStyle := xlValidAlertStop , _
                       Operator := xlBetween , Formule1 :=5, Formule2 :=15
    rng.Validation.IgnoreBlank = True
    rng.Validation.InCellDropdown = False
‘Exemple 4 : Validation basée sur une formule
« Assurez-vous que la valeur dans E2:E10 est supérieure à la valeur dans D2:D10
    Set rng = ws.Range ("E2:E10")
    ' Effacer les validations existantes
    rng.Validation.Delete
« Ajouter une validation basée sur une formule
    rng.Validation.Add Type:= xlValidateCustom , AlertStyle := xlValidAlertStop , _
                       Operator := xlBetween , Formule1 :="=E2>D2"
    rng.Validation.IgnoreBlank = True
    rng.Validation.InCellDropdown = False
‘Message final
    MsgBox « La validation avancée des données a été appliquée avec succès ! », vbInformation
End Sub

Explication détaillée de chaque étape
1. Validation de liste dynamique basée sur la valeur d’une autre cellule (exemple 1)

« Créer une liste de validation dynamique basée sur la valeur de la cellule A1
If ws.Range ("A1").Value = "Fruits" Then
    rng.Validation.Add Type:= xlValidateList , Formula1:=" Pomme, Banane, Orange "
Else If ws.Range ("A1").Value = "Légumes" Then
    rng.Validation.Add Type:= xlValidateList , Formula1:=" Carotte, Pomme de terre, Tomate "
End If

• Objectif : Cette technique permet de créer une liste déroulante dépendante . Les options de la liste changent en fonction de la valeur saisie dans une cellule parente (par exemple, A1).
• Comment ça travaux :
• La méthode Validation.Add applique la validation des données à une plage spécifiée.
• Si la cellule A1 contient « Fruits », le menu déroulant de B2:B10 affichera les options de fruits. Si la cellule A1 contient « Légumes », le menu déroulant affichera les options de légumes.

2. Validation de la plage de dates (exemple 2)

'Valider que la date saisie est comprise entre le 01-janv.-2020 et le 31-déc.-2025
rng.Validation.Add Type:= xlValidateDate , Formule1:="01/01/2020", Formule2:="31/12/2025"

• Objectif : Cela garantit que les données saisies correspondent à une date valide dans une plage de dates spécifique.
• Comment ça travaux :
• Le type de validation xlValidateDate est utilisé.
• Formule1 et Formule2 spécifient les dates de début et de fin de la plage valide.
• Si l’utilisateur saisit une date en dehors de cette plage, Excel déclenchera un message d’erreur.

3. Validation de la longueur du texte (exemple 3)

'Valider que la longueur du texte saisi est comprise entre 5 et 15 caractères
rng.Validation.Add Type:= xlValidateTextLength , Formule1:=5, Formule2:=15

• Objectif : cela limite la longueur de la saisie de texte dans les cellules à une plage spécifique, empêchant ainsi les entrées excessivement courtes ou longues.
• Comment ça travaux :
• Le type xlValidateTextLength est utilisé pour restreindre la saisie de texte à une plage définie par Formula1 (caractères minimum) et Formula2 (caractères maximum).
• Les utilisateurs ne peuvent saisir que du texte d’une longueur comprise entre 5 et 15 caractères.

4. Validation basée sur une formule (exemple 4)

« Assurez-vous que la valeur dans E2:E10 est supérieure à la valeur dans D2:D10
rng.Validation.Add Type:= xlValidateCustom , Formule1:="=E2>D2"

• Objectif : cette validation utilise une formule personnalisée pour comparer les valeurs entre deux colonnes, garantissant que l’une est supérieure à l’autre.
• Comment ça travaux :
• Le type de validation xlValidateCustom permet l’utilisation d’une formule Excel pour la validation.
• La formule « =E2>D2 » vérifie que la valeur de la colonne E est supérieure à la valeur de la colonne D. Si la condition n’est pas remplie, l’utilisateur verra un message d’erreur.

Supplémentaire Caractéristiques :

• Messages d’erreur : vous pouvez personnaliser le message d’erreur à l’aide des propriétés ErrorTitle et ErrorMessage dans l’objet Validation.
• Gestion de la saisie de données : en définissant la propriété InCellDropdown sur True, vous garantissez que l’utilisateur peut voir une liste déroulante pour les validations basées sur une liste.
• Suppression des validations : la méthode Validation.Delete est utilisée pour effacer toutes les validations existantes avant d’en appliquer de nouvelles.

Conclusion
En utilisant les techniques ci-dessus, vous pouvez créer des règles de validation de données robustes dans Excel via VBA. Cela permet des règles de validation dynamiques, basées sur des formules et même contextuelles, garantissant que les données saisies dans vos feuilles de calcul Excel répondent à vos exigences spécifiques.