Bien débuter avec VBA E-Book

Rémy Lentzner

• Herausgeber: Remylent
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Französisch
• Veröffentlichungsjahr: 2021

Seitenzahl: 124

Beschreibung

Bienvenue dans la programmation des macros avec le langage VBA.
Vous gagnerez du temps en automatisant des tâches répétitives et en développant vos propres applications. Dans cet ouvrage, vous découvrirez progressivement comment écrire des procédures Sub en utilisant les objets principaux d’Excel, comme les cellules (cells), les feuilles (sheets) ou les classeurs (workbooks). Vous pourrez vous familiariser avec les propriétés, les verbes d’actions qu’on appelle des méthodes et les structures de contrôle. Vous apprendrez également à créer un formulaire utilisateur UserForm. 
Accessible à tous, ce manuel vous guidera pas à pas afin d’améliorer vos connaissances. Les exercices proposés peuvent être effectués sur toutes les versions d’Excel.
Bonne lecture.


À PROPOS DE L'AUTEUR


Rémy Lentzner est formateur en informatique depuis 1985. Spécialisé dans la maîtrise des outils bureautiques, il accompagne les entreprises dans la formation professionnelle de leurs salariés. Autodidacte, il a plusieurs ouvrages informatiques à son actif. En 2016, il crée les EDITIONS REMYLENT, diffuse et distribue lui-même ses livres.

Leseprobe

REMY LENTZNER

Bien débuter AVEC VBA

EDITIONS REMYLENT, Paris, 1ère édition, 2021

R.C.S. 399 397 892 Paris

25 rue de la Tour d’Auvergne - 75009 Paris

[email protected]

www.REMYLENT.FR

ISBN EPUB : 978-2-490275-63-2

Le Code de la propriété intellectuelle interdit les copies ou reproductions destinées à une utilisation collective. Toute représentation ou reproduction intégrale ou partielle faite par quelque procédé que ce soit, sans le consentement de l’auteur ou de ses ayants droit ou ayants cause, est illicite et constitue une contrefaçon, aux termes des articles L.335-2 et suivants du Code de la propriété intellectuelle.

Couverture : Anna Lentzner - www.lheuredelasieste.fr

Un immense merci à Anna pour la couverture

INTRODUCTION

Bienvenue dans la programmation VBA ou Visual Basic Application. Ce livre s'adresse aux personnes qui travaillent avec Excel (toutes versions) et qui possèdent une expérience quotidienne du tableur. En d'autres termes, vous savez faire des formules, utiliser les menus ou organiser des mises en page.

Vous avez sûrement remarqué certaines tâches répétitives dans votre travail quotidien. Par exemple, vous cachez régulièrement certaines colonnes, ou bien vous imprimez une partie d'un tableau tous les mois. Ou encore, vous filtrez les mêmes groupes d'information.

Ces répétitions peuvent être automatisées par un dispositif appelé Macros. Il s'agit d'enregistrer ce que vous faites à la main, avec le clavier ou avec la souris. Ce mécanisme est sensiblement identique à celui d'un enregistreur vocal avec lequel vous démarrez un enregistrement, vous parlez, vous arrêtez puis vous écoutez le résultat.

Une macro réalisera ce que vous effectuez régulièrement de la même façon. Il n'y a pas de limite et vous pouvez recommencez en cas d'erreur. VBA est le langage de programmation d'Excel avec lequel vous pouvez développer des applications personnalisées, et ainsi créer de nouvelles fonctionnalités qui manquent à votre travail quotidien.

VBA existe aussi dans les applications Word, Outlook et Powerpoint et permet une communication entre elles. Quand vous effectuez une macro, Excel crée le code de celle-ci en arrière plan que vous pouvez modifier à volonté.

La modification du code nécessite d'apprendre les rudiments du langage. Il est totalement inutile de tout connaître, mais certaines commandes sont essentielles et indispensables.

L'objectif de cet ouvrage est de vous aider à réaliser des macros et à manipuler le langage VBA, sa structure, ses mots-clés et ses commandes importantes. La plupart des exemples de cet ouvrage sont reproductibles avec toutes les versions d’Excel en français, mais à partir d’Excel 2007.

Ce livre est structuré en cinq chapitres.

Le chapitre 1 s’intéresse à la gestion des macros, en commençant par le menu Développeur puis par plusieurs exemples concrets. Vous étudierez également la notion de complément qui permet d'ajouter des fonctionnalités dans Excel.

Le chapitre 2 porte sur le langage VBA au quotidien avec la manipulation des cellules, des feuilles et des classeurs. Vous découvrirez la manière de contrôler par programme des applications extérieures comme Outlook, Powerpoint et Word.

Le chapitre 3 étudie les structures de contrôles, en particulier les boucles For...Next,Do...Loop et For...Each qui permettent de répéter des groupes de commandes. Vous étudierez les expressions conditionnelles comme If..End If, If...ElseIF ou encore Select...Case. Vous recherchez des informations avec la commande Find. D'autres exemples vous permettront de vous exercer avec des dates.

Le chapitre 4 expose la manière de gérer correctement les erreurs inévitables qui apparaissent au moment de la programmation. Vous apprendrez les techniques de Débogage, le Pas à pas, les fenêtres Espions et les Variables locales ainsi que le gestionnaire d'erreurs On Error Goto qui permet de capturer les erreurs d'exécution.

Le chapitre 5 termine cet ouvrage par la création d'un formulaire utilisateur Userform qui vous permet de développer des applications personnalisées en utilisant des contrôles comme des boutons poussoirs ou des zones de texte.

J’espère que la lecture de ce livre vous intéressera et vous permettra de progresser dans la gestion des macros et du langage VBA.

N'hésitez pas à me contacter à [email protected] si vous avez des remarques sur ce livre ou bien des questions.

Je ne manquerai pas de vous répondre.

Bonne lecture.

L’auteur

Table des matières

Chapitre 1 Créer des macros

1.1 Le menu Développeur

1.1.1 Les outils pour manipuler les macros

1.1.2 Le type XLSM et la sécurité des macros

1.1.3 Utiliser les références relatives

1.2 Atelier 1 : Créez votre première macro

1.2.1 Remettre à vide des zones remplies

1.2.2 Modifier une macro enregistrée

1.2.3 Le code de la macro

1.2.4 Exécuter une macro à partir d'un bouton poussoir

1.2.5 Apprendre le langage par l'exemple

1.3 Atelier 2 : Une macro de filtrage

1.3.1 Mise en place d'une base de données.

1.3.2 Une macro qui filtre des secteurs

1.3.3 Une macro qui filtre à partir d'une boîte de dialogue.

1.3.4 Une macro qui filtre à partir d'une cellule.

1.4 Atelier 3 : Une macro de mise en page et d’impression

1.5 Retour sur le classeur de macros personnelles

1.6 Les compléments

1.6.1 Les compléments COM déjà installés

1.6.2 Créer un complément personnalisé

Chapitre 2 VBA dans le quotidien

2.1 Le travail avec les cellules

2.1.1 L'instruction RANGE

2.1.2 La commande OFFSET(ligne,colonne)

2.1.3 Les lignes et les colonnes

2.1.4 Mais où est la dernière ligne ?

2.1.5 La commande CELLS(ligne,colonne)

2.1.6 Les propriétés des cellules

2.2 Le travail avec les feuilles

2.2.1 Les propriétés des feuilles

2.2.2 Agir sur les feuilles avec des méthodes

2.3 Le travail avec les classeurs

2.3.1 Les propriétés du classeur

2.3.2 Ouvrir et fermer un classeur

2.3.3 D'un classeur vers un autre

2.3.4 Avec des variables objets

2.3.5 Importer plusieurs feuilles dans une feuille

2.3.6 Importer un fichier de type texte

2.3.7 Exporter du contenu vers un fichier texte

2.3.8 Remplissage instantané avec FlashFill

2.4 Les événements worksheet et workbook

2.4.1 Contrôler l'activation d'une feuille

2.4.2 Contrôler l'activation d'une feuille

2.4.3 Demander un mot de passe avec Inputbox

2.4.4 Contrôler la saisie d'une valeur dans une feuille

2.4.5 Les paramètres des fonctions Msgbox et Inputbox

2.4.6 Contrôler un double-clic

2.4.7 Ouverture et fermeture d'un classeur

2.5 L'objet Application et le monde extérieur

2.5.1 Quelques propriétés de l'objet Application

2.5.2 Désactiver le menu contextuel

2.5.3 Contrôler Word à partir de VBA Excel

2.5.4 Contrôler PowerPoint à partir de VBA Excel

2.5.5 Contrôler Outlook à partir d'Excel sans pièce jointe

2.5.6 VBA envoie un mail avec une pièce jointe

2.6 Les commentaires et VBA

2.6.1 Modifier un commentaire

2.6.2 Modifier la forme du commentaire

2.6.3 Une image dans un commentaire

2.6.4 Extraire le texte des commentaires

2.6.5 Police et taille d'un groupe de commentaires

2.6.6 Colorier plusieurs commentaires

2.6.7 Supprimer tous les commentaires

Chapitre 3 Les structures de contrôle

3.1 Les boucles et les conditions

3.1.1 For...Next avec If ... End If

3.1.2 For...Next avec Select Case

3.1.3 For...Next avec If ... ElseIf

3.1.4 Boucler avec Do While...Loop

3.1.5 Boucler avec While ... Do Until

3.1.6 Rechercher avec la commande Find

3.1.7 Recherche de doublons

3.2 Des dates professionnelles

3.2.1 Dates de licenciement et ancienneté

3.2.2 Règlement de factures et jours ouvrés

Chapitre 4 La gestion des erreurs

4.1 Le débogage

4.1.1 Le mode pas à pas et les points d'arrêt

4.1.2 Réinitialiser le code après un plantage

4.1.3 La fenêtre Espions scrute les variables

4.1.4 La fenêtre Exécution

4.1.5 La fenêtre des variables locales

4.2 Le gestionnaire d'erreurs On error Goto

Chapitre 5 Le formulaire Userform

5.1 Création du formulaire utilisateur

5.2 Atelier : Réalisation d'un formulaire

5.2.1 Création d’une base Excel

5.2.2 Appel du formulaire

5.2.3 Un nom pour chaque contrôle

5.2.4 Programmation des contrôles

Chapitre 1 Créer des macros

Ce chapitre s'intéresse aux mécanismes qui permet de créer et d'exécuter des macros, par l'intermédiaire du menu Développeur. Vous apprendrez à modifier des macros tout en étudiant le code VBA (Visual Basic Application).

1.1 Le menu Développeur

Au moment de l'installation d'Excel, le menu Développeur (nécessaire à la manipulation des macros) n'est pas installé. Vous devez le faire vous-même et, une fois cette opération réalisée, vous constaterez l'apparition de plusieurs dispositifs très proches du domaine de la programmation. Par exemple, vous verrez des objets de formulaire (case à cocher, liste modifiable, boutons d'options, etc), le gestionnaire des compléments Excel et Com, et bien sûr le dispositif des macros.

Pour faire apparaître ce menu Développeur, suivez les instructions ci-dessous :

Fichier / Options / Personnaliser le rubanCochez la case Développeur à droite dans la boîte de dialogue.Terminez par le bouton OK.

Constatez l'apparition du nouveau menu comme le montre la figure 1.1 ci-dessous.

Figure 1.1 : Le menu Développeur

Le menu Développeur est structuré en plusieurs parties. La première, à gauche de l'écran, concerne la gestion des macros. La deuxième partie, au centre, permet de gérer les compléments dans Excel.

Un complément est un groupe de fonctions ou d'applications extérieures que l'on peut programmer ou insérer dans le tableur. Par exemple, le complément Cartes Bing de Microsoft facilite l'ajout de valeurs numériques sous forme graphique sur une carte du monde.

La troisième partie (bouton Insérer) s'intéresse aux contrôles que l'on peut insérer dans les feuilles de calcul ou bien dans les formulaires utilisateurs appelés UserForm.

Il s'agit essentiellement de cases à cocher, de boutons d'options, de listes modifiables et bien d'autres qui sont programmables. Les contrôles de formulaire sont plutôt simples à utiliser mais pauvres en propriétés.

Les contrôles ActiveX (norme Microsoft pour les objets) sont riches en propriétés et programmables par le langage Visual Basic pour l'application Excel.

Enfin, la dernière partie (à droite du menu) gère les dispositifs qui permettent de contrôler des documents dynamiques en relation avec un flux de travail. En d'autres termes, vous gérez des données de l'extérieur (des informations boursières par exemple) qui arrivent en flux et que vous souhaitez intégrer dans Excel en continu.

Cette mise en place s'effectue par l'intermédiaire du langage XML et par l'insertion d'un kit d'extension XML. Pour dire les choses autrement, il s'agit de développer des applications qui mélangent du VBA avec des balises HTML, à l'intérieur d'une structure XML.

1.1.1 Les outils pour manipuler les macros

Plusieurs outils du menu Développeur vous permettent de gérer très facilement la création, la modification et la suppression des macros.

L'icône Visual Basic vous permet d'accéder à l'environnement de développement intégré du langage VBA. C'est ici que vous manipulerez le code écrit automatiquement par l'enregistreur de macros.

En cliquant sur cette icône, vous faites apparaître une fenêtre coupée en deux, la partie gauche proposant une liste de feuilles qui représentent le projet VBA, avec des propriétés situées dans la partie inférieure.

La partie droite de la fenêtre peut être vide ou bien comporter des lignes de code. C'est dans cet environnement que l'on programme des applications qui interfèrent directement avec les données présentes dans les feuilles de calcul.

Quand vous fermez cette fenêtre, vous revenez immédiatement dans l'environnement d'Excel.

On peut basculer d'Excel à VBA et réciproquement par la composition des touches ALT F11.L'icône Macros fait apparaître une boîte de dialogue qui affiche les macros déjà créées.Le bouton Exécuter lance tout simplement les instructions stockées dans une macro pré-enregistrée.Le bouton Pas à pas détaillé ouvre l'environnement de développement intégré et affiche une surbrillance en jaune sur la première instruction de la macro.Utilisez la touche F8 pour exécuter la ligne suivante. Cette opération exécute le code en pas à pas, c'est à dire ligne par ligne. On dit que la macro se trouve en mode débogage.Le bouton Modifier ouvre la procédure que vous pouvez changer à cet emplacement.Le bouton Supprimer supprime la macro.Le bouton Options vous permet de définir une touche de raccourci qui exécutera la macro à partir du clavier.Le lien Enregistrer une macro ouvre une boîte de dialogue très importante. Vous devez indiquer un nom de macro en respectant certaines règles : pas d'espace dans le nom, préférez les noms simples et explicites, ne commencez pas le nom par un chiffre. Vous pouvez également indiquer une touche de raccourci.

Une macro est une procédure qui commence par le mot Sub et se termine par le mot End Sub.

La liste Enregistrer la macro dans vous permet de stocker vos futures instructions soit dans le classeur en cours, soit dans un classeur de macros personnelles, soit dans un nouveau classeur.

Un classeur de macros personnelles est un classeur spécifique Personal.xlsb qui est une sorte de conteneur public de macros. Il est caché par défaut à l'ouverture d'Excel. Les macros qu'il contient sont accessibles à partir de n'importe quel classeur ouvert. Il est généralement stocké dans le répertoire XLSTART qui permet de démarrer automatiquement des classeurs dès le démarrage d'Excel. La position de ce dossier XLSTART varie en fonction des versions d'Excel.

Si vous enregistrez une macro dans ce classeurPersonal.xslb, Excel le créera automatiquement la première fois. Il est aussi possible de le rendre visible par la commande Affichage/Afficher.

Les macros (ou procédures) sont visibles dans l'environnement de développement au sein du projet VBAProject (Personal.xlsb). Pour supprimer ce classeur de macros personnelles, il faut fermer Excel, trouver le répertoire XLSTART, puis le supprimer à la main.

Pour résumer, si vous avez besoin d'une macro qui doit être disponible pour plusieurs classeurs, stockez-la dans le classeur de macros personnelles.

En général, les macros sont stockées dans un conteneur de macros qu'on appelle Module. Excel stocke les macros au fur et à mesure dans les modules. Un numéro est donné aux différents modules, comme Module1, Module2, etc.

1.1.2 Le type XLSM et la sécurité des macros

Pour développer des macros dans Excel, vous devez enregistrer le classeur avec un format spécifique XLSM, c'est à dire un classeur prenant en charge les macros.

Par défaut, lorsque vous enregistrez un classeur, le type de format est XLSX. Ce format a été mis en place à partir de la version Excel 2007 et pour les versions précédentes, le format était .XLS.

Si vous développez des macros dans un classeur de type XLSX, au moment de l'enregistrement du classeur, Excel vous demandera de l'enregistrer dans le format adéquat. D'une manière générale, les macros sont désactivées par souci de sécurité et vous ne pouvez pas les créer sans modifier un paramètre particulier.

Pour permettre la création d'une macro, cliquez sur le petit triangle jaune (Sécurité des macros) dans le menu Développeur, vous ferez apparaître alors la boîte de dialogue Centre de gestion de la confidentialité. Cliquez ensuite sur le lien Paramètres des macros, vous verrez quatre paramètres décrits dans la liste ci-dessous :

Désactivez toutes les macros sans notification. Les macros et les alertes de sécurité associées aux macros ne fonctionnent pas.Désactivez toutes les macros avec notification. Les macros sont désactivées, mais les alertes de sécurité s'affichent si des macros sont présentes.Désactivez toutes les macros à l'exception des macros signées numériquement. Si une macro est signée numériquement par un éditeur approuvé, elle est exécutée si cet éditeur est approuvé.Activer toutes les macros (non recommandé : risque d'exécution de code potentiellement dangereux). Toutes les macros s'exécutent.Accès approuvé au modèle d'objet du projet VBA. Vous pouvez interdire ou non l'accès par programme au modèle objet VBA. Ce paramètre est utilisé pour rendre plus difficile l'accès à VBA par un programme malveillant.