Dans cet article, je vais vous montrer certains des codes Excel VBA les plus étonnants que vous pouvez utiliser pour optimiser votre travail. VBA est un langage de programmation qui peut être utilisé pour étendre les capacités de MS Excel et d’autres applications MS Office. Il est extrêmement utile pour les utilisateurs de MS Excel, car il peut être utilisé pour automatiser votre travail et améliorer considérablement votre efficacité. Cet article vous présentera VBA et vous montrera certains des codes VBA les plus utiles et prêts à l’emploi qui existent. Vous pouvez utiliser ces exemples de macro pour créer vos propres scripts qui correspondent à vos propres besoins.

Vous n’avez pas besoin d’expérience en programmation pour tirer parti des informations contenues dans cet article, mais vous devez avoir des connaissances de base sur Excel. Si vous êtes un utilisateur débutant, je vous recommande de lire l’article 20 Formules Excel Que vous devriez Commencer à Utiliser Maintenant pour en savoir plus sur les fonctionnalités de base d’Excel.

J’ai préparé pour vous un certain nombre d’exemples de macro Excel VBA prêts à l’emploi avec de grandes fonctionnalités que vous pouvez utiliser pour optimiser votre travail. Pour les utiliser, vous devez les « installer » dans votre fichier Excel. Le paragraphe suivant traite de l’installation de macro Excel. Ignorez cette partie si vous êtes déjà familier avec cela.

Comment installer une macro

Dans Excel, appuyez sur la combinaison de touches alt + F11. Cela vous mènera à l’éditeur VBA dans MS Excel. Ensuite, faites un clic droit sur le dossier Objets Microsoft Excel à gauche et sélectionnez le module Insert=>. C’est l’endroit où les macros sont stockées. Pour utiliser la macro, vous devez enregistrer le document Excel en tant que macro-activé. Dans l’onglet fichier=> enregistrer sous, choisissez Enregistrer sous classeur compatible avec les macros (le.extension xlsm) Maintenant, il est temps d’écrire votre première macro!

1. Copiez les données d’un fichier à un autre.

Macro très utile, car elle montre comment copier une plage de données à partir de vba et comment créer et nommer un nouveau classeur. Vous pouvez facilement le mettre à niveau pour répondre à vos propres besoins:

Sub CopyFiletoAnotherWorkbook()' Copiez les fiches techniques ("Exemple 1").Gamme ("B4: C15").Copier " Créer un nouveau classeur Classeurs.Ajoutez 'Coller la feuille de données actives.Coller ' Désactiver l'application alertes d'application.DisplayAlerts=False ' Enregistre le nouveau fichier. Modifiez le nom du répertoire. Livre de travail actif.Nom du fichier SaveAs : ="C:\Temp\MyNewBook .xlsx " ' Réactivez les alertes d'application sur l'application.DisplayAlerts =TrueEnd Sub

2. Afficher les lignes cachées

De temps en temps, les gros fichiers Excel contiennent des lignes cachées pour une meilleure clarté. Voici une macro qui affichera toutes les lignes d’une feuille de calcul active :

Sous Colonnes ShowHiddenRows().Colonne entière.Hidden = Fausses lignes.Tout le monde.Hidden=FalseEnd Sub

3. Supprimer des lignes et des colonnes vides

Les lignes vides dans Excel posent un problème de traitement des données. Voici comment s’en débarrasser:

Sub DeleteEmptyRowsAndColumns() ' Déclare vos variables. Dim MyRange Comme Plage Dim iCounter Aussi Longtemps ' Définit la plage cible. Définir MyRange= Feuille active.UsedRange ' Commence à faire une boucle inverse dans la plage de lignes. Pour iCounter = MyRange.Rangée.Comptez jusqu'À 1 Étape -1' Si la ligne entière est vide, supprimez-la. Si Application.CountA(Lignes (iCounter).EntireRow) = 0 Puis Lignes (iCounter).Delete 'Remove comment to See which are the empty rows'MsgBox"row"&iCounter &"is empty" End If 'Increment the counter down Next iCounter' Step 6: Start reverse looping through the range of Columns. Pour iCounter = MyRange.Colonne.Comptez jusqu'À 1 Étape -1 ' Étape 7: Si la colonne entière est vide, supprimez-la. Si Application.CountA (Colonnes (iCounter).EntireColumn) = 0 Puis Colonnes (iCounter).Supprimer la fin Si ' Étape 8: Incrémentez le compteur vers le bas Suivant iCounter End Sub

4. Trouvez une cellule vide

 Sous FindEmptyCell()ActiveCell.Décalage (1, 0).Sélectionnez Faire Alors Qu'Il N'Est Pas Vide (Cellule active) Cellule Active.Décalage (1, 0).Sélectionnez LoopEnd Sub

5. Remplacez les cellules vides par une valeur.

Comme mentionné précédemment, les cellules vides interfèrent avec le traitement des données et la création de tableaux croisés dynamiques. Voici un code qui remplace toutes les cellules vides par 0. Cette macro a une très grande application car vous pouvez l’utiliser pour trouver et remplacer les résultats N / A, ainsi que d’autres caractères tels que des points, des virgules ou des valeurs en double:

 Sub FindAndReplace() 'Déclarez vos variables Dim MyRange Comme plage Dim MyCell Comme plage' Enregistrez le Classeur avant de changer de cellule? Sélectionnez Case MsgBox ("Impossible d'annuler cette action. "&_" Enregistrez d'abord le classeur?", vbYesNoCancel) Le Cas Est = vbYes ThisWorkbook.Enregistrer la casse Est = vbCancel Exit Sub End Sélectionnez ' Définir la plage cible. Set MyRange=Selection ' Commencez à parcourir la plage en boucle. Pour chaque cellule de MyRange, Vérifiez la longueur nulle, puis ajoutez 0. Si Len (MyCell.Valeur) = 0 Alors MyCell = 0 Fin Si ' Obtenez la cellule suivante dans la plage Suivante MyCellEnd Sub

6. Trier les nombres

La macro suivante trie dans l’ordre croissant tous les nombres de la colonne de la cellule active. Double-cliquez simplement sur une cellule d’une colonne que vous souhaitez trier.NB: Vous devez mettre le code dans la feuille 1 et non dans un module pour fonctionner:

Sous-feuille de calcul Privée_beforedoubleclick(Cible ByVal en Tant que Plage, Annuler En Tant Que Booléen) ' Déclarez vos Variables Dim LastRow Aussi Longtemps' Trouvez la dernière ligne LastRow= Cells(Lignes.Comte, 1).Fin (xlUp).Tri des lignes ascendantes sur les lignes de colonne double-cliquées ("6:"&LastRow).Trier _ Key1: = Cellules(6, Cellule active.Colonne), _Order1: = xlAscendingEnd Sub

7. Supprimer les espaces vides

Parfois, les données du classeur contiennent des espaces supplémentaires (espaces) qui peuvent interférer avec l’analyse des données et corrompre les formules. Voici une macro qui supprimera tous les espaces d’une plage de cellules présélectionnée:

Sub TrimTheSpaces() 'Déclarez vos variables Dim MyRange Comme Plage Dim MyCell Comme plage' Enregistrez le classeur avant de changer de cellule Sélectionnez la casse MsgBox ("Impossible d'annuler cette action. "&_" Enregistrez d'abord le classeur?", vbYesNoCancel) Le Cas Est = vbYes ThisWorkbook.Enregistrer la casse Est = vbCancel Exit Sub End Sélectionnez ' Définir la plage cible. Set MyRange=Selection ' Commencez à parcourir la plage en boucle. Pour Chaque Cellule De MyRange, Coupez les Espaces. Si ce n'est pas IsEmpty(MyCell) Alors MyCell = Trim(MyCell) End If ' Obtenez la cellule suivante dans la plage Suivante MyCellEnd Sub

8. Mettez en surbrillance les valeurs doublées

Parfois, il y a des valeurs en double dans les plusieurs colonnes que nous aimerions éclairer. Voici une macro qui fait exactement cela:

Sub HighlightDuplicates() ' Déclarez vos variables Dim MyRange Comme Plage Dim MyCell Comme Plage' Définissez la plage cible. Set MyRange=Selection ' Commencez à parcourir la plage en boucle. Pour chaque cellule De MyRange, Assurez-vous que la cellule a un formatage de texte. Si fonction de feuille de calcul.CountIf (MyRange, MyCell.Valeur) >1 Puis MyCell.Intérieur.ColorIndex = 36 End If ' Récupère la cellule suivante dans la plage Suivante MyCellEnd Sub

9. Mettez en surbrillance les dix premières valeurs

Ce code mettra en surbrillance les dix premières valeurs d’une sélection de cellules :

 Sélection Sub TopTen().FormatConditions.Sélection AddTop10.FormatConditions (Sélection.FormatConditions.Compter).SetFirstPriority Avec Sélection.FormatConditions (1).TopBottom = xlTop10Top ' Modifiez le rang ici pour mettre en évidence un nombre différent de valeurs.Rang = 10.Pourcentage = Faux Fin Avec Avec Sélection.FormatConditions (1).Police .Couleur = -16752384.TintAndShade = 0 Fin Avec Avec Sélection.FormatConditions (1).Intérieur .PatternColorIndex = xlautomatique.Couleur = 13561798.TintAndShade = 0 Se Termine Par La Sélection.FormatConditions (1).StopIfTrue=FalseEnd Sub

Vous pouvez facilement modifier le code pour mettre en évidence différents nombres de valeurs.

10. Lorsque vous exécutez ce code, une fenêtre vous invite à mettre en surbrillance une valeur supérieure à values

. Il vous demandera la valeur que vous souhaitez comparer les cellules que vous avez sélectionnées.

Sous HighlightGreaterThanValues() Dim i Comme Entier i = Sélection InputBox("Entrée Supérieure À La Valeur", "Entrée De Valeur").FormatConditions.Delete ' Change l'opérateur en xlLower pour mettre en surbrillance la sélection inférieure aux valeurs.FormatConditions.Ajouter le type: = xlCellValue, Opérateur: = xlGreater, Formula1: = i Sélection.FormatConditions (Sélection.FormatConditions.Compter).SetFirstPriority Avec Sélection.FormatConditions (1).Police.Couleur = RVB (0, 0, 0).Intérieur.Color= RGB(31, 218, 154) End WithEnd Sub

Vous pouvez également modifier ce code pour mettre en évidence les valeurs inférieures.

Une macro simple qui met en évidence toutes les cellules contenant des commentaires :

Sélection Sub HighlightCommentCells().Cellules spéciales (xlCellTypeComments).Sélectionnez Sélection.Style="Note" End Sub

12. Mettez en surbrillance les cellules avec des mots mal orthographiés

Ceci est extrêmement utile lorsque vous travaillez avec des fonctions qui prennent des chaînes, cependant, quelqu’un a entré la chaîne avec une erreur et vos formules ne fonctionnent pas. Voici comment résoudre ce problème :

Sub ColorMispelledCells() Pour Chaque cl Dans ActiveSheet.UsedRange Si Ce N'Est Pas L'Application.CheckSpelling (Mot:= cl.Texte) Puis _cl.Intérieur.ColorIndex =28 Sous-couche suivante

13. Créer un tableau croisé dynamique

Voici comment créer un tableau croisé dynamique à partir de MS Excel (version 2007). Particulièrement utile, lorsque vous créez un rapport personnalisé tous les jours. Vous pouvez optimiser la création du tableau croisé dynamique de la manière suivante :

Sub Pivotableforexcel2007() Dim SourceRange En tant que Plage Définie SourceRange=Sheets("Sheet1").Gamme ("A3:N86") Livre de travail actif.Caches pivotantes.Créer(_ Type de source: =xlDatabase, _SourceData:=SourceRange, _Version:=xlPivotTableVersion12).CreatePivotTable _TableDestination: ="", _TableName:="", _DefaultVersion:=xlPivotTableVersion12End Sub

14. Joignez le classeur actif dans un e-mail

Mon code VBA préféré. Il vous permet de joindre et d’envoyer le fichier sur lequel vous travaillez avec une adresse e-mail prédéfinie, un titre de message et un corps de message! Vous devez d’abord définir la référence à Microsoft Outlook (dans votre édition VBA, cliquez sur tools=> références et choisissez Microsoft Outlook).

Sous SendFIleAsAttachment() 'Déclarez vos variables' Définit la référence à la bibliothèque d'objets Microsoft Outlook Dim OLApp Comme Outlook.Application Dim OLMail As Object ' Ouvrir Outlook démarrer un nouvel élément de messagerie Set OLApp = New Outlook.Ensemble d'applications OLMail=OLApp.Créer un élément (0) OLApp.Session.Connectez-vous ' Créez votre élément de messagerie et envoyez avec OLMail.To ="[email protected] ; [email protected] ".CC = "".BCC = "".Subject = "Ceci est la ligne d'objet".Body= "Bonjour".Attachement.Ajouter ActiveWorkbook.Nom complet.Afficher ' Changer pour.Envoyer à envoyer sans passer en revue Se Termine Par 'Nettoyage de la mémoire Set OLMail = Nothing Set OLApp=NothingEnd Sub

15. Envoyer tous les graphiques Excel à une présentation PowerPoint

Une macro très pratique qui vous permet d’ajouter tous vos graphiques Excel dans votre présentation Powerpoint en un seul clic:

 Sous SendExcelFiguresToPowerPoint() 'Définir la référence à la bibliothèque d'objets Microsoft Powerpoint' Déclarez vos variables Dim PP comme PowerPoint.Application Dim PPPres Comme PowerPoint.Présentation Dim PPSlide Comme PowerPoint.Faites glisser Dim i Comme Entier ' Vérifier les graphiques; quittez si aucun graphique n'existe de feuilles ("Données de diapositives").Sélectionnez Si Feuille active.Objets graphiques.Count < 1 Puis MsgBox "Aucun graphique existant dans la feuille active" Quitte la Sous-fin Si 'Ouvrez PowerPoint et créez une nouvelle présentation Set PP = New PowerPoint.Ensemble d'applications PPPres = PP.Présentation.Ajouter PP.Visible = True ' Démarre la boucle en fonction du nombre de graphiques Pour i = 1 À ActiveSheet.Objets graphiques.Count ' Copiez le graphique sous forme de feuille d'activité d'image.Objets graphiques (i).Graphique.CopyPicture _ Taille: = xlScreen, Format: = Application xlPicture.Attendre (Maintenant + TimeValue ("0:00:1")) ' Comptez les diapositives et ajoutez une nouvelle diapositive comme prochain numéro de diapositive disponible ppSlideCount = PPPres.Diapositive.Ensemble de comptage PPSlide = PPPres.Diapositive.Ajouter (SlideCount + 1, ppLayoutBlank) PPSlide.Sélectionnez "Coller l'image et ajuster sa position; Passez au graphique suivant PPSlide.Formes.Coller.Sélectionnez PP.Fenêtre active.Sélection.La forme.Alignez les centres de signalisation Msoalign, True PP.Fenêtre active.Sélection.La forme.Align msoAlignMiddles, True Ensuite je ' Nettoyage de la mémoire Set PPSlide = Rien Set PPPres = Rien Set PP =NothingEnd Sub

16. Envoyer un tableau Excel dans MS Word

Les tableaux Excel sont généralement placés dans des documents texte. Voici un moyen automatisé d’exporter votre table Excel vers MS Word:

Sous ExcelTableInWord() 'Définir la référence à la bibliothèque d'objets Microsoft Word' Déclarez vos variables Dim MyRange comme Excel.Gamme Dim wd Comme Mot.Application Dim wdDoc Comme Mot.Documentez Dim WdRange Sous Forme De Mot.Range' Copiez les feuilles de plage définies ("Tableau des revenus").Gamme ("B4: F10").Cop ' Ouvre le jeu de documents Word cible wd= Nouveau Mot.Ensemble d'applications wdDoc = wd.Document.Ouvrir _ (Ce livre de travail.Chemin &&"Table de pâtes.docx") wd.Visible= True ' Définir le focus sur le signet cible Définir WdRange=wdDoc.Signets ("Datatableici").Range ' Supprimez l'ancienne table et collez la nouvelle En cas d'erreur Reprendre la prochaine WdRange.Tableaux (1).Supprimer WdRange.Coller 'coller dans le tableau' Ajustez les largeurs de colonne WdRange.Tableaux (1).Colonne.SetWidth _(MyRange.Largeur / MyRange.Colonne.Count), wdAdjustSameWidth ' Réinsère le signet wdDoc.Signet.Ajoutez "DataTableHere", WdRange 'Nettoyage de la mémoire Set wd= Nothing Set wdDoc= Nothing Set WdRange=NothingEnd Sub

17. Extraire un mot spécifique d’une cellule

Nous pouvons utiliser des formules si nous voulons extraire un certain nombre de symboles. Mais que se passe-t-il si nous voulons extraire seulement le deuxième mot d’une phrase ou d’une gamme de mots dans une cellule? Pour ce faire, nous pouvons créer une fonction Excel personnalisée avec VBA. C’est l’une des fonctionnalités VBA les plus importantes, car elle vous permet de créer vos propres fonctions inexistantes dans MS Excel. Continuons et créons deux fonctions: findword() et findwordrev(). Voici le code vba pour cela:

Fonction FindWord (Source Sous Forme De Chaîne, Position Sous Forme D'Entier) Sous Forme De Chaîne En Cas D'Erreur Reprise Suivant FindWord = Split(Fonction De Feuille De Calcul.Trim(Source), "") (Position-1) En Cas D'Erreur, Allez À 0End FunctionFunction FindWordRev(Source Sous Forme De Chaîne, Position Sous Forme D'Entier) Sous Forme De Chaîne Dim Arr() Sous Forme De Chaîne Arr = VBA.Split (fonction de feuille de calcul.Trim(Source), "") En Cas d'erreur Resume Next FindWordRev =Arr(UBound(Arr)-Position +1) En cas d'erreur GoTo 0End Function

Très bien, nous avons créé deux fonctions Excel cstom. Maintenant, essayez de les utiliser dans Excel. La fonction =FindWordRev(A1,1) prend le dernier mot de la cellule A1. La fonction =FindWord(A1,3) prend le troisième mot de la cellule A1, etc.

18. Protégez votre classeur

Parfois, nous voulons protéger les données de notre fichier afin que nous seuls puissions les modifier. Voici comment faire cela avec VBA:

Sub ProtectSheets() ' Déclarez vos variables Dim ws En Tant Que Feuille de calcul' Commencez à parcourir toutes les feuilles de calcul Pour Chaque ws Dans ActiveWorkbook.Protection des feuilles de calcul et boucle vers la feuille de calcul suivante ws.Protect Password:="1234" Suivant wsEnd Sub

Félicitations! Puisque vous lisez toujours ceci, vous êtes vraiment désireux d’apprendre VBA. Comme vous l’avez déjà vu par vous-même, le langage de programmation VBA est extrêmement utile et peut nous faire gagner beaucoup de temps. J’espère que vous avez trouvé ces informations utiles et que vous les utiliserez pour devenir un maître en MS Excel, VBA et les logiciels informatiques en général.