Comment comparer automatiquement deux feuilles de calcul sur Excel – On développe une mini-application ensemble

Dans ce tutoriel, je vais vous montrer comment créer une mini-application qui permettra de comparer rapidement deux feuilles de calcul pour en détecter les cellules différentes.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Sélection des feuilles à comparer

La toute première étape à mettre en place avant de pouvoir comparer les feuilles de calculs est de mettre en place un système qui puisse permettre de sélectionner les classeurs et les feuilles concernées.

Pour cela, nous partir d’un petit tableau avec les colonnes reprenant la feuille principale et la feuille secondaire :

Sur la première ligne, nous allons sélectionner les noms des classeurs, et sur la seconde les noms des feuilles de calcul.

   1.1. Sélectionner le nom du classeur

Pour commencer, voyons comment sélectionner facilement le nom du classeur.

Et pour cela, nous allons proposer à l’utilisateur de sélectionner l’un des classeurs parmi tous ceux déjà ouverts.

Bien entendu, pour réaliser cette opération, nous allons passer par le développement d’une petite macro-commande en VBA.

Et pour que ce tutoriel soit accessible à tout le monde, y compris aux grands débutants, nous allons aborder dans le détail chacune des lignes de développement.

Mais si vous souhaitez aller plus loin dans la découverte du langage VBA, vous pouvez évidemment consulter la formation disponible sur le blog en cliquant ici.

Pour commencer, nous allons lancer l’éditeur de code VBE (pour Visual Basic Editor), qui est l’éditeur de code intégré dans les différentes applications de la suite Office et qui permet de développer des macro-commandes en VBA.

Pour cela, nous appuyons simplement sur les touches [Alt]+[F11] :

VBE s’ouvre alors et nous allons commencer par créer un nouveau module.

Un module est une feuille de saisie de code dans laquelle nous allons pouvoir saisir nos macros et fonctions.

Pour insérer un nouveau module, nous nous rendons dans le menu Insertion > Module :

Une fois le module inséré, nous allons créer une nouvelle macro-commande que nous appelons actualiserListe en utilisant simplement le mot clé Sub :

Sub actualiserListe()    
End Sub

Cette macro va analyser tous les classeurs actuellement ouverts et enregistrer leur nom dans une chaîne de caractères.

Nous insèrerons ensuite cette chaîne de caractère dans la règle de validation de la cellule, ce qui permettra d’afficher un menu déroulant.

Pour commencer, nous allons instancier deux variables :

• La première variable, nommée i va nous permettre de passer en revue tous les classeurs ouverts avec une boucle For. Nous allons donc typer cette variable en tant que Integer, c’est-à-dire un nombre entier.
• La seconde variable, nommée listeClasseursOuverts va quant à elle la chaîne de caractères dans laquelle nous allons insérer tous les noms de classeurs ouverts, séparés par des virgules. Nous allons typer cette variable en tant que String.

    Dim i As Integer, listeClasseursOuverts  As String

À présent, nous pouvons mettre en place la boucle For qui va nous permettre d’énumérer tous les classeurs ouverts.

Comme nous l’avons déjà vu dans le tutoriel dédié aux boucles For, celles-ci permettent de répéter une opération un certain nombre de fois.

Ici, nous allons utiliser la variable i comme index pour récupérer le nom de tous les classeurs ouverts.

Nous commençons pour lui donner la valeur de 1, nous allons vouloir que la valeur maximale donnée à la valeur i soit égale au nombre de classeurs ouverts :

For i = 1 To Workbooks.Count
    Next

Ensuite, nous pouvons utiliser la variable i pour récupérer les noms de classeurs que nous stockons dans la chaîne listeClasseursOuverts, en séparant chacun de ces noms par des virgules :

    For i = 1 To Workbooks.Count
        listeClasseursOuverts = listeClasseursOuverts  & Workbooks(i).Name & ","
    Next

Nous pouvons maintenant afficher la chaîne de caractères dans un MsgBox pour visionner le résultat :

    MsgBox listeClasseursOuverts 

Nous retrouvons ici le nom de trois classeurs : le classeur actuel « Excelformation.fr – Comparer deux feuilles de calculs Excel.xlsm » et deux autres classeurs que nous allons souhaiter comparer « Liste personnel 1.xlsx » et « Liste personnel 2.xlsx ».

Parfait !

Par contre, si nous faisons bien attention au résultat proposé, nous pouvons constater qu’une virgule est également présente à la toute fin de la chaîne.

Pour la retirer, nous allons utiliser la fonction Left. Celle-ci permet de récupérer le début d’une chaîne de caractère :

    listeClasseursOuverts = Left(listeClasseursOuverts,  Len(listeClasseursOuverts) - 1)

Nous extrayons ici un nombre de caractères égal au nombre total, minoré d’un caractère.

Maintenant que la chaîne est construite, il ne reste plus qu’à l’insérer en tant que validation de données.

Pour cela, nous revenons sur la feuille de calcul et nous sélectionnons les deux cellules concernées (B7 et C7) :

Puis nous donnons un nom à la plage de ce ces deux cellules en le saisissant simplement dans la zone des noms et en validant en appuyant sur la touche [Entrée] :

Nous appelons ces deux cellules « nomsClasseurs ».

De retour dans le code VBA, nous allons insérer la chaîne de caractères listeClasseursOuverts dans la validation des données avec l’instruction Validation.Add :

    [nomsClasseurs].Validation.Add  Type:=xlValidateList, Formula1:=listeClasseursOuverts 

Attention, dans le cas où les cellules possèderaient déjà une validation, nous allons insérer une ligne juste avant pour la supprimer, en utilisant cette fois-ci l’instruction Validation.Delete :

    [nomsClasseurs].Validation.Delete
    [nomsClasseurs].Validation.Add  Type:=xlValidateList, Formula1:=listeClasseursOuverts 

Nous pouvons maintenant tester la macro en appuyant sur la touche [F5].

En apparence, il ne se passe rien, mais il suffit de revenir dans la feuille de calcul pour constater que les menus déroulants ont maintenant été ajoutés pour permettre de sélectionner les classeurs à sélectionner :

Il ne reste maintenant plus qu’à insérer un bouton pour pouvoir lancer directement la macro depuis la feuille de calcul et ainsi permettre d’actualiser les noms de classeurs.

Pour cela, nous insérons une zone de texte (menu Insertion > Zone de texte) et nous saisissons le texte « Actualiser les noms de classeurs » :

Puis nous effectuons un clic sur cette zone de texte afin de sélectionner « Affecter une macro ».

Dans la fenêtre qui apparaît, nous sélectionnons le nom de la macro-commande que nous venons de créer et nous confirmons en appuyant sur le bouton [OK].

   1.2. Sélectionner le nom de la feuille de calcul

Maintenant que nous connaissons les deux classeurs dans lesquels se trouvent les feuilles à comparer, nous allons pouvoir nous atteler à retrouver ces dernières.

Nous allons donc créer une nouvelle macro, que nous allons souhaiter lancer lorsque nous choisissons un classeur dans les cellules B7 ou C7.

Pour automatiser le lancement de cette macro-commande, nous allons l’insérer dans un évènement VBA.

En effet, utiliser les évènements permet de déclencher automatiquement une ou plusieurs actions lorsque quelque chose en particulier se passe sur un classeur ou une feuille de calcul. Pour en savoir plus sur les évènements VBA, vous pouvez consulter le tutoriel dédié en cliquant ici.

Ici, l’évènement qui nous intéresse est l’évènement Worksheet_SheetChange qui va lancer une macro-commande si une cellule est modifiée.

Pour l’utiliser, nous double cliquons sur la feuille de calcul « excelformation.fr » dans l’explorateur de projet :

VBE affiche alors une feuille de saisie blanche :

Dans les menus déroulants situés au-dessus de la feuille de saisie, nous choisissons respectivement « Worksheet » et « Change » comme ceci :

L’évènement Worksheet_Change s’insère alors automatiquement.

Dans celui-ci la cellule modifiée va nous être retournée par la variable Target.

Nous allons commencer par vérifier que cette variable appartient à la plage des cellules « nomsClasseurs » avec l’instruction Intersect :

    If Not Intersect([nomsClasseurs], Target) Is  Nothing Then
 
    End If

L’instruction Intersect renvoie la plage de cellules commune aux différentes plages passées en argument. Si la cellule modifiée n’appartient pas à la plage nomsClasseurs, alors le résultat retourné sera égal à Nothing (rien).

Nous vérifions justement que ce n’est pas le cas (le résultat doit être différent de Nothing).

Ensuite, nous affichons les noms des feuilles de calculs en procédant d’une manière relativement proche de la méthode découverte dans la partie précédente.

Nous commençons par déclarer deux variables :

• Une variable listeFeuilles dans la laquelle nous allons insérer le nom des feuilles de calcul du classeur sélectionné séparé par des virgules. Cette variable étant une chaîne de caractères nous la typons en tant que String.
• Une seconde variable feuille que nous typons en tant qu’objet Worksheet et qui va nous permettre de passer en revue toutes les feuilles de calcul du classeur sélectionné

        Dim listeFeuilles As String,  feuille As Worksheet 

Ici la boucle For que nous allons utiliser va être légèrement différente de celle mise en place dans la partie précédente.

Il s’agit en effet d’une boucle For Each, qui permet de passer en revue tous les éléments contenus dans une collection, ici toutes les feuilles de calcul d’un classeur :

        For Each feuille In Workbooks(Target.Value).Worksheets
            listeFeuilles = listeFeuilles &  feuille.Name & ","
        Next 
        listeFeuilles = Left(listeFeuilles,  Len(listeFeuilles) - 1)

Il ne reste plus qu’à insérer la valeur de la variable listeFeuille dans la validation des données de la cellule située juste en dessous de la cellule modifiée.

Pour atteindre cette cellule, nous allons utiliser la méthode Offset qui permet justement d’effectuer un décalage de cellule : ici ce décalage correspond à une cellule vers le bas, et aucune cellule horizontalement.

        Target.Offset(1, 0).Validation.Delete
        Target.Offset(1, 0).Validation.Add  Type:=xlValidateList, Formula1:=listerFeuilles(Target.Value)

Avant de tester la macro, nous allons ajouter une dernière ligne qui va permettre de supprimer le contenu éventuellement inséré dans la cellule :

        Target.Offset(1, 0).ClearContents

Nous pouvons maintenant tester la procédure en sélectionnant simplement des classeurs ouverts dans le tableau :

2. Analyse des différences

Maintenant que nous connaissons les noms des classeurs et des feuilles de calcul à analyser, nous allons pouvoir créer notre dernière macro-commande.

Nous allons appeler cette macro lancerAnalyse :

Sub lancerAnalyse()
 
End Sub

Nous allons commencer par dupliquer la feuille de calcul principale dans le classeur actuel, mais en ne rapatriant que les valeur (sans les formules).

Nous cela, nous insérons une nouvelle feuille dans le classeur :

    Sheets.Add After:=ActiveSheet

Puis nous allons effectuer un copier-coller spécial de toutes les cellules contenues dans la feuille d’origine afin de ne reprendre que les valeurs et formats :

    Workbooks([nomsClasseurs].Cells(1).Value).Worksheets([nomsClasseurs].Cells(1).Offset(1,  0).Value).Cells.Copy
    Selection.PasteSpecial Paste:=xlPasteFormats
    Selection.PasteSpecial Paste:=xlPasteValues

Pour tester si tout fonctionne correctement, nous pouvons créer un nouveau bouton qui lance cette macro-commande (voir la partie précédente) :

Normalement si tout va bien, vous devriez vous retrouver avec une nouvelle feuille de calcul :

Pour lancer l’analyse, nous allons maintenant passer en revue toutes les cellules de cette feuille et vérifier si les valeurs sont égales à celles situées sur la feuille de comparaison.

Nous allons donc utiliser une nouvelle boucle For Each dans laquelle nous allons utiliser une variable nommée c qui va reprendre toutes les cellules de la plage des cellules utilisées, cette plage étant récupérée avec UsedRange :

    Dim c As Range
    For Each c In ActiveSheet.UsedRange
 
    Next

Ensuite, il ne reste plus qu’à contrôler si la cellule correspondante est différente, et dans ce cas-là nous modifierons la couleur de fond pour que la cellule soit facilement identifiable :

                If Workbooks([nomsClasseurs].Cells(1).Value).Worksheets([nomsClasseurs].Cells(1).Offset(1,  0).Value).Range(c.Address).Value <> Workbooks([nomsClasseurs].Cells(2).Value).Worksheets([nomsClasseurs].Cells(2).Offset(1,  0).Value).Range(c.Address).Value Then
            c.Interior.ColorIndex = 1
        End If

Nous pouvons maintenant tester la macro-commande pour identifier rapidement les cellules différentes entre les deux feuilles de calcul avec la couleur de fond noir :