Pour automatiser les tâches répétitives, vous pouvez enregistrer une macro à l'aide de Macro Recorder dans Microsoft Excel. Imaginez que vous ayez des dates dans des formats aléatoires et que vous souhaitiez appliquer un format à toutes. Cela peut être fait à l'aide d'une macro. Vous pouvez enregistrer une macro en utilisant le format souhaité et lire la macro si nécessaire.

Lorsque vous enregistrez une macro, toutes les actions nécessaires sont enregistrées sous forme de code Visual Basic pour Applications (VBA). Ces actions peuvent inclure la saisie de texte ou de chiffres, la sélection de cellules ou de commandes sur le ruban ou le menu, le formatage de cellules, de lignes ou de colonnes et même l'importation de données à partir d'une source externe telle que Microsoft Access. Visual Basic pour Applications (VBA) fait partie du langage de programmation Visual Basic. Il est disponible dans la plupart des applications Office. Bien que VBA vous permette d'automatiser les processus au sein et entre les applications Office, vous n'avez pas besoin d'être capable de programmer ou de connaître le langage VBA car l'enregistreur de macros fait tout ce dont vous avez besoin.

Il est important de savoir que lorsque vous enregistrez une macro, presque tout ce que vous faites est enregistré. Ainsi, si vous faites une erreur, comme appuyer sur le mauvais bouton, l'enregistreur de macros enregistrera cette action. Dans ce cas, vous pouvez réécrire la séquence entière ou modifier le code VBA. Par conséquent, avant d’enregistrer le processus, vous devez bien le travailler. Plus vous enregistrez la séquence avec précision, plus la macro fonctionnera efficacement.

Développeur, qui est masqué par défaut, vous devez donc d'abord l'activer. Pour plus d’informations, consultez Afficher l’onglet Développeur.

Enregistrer une macro

Sur l'onglet Développeur Cliquez sur Macro pour afficher les macros associées au classeur. Alternativement, vous pouvez appuyer sur les touches ALT+F8. Cela ouvrira une boîte de dialogue Macro.

Attention:

		Découvrez les paramètres de sécurité des macros et leur signification.
	Les macros peuvent être exécutées de différentes manières, par exemple via un raccourci clavier, un graphique, une barre d'outils d'accès rapide, un bouton ou même lors de l'ouverture d'un classeur.
	Vous pouvez utiliser Visual Basic Editor pour modifier les macros attachées à un classeur.
	attribuer une macro. Sur le terrain Attribuer une macro
	Découvrez comment activer ou désactiver les macros dans les fichiers Office.
	Appuyez sur les touches ALT+F11.

Travailler avec du code enregistré dans Visual Basic Editor (VBE)

Avec Visual Basic Editor (VBE), vous pouvez ajouter vos propres variables, structures de contrôle et autres éléments au code enregistré que l'enregistreur de macros ne prend pas en charge. Étant donné que l'enregistreur de macros capture presque toutes les étapes effectuées pendant l'enregistrement, vous devrez peut-être également supprimer le code inutile. La révision du code enregistré est un excellent moyen d’apprendre la programmation VBA ou de perfectionner vos compétences.

Un exemple de modification du code enregistré peut être trouvé dans l'article Premiers pas avec VBA dans Excel.

Enregistrer une macro

Avant d'enregistrer des macros, il est utile de connaître les points suivants :

Une macro écrite pour fonctionner avec une plage Excel ne s'exécutera que sur les cellules de cette plage. Par conséquent, si vous ajoutez une nouvelle ligne à la plage, la macro ne s’y appliquera pas.

Si vous devez enregistrer une longue séquence de tâches, nous vous recommandons d'utiliser plusieurs macros plus petites.

Une macro peut également contenir des tâches non Excel. Le processus de macro peut s'étendre à d'autres applications Office et à d'autres programmes prenant en charge Visual Basic pour Applications (VBA). Par exemple, vous pouvez enregistrer une macro qui met d'abord à jour un tableau dans Excel, puis ouvre Outlook pour l'envoyer par courrier électronique.

Les macros et les outils VBA se trouvent dans l'onglet Développeur, qui est masqué par défaut, vous devez donc d'abord l'activer.

Sélectionner Exceller > Possibilités> Ruban et panneau.

Pour enregistrer une macro, suivez les instructions ci-dessous.

Travailler avec des macros enregistrées dans Excel

Sur l'onglet Développeur Cliquez sur Macro pour afficher les macros associées au classeur. Cela ouvrira une boîte de dialogue Macro.

Note: Les macros ne peuvent pas être annulées. Avant d'exécuter une macro enregistrée pour la première fois, enregistrez ou créez une copie du classeur pour éviter les modifications indésirables. Si vous n'êtes pas satisfait des résultats de la macro, vous pouvez fermer le classeur sans l'enregistrer.

Voici plus d’informations sur l’utilisation des macros dans Excel.

		Découvrez comment activer ou désactiver les macros dans Excel pour Mac.
	Pour gagner du temps sur les tâches fréquemment répétées, vous pouvez enregistrer la séquence d'actions correspondante sous forme de macro. Apprenez à créer et exécuter des macros.
	Si un classeur contient une macro VBA que vous souhaitez utiliser ailleurs, vous pouvez copier le module dans un autre classeur à l'aide de Microsoft Visual Basic Editor.
Affectation d'une macro à un objet, une forme ou un élément graphique	Dans une feuille de calcul, cliquez avec le bouton droit sur l'objet, l'image, la forme ou l'élément auquel vous souhaitez attribuer une macro existante, puis sélectionnez attribuer une macro. Sur le terrain Attribuer une macro sélectionnez la macro que vous souhaitez attribuer.
	Vous pouvez attribuer une macro à une icône et l'ajouter à votre barre d'outils d'accès rapide ou à votre ruban.
	Vous pouvez attribuer des macros aux formulaires et aux contrôles ActiveX sur une feuille de calcul.
Ouverture de l'éditeur Visual Basic	Sur l'onglet Développeur Cliquez sur Visual Basic ou sélectionnez Service > Macro > Éditeur Visual Basic.
	Découvrez comment trouver de l’aide pour les éléments Visual Basic.

Informations Complémentaires

Vous pouvez toujours poser une question à la communauté Excel Tech, demander de l'aide à la communauté Answers ou suggérer une nouvelle fonctionnalité ou une amélioration du site Web.

Excel possède une capacité puissante, mais rarement utilisée, de créer des séquences d’actions automatisées à l’aide de macros. Une macro est une solution idéale si vous avez affaire au même type de tâche répétée plusieurs fois. Par exemple, traiter des données ou formater des documents à l'aide d'un modèle standardisé. Dans le même temps, vous n'avez pas besoin de connaître les langages de programmation.

Êtes-vous déjà curieux de savoir ce qu’est une macro et comment elle fonctionne ? Alors allez-y avec audace - nous passerons ensuite en revue tout le processus de création d'une macro étape par étape avec vous.

Qu'est-ce qu'une macro ?

Une macro dans Microsoft Office (oui, cette fonctionnalité fonctionne de la même manière dans de nombreuses applications de la suite Microsoft Office) est un code de programme dans un langage de programmation (VBA) enregistré dans un document. Pour que ce soit plus clair, un document Microsoft Office peut être comparé à une page HTML, alors une macro est un analogue de Javascript. Ce que Javascript peut faire avec les données HTML d'une page Web est très similaire à ce qu'une macro peut faire avec les données d'un document Microsoft Office.

Les macros peuvent effectuer presque toutes les actions que vous pourriez souhaiter dans un document. En voici quelques-uns (une toute petite partie) :

• Appliquez des styles et une mise en forme.
• Effectuez diverses opérations avec des données numériques et textuelles.
• Utiliser des sources de données externes (fichiers de base de données, documents texte, etc.)
• Créez un nouveau document.
• Effectuez toutes les étapes ci-dessus dans n’importe quelle combinaison.

Créer une macro - Exemple pratique

Par exemple, prenons le fichier le plus ordinaire CSV. Il s'agit d'un simple tableau 10x20 rempli de nombres de 0 à 100 avec des en-têtes pour les colonnes et les lignes. Notre tâche consiste à transformer cet ensemble de données en un tableau formaté présentable et à générer des totaux dans chaque ligne.

Comme déjà mentionné, une macro est un code écrit dans le langage de programmation VBA. Mais dans Excel, vous pouvez créer un programme sans écrire de ligne de code, ce que nous allons faire maintenant.

Pour créer une macro, ouvrez Voir(Voir) > Macro(Macro) > Enregistrer une macro(Enregistrer une macro...)

Donnez un nom à votre macro (sans espaces) et cliquez sur D'ACCORD.

A partir de ce moment, TOUTES vos actions avec le document sont enregistrées : changements de cellules, défilement dans le tableau, voire changement de taille de la fenêtre.

Excel signale que le mode d'enregistrement de macro est activé à deux endroits. Tout d'abord, au menu Macro(Macros) – au lieu d'une ligne Enregistrer une macro La ligne (Enregistrer la macro...) est apparue Arrête d'enregistrer(Arrête d'enregistrer).

Deuxièmement, dans le coin inférieur gauche de la fenêtre Excel. Icône Arrêt(petit carré) indique que le mode d’enregistrement macro est activé. Cliquer dessus arrêtera l’enregistrement. A l’inverse, lorsque le mode enregistrement n’est pas activé, il y a une icône à cet endroit pour activer l’enregistrement macro. Cliquer dessus donnera le même résultat que d'activer l'enregistrement via le menu.

Maintenant que le mode d'enregistrement de macro est activé, passons à notre tâche. Tout d’abord, ajoutons des en-têtes pour les données récapitulatives.

• =SOMME(B2:K2) ou =SOMME(B2:K2)
• =MOYENNE(B2:K2) ou =MOYENNE(B2:K2)
• =MIN(B2:K2) ou =MIN(B2:K2)
• =MAX(B2:K2) ou =MAX(B2:K2)
• =MÉDIANE(B2:K2) ou =MÉDIANE(B2:K2)

Sélectionnez maintenant les cellules avec les formules et copiez-les dans toutes les lignes de notre tableau en faisant glisser la poignée de remplissage automatique.

Une fois cette étape terminée, les totaux correspondants devraient apparaître dans chaque ligne.

Respectivement:

• =SOMME(L2:L21) ou =SOMME(L2:L21)
• = MOYENNE (B2:K21) ou = MOYENNE (B2:K21)– pour calculer cette valeur, il faut reprendre exactement les données du tableau d’origine. Si vous faites la moyenne des moyennes des lignes individuelles, le résultat sera différent.
• =MIN(N2:N21) ou =MIN(N2:N21)
• =MAX(O2:O21) ou =MAX(O2:O21)
• =MÉDIANE(B2:K21) ou =MÉDIANE(B2:K21)– nous calculons en utilisant les données du tableau original pour la raison indiquée ci-dessus.

Maintenant que nous avons terminé les calculs, passons au formatage. Tout d’abord, définissons le même format d’affichage des données pour toutes les cellules. Sélectionnez toutes les cellules de la feuille à l'aide du raccourci clavier Ctrl+A, ou cliquez sur l'icône Tout sélectionner, qui se trouve à l'intersection des en-têtes de ligne et de colonne. Puis clique Style virgule Onglet (Format délimité) Maison(Maison).

• Style de police gras.
• Alignement central.
• Remplir de couleur.

Et enfin, définissons le format des valeurs totales.

Voilà à quoi cela devrait ressembler au final :

Si vous êtes satisfait de tout, arrêtez d'enregistrer la macro.

Toutes nos félicitations! Vous venez d'enregistrer vous-même votre première macro Excel.

Pour utiliser la macro créée, nous devons enregistrer le document Excel dans un format prenant en charge les macros. Tout d'abord, nous devons supprimer toutes les données de la table que nous avons créée, c'est-à-dire faites-en un modèle vide. Le fait est qu'à l'avenir, en travaillant avec ce modèle, nous y importerons les données les plus récentes et les plus pertinentes.

Pour effacer toutes les cellules de données, faites un clic droit sur l'icône Tout sélectionner, qui est situé à l'intersection des en-têtes de ligne et de colonne, et dans le menu contextuel, sélectionnez l'élément Supprimer(Supprimer).

Désormais, notre feuille est complètement vidée de toutes les données, tandis que la macro reste enregistrée. Nous devons enregistrer le classeur en tant que modèle Excel prenant en charge les macros et portant l'extension XLTM.

Point important! Si vous enregistrez le fichier avec l'extension XLTX, alors la macro ne fonctionnera pas. À propos, vous pouvez enregistrer le classeur en tant que modèle Excel 97-2003, au format XLT, il prend également en charge les macros.

Une fois le modèle enregistré, vous pouvez fermer Excel en toute sécurité.

Exécuter une macro dans Excel

Avant de révéler toutes les capacités de la macro que vous avez créée, je pense qu'il est juste de prêter attention à quelques points importants concernant les macros en général :

• Les macros peuvent être nuisibles.
• Relisez le paragraphe précédent.

Le code VBA est très puissant. Il peut notamment effectuer des opérations sur des fichiers extérieurs au document actuel. Par exemple, une macro peut supprimer ou modifier n'importe quel fichier dans un dossier Mes documents. Pour cette raison, exécutez et autorisez uniquement les macros provenant de sources de confiance.

Pour exécuter notre macro de formatage des données, ouvrez le fichier modèle que nous avons créé dans la première partie de ce didacticiel. Si vous disposez de paramètres de sécurité standard, lorsque vous ouvrez un fichier, un avertissement apparaîtra au-dessus du tableau indiquant que l'exécution des macros est désactivée et un bouton pour activer leur exécution. Puisque nous avons réalisé le modèle nous-mêmes et que nous nous faisons confiance, nous appuyons sur le bouton Autoriser le contenu(Inclure le contenu).

L'étape suivante consiste à importer le dernier ensemble de données mis à jour à partir du fichier CSV(nous avons créé notre macro basée sur un tel fichier).

Lorsque vous importez des données depuis un fichier CSV, Excel peut vous demander de configurer certains paramètres pour transférer correctement les données dans le tableau.

Une fois l'importation terminée, allez dans le menu Macro Onglet (Macros) Voir(Afficher) et sélectionnez la commande Afficher les macros(Macro).

Dans la boîte de dialogue qui s'ouvre, nous verrons une ligne avec le nom de notre macro FormatDonnées. Sélectionnez-le et cliquez Courir(Courir).

Lorsque la macro démarre, vous verrez le curseur du tableau passer de cellule en cellule. Après quelques secondes, les mêmes opérations seront effectuées avec les données que lors de l'enregistrement de la macro. Lorsque tout est prêt, le tableau devrait ressembler à l'original que nous avons formaté manuellement, juste avec des données différentes dans les cellules.

Regardons sous le capot : Comment fonctionne une macro ?

Comme cela a été mentionné à plusieurs reprises, une macro est un code de programme dans un langage de programmation. Visual Basic pour les applications(VBA). Lorsque vous activez le mode d'enregistrement des macros, Excel enregistre chaque action que vous effectuez sous forme d'instructions dans VBA. En termes simples, Excel écrit le code pour vous.

Pour voir ce code de programme, vous devez aller dans le menu Macro Onglet (Macros) Voir(Voir) cliquez Afficher les macros(Macros) et dans la boîte de dialogue qui s'ouvre, cliquez sur Modifier(Changement).

Une fenêtre s'ouvrira Visual Basic pour les applications, dans lequel nous verrons le code de programme de la macro que nous avons enregistrée. Oui, vous avez bien compris, ici vous pouvez modifier ce code et même créer une nouvelle macro. Les actions que nous avons effectuées avec le tableau dans cette leçon peuvent être enregistrées à l'aide de l'enregistrement automatique de macros dans Excel. Mais les macros plus complexes, avec une séquence et une logique d’actions finement réglées, nécessitent une programmation manuelle.

Ajoutons une étape supplémentaire à notre tâche...

Imaginez que notre fichier de données d'origine données.csv est créé automatiquement par un processus et est toujours enregistré sur le disque au même endroit. Par exemple, C:\Données\données.csv– chemin d'accès au fichier contenant les données mises à jour. Le processus d'ouverture de ce fichier et d'importation de données à partir de celui-ci peut également être enregistré dans une macro :

1. Ouvrez le fichier modèle dans lequel nous avons enregistré la macro - FormatDonnées.
2. Créez une nouvelle macro nommée Charger les données.
3. Pendant l'enregistrement d'une macro Charger les données importer des données à partir d'un fichier données.csv- comme nous l'avons fait dans la partie précédente de la leçon.
4. Une fois l'importation terminée, arrêtez d'enregistrer la macro.
5. Supprimez toutes les données des cellules.
6. Enregistrez le fichier en tant que modèle Excel avec prise en charge des macros (extension XLTM).

Ainsi, en exécutant ce modèle, vous avez accès à deux macros : l'une charge les données, l'autre les formate.

Si vous souhaitez vous lancer dans la programmation, vous pouvez combiner les actions de ces deux macros en une seule - simplement en copiant le code de Charger les données au début du code FormatDonnées.

Travailler avec des livres

Macro 1 : Créer un nouveau classeur à partir de zéro
Macro 2. Enregistrement d'un classeur lorsqu'une cellule/plage spécifique est modifiée
Macro 3. Sauvegarder le classeur avant de le fermer
Macro 4. Protéger une feuille de calcul dans un classeur avant la fermeture
Macro 5. Déprotéger une feuille lors de l'ouverture d'un fichier Excel
Macro 6. Ouvrir un classeur sur la feuille souhaitée
Macro 7. Ouverture d'un classeur spécifique défini par l'utilisateur
Macro 8. Déterminer si le livre est ouvert
Macro 9. Déterminer si un livre existe dans un dossier
Macro 10. Mettre à jour toutes les connexions dans les classeurs ouverts
Macro 11. Fermez tous les livres en même temps
Macro 12. Ouvrir tous les classeurs d'un dossier
Macro 13. Imprimer tous les livres d'un dossier
Macro 14. Ne pas fermer le livre tant que la cellule n'est pas remplie
Macro 15. Créer une copie de sauvegarde du classeur actuel avec la date d'aujourd'hui

Travailler avec des feuilles

Macro 16. Ajouter une nouvelle feuille de calcul et attribuer un nom
Macro 17. Supprimer toutes les feuilles sauf celle active
Macro 18. Masquer tout sauf la feuille de calcul active
Macro 19. Afficher toutes les feuilles du classeur
Macro 20. Déplacer des feuilles de calcul
Macro 21. Trier les feuilles par nom
Macro 22. Regrouper les feuilles par couleur d'étiquette
Macro 23. Copier une feuille dans un nouveau classeur
Macro 24. Créer un nouveau classeur pour chaque feuille
Macro 25. Impression de feuilles
Macro 26. Protéger toutes les feuilles
Macro 27. Déprotéger toutes les feuilles
Macro 28. Création d'une table des matières
Macro 29 : Double-cliquez pour zoomer sur une feuille de calcul
Macro 30. Sélectionnez la colonne de ligne active

Sélection et modification des plages

Macro 31. Sélection et formatage d'une plage
Macro 32. Création et sélection de plages nommées
Macro 33. Énumération utilisant un certain nombre de cellules
Macro 34. Sélection et formatage des plages
Macro 35. Insérer des lignes vides dans une plage
Macro 36. Afficher toutes les lignes et colonnes masquées
Macro 37. Suppression des lignes vides
Macro 38. Suppression des colonnes vides
Macro 39. Sélection et formatage de toutes les formules du classeur
Macro 40 : Rechercher et sélectionner la première ligne ou colonne vide
Macro 41. Appliquer une couleur de remplissage supplémentaire
Macro 42. Trier les plages par double clic
Macro 43. Limitation de la plage de défilement dans une zone particulière
Macro 44. Définir automatiquement la zone d'impression de la feuille

Travailler avec des données

Macro 45. Copier et coller une plage
Macro 46 : Convertir toutes les formules d'une plage en valeurs
Macro 47. Convertir les valeurs de texte en valeurs numériques
Macro 48. Conversion d'un tiret en moins
Macro 49. Supprimer les espaces supplémentaires de toutes les cellules de la plage
Macro 50. Coupez 5 caractères à gauche dans chaque cellule de la plage
Macro 51. Ajouter les zéros manquants à la cellule
Macro 52. Remplacer les cellules vides par zéro
Macro 53. Ajouter du texte au début ou à la fin d'une cellule
Macro 54. Création d'une macro de conversion de données
Macro 55. Effacer les données (caractères non imprimables)
Macro 56. Sélectionnez les doublons dans la plage de données
Macro 57. Masquer les lignes répétitives
Macro 58. Masquer sélectivement les flèches du filtre automatique
Macro 59. Copiez les lignes filtrées dans un nouveau classeur
Macro 60. Création d'une nouvelle feuille pour chaque élément dans l'AutoFiltre
Macro 61. Afficher les colonnes filtrées dans la barre d'état

Travailler avec des tableaux croisés dynamiques

Macro 62 : Rendre un tableau croisé dynamique rétrocompatible
Macro 63. Mettre à jour tous les tableaux croisés dynamiques dans un livre
Macro 64. Créer un « inventaire » de tous les tableaux croisés dynamiques du livre
Macro 65. Créez tous les tableaux croisés dynamiques en utilisant le même cache de données
Macro 66. Masquer tous les sous-totaux dans le tableau croisé dynamique
Macro 67. Modifier les noms de données de tous les champs récapitulatifs
Macro 68. Somme forcée pour toutes les données récapitulatives
Macro 69 : Appliquer le format numérique à tous les éléments de données
Macro 70. Tri des champs récapitulatifs par ordre alphabétique
Macro 71. Appliquer un tri personnalisé aux éléments de données
Macro 72 : Mettre une protection sur le tableau croisé dynamique
Macro 73 : Appliquer des contraintes de champ pivot
Macro 74. Suppression automatique des feuilles avec détails récapitulatifs
Macro 75 : imprimer un tableau croisé dynamique pour chaque élément de filtre
Macro 76. Créer un nouveau fichier pour chaque élément filtrant
Macro 77. Préparation d'une plage de données pour un tableau croisé dynamique

Travailler avec des tableaux et des graphiques

Macro 78. Redimensionner les graphiques sur une feuille de calcul
Macro 79. Lier le graphique à une plage spécifique
Macro 80 : Créer un ensemble de diagrammes disjoints
Macro 81 : Imprimer tous les graphiques sur une feuille de calcul
Macro 82. Marquage des meilleures et des pires valeurs sur
Macro 83. Mêmes couleurs pour les valeurs sur différents graphiques
Macro 84. Faire correspondre la couleur des graphiques à la couleur des plages

Envoi d'e-mails depuis Excel

Macro 85. Envoi d'un livre actif par mail (pièce jointe)
Macro 86 : Envoi d'une plage de valeurs en pièce jointe
Macro 87. Envoi d'une feuille en pièce jointe
Macro 88. Envoyer un email avec un lien vers nos fichiers
Macro 89 : Envoi d'e-mails ajoutant des adresses à notre liste de contacts
Macro 90. Enregistrer toutes les pièces jointes dans un dossier séparé
Macro 91. Enregistrer des pièces jointes spécifiques dans un dossier

Interopérabilité avec d'autres applications Office

Macro 92. Exécuter une demande d'accès depuis Excel



Macro 96. Compresser une base de données Access à partir d'Excel
Macro 97. Envoi de données Excel vers un document Word
Macro 98. Faire une fusion avec un document Word
Macro 99 : Envoi de données Excel vers une présentation PowerPoint
Macro 100. Envoi de graphiques Excel vers des présentations PowerPoint
Macro 101 : Conversion d'un classeur en présentation PowerPoint

Interopérabilité avec d'autres applications Office
apprendre à travailler avec Word, Access et PowerPoint
Macro 92. Exécuter une demande d'accès depuis Excel
Macro 93. Exécution d'une macro Access à partir d'Excel
Macro 94. Ouverture d'un rapport Access depuis Excel
Macro 95. Ouverture d'un formulaire d'accès depuis Excel

Les exemples de macros Excel simples suivants illustrent certaines des fonctionnalités et techniques décrites dans le didacticiel Excel VBA.

Macro Excel : exemple 1

Initialement, cette procédure Sous a été donné comme exemple d'utilisation de commentaires dans le code VBA. Cependant, vous pouvez également voir ici comment les variables sont déclarées, comment fonctionnent les références de cellules Excel et l'utilisation d'une boucle. Pour, opérateur conditionnel Si et afficher une fenêtre de message.

"La procédure Sub recherche une cellule contenant la chaîne spécifiée" dans la plage de cellules A1:A100 de la feuille active Sub Find_String(sFindText As String) Dim i As Integer "Un entier de type Integer, utilisé dans une boucle For Dim iRowNumber As Integer "Un entier de type Integer pour stocker le résultat iRowNumber = 0 "Parcourt les cellules A1:A100 une par une jusqu'à ce que la chaîne soit trouvée sFindText For i = 1 To 100 If Cells(i, 1).Value = sFindText Then " Si une correspondance avec la chaîne spécifiée est trouvée " enregistrez le numéro de la ligne actuelle et quittez la boucle For iRowNumber = i Exit For End If Next i " Informez l'utilisateur dans une fenêtre contextuelle si la ligne requise a été trouvée " Si la ligne spécifiée est trouvée, indiquez dans quelle cellule la correspondance est trouvée Si iRowNumber = 0 Then MsgBox "Row" & sFindText & " not found" Else MsgBox "Row " & sFindText & " found in cell A" & iRowNumber End If End Sub

Macro Excel : exemple 2

Procédure suivante Sous– exemple d'utilisation d'une boucle Faire pendant. Vous pouvez également voir comment les variables sont déclarées, en travaillant avec des références de cellules Excel et en utilisant une instruction conditionnelle. Si.

"La procédure Sub génère des nombres de Fibonacci ne dépassant pas 1000 Sub Fibonacci() Dim i As Integer "Un compteur pour indiquer la position d'un élément dans la séquence Dim iFib As Integer "Stocke la valeur actuelle de la séquence Dim iFib_Next As Integer "Stocke le valeur suivante de la séquence Dim iStep As Integer "Stocke la taille du prochain incrément" Initialise les variables i et iFib_Next i = 1 iFib_Next = 0 "La boucle Do While sera exécutée jusqu'à ce que la valeur du "numéro de Fibonacci actuel dépasse 1000 Do While iFib_Suivant< 1000 If i = 1 Then "Особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else "Сохраняем размер следующего приращения перед тем, как перезаписать "текущее значение последовательности iStep = iFib iFib = iFib_Next End If "Выводим текущее число Фибоначчи в столбце A активного рабочего листа "в строке с индексом i Cells(i, 1).Value = iFib "Вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

Macro Excel : exemple 3

Cette procédure Sous scanne les cellules d'une colonne UN feuille active jusqu’à ce qu’elle rencontre une cellule vide. Les valeurs sont écrites dans un tableau. Cette simple macro Excel montre l'utilisation de tableaux dynamiques, ainsi que l'utilisation d'une boucle. Faire jusqu'à ce que. Dans cet exemple, nous n'effectuerons aucune action avec le tableau, bien que dans la pratique de programmation réelle, une fois les données écrites dans le tableau, de telles actions soient généralement effectuées sur eux.

"La procédure Sub stocke les valeurs des cellules de la colonne A de la feuille active dans le tableau Sub GetCellValues() Dim iRow As Integer "Stocke le numéro de la ligne actuelle Dim dCellValues() As Double "Un tableau pour stocker les valeurs des cellules ​​iRow = 1 ReDim dCellValues(1 To 10) "Do Loop Until parcourt séquentiellement les cellules de la colonne A de la feuille active" et extrait leurs valeurs dans un tableau jusqu'à ce qu'une cellule vide soit rencontrée Do Until IsEmpty(Cells( iRow, 1)) "Vérifiez que le tableau dCellValues ​​est de taille suffisante "Sinon, augmentez la taille du tableau de 10 en utilisant ReDim If UBound(dCellValues)< iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub

Macro Excel : exemple 4

Dans cet exemple, la procédure Sous lit les valeurs d'une colonne UN feuille de travail Feuille2 et effectue des opérations arithmétiques sur eux. Les résultats sont saisis dans les cellules de la colonne UN sur la feuille de calcul active. Cette macro montre l'utilisation d'objets Excel. En particulier, le recours s'effectue selon la procédure Sousà l'objet Colonnes, et montre comment cet objet est accessible via l'objet Feuille de travail. Il est également montré que lors de l'accès à une cellule ou plage de cellules de la feuille active, il n'est pas nécessaire de préciser le nom de cette feuille lors de l'écriture du lien.

"La procédure Sub, à l'aide d'une boucle, lit les valeurs de la colonne A de la feuille de calcul Sheet2", effectue des opérations arithmétiques avec chaque valeur et écrit le résultat dans la "colonne A de la feuille de calcul active (Sheet1) Sub Transfer_ColA() Dim i As Integer Dim Col As Range Dim dVal As Double "Attribuer à la variable Col colonne A de la feuille de calcul Feuille 2 Set Col = Sheets("Sheet2").Columns("A") i = 1 "À l'aide d'une boucle, nous lisons les valeurs ​​des cellules de la colonne Col jusqu'à "jusqu'à ce qu'une cellule vide soit rencontrée Do Until IsEmpty(Col.Cells(i)) "Effectuer des opérations arithmétiques sur la valeur de la cellule courante dVal = Col.Cells(i).Value * 3 - 1 "La commande suivante écrit le résultat dans la colonne A de la feuille de calcul active "Indiquer le nom de la feuille dans le lien n'est pas nécessaire puisqu'il s'agit de la feuille active Cells(i, 1) = dVal i = i + 1 Loop End Sub

Macro Excel : exemple 5

Cette macro montre un exemple de code VBA qui surveille un événement Excel. L'événement auquel la macro est attachée se produit chaque fois qu'une cellule ou une plage de cellules est sélectionnée sur la feuille de calcul. Dans notre cas, lors de la sélection d'une cellule B1, une fenêtre de message apparaît à l'écran.

"Ce code affiche une boîte de message si la cellule B1 est sélectionnée sur la feuille de calcul actuelle. Private Sub Worksheet_SelectionChange (ByVal Target As Range) "Vérifiez si la cellule B1 est sélectionnée Si Target.Count = 1 Et Target.Row = 1 Et Target.Column = 2 Puis "Si la cellule B1 est sélectionnée, effectuez l'action requise MsgBox "Vous avez sélectionné la cellule B1" End If End Sub

Macro Excel : exemple 6

Cette procédure illustre l'utilisation des opérateurs En cas d'erreur Et CV pour la gestion des erreurs. Ce code montre également un exemple d'ouverture et de lecture de données à partir d'un fichier.

"La procédure Sub attribue aux arguments Val1 et Val2 les valeurs des cellules A1 et B1" à partir du classeur Data.xlsx situé dans le dossier C:\Documents and Settings Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo ErrorHandling " Ouvrez le classeur avec les données Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data") "Attribuez les valeurs des variables Val1 et Val2 du classeur donné Val1 = Sheets("Sheet1 ".Cells(1, 1) Val2 = Sheets("Sheet1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling : "Si le fichier n'est pas trouvé, l'utilisateur sera invité à placer le fichier recherché " dans le dossier souhaité, puis continuez à exécuter la macro MsgBox « Le fichier Data.xlsx est introuvable ! » & _ « Veuillez ajouter le classeur au dossier C:\Documents and Settings et cliquez sur OK » Reprendre Fin du sous-marin

Peu de gens savent que la première version du populaire produit Microsoft Excel est apparue en 1985. Depuis lors, il a subi plusieurs modifications et est très demandé par des millions d'utilisateurs à travers le monde. Dans le même temps, de nombreuses personnes ne travaillent qu’avec une petite fraction des capacités de ce tableur et ne réalisent même pas à quel point la possibilité de programmer dans Excel pourrait leur faciliter la vie.

Qu'est-ce que VBA

La programmation dans Excel s'effectue à l'aide du langage de programmation Visual Basic for Application, initialement intégré au tableur le plus célèbre de Microsoft.

Les experts attribuent sa relative facilité d’apprentissage à ses avantages. Comme le montre la pratique, même les utilisateurs qui n'ont pas de compétences professionnelles en programmation peuvent maîtriser les bases de VBA. Les fonctionnalités de VBA incluent l'exécution de scripts dans un environnement d'application bureautique.

L'inconvénient du programme réside dans les problèmes liés à la compatibilité des différentes versions. Ils se produisent parce que le code du programme VBA accède à des fonctionnalités présentes dans la nouvelle version du produit mais pas dans l'ancienne version. En outre, les inconvénients incluent une trop grande ouverture du code aux modifications apportées par un étranger. Cependant, Microsoft Office ainsi qu'IBM Lotus Symphony permettent à l'utilisateur de crypter le code initial et de définir un mot de passe pour l'afficher.

Objets, collections, propriétés et méthodes

Ce sont les concepts qui doivent être compris par ceux qui vont travailler dans l’environnement VBA. Tout d’abord, vous devez comprendre ce qu’est un objet. Dans Excel, ces fonctions sont feuille, classeur, cellule et plage. Ces objets ont une hiérarchie particulière, c'est-à-dire obéissez-vous les uns aux autres.

Le principal est Application, qui correspond au programme Excel lui-même. Viennent ensuite les classeurs, les feuilles de calcul et la plage. Par exemple, pour accéder à la cellule A1 d'une feuille de calcul spécifique, vous devez spécifier un chemin qui prend en compte la hiérarchie.

Quant au concept de « collection », il s’agit d’un groupe d’objets de la même classe, qui dans l’entrée a la forme ChartObjects. Ses éléments individuels sont également des objets.

Le concept suivant concerne les propriétés. Ils constituent une caractéristique nécessaire de tout objet. Par exemple, pour Range, il s'agit d'une valeur ou d'une formule.

Les méthodes sont des commandes qui indiquent ce qui doit être fait. Lors de l'écriture de code en VBA, ils doivent être séparés de l'objet par un point. Par exemple, comme nous le montrerons plus tard, très souvent lors de la programmation dans Excel, la commande Cells(1,1).Select est utilisée. Cela signifie que vous devez sélectionner une cellule avec des coordonnées

Selection.ClearContents est souvent utilisé avec lui. Exécuter cela signifie effacer le contenu de la cellule sélectionnée.

Comment commencer

Ensuite, vous devez vous rendre sur l'application VB, pour laquelle il vous suffit d'utiliser la combinaison de touches « Alt » et « F11 ». Plus loin:

• dans la barre de menu située en haut de la fenêtre, cliquez sur l'icône à côté de l'icône Excel ;
• sélectionnez la commande Mudule ;
• enregistrez en cliquant sur l'icône avec l'image ;
• ils écrivent, disons, une ébauche du code.

Cela ressemble à ceci :

Sous-programme()

"Notre code

Veuillez noter que la ligne "Notre code" sera surlignée d'une couleur différente (vert), en raison de l'apostrophe placée en début de ligne, qui indique qu'un commentaire suit.

Vous pouvez désormais écrire n'importe quel code et créer vous-même un nouvel outil dans VBA Excel (voir exemples de programmes ci-dessous). Bien entendu, cela sera beaucoup plus simple pour ceux qui connaissent les bases de Visual Basic. Cependant, même ceux qui n’en disposent pas peuvent s’y habituer assez rapidement s’ils le souhaitent.

Macros dans Excel

Ce nom cache les programmes écrits en Visual Basic pour Application. Ainsi, programmer dans Excel signifie créer des macros avec le code nécessaire. Grâce à cette fonctionnalité, le tableur Microsoft se développe lui-même, s'adaptant aux exigences d'un utilisateur spécifique. Après avoir compris comment créer des modules pour écrire des macros, vous pouvez commencer à examiner des exemples spécifiques de programmes VBA Excel. Il est préférable de commencer par les codes les plus basiques.

Exemple 1

Tâche : écrire un programme qui copiera la valeur du contenu d'une cellule puis l'écrira dans une autre.

Pour ça:

• ouvrez l'onglet « Affichage » ;
• allez sur l'icône « Macros » ;
• cliquez sur « Enregistrer la macro » ;
• remplissez le formulaire qui s'ouvre.

Pour plus de simplicité, laissez « Macro1 » dans le champ « Nom de la macro », et insérez par exemple hh dans le champ « Raccourci clavier » (cela signifie que vous pouvez lancer le programme avec la commande rapide « Ctrl+h »). Appuyez sur Entrée.

Maintenant que l'enregistrement de la macro a déjà commencé, le contenu d'une cellule est copié dans une autre. Revenez à l'icône d'origine. Cliquez sur « Enregistrer une macro ». Cette action signifie la fin du programme.

• revenez à la ligne « Macros » ;
• sélectionnez « Macro 1 » dans la liste ;
• cliquez sur « Exécuter » (la même action est lancée en lançant la combinaison de touches « Ctrl+hh »).

En conséquence, l'action effectuée lors de l'enregistrement de la macro se produit.

Il est logique de voir à quoi ressemble le code. Pour cela, retournez sur la ligne « Macros » et cliquez sur « Modifier » ou « Connexion ». Du coup, ils se retrouvent dans l’environnement VBA. En fait, le code de la macro lui-même se trouve entre les lignes Sub Macro1() et End Sub.

Si la copie a été effectuée, par exemple, de la cellule A1 vers la cellule C1, alors l'une des lignes de code ressemblera à Range("C1").Select. Traduit, cela ressemble à « Range(« C1 »).Select », en d’autres termes, cela va dans VBA Excel, dans la cellule C1.

La partie active du code est complétée par la commande ActiveSheet.Paste. Cela signifie écrire le contenu de la cellule sélectionnée (dans ce cas A1) dans la cellule sélectionnée C1.

Exemple 2

Les boucles VBA vous aident à créer diverses macros dans Excel.

Les boucles VBA vous aident à créer diverses macros. Supposons que nous ayons une fonction y=x + x 2 + 3x 3 - cos(x). Vous devez créer une macro pour obtenir son graphique. Cela ne peut être fait qu'à l'aide de boucles VBA.

Les valeurs initiales et finales de l'argument de la fonction sont x1=0 et x2=10. De plus, vous devez saisir une constante - la valeur de l'étape de changement d'argument et la valeur initiale du compteur.

Tous les exemples de macros VBA Excel sont créés en utilisant la même procédure que celle présentée ci-dessus. Dans ce cas particulier, le code ressemble à :

Sous-programme()

pas = 0,1

Faire pendant x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

y=x1 + x1^2 + 3*x1^3 - Cos(x1)

Cells(i, 1).Value = x1 (la valeur x1 est écrite dans la cellule avec les coordonnées (i,1))

Cells(i, 2).Value = y (la valeur y est écrite dans la cellule avec les coordonnées (i,2))

i = i + 1 (le compteur est en vigueur) ;

x1 = x1 + shag (l'argument change en fonction de la valeur du pas) ;

Fin du sous-marin.

Suite à l'exécution de cette macro dans Excel, nous obtenons deux colonnes, dont la première contient les valeurs de x et la seconde de y.

Ensuite, un graphique est construit à partir d'eux de manière standard pour Excel.

Exemple 3

Pour implémenter des boucles dans VBA Excel 2010, comme dans d'autres versions, en plus de la construction Do While déjà donnée, For est utilisé.

Considérons un programme qui créera une colonne. Dans chacune de ses cellules seront écrits les carrés du numéro de ligne correspondant. L'utilisation de la construction For vous permettra de l'écrire très brièvement, sans utiliser de compteur.

Vous devez d’abord créer une macro comme décrit ci-dessus. Ensuite, nous écrivons le code lui-même. Nous supposons que nous nous intéressons aux valeurs de 10 cellules. Le code ressemble à ceci.

Pour i = 1 à 10 Suivant

La commande est traduite en langage « humain » par « Répéter de 1 à 10 par pas de un ».

Si la tâche consiste à obtenir une colonne avec des carrés, par exemple, de tous les nombres impairs compris entre 1 et 11, alors nous écrivons :

Pour i = 1 à 10 étape 1 Suivant.

Ici, une étape est une étape. Dans ce cas, il est égal à deux. Par défaut, l'absence de ce mot dans le cycle signifie que l'étape est unique.

Les résultats obtenus doivent être sauvegardés dans des cellules numérotées (i,1). Ensuite, chaque fois que la boucle est démarrée, avec i augmentant d'un pas, le numéro de ligne augmentera automatiquement. De cette façon, le code sera optimisé.

Globalement, le code ressemblera à :

Sous-programme()

Pour i = 1 à 10 Étape 1 (vous pouvez simplement écrire Pour i = 1 à 10)

Cellules(i, 1).Value = i ^ 2 (c'est-à-dire que la valeur du carré i est écrite dans la cellule (i,1)

Suivant (joue en un sens le rôle d'un compteur et signifie un autre début de boucle)

Fin du sous-marin.

Si tout est fait correctement, y compris l'enregistrement et l'exécution de la macro (voir les instructions ci-dessus), alors lorsque vous l'appelez, vous obtiendrez à chaque fois une colonne de la taille spécifiée (dans ce cas, composée de 10 cellules).

Exemple 4

Dans la vie de tous les jours, il est souvent nécessaire de prendre telle ou telle décision en fonction de certaines conditions. Vous ne pouvez pas vous en passer dans VBA Excel. Des exemples de programmes où le déroulement ultérieur de l'exécution de l'algorithme est choisi et non prédéterminé initialement, utilisent le plus souvent la construction If ... Then (pour les cas complexes) If ... Then ... END If.

Considérons un cas précis. Supposons que vous deviez créer une macro pour Excel afin que ce qui suit soit écrit dans la cellule avec les coordonnées (1,1) :

1 si l'argument est positif ;

0 si l'argument est nul ;

-1 si l'argument est négatif.

La création d'une telle macro pour Excel commence de manière standard, grâce à l'utilisation des raccourcis clavier Alt et F11. Ensuite le code suivant est écrit :

Sous-programme()

x= Cells(1, 1).Value (cette commande attribue à x la valeur du contenu de la cellule aux coordonnées (1, 1))

Si x>0 Alors Cellules(1, 1).Valeur = 1

Si x=0 Alors Cellules(1, 1).Valeur = 0

Si x<0 Then Cells(1, 1).Value = -1

Fin du sous-marin.

Il ne reste plus qu'à exécuter la macro et à obtenir la valeur requise pour l'argument dans Excel.

Fonctions VBA

Comme vous l'avez peut-être déjà remarqué, programmer dans le tableur le plus célèbre de Microsoft n'est pas si difficile. Surtout si vous apprenez à utiliser les fonctions VBA. Au total, ce langage de programmation, créé spécifiquement pour écrire des applications dans Excel et Word, possède environ 160 fonctions. Ils peuvent être divisés en plusieurs grands groupes. Ce:

• Fonctions mathématiques. En les appliquant à l'argument, ils obtiennent la valeur du cosinus, du logarithme naturel, de la partie entière, etc.
• Fonctions financières. Grâce à leur disponibilité et grâce à la programmation Excel, vous pouvez obtenir des outils efficaces de calculs comptables et financiers.
• Fonctions de traitement de tableaux. Ceux-ci incluent Array, IsArray ; LBound ; ULié.
• Fonctions Excel VBA pour chaîne. Il s'agit d'un groupe assez important. Cela inclut, par exemple, les fonctions Space pour créer une chaîne avec un nombre d'espaces égal à l'argument entier, ou Asc pour convertir des caractères en code ANSI. Tous sont largement utilisés et vous permettent de travailler avec des lignes dans Excel, créant ainsi des applications qui facilitent grandement le travail avec ces tableaux.
• Fonctions de conversion de type de données. Par exemple, CVar renvoie la valeur de l'argument Expression en le convertissant en type de données Variant.
• Fonctions pour travailler avec des dates. Ils élargissent considérablement les standards.Ainsi, la fonction WeekdayName renvoie le nom (complet ou partiel) du jour de la semaine par son numéro. Timer est encore plus utile. Il donne le nombre de secondes écoulées entre minuit et un moment précis de la journée.
• Fonctions pour convertir un argument numérique en différents systèmes numériques. Par exemple, Oct génère des nombres en octal.
• Fonctions de formatage. Le plus important d'entre eux est le format. Il renvoie une valeur Variant avec une expression formatée selon les instructions spécifiées dans la déclaration de format.
• etc.

L'étude des propriétés de ces fonctions et de leur application élargira considérablement la portée d'Excel.

Exemple 5

Essayons de passer à la résolution de problèmes plus complexes. Par exemple:

Un document papier rendant compte du niveau réel des coûts de l'entreprise est remis. Requis:

• développer sa partie modèle à l'aide du tableur Excel ;
• créez un programme VBA qui demandera les données initiales pour le remplir, effectuera les calculs nécessaires et remplira les cellules du modèle correspondant avec elles.

Considérons l'une des options de solution.

Créer un modèle

Toutes les actions sont réalisées sur une feuille standard sous Excel. Des cellules libres sont réservées à la saisie des données sur le nom de l'entreprise consommatrice, le montant des coûts, leur niveau et leur chiffre d'affaires. Le nombre d'entreprises (sociétés) pour lesquelles le rapport est établi n'est pas fixe, les cellules de saisie des valeurs en fonction des résultats et du nom du spécialiste ne sont pas réservées à l'avance. La feuille de calcul reçoit un nouveau nom. Par exemple, « rapports ».

Variables

Pour écrire un programme permettant de remplir automatiquement un modèle, vous devez sélectionner des notations. Ceux-ci seront utilisés pour les variables :

• NN - numéro de la ligne actuelle du tableau ;
• TP et TF - chiffre d'affaires commercial prévu et réel ;
• SF et SP - montant des coûts réels et prévus ;
• IP et IF - niveau de coûts prévu et réel.

Notons l'accumulation du total pour cette colonne en utilisant les mêmes lettres, mais avec le « préfixe » Itog. Par exemple, ItogTP - fait référence à la colonne du tableau intitulée « chiffre d'affaires prévu ».

Résoudre un problème en utilisant la programmation VBA

En utilisant les notations introduites, nous obtenons des formules d'écarts. Si vous devez calculer en %, nous avons (F - P) / P * 100, et au total - (F - P).

Il est préférable de saisir les résultats de ces calculs directement dans les cellules appropriées d'une feuille de calcul Excel.

Pour les totaux réels et prévisionnels, ils sont obtenus à l'aide des formules ItogP=ItogP + P et ItogF=ItogF+ F.

Pour les écarts utiliser = (ItogF - ItogP) / ItogP * 100 si le calcul est effectué en pourcentage, et dans le cas d'une valeur totale - (ItogF - ItogP).

Les résultats sont à nouveau immédiatement écrits dans les cellules appropriées, il n'est donc pas nécessaire de les affecter à des variables.

Avant d'exécuter le programme créé, vous devez enregistrer le classeur, par exemple sous le nom "Report1.xls".

Il suffit d'appuyer une seule fois sur le bouton « Créer un tableau de rapport » après avoir saisi les informations d'en-tête. Il y a d'autres règles que vous devriez connaître. En particulier, il faut cliquer sur le bouton « Ajouter une ligne » à chaque fois après avoir saisi les valeurs de chaque type d'activité dans le tableau. Après avoir saisi toutes les données, vous devez cliquer sur le bouton « Terminer », puis passer à la fenêtre Excel.

Vous savez maintenant comment résoudre les problèmes d'Excel à l'aide de macros. La possibilité d'utiliser VBA Excel (voir les exemples de programmes ci-dessus) peut également être nécessaire pour travailler dans l'éditeur de texte le plus populaire du moment, Word. Vous pouvez notamment créer des boutons de menu en écrivant, comme indiqué au tout début de l'article, ou en écrivant du code, grâce auquel de nombreuses opérations sur le texte peuvent être effectuées en appuyant sur les touches de service ou via l'onglet « Affichage » et l'icône « Macros ».