L'utilisation des macros Excel peut accélérer le travail et vous faire gagner beaucoup de temps.
Une façon d'obtenir le code VBA est d'enregistrer la macro et de prendre le code qu'elle génère. Cependant, ce code par enregistreur de macros est souvent plein de code qui n'est pas vraiment nécessaire. L'enregistreur de macros a également quelques limitations.
Il est donc avantageux d'avoir une collection de codes macro VBA utiles que vous pouvez avoir dans votre poche arrière et l'utiliser en cas de besoin.
Bien que l'écriture d'un code de macro Excel VBA puisse prendre un certain temps au départ, une fois que c'est fait, vous pouvez le garder disponible comme référence et l'utiliser chaque fois que vous en avez besoin.
Dans cet article massif, je vais énumérer quelques exemples de macros Excel utiles dont j'ai souvent besoin et que je garde cachés dans mon coffre-fort privé.
Je continuerai à mettre à jour ce tutoriel avec plus d'exemples de macros. Si vous pensez que quelque chose devrait figurer sur la liste, laissez simplement un commentaire.
Vous pouvez ajouter cette page à vos favoris pour référence future.
Maintenant, avant d'entrer dans l'exemple de macro et de vous donner le code VBA, laissez-moi d'abord vous montrer comment utiliser ces exemples de codes.
Utilisation du code des exemples de macros Excel
Voici les étapes à suivre pour utiliser le code de l'un des exemples :
- Ouvrez le classeur dans lequel vous souhaitez utiliser la macro.
- Maintenez la touche ALT et appuyez sur F11. Cela ouvre l'éditeur VB.
- Cliquez avec le bouton droit sur l'un des objets dans l'explorateur de projet.
- Allez dans Insertion -> Module.
- Copiez et collez le code dans la fenêtre de code du module.
Si l'exemple indique que vous devez coller le code dans la fenêtre de code de la feuille de calcul, double-cliquez sur l'objet de la feuille de calcul et copiez-collez le code dans la fenêtre de code.
Une fois que vous avez inséré le code dans un classeur, vous devez l'enregistrer avec une extension .XLSM ou .XLS.
Comment exécuter la macro
Une fois que vous avez copié le code dans l'éditeur VB, voici les étapes pour exécuter la macro :
- Allez dans l'onglet Développeur.
- Cliquez sur Macros.
- Dans la boîte de dialogue Macro, sélectionnez la macro que vous souhaitez exécuter.
- Cliquez sur le bouton Exécuter.
Si vous ne trouvez pas l'onglet développeur dans le ruban, lisez ce didacticiel pour savoir comment l'obtenir.
Tutoriel associé : Différentes manières d'exécuter une macro dans Excel.
Si le code est collé dans la fenêtre de code de la feuille de calcul, vous n'avez pas à vous soucier de l'exécution du code. Il s'exécutera automatiquement lorsque l'action spécifiée se produira.
Passons maintenant aux exemples de macros utiles qui peuvent vous aider à automatiser le travail et à gagner du temps.
Remarque : Vous trouverez de nombreuses occurrences d'une apostrophe (‘) suivie d'une ligne ou deux. Ce sont des commentaires qui sont ignorés lors de l'exécution du code et sont placés en tant que notes pour l'auto/le lecteur.
Si vous trouvez une erreur dans l'article ou le code, soyez génial et faites-le moi savoir.
Exemples de macros Excel
Les exemples de macros ci-dessous sont traités dans cet article :
Afficher toutes les feuilles de calcul en une seule fois
Si vous travaillez dans un classeur contenant plusieurs feuilles masquées, vous devez afficher ces feuilles une par une. Cela peut prendre un certain temps s'il y a beaucoup de feuilles cachées.
Voici le code qui affichera toutes les feuilles de calcul du classeur.
'Ce code affichera toutes les feuilles du classeur Sub UnhideAllWoksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
Le code ci-dessus utilise une boucle VBA (For Each) pour parcourir chaque feuille de calcul du classeur. Il modifie ensuite la propriété visible de la feuille de calcul en visible.
Voici un didacticiel détaillé sur l'utilisation de diverses méthodes pour afficher des feuilles dans Excel.
Masquer toutes les feuilles de calcul sauf la feuille active
Si vous travaillez sur un rapport ou un tableau de bord et que vous souhaitez masquer toute la feuille de calcul à l'exception de celle qui contient le rapport/le tableau de bord, vous pouvez utiliser ce code de macro.
'Cette macro masquera toute la feuille de calcul sauf la feuille active Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub
Trier les feuilles de calcul par ordre alphabétique à l'aide de VBA
Si vous avez un classeur avec de nombreuses feuilles de calcul et que vous souhaitez les trier par ordre alphabétique, ce code de macro peut s'avérer très utile. Cela peut être le cas si vous avez des noms de feuille comme années ou des noms d'employés ou des noms de produits.
'Ce code triera les feuilles de calcul par ordre alphabétique 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
Protégez toutes les feuilles de calcul 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 ce code de macro.
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.
'Ce code protégera 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 souhaitez Pour chaque ws dans les feuilles de calcul ws.Protect password:=password Next ws Fin du sous-marin
Déprotéger toutes les feuilles de calcul en une seule fois
Si vous avez protégé tout ou partie des feuilles de calcul, vous pouvez simplement utiliser une légère modification du code utilisé pour protéger les feuilles pour les déprotéger.
'Ce code protégera 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 souhaitez Pour chaque ws dans les feuilles de calcul ws.Unprotect password:=password Next ws Fin du sous-marin
Notez que le mot de passe doit être le même que celui utilisé pour verrouiller les feuilles de calcul. Si ce n'est pas le cas, vous verrez une erreur.
Afficher toutes les lignes et colonnes
Ce code de macro affichera toutes les lignes et colonnes masquées.
Cela pourrait être très utile si vous obtenez un fichier de quelqu'un d'autre et que vous voulez être sûr qu'il n'y a pas de lignes/colonnes cachées.
'Ce code affichera toutes les lignes et colonnes de la feuille de calcul Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Annuler la fusion de toutes les cellules fusionnées
C'est une pratique courante de fusionner des cellules pour en faire une. Pendant qu'il fait le travail, lorsque les cellules sont fusionnées, vous ne pourrez pas trier les données.
Si vous travaillez avec une feuille de calcul avec des cellules fusionnées, utilisez le code ci-dessous pour annuler la fusion de toutes les cellules fusionnées en une seule fois.
'Ce code annulera la fusion de toutes les cellules fusionnées Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub
Notez qu'au lieu de Fusionner et centrer, je recommande d'utiliser l'option Centrer sur la sélection.
Enregistrer le classeur avec TimeStamp dans son nom
Beaucoup de temps, vous devrez peut-être créer des versions de votre travail. Ceux-ci sont très utiles dans les longs projets où vous travaillez avec un fichier au fil du temps.
Une bonne pratique consiste à enregistrer le fichier avec des horodatages.
L'utilisation d'horodatages vous permettra de revenir à un certain fichier pour voir quelles modifications ont été apportées ou quelles données ont été utilisées.
Voici le code qui enregistrera automatiquement le classeur dans le dossier spécifié et ajoutera un horodatage chaque fois qu'il sera enregistré.
'Ce code enregistrera le fichier avec un horodatage dans son nom Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & horodatage End Sub
Vous devez spécifier l'emplacement du dossier et le nom du fichier.
Dans le code ci-dessus, "C:UsersUsernameDesktop est l'emplacement du dossier que j'ai utilisé. Vous devez spécifier l'emplacement du dossier dans lequel vous souhaitez enregistrer le fichier. De plus, j'ai utilisé un nom générique "WorkbookName" comme préfixe de nom de fichier. Vous pouvez spécifier quelque chose en rapport avec votre projet ou votre entreprise.
Enregistrez chaque feuille de calcul en tant que PDF séparé
Si vous travaillez avec des données pour différentes années, divisions ou produits, vous devrez peut-être enregistrer différentes feuilles de calcul sous forme de fichiers PDF.
Bien que cela puisse être un processus fastidieux s'il est effectué manuellement, VBA peut vraiment l'accélérer.
Voici un code VBA qui enregistrera chaque feuille de calcul dans un fichier PDF distinct.
'Ce code enregistrera chaque feuille de travail dans un sous-PDF séparé SaveWorkshetAsPDF() Dim ws As Worksheet pour chaque ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Suivant ws End Sub
Dans le code ci-dessus, j'ai spécifié l'adresse de l'emplacement du dossier dans lequel je souhaite enregistrer les fichiers PDF. De plus, chaque PDF aura le même nom que celui de la feuille de calcul. Vous devrez modifier l'emplacement de ce dossier (à moins que votre nom ne soit également Sumit et que vous l'enregistriez dans un dossier de test sur le bureau).
Notez que ce code fonctionne uniquement pour les feuilles de calcul (et non pour les feuilles de graphique).
Enregistrez chaque feuille de calcul en tant que PDF séparé
Voici le code qui enregistrera l'intégralité de votre classeur au format PDF dans le dossier spécifié.
'Ce code enregistrera l'intégralité du classeur au format PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Vous devrez changer l'emplacement du dossier pour utiliser ce code.
Convertir toutes les formules en valeurs
Utilisez ce code lorsque vous avez une feuille de calcul qui contient beaucoup de formules et que vous souhaitez convertir ces formules en valeurs.
'Ce code convertira toutes les formules en valeurs Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub
Ce code identifie automatiquement les cellules utilisées et les convertit en valeurs.
Protéger/verrouiller les cellules avec des formules
Vous voudrez peut-être verrouiller des cellules avec des formules lorsque vous avez beaucoup de calculs et que vous ne voulez pas les supprimer ou les modifier accidentellement.
Voici le code qui verrouillera toutes les cellules qui ont des formules, alors que toutes les autres cellules ne sont pas verrouillées.
'Ce code de macro verrouillera toutes les cellules avec des formules Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub
Tutoriel connexe : Comment verrouiller des cellules dans Excel.
Protéger toutes les feuilles de calcul du classeur
Utilisez le code ci-dessous pour protéger toutes les feuilles de calcul d'un classeur en une seule fois.
'Ce code protégera toutes les feuilles du classeur Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Next ws End Sub
Ce code parcourra toutes les feuilles de calcul une par une et les protégera.
Si vous souhaitez déprotéger toutes les feuilles de calcul, utilisez ws.Unprotect au lieu de ws.Protect dans le code.
Insérer une ligne après chaque autre ligne dans la sélection
Utilisez ce code lorsque vous souhaitez insérer une ligne vide après chaque ligne de la plage sélectionnée.
'Ce code insère une ligne après chaque ligne dans la sélection Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Insérer ActiveCell.Offset(2, 0).Select Next i End Sub
De même, vous pouvez modifier ce code pour insérer une colonne vide après chaque colonne de la plage sélectionnée.
Insérer automatiquement la date et l'horodatage dans la cellule adjacente
Un horodatage est quelque chose que vous utilisez lorsque vous souhaitez suivre des activités.
Par exemple, vous souhaiterez peut-être suivre des activités telles que la date à laquelle une dépense particulière a été engagée, l'heure à laquelle la facture de vente a été créée, la date de saisie des données dans une cellule, la dernière mise à jour du rapport, etc.
Utilisez ce code pour insérer une date et une heure dans la cellule adjacente lorsqu'une entrée est effectuée ou que le contenu existant est modifié.
'Ce code insère un horodatage dans la cellule adjacente Private Sub Worksheet_Change (ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value "" Then Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "jj-mm-aaaa hh:mm:ss") Application.EnableEvents = True End If Handler : End Sub
Notez que vous devez insérer ce code dans la fenêtre de code de la feuille de calcul (et non dans la fenêtre de code du module comme nous l'avons fait dans d'autres exemples de macros Excel jusqu'à présent). Pour ce faire, dans l'éditeur VB, double-cliquez sur le nom de la feuille sur laquelle vous souhaitez cette fonctionnalité. Ensuite, copiez et collez ce code dans la fenêtre de code de cette feuille.
En outre, ce code est conçu pour fonctionner lorsque la saisie des données est effectuée dans la colonne A (notez que le code a la ligne Target.Column = 1). Vous pouvez modifier cela en conséquence.
Mettez en surbrillance les lignes alternatives dans la sélection
La mise en évidence de lignes alternatives peut augmenter considérablement la lisibilité de vos données. Cela peut être utile lorsque vous devez faire une impression et parcourir les données.
Voici un code qui mettra instantanément en évidence des lignes alternatives dans la sélection.
'Ce code mettrait en évidence des lignes alternatives dans la sélection Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End Si suivant Myrow End Sub
Notez que j'ai spécifié la couleur comme vbCyan dans le code. Vous pouvez également spécifier d'autres couleurs (telles que vbRed, vbGreen, vbBlue).
Mettez en surbrillance les cellules avec des mots mal orthographiés
Excel n'a pas de vérification orthographique comme dans Word ou PowerPoint. Bien que vous puissiez exécuter la vérification orthographique en appuyant sur la touche F7, il n'y a pas de repère visuel en cas de faute d'orthographe.
Utilisez ce code pour mettre instantanément en surbrillance toutes les cellules contenant une faute d'orthographe.
'Ce code mettra en évidence les cellules qui ont des mots mal orthographiés Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl End Sub
Notez que les cellules mises en surbrillance sont celles qui contiennent du texte qu'Excel considère comme une faute d'orthographe. Dans de nombreux cas, cela mettrait également en évidence des noms ou des termes de marque qu'il ne comprend pas.
Actualiser tous les tableaux croisés dynamiques du classeur
Si vous avez plusieurs tableaux croisés dynamiques dans le classeur, vous pouvez utiliser ce code pour actualiser tous ces tableaux croisés dynamiques à la fois.
'Ce code actualisera tout le tableau croisé dynamique dans le classeur Sub RefreshAllPivotTables() Dim PT As PivotTable For Each PT In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub
Vous pouvez en savoir plus sur l'actualisation des tableaux croisés dynamiques ici.
Changer la casse des cellules sélectionnées en majuscules
Bien qu'Excel dispose des formules pour modifier la casse du texte, il vous oblige à le faire dans un autre ensemble de cellules.
Utilisez ce code pour changer instantanément la casse du texte dans le texte sélectionné.
'Ce code changera la sélection en majuscule Sub ChangeCase() Dim Rng As Range pour chaque Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub
Notez que dans ce cas, j'ai utilisé UCase pour rendre le texte en majuscule. Vous pouvez utiliser LCase pour les minuscules.
Mettez en surbrillance toutes les cellules avec des commentaires
Utilisez le code ci-dessous pour mettre en surbrillance toutes les cellules contenant des commentaires.
'Ce code mettra en évidence les cellules qui ont des commentaires' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub
Dans ce cas, j'ai utilisé vbBlue pour donner une couleur bleue aux cellules. Vous pouvez changer cela en d'autres couleurs si vous le souhaitez.
Mettez en surbrillance les cellules vides avec VBA
Bien que vous puissiez mettre en surbrillance une cellule vide avec une mise en forme conditionnelle ou en utilisant la boîte de dialogue Aller à spécial, si vous devez le faire assez souvent, il est préférable d'utiliser une macro.
Une fois créée, vous pouvez avoir cette macro dans la barre d'outils d'accès rapide ou l'enregistrer dans votre classeur de macros personnel.
Voici le code de la macro VBA :
'Ce code mettra en évidence toutes les cellules vides de l'ensemble de données Sub HighlightBlankCells() Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub
Dans ce code, j'ai spécifié les cellules vides à mettre en surbrillance en rouge. Vous pouvez choisir d'autres couleurs telles que le bleu, le jaune, le cyan, etc.
Comment trier les données par colonne unique
Vous pouvez utiliser le code ci-dessous pour trier les données selon la colonne spécifiée.
Sub SortDataHeader() Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes End Sub
Notez que j'ai créé une plage nommée avec le nom "DataRange" et que je l'ai utilisée à la place des références de cellule.
Trois paramètres clés sont également utilisés ici :
- Key1 - C'est le sur lequel vous souhaitez trier l'ensemble de données. Dans l'exemple de code ci-dessus, les données seront triées en fonction des valeurs de la colonne A.
- Ordre - Ici, vous devez spécifier si vous souhaitez trier les données par ordre croissant ou décroissant.
- En-tête - Ici, vous devez spécifier si vos données ont des en-têtes ou non.
En savoir plus sur la façon de trier les données dans Excel à l'aide de VBA.
Comment trier les données par plusieurs colonnes
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :
Vous trouverez ci-dessous le code qui triera les données en fonction de plusieurs colonnes :
Sub SortMultipleColumns() avec ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlYes .Apply End With End Sub
Notez qu'ici, j'ai spécifié de trier d'abord en fonction de la colonne A, puis en fonction de la colonne B.
La sortie serait quelque chose comme indiqué ci-dessous:
Comment obtenir uniquement la partie numérique d'une chaîne dans Excel
Si vous souhaitez extraire uniquement la partie numérique ou uniquement la partie texte d'une chaîne, vous pouvez créer une fonction personnalisée dans VBA.
Vous pouvez ensuite utiliser cette fonction VBA dans la feuille de calcul (tout comme les fonctions Excel normales) et elle extraira uniquement la partie numérique ou texte de la chaîne.
Quelque chose comme indiqué ci-dessous :
Vous trouverez ci-dessous le code VBA qui créera une fonction pour extraire la partie numérique d'une chaîne :
'Ce code VBA créera une fonction pour obtenir la partie numérique d'une chaîne Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function
Vous devez placer le code dans un module, puis vous pouvez utiliser la fonction = GetNumeric dans la feuille de calcul.
Cette fonction ne prendra qu'un seul argument, qui est la référence de cellule de la cellule à partir de laquelle vous souhaitez obtenir la partie numérique.
De même, vous trouverez ci-dessous la fonction qui ne vous fournira que la partie texte d'une chaîne dans Excel :
'Ce code VBA créera une fonction pour obtenir la partie texte d'une chaîne Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not 1))) Then Result = Result & Mid(CellRef, i, 1) Next i GetText = Result End Function
Voici donc quelques-uns des codes de macro Excel utiles que vous pouvez utiliser dans votre travail quotidien pour automatiser des tâches et être beaucoup plus productif.
