Travailler avec des feuilles de calcul à l'aide d'Excel VBA (expliqué avec des exemples)

Outre les cellules et les plages, l'utilisation de feuilles de calcul est un autre domaine que vous devez connaître pour utiliser efficacement VBA dans Excel.

Comme tout objet dans VBA, les feuilles de calcul ont différentes propriétés et méthodes associées que vous pouvez utiliser tout en automatisant votre travail avec VBA dans Excel.

Dans ce tutoriel, je couvrirai les « feuilles de travail » en détail et je vous montrerai également quelques exemples pratiques.

Alors, commençons.

Tous les codes que je mentionne dans ce tutoriel doivent être placés dans l'éditeur VB. Rendez-vous dans la section « Où mettre le code VBA » pour savoir comment cela fonctionne.

Si vous souhaitez apprendre VBA en toute simplicité, consultez mon Formation en ligne Excel VBA.

Différence entre les feuilles de calcul et les feuilles en VBA

En VBA, vous avez deux collections qui peuvent parfois être un peu déroutantes.

Dans un classeur, vous pouvez avoir des feuilles de calcul et des feuilles de graphique. L'exemple ci-dessous comporte trois feuilles de calcul et une feuille de graphique.

Dans Excel VBA :

• La collection « Feuilles de calcul » ferait référence à la collection de tous les objets de feuille de calcul dans un classeur. Dans l'exemple ci-dessus, la collection Worksheets consisterait en trois feuilles de calcul.
• La collection « Feuilles » ferait référence à toutes les feuilles de calcul ainsi qu'aux feuilles de graphique du classeur. Dans l'exemple ci-dessus, il aurait quatre éléments - 3 feuilles de calcul + 1 feuille de graphique.

Si vous avez un classeur qui ne contient que des feuilles de calcul et aucune feuille de graphique, les collections « Feuilles de calcul » et « Feuilles » sont identiques.

Mais lorsque vous avez une ou plusieurs feuilles de graphique, la collection "Feuilles" serait plus grande que la collection "Feuilles de travail"

Feuilles = Feuilles de calcul + Feuilles graphiques

Maintenant, avec cette distinction, je recommande d'être aussi précis que possible lors de l'écriture d'un code VBA.

Donc, si vous devez vous référer uniquement aux feuilles de calcul, utilisez la collection « Feuilles de calcul », et si vous devez vous référer à toutes les feuilles (y compris les feuilles de graphique), utilisez la collection « Feuilles ».

Dans ce tutoriel, j'utiliserai uniquement la collection « Feuilles de travail ».

Référencer une feuille de calcul en VBA

Il existe de nombreuses manières différentes de faire référence à une feuille de calcul en VBA.

Comprendre comment faire référence aux feuilles de calcul vous aiderait à écrire un meilleur code, en particulier lorsque vous utilisez des boucles dans votre code VBA.

Utilisation du nom de la feuille de calcul

La façon la plus simple de faire référence à une feuille de calcul est d'utiliser son nom.

Par exemple, supposons que vous ayez un classeur avec trois feuilles de calcul - Feuille 1, Feuille 2, Feuille 3.

Et vous voulez activer la feuille 2.

Vous pouvez le faire en utilisant le code suivant : Sub ActivateSheet() Worksheets("Sheet2").Activate End Sub

Le code ci-dessus demande à VBA de faire référence à Sheet2 dans la collection Worksheets et de l'activer.

Puisque nous utilisons le nom exact de la feuille, vous pouvez également utiliser la collection Sheets ici. Donc, le code ci-dessous ferait également la même chose.

Sub ActivateSheet() Sheets("Sheet2").Activate End Sub

Utilisation du numéro d'index

Bien que l'utilisation du nom de la feuille soit un moyen simple de faire référence à une feuille de calcul, il se peut que vous ne connaissiez pas parfois le nom exact de la feuille de calcul.

Par exemple, si vous utilisez un code VBA pour ajouter une nouvelle feuille de calcul au classeur et que vous ne savez pas combien de feuilles de calcul sont déjà présentes, vous ne sauriez pas le nom de la nouvelle feuille de calcul.

Dans ce cas, vous pouvez utiliser le numéro d'index des feuilles de calcul.

Supposons que vous ayez les feuilles suivantes dans un classeur :

Le code ci-dessous activerait Sheet2 :

Sub ActivateSheet() Worksheets(2).Activate End Sub

Notez que nous avons utilisé l'index numéro 2 dans Feuilles de travail(2). Cela ferait référence au deuxième objet de la collection des feuilles de calcul.

Maintenant, que se passe-t-il lorsque vous utilisez 3 comme numéro d'index ?

Il sélectionnera Sheet3.

Si vous vous demandez pourquoi il a sélectionné Sheet3, car c'est clairement le quatrième objet.

Cela se produit car une feuille de graphique ne fait pas partie de la collection de feuilles de calcul.

Ainsi, lorsque nous utilisons les numéros d'index dans la collection Worksheets, cela ne fera référence qu'aux feuilles de calcul du classeur (et ignorera les feuilles de graphique).

Au contraire, si vous utilisez Sheets, Sheets(1) ferait référence à Sheets1, Sheets(2) ferait référence à Sheet2, Sheets(3) ferait référence à Chart1 et Sheets(4) ferait référence à Sheet3.

Cette technique d'utilisation du numéro d'index est utile lorsque vous souhaitez parcourir toutes les feuilles de calcul d'un classeur. Vous pouvez compter le nombre de feuilles de calcul, puis les parcourir en boucle à l'aide de ce nombre (nous verrons comment procéder plus tard dans ce didacticiel).

Remarque : Le numéro d'index va de gauche à droite. Donc, si vous déplacez Sheet2 vers la gauche de Sheet1, alors Worksheets(1) fera référence à Sheet2.

Utilisation du nom de code de la feuille de calcul

L'un des inconvénients de l'utilisation du nom de la feuille (comme nous l'avons vu dans la section ci-dessus) est qu'un utilisateur peut le modifier.

Et si le nom de la feuille a été modifié, votre code ne fonctionnera pas tant que vous ne modifierez pas également le nom de la feuille de calcul dans le code VBA.

Pour résoudre ce problème, vous pouvez utiliser le nom de code de la feuille de calcul (au lieu du nom normal que nous avons utilisé jusqu'à présent). Un nom de code peut être attribué dans l'éditeur VB et ne change pas lorsque vous modifiez le nom de la feuille dans la zone de la feuille de calcul.

Pour donner un nom de code à votre feuille de calcul, suivez les étapes ci-dessous :

1. Cliquez sur l'onglet Développeur.
2. Cliquez sur le bouton Visual Basic. Cela ouvrira l'éditeur VB.
3. Cliquez sur l'option Afficher dans le menu et cliquez sur Fenêtre de projet. Cela rendra le volet Propriétés visible. Si le volet Propriétés est déjà visible, ignorez cette étape.
4. Cliquez sur le nom de la feuille dans l'explorateur de projet que vous souhaitez renommer.
5. Dans le volet Propriétés, modifiez le nom dans le champ devant (Nom). Notez que vous ne pouvez pas avoir d'espaces dans le nom.

Les étapes ci-dessus modifieraient le nom de votre feuille de calcul dans le backend VBA. Dans la vue feuille de calcul Excel, vous pouvez nommer la feuille de calcul comme vous le souhaitez, mais dans le backend, elle répondra à la fois aux noms - le nom de la feuille et le nom de code.

Dans l'image ci-dessus, le nom de la feuille est "SheetName" et le nom de code est "CodeName". Même si vous modifiez le nom de la feuille sur la feuille de calcul, le nom de code reste le même.

Maintenant, vous pouvez utiliser la collection Worksheets pour faire référence à la feuille de calcul ou utiliser le nom de code.

Par exemple, les deux lignes activeront la feuille de calcul.

Worksheets("Sheetname").Activer CodeName.Activate

La différence entre les deux est que si vous modifiez le nom de la feuille de calcul, la première ne fonctionnera pas. Mais la deuxième ligne continuerait à fonctionner même avec le nom modifié. La deuxième ligne (utilisant le nom de code) est également plus courte et plus facile à utiliser.

Se référer à une feuille de calcul dans un autre classeur

Si vous souhaitez faire référence à une feuille de calcul dans un autre classeur, ce classeur doit être ouvert pendant l'exécution du code et vous devez spécifier le nom du classeur et de la feuille de calcul auxquels vous souhaitez vous référer.

Par exemple, si vous avez un classeur nommé Exemples et que vous souhaitez activer Sheet1 dans le classeur Exemple, vous devez utiliser le code ci-dessous :

Sub SheetActivate() Workbooks("Examples.xlsx").Worksheets("Sheet1").Activate End Sub

Notez que si le classeur a été enregistré, vous devez utiliser le nom du fichier avec l'extension. Si vous n'êtes pas sûr du nom à utiliser, demandez l'aide de l'Explorateur de projets.

Si le classeur n'a pas été enregistré, vous n'avez pas besoin d'utiliser l'extension de fichier.

Ajout d'une feuille de calcul

Le code ci-dessous ajouterait une feuille de calcul (en tant que première feuille de calcul, c'est-à-dire en tant que feuille la plus à gauche dans l'onglet de la feuille).

Sub AddSheet() Worksheets.Add End Sub

Il prend le nom par défaut Sheet2 (ou tout autre nombre en fonction du nombre de feuilles déjà présentes).

Si vous souhaitez qu'une feuille de calcul soit ajoutée avant une feuille de calcul spécifique (disons Sheet2), vous pouvez utiliser le code ci-dessous.

Sub AddSheet() Worksheets.Add Before:=Worksheets("Sheet2") End Sub

Le code ci-dessus indique à VBA d'ajouter une feuille, puis utilise l'instruction « Before » pour spécifier la feuille de calcul avant laquelle la nouvelle feuille de calcul doit être insérée.

De même, vous pouvez également ajouter une feuille après une feuille de calcul (disons Sheet2), en utilisant le code ci-dessous :

Sub AddSheet() Worksheets.Add After:=Worksheets("Sheet2") End Sub

Si vous souhaitez que la nouvelle feuille soit ajoutée à la fin des feuilles, vous devez d'abord savoir combien de feuilles il y a. Le code suivant compte d'abord le nombre de feuilles et ajoute la nouvelle feuille après la dernière feuille (à laquelle nous nous référons en utilisant le numéro d'index).

Sub AddSheet() Dim SheetCount As Integer SheetCount = Worksheets.Count Worksheets.Add After:=Worksheets(SheetCount) End Sub

Supprimer une feuille de calcul

Le code ci-dessous supprimerait la feuille active du classeur.

Sub DeleteSheet() ActiveSheet.Delete End Sub

Le code ci-dessus afficherait une invite d'avertissement avant de supprimer la feuille de calcul.

Si vous ne voulez pas voir l'invite d'avertissement, utilisez le code ci-dessous :

Sub DeleteSheet() Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub

Lorsque Application.DisplayAlerts est défini sur False, il ne vous montrera pas l'invite d'avertissement. Si vous l'utilisez, n'oubliez pas de le remettre à True à la fin du code.

N'oubliez pas que vous ne pouvez pas annuler cette suppression, utilisez donc le code ci-dessus lorsque vous en êtes absolument sûr.

Si vous souhaitez supprimer une feuille spécifique, vous pouvez le faire en utilisant le code suivant :

Sub DeleteSheet() Worksheets("Sheet2").Delete End Sub

Vous pouvez également utiliser le nom de code de la feuille pour la supprimer.

Sub DeleteSheet() Sheet5.Delete End Sub

Renommer les feuilles de calcul

Vous pouvez modifier la propriété name de la feuille de calcul pour changer son nom.

Le code suivant changera le nom de Sheet1 en « Résumé ».

Sub RenameSheet() Worksheets("Sheet1").Name = "Summary" End Sub

Vous pouvez combiner cela avec la méthode d'ajout de feuille pour avoir un ensemble de feuilles avec des noms spécifiques.

Par exemple, si vous souhaitez insérer quatre feuilles portant le nom 2021-2022 Q1, 2021-2022 Q2, 2021-2022 Q3 et 2021-2022 Q4, vous pouvez utiliser le code ci-dessous.

Sub RenameSheet() Dim Countsheets As Integer Countsheets = Worksheets.Count For i = 1 To 4 Worksheets.Add after:=Worksheets (Countsheets + i - 1) Worksheets (Countsheets + i).Name = "2018 Q" & i Next i Fin du sous-marin

Dans le code ci-dessus, nous comptons d'abord le nombre de feuilles, puis utilisons une boucle For Next pour insérer de nouvelles feuilles à la fin. Au fur et à mesure que la feuille est ajoutée, le code la renomme également.

Affectation d'un objet feuille de calcul à une variable

Lorsque vous travaillez avec des feuilles de calcul, vous pouvez affecter une feuille de calcul à une variable d'objet, puis utiliser la variable à la place des références de la feuille de calcul.

Par exemple, si vous souhaitez ajouter un préfixe d'année à toutes les feuilles de calcul, au lieu de compter les feuilles et d'exécuter la boucle autant de fois, vous pouvez utiliser la variable objet.

Voici le code qui ajoutera 2021-2022 comme préfixe à tous les noms de la feuille de calcul.

Sub RenameSheet() Dim Ws As Worksheet pour chaque Ws dans les feuilles de calcul Ws.Name = "2018 - " & Ws.Name Next Ws End Sub

Le code ci-dessus déclare une variable Ws comme type de feuille de calcul (en utilisant la ligne « Dim Ws As Worksheet »).

Maintenant, nous n'avons pas besoin de compter le nombre de feuilles pour les parcourir. Au lieu de cela, nous pouvons utiliser la boucle « Pour chaque Ws dans les feuilles de calcul ». Cela nous permettra de parcourir toutes les feuilles de la collection de feuilles de calcul. Peu importe qu'il y ait 2 feuilles ou 20 feuilles.

Bien que le code ci-dessus nous permette de parcourir toutes les feuilles, vous pouvez également affecter une feuille spécifique à une variable.

Dans le code ci-dessous, nous attribuons la variable Ws à Sheet2 et l'utilisons pour accéder à toutes les propriétés de Sheet2.

Sub RenameSheet() Dim Ws As Worksheet Set Ws = Worksheets("Sheet2") Ws.Name = "Summary" Ws.Protect End Sub

Une fois que vous avez défini une référence de feuille de calcul à une variable d'objet (à l'aide de l'instruction SET), cet objet peut être utilisé à la place de la référence de feuille de calcul. Cela peut être utile lorsque vous avez un code long et compliqué et que vous souhaitez modifier la référence. Au lieu d'effectuer la modification partout, vous pouvez simplement effectuer la modification dans l'instruction SET.

Notez que le code déclare l'objet Ws en tant que variable de type Worksheet (en utilisant la ligne Dim Ws as Worksheet).

Masquer les feuilles de calcul à l'aide de VBA (caché + très caché)

Masquer et afficher des feuilles de calcul dans Excel est une tâche simple.

Vous pouvez masquer une feuille de calcul et l'utilisateur ne la verra pas lorsqu'il ouvrira le classeur. Cependant, ils peuvent facilement afficher la feuille de calcul en cliquant avec le bouton droit sur n'importe quel onglet de feuille.

Mais que se passe-t-il si vous ne voulez pas qu'ils puissent afficher la ou les feuilles de calcul ?

Vous pouvez le faire en utilisant VBA.

Le code ci-dessous masquerait toutes les feuilles de calcul du classeur (à l'exception de la feuille active), de sorte que vous ne pouvez pas l'afficher en cliquant avec le bouton droit sur le nom de la feuille.

Sub HideAllExcetActiveSheet() Dim Ws As Worksheet pour chaque Ws dans ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetVeryHidden Next Ws End Sub

Dans le code ci-dessus, la propriété Ws.Visible est modifiée en xlFeuilleTrèsCaché.

• Lorsque la propriété Visible est définie sur xlSheetVisible, la feuille est visible dans la zone de la feuille de calcul (sous forme d'onglets de feuille de calcul).
• Lorsque la propriété Visible est définie sur xlSheetHidden, la feuille est masquée mais l'utilisateur peut l'afficher en cliquant avec le bouton droit sur n'importe quel onglet de feuille.
• Lorsque la propriété Visible est définie sur xlSheetVeryHidden, la feuille est masquée et ne peut pas être affichée dans la zone de la feuille de calcul. Vous devez utiliser un code VBA ou la fenêtre des propriétés pour l'afficher.

Si vous souhaitez simplement masquer des feuilles, qui peuvent être facilement affichées, utilisez le code ci-dessous :

Sub HideAllExceptActiveSheet() Dim Ws As Worksheet pour chaque Ws dans ThisWorkbook.Worksheets If Ws.Name ActiveSheet.Name Then Ws.Visible = xlSheetHidden Next Ws End Sub

Le code ci-dessous afficherait toutes les feuilles de calcul (à la fois cachées et très cachées).

Sub UnhideAllWoksheets() Dim Ws As Worksheet pour chaque Ws dans ThisWorkbook.Worksheets Ws.Visible = xlSheetVisible Next Ws End Sub

Article associé: Afficher toutes les feuilles dans Excel (en une seule fois)

Masquer les feuilles en fonction du texte qu'il contient

Supposons que vous ayez plusieurs feuilles avec le nom de différents départements ou années et que vous souhaitiez masquer toutes les feuilles sauf celles qui contiennent l'année 2021-2022.

Vous pouvez le faire en utilisant une fonction VBA INSTR.

Le code ci-dessous masquerait toutes les feuilles, à l'exception de celles contenant le texte 2021-2022.

Sub HideWithMatchingText() Dim Ws As Worksheet pour chaque Ws dans les feuilles de calcul If InStr(1, Ws.Name, "2018", vbBinaryCompare) = 0 Then Ws.Visible = xlSheetHidden End If Next Ws End Sub

Dans le code ci-dessus, la fonction INSTR renvoie la position du caractère où elle trouve la chaîne correspondante. S'il ne trouve pas la chaîne correspondante, il renvoie 0.

Le code ci-dessus vérifie si le nom contient le texte 2021-2022. Si c'est le cas, rien ne se passe, sinon la feuille de calcul est masquée.

Vous pouvez aller plus loin en plaçant le texte dans une cellule et en utilisant cette cellule dans le code. Cela vous permettra d'avoir une valeur dans la cellule, puis lorsque vous exécuterez la macro, toutes les feuilles, à l'exception de celle contenant le texte correspondant, resteraient visibles (avec les feuilles où vous entrez la valeur dans le cellule).

Tri des feuilles de calcul par ordre alphabétique

En utilisant VBA, vous pouvez rapidement trier les feuilles de calcul en fonction de leurs noms.

Par exemple, si vous avez un classeur qui contient des feuilles pour différents départements ou années, vous pouvez utiliser le code ci-dessous pour trier rapidement ces feuilles dans un ordre croissant.

Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

Notez que ce code fonctionne bien avec les noms de texte et dans la plupart des cas avec les années et les nombres aussi. Mais cela peut vous donner de mauvais résultats au cas où vous auriez les noms de feuille 1,2,11. Il triera et vous donnera la séquence 1, 11, 2. C'est parce qu'il fait la comparaison sous forme de texte et considère 2 plus grands que 11.

Protéger/Déprotéger toutes les feuilles en une seule fois

Si vous avez beaucoup de feuilles de calcul dans un classeur et que vous souhaitez protéger toutes les feuilles, vous pouvez utiliser le code VBA ci-dessous.

Il vous permet de spécifier le mot de passe dans le code. Vous aurez besoin de ce mot de passe pour déprotéger la feuille de calcul.

Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'remplacez Test123 par le mot de passe que vous souhaitez Pour chaque ws dans les feuilles de calcul ws.Protect password:=password Next ws End Sub

Le code suivant déprotégerait toutes les feuilles en une seule fois.

Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'remplacez Test123 par le mot de passe que vous avez utilisé lors de la protection de For Each ws In Worksheets ws.Unprotect password:=password Next ws End Sub

Création d'une table des matières de toutes les feuilles de calcul (avec des hyperliens)

Si vous avez un ensemble de feuilles de calcul dans le classeur et que vous souhaitez insérer rapidement une feuille récapitulative contenant les liens vers toutes les feuilles, vous pouvez utiliser le code ci-dessous.

Sub AddIndexSheet() Worksheets.Add ActiveSheet.Name = "Index" For i = 2 To Worksheets.Count ActiveSheet.Hyperlinks.Add Anchor:=Cells(i - 1, 1), _ Address:="", SubAddress:=Worksheets (i).Name & "!A1", _ TextToDisplay:=Worksheets(i).Name Next i End Sub

Le code ci-dessus insère une nouvelle feuille de calcul et la nomme Index.

Il parcourt ensuite toutes les feuilles de calcul et crée un lien hypertexte pour toutes les feuilles de calcul de la feuille d'index.

Où mettre le code VBA

Vous vous demandez où va le code VBA dans votre classeur Excel ?

Excel a un backend VBA appelé éditeur VBA. Vous devez copier et coller le code dans la fenêtre de code du module VB Editor.

Voici les étapes à suivre :

1. Allez dans l'onglet Développeur.
2. Cliquez sur l'option Visual Basic. Cela ouvrira l'éditeur VB dans le backend.
3. Dans le volet Explorateur de projets de l'éditeur VB, cliquez avec le bouton droit sur n'importe quel objet du classeur dans lequel vous souhaitez insérer le code. Si vous ne voyez pas l'Explorateur de projets, accédez à l'onglet Affichage et cliquez sur Explorateur de projets.
4. Allez dans Insérer et cliquez sur Module. Cela insérera un objet module pour votre classeur.
5. Copiez et collez le code dans la fenêtre du module.

Vous aimerez peut-être également les didacticiels Excel VBA suivants :

• Travailler avec des classeurs en utilisant VBA.
• Utilisation des instructions IF Then Else dans VBA.
• Pour la prochaine boucle en VBA.
• Création d'une fonction définie par l'utilisateur dans Excel.
• Comment enregistrer une macro dans Excel.
• Comment exécuter une macro dans Excel.
• Événements Excel VBA - Un guide simple (et complet).
• Comment créer un complément dans Excel.
• Comment enregistrer et réutiliser une macro à l'aide du classeur de macros personnelles Excel.