Tutoriel Excel VBA - Comment écrire du code dans une feuille de calcul à l'aide de Visual Basic

introduction

Il s'agit d'un didacticiel sur l'écriture de code dans des feuilles de calcul Excel à l'aide de Visual Basic pour Applications (VBA).

Excel est l'un des produits les plus populaires de Microsoft. En 2016, le PDG de Microsoft a déclaré: "Pensez à un monde sans Excel. C'est tout simplement impossible pour moi." Eh bien, peut-être que le monde ne peut pas penser sans Excel.

  • En 1996, il y avait plus de 30 millions d'utilisateurs de Microsoft Excel (source).
  • Aujourd'hui, on estime à 750 millions le nombre d'utilisateurs de Microsoft Excel. C'est un peu plus que la population européenne et 25 fois plus d'utilisateurs qu'il n'y en avait en 1996.

Nous sommes une grande famille heureuse!

Dans ce didacticiel, vous découvrirez VBA et comment écrire du code dans une feuille de calcul Excel à l'aide de Visual Basic.

Conditions préalables

Vous n'avez besoin d'aucune expérience de programmation préalable pour comprendre ce didacticiel. Cependant, vous aurez besoin de:

  • Connaissance de base à intermédiaire de Microsoft Excel
  • Si vous souhaitez suivre les exemples VBA de cet article, vous aurez besoin d'accéder à Microsoft Excel, de préférence la dernière version (2019), mais Excel 2016 et Excel 2013 fonctionneront très bien.
  • Une volonté d'essayer de nouvelles choses

Objectifs d'apprentissage

Au cours de cet article, vous apprendrez:

  1. Qu'est-ce que VBA
  2. Pourquoi vous utiliseriez VBA
  3. Comment se configurer dans Excel pour écrire VBA
  4. Comment résoudre certains problèmes du monde réel avec VBA

Concepts importants

Voici quelques concepts importants que vous devez connaître pour bien comprendre ce didacticiel.

Objets : Excel est orienté objet, ce qui signifie que tout est un objet - la fenêtre Excel, le classeur, une feuille, un graphique, une cellule. VBA permet aux utilisateurs de manipuler et d'effectuer des actions avec des objets dans Excel.

Si vous n'avez aucune expérience de la programmation orientée objet et qu'il s'agit d'un tout nouveau concept, prenez une seconde pour laisser cela pénétrer!

Procédures : une procédure est un morceau de code VBA, écrit dans Visual Basic Editor, qui accomplit une tâche. Parfois, cela est également appelé macro (plus d'informations sur les macros ci-dessous). Il existe deux types de procédures:

  • Sous-programmes: un groupe d'instructions VBA qui effectue une ou plusieurs actions
  • Fonctions: un groupe d'instructions VBA qui effectue une ou plusieurs actions et renvoie une ou plusieurs valeurs

Remarque: vous pouvez faire fonctionner des fonctions à l'intérieur des sous-programmes. Vous verrez plus tard.

Macros : si vous avez passé du temps à apprendre des fonctionnalités Excel plus avancées, vous avez probablement rencontré le concept de «macro». Les utilisateurs d'Excel peuvent enregistrer des macros, constituées de commandes / frappes / clics utilisateur, et les lire à la vitesse de l'éclair pour accomplir des tâches répétitives. Les macros enregistrées génèrent du code VBA, que vous pouvez ensuite examiner. C'est en fait assez amusant d'enregistrer une macro simple, puis de regarder le code VBA.

Gardez à l'esprit que parfois, il peut être plus facile et plus rapide d'enregistrer une macro plutôt que de coder manuellement une procédure VBA.

Par exemple, vous travaillez peut-être dans la gestion de projet. Une fois par semaine, vous devez transformer un rapport brut exporté de votre système de gestion de projet en un rapport parfaitement formaté et propre pour le leadership. Vous devez mettre en forme les noms des projets dépassant le budget en texte rouge gras. Vous pouvez enregistrer les modifications de mise en forme sous forme de macro et l'exécuter chaque fois que vous devez effectuer la modification.

Qu'est-ce que VBA?

Visual Basic pour Applications est un langage de programmation développé par Microsoft. Chaque logiciel de la suite Microsoft Office est fourni avec le langage VBA sans frais supplémentaires. VBA permet aux utilisateurs de Microsoft Office de créer de petits programmes qui fonctionnent dans les logiciels Microsoft Office.

Pensez à VBA comme à un four à pizza dans un restaurant. Excel est le restaurant. La cuisine est livrée avec des appareils commerciaux standard, tels que de grands réfrigérateurs, des cuisinières et des fours classiques - ce sont toutes les fonctionnalités standard d'Excel.

Mais que faire si vous voulez faire une pizza au feu de bois? Je ne peux pas faire cela dans un four de cuisson commercial standard. VBA est le four à pizza.

Miam.

Pourquoi utiliser VBA dans Excel?

Parce que la pizza au feu de bois est la meilleure!

Mais sérieusement.

Beaucoup de gens passent beaucoup de temps dans Excel dans le cadre de leur travail. Le temps dans Excel se déplace également différemment. Selon les circonstances, 10 minutes dans Excel peuvent sembler éternelles si vous ne pouvez pas faire ce dont vous avez besoin, ou 10 heures peuvent passer très rapidement si tout se passe bien. Quel est le moment où vous devriez vous demander, pourquoi diable suis-je passé 10 heures dans Excel?

Parfois, ces jours sont inévitables. Mais si vous passez 8 à 10 heures par jour dans Excel à faire des tâches répétitives, à répéter un grand nombre des mêmes processus, à essayer de nettoyer après d'autres utilisateurs du fichier, ou même à mettre à jour d'autres fichiers après des modifications apportées au fichier Excel, une procédure VBA pourrait bien être la solution pour vous.

Vous devriez envisager d'utiliser VBA si vous devez:

  • Automatisez les tâches répétitives
  • Créez des moyens faciles pour les utilisateurs d'interagir avec vos feuilles de calcul
  • Manipulez de grandes quantités de données

Se préparer à écrire VBA dans Excel

Onglet Développeur

Pour écrire VBA, vous devrez ajouter l'onglet Développeur au ruban, de sorte que vous verrez le ruban comme celui-ci.

Pour ajouter l'onglet Développeur au ruban:

  1. Sous l'onglet Fichier, accédez à Options> Personnaliser le ruban.
  2. Sous Personnaliser le ruban et sous Onglets principaux, cochez la case Développeur.

Une fois que vous affichez l'onglet, l'onglet Développeur reste visible, sauf si vous désactivez la case à cocher ou devez réinstaller Excel. Pour plus d'informations, consultez la documentation d'aide de Microsoft.

Éditeur VBA

Accédez à l'onglet Développeur et cliquez sur le bouton Visual Basic. Une nouvelle fenêtre apparaîtra - c'est l'éditeur Visual Basic. Pour les besoins de ce didacticiel, il vous suffit de vous familiariser avec le volet Explorateur de projet et le volet Propriétés de propriété.

Exemples Excel VBA

Tout d'abord, créons un fichier dans lequel nous pouvons jouer.

  1. Ouvrez un nouveau fichier Excel
  2. Enregistrez-le en tant que classeur prenant en charge les macros (. Xlsm)
  3. Sélectionnez l'onglet Développeur
  4. Ouvrez l'éditeur VBA

Allons-y avec quelques exemples simples pour vous permettre d'écrire du code dans une feuille de calcul à l'aide de Visual Basic.

Exemple n ° 1: afficher un message lorsque les utilisateurs ouvrent le classeur Excel

Dans l'éditeur VBA, sélectionnez Insérer -> Nouveau module

Écrivez ce code dans la fenêtre Module (ne pas coller!):

Sous Auto_Open ()

MsgBox ("Bienvenue dans le classeur XYZ.")

End Sub

Enregistrez, fermez le classeur et rouvrez le classeur. Cette boîte de dialogue devrait s'afficher.

Ta da!

Comment fait-il cela?

En fonction de votre connaissance de la programmation, vous pouvez avoir des suppositions. Ce n'est pas particulièrement complexe, mais il se passe beaucoup de choses:

  • Sub (abréviation de «Subroutine): rappelez-vous depuis le début,« un groupe d'instructions VBA qui effectue une ou plusieurs actions ».
  • Auto_Open: c'est le sous-programme spécifique. Il exécute automatiquement votre code à l'ouverture du fichier Excel - c'est l'événement qui déclenche la procédure. Auto_Open ne s'exécutera que lorsque le classeur est ouvert manuellement; il ne s'exécutera pas si le classeur est ouvert via le code d'un autre classeur (Workbook_Open le fera, en savoir plus sur la différence entre les deux).
  • Par défaut, l'accès à un sous-programme est public. Cela signifie que tout autre module peut utiliser ce sous-programme. Tous les exemples de ce didacticiel seront des sous-programmes publics. Si nécessaire, vous pouvez déclarer les sous-programmes comme privés. Cela peut être nécessaire dans certaines situations. En savoir plus sur les modificateurs d'accès aux sous-programmes.
  • msgBox: il s'agit d'une fonction - un groupe d'instructions VBA qui effectue une ou plusieurs actions et renvoie une valeur. La valeur renvoyée est le message «Bienvenue dans le classeur XYZ».

En bref, il s'agit d'un sous-programme simple qui contient une fonction.

Quand pourrais-je l'utiliser?

Peut-être avez-vous un fichier très important auquel on accède rarement (par exemple, une fois par trimestre), mais qui est automatiquement mis à jour quotidiennement par une autre procédure VBA. Lorsqu'il est accédé, c'est par de nombreuses personnes dans plusieurs départements, dans toute l'entreprise.

  • Problème: la plupart du temps, lorsque les utilisateurs accèdent au fichier, ils ne savent pas quel est l'objectif de ce fichier (pourquoi il existe), comment il est mis à jour si souvent, qui le maintient et comment ils devraient interagir avec lui. Les nouveaux employés ont toujours des tonnes de questions, et vous devez répondre à ces questions encore et encore.
  • Solution: créez un message utilisateur contenant une réponse concise à chacune de ces questions fréquemment posées.

Exemples du monde réel

  • Utilisez la fonction MsgBox pour afficher un message en cas d'événement: l'utilisateur ferme un classeur Excel, l'utilisateur imprime, une nouvelle feuille est ajoutée au classeur, etc.
  • Utilisez la fonction MsgBox pour afficher un message lorsqu'un utilisateur doit remplir une condition avant de fermer un classeur Excel
  • Utilisez la fonction InputBox pour obtenir des informations de l'utilisateur

Exemple # 2: autoriser l'utilisateur à exécuter une autre procédure

Dans l'éditeur VBA, sélectionnez Insérer -> Nouveau module

Écrivez ce code dans la fenêtre Module (ne pas coller!):

Sous UserReportQuery ()

Dim UserInput As Long

Réponse atténuée en tant qu'entier

UserInput = vbYesNo

Answer = MsgBox ("Traiter le rapport XYZ?", UserInput)

Si Answer = vbYes Then ProcessReport

End Sub

Sous ProcessReport ()

MsgBox ("Merci d'avoir traité le rapport XYZ.")

End Sub

Enregistrez et revenez à l'onglet Développeur d'Excel et sélectionnez l'option «Bouton». Cliquez sur une cellule et attribuez la macro UserReportQuery au bouton.

Cliquez maintenant sur le bouton. Ce message doit afficher:

Cliquez sur «oui» ou appuyez sur Entrée.

Encore une fois, tada!

Veuillez noter que le sous-programme secondaire, ProcessReport, peut être n'importe quoi . Je vais vous montrer plus de possibilités dans l'exemple n ° 3. Mais d'abord ...

Comment fait-il cela?

Cet exemple s'appuie sur l'exemple précédent et contient de nombreux nouveaux éléments. Passons en revue les nouveautés:

  • Dim UserInput As Long: Dim est l'abréviation de «dimension» et vous permet de déclarer des noms de variables. Dans ce cas, UserInput est le nom de la variable et Long est le type de données. En clair, cette ligne signifie "Voici une variable appelée" UserInput ", et c'est un type de variable Long."
  • Dim Answer As Integer: déclare une autre variable appelée «Answer», avec un type de données Integer. En savoir plus sur les types de données ici.
  • UserInput = vbYesNo: affecte une valeur à la variable. Dans ce cas, vbYesNo, qui affiche les boutons Oui et Non. Il existe de nombreux types de boutons, en savoir plus ici.
  • Answer = MsgBox («Traiter le rapport XYZ?», UserInput): affecte la valeur de la variable Answer à une fonction MsgBox et à la variable UserInput. Oui, une variable dans une variable.
  • If Answer = vbYes Then ProcessReport: il s'agit d'une «instruction If», une instruction conditionnelle, qui nous permet de dire si x est vrai, alors faites y. Dans ce cas, si l'utilisateur a sélectionné «Oui», exécutez le sous-programme ProcessReport.

Quand pourrais-je l'utiliser?

Cela pourrait être utilisé de très nombreuses manières. La valeur et la polyvalence de cette fonctionnalité sont davantage définies par ce que fait le sous-programme secondaire.

Par exemple, vous avez peut-être un fichier qui est utilisé pour générer 3 rapports hebdomadaires différents. Ces rapports sont formatés de manière radicalement différente.

  • Problème: chaque fois qu'un de ces rapports doit être généré, un utilisateur ouvre le fichier et modifie la mise en forme et les graphiques; etc., etc. Ce fichier est largement édité au moins 3 fois par semaine, et cela prend au moins 30 minutes à chaque fois qu'il est édité.
  • Solution: créez 1 bouton par type de rapport, qui reformate automatiquement les composants nécessaires des rapports et génère les graphiques nécessaires.

Exemples du monde réel

  • Créer une boîte de dialogue pour que l'utilisateur remplisse automatiquement certaines informations sur plusieurs feuilles
  • Utilisez la fonction InputBox pour obtenir des informations de l'utilisateur, qui sont ensuite remplies sur plusieurs feuilles

Exemple # 3: Ajouter des nombres à une plage avec une boucle For-Next

Les boucles For sont très utiles si vous devez effectuer des tâches répétitives sur une plage de valeurs spécifique - tableaux ou plages de cellules. En anglais simple, une boucle dit «pour chaque x, fais y».

Dans l'éditeur VBA, sélectionnez Insérer -> Nouveau module

Écrivez ce code dans la fenêtre Module (ne pas coller!):

Sous-boucleExemple ()

Dim X As Integer

Pour X = 1 à 100

Plage ("A" & X) .Valeur = X

X suivant

End Sub

Enregistrez et revenez à l'onglet Développeur d'Excel et sélectionnez le bouton Macros. Exécutez la macro LoopExample.

Cela devrait arriver:

Etc, jusqu'au 100ème rang.

Comment fait-il cela?

  • Dim X As Integer: déclare la variable X comme un type de données Integer.
  • Pour X = 1 à 100: c'est le début de la boucle For. En termes simples, il indique à la boucle de se répéter jusqu'à ce que X = 100. X est le compteur . La boucle continuera à s'exécuter jusqu'à X = 100, s'exécutera une dernière fois, puis s'arrêtera.
  • Range ("A" & X) .Value = X: ceci déclare la plage de la boucle et ce qu'il faut mettre dans cette plage. Puisque X = 1 initialement, la première cellule sera A1, à quel point la boucle placera X dans cette cellule.
  • Next X: cela indique à la boucle de s'exécuter à nouveau

Quand pourrais-je l'utiliser?

La boucle For-Next est l'une des fonctionnalités les plus puissantes de VBA; il existe de nombreux cas d'utilisation potentiels. Il s'agit d'un exemple plus complexe qui nécessiterait plusieurs couches de logique, mais il communique le monde des possibilités dans les boucles For-Next.

Peut-être avez-vous une liste de tous les produits vendus dans votre boulangerie dans la colonne A, le type de produit dans la colonne B (gâteaux, beignets ou muffins), le coût des ingrédients dans la colonne C et le coût moyen du marché de chaque type de produit dans une autre feuille.

Vous devez déterminer quel devrait être le prix de détail de chaque produit. Vous pensez que ce devrait être le coût des ingrédients plus 20%, mais aussi 1,2% sous la moyenne du marché si possible. Une boucle For-Next vous permettrait de faire ce type de calcul.

Exemples du monde réel

  • Utilisez une boucle avec une instruction if imbriquée pour ajouter des valeurs spécifiques à un tableau distinct uniquement si elles remplissent certaines conditions
  • Effectuer des calculs mathématiques sur chaque valeur dans une plage, par exemple calculer des frais supplémentaires et les ajouter à la valeur
  • Parcourez chaque caractère d'une chaîne et extrayez tous les nombres
  • Sélectionnez au hasard un certain nombre de valeurs dans un tableau

Conclusion

Maintenant que nous avons parlé de pizza et de muffins et oh-ouais, comment écrire du code VBA dans des feuilles de calcul Excel, faisons un test d'apprentissage. Voyez si vous pouvez répondre à ces questions.

  • Qu'est-ce que VBA?
  • Comment configurer pour commencer à utiliser VBA dans Excel?
  • Pourquoi et quand utiliseriez-vous VBA?
  • Quels sont les problèmes que je pourrais résoudre avec VBA?

Si vous avez une bonne idée de la façon dont vous pourriez répondre à ces questions, alors cela a réussi.

Que vous soyez un utilisateur occasionnel ou un utilisateur expérimenté, j'espère que ce tutoriel a fourni des informations utiles sur ce qui peut être accompli avec juste un peu de code dans vos feuilles de calcul Excel.

Bon codage!

Ressources d'apprentissage

  • Programmation Excel VBA pour les nuls, John Walkenbach
  • Premiers pas avec VBA, documentation Microsoft
  • Apprendre VBA dans Excel, Lynda

Un peu de moi

Je suis Chloe Tucker, artiste et développeur à Portland, Oregon. En tant qu'ancienne éducatrice, je recherche continuellement l'intersection entre l'apprentissage et l'enseignement, ou la technologie et l'art. Contactez-moi sur Twitter @_chloetucker et consultez mon site Web à chloe.dev.