Macros-commandes VBA/Version imprimable

Macros-commandes VBA

Une version à jour et éditable de ce livre est disponible sur la Wikiversité,
une bibliothèque de livres pédagogiques, à l'URL :
http://fr.wikiversity.org/wiki/Macros-commandes_VBA

Vous avez la permission de copier, distribuer et/ou modifier ce document selon les termes de la Licence de documentation libre GNU, version 1.2 ou plus récente publiée par la Free Software Foundation ; sans sections inaltérables, sans texte de première page de couverture et sans Texte de dernière page de couverture. Une copie de cette licence est inclue dans l'annexe nommée « Licence de documentation libre GNU ».

Les Macros Excel

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Introduction

La Macro-commande, le langage VBA, ces termes vous font peur ? Oui ? Et bien… Cette leçon est faite pour vous !!! Nous allons vous porter et vous encourager à vous servir de l’un des outils les plus intéressants développés depuis bien longtemps par Microsoft : la programmation VBA.

Définition

Une macro-commande, au sens informatique, est une série d'instructions qui sont exécutées les unes après les autres (toujours dans l’ordre séquentiel correspondant au rang de leur ligne de texte). La macro possède en général de nombreuses propriétés dont

• un nom qui permet sa réutilisation
• un commentaire qui permet sa compréhension
• plusieurs lignes de code informatique qui permettent chacune une action

Les lignes de texte contenant des ensembles cohérents d’instructions VBA (Visual Basic Application) sont appelées code du programme. Le codage (ou la programmation) désigne le fait de fabriquer ces lignes de texte de 2 manières complètement différentes :

• L’utilisateur béotien (en général non informaticien enregistre les lignes de code voulues via un système de magnétophone qui traduit en lignes d'instructions (plus exactement en code VBA) toutes ses interactions avec le logiciel grâce à un outil unique et magique : l’enregistreur de macros. En effet, la majorité des utilisateurs du tableur EXCEL redoute la complexité relative du langage VBA et tarde à se lancer dans la programmation, pensant devoir apprendre au préalable les fondamentaux prérequis pour le développement de logiciel.
• Le concepteur (en général programmeur confirmé VBA) crée et édite directement les lignes de code voulues à l'aide d'un outil de traitement de texte spécialisé

Remarque

Cette leçon a été réalisée pour expliquer l’utilité de la Macro-commande sur tableur de la façon la plus ludique et compréhensible possible. Ainsi, le fil conducteur de ce cours sera d’expliquer graduellement comment utiliser les Macro-commandes de votre tableur favori.

Historique

La Macro-commande pour tableur, exploitant le langage VBA a été créé en 1995 par Microsoft sous la version Excel 5, sa finalité : transformer les actions effectuées par l’utilisateur ou le logiciel en instructions informatiques VISUAL BASIC.

Exemple :

• Phase 1 : l'utilisateur démarre un enregistrement de la macro nommée macroQuiSouligne puis sélectionne des cellules, les souligne, arrête l'enregistrement
• Phase 2 : l'utilisateur sélectionne d'autres cellules puis appelle la macro nommée macroQuiSouligne, puis l'exécute, les nouvelles cellules sélectionnées seront alors soulignées

Utilité des macros VBA

Définition

Les macros VBA dédiées à Excel (ou la programmation VBA) permettent de compléter des outils bureautiques déjà très performants et puissants (EXCEL, WORD, ACCESS, POWERPOINT, OUTLOOK, PROJECT, VISIO,...). Ci-dessous quelques exemples de ces compléments :

• Enregistrer des actions répétitives (une suite de tâches toujours effectuées automatiquement ou manuellement dans le même ordre)
• Mémoriser ces processus opératoires d'utilisations complexes, longues et répétitives
• Répéter ce qui a été enregistré précédemment (grâce au rappel du nom dédié à la macro)
• Améliorer ou créer de nouvelles commandes pour l’application hôte
• Améliorer ou créer de nouvelles fonctions inexistantes dans l’application hôte
• Créer des interfaces spécialisées (formulaires d'affichage ou de saisie)
• Personnaliser un logiciel (ruban ou options EXCEL)
• Faire interagir plusieurs documents bureautiques entre eux (par exemple : plusieurs classeurs EXCEL)
• Faire interagir plusieurs applications bureautiques entre elles (par exemple : piloter EXCEL avec POWERPOINT)
  • WORD
  • POWERPOINT
  • OUTLOOK
  • ACCESS
  • PROJECT
  • VISIO
  • ...
• Ordonner, organiser les commandes entre elles

Un heureux mariage

Un Langage VISUAL BASIC

Visual Basic est le langage informatique développé dans les années 1980 par Microsoft pour banaliser la programmation. Les informaticiens puristes trouvent à VB un défaut majeur, il n’est pas un langage de programmation orienté objet. Cette affirmation est fausse, VBA comme la plupart des langages modernes ou WEB (PHP, RUBY, PHP, …) permet de créer ses propres objets (données, méthodes et instances) et donc de programmer des actions sur tout objet informatique accessible. Il permet aussi de manipuler des variables programmes non objets, ce qui en fait un langage de programmation à la fois procédural et à la fois orienté objet. L'intérêt majeur de la programmation Objet est qu’il n’est pas utile de connaître la composition et le fonctionnement d'un objet pour l’utiliser, seul son interface utilisateur (en général ses propriétés modifiables via VBA) doit être maîtrisée. Par analogie, lors de l’utilisation d'un téléviseur, il n’est pas nécessaire d’en connaître les composants et leur fonctionnement, seule son interface utilisateur (en général la télécommande) doit pouvoir être maniée.

Un Logiciel EXCEL

Excel est le logiciel tableur développé dans les années 1980 par Microsoft pour effectuer tout type de calcul et afficher tout type de graphique. Ce logiciel, à ses débuts, était souvent cité comme le plus mauvais des logiciels de type Tableurs ; après quelques années seulement et un travail d'amélioration gigantesque, il est devenu le logiciel de référence des tableurs. Tant et si bien que l’on peut parler de situation de monopole, vu son taux de couverture de marché avoisinant les 100%. Le logiciel Excel est également l'hôte dont le VBA a besoin pour pouvoir être embarqué.

Un Langage VB + Un Logiciel Excel = Une Visual Basic Application

Si vous utilisez le VBA, vous travaillez avec des objets. Le modèle Excel (comme le modèle Word ainsi que tous les autres modèles des logiciels MICROSOFT) possède des collections, des objets, des méthodes, des propriétés et réagit aux événements déclenchés par l'utilisateur.

Si vous vous servez directement d’Excel comme application hôte, vous ne devez pas déclarer d'objet Application dans le code VBA, vous ne devrez le faire que si vous utilisez une autre application hôte que Excel.

Certaines instructions VBA sont donc spécifiques à Excel, celles qui manipulent des objets EXCEL telles que l’affectation de formule à une cellule, l’ouverture de classeur, …

Le développeur communique avec Excel via la bibliothèque d'objets

La bibliothèque d'objet contient tous les objets utilisés par le tableur : classeur, feuille, colonne, cellule … ainsi que toutes les opérations possibles sur ces objets. Par exemple : Application, Worksheets, Workbooks, Selection, Range, …

 SUB activerFeuille()
  Worksheets(1).Activate
 END SUB

Le développeur communique avec Excel via les macros

Les modules de code contiennent toutes les macros utilisés par le tableur : déclarations, procédures et fonctions

 SUB enregistrerComme()
  DIM fichier as String
  fichier = "C:\excel\MonDouble.xls"
  ActiveWorkbook.SaveCopyAs fichier
 END SUB

Présentation du langage

Un exemple de fenêtre de programme réalisé en VB.

L'objectif de ce cours est d'enseigner la programmation d’applications en Visual Basic (alias "la suite VB"), en réalisant des programmes qui fonctionnent sous Microsoft Windows.

En effet ce langage est considéré comme simple et rapide, avec un débogueur pas à pas (F8) permettant de suivre le programme au plus près pendant son exécution.

Certains autres langages s'en sont d'ailleurs inspirés et assurent dans la plupart des cas une compatibilité minimum avec VBA, comme OOo basic et Gambas. Les macros de chacun de ces langages sont stockées dans le document et peuvent être copiées dans d'autres, et même parfois converties en OpenOffice basic.

Visual Basic for Applications (VBA) est une implémentation de Microsoft Visual Basic qui est intégrée dans toutes les applications de Microsoft Office (ex : Microsoft Word, Microsoft Excel, ...), dans quelques autres applications Microsoft comme Visio et au moins partiellement dans quelques autres applications non Microsoft comme AutoCAD et WordPerfect. VBA, de plus, remplace et étend les capacités des langages macro spécifiques aux plus anciennes applications comme le langage WordBasic intégré à une ancienne version du logiciel Word. VBA peut aussi être utilisé pour contrôler la quasi-totalité de l'IHM des applications hôtes, ce qui inclut la possibilité de manipuler les fonctionnalités de l'interface utilisateur comme les menus, les barres d’outils et le fait de pouvoir personnaliser les boîtes de dialogue et les formulaires utilisateurs.

Installation

Visual Basic est fourni avec le pack Microsoft Office, ce qui a le mérite de fournir des interfaces graphiques compatibles. Il est alors stocké dans différents types de fichiers .bas, .cls, .xls, .xlsm, .doc, .docm, .ppt, .pptm, .mdb, .mde. Il est recommandé de configurer ces logiciels en y ajoutant l'onglet "Développeur" dans le menu Fichier\Options\Personnaliser le ruban, et d'activer toutes les macros par défaut dans Fichier\Options\Centre de gestion de la confidentialité\Paramètres du Centre de gestion de la confidentialité\Paramètres des macros. Cet onglet permet par exemple d'enregistrer une macro reproduisant les opérations effectuées manuellement.

Il est toutefois également disponible gratuitement avec le compilateur Visual Studio express. Dans ce cas il sera présent dans les fichiers .vb, .sln, .dsw, .vcw, .vbproj. Une fois ce dernier installé il est recommandé d'afficher les numéros de ligne pour pouvoir se repérer plus facilement, dans le menu Tools\Options\Text Editor\All Languages\Line numbers.

Chargement automatique de modules

Le format .xlsb sert à stocker des scripts VBA sans afficher de feuilles. Il peut ainsi être chargé silencieusement automatiquement à chaque ouverture de fichiers Excel. Pour ce faire, il suffit de :

• Enregistrer des macros dans les modules d'un .xlsb.
• Le placer dans C:\Users\%USERNAME%\AppData\Roaming\Microsoft\Excel\XLSTART\.
• Cliquer dans l'onglet "Affichage" sur "Masquer".

Chargement automatique de bibliothèques

Pour charger une bibliothèque de scripts .xla, .xlam ou .xll, il faut :

• Copier le fichier dans C:\Users\%USERNAME%\AppData\Roaming\Microsoft\AddIns\. Son code peut être protégé par un mot de passe, mais il s'exécutera quand même si on l'appelle.
• Dans l'éditeur de macros, Outils, Références, cliquer sur Parcourir pour accéder au fichier, et ainsi le faire apparaitre coché en haut de la liste.

Annexes

Bibliographie

• Greg Harvey et John Walkenbach, 2013, Excel 2013 et VBA Pour Les Nuls, FIRST, Hoboken
• Amelot M., 2011, VBA Excel 2010, ENI, St Herblain

Liens internes

• Leçon Tableur EXCEL

Liens externes

• FAQ developpez VBA
• Une introduction au Visual Basic

Pour démarrer

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Un Onglet masqué en standard par EXCEL

Pour des raisons de sécurité, vous ne pourrez pas vous lancer dans la programmation VBA sans avoir l'onglet Développeur (masqué en standard par EXCEL) à votre disposition ni sans autoriser l’utilisation des macros. En effet, il est possible de travailler sur EXCEL (ou tout autre produit bureautique) sans vouloir exécuter de macro. On désactive aussi les macros quand un fichier bureautique EXCEL est soupçonné de dysfonctionnement ou d’être porteur de virus.

Étape 1 : Affichage de l'environnement de développement	Personnaliser le ruban via les options EXCEL pour afficher l’onglet développeur (case à cocher)
Étape 2 : Autorisation d’utilisation des macros	Personnaliser les options EXCEL pour activer les macros (autre case à cocher)
Étape 3 : Bravo !	L'onglet "Développeur" apparaît dans le ruban, place à la programmation !

Affichage de l'onglet développeur : cet affichage supplémentaire d’un onglet sur le ruban va permettre au concepteur de gérer le code VBA par simple action sur les différentes commandes de l’onglet : code, contrôle, xml, … Sans cet affichage, l’accès aux modules et codes Visual Basic n’est pas possible.

Activation des macros : cette activation va permettre à l’utilisateur d’exécuter le code VBA contenu par les macros par simple appel ou action sur les différentes commandes EXCEL (ou tout autre produit bureautique) : code, contrôle, xml, … Sans cette autorisation complémentaire l’exécution des macros et codes Visual Basic est interdite.

Remarque

dans l'onglet Fichier cliquer sur Options, une fois que la fenêtre option apparue, cliquer sur Personnaliser le ruban puis cocher développeur ; EXCEL 2007 ==> la même action mais démarrée depuis le bouton Office

Intérêts des macros pour les novices

La macro permet principalement d’automatiser des tâches répétitives. L’une des premières utilités de cette automatisation est de demander au logiciel d’effectuer les instructions que l’utilisateur aura préalablement historisées dans une macro. Ainsi, si vous rappelez votre macro archivée, le logiciel exécutera l’ensemble des instructions préenregistrées, gain de temps et de productivité garanti.

Cet aspect théorique ne vous suffit pas pour parcourir notre leçon ? Pas de problème ? Nous vous avons alors répertorié quelques tâches possibles avec cet outil :

• Faire le lien entre plusieurs outils bureautiques (par exemple appeler une feuille EXCEL lors d’un diaporama POWERPOINT)
• Créer un formulaire de saisie via EXCEL (par exemple réaliser une enquête de satisfaction sur votre nouvel outil de publipostage)
• Poser une question à un utilisateur EXCEL via une boite de dialogue (par exemple, demander quel exercice comptable il veut travailler)
• Simuler des outils de gestion EXCEL (par exemple consolider plusieurs classeurs EXCEL de reporting envoyés par les différentes filiales de l'entreprise)
• Créer les nouvelles fonctions EXCEL dont vous avez besoin (par exemple inventer une fonction standard de mise en page avant impression)

N'hésitez pas !

Nous vous souhaitons à présent une bonne lecture de la leçon et restons à votre disposition pour tout complément d’information relatif à cette leçon. Mais n'oubliez pas !!!

• De nombreux utilisateurs non informaticiens ont pris un plaisir certain à créer leurs propres programmes, vous serez peut-être l'un d'eux ou l'une d’elles !!!
• Imaginer l’immense potentiel que vous représenterez vis-à-vis de votre employeur actuel ou futur grâce à votre connaissance experte du tableur !!!
• Après avoir sillonné les chapitres de début de leçon et les exercices qui les étendent, vous pourrez aborder les chapitres de fin et les annexes qui vous amèneront de manière à cette expertise du tableur !!!

Dans un premier temps, nous allons voir la structure de base d’un projet Visual Basic.

Projet

Définition

Un projet Visual Basic est l’ensemble des fichiers écrits en VB nécessaires à la compilation d'une application ou d’un formulaire.

Un projet VBA EXCEL peut être composé de différents éléments :

• Un ensemble de propriétés du projet (nom, version, description, icône...)
• Un ensemble de feuilles de tableurs contenant des lignes et des colonnes formant des cellules
• Un ensemble de formulaires, encore appelés userform contenant des contrôles activex
• Un ensemble de modules de code comprenant des lignes écrites en Visual Basic

Module

Définition

Il existe deux types de modules :

• Les modules standards qui sont un regroupement de différentes fonctions, procédures et variables au sein d’un même fichier ayant pour extension .bas
• Les modules de classe qui représentent un type d'objet personnalisé sous la forme d’un fichier .cls

Un module VBA EXCEL peut être composé de différents éléments :

• Un ensemble de propriétés du module(nom ...)
• Un ensemble de constantes et de variables VBA, outils de stockage de valeurs (entier, chaine, date, ...)
• Un ensemble de procédures et de fonctions comprenant
  • Un ensemble de constantes et de variables VBA, outils de stockage de valeurs (entier, chaine, date, ...)
  • Un ensemble d'instructions VBA, outils de gestion du programme (affectation, débranchement, boucle, ...)

Formulaire

Définition

Un formulaire Visual Basic est une fenêtre du système d'exploitation qui permet l’affichage d'informations à l'écran.
Il est défini dans un fichier d'extension .frm et est représenté dans le code par l’objet Form

Un formulaire VBA EXCEL peut être composé de différents éléments :

• Un ensemble de propriétés du formulaire (nom, titre, couleur, visibilité, image de fonds ...)
• Un ensemble de contrôles ActiveX, outils de communication avec l'utilisateur (bouton, case à cocher, boite de saisie, ...)
• Un ensemble de constantes et de variables VBA, outils de stockage de valeurs (entier, chaine, date, ...)
• Un ensemble de procédures et de fonctions comprenant
  • Un ensemble de constantes et de variables VBA, outils de stockage de valeurs (entier, chaine, date, ...)
  • Un ensemble d'instructions VBA, outils de gestion du programme (affectation, débranchement, boucle, ...)
• Un ensemble de procédures de gestion d’événements VBA, outils de gestion des événements liés à un contrôle ActiveX (click, focus, ouverture, ...)

Contrôles

Définition

Un contrôle, ou composant ActiveX, est un élément indépendant pouvant être utilisé dans un formulaire

Les contrôles peuvent être :

• ceux de base (bouton, zone de texte, image...)
• développés par un tiers et incorporés au projet
• un contrôle utilisateur défini dans le projet

Annexes

Bibliographie

• Amelot M., 2011, VBA Excel 2010, ENI, Saint Herblain
• John Walkenbach, 2013,Programmation Vba Excel 2010 Et 2013 Pour Les Nuls, FIRST, Hoboken

Liens externes

• http://www.excel-pratique.com/fr/vba/premiere_macro.php
• « http://www.microapp.com/extrait_html_8851_vba_pour_excel_2010.html » ^{(Archive • Wikiwix • Que faire ?)}. Consulté le 2017-07-23

Architecture

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Introduction

Le langage VBA manipule différents concepts : des variables, des procédures, des fonctions, des affectations, des instructions séquentielles, des conditions, des boucles... Ces concepts sont représentés dans le code par de simples lignes de texte :

• les procédures et fonctions organisent les objets sur lesquels le travail repose ;
• les variables précisent les objets sur lesquels le travail repose ;
• les instructions animent les objets sur lesquels le travail repose ;
• les conditions testent les objets sur lesquels le travail repose ;
• les boucles automatisent le travail.

Structures de base

Les procédures

Définition : une procédure effectue un traitement (exécute une série d'instructions représentée par des lignes).

Structure :

Début
 déclarations de variables
 séquences d'actions
Fin

Exemple :

 ' Cette macro affiche bonjour 
 Sub afficherMessageBonjour( )
  MsgBox "Bonjour à tous !"
 End Sub

• Appel de la procédure : afficherMessageBonjour => Résultat affiché à l’écran : Bonjour à tous !

Les fonctions

Définition : une fonction effectue un traitement (exécute une série d'instructions) et de plus retourne une valeur typée à la fin de ce traitement, cette valeur pouvant être exploitée par une autre procédure, fonction ou application hôte

Structure :

Début
 déclarations de variables
 séquences d'actions
 renvoi d'une valeur
Fin

Exemple :

 ' Cette fonction renvoie le carré d'un nombre passé en paramètre 
 Function renvoieUniquementUnCarre(monNombre as Long) As Long
  renvoieUniquementUnCarre = monNombre * monNombre 
 End Function

• Appel de la fonction : monCarre = renvoieUniquementUnCarre(10) => Résultat : la variable monCarre prend la valeur 100

Les variables

• Définition : les variables sont des boites nommées de stockage de valeurs qui évolueront au fur et à mesure que le programme se déroule.
• Déclaration : pour déclarer une variable, on écrit : DIM maVariable AS type où type précise l'objectif d'utilisation de la variable (INTEGER pour un nombre, STRING pour un texte, DATE pour une date...).

Une variable est une sorte de boîte qui va contenir une valeur. Elle peut être utilisée à tout moment en fonction de la portée que l’on souhaite lui attribuer. Il existe différents types de portée des variables, nous en retiendrons deux :

• Une variable globale est une variable que l’on peut utiliser dans tous les modules.
• Une variable locale est une variable déclarée dans une procédure d'un module.

Déclarer une variable

La déclaration d'une variable n’est pas obligatoire, mais elle est vivement recommandée.

Exemple ==> Dim monAge As Integer

• Dim : instruction de déclaration de la variable ;
• monAge : nom de la variable ;
• As: déclaration du type de la variable ;
• Integer : type de la variable choisi.

Nommer une variable

Le nom des variables doit :

• commencer par un caractère alphabétique ;
• ne pas excéder 255 caractères ;
• ne pas contenir de caractères spéciaux (le caractère underscore _ est accepté).

Exemple ==> Dim LeçonVBA As String.

Typer une variable

Pour aller plus loin, voir : Les types en VB.

Type de donnée	Plage
Byte (Numérique)	Nombre entier de 0 à 255.
Integer (Numérique)	Nombre entier de -32 768 à 32 767.
Long (Numérique)	Nombre entier de - 2 147 483 648 à 2 147 483 647.
Currency (Numérique)	Nombre à décimale fixe de -922 337 203 685 477.5808 à 922 337 203 685 477.5807.
Single (Numérique)	Nombre à virgule flottante de -3.402823E38 à 3.402823E38.
Double (Numérique)	Nombre à virgule flottante de -1.79769313486232D308 à 1.79769313486232D308.
String (Texte)	Texte.
Date (Date)	Date et heure.
Boolean	True (vrai) ou False (faux).
Object	Objet Microsoft.
Variant	Tout type de données (type par défaut si la variable n’est pas déclarée).

Exemple ==> Dim monAge As Integer (permet l'affectation monAge = 22, interdit l'affectation monAge = "MOI" Exemple ==> Dim monNom as String (permet l'affectation monNom = "MOI")

Les opérateurs

Les opérateurs sont, en informatique (comme en mathématiques ou en pratiques EXCEL) des outils identifiés par des symboles permettant des calculs ou des comparaisons entre des opérandes (appelés variables en VBA). Voici les différents opérateurs de VBA :

Opérateurs	Symboles	Utilités
Relationnels	"=,>,<,>=,<= , <>"	permettent d’effectuer des tests et de déclencher un traitement en fonction du résultat. Par exemple : si maDate >"20/01/2020" alors je fais quelque chose… sinon je fais autre chose… finsi (fin de comparaison)
Arithmétiques	"+,-,*,/,mod"	permettent d’exécuter des opérations de calcul. Par exemple : monTotal = (100+50)/3, monReste = 100 Mod 24
Affectations	"="	permettent d’effectuer des opérations de rangement dans des variables monNom = "Martin" => Résultat : monNom prend la valeur "Martin"
Logiques	"not, or, xor, and, eqv"	permettent d’enchainer des opérations de comparaison. Par exemple : si a and b, not v
Concaténations	"&"	Permettent d’effectuer des opérations de groupage de chaînes de caractères. Ex : nomComplet="Jules" &"-" & "Martin" => Résultat : nomComplet prend la valeur "Jules-Martin"

Les instructions de base

Pour aller plus loin, voir : Manipulation des variables.

Les commentaires

Les commentaires sont, en informatique, des lignes (ou portions de lignes) du code source ignorées par le compilateur ou l’interpréteur, car ils ne sont pas nécessaires à l’exécution du programme. Ils représentent un outil indispensable pour décrire de manière détaillée le traitement d'une macro. Les commentaires peuvent soit suivre une instruction sur la même ligne, soit occuper une ligne entière. Ces deux cas sont illustrés par le code suivant :

 ' Ceci est un commentaire commençant en début de ligne
 monPrenom = "Bilbo"   ' Ceci est un commentaire commençant en fin de ligne

Les affectations

Le langage VBA, comme tous les langages informatiques ou automatiques, range des valeurs dans des variables, il utilise l'opérateur d'affectation "=".

 age = 12
 message = "coucou"
 anniversaire = "12/12/2014"

Ici, la variable age recevra la valeur 12, la variable message recevra la valeur "coucou", la variable anniversaire recevra la valeur "12/12/2014".

Les instructions séquentielles

Le langage VBA, comme tous les langages informatiques ou automatiques, exécute les instructions une par une

 
 Range("E6").Select
 age = 12
 ActiveCell.FormulaR1C1 = age

Ici, la cellule E6 de la feuille courante sera sélectionnée, puis la variable age recevra la valeur 12, puis cette valeur 12 sera écrite dans la cellule E6.

Les instructions conditionnelles

Pour aller plus loin, voir : Conditionnelles et boucles.

Le langage VBA, comme tous les langages informatiques ou automatiques, exécute ou non certaines instructions uniquement après un test.

 IF (age = 18) THEN Range("E6").Value = "Vous êtes majeur(e)"

Ici, si la variable age est égale à 18, la cellule E6 recevra le libellé "Vous êtes majeur(e)".

Les boucles

Le langage VBA, comme tous les langages informatiques ou automatiques, sait exécuter plusieurs fois certaines instructions.

 FOR age = 1 to 18
  IF (age < 18) THEN Range("E" & age).Value = "Vous êtes mineur(e)"
 NEXT age

Ici, tant que la variable age sera inférieure à 18, les cellules E1 à E17 recevront le libellé "Vous êtes mineur(e)".

Annexes

Bibliographie

• Greg Harvey et John Walkenbach, 2013, Excel 2013 et VBA Pour Les Nuls, FIRST, Hoboken
• Amelot M., 2011, VBA Excel 2010, ENI, St Herblain
• Bidault M., 2000, Excel et VBA, CAMPUS, Paris

Liens internes

• Multiplication : exercice simple de codage via l'éditeur de texte VBE

Liens externes

• Apprentissage VBA via films
• Formation VBA pour Excel

Les types en VB

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Différents types

Programmer, c’est avant tout manipuler des variables, qui peuvent être de plusieurs types selon les données que l’on stocke dedans. Il est primordial de les avoir à l'esprit, car par exemple si on compare un nombre et un texte (ex : 1 < "2"), le résultat pourra être le contraire de celui attendu.

Il existe ainsi en VB de nombreux types, dont les plus courants sont résumés ci-dessous :

Types VB les plus couramment rencontrés

Type	Description	Valeurs possibles	Encombrement mémoire	Fonction de conversion
Byte	Octet	0 à 255	1 o	CByte()
Boolean	Booléen	True (vrai, -1) ou False (faux, 0)	2 o	CBool()
Integer	Entier	−32 768 (soit $-2^{15}$) à 32 767 ($2^{15}-1$)	2 o	CInt()
Long	Entier long	−2 147 483 648 (soit $-2^{31}$) à 2 147 483 647 ($2^{31}-1$)	4 o	CLng()
Single	Variable à virgule flottante : simple précision	de -3.402823 $10^{38}$ à -1.401298 $10^{-45}$ pour les nombres négatifs de 1.401298 $10^{-45}$ à 3.402823 $10^{38}$ pour les nombres positifs	4 o	CSng()
Double	Variable à virgule flottante : double précision	de -1.79769313486232 $10^{308}$ à -4.94065645841247 $10^{-324}$ pour les nombres négatifs de 4.94065645841247 $10^{-324}$ à 1.79769313486232 $10^{308}$ pour les nombres positifs	8 o	CDbl()
String	Chaîne de caractères (texte unicode)	*	< 255 o	Cstr()
Currency	Monnaie		8 o	CCur()
Date	Date du calendrier grégorien	Par défaut la commande Now() renvoie la date actuelle sous la forme 22/06/2012 17:23:58. Mais il est modifiable avec la commande Format(Now(), "yyy-mm-dd").	8 o	CDate()
Decimal	Flottant signé		14 o	CDec()
Variant	Ce type permet de s'adapter à n’importe quel autre type, mais au prix d'une consommation de mémoire importante. Il s'agit du type par défaut des variables non déclarées.		16 o	CVar(), CVErr()

Détermination des types

Pour déterminer le type d'une variable, il existe la fonction TypeName() :

MsgBox ("La cellule 1 contient un " & TypeName(Cells(1,1).Value))

On peut aussi utiliser des fonctions booléennes de test :

Fonction	VB 6	VBA	VBScript	VB .NET	T-SQL
IsArray	Oui	Oui	Oui	Oui	Non
IsDate	Oui	Oui	Oui	Oui	Oui
IsDBNull	Non	Non	Non	Oui	Non
IsEmpty	Oui	Oui	Oui	Non	Non
IsError	Oui	Oui	Non	Oui	Non
IsMissing	Oui	Oui	Non	Non	Non
IsNothing	Non	Non	Non	Oui	Non
IsNull	Oui	Oui	Oui	Non	Oui
IsNumeric	Oui	Oui	Oui	Oui	Oui
IsObject	Oui	Oui	Oui	Non	Non
IsReference	Non	Non	Non	Oui	Non

Déclaration des types

Les types des variables sont donc déclarés grâce à la commande dim, par exemple pour stocker un nom :

Dim nom as String

Tableaux

Les tableaux se déclarent par le type de leur contenu :

Sub ordinaux()
    Dim liste(2) As String  ' Tableau de trois éléments texte
    
    liste(0) = "premier"
    liste(1) = "deuxième"
    liste(2) = "troisième"
    
    For i = 0 To 2
      MsgBox (liste(i))
    Next
End Sub

Tableau à deux dimensions :

Sub ordinaux2()
    Dim Tabl(2, 1) As String
    Dim LigneTab, ColonneTab As Integer
 
    Tabl(0, 0) = "premier"
    Tabl(1, 0) = "deuxième"
    Tabl(2, 0) = "troisième"
    Tabl(0, 1) = "1er"
    Tabl(1, 1) = "2e"
    Tabl(2, 1) = "3e"
 
    For LigneTab = LBound(Tabl, 1) To UBound(Tabl, 1)
      For ColonneTab = LBound(Tabl, 2) To UBound(Tabl, 2)
        MsgBox (Tabl(LigneTab, ColonneTab))
      Next ColonneTab
    Next LigneTab

    ' reset
    Erase Tabl
    MsgBox UBound(Tabl) ' = 2 car le tableau vide a conservé sa taille
End Sub

Tableau dynamique :

• Utiliser ReDim au lieu du Dim pour redimensionner le tableau, afin par exemple que le UBound ne considère que le nombre de champs remplis (et non celui déclaré au début).
• Idem pour ReDim Preserve qui conserve le contenu du tableau redimensionné.

Voir aussi les tableaux Excel.

Recherches

Pour rechercher dans un tableau :

If Not IsError(Application.Match("Valeur", MonTableau, 0)) Then
  MsgBox "Valeur trouvée"
Else
  MsgBox "Valeur non trouvée"
End If

Test si nul

Un tableau déclaré mais non utilisé ne renverra pas une taille zéro avec UBound(), mais une erreur disant que l’indice n'appartient pas à la sélection. Voici donc l’astuce pour vérifier qu'un tableau est vierge :

 if Len(Join(MonTableau)) = 0 then ...

Fonctions

Les fonctions aussi peuvent être de plusieurs types, et sont déclarées ainsi :

Sub NomDeFonction()
 ...
End Sub

Celle-ci ne renvoie rien, c’est l'équivalent du type void en C et en Java.

Ou encore privée avec un paramètre obligatoire :

Private Sub NomDeFonction(NomDuParametre As String)
 ...
End Sub

Publique avec un paramètre facultatif :

Public Sub NomDeFonction2(Optional NomDuParametre2 As Integer)
 ...
End Sub

Pour se servir de la fonction comme d'une variable :

Function NomDeFonction() as String
 ...
 NomDeFonction = "résultat"  ' ou
 return "résultat"
End Function

Remarques

• Cela peut aussi servir à créer des fonctions pour les formules Excel : une cellule peut contenir =NomDeFonction() si la fonction est placée dans un module et contient la ligne Application.Volatile.
• Si la fonction est placée dans ThisWorkbook, et s’appelle Sub Auto_open() ou Sub ThisWorkbook_Open(), elle se lance automatiquement à l'ouverture du fichier qui la contient.
• Si elle s’appelle Auto_close(), elle s'exécutera à la fermeture du fichier.

POO

En programmation orientée objets, les procédures correspondent aux classes, et sont stockées dans des fichiers .cls.

• La déclaration des objets se fait ainsi : Dim NomDeLaVariable As New NomDeLaClasse.
• Les classes filles spécifient leur classe mère avec le code Implements NomDeLaClasseMere.

Type MaClasse
  Nom As String
  Taille As Long
End Type

On y accède avec MaClasse.Nom.

Collections

L'avantage des collections sur les tableaux est que l’on peut incrémenter la liste à la suite ou insérer une ligne à un endroit, sans connaitre ou modifier manuellement sa taille.

L'exemple suivant affiche "ligne 1", ligne 2", "ligne 3" (sans tenir compte des autres lignes supprimées) :

 Dim Liste As New Collection
 Dim c as Integer ' compteur
 Liste.Add "ligne 1"
 Liste.Add "ligne 3"
 Liste.Add "ligne 2", Before:=2
 Liste.Add "ligne 4"
 Liste.Remove(4)
 For c = 1 To Liste.Count
     MsgBox Liste(c)
 Next c
 ' reset
 Set Liste = New Collection
 MsgBox Liste.Count ' = 0

Manipulation des variables

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Déclaration des variables

Syntaxe

Une variable VB doit porter un nom commençant par une lettre et ne devant pas comporter certains caractères spéciaux :

• espace
• ! (réservé aux Single)
• # (réservé aux Double)
• % (réservé aux Integer)
• $ (réservé aux String)
• & (réservé aux Long)
• @

Chaque fois que l’on voudra utiliser une nouvelle variable, il est fortement conseillé de la déclarer dans la feuille de code pour signaler au compilateur avec quoi il travaille et quelle place allouer à la mémoire pour gérer ces variables.

Début de l'exemple

Exemple

Dim Nom As String
Dim Age As Integer

déclare une chaîne de caractères Nom et un entier Age

Attention

Dim B1, B2, B3 As Boolean

déclare B1 et B2 comme variant et uniquement B3 comme booléen.

Fin de l'exemple

Portée

Une variable VB peut avoir trois portées différentes.

• Le niveau procédure, le plus fin, qui permet de limiter l’existence de la variable à la fonction ou procédure en cours.
• Le niveau formulaire permet l'accès à la variable à toutes les procédures d’un même formulaire.
• Le niveau projet permettant d'accéder au contenu de la variable dans toute l'application.

Début de l'exemple

Exemple

Dim Nom As String

placé en début d'une procédure, déclare une chaîne de caractères Nom pour la procédure courante uniquement.

Dim Nom As String

placé en début de formulaire, déclare une chaîne de caractères Nom pour toutes les procédures de celui-ci.

Public Nom as String

placé dans un module, déclare une chaîne de caractères Nom pour tout le projet.

Fin de l'exemple

Opérations possibles sur les variables

Affectation

Pour donner une valeur à une variable, on utilise le signe =

Début de l'exemple

Exemple

Dim x As Integer
Dim y As Boolean

x = 3
y = True

x vaut 3 et y vaut Vrai

Fin de l'exemple

Opérations mathématiques

Usuelles

• Les opérations usuelles (addition, soustraction, multiplication, division) s'écrivent en VB avec leurs symboles usuels.

Début de l'exemple

Exemple

a = 52
b = 8

c = a + b 'c vaut 60
d = a - b 'd vaut 44
e = a * b 'e vaut 416
f = a / b 'f vaut 6,5

Fin de l'exemple

• Pour le type Integer, le reste de la division euclidienne est accessible avec l'instruction Mod

Début de l'exemple

Exemple

a = 52
b = 8
c = a Mod b 'c vaut 4

Fin de l'exemple

• VB dispose également par défaut d'une petite bibliothèque de fonctions mathématiques standard :

Nom	Description
Cos	Cosinus de l'angle (en radians)
Sin	Sinus de l'angle (en radians)
Tan	Tangente de l'angle (en radians)
Exp	Exponentielle (en base e)
Log	Logarithme népérien
Sqr	Racine carrée

Ces fonctions retournent un résultat du type Double.

Comparer deux nombres

MsgBox Application.WorksheetFunction.Min(2, 3) ' = 2
MsgBox Application.WorksheetFunction.Max(2, 3) ' = 3

Arrondir un nombre

Il existe par défaut quatre fonctions pour arrondir :

Sub arrondir()
    a = "5,5"
    MsgBox Int(a)       ' 5
    MsgBox Fix(a)       ' 5
    MsgBox Round(a)     ' 6
    MsgBox Format(a, 0) ' 6
End Sub

Sub arrondir2()
    a = "5,4"
    MsgBox Int(a)       ' 5
    MsgBox Fix(a)       ' 5
    MsgBox Round(a)     ' 5
    MsgBox Format(a, 0) ' 5
End Sub

Au supérieur

a = 1,33
msgbox round(a+0.5)

À l'inférieur

a = 1,66
msgbox round(a-0.5)

Opérations logiques

Les opérations logiques se font en temps normal sur des variables de type Boolean.

VB connaît les principales opérations :

• Not : opérateur NON
• And : opérateur ET
• Or : opérateur OU
• Xor : opérateur OU exclusif

Début de l'exemple

Exemple

Dim a, b As Boolean
a = True
b = False

c = a And b 'c contient False
d = a Or b 'd contient True
e = a Xor b 'e contient True
f = Not a 'f contient False

Fin de l'exemple

Comparaison

VB sait comparer :

• deux variables numériques (2 > 1).
• deux chaînes de caractères, l’ordre étant celui du codage ASCII par défaut (B > A et a > A).

Propriété

En début de module, la commande OPTION COMPARE TEXT change ce mode de comparaison des chaines et ignore la casse (a = A). De plus, "B > " et "A > B" renvoient vrai dans ce mode. Pour y comparer des chaines sans avoir à le faire caractère par caractère, il faut utiliser StrComp(). Exemple :

 StrComp("A", "B", vbBinaryCompare)

Qui donne le même résultat (un booléen faux, soit "-1") que :

 OPTION COMPARE BINARY
 A > B

Les opérateurs de comparaison sont les suivants :

Opérateur	Signification
<	Inférieur
>	Supérieur
<=	Inférieur ou égal
>=	Supérieur ou égal
=	Égal
<>	Différent

Début de l'exemple

Exemple

c1 = "minuscule"
c2 = "VisualBasic"
c3 = "Zanzibar"

b1 = c2 < c3 'b1 contient True
b2 = c1 < c3 'b2 contient False : dans l’ordre ASCII, les majuscules sont avant les minuscules

a = 12
b = 12
c = a <> b 'c contient False
c = a = b 'c contient True

Fin de l'exemple

Les chaines de caractères contenant des nombres ne sont pas converties automatiquement en nombre lors des comparaisons : a = "1" MsgBox a > 2 ' True MsgBox 1 > 2 ' False MsgBox CInt("1") > 2 ' False

Opérations sur les chaînes de caractères

Longueur de la chaîne

La longueur d'une chaîne de caractères est accessible grâce à l'instruction Len.

Début de l'exemple

Exemple

a = "abcdefghi"
b = Len(a) 'b contient 9

Fin de l'exemple

Concaténation

Concaténer deux chaînes de caractères est l'opération qui consiste à les mettre bout à bout. De manière très intuitive, cette opération peut se faire en VB grâce à deux opérateurs : + et &.

Début de l'exemple

Exemple

a = "abcd"
b = "efgh"
c = a + b 'c contient la chaîne "abcdefgh"

Fin de l'exemple

Suppression des espaces latéraux

VB dispose de trois fonctions permettant de s'affranchir des espaces inutiles aux extrémités des chaînes de caractères :

• LTrim$ (L comme Left), qui retire les espaces inutiles à gauche de la chaîne
• RTrim$ (R comme Right), qui retire les espaces inutiles à droite de la chaîne
• Trim$, qui retire les espaces inutiles à droite et à gauche de la chaîne

Début de l'exemple

Exemple

a = " abcd "
b = " efgh "
c = a + b

a1 = LTrim$(a) 'a1 contient la chaîne "abcd "
b1 = RTrim$(b) 'b1 contient la chaîne " efgh"
c1 = Trim$(c) 'c1 contient la chaîne "abcd  efgh"

Fin de l'exemple

Changement de casse

Il existe deux fonctions qui permettent d'harmoniser la casse d'une chaîne :

• LCase$ (comme Lower Case), qui permet d'obtenir la chaîne en minuscules
• UCase$ (comme Upper Case), qui permet d'obtenir la chaîne en majuscules

Début de l'exemple

Exemple

a = "BlAbLa"
b = LCase$(a) 'b vaut "blabla"
c = UCase$(a) 'c vaut "BLABLA"

Fin de l'exemple

Extraction d'une sous-chaîne

On peut facilement extraire des sous-chaînes en VB de trois manières :

• Left$: permet d'extraire les caractères les plus à gauche de la chaîne
• Right$: permet d'extraire les caractères les plus à droite de la chaîne
• Mid$: permet d'extraire une sous-chaîne intérieure

Début de l'exemple

Exemple

a = "123456789"
b = Right$(a, 5) 'b contient les 5 caractères les plus à droite de a, c'est-à-dire "56789"
c = Left$(a, 2) 'c contient les 2 caractères les plus à gauche de a, c'est-à-dire "12"

d = Mid$(a, 3, 4) 'd contient 4 caractères à partir du troisième, c'est-à-dire "3456"
'Attention aux paramètres : Mid$(chaîne, départ, longueur)

Fin de l'exemple

Recherche d'une sous-chaîne

VB permet de trouver la position de la première occurrence d'une chaîne dans une autre chaîne grâce à l'instruction InStr.

Début de l'exemple

Exemple

t1 = "45"
t2 = "w"
s = "123456789"

b1 = InStr(s, t1) 'b1 vaut 4 car "45" commence au quatrième caractère de s
b2 = InStr(s, t2) 'b2 vaut 0 car "w" est introuvable dans s

Fin de l'exemple

Valeur numérique

L'instruction Val permet d'extraire un nombre présent au début d'une chaîne de caractères.

Début de l'exemple

Exemple

a = "23"
b = "17.5 ans"
y = "taratata"

c = Val(a) 'c vaut 23
d = Val(b) 'd vaut 17.5
z = Val(y) 'z vaut 0

Fin de l'exemple

Opérations sur les dates

Quand on manipule des dates, pour éviter de convertir et rechercher si nous devons changer de mois le 30 ou le 31, il existe des fonctions prédéfinies :

1. DatePart() : extrait une partie de date (jour, heures...)
2. DateAdd() : ajoute une durée.
3. DateDiff() : calcule une différence entre deux dates.

Influence du typage sur le contenu des variables

Contrairement à d'autres langages, VB fait preuve d’un certain laxisme envers le typage des variables. Ce peut être un avantage comme un inconvénient.

Début de l'exemple

Exemple

Dim a As Integer
Dim b As Integer

a = 2.51
b = 2.49

Après l'exécution de ces lignes, a contiendra en réalité 3 et b contiendra 2.

Fin de l'exemple

Toutefois, cette souplesse se manifeste avec certains opérateurs et pas d'autres.

Début de l'exemple

Exemple

Dim s As String
Dim a As Integer
Dim b As Double

a = 14
b = 17.2
s = "a+b vaut " & (a + b) & " !"

Avec &, s contient "a+b vaut 31.2 !"

Dim s As String
Dim a As Integer
Dim b As Double

a = 14
b = 17.2
s = "a+b vaut " + (a + b) + " !"

Avec +, on obtient l'erreur d'exécution 13 : Types incompatibles

Fin de l'exemple

Variables publiques connues

Certaines variables sont propres aux logiciels, par exemple dans Excel : xlDown, xlUp, xlToLeft, xlToRight renvoient toujours les limites de la feuille courante (respectivement en bas, haut, gauche et droite).

Conditionnelles et boucles

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Structures de test

Test conditionnel simple : le bloc If

Exemple :

if (testCondition) then
   instruction1
else
   instruction2
end if

Distinction de cas : Select Case

'Exemple d'instruction SELECT

 Select Case foo
    Case "valeur":
    Instructions_1
 [...]
    Case "Valeur_bis":
    Instructions_2
 End Select

'Fin de l'exemple

Fonctionnement : selon la valeur de la variable foo, il sera exécuté une série d'instruction définie comme suit.

Si foo = "valeur" alors on fera les Instructions_1 Si foo = "Valeur_bis" alors on exécutera les Instructions_2

C'est une fonction très pratique et plus propre que les if elseif else à répétitions.

Structures de boucle

Une boucle, c’est quoi ?

C'est comme son nom l'indique, le fait de faire quelque chose qui tourne en rond, de le répéter un certain nombre de fois.

Par exemple, on souhaite calculer la moyenne d'une classe de CM1, on connait toutes les moyennes des élèves et donc on va faire la boucle suivante :

Pour chaque élève de la classe, additionner la moyenne. Une fois tous les élèves y sont passés, on divise le total par le nombre d'élève.

Boucles à compteur : For

En anglais, "for" = "pour".

Syntaxe :

For ''var'' = ''start'' to ''end''
  ''Instructions''
Next ''var''

Traduction littérale : Pour la variable var égale à start, exécuter les Instructions et incrémenter var de 1 jusqu'à end

L'instruction Step Step = Pas. En définissant un pas, on choisi de modifier l'incrémentation de la variable a chaque passage de la boucle

For ''var'' = ''start'' to ''end'' Step ''pas''
  ''Instructions''
Next ''var''

Traduction littérale : Pour la variable var égale à start, exécuter les Instructions et incrémenter var de pas jusqu'à end

Cela sert quand le pas d'incrémentation est supérieur à 1, et/ou quand on connait le nombre d'itération.

For Each

Pour parcourir toutes les lignes d'un tableau :

 Dim MonTableau(1) As String
 Dim Ligne As Variant
 MonTableau(0) = "Ligne 1"
 MonTableau(1) = "Ligne 2"
 For Each Ligne In MonTableau
   MsgBox Ligne
 Next

Boucles conditionnelles : While

En anglais, "while" = "tant que".

Syntaxe :

 While ''condition''
   ''Instructions''
 Wend

Traduction littérale : tant que condition est vrai alors on exécute les instructions. Le contenu d'un while peut donc en théorie se produire zéro fois, si sa condition n’est pas remplie au départ.

Une variante permet donc de passer au moins une fois dans la boucle avant de tester sa condition de sortie :

 Do while ''condition''
   ''Instructions''
 Loop

Pour sortir du Do while il existe la commande Exit Do.

Calculer la moyenne de la classe de CM1

Code commun :

 Option Explicit
 Dim notes(5) as integer
 Dim noteTotale as integer
 Dim moyenne as long
 
 notes(0) = 10
 notes(1) = 12
 notes(2) = 18
 notes(3) = 5
 notes(4) = 15
 notes(5) = 11

Avec For : For i=0 to 5 noteTotale = noteTotale + notes(i) Next i

Avec While i=0 While(i<6) noteTotale = noteTotale + notes(i) i = i+1 Wend

Calcul de la moyenne

 moyenne = noteTotal/6

Étiquettes

À tout moment il est possible de sortir d'une boucle par une étiquette.

 i=0
 While(i<5)
  if i = x then
   goto sortie1
  end if
  i = i+1
 Wend
sortie1:
 msgbox ("x - i est nul")

De plus, afin de ne pas déranger l'utilisateur à chaque erreur d'exécution, de nombreux programmes débutent par :

on Error Resume Next

Pour annuler cette levée d'exception :

On Error GoTo 0

Il est également possible de spécifier où le programme doit poursuivre son exécution, avec goto + étiquette :

On Error GoTo ÉtiquetteErreur

Il est fortement déconseillé d’utiliser de goto quand on peut faire autrement, car cela rend le code moins compréhensible et peut être considéré comme de la programmation spaghetti.

Enregistreur

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Un outil magique pour l'apprentissage

Le principe de l'enregistreur de macros est de créer une procédure VBA sans avoir la moindre connaissance du langage. Après avoir saisi quelques paramètres (nom, raccourci, localité, commentaire) vous pouvez démarrer l'enregistrement d'une macro, toutes les actions que vous réalisez sur le(s) classeur(s) sont traduites en instructions dans le langage VBA. À la fin de la séquence, il vous faut arrêter l'enregistrement de la macro. Lorsque vous rappellerez cette macro enregistrée par le nom que vous lui avez donné, Excel exécutera de nouveau toutes les actions que vous avez effectuées lors de l'enregistrement.

L'enregistreur trouve son intérêt dans deux circonstances :

• Vous êtes développeurs de macros débutant et ne connaissez pas toutes les instructions du langage VBA
• Vous souhaitez créer une macro immédiatement effective grâce au logiciel Excel seul sans passer par la fenêtre d'édition de macros VBE (Visual Basic Environment)

Remarque

La majorité des débutants VBA passe d’abord par l'enregistreur, puis complète ou supprime les lignes générées. Avec le temps et la maitrise, la majorité des utilisateurs VBA arrive à coder directement une macro sans passer par l'enregistreur. En d'autres termes, coder une macro directement en VBA n’est pas à la portée de tous les utilisateurs, il faut un minimum de connaissances de vocabulaire, de mots-clés et d'objets pour pouvoir le faire.

Avant de vous lancer dans l'enregistrement automatique d’une macro, il est judicieux de dégager le réel intérêt de la macro, en se poser quelques questions :

• "Comment la nommer, comment la déclencher ?"
• "Est-ce qu'elle peut être utilisée par d'autres classeurs ?"
• "Quel va être le but de ma macro, à quoi va t-elle servir ?"

Remarque

Les exemples de cette leçon sont relativement simples, l’utilité de certaines macros peut sembler discutable, mais il faut acquérir une base solide de connaissances avant de s'attaquer à la création de macros plus utiles et donc plus complexes. Comme en mathématiques, seul un entraînement régulier et répétitif sur des macros simples, voire simplistes, vous permettra d'atteindre le niveau requis pour la création de macros professionnelles

Enregistrer une macro

Pour démarrer l’enregistreur, suivez les différentes étapes :

1. Aller sur l’onglet développeur
2. Dans la partie Code, cliquez sur Enregistrer une macro, la boîte de dialogue "Enregistrer une macro" s’ouvre, un formulaire s'affiche avec les champs suivants :
   1. Nom de la macro
   2. Touche de raccourci
   3. Enregistrer la macro dans
   4. Description
   5. Valider avec OK
3. Effectuer toutes les actions que vous voulez sur votre classeur EXCEL
4. Retourner sur l’onglet développeur
5. Dans la partie Code, cliquez sur Arrêter l'enregistrement

Graissage automatique

Donner un nom à la macro

Le nom d’une macro est soumis à quelques règles :

• Le nom doit commencer par une lettre et doit correspondre à ce que fait la macro
• Le nombre de caractères maximal que peut contenir le nom de la macro est de 80 caractères (Lettres et chiffres exclusivement)
• Dans le cas où le bouton « OK » reste grisé, cela signifierait que le nom saisi est incorrect.

Début de l'exemple

Exemple

Si vous réalisez une macro qui graisse la police de caractère des cellules sélectionnées, vous pouvez la nommer mGraisserSelection

Fin de l'exemple

Associer une combinaison de touches à la macro

Une combinaison de touches qui vous permettra d’activer directement la macro, cette combinaison est soumise aussi à quelques règles :

• La touche CTRL est toujours retenue, vous pouvez la compléter par une autre lettre : CTRL/N
• Pour ne pas interférer avec les combinaisons de touches standard EXCEL, il est plutôt conseillé d’utiliser aussi la touche MAJ : CTRL/MAJ/N
• La combinaison de touches n’est pas obligatoire (option recommandée), d'autres méthodes de lancement de la macro sont plus efficaces (bouton, appel automatique, formulaire, ...)

Début de l'exemple

Exemple

Si vous réalisez une macro qui graisse la police de caractère des cellules sélectionnées, vous pouvez lier votre macro à la combinaison CTRL + MAJ + G

Fin de l'exemple

Choisir le lieu de stockage de la macro

Ici, Excel vous propose d’enregistrer la macro dans :

• Ce classeur : Si vous voulez que la macro ne soit effective que sur le classeur que vous utilisez (option recommandée)
• Nouveau classeur : La macro sera enregistrée sur un nouveau classeur
• Classeur de macros personnelles : la macro sera accessibles à partir de tous les classeurs EXCEL de votre poste de travail (à n’utiliser que si vous êtes sur de la fiabilité de votre macro), la macro est alors enregistrée sur le fichier de macros personnelles : perso.xls (fichier de paramètre caché d’Excel)

Début de l'exemple

Exemple

Si vous réalisez une macro qui graisse la police de caractère des cellules sélectionnées, il est recommandé de stocker votre macro dans Ce classeur

Fin de l'exemple

Commenter la macro

Il est indispensable de saisir une description concise à chaque création de macro, cela vous permettra de savoir exactement ce que votre macro fait quand vous voudrez l’utiliser de nouveau

Début de l'exemple

Exemple

Si vous réalisez une macro qui graisse la sélection, vous pouvez lui donner la description : Cette macro graisse les polices de toutes les cellules sélectionnées

Fin de l'exemple

Code VBA généré après l'enregistrement des actions

Après avoir effectué toutes les actions EXCEL reproductibles puis arrêté l’enregistrement, la suite d’instructions suivante affectée à la macro est accessible dans l'environnement VBE

Sub mGraisserSelection()
'
' mGraisserSelection Macro
' Cette macro graisse les polices de toutes les cellules sélectionnées
'
' Touche de raccourci du clavier: Ctrl+Shift+G
'
    Selection.Font.Bold = True
End Sub

Conseil

Préparer à l'avance les commandes à enregistrer afin de ne pas créer du code VBA automatique inutile

Un outil facile pour l’utilisation des macros => l’assistant Macros

Assistant de graissage automatique

Pour démarrer l’assistant, suivez les étapes suivantes :

1. Allez sur l’onglet développeur
2. Dans la partie Code, cliquez sur Macros, la boîte de dialogue "Macro" s’ouvre avec les objets suivants :
   1. la macro sélectionnée
   2. une liste de toutes les macros exécutables
   3. une suite de boutons de commandes
   4. le lieu de stockage de la macro sélectionnée
   5. la description de la macro sélectionnée
3. Cliquez sur le bouton de votre choix

Début de l'exemple

Exemple

• Exécuter : permet de lancer en un seul jet toutes les commandes enregistrées de la macro, ce processus représente l'une des utilisations standard des macros
• Pas à pas : permet de lancer les commandes enregistrées de la macro avec un arrêt après chaque instruction, ce processus est extrêmement utile lors de phases de mise au point ou de réparation des macros-commandes
• Modifier : permet d'afficher la fenêtre d'édition VBE afin de visualiser et/ou de modifier des lignes d'instruction
• Créer : permet d'afficher la fenêtre d'édition VBE afin de créer une nouvelle macro par une saisie directe des lignes d'instructions de code VBA
• Supprimer : permet de supprimer une macro devenue inutile
• Options : permet de visualiser et modifier les propriétés générales de la macro (nom, raccourci, description)

Fin de l'exemple

Les avantages et les inconvénients de l'enregistreur

Les avantages

• Méthode simple ne nécessitant pas une connaissance parfaite de la syntaxe du code VBA et des objets EXCEL
• Simplicité d'utilisation (principe de magnétophone qui enregistre toutes les actions pendant un temps donné)
• Apprentissage rapide de la syntaxe du code VBA et des objets EXCEL (grâce au codage VBA automatique du magnétophone)
• Apprentissage de la programmation VBA cohérente et utilisable sur toutes les applications d'office

Les inconvénients

• L'enregistreur de macro-commandes ne traduit que les opérations réalisées par l'utilisateur, il ne résout pas lui-même tous les besoins de programmation (boucles, conditions, ...) ceci implique des actions complémentaires à coder dans l'environnement VBE
• L'enregistreur de macro-commandes est parfois prolixe, voire trop bavard, il lui arrive d'écrire plus de lignes de code VBA que ce qu’il n'en faudrait
• Les programmes VBA sont sujets comme tout logiciel aux erreurs (bogues) qui peuvent entrainer des dysfonctionnements de l’application hôte
• Les macros VBA ne peuvent être utilisées sans l’application sous laquelle elles sont développées

Cas pratique

Graissage et soulignement automatiques

Dans le cas présent, il s'agit juste de changement d'affichage sur la police de caractère

Début de l'exemple

Exemple

Voici l'énoncé :

• Créer une nouvelle macro-commande : Actionner le bouton nouvelle macro
• Nommer la macro mGraisserEtSouligner, la commenter
• Démarrer le magnétophone : Actionner le bouton d'enregistrement
• Enregistrer vos actions : Sur la feuille Excel graisser et souligner la cellule sélectionnée
• Arrêter le magnétophone : Actionner le bouton de fin d'enregistrement

Fin de l'exemple

Dans le cas présent, l'enregistreur magique a enregistré ceci dans l'environnement VBE

Solution

Sub mGraisserEtSouligner()
'
' mGraisserEtSouligner Macro
' Cette macro graisse et souligne la sélection en cours
'
    Selection.Font.Bold = True
    Selection.Font.Underline = xlUnderlineStyleSingle
End Sub

 

Annexes

Bibliographie

• Amelot M., 2011, VBA Excel 2010, ENI, St Herblain
• Bidault M., 2000, Excel et VBA, CAMPUS, Paris

Liens internes

• Format Euro : exercice de création de macro via l'enregistreur
• Leçon VISUAL BASIC
• Leçon Tableur EXCEL

Liens externes

• Vos premiers pas dans l'éditeur de macros Excel

Boite de dialogue

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Parmi les outils VBA il y a différentes boites de dialogue standard fournies par le langage.

La boite de dialogue MsgBox

Cette boite de dialogue permet d'afficher des messages à l'utilisateur à partir de constantes ou de variables VBA, elle permet aussi la saisie de réponses à des questions fermées (oui, non, ok, cancel, …). L'utilisateur dispose de plusieurs types de boutons pour poser une question fermée (vbOKOnly, vbOKCancel, vbAbortRetryIgnore, vbYesNo, vbYesNoCancel, vbRetryCancel, …) qui renvoient plusieurs types de réponses quand ils sont activés :

• Le bouton OK renvoie la valeur vbOK
• Le bouton OUI renvoie la valeur vbYes
• Le bouton NON renvoie la valeur vbNo
• Le bouton Annuler renvoie la valeur vbCancel
• Le bouton Ignorer renvoie la valeur vbIgnore
• Le bouton Recommencer renvoie la valeur vbRetry
• ...

Boite de dialogue à réponse fermée

Titre	Exemple	Commentaire
Syntaxe générale	MsgBox(prompt[, buttons] [, title] [, helpfile, context])	La fonction MsgBox possède de nombreux autres arguments, consulter l’aide pour plus d’informations
Uniquement afficher un message	MsgBox "Hello World",vbOKOnly+vbInformation,"INFO"	Message simple dans une fenêtre INFO avec un indicateur informatif et un bouton OK (seule réponse possible)
Afficher un message avec une variable	Msgbox "Vous avez ouvert " & Workbooks.Count & " classeurs"	Message simple dans une fenêtre avec affichage de constantes et de variables
Poser une question fermée à choix unique	Reponse=MsgBox("Voulez-vous continuer ?",vbYesNo)	Message simple dans une fenêtre avec saisie de réponses soit OUI soit NON

La boite de dialogue InputBox

Cette boite de dialogue permet de récupérer des valeurs saisies par l'utilisateur en réponse à des questions ouvertes. Les réponses sont stockées dans des variables VBA pour être réutilisées ultérieurement.

Boite de dialogue à question ouverte

Titre	Exemple	Commentaire
Syntaxe générale	InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])	La fonction InputBox possède plusieurs autres arguments, consulter l’aide pour plus d’informations
Poser une question ouverte à réponse textuelle	nom=InputBox("saisir votre nom")	Cette boîte de saisie permet à l’utilisateur de saisir un texte répondant à ce qui lui est demandé, la variable nom prend alors la valeur qui a été saisie par l'utilisateur
Poser une question ouverte à réponse numérique	reponse = InputBox("Entrer un nombre entre 1 et 100", "JEU")	Cette boîte de saisie permet à l’utilisateur de saisir un nombre dans une fenêtre "JEU", la variable reponse prend alors la valeur qui a été saisie par l'utilisateur

La boite de dialogue d’ouverture de fichier

Cette boite fait partie du modèle objet et permet via une fenêtre de dialogue spécifique l’ouverture de fichiers ou de dossier. Ici, c’est l’utilisateur qui dirige la manœuvre de recherche arborescente et graphique de fichier.

Les paramètres facultatifs suivants peuvent être utilisés pour la méthode GetOpenFileName : FileFilter, FilterIndex, Title, MultiSelect

La boite de dialogue de sauvegarde de fichier

Cette boite fait partie du modèle objet et permet via une fenêtre de dialogue spécifique l'enregistrement de fichier. Ici, c’est l’utilisateur qui dirige la manœuvre de recherche arborescente, de typage et de nommage de fichier.

Les paramètres facultatifs suivants peuvent être utilisés pour la méthode GetSaveAsFileName : InitialeFile, FileFilter, FilterIndex, Title

Liste des imprimantes du PC

La liste des imprimantes installées sur un PC est accessible avec :

Application.Dialogs(xlDialogPrinterSetup).Show
MsgBox Application.ActivePrinter

Le bouton "Configurer" de cette boite permet d'accéder aux propriétés de l'imprimante sélectionnée.

La sélection effectuée par l'utilisateur modifie directement l'imprimante active.

Par contre pour rechercher une imprimante par une partie de son nom, il faut que le script lise la base de registre.

Références

Bibliographie

• Jensen O.G., 2003, Initiation à la programmation VBA Word, KNOWWARE, Ecuelles
• Bidault M., 2000, Excel et VBA, CAMPUS, Paris

Liens internes

• Convertisseur Euro-Franc : exercice de saisie d'une valeur avec boite de dialogue pour calcul
• Filtre_dynamique : exercice de saisie d'une valeur avec boite de dialogue pour filtre
• Quiz facile VBA
• Leçon VISUAL BASIC
• Leçon Tableur EXCEL

Liens externes

• Cours VBA Les boites de dialogue
• Une vidéo sur les boites de dialogue
• Une vidéo sur les boites de dialogue intégrée

Création de Formulaire

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Un Userform (également appelé formulaire dynamique), permet de réaliser des interfaces utilisateurs simples et conviviales pour la saisie, la modification ou la visualisation de données. Nous allons dans ce chapitre expliquer le fonctionnement de base de la création de formulaire à l’aide de plusieurs exemples.

Généralités

Définition du formulaire

Un formulaire est un document conçu avec une structure et un format qui permettent de faciliter la lecture, la capture, l’organisation et la modification des informations à saisir ou à afficher. Il existe 2 types de formulaires :

• Les formulaires imprimés sur papier (formulaires statiques) qui contiennent des mises en forme, des étiquettes et des espaces vides pour écrire des données. Vous pouvez utiliser Excel et les modèles Excel pour créer des formulaires imprimés.
• Les formulaires affichés à l'écran (formulaires dynamiques) qui contiennent des mises en forme, des étiquettes, des objets de saisie et des espaces vides pour renseigner, cocher ou sélectionner des données et qui peuvent surtout réagir aux actions de l’utilisateur (clic, déplacement, ...). Vous pouvez utiliser VBA Excel pour créer des formulaires affichés.

Types de formulaires dynamiques Excel

Vous pouvez créer 2 types de formulaires dans Excel : formulaires utilisateurs (userforms) ou feuilles de calcul (sheets). Les 2 types peuvent contenir des contrôles de formulaire et des contrôles ActiveX ainsi que le code VBA qui leur est associé. Vous pouvez utiliser chaque type de formulaire de manière autonome, ou les combiner de différentes façons pour créer une solution qui vous convienne. Nous allons dans ce chapitre étudier les formulaires utilisateurs VBA.

Un peu de vocabulaire autour des formulaires

Dans le module Userform (formulaire), toutes les procédures sont des actions répondant à des actions sur des objets nommés contrôles ActiveX (formulaire, bouton, boite de texte, case à cocher, liste déroulante, …) inclus dans le formulaire. Ce formulaire présentant en outre un ensemble de propriétés , de méthodes et répondant à des événements :

• Name : permet de définir le nom du formulaire
• Caption : permet de définir le titre du formulaire
• Minimize : rapetisse le formulaire
• Load : charge en mémoire et ouvre le formulaire
• Show : affiche le formulaire
• Change : permet de déclencher des actions sur changement intervenu sur le formulaire
• Initialize : permet de déclencher des actions sur chargement du formulaire
• Activate : permet de déclencher des actions sur activation du formulaire
• Close : permet de déclencher des actions sur fermeture du formulaire

Création de l'interface

Accès à l'éditeur Visual Basic

Avant de créer l'interface de notre formulaire, l'utilisateur devra, en se positionnant sur l'onglet développeur du fichier EXCEL sur lequel il se trouve, 'ouvrir la fenêtre VBA'. Il pourra également utiliser le raccourci Alt + F11.

Insertion du formulaire

Une fois la fenêtre VBA ouverte, l'utilisateur pourra créer un userform (formulaire), via insertion puis user form.

Il récupère alors un 'Userform vierge' qui apparait qu’il va personnaliser en fonction de ce qu’il veut effectuer avec ce formulaire (saisie de données, contrôles des données, proposition à l’utilisateur…). Cette personnalisation va notamment s’effectuer via 'les propriétés' et 'la boite à outils'.

Les propriétés

Grâce aux propriétés du formulaire, il est possible de modifier un certain nombre de choses. Par souci de simplicité, nous n’abordons que quelques propriétés du formulaire dans cette leçon mais sachez qu’il en existe beaucoup d’autres. Nous aborderons les propriétés les plus courantes telles que :

• Name
• Caption
• Font
• Backcolor – Bordercolor - Border style
• Picture

Pour afficher les propriétés

Pour 'afficher les propriétés', dans la même logique qu’avec Excel ou Windows, je place mon curseur sur le UserForm, j’effectue un clic droit et je vais afficher les propriétés.La fenêtre de propriété du UserForm s’affiche à droite en dessous de notre fenêtre projet, une multitude de propriété apparait.

Fenêtre des propriétés

Par souci de simplicité, nous ne présentons que quelques illustrations de l’utilisation des propriétés les plus utilisées permettant d’apporter plus d’esthétique (police, couleur…), mais également plus d'interactivité avec l'utilisateur.

Name

La propriété name correspond au nom de l’objet, c’est ce nom que l’on va utiliser dans notre code pour appeler l’objet.

De manière générale, et par souci d’efficacité, il est recommandé de nommer les objets en expliquant à quoi ils servent. Dans l’exemple que nous avons choisi, le formulaire va permettre à l’utilisateur l’allocation d’une charge de travail de chaque acteur en fonction du projet sur lequel il travaille. Il est donc recommandé de renommer le UserForm1 de base par UserFormProjet. Ce simple nommage permettra à l’utilisateur de s’y retrouver beaucoup plus facilement dans son code par la suite, de gagner du temps et d’éviter de nombreuses erreurs d’inattention.

Propriété name du userform

Remarque

La propriété Name peut aussi s'appliquer sur la propriété Parent de l'objet. Par exemple dans Excel :

 Dim F As Excel.Worksheet
 Set F = ActiveSheet
 MsgBox "La feuille " & F.Name & " se trouve dans le fichier " & F.Parent.Name & " de l'application " & F.Parent.Parent.Name

Caption

La propriété caption permet à l’utilisateur de modifier le titre de son formulaire. Là encore, il est fortement recommandé à l’utilisateur de respecter une certaine cohérence. Toujours avec notre même exemple de formulaire pour la saisie de projet :

Propriété Caption

Changement de la caption

Font

Cette propriété va permettre à l’utilisateur de modifier la police de la caption que nous avons abordée précédemment. L’utilisateur va ainsi avoir la possibilité de choisir un type de police particulier, sa taille, si cette police sera en italique, en gras … Il suffit de se positionner sur font dans les propriétés, puis d’appuyer sur le bouton qui apparait à l’extrémité droite de la ligne.

Propriété Front

Une fois cette étape effectuée, une boite de dialogue « police » apparait, il vous suffit ensuite de choisir la police qui vous convient.

Modifier la propriété Front

Backcolor – Bordercolor - Border style

Ces trois propriétés vont permettre à l’utilisateur de modifier à sa guise la couleur de fond du formulaire, la couleur de la bordure mais également le style de la bordure. Ces trois propriétés sont très utiles quand vous voulez donner une esthétique recherchée à votre formulaire. Ces propriétés fonctionnent de la même façon que la propriété Font, en effet, il suffit de se placer sur la ligne de ces propriétés, cliquer sur le bouton qui apparait à l’extrémité droite, puis une liste de choix apparait.

Propriété backcolor - Bordercolor

BorderStyle

La taille 0 correspond à une bordure invisible alors que la taille 1 correspond à une bordure simple.

Picture

Pour aller encore plus loin dans l’esthétisme, vous avez également la possibilité de placer une photo en image de fond de votre formulaire. Il suffit de se placer dans le propriété Picture, cliquer sur le bouton placé à l'extrémité de la ligne.

Propriété picture

Une fois cette manipulation effectuée, une boite de dialogue charger une image apparaitra, il vous suffira alors d’aller chercher dans vos dossiers l’image que vous souhaitez sélectionner en image de fond pour votre formulaire.

Voilà à quoi ressemble votre formulaire une fois que vous avez effectué toutes ces modifications, simplement en ayant modifié quelques propriétés.

1er aperçu avec l'image en fond

ControlSource

Cela permet de lier un champ du formulaire à une cellule d'une feuille qui existe dans le classeur. Le champ affichera donc la valeur de la cellule, puis sur libération il lui donnera sa nouvelle valeur (synchronisation).

Ex : Feuil1!A1.

Si la cellule contient une formule, le champ du formulaire la remplacera par sa valeur.

SpecialEffect

Ce champ définit le look de l’objet (formulaire, TextBox...), il peut prendre cinq valeurs différentes :

1. 0-frmSpecialEffectFlat : rendu 2D. C'est celui par défaut des formulaires.
2. 1-fmSpecialEffectRaised : rendu 3D où l’objet est surélevé (mis en avant).
3. 2-frmSpecialEffectSunken : rendu 3D où l’objet est abaissé (mis en arrière). C'est celui par défaut des TextBox et CheckBox.
4. 3-fromSpecialEffectEtched : 2D avec une bordure.
5. 4-frmSpecialEffectBump : 3D avec une bordure.

Les contrôles de la boîte à outils

Pour afficher la boite à outils, si elle ne s’affiche pas automatiquement, il faut se rendre dans affichage et sélectionner « boite à outils ».

Comme pour les propriétés, nous allons vous présenter un certain nombre de contrôles qui vous seront utiles pour créer votre formulaire personnalisé. Vous allez sélectionner certains contrôles de la boite à outils, les insérer dans votre formulaire, puis régler les propriétés de ces contrôles et enfin dynamiser ces contrôles en programmant leurs réactions aux événements (Clic, Saisie, Déplacement, Ouverture, ...).

De plus, vous pourrez ajouter des contrôles supplémentaires et personnaliser ainsi votre boite à outils. Vous trouverez également un certain nombre de contrôles ActiveX sur internet et il vous sera même possible, une fois que vous serez à l’aise avec la programmation, de créer vous-même vos contrôles.

Le label

Le contrôle label permet généralement de placer un intitulé (libellé) à côté d’un contrôle ne procédant pas cet attribut, à côté d’une zone de texte par exemple, afin de permettre à l’utilisateur d’en identifier le contenu.

Le label

À partir de là, vous pouvez utiliser l’ensemble des propriétés que nous avons vu précédemment notamment renommer votre label afin d’aider l’utilisateur dans la compréhension du formulaire. Et voici ce à quoi votre formulaire pourrait ressembler si vous ajoutez 18 labels que vous renommez.

2 ème aperçu

2 ème aperçu

Le TextBox

Le contrôle TextBox permet de placer des zones de saisie de texte sur la feuille, dans laquelle l’utilisateur pourra renseigner des informations. Les propriétés d’un contrôle TextBox permettent de contrôler le nombre de caractères maximum que pourra entrer l’utilisateur, l’affichage sur plusieurs lignes du texte ou encore le type d’alignement du texte dans la zone de texte...

Le TextBox

Voici le formulaire obtenu une fois ajoutées 12 x 6 = 72 TextBox pour la saisie des utilisateurs. Afin que l’utilisateur ait l’impression de saisir ses informations dans un tableau, nous avons modifié l’une des propriétés des TextBox, la propriété BackStyle, en appliquant BackStyleOpaque.

La ComboBox

Le contrôle ComboBox, ou la zone de liste modifiable, est une zone de texte permettant à l’utilisateur de saisir une valeur manuellement ou de la sélectionner dans la liste qui se déroule lorsqu’il clique sur le bouton prévu à cet effet. Un contrôle ComboBox peut permettre à l’utilisateur de saisir une valeur ne figurant pas dans la liste, ou n’autoriser qu’une des valeurs de la liste.

Ce contrôle n’inclut pas d’intitulé, par conséquent il est recommandé d’ajouter un Label pour que l’utilisateur s’y retrouve dans le formulaire.

La ComboBox

Les alternatives sont la ListBox (liste défilante), et dans Excel la cellule liste (Données, Validation des données, Autoriser = Liste).

Le CommandButton

Il est possible d’ajouter à votre formulaire un bouton permettant à l’utilisateur d’effectuer diverses actions. Il peut par exemple servir à valider les informations entrées dans la feuille (bouton OK) afin de passer à l’étape suivante du programme, ou au contraire d’interrompre le programme (bouton Annuler).

CommandButton

Il est important de garder à l’esprit lorsque que vous manipulez les contrôles ActiveX, que chaque ActiveX dispose de ses propres propriétés que vous pouvez manipuler à votre guise.

Finaliser la présentation du formulaire

La taille d'un formulaire est fixe ; il ne s'adapte donc pas automatiquement à la taille ou à la résolution de l'écran. Si le formulaire est utilisé sur un autre PC, en fonction des dimensions de ce dernier, il y aura certaines différences.

Il est possible de redimensionner le formulaire ainsi que tous les contrôles par code VBA mais ce n’est pas évident. Dans ce cas, il vaut mieux réaliser de petits formulaires afin d’anticiper l’usage d’un écran de plus petite taille que celui initialement utilisé.

Il est important de savoir que les contrôles onglet et surtout multi-pages permettent de répartir les différents contrôles sur plusieurs pages successives : c’est une bonne méthode pour permettre de saisir de façon plus simple un nombre conséquent de données.

Initialiser une boite de texte

Il est également possible de pré remplir une boîte de texte avec un texte que l'utilisateur effacera pour faire sa saisie. Cela se fait au moment de l'initialisation du formulaire, par code VBA, ou bien dans la fenêtre propriété en remplissant la propriété "Text".

Modifier les couleurs

Il est possible de choisir les couleurs de fond des formulaires ainsi que des différents contrôles utilisés. Pour cela, il y a deux possibilités :

• Utiliser une des couleurs prédéfinies de la palette en cliquant sur la flèche en face de backcolor puis sur palette.
• Définir sa propre couleur : clic droit sur la palette puis sur définissez votre couleur et enfin cliquer sur le bouton ajouter.

Aligner les contrôles

Aligner les contrôles permet d’avoir un meilleur visuel quant à la disposition des contrôles dans un formulaire. Pour cela, il faut :

• Sélectionner les contrôles voulus avec la touche Ctrl
• Les aligner : Menu format puis Aligner de l'éditeur VBA.

Il est également possible de les répartir ou encore d’ajuster leurs tailles de façon à avoir des dimensions uniformes et des espacements équidistants.

Définir l’ordre de saisie des contrôles

Afin de faciliter la saisie, il paraît plus pratique d’user de la touche tabulation pour se déplacer dans un formulaire. Il est possible de définir l’ordre dans lequel les contrôles seront sélectionnés, pour cela :

• Ouvrir le menu affichage
• Sélectionner ordre de tabulation
• Faire monter ou descendre les différents contrôles qui apparaissent dans la liste jusqu'à ce que l’ordre convienne.

Afficher des messages d'aide

Insérer des messages de façon à indiquer à l’utilisateur comment entrer une donnée est utile dans bien des cas. En effet, cela permet d’informer l’utilisateur et lui éviter de saisir plusieurs fois une information car le format n’étant pas adapté, l’information ne sera pas prise en compte : par exemple, indiquer que la date doit être saisie sous format jj/mm/aaaa. C’est la propriété controlTipText qui permet cela.

Exemple : renseignez "numéro à 10 chiffres sans espaces" dans le ControlTypeText de la textbox concernée

Tester l’apparence

Il est possible de visualiser le formulaire à tout moment afin de vérifier comment le formulaire apparaîtra à l'utilisateur et de pouvoir ainsi corriger les éventuels indésirables. Pour cela, il suffit d’appuyer sur la touche F5 ; elle permet de lancer le formulaire.

Initialisation du formulaire : transfert de données de la feuille vers le formulaire

Dans cette partie, il s’agit de montrer comment procéder pour l’intégration des données issues d’un tableau Excel dans un formulaire dynamique. Il est à noter que ce n’est pas systématiquement un besoin. En effet, en fonction de l’exercice et des exigences, cela sera demandé ou non.

Le module VBA du formulaire

Dans un premier temps, il s’agit de d’accéder à l’Userform ou formulaire. Pour cela, il faut :

• Aller dans l’éditeur VBA
• Faire un clic droit sur le nom du formulaire pour visualiser le code associé.

Le formulaire s’ouvre alors ainsi que la fenêtre qui permet de visualiser tous les contrôles associés au formulaire.

Remplir les listes

Pour que les différents choix possibles s'affichent dans les listes, il y a plusieurs solutions. Celles-ci s'appliquent que ce soit une ListBox ou une ComboBox.

• La méthode AddItem : cette option consiste à taper un code VBA en utilisant l’instruction AddItem
• La propriété RowSource : cette option consiste à afficher la fenêtre propriété du contrôle et la paramétrer afin de sélectionner une plage de données bien précise. Elle peut être remplie en mode graphique avec un tableau (ex : Feuille1!C22:C28)
• La propriété List : cette option consiste également à afficher la fenêtre propriété du contrôle et la paramétrer afin de sélectionner une plage de données bien précise

Concernant les propriétés RowSource et List, il s’agit de compléter manuellement dans la fenêtre propriétés du contrôle ou par VBA.

Via VBA

Il s’agit de la méthode AddItem qui consiste en l’affectation d’une propriété à la plage de donnée voulue.

La méthode AddItem ajoute les éléments les uns après les autres. Pour cela, la macro doit être écrite dans le UserForm ‘UserForm Initialize’. Pour cette première méthode, il y a différentes étapes à suivre :

• Tout d’abord, il s’agit dans un premier temps de sélectionner la ComboBox ou ListBox concernée et vérifier que la propriété RowSource est vide.
• Ensuite, il faut double cliquer sur le userform créé pour accéder au code
• Enfin, il s’agit de composer le code avec l’instruction AddItem
• Après cela, il faut exécuter le programme afin de le tester.

Illustration :

Pour un exemple où il s'agit d'intégrer différentes projets, il faut agir comme suit :

 Private Sub UserForm_Initialize()
  'Cette procédure renseigne le ComboBox1 avec des valeurs
   ComboBox1.AddItem "Projet 1"
   ComboBox1.AddItem "Projet 2"
   ComboBox1.AddItem "Projet 3"
 End Sub

Ou

 Private Sub UserForm_Initialize()
  'Cette procédure renseigne la ListBox1 avec des valeurs
   ListBox1.AddItem "Projet 1"
   ListBox1.AddItem "Projet 2"
   ListBox1.AddItem "Projet 3"
 End Sub

On peut également remplir une liste à partir d'une plage de données sélectionnées : c’est conseillé et surtout utile lorsque on souhaite par exemple sélectionner uniquement les projets commençant par un caractère bien précis.

Illustration :

  Private Sub UserForm_Initialize()
   For Each projet in range("data!A1:A20")                                                                                       
   If left(projet.value,1)="1" then ComboBoxchoixProjet.AddItem (projet.value) 
  End Sub

Il est à noter qu’il existe également la méthode RemoveItem mais celle-ci fait exactement l'inverse de la méthode AddItem : elle permet de supprimer un élément de la ComboBox ou ListBox.

Via la fenêtre propriétés et VBA

Cela concerne principalement la propriété RowSource : elle permet d’affecter une référence à une plage de données ; elle attend un champ vertical. Pour cette seconde méthode, il y a différentes étapes à suivre :

• On inscrit d’abord les données qui nous intéressent pour cette manipulation dans une feuille Excel
• Poser un label sur le UserForm
• Poser une zone de liste modifiable sur le UserForm
• Rechercher la propriété RowSource de la ComboBox et entrer simplement :
  • 1ère option : les références de la feuille Excel comme ceci : Feuil1!A1:A3 – Il s’agit tout simplement de la source des données affichées dans la ComBox / l'adresse de la plage contenant les données ou,
  • 2^e option : un nom défini (onglet formules/définir un nom) faisant référence à la plage souhaitée ou,
  • 3^e option : la référence à une colonne d'un tableau Excel : par exemple Projet [Nucléaire] si le tableau s’appelle Projet et le titre de la colonne est Nucléaire. Pour cela il faut que les données source soient sous forme de tableau.

( Cette option présente un avantage comparé aux précédentes : elle permet d’intégrer automatiquement dans la liste sur le formulaire s’il y a ajout de données dans la colonne en question. )

Attention ! Il faut respecter les majuscules et minuscules dans les noms des tables et colonnes.

• Rechercher la propriété Value et lui donner comme valeur ‘Choisir’
• Poser un bouton de commande et entrer le code suivant :

  Private Sub CommandButton1_Click() 
     Label1.caption = ComboBox1.Value 
  End Sub

• Enfin, exécuter le programme pour s’assurer de son bon fonctionnement

Instruction Array

Par ailleurs, on peut utiliser un array ou tableau interne pour remplir une liste ou une combobox.

C’est utile dans le sens où cela évite de devoir créer un tableau avec les données nécessaires dans un classeur Excel. C'est donc pratique uniquement s’il n’y que quelques éléments à ajouter dans la liste de choix. L’utilisation des array présente un autre intérêt non négligeable. En effet, l’utilisateur peut avoir besoin de construire une liste à partir d'éléments qui auront été sélectionnés auparavant suivant des critères complexes. Le résultat de ce tri peut être stocké dans un array qui sera ensuite réinjecté dans la liste en question.

L’un des grands avantages des tableaux est leur très grande rapidité d'exécution : travailler avec les tableaux est beaucoup plus rapide que de travailler en faisant des boucles sur des plages de données de la feuille.

Illustration :

  Private Sub RemplirCombo() 
    ComboBoxProjet.List = Array("Un", "Deux", "Trois", "Quatre")
  End Sub

C’est utile dans le sens où cela évite de devoir créer un tableau avec les données nécessaires dans un classeur excel. C'est donc pratique uniquement s’il n’y que quelques éléments connus d'avance à ajouter dans la liste de choix.

Il existe également la propriété List qui elle est remplie par un tableau vertical ou horizontal.

Afficher par défaut un élément de la liste ou une case vide

C’est l’instruction listIndex qui permet d’afficher par défaut un élément de la liste.

Il est possible de forcer l’affichage du premier choix (ou d'un autre) élément de la liste en ajoutant l'instruction suivante :

  ComboBoxProjet.ListIndex = 0 (ou 1 ou 2...)

Pour forcer l’affichage d'une case vide dans une ComBox, il faut utiliser l'instruction suivante :

  ComboBoxProjet.ListIndex =  -1

Les listes multi colonnes

Il est envisageable de vouloir afficher non pas une mais deux voire deux, trois ou même quatre colonnes dans une seule liste.

La propriété ColumnCount est particulièrement utile dans ce cas là : elle permet d’indiquer le nombre de colonnes qu’il faudra par la suite compléter.

Il est plus simple de le faire à partir des données de la feuille.

Dans ce cas, il faut utiliser la propriété List: pour une ListBox ou une ComboBox multi colonnes, la propriété List attend un array à 2 dimensions afin de prendre en compte les lignes et les colonnes. Pour afficher une liste de 3 colonnes et de 7 lignes dans la ComboBox nommée Projet, il faudra agir comme suit :

Illustration :

  With Projet
  ColumnCount = 3
  List = Range("H2:J8").Value
  End With

La propriété list attend un array de valeurs. Il est donc parfaitement possible de remplir la liste multi-colonnes avec un array créé en VBA à partir d'autres données (feuille, autre array...).

C'est utile si l’on souhaite par exemple récupérer les données de la feuille puis les trier par ordre alphabétique ou ne conserver que les valeurs répondant à un critère particulier.

Par défaut, la propriété par défaut Value d'une ListBox ou d'une ComboBox renvoie la valeur de la première colonne. Si vous voulez que ce soit par défaut la valeur de la 2^e colonne ou autre, il faut le préciser en utilisant la propriété BoundColumn

Illustration :

  Projet.boundColumn = 2

Les listes à choix multiples

Les listes à choix multiples permettent de sélectionner plusieurs réponses. Ce n’est pas le cas des ComboBox.

Par défaut, un seul choix est possible mais il est possible d’autoriser le choix multiple par l’intermédiaire de l’instruction MultiSelect.

Illustration :

Il faut ajouter cette ligne dans le UserFor_Initialise :

  ListActivité.MultiSelect = fmMultiSelectExtended

Les boutons d'option et les cases à cocher

Il est possible de proposer un choix par défaut à l'utilisateur quand le formulaire s'affiche comme par exemple répondre "Monsieur" à la demande d’État Civil.

La propriété par défaut d'un bouton d'option ou d'une case à cocher est la propriété value : elle n'admet que deux valeurs : true ou false (vrai ou faux) et cela même si vous affichez "Monsieur" ou "Madame" sur vos options. On pourra bien entendu récupérer le genre par la suite quand le formulaire sera validé.

Pour initialiser le formulaire avec l'option "Monsieur" pré cochée, il suffit d'ajouter une ligne dans le code UserForm_Initialize.

Dans le cas où le bouton s’appelle OptF, on procède comme suit :

Illustration :

  OptF.value = true ou OptF = True 

La propriété value étant la propriété par défaut il n’est pas nécessaire de le préciser ainsi, les deux écritures sont équivalentes.

Il faut noter que les boutons d'option sont exclusifs ainsi si l’on écrit : OptM=true et OptF=true, seul le bouton OptF sera coché.

Pour ce qui est des cases à cocher, il en est tout autrement : il est possible de cocher plusieurs cases.

Les boîtes de texte et les labels

Il est possible d’afficher, sur le formulaire, une information qui n’a pas à être modifié par l’utilisateur comme par exemple la date ou le contenu d’une cellule.

Illustration :

Pour afficher la date du jour dans l'étiquette nommée LblDate :

  LblDate = Date

Il faut noter que ‘Date’ est une instruction de VBA qui affiche la date du jour.

Autrement, pour afficher le contenu de la cellule D2 de la feuille Data :

  LblX = sheets("data").range("D2")

Attention ! : Il n’est pas possible d’afficher une plage de données dans une étiquette ; Excel renvoie une erreur d'incompatibilité.

Il est également possible de pré remplir une boîte de texte, que ce soit pour mettre un texte indiquant à l'utilisateur ce qu’il doit remplir, ou pour proposer une réponse.

Illustration :

  TxtPrénom.Text = "saisissez votre prénom ou votre pseudo"

Tester votre formulaire

Il est possible de tester le formulaire à tout moment pendant l’écriture du code dans le UserForm_Initialize en appuyant sur la touche F5 avec le curseur placé n’importe où dans le code, ou sur le formulaire dans l'éditeur VBA. À ce stade du cours, l’on a un formulaire avec des listes pré remplies, des options pré côchées et des boites de texte vides ou pré remplies. Cependant, l'appui sur les boutons ou le choix dans une liste ne déclenche rien. Nous allons voir dans le paragraphe suivant comment activer les boutons et récupérer les informations saisies par l'utilisateur.

Afficher et masquer un formulaire

Comment afficher un formulaire grâce à un bouton ?

Pour pouvoir afficher un formulaire, nous pouvons utiliser deux méthodes : afficher le formulaire à l'ouverture du classeur ou à l'aide d'un bouton. Nous allons voir dans un premier temps la méthode du bouton placé sur la feuille

Bouton formulaire ou bouton activeX ?

Ouvrir Excel et placer un bouton sur la feuille Excel à l'endroit souhaité : pour effectuer cette manœuvre les étapes à suivre sont les suivantes :

• Aller dans l'onglet développeur
• Cliquer sur insérer

Excel nous propose d'insérer deux types de contrôles sur votre feuille : Des contrôles de type formulaire. Des contrôles de type ActiveX.

Définition

Un contrôle formulaire permet de faciliter l’organisation et la modification des informations. Les contrôles formulaires sont issus des versions antérieures d'excel, ils sont conservés pour des raisons de compatibilité d'excel.
Les contrôles Activex étaient connus sous le nom de contrôles OLE ou de contrôles OCX, en insérant dans une page Web ou autre programme des contrôles ActiveX d'autres utilisateurs pourront réutiliser les fonctionnalités insérées.

Nous allons nous intéresser aux UserForm VBA incluant des objets ActiveX.

1. Pour insérer un contrôle, sélectionnez le dans la boite à outils et faites le glisser dans le formulaire. Au moment du dépôt dans le formulaire, certains assistants se déclenchent (boutons, options, ...), puis entrez ses propriétés et nommez le grâce à la fenêtre propriétés.
2. Pour programmer un contrôle, double-cliquer dessus et renseigner le code VBA à lui associer

Afficher le formulaire avec un bouton placé dans la feuille

Nous allons voir les différents boutons proposés par l'onglet "Développeur"

Placer le bouton de votre choix sur la feuille excel. Dans l'onglet Développeur et cliquez dans propriétés.

Voici un bouton placé sur feuille Excel

La mise en forme du bouton peut être modifiable voici les actions pouvant être menées :

• Contrôler l’affichage définitif du formulaire
• Sélectionner et de désélectionner les contrôles afin de procéder à des modification supplémentaires.
• Modification du texte dans un contrôle, telle que la légende ou l’étiquette.
• Grouper, copier, déplacer et aligner les contrôles afin d’organiser la disposition du formulaire de feuille de calcul.
• Redimensionner les contrôles afin d’obtenir l’affichage souhaité.
• Positionner ou dimensionner un contrôle avec une cellule.
• Protéger les contrôles et les cellules en fonction de vos besoins de protection
• Activer ou désactiver l’impression des contrôles lors de l’impression du formulaire de feuille de calcul.
• Supprimer des contrôles qui sont inutiles.

Il faut créer un bouton dans la feuille (partie développeur → insérer → bouton - > cliquer sur la cellule choisie) et lui affecter la macro suivante.Une fois le bouton installé vous devez inscrire une instruction qui lance l’affichage d'un formulaire en appelant par son nom :

  Private Sub CommandButtonformulaire_Click() 
    UserFormprojet.Show
  End Sub

Cette instruction lance l’affichage du formulaire en commençant par la macro UserFormprojet.Show. Si Excel vous affiche un message d'erreur à ce stade, lancez votre formulaire en mode pas à pas : mettez vous dans l'éditeur VBA cliquez dans le formulaire et appuyez sur la touche F8 de façon répétée. Le code défile et vous verrez où se situe votre erreur.

Masquer un formulaire

L'instruction qui vous permettre de masquer le formulaire est la suivante :

            UsfAdhérent.hide

Mais si vous voulez faire réapparaître un formulaire dans l'état où il était lorsque vous l'avez masqué, utiliser l'instruction suivante :

            UsfAdhérent.show

Modification du formulaire en cours de saisie et validation des données

Modifier les couleurs d'un bouton

Cliquer sur le bouton "CommandButton1", puis affichage ⇒ Fenêtre Propriétés

http://bdiemert.free.fr/wikiversity/VBA/IDEFenetrePropriete.jpg

Dans la liste des Propriétés ⇒ Affichage "Par catégorie" ⇒ Menu "Apparence" ⇒ Propriété "BackColor" pour la couleur du fond (ou Propriété "ForeColor" Pour la couleur du texte) ⇒ Dans la liste déroulante à droite ⇒ Affichage "Palette" et sélection de la couleur

http://bdiemert.free.fr/wikiversity/VBA/IDEFenetrePaletteCouleurs.jpg

Modifier le texte et la police du bouton

Dans la liste des Propriétés ⇒ Affichage "Par catégorie" ⇒ Menu "Apparence" ⇒ Propriété "Caption" ⇒ Entrer le libellé : "Quitter"

Pour modifier la police ⇒ Menu "Police" ⇒ Propriété "Font" et sélectionner la police, le style et la taille

http://bdiemert.free.fr/wikiversity/VBA/IDEFenetrePolice.jpg

Résultat sur le formulaire

http://bdiemert.free.fr/wikiversity/VBA/FRMBoutonQuitter.jpg

Utiliser un intitulé (Label) et une zone de texte (TextBox)

Pour cet exemple nous allons créer 2 zones de texte, une pour le nom l'autre pour le prénom, 2 intitulés et un bouton "Valider".

• Placer 2 intitulés sur le formulaire, (le contrôle avec un « A ») l'une à côté de l'autre, dans la propriété "Caption" de la première taper : « Nom », dans la deuxième : « Prénom », puis utiliser les propriétes "BackColor, ForeColor, Font ou autres pour la mise en forme ⇒ http://bdiemert.free.fr/wikiversity/VBA/IDEBoiteOutilsLabel.jpg
• Sous chaque intitulé nous placerons 2 zones de texte (le contrôle avec « ab| ») ⇒ http://bdiemert.free.fr/wikiversity/VBA/IDEBoiteOutilsTexte.jpg
• Faire un double-clic sur la première (pour afficher le code) et taper le code suivant :

 Private Sub TextBox1_Change()
  [A1] = UserForm1.TextBox1
 End Sub

Ceci affiche dans la cellule A1 le texte tapé de la zone de texte 1 (TextBox1)

• Idem pour la deuxième mais avec ce code ci ⇒ http://bdiemert.free.fr/wikiversity/VBA/FRMNomPrenom.jpg

 Private Sub TextBox2_Change()
  [B1] = UserForm1.TextBox2
 End Sub

• • Change() est un des évènements prédéfinis qui surviennent lors des manipulations de zone de texte, comme les suivants
  • Click()
  • Initialize()
  • BeforeUpdate()
  • AfterUpdate()

Bouton "Valider"

Maintenant nous allons concaténer le nom et le prénom dans la cellule C1. Pour cela, nous allons créer un bouton, et rentrer dans la propriété "Caption", y écrire "Valider", puis dans la propriété ⇒ Apparence ⇒ (Name) le nommer : "Bt_Valider", Double-cliquer sur ce bouton et taper ce code :

 Private Sub Bt_Valider_Click()
  [C1] = UserForm1.TextBox1 & " " & UserForm1.TextBox2
 End Sub

Maintenant, nous allons afficher le formulaire, taper le nom dans la zone de texte 1 et le prénom dans la 2 puis cliquer sur le bouton « Valider » ⇒ http://bdiemert.free.fr/wikiversity/VBA/IDENomPrenomValider.jpg

Programmer une zone de liste (ComboBox)

Pour cet exemple nous allons créer un intitulé « Où habites-tu? » et une zone de liste « Les villes ».

Intitulé

Placer un intitulé sur l'UserForm, rentrer dans la propriété "Caption" : « Où habites-tu ? » puis utiliser les propriétés de mise en forme « BackColor, ForeColor, Font » etc.

Zone de liste (ComboBox)

Dans les cellules A1 à A15 de la feuille 2 (Feuil2) du classeur entrer des noms de ville. '11^e image' « http://imageshack.com/a/img89/4760/5wnz.jpg » ^{(Archive • Wikiwix • Que faire ?)}. Consulté le 2017-07-23

Revenir à l'éditeur Visual Basic (« Alt » + « F11 »), sélectionner le formulaire, y placer une zone de liste modifiable ⇒ '12^e image' « http://imageshack.com/a/img23/6808/nwxn.jpg » ^{(Archive • Wikiwix • Que faire ?)}. Consulté le 2017-07-23

Dans les propriétés ⇒ Données ⇒ RowSource, rentrer la zone ciblée : Feuil2!A1:A15 ⇒ '13^e image' « http://imageshack.com/a/img801/245/3pfh.jpg » ^{(Archive • Wikiwix • Que faire ?)}. Consulté le 2017-07-23

Double-cliquer sur la zone de liste (ComboBox) et rentrer ce code:

 Private Sub ComboBox1_Change()
  [C3] = UserForm1.ComboBox1
 End Sub

Utiliser un bouton Option

• Pour cet exemple nous allons créer 2 boutons d'option pour choisir entre : « Fille » ou « Garçon », il faut placer avant tout chose un cadre (Frame) sur l'UserForm. Propriété ⇒ Caption : « Fille ou garçon » ⇒ '14^e image' « http://imageshack.com/a/img20/8900/4bvh.jpg » ^{(Archive • Wikiwix • Que faire ?)}. Consulté le 2017-07-23
• Il faut ensuite placer sur ce cadre 2 boutons d'option, nommer le 1er : « fille », le 2^e : « garçon », se servir des propriétés « BackColor, ForeColor, Font etc » pour la mise en forme ⇒ '15^e image' « http://imageshack.com/a/img4/6462/anbj.jpg » ^{(Archive • Wikiwix • Que faire ?)}. Consulté le 2017-07-23
• Double-cliquer sur le premier bouton (Fille) et entrer ce code:

 Private Sub OptionButton1_Click()
  If Controls("OptionButton1").Value = True Then
   [C4] = "Vous êtes une " & Controls("OptionButton1").Caption
  End If
 End Sub

Ce qui écrira dans la cellule C4 "Vous êtes une fille" lorsque ce bouton sera coché

• Double-cliquer sur le deuxième bouton (Garçon) et saisir le code :

 Private Sub OptionButton2_Click()
  If Controls("OptionButton2").Value = True Then
   [C4] = "Vous êtes un " & Controls("OptionButton2").Caption
  End If
 End Sub

Cela écrira dans la cellule C4 "Vous êtes un garçon" lorsque ce bouton sera coché ⇒ '16^e image' « http://imageshack.com/a/img191/7605/nwl1.jpg » ^{(Archive • Wikiwix • Que faire ?)}. Consulté le 2017-07-23

Contrôler une saisie dans une TextBox

Nous allons vérifier maintenant la saisie d’une TextBox à l’aide d’un nouvel exemple :

1. Placer un label en haut du formulaire, Propriété ⇒ Caption : Indiquez votre prénom
2. Placer une TextBox dessous
3. Le bouton de commande tout en bas, Propriété ⇒ Caption : Valider ⇒ '17^e image' « http://imageshack.com/a/img41/4899/bcy4.jpg » ^{(Archive • Wikiwix • Que faire ?)}. Consulté le 2017-07-23
4. Programmer le bouton "Valider" via un double-clic et la saisie du code suivant :

 Private Sub CommandButton1_Click()
  If Controls("Textbox1") = "" Then
   MsgBox "Vous devez ABSOLUMENT indiquer votre prénom !", vbExclamation, "ERREUR"
   Controls("Textbox1").SetFocus
  End If
  [A1] = UserForm1.TextBox1
 End Sub

Tant qu'au moins une lettre du prénom ne sera pas saisie, le focus replacé sur la TextBox1, sinon le prénom s'affichera en A1 ⇒ '18^e image' « http://imageshack.com/a/img51/1829/2z3r.jpg » ^{(Archive • Wikiwix • Que faire ?)}. Consulté le 2017-07-23

Transfert de données du formulaire dans la feuille

Boîtes de texte

De base, la propriété d’une zone de texte par défaut est le texte. Pour mettre le contenu de cette boîte de texte dans une cellule de la feuille de calcul, écrivez :

  Range("C1").value = TextBox1
  Range("C2").value = TextBox2

Il y a bien sûr la possibilité d’effectuer une boucle afin d’affecter une valeur aux TextBox de façon automatique. (Voir exercice associé à cette leçon).

Exercice :

 Private Sub RangeBoitesDansCellules()
    ' se positionne sur la feuille suivi
    Set f = Sheets("Tb suivi Projets + MCO + Act")
    ' teste l’ordre du projet sélectionné
    If numeroDeProjet >= 0 Then
        'initialise les lignes de début et de fin
        'en fonction de l’ordre du projet dans le combo
        ligned = 3 puis 11 puis 19 puis 27 ...
        lignef = 8 puis 16 puis 24 puis 32
        ligned = (numeroDeProjet * 8) + 3
        lignef = ligned + 5
    End If
    ' initialiser les box
    boite = 0
    ' boucle sur les lignes de début à fin
    For ligne = ligned To lignef
        ' boucle sur les colonnes C à N
        For colonne = 3 To 14
            ' change de textbox à chaque tour
            boite = boite + 1
            ' range dans la cellule pointée par la colonne et la ligne
            ' la valeur de la boite de texte pointée par le numéro de box
            f.Cells(ligne, colonne) = Me.Controls("TextBox" & boite).Value
        ' va à la colonne suivante
        Next colonne
    ' va à la ligne suivante
    Next ligne    
  End Sub

'On peut aussi affecter cette valeur à une variable :'

Dim Nom as String
Nom = TextBox1

ComboBox

Concernant la propriété d'une ComboBox, par défaut c’est .Value. Contrairement aux TextBox qui ne renvoient que du texte qu’il faut ensuite éventuellement transtyper (changer le Type texte en nombre, date...), Excel reconnait le type de donnée renvoyé par une liste. Dim Activité as String 'à modifier si la liste contient une date ou un nombre ! Activité= ListActivité Range("F2")=Activité ou bien directement Range("F2")=ListActivité

Exercice :

 Private Sub RangeProjetsDansCombo()
    ' se positionne sur la feuille projet
    Set f = Sheets("Projets")
    'initialise les variables de projets
    numeroDeProjet = -1
    'Alimentation de la combobox avec le nom des différents projets
    For projet = 2 To 4
        ComboBoxChoixProjet.AddItem f.Cells(projet, 1)
    Next projet         
 End Sub

Exercices

Exemple d'initialisation d'un formulaire

Le code suivant initialise le formulaire en masquant et minimisant Excel, en réglant taille, hauteur, largeur et position

 Sub monFormulaire_Initialize()
  Excel.Application.Visible = False  ' on peut masquer les feuilles de calculs
  WindowState = Excel.XlWindowState.xlMinimized  ' ou bien les minimiser
  Me.Width = Application.Width  ' ou encore recouvrir toute leur largeur
  Me.Height = 590  ' définir de la hauteur en pixel
  Me.Top = 0 'positionner en haut
  Me.Left = Application.Left + Application.Width - Me.Width  ' positionner le plus à droite possible
 End Sub

Exemple de code associé à un contrôle

Pour commencer à associer du code à un contrôle, il est pratique de double cliquer dessus car cela crée automatiquement l'en-tête de la fonction.

Le code suivant rend visible un bouton quand on coche une case, et le masque quand on la décoche

 Sub maCasaCocher_Click()
  If maCasaCocher.maCasaCocher= True Then
   Me.monBoutonaCacher.Visible = True
   Function
  Else
   Me.monBoutonaCacher.Visible = False
  End If
 End Sub
 End Function

Exemple de création d'un formulaire personnalisé

Ci-dessous, le lien pour l'exercice de création de formulaire personnalisé :

• Macros-commandes VBA/Exercices/Création de formulaire personnalisé

Les formulaires Access et Excel

Description

Qu'est ce qu'un formulaire ?

Un formulaire est un document conçu avec une structure et un format standards qui permettent de faciliter la capture, l’organisation et la modification des informations.

Les formulaires imprimés contiennent des instructions, des mises en forme, des étiquettes et des espaces vides pour écrire ou taper des données. Vous pouvez utiliser Excel et les modèles Excel pour créer des formulaires imprimés.

Types de formulaires Excel

Vous pouvez créer plusieurs types de formulaires dans Excel : formulaires de données, feuilles de calcul contenant des contrôles de formulaire et des contrôles ActiveX, et formulaires utilisateurs VBA (Visual Basic Applications). Vous pouvez utiliser chaque type de formulaire de manière autonome, ou les combiner de différentes façons pour créer une solution qui vous convienne. Nous allons dans ce chapitre les formulaires utilisateurs VBA.

Un peu de vocabulaire avant de commencer

Dans les modules Userforms (formulaire), toutes les procédures sont des actions répondant aux contrôles ActiveX (formulaire, bouton, boite de texte, case à cocher, …) inclus dans le formulaire.

Propriétés du formulaire

Name : permet de définir le nom du formulaire

Caption : permet de définir le titre du formulaire

Méthodes du formulaire

Minimize : rapetisse le formulaire

Load : charge en mémoire et ouvre le formulaire

Show : affiche le formulaire

Événements du formulaire

Change : permet de déclencher des actions sur changement du formulaire

Initialize : permet de déclencher des actions sur chargement du formulaire

Activate : permet de déclencher des actions sur activation du formulaire

Close : permet de déclencher des actions sur fermeture du formulaire

Comment créer son formulaire simple ?

Avant toute chose, une fois votre fichier Excel ouvert, allez dans Fichier, Option et cochez Afficher l'onglet développeur dans le ruban. Une fois cette option activée, placez-vous sur l'onglet développeur et cliquez sur Visual Basic à gauche du ruban. Une nouvelle fenêtre VBA s'affiche. Cliquez maintenant sur Insertion : UserForm. La base de votre formulaire est créée qui s’appelle userform1. À ce stade, diverses fenêtres se sont affichées :

Fenêtre projet

Affiche tous les conteneurs de code VBA (modules, formulaires, …)

Fenêtre code

Affiche tous les codes VBA (modules, formulaires, …), dans le cas des formulaires, affiche aussi le design de la fenêtre

Fenêtre propriété

Affiche toutes les propriétés des objets sélectionnés (modules, formulaires, listes, …)

Une boite à outils est également apparue; elle vous servira à construire votre formulaire.

Vous pouvez modifier le nom de votre formulaire userform 1 dans la fenêtre propriété, name.

Les principaux contrôles de la boite à outils

Intitulé : texte simple non modifiable par l'utilisateur : titre, étiquette accompagnant une zone de texte

Zone de texte : champ texte accessible en saisie

Zone de liste modifiable : permet de sélectionner 1 à plusieurs valeurs dans une liste prédéfinie

Zone de liste : permet de sélectionner 1 à plusieurs valeurs dans une liste prédéfinie

Case à cocher : permet de sélectionner 0 ou plusieurs options parmi celles proposées

Bouton d'option : permet de sélectionner une option parmi plusieurs proposées

Bouton bascule : bouton prenant deux états : appuyé ou relâché

Cadre : contient d'autres contrôles

Bouton de commande : permet d'exécuter une action en cliquant dessus (ouvrir un formulaire, calculer, etc.)

Contrôle d'onglet : permet de définir différents onglets contenant des informations différentes

Image : permet d'insérer une image dans le formulaire

Les Assistants : uniquement pour certains contrôles simples (boutons, options, etc.)

Sélectionnez un contrôle de la boite à outils afin de déterminer la forme et, en restant appuyé dessus, faites le glisser dans userform1.

Tapez vos données à l'intérieur et nommez le dans la fenêtre propriétés.

Recommencez autant de fois que nécessaire.

Création

Il suffit de faire glisser les objets de la boite à outils pour remplir un formulaire vierge

Access et Excel permettent l’utilisation de formulaires, en plus de leurs bases de données et feuilles de calcul.

Pour créer un userform dans Excel, il faut passer en vision du code : ALT + F11. Ensuite dans l'arborescence à gauche, le clic droit permet la création de trois type d'objets : feuille, userform et module.

Si les modules ne peuvent contenir que du Visual Basic, les feuilles et userform peuvent en comporter également.

Pour qu'un userform se lance automatiquement au démarrage, ajouter le code suivant dans une feuille :

Sub workbook_open()
    MonFormulaire1.Show vbModeless
End Sub

• On désigne donc le formulaire par son nom (MonFormulaire1 ci-dessus). Mais dans le code du formulaire, on peut utiliser Me à la place.
• Supprimer la mention vbModeless ci-dessus entraine l'impossibilité d'ouvrir d'autres fichiers Excel pendant l’utilisation du formulaire.

Le code suivant initialise le userform qui le contient, en effet le nom de cette fonction est connu et la lance automatiquement au lancement :

Sub UserForm_Initialize()
  Excel.Application.Visible = False  ' on peut masquer les feuilles de calculs
  WindowState = Excel.XlWindowState.xlMinimized  ' ou bien les minimiser
  Me.Width = Application.Width  ' ou encore recouvrir toute leur largeur
  Me.Height = 590  ' définition de la hauteur en pixel

  Me.StartUpPosition = 0  ' position du userform
  Me.Top = 0
  Me.Left = Application.Left + Application.Width - Me.Width  ' le plus à droite possible
End Sub

Ensuite, en mode création la boite à outil permet d'ajouter des objets dans le userform (boutons, champs...). Nommer ces objets dans leur propriété permet de le appeler ensuite :

  MonFormulaire1.MonChamp1 = "Hello the world !"

Les principaux contrôles et leurs propriétés

Frame

Cadre permettant de subdiviser le formulaire en plusieurs sous-parties.

Button

• Name
• Backcolor
• Enabled
• Location
• Size
• TabIndex
• TabStop
• Tag
• Text
• Textalign
• Visible

Label

Intitulé.

LinkLabel

Hyperlien.

TextBox

Champ pouvant être complété par l'utilisateur.

CheckBox

Case à cocher pour questionnaire à choix multiple.

OptionButton

Le bouton d'option (ou bouton radio) permet de ne choisir qu'une seule réponse dans une liste.

Pour définir le groupe de boutons dans lequel une seule case pourra être cochée (égale à True), il faut préciser son nom dans la propriété GroupName de chaque bouton.

ListBox

Menu déroulant. La différence avec ComboBox est qu'elle n’est pas modifiable.

Image

PictureBox

Ligne - Line

Forme géométrique - Shape

Explorateur de disques

Explorateur de dossiers

Explorateur de fichiers

Une fois tous les objets placés à leurs emplacements définitifs dans le formulaire, il faut ordonner leur paramètre Tab_Index (en commençant à 0) pour que l'utilisateur puisse les parcourir normalement en appuyant sur TAB.

Exemple de code associé à un contrôle

Pour commencer à associer du code à un contrôle, il est pratique de double cliquer dessus car cela crée automatiquement l'en-tête de la fonction.

Celle ci-dessous affiche un bouton quand on coche une case, et le masque quand on la décoche :

Sub MaCasaCoche_Click()
    If Me.MaCasaCoche = True Then
        Me.MonBoutonCache.Visible = True
    Else
         Me.MonBoutonCache.Visible = False
    End If
End Sub

Inventaire des composantes d'un formulaire

Pour lister tous les objets que contient un formulaire :

    Dim ctrl As MSForms.Control
    For Each ctrl In Me.Controls
      MsgBox ctrl.Name
    Next

Compiler un exécutable

Pour créer une application .exe standard avec Visual Basic, il faut réer un nouveau projet ActiveX EXE.

Les applications MDI permettent l’utilisation de sous-fenêtres, par opposition aux SDI (une seule fenêtre) aux TDI (plusieurs onglets).

Références

• http://www.fontstuff.com/vba/vbatut09b.htm
• ftp://ftp-developpez.com/silkyroad/VBA/UserForm/UserForm.pdf

Annexes

Bibliographie

• Amelot M., 2011, VBA Excel 2010, ENI, St Herblain
• Premium Consultants, 2010, VBA pour excel 2010 , Micro Application, Paris
• Jensen O.G., 2003, Initiation à la programmation VBA Word, KNOWWARE, Ecuelles
• Greg Harvey, 2013, Excel 2013 et VBA pour le nuls, Editions Générales First
• Henri Laugié , 2003, VBA Excel : entrainez vous à créer des applications professionnelles , Microsoft
• Mikael Bidault, 2013, Excel vba developpez des macros compatibles avec toutes les versions d excel , Pearson education

Liens internes

• Formulaire simple : exercice simple sur boutons de formulaire
• Leçon VISUAL BASIC
• Leçon Tableur EXCEL
• Les formulaires VBA

Liens externes

• Utiliser les formulaires VBA
• Utiliser les contrôles dans les formulaires VBA
• ActiveX
• Interfaces VBA
• un formulaire personnalisé
• Visual Basic .NET/Contrôles sur Wikilivres

Création de Fonction

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Création d'une fonction

Définition

Une fonction est un sous-programme qui permet d'exécuter une série d'instructions et retourne une valeur typée à la fin de ce traitement. Cette valeur peut être par la suite exploitée par une autre procédure, fonction ou application. Et la notion de sous-programme quant à elle correspond à une fonction ou procédure dans la plupart des langages de programmation

Différence entre les mots clés Sub et Fonction

• Une procédure Sub est une suite d'instructions qui peut être appelée et exécutée par l'utilisateur ou par une autre procédure et ne renvoie pas de valeur de sortie
• Une procédure Function (ou fonction) est une suite d'instructions qui peut être appelée et exécutée par l'utilisateur ou par une autre procédure et qui renvoie à chaque utilisation une valeur, tout comme les fonctions EXCEL et les fonctions intégrées de VBA, les fonctions peuvent être utilisées dans deux situations :
  • Dans une procédure VBA en tant qu'élément d'une expression
  • Dans une feuille de travail EXCEL directement avec les formules Standard EXCEL

Début de l'exemple

Exemple

FONCTION :

  Function carre(nombre)
     carre = nombre ^ 2 'La fonction "carre" renvoie la valeur de "carre"
  End Function

PROCEDURE :

   Sub macro_test()
    Dim resultat As Double
    resultat = carre(9.876) 'La variable resultat reçoit la valeur retournée par la fonction
    MsgBox resultat 'Affiche le résultat (ici, le carré de 9.876)
   End Sub

Fin de l'exemple

Utilité des fonctions personnalisées

Excel contient plus de 300 fonctions prédéfinies dans une feuille de travail. Mais si cela ne suffit pas, grâce à VBA, on peut créer des fonctions personnalisées.

En effet, les fonctions personnalisées sont utiles dans les formules de feuille de travail Excel et les procédures VBA. Les fonctions personnalisées simplifient le travail et permettent à l’utilisateur de gagner du temps.

Par exemple, la création d'une fonction personnalisée capable de raccourcir les formules de manière significative.

Début de l'exemple

Exemple

Supposons le cas d'un calcul de prime tel que le niveau de prime dépende du montant des ventes.

Sous Excel, on utiliserait la fonction : =SI(B5>1000;B5*3/100;SI(B5>800;B5*1/100;B5*0,5/100)).

Cependant, si on veut modifier par la suite cette fonction, on sera obligé de modifier toutes les cellules où elle a été introduite et on conserve une certaine complexité de l'écriture.

Alors que sous VBA, il est possible de déclarer la fonction (plus lisible et plus facile à corriger en cas d'erreur) :

  Function calculPrime(montant as Double) as Double
     If montant>1000 then
       calculPrime=montant*3/100
     ElseIf montant>800 then 
       calculPrime=montant*1/100
     Else
        calculPrime=montant*0.5/100
     End If
  End Function

Dans ce cas, il suffit d'introduire dans une case Excel : =calculPrime(B5) pour obtenir le même résultat qu'avec une fonction SI imbriquée.

Fin de l'exemple

Attention, suivant la qualité de la programmation, les fonctions personnalisées peuvent être beaucoup plus lentes que les fonctions intégrées. Tout utilisateur peut appeler la fonction à partir d'une procédure ou de l'interface EXCEL. Une fonction personnalisée permet en principe d'éliminer les redondances de code et de réduire le nombre d'erreurs.

L'écriture d'une fonction personnalisée

Pour créer une fonction Excel sous VBA plusieurs étapes sont nécessaires :

• Ajouter le menu "Développeur" au ruban Excel s'il n'apparait pas
• Ouvrir l’éditeur VBA en sélectionnant la case "Afficher l'onglet Développeur dans le ruban"
• Entrer dans l'éditeur VBA et sélectionner l'icône "Visual Basic".
• clic droit sur la case Module ==> Insertion ==> Module (on peut aussi créer une fonction qui sera rattachée à une feuille de calcul ou à un classeur plutôt qu’à un module)

Instruction de création de fonction sous VBA

• L'éditeur VBA présente alors une page vide dans le Module1 (ou 2 ou 3 ou ...) sur laquelle le code VBA sera saisi
• Écrire la fonction : la déclaration d'une fonction commence par le mot clé "Function" suivi du nom de la fonction, puis d’une liste d’arguments obligatoires ou facultatifs entre parenthèses séparés les uns des autres par une virgule. Le mot clé "End Function" marque la fin de la fonction. Une fonction a la structure suivante :

Structure :

Début
 déclarations de variables
 séquences d'actions
 renvoi d'une valeur
Fin

Exemple :

 Function diviser_par_1000(Cellule_a_diviser As Double)
  diviser_par_1000 = Cellule_a_diviser / 1000
 End Function

Conseil

Le nom du module courant peut être retrouvé par ses fonctions ainsi :

 MsgBox Application.VBE.ActiveCodePane.CodeModule.Name

Utilisation d'une fonction personnalisée sous VBA

La création de formules dans Visual Basic est un outil important si l’on souhaite personnaliser son outil ou utiliser dans des procédures des calculs nécessitant une formule non présente dans Excel.

• Pour créer une formule dans VBA, ouvrons Visual Basic (par exemple ALT+F11), puis ouvrons un module dans un classeur vierge
• Dans le même module on va appeler la fonction Sub AppelFonction()
• Ensuite, il faut lui transmettre les paramètres :

  Sub AppelFonction()
    Dim variable1 As Byte, variable2 As Byte
      variable1 = 3
      variable2 = 5
      resultat = variable1 * variable2
      MsgBox resultat
  End Sub

• il est aussi conseillé de décrire ce que fait la fonction

  ' déclarer les variables
   Dim variable1 As Byte, variable2 As Byte
  ' initialiser les constantes
      variable1 = 3
      variable2 = 5
  ' récupérer le résultat saisi par l'opérateur
      resultat = variable1 * variable2
      MsgBox resultat
    End Sub

• Enfin, fermer la fenêtre appuyer sur la touche F5 pour lancer la macro.

Utilisation d'une fonction personnalisée sous Excel

La fonction nouvellement créée est enregistrée dans Excel et peut être utilisée de la même manière que toutes les autres fonctions initiales d'Excel.

• Sélectionnez la cellule où vous désirez voir apparaître le résultat de la fonction (exemple la cellule: D5).
• Cliquez sur Insérer une fonction du Menu principal
• Vous obtenez la fenêtre «Insérer une fonction»
• Vous choisissez la catégorie «Personnalisées» et dans "Sélectionner une fonction:" vous choisissez la fonction crée

Méthode d'Insertion une fonction personnalisée sous Excel

• Cliquez sur le bouton OK
• Vous obtenez une fenêtre intitulée "Arguments de la fonction". Ici, elle vous demande les ventes et l'ancienneté. Vous indiquez la cellule qui contient les ventes (exemple la cellule: B5) et l'ancienneté (exemple la cellule: C5).

Méthode de sélection d'un argument issu d'une fonction personnalisée sous Excel

Description de la signature d'une fonction

Les signatures des fonctions (1ère ligne correspondant à la déclaration de la fonction) peuvent être beaucoup plus complexes, et leur analyse peut être utile à la compréhension de leur comportement.

Structure :

[Public | Private | Friend] [Static] Function name [(liste d'arguments)] [As type]
 (séquences d'instructions)
End Function

En effet, pour créer une procédure fonction, il faut respecter les étapes suivantes :

1. déterminer la portée de la fonction
2. déclarer la procédure en fonction avec le mot clé: Function, suivi du nom de la procédure
3. définir les arguments en les indiquant entre parenthèse après le nom de la procédure
4. préciser le type de la valeur retournée après le mot clé As

La portée d'une fonction

La notion de portée, parfois appelée visibilité, défini les limites d’accessibilité d'une variable. Il existe plusieurs instructions de déclarations selon la portée désirée et la déclaration ne se fait pas au même endroit. Et leurs utilisations sont facultatives.

• Public : une fonction créée est par défaut de type Public. Il n'est donc pas nécessaire d'écrire le mot Public devant l'instruction Function. La fonction sera alors accessible depuis tous les modules.
• Private : pour rendre une fonction utilisable uniquement par les procédures VBA du module dans lequel elle est déclarée, il faut faire précéder le mot Function par le mot Private. Cette fonction pourra alors être utilisée dans la feuille de calcul mais n'apparaitra pas dans la boîte de dialogue "Insérer les fonctions".
• Friend : La fonction sera alors accessible depuis tous les modules du projet en cours, le mot clé Friend est utilisé uniquement dans un module de classe.

Le nom de la fonction

Dans VBA, le nom de la fonction sert à la fois à l'identifier et à stocker la valeur de retour. Mais, il peut aussi être considéré comme une variable locale à la fonction, créée automatiquement lors de l'appel, et utilisable à ce titre comme toute autre variable locale.

Le nom de la fonction jouant le rôle d'une variable locale à la fonction, il est possible de l’utiliser tout au long de la procédure ce qui permet d'économiser la création d'une variable temporaire supplémentaire .

 Function MAFONCTION(Param1,... ParamN) As Double
   MAFONCTION = Param1 + Param2 /5
   ' [...]
   MAFONCTION = MAFONCTION ^ 2
 End Function

Pour faciliter la création de fonction, le nom doit respecter des conventions standards d'affectation, comme :

• nommer les variables en évitant les mots clés ou instructions réservés par Excel (par exemple Val, Left...)
• nommer les variables en commençant par un caractère alphabétique et ne pas excéder 255 caractères
• nommer les variables sans caractères spéciaux (#@+ ...), seul le caractère underscore _ est accepté
• donner des noms les plus explicites possibles afin de faciliter la relecture de votre programme
• Il est conseillé d’avoir au moins une majuscule dans la variable déclarée. Ensuite lors de la saisie de la variable en minuscule dans la macro, celle-ci reprendra automatiquement la majuscule: cette astuce permet de vérifier les fautes d'orthographe éventuelles.

Les arguments de la fonction

Les arguments se déclarent lors de la définition de la procédure.

Les paramètres suivent les règles de déclarations suivantes :

• Un mot clé spécifique à la déclaration d'arguments
• Le nom de l'argument
• Son type
• Éventuellement sa valeur

Déclaration :

 [Optional] [ByVal] [ByRef] [ParamArray] variable As Type

• L'option Optional : indique que l'argument est facultatif. Tous les arguments facultatifs doivent être situés en fin de liste des arguments, et être de type Variant.
• L'option ByVal : indique que l'argument est passé par valeur.
• L'option ByRef : indique que l'argument est passé par référence. C'est l'option par défaut.
• Le mot clé ParamArray : utilisé uniquement comme dernier argument de la liste pour indiquer que celui-ci est un tableau facultatif d'éléments de type variant. Il ne peut être utilisé avec les mots clés ByVal, ByRef ou Optional.
• Type : précise le type de données de l'argument passé à la procédure (Byte,Integer, Long,..)

Cependant, il faut retenir que:

• les arguments peuvent être des variables , des constantes, des valeurs littérales ou des expressions
• Certaines fonctions n'ont pas d'argument
• Certaines fonctions ont un nombre fixe d'arguments requis (de 1 à 60)
• Certaines fonctions ont une combinaison d'arguments requis et facultatifs

En effet, les fonctions sont constituées d'arguments obligatoires et optionnels. Comme leur nom l'indique, les arguments obligatoires sont nécessaires au bon fonctionnement de la procédure. Quant aux arguments optionnels, lorsqu’ils sont omis, cela n'empêche pas l'exécution de la macro. Les arguments optionnels sont obligatoirement placés en fin de la déclaration.

La valeur de retour de la fonction

Contrairement à une procédure, une fonction retourne un résultat. Ainsi, l'option As type permet de spécifier le type de la valeur retournée.

La syntaxe est la suivante :

  Function nom_fonction(paramètre1, paramètre2,,....) AS type
    instruction1
    instruction2.....
   nom_fonction=valeur_retour
  End Function

Un nombre quelconque d'assignations de ce type peut apparaître n’importe où dans la procédure. Si aucune valeur n'est attribuée à l'argument nom_fonction, la procédure renvoie une valeur par défaut :

• Une fonction numérique renvoie la valeur 0
• Une fonction de type String renvoie une chaîne de longueur nulle ""
• Une fonction de type Variant, la valeur Empty
• Une fonction de type Object renvoie Nothing

Appel d'une fonction

VBA comporte des fonctions financières et mathématiques. En effet, dans l'explorateur d'objet et à l'intérieur de la librairie VBA, on peut trouver la liste des fonctions.

Néanmoins, pour les fonctions mathématiques, le nombre de fonctions peut être très modeste. Ceci est évidemment la conséquence du fait que l’on a déjà à notre disposition l’ensemble des fonctions dans la feuille de travail Excel.

Pour faire appel à ces fonctions sous VBA, il convient soit de taper Application.WorksheetFunction ou plus simplement WorksheetFunction en appliquant à ceci soit un nombre, soit une variable, soit une plage.

Ainsi par exemple, pour calculer la racine carrée de 25, on pourra si l’on utilise la fonction Racine (Sqrt en anglais) d'Excel, soit taper directement :

WorksheetFunction.Sqrt(25)

soit si 25 est par exemple la valeur de la cellule B4 :

WorksheetFunction.Sqrt(Range(B4))

ou encore :

WorksheetFunction.Sqrt(Cells(4,2))

soit si 25 est la valeur affectée à une variable de type Double appelé x :

WorksheetFunction.Sqrt(x)

Description personnalisée d'une fonction

Contrairement aux fonctions natives d'Excel ou VBA, les fonctions personnalisées n'ont aucune description. Pour cela, dans l’éditeur de macros, utiliser le raccourci clavier F2 pour afficher l'explorateur d'objets puis sélectionner "VBAproject" dans le menu déroulant

Ainsi, la fonction apparait dans la fenêtre de droite.

Faire un clic droit, Sélectionner l'option "Propriétés", Saisissez la description

Option de membre

Vous pouvez visualiser le résultat en affichant la boîte de dialogue "Insérer une fonction":

Description d'une fonction

Les fonctions VBA personnalisées

VBA offre la possibilité de créer des fonctions personnalisées, en voici quelques exemples :

Calcul des intérêts

Grâce aux fonctions personnalisées, il est possible de créer une fonction permettant de calculer plus facilement le montant des intérêts à verser en fonction du montant du Capital, du taux d'intérêt et de la durée de l'emprunt.

  Function CalculerInteret (ByVal Capital As Long, ByVal Taux As Double, ByVal Duree As Integer) As Currency
    CalculerInteret = Format ( Capital * Taux / 100 * Duree), "Currency")
  End Function

Le mot clé Function indique le début de la fonction qui a pour nom CalculerInteret, le mot clé End Function indique la fin de la fonction Le mot ByVal permet d'indiquer à la procédure qu'elle doit passer l'argument qui suit en valeur

• Long signifie que le capital est un nombre entier d'une grande valeur.
• Double signifie que le taux est un nombre à décimale.
• Integer indique que la durée est un nombre entier compris entre -32 768 et 32 767.
• Currency spécifie que le montant de l’intérêt calculé sera un nombre décimal de grande valeur.

Cette fonction calcule le montant des intérêts en multipliant le capital par la durée et par le taux d'intérêt divisé par 100 (exemple: 5/100 si le taux est de 5%).

Par exemple, pour un emprunt de 10 000 € sur une durée de 1 an à un taux d'intérêt de 3 %, le montant des intérêts peut se calculer par la formule : = 10000*(3/100)*1.

En utilisant la fonction CalculerInteret, l'utilisateur calculera le montant des intérêts à payer directement même si les montants des capitaux et les taux d'intérêts sont différents.

Calcul d'un montant HT en fonction du Taux de TVA

Cette fonction est une fonction personnalisée qui permet de calculer le montant Hors Taxe en fonction du montant TTC et du taux de TVA

  Function HT(Montant, TauxTva)
    HT= (Montant / (100 + TauxTva) * 100) 
  End Function

La Fonction se nomme HT et dépend de deux informations, le montant et le taux de TVA. Pour calculer le montant HT, la fonction va diviser le montant par le taux de TVA auquel on aura ajouté 100, puis va diviser le résultat par 100. Par exemple pour un montant de 12 000 € et un taux de TVA à 20 %, le calcul de montant Hors taxe se ferait par la formule : = (12000/(100+20)*100). En utilisant la fonction HT, l'utilisateur pourra calculer le montant HT plus facilement et plus rapidement, surtout si les taux de TVA diffèrent d'un produit à l'autre.

Calcul d'un montant TTC en fonction du Taux de TVA

Cette fonction permettra de calculer le montant TTC en fonction du montant Hors taxe et du taux de TVA

  Function TTC(HorsTaxe, TauxTva) 
    TTC = (HorsTaxe + (HorsTaxe * TauxTva) / 100)
  End Function

La Fonction se nomme TTC et dépend de deux informations, le montant HT et le taux de TVA. Pour calculer le montant TTC, la fonction va multiplier le montant HT par le taux de TVA puis va ajouter 100 au résultat obtenu, et enfin va diviser le résultat par 100. Par exemple pour un montant HT de 1 000 € et un taux de TVA à 19,6 %, le calcul de montant TTC se ferait par la formule : = (1000+(1000*19,6)/100). En utilisant la fonction TTC, l'utilisateur pourra calculer le montant TTC plus facilement et plus rapidement.

Calcul de la rentabilité globale

Cette fonction permettra de calculer la rentabilité globale d'une opération

  Function RentabilitéGlobale(Résultat_net, Valeur_Ajoutée)
    RentabilitéGlobale = Résultat_net / Valeur_Ajoutée
  End Function

La fonction a pour nom RentabilitéGlobale. Elle dépend de deux informations, le Résultat Net et la Valeur Ajoutée. La fonction va donc calculer la rentabilité en divisant le montant du Résultat Net par le montant de la Valeur Ajoutée. Par exemple, pour un Résultat net de 20 000 € et une Valeur ajoutée de 10 000, la fonction RentabilitéGlobale trouve une rentabilité de 2. Cela facilitera donc le travail de tous les comptables et autres gestionnaires.

Annexes

Bibliographie

• Ouvrage complet sur l’utilisation d'EXCEL et VBA dans le domaine financier ==> Chelali HERBADJI, 2012, "La gestion sous EXCEL et VBA : Techniques quantitatives de gestion", Groupe Eyrolles

Liens internes

• Testez vos connaissances au travers d'un Quiz : Macros-commandes VBA/Quiz/QCM Les fonctions VBA
• Appliquez vos connaissances en résolvant l'exercice : Macros-commandes VBA/Exercices/Calcul d'une commission
• Gérez vos erreurs grâce à l'annexe gestion des erreurs : Macros-commandes VBA/Annexe/La gestion des erreurs
• Quelques fonctions financières existantes : Macros-commandes VBA/Annexe/Quelques fonctions Financières sous VBA

Liens externes

• Pour un apprentissage virtuel de la création de fonction : http://www.dailymotion.com/video/xhl30u_fonction-vba-excel_tech
• Cours complémentaires sur les procédures et les fonctions : http://www.excel-pratique.com/fr/vba/procedures_fonctions.php

Création de Graphique

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Définitions

Qu’est-ce qu’un graphique ?

Un graphique permet de représenter des données chiffrées pour qu’elles soient plus lisibles. Il s'agit d'une "représentation visuelle".

Ainsi, un graphique permet de mieux comprendre les chiffres et de mieux les analyser. Cela permet également d'expliquer certains phénomènes. On voit plus facilement les relations entres plusieurs données.

Excel donne la possibilité à ses utilisateurs de créer un graphique à partir d'une base de données.

Excel permet à ses utilisateurs d’avoir un large choix dans les types de graphiques :

• Histogramme
• Courbes
• Secteurs
• Barres
• Aires
• Nuage de points
• Boursier
• Surface
• Anneau
• Bulles
• Radar

Remarque

Sous Excel, une représentation graphique est issue d'un tableau réalisé au préalable.

Qu’est-ce qu’une macro ?

Une macro ou macro-commande permet d'enregistrer une suite d'instructions écrites les unes après les autres. L'enregistrement d'une macro donne naissance à un code qui se fait en langage VBA (Visual Basic for Applications). Le langage VBA est un dérivé du langage Visual Basic, spécialement développé pour les applications du pack Office (Word, Excel, PowerPoint).

Lorsque l’on exécute la macro, le tableur va interpréter les différentes instructions sauvegardées dans le code de la macro à exécuter. Cette exécution permettra de reproduire l'intégralité des commandes enregistrées. Chaque étape sera répétée à l'identique à chaque exécution de la macro.

La macro a pour finalité d'automatiser des tâches afin de les rendre beaucoup plus rapides que si elles avaient été effectuées manuellement.

Dans le cadre d'un projet d'entreprise, elles peuvent aussi avoir un intérêt lors de tests répétitifs de contrôle de qualité des données et/ou de tests de non-régression.

Son rôle est aussi de simplifier la vie de l'utilisateur. La macro est souvent utilisée lorsqu'une tâche est répétitive, ce qui permet à l'utilisateur de travailler beaucoup plus rapidement et efficacement sans erreurs manuelles.

Comment créer un graphique à partir d’un tableau créé manuellement ?

Créer une base de données

Pour créer un graphique, il faut dans un premier temps créer une base de données dans un tableur pouvant faire appel aux graphiques.

Cette base de données devra alors contenir des titres en colonnes et/ou en lignes, puis devra être alimentée selon les besoins de l’utilisateur qui crée le tableau.

Les titres présents en colonnes ou en lignes sont utilisés afin d’avoir une légende claire et précise.

Début de l'exemple

Exemple

Prenons l'exemple d'une entreprise X, admettons que celle-ci souhaite étudier l'évolution de son chiffre d'affaires sur 5 ans.

Elle va alors faire un tableau de ce type sur Excel :

Annexe 1 : Base de données

Fin de l'exemple

Création manuelle du graphique

Après avoir créé une base de données, il devient alors facile de lui associer un graphique. Pour ce faire, il suffit de :

• Sélectionner la base de données
• Cliquer sur la rubrique "Insertion"
• Cliquer sur "Graphiques"
• Choisir le type de graphique le plus adapté

Conseil

Lors de la sélection des données, il est conseillé de sélectionner la totalité des colonnes (jusqu'en bas de la feuille). Cela évite de devoir redéfinir la source du graphique à chaque nouvelle saisie dans la base de données.

Configurer le graphique

Pour configurer le graphique, il suffit d'aller dans les différentes rubriques :

• Création : elle a pour but de modifier le type de graphique ; de changer la source des données ; de configurer la disposition du graphique ; de modifier les couleurs du graphique etc.
• Disposition : elle a pour but d'insérer des objets ; d'ajouter des étiquettes de données ; d'ajouter des titres, etc.
• Mise en forme : elle a pour but de modifier la mise en forme

Comment créer automatiquement un graphique à l’aide d’une macro ?

Test avec enregistrement

Démarche

Pour automatiser la création d'un graphique, on peut utiliser la méthode de l’enregistreur.

La démarche est la suivante :

1ère étape : Création de la macro

• Activation de l’enregistreur
• Dénomination de la macro : « CREERGRAPHIQUE » ; « OK »
• Sélection des données : sélectionner les colonnes afin que cela prenne en compte toutes les lignes en cas d’ajout de données
• Insertion du graphique : « Insertion » ; « graphique »
• Choix du type de graphique
• Choix de la mise en forme
• Ajout des étiquettes de données
• Arrêt de l’enregistreur

2ème étape : Exécution de la macro

Lors de l'exécution de la macro, il peut se produire deux choses :

• Soit la macro s'exécute parfaitement
• Soit une boîte de dialogue s’ouvre pour indiquer une erreur (Annexe 2)

Dans ce cas présent, la macro s'effectue correctement.

3ème étape : Analyse de l’écriture dans Visual Basic

Il est intéressant d'analyser l'écriture Visal Basic, surtout en cas d'erreur. La méthode de l'enregistreur pour créer un graphique ne génère pas d'erreur contrairement à d'autres créations. Il n'y a donc pas besoin de retoucher l'écriture.

Remarque

Lorsqu’il y a une erreur dans la macro, une boîte de dialogue apparaît pour nous l'indiquer (Annexe 2). Il est alors facile de localiser la partie du code incorrecte car celle-ci est surlignée en jaune.

En reprenant l'exemple de l'entreprise X, suite à l'enregistrement, nous obtenons l'écriture suivant sous Visual Basic :

Sub CREERGRAPHIQUE()
'
' CREERGRAPHIQUE Macro
'
    Columns("A:B").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Feuil1'!$A:$B")
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.ApplyLayout (3)
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ApplyDataLabels

End Sub

4ème étape : Masquer les étapes de l'exécution de la macro

Afin de ne pas voir les différentes étapes et d'améliorer les performances de la macro lors de son exécution, il est possible de les masquer (désactiver la carte vidéo de l'ordinateur) :

• Ajouter au début de la macro :

 Application.ScreenUpdating=False

• Ajouter à la fin de la macro :

 Application.ScreenUpdating=True

5ème étape : L'écriture finale pour créer un graphique automatiquement

En reprenant l’exemple de l’entreprise X, l'écriture finale qui apparaît sous Visual Basic est :

Sub CREERGRAPHIQUE()
'
' CREERGRAPHIQUE Macro

'
Application.ScreenUpdating=False
    Columns("A:B").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Feuil1'!$A:$B")
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.ApplyLayout (3)
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ApplyDataLabels
Application.ScreenUpdating=True
End Sub

6ème étape : Création d'un bouton

• Créer un bouton : Insertion d'une forme
• Ajouter un texte : clic droit ; "modifier le texte" ; exemple : "CREER UN GRAPHIQUE"
• Affecter la macro : clic droit ; "affecter une macro" ; choisir la macro "CREERGRAPHIQUE" ; "OK"

Conseil

Pour pouvoir comprendre les étapes de la macro, il est possible de suivre l'écriture Visual Basic en utilisant la touche F8.

Avantages

Les avantages de la création d'un graphique automatique avec l'enregistreur :

• Macro générée automatiquement par l’enregistreur
• Méthode simple lorsque l’on ne connaît pas le langage VBA pour créer un graphique
• Permet de se familiariser avec le langage VBA

Inconvénients

Les inconvénients de la création d'un graphique automatique avec l'enregistreur :

• Méthode peu souple
• Restreint les utilisateurs
• Source d'erreur
• La macro peut seulement faire les actions que l’on a enregistré
• De ce fait, elle ne laisse pas le choix du type de graphiques

De manière générale :

• Lorsqu’il y a une erreur, la macro ne fonctionne pas du tout
• Il faut parfois revenir sur le code
• Il n’est pas évident de comprendre les erreurs de programmation

Test avec langage Visual Basic

Démarches

Notre objectif est ici de créer un graphique via le code VBA.
Nous avons deux possibilités :

• Créer un graphique sur une nouvelle feuille (feuille de graphique)
• Créer un graphique sur notre feuille de calcul (feuille des données)

Nous allons expliquer les deux démarches ci-dessous.

Début de l'exemple

Exemple

Nous remplissons un tableau de base avec toutes les données.
Par exemple le tableau ci-contre concernant l'évolution des ventes de noël de 2012 à 2015 :

Annexe 3 : Tableau de base sur les ventes de noël

• 1ère possibilité : Créer un graphique sur une nouvelle feuille (feuille de graphique) :

La démarche de notre programmation est la suivante :

1. Dimensionner les variables utilisées dans le code (phase facultative mais recommandée pour limiter les erreurs)
2. Définir la plage des données sources du graphique
3. Supprimer le graphique si déjà existant afin de rafraîchir proprement notre graphique
4. Créer le graphique en indiquant le type de graphique (ici histogramme), la plage du graphique, le titre, l'intitulé du titre s'il y a et enfin le nom de la feuille qui recevra le graphique
5. Afficher la feuille où se situe le graphique (facultatif mais pratique afin de voir directement le résultat de notre graphique)
Ci-dessous le code utilisé :

Sub CreerGraphiqueNvelleFeuille()

    Const sheDonnéesSource  As String = "DataSource"
    Const sheGraphique      As String = "Graphique"
    
    Dim chGraph             As Chart
    Dim rPlage              As Range

    ' Définition de la plage des données source du graphique 
    Set rPlage = Sheets(sheDonnéesSource).Range("A:E")

    ' Suppression du graphique si déjà existant
    On Error Resume Next
    Sheets(sheGraphique).Delete
    On Error GoTo 0
    
    ' Création du graphique
    Set chGraph = Charts.Add
    With chGraph
        ' Type histogramme
        .ChartType = xlColumnClustered
        ' Source du graphique
        .SetSourceData Source:=rPlage, PlotBy:=xlColumns
        ' Affichage du titre
        .HasTitle = True
        ' Intitulé
        .ChartTitle.Characters.Text = rPlage.Cells(1, 1)
        ' Nom de la feuille recevant le graphique
        .Name = sheGraphique
    End With
    
    Sheets(sheGraphique).Select
    
End Sub

Et voici ce que nous obtenons comme graphique :

Annexe 4 : Graphique ventes noël sur nouvelle feuille

• 2ème possibilité : Créer un graphique sur notre feuille de calcul (feuille des données) :

La démarche de notre programmation est la suivante :
1. Dimensionner les variables utilisées dans le code
2. Dans la feuille de donnée (avec "With"), définir la plage accueillant le graphique, créer le graphique et mettre la source des données du graphique
3. Dans le graphique (avec "With"), définir le type de graphique (ici barre empilée), la source du graphique, la présence ou non de titre avec l'intitulé et enfin la position de la légende
5. Afficher la feuille où se situe le graphique
Ci-dessous le code utilisé :

Sub CreerGraphiqueFeuilleDonnees()

    Const sheDonnéesSource  As String = "DataSource"

    Dim chGraph         As Chart
    Dim rPlageAcceuil   As Range
    Dim rPlageSource    As Range
    
    With Sheets(sheDonnéesSource)
        ' Plage devant accueuillir le graphique
        Set rPlageAcceuil = .Range("A10:F20").Offset(0, 1)
        ' Création du graphique, ne pas oublier le .Chart final
        ' L'objet graphique se place sur la plage et à sa taille
        Set chGraph = .ChartObjects.Add(rPlageAcceuil.Left, rPlageAcceuil.Top, rPlageAcceuil.Width, rPlageAcceuil.Height).Chart            
        ' Source du graphique
        Set rPlageSource = .Range("A:E")
    End With
      
    With chGraph
        ' Type barre empilée
        .ChartType = xlBarStacked
        ' Source du graphique
        .SetSourceData Source:=rPlageSource, PlotBy:=xlColumns
        ' Affichage du titre
        .HasTitle = True
        ' Intitulé
        .ChartTitle.Characters.Text = rPlageSource.Cells(1, 1)
        ' Légende en position haute
        .Legend.Position = xlLegendPositionTop     
     End With

    Sheets(sheDonnéesSource).Select
    
End Sub

Et voici ce que nous obtenons comme graphique :

Annexe 5 : Graphique ventes noël sur feuille de données

Fin de l'exemple

Avantages

Les avantages de la création d'un graphique automatique avec le langage Visual Basic :

• Suppression des erreurs humaines dès lors que le programme est débogué et qu’il n'y a pas de modification de la structure des données de base
• Automatisation des tâches répétitives
• Une fois créé, c’est un gain de temps pour l'utilisateur
• Simplification de la transmission des connaissances à d'autres utilisateurs
• Choix de n’importe quel type de graphique présent dans Excel
• Harmonisation de la structure d'un graphique pour un ensemble d'utilisateurs. Le graphique "officiel" ne pourra pas prendre une autre forme que celui programmé

Inconvénients

Les inconvénients de la création d'un graphique automatique avec le langage Visual Basic :

• Nécessite une connaissance technique d'Excel et VBA pour développer le programme et le déboguer
• Demande un investissement en temps au départ pour mettre en œuvre la solution finalisée
• Demande une transmission des savoirs lors des changements d'organisation du travail

Comment personnaliser son graphique via le langage VBA ?

Les constantes utilisées pour choisir un type de graphique

Lorsque nous créons un graphique il faut indiquer "ChartType= XXX"
"XXX" étant l'une des constante ci-dessous (principaux types de graphiques) :

• xlArea - Aires
• xlAreaStacked - Aires empilées
• xl3DArea - Aires 3D
• xlBarOfPie - Barres de secteurs
• xlBarStacked - Barres empilées
• xl3DBarStacked - Barres 3D empilées
• xlBubble - Bulles
• xlBubble3DEffect - Bulles 3D
• xlLine - Courbes
• xlLineMarkersStacked - Courbes empilées avec marqueurs
• xl3DLine - Courbes 3D
• xlColumnClustered - Histogramme en cluster
• xlColumnStacked - Histogramme empilé
• xl3DColumn - Histogramme 3D
• xl3DColumnClustered - Histogramme 3D en cluster
• xl3DColumnStacked - Histogramme 3D empilé
• xlXYScatter - Nuages de points
• xlXYScatterLines - Nuages de points avec courbes
• xlRadar - Radar
• xlPie - Secteurs
• xl3DPie - Secteurs 3D
• xlSurface - Surface 3D

Par exemple si nous utilisons le type de graphique "secteur" correspondant à la constante xlPie, nous obtenons (en reprenant notre exemple du dessus) :

Annexe 6 : Graphique secteur ventes nöel

Manipuler la taille du graphique

Il vous est possible de changer la taille du graphique, pour cela, il faut utiliser ce langage :

 
     ActiveSheet.ChartObjects(1).Activate
     ActiveChart.ChartArea.Select
     With ActiveSheet.Shapes(1)
        .ScaleWidth 1.48, msoFalse, msoScaleFromBottomRight
        .ScaleHeight 1.49, msoFalse, msoScaleFromBottomRight
        .ScaleWidth 1.32, msoFalse, msoScaleFromTopLeft
        .ScaleHeight 1.26, msoFalse, msoScaleFromTopLeft
     End With

Ce code va vous permettre d'agrandir votre graphique.

ScaleHeigt va être la mesure pour redimensionner la hauteur du graphique et ScaleWidth la largeur.

Manipuler les couleurs d'un graphique

Il est possible de changer les couleurs des courbes d'un graphique, pour cela il faut utiliser le code suivant (mettre la courbe en rouge par exemple) :

 
 With ActiveChart.SeriesCollection(1)
   .Border.Color = RGB(255, 0, 0)
 End with

Voici en langage VBA, les codes couleurs pour les principales couleurs utilisées :

 
   RGB(0, 0, 0) : noir
   RGB(255, 255, 255) : blanc
   RGB(255, 0, 0) : rouge
   RGB(0, 255, 0) : vert
   RGB(0, 0, 255) : bleu

Manipuler les axes d'un graphique

Il est possible via le langage VBA de manipuler les axes d'un graphique :

En reprenant l'exemple ci-dessus de la macro "CreerGraphiqueFeuilleDonnees" :

On peux rajouter au code du dessus ce code :

 ' Axe des catégories
        With .Axes(xlCategory)
        
            ' Inversé
            .ReversePlotOrder = True
            ' Coupe catégorie max
            .Crosses = xlMaximum
            ' Toutes les étiquettes
            .TickLabelSpacing = 1
            ' Titre de l'axe
            ' Affichage du titre
            .HasTitle = True
            .AxisTitle.Text = "Produits"
             ' Police des étiquettes
             With .TickLabels.Font
               .Bold = True
               .Color = RGB(85, 130, 50)
               .Size = 14
             End With
        
        End With

Ce code permet :

• D'inverser l'axe xlCategory
• De classer les catégories par grandeur
• De donner un titre à l'axe "Produits"
• De mettre en gras et de colorier les étiquettes

Ce qui donne :

Sub CreerGraphiqueFeuilleDonnees()

    Const sheDonnéesSource  As String = "DataSource"

    Dim chGraph         As Chart
    Dim rPlageAcceuil   As Range
    Dim rPlageSource    As Range
    
    With Sheets(sheDonnéesSource)
        ' Plage devant accueillir le graphique
        Set rPlageAcceuil = .Range("A10:F20").Offset(0, 1)
        ' Création du graphique, ne pas oublier le .Chart final
        ' L'objet graphique se place sur la plage et à sa taille
        Set chGraph = .ChartObjects.Add(rPlageAcceuil.Left, rPlageAcceuil.Top, rPlageAcceuil.Width, rPlageAcceuil.Height).Chart          
        ' Source du graphique
        Set rPlageSource = .Range("A1:E7")
    End With
      
    With chGraph
        ' Type barre empilée
        .ChartType = xlBarStacked
        ' Source du graphique
        .SetSourceData Source:=rPlageSource, PlotBy:=xlColumns
        ' Affichage du titre
        .HasTitle = True
        ' Intitulé
        .ChartTitle.Characters.Text = rPlageSource.Cells(1, 1)
        ' Légende en position haute
        .Legend.Position = xlLegendPositionTop
        
        ' Axe des catégories
        With .Axes(xlCategory)   
            ' Inversé
            .ReversePlotOrder = True
            ' Coupe catégorie max
            .Crosses = xlMaximum
            ' Toutes les étiquettes
            .TickLabelSpacing = 1
            ' Titre de l'axe
            ' Affichage du titre
            .HasTitle = True
            .AxisTitle.Text = "Produits"
             ' Police des étiquettes
             With .TickLabels.Font
               .Bold = True
               .Color = RGB(85, 130, 50)
               .Size = 14
             End With   
        End With
     End With

    Sheets(sheDonnéesSource).Select
    
End Sub

Voici ce que nous obtenons en manipulant le graphique :

Annexe 7 : Graphique ventes noël manipulation des axes

Ajouter un texte sur le graphique

Pour ajouter un texte sur le graphique, il faut utiliser le langage suivant :

   ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 89.25, 21.75).Select
   Selection.Characters.Text = "Le nom du texte que vous voulez mettre"

Modifier le nom du graphique

Pour modifier le nom du graphique, il faut utiliser le langage suivant :

   ActiveChart.SeriesCollection(1).Name = "Nouveau nom du graphique"

Remplacer le graphique par une image

Il est possible de remplacer le graphique par une image.
Voici le code nécessaire ci-dessous :

Sub RemplaceGraphiqueParImage()

    With ActiveSheet
        .ChartObjects(1).CopyPicture
        .ChartObjects(1).Delete
        .Paste .Range("A1")
    End With
    
End Sub

Annexes

Bibliographie

Monier.C, 2013, Mon eFormation Excel 2013 & VBA-Macros et Programmations, PEARSON

Liens internes

Leçon : Macros-commandes VBA
Chap. Création de Tableau croisé

Liens externes

http://excel.developpez.com/faq/?page=Graphique#GraphCreate
http://doc.frapp.fr/doku.php?id=programmation:vba:graphiques

Création de Tableau croisé

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Définitions

Qu’est-ce qu’un tableau croisé dynamique ?

Un tableau croisé dynamique (appelé aussi sous l’abréviation « TCD ») permet grâce à l’utilisation de certains tableurs, de synthétiser une base de données brutes. Le TCD est un outil très utile pour analyser des bases de données très fournies puisqu’il en facilite leurs analyses et leurs comparaisons.

De plus, c’est un outil totalement modulable et personnalisable. Sa particularité est qu’il est dynamique. On entend par dynamique, le fait que le tableau soit modulable en fonction des besoins d'analyse mais aussi par le fait qu’il prenne en compte toutes les modifications apportées au tableau brut, cela permet ainsi d’actualiser les données du TCD. En effet, on peut choisir les données que l’on veut faire apparaître en colonne et en ligne même après avoir généré un premier TCD. L'utilisateur a le choix de la fonction de regroupement, il peut également décider des champs à utiliser pour réaliser son analyse.

Grâce au TCD il est également possible d'afficher plusieurs fonctions (des moyennes, des sommes, etc.).

Début de l'exemple

Exemple

Afin de comprendre l’utilité du TCD, prenons l’exemple d’une entreprise :

Une entreprise X dans le secteur de la téléphonie veut savoir combien de contrats clients ont été résiliés au cours de l’année N et pour quels motifs.

Au cours de l’année les salariés de l’entreprise X regroupent dans un tableau les différentes résiliations de la manière suivante :

Base de données de l'entreprise X

En fin d’année, le chef de service veut analyser les motifs de résiliations les plus courants. Son but : comprendre la raisons de la résiliation des clients et mettre en œuvre une solution pour y remédier. Les membres de son service réalisent alors le TCD suivant :

TCD de l'entrerprise X

Ainsi, le chef de service peut en conclure que le motif de résiliation qui revient le plus souvent est le prix. Il pourra alors appliquer une solution pour réduire les prix et conserver ses clients.

Fin de l'exemple

Qu’est-ce qu’une macro ?

Une macro ou macro-commande permet d'enregistrer une suite d'instructions écrites les unes après les autres. L'enregistrement d'une macro donne naissance à un code qui se fait en langage VBA (Visual Basic for Applications). Le langage VBA est un dérivé du langage Visual Basic, celui-ci a été spécialement développé pour les applications du pack Office (Word, Excel, PowerPoint).

Lorsque l’on exécute la macro, le tableur va interpréter les différentes instructions sauvegardées dans le code de la macro à exécuter. Cette exécution permettra de reproduire l'intégralité des commandes enregistrées. Chaque étape sera répétée à l'identique à chaque exécution de la macro.

La macro a pour finalité d'automatiser des tâches afin de les rendre beaucoup plus rapides que si elles avaient été effectuées manuellement.

Dans le cadre d'un projet d'entreprise, elles peuvent aussi avoir un intérêt lors de tests répétitifs de contrôle de qualité des données et/ou de tests de non-régression.

Son rôle est aussi de faciliter la vie de l'utilisateur. La macro est souvent utilisée lorsqu'une tâche est répétitive, ce qui permet à l'utilisateur de travailler beaucoup plus rapidement et efficacement sans erreurs manuelles.

Comment actualiser avec une macro un TCD créé manuellement?

Renseignement des données

Pour créer un TCD, il faut dans un premier temps créer une base de données dans un tableur pouvant faire appel au TCD.

Cette base de données devra alors contenir des titres en colonnes et/ou en ligne, puis devra être alimentée selon l'envie de l’utilisateur qui crée le tableau.

Il est primordial que chaque colonne possède une en-tête si l’on veut qu’elles figurent dans le TCD.

Remarque

Un TCD ne peut être créé s'il n'y a pas d'en-tête dans les colonnes.

Début de l'exemple

Exemple

On peut reprendre l’exemple de l’entreprise X (voir « qu’est-ce qu’un tableau croisé dynamique ») qui regroupait en colonne les rubriques :

• « Nom »
• « Date d’inscription »
• « Date de résiliation »
• « Motifs de résiliation ».

Cette entreprise a ainsi alimenté son tableau au fur et à mesure de l’année.

Fin de l'exemple

Création manuelle du TCD

Lorsque le moment de l’analyse arrive, l’individu va créer manuellement son TCD.

La démarche est alors simple :

• Sélectionner les données
• Cliquer sur l'onglet « insertion »
• Cliquer sur « tableau croisé dynamique »
• Une boîte de dialogue s'affiche (Annexe 1)
• Sélectionner "nouvelle feuille de calcul" ou "feuille de calcul existante"
• Cliquer sur "Ok"

Conseil

Lors de la sélection des données, il est conseillé de sélectionner les colonnes afin que la sélection s'effectue jusqu'en bas. Cela évite de devoir redéfinir la source du TCD à chaque nouvelle saisie dans la base de données.

L'étape suivante est de définir les rubriques du TCD, c’est-à-dire, savoir ce que l’on va mettre en colonne, en ligne, et ce que l’on veut mettre en avant (une somme, un nombre, un pourcentage etc.).

Le choix peut être plus ou moins complexe, mais celui-ci est essentiel. Il faut que les résultats qui ressortent du TCD soient clairs et précis, afin que l’on puisse facilement les analyser. Il ne faut surtout pas que le TCD soit plus complexe que le tableau brut. Les résultats qui en ressortent doivent être évidents et "sauter aux yeux".

Enregistrement de la macro : Actualiser le TCD automatiquement

Afin d’actualiser automatiquement le TCD, nous allons faire appel à une macro VBA. Pour que cela soit plus simple à réaliser, nous avons décidé de faire appel à l’enregistreur. Cette manipulation est simple, il suffit de faire l’actualisation du TCD manuellement tout en enregistrant une macro. Ainsi, l’enregistreur écrira tout seul la macro dans Visual Basic.

1ère étape : L’enregistreur

• Cliquer sur l'onglet « Affichage » ou sur l'onglet "Développeur"
• Cliquer sur « enregistrer une macro », une boite de dialogue va alors s'ouvrir (annexe 2) avec les rubriques suivantes :
  • Nom de la macro, ex : « ActualiserTCD »
  • Touche de raccourci
  • Enregistrer la macro dans
  • Description, ex : "Actualiser automatiquement le TCD"
  • OK
• Effectuer l'action d'actualisation : Faire un clic droit sur le TCD et cliquer sur « Actualiser les données »
• Se rendre à nouveau sur l'onglet "Affichage" ou "Développeur"
• Cliquer sur « Arrêter l’enregistrement »

2e étape : Analyse du langage Visual Basic

• Cliquer sur « Macro »
• Cliquer sur la macro crée : « ActualiserTCD » ;
• Cliquer sur « Modifier » ;

Ou passer par Visual Basic dans "outil"

La macro suivante apparaît :

Sub ActualiserTCD()
'
' ActualiserTCD Macro
'
    Sheets("Feuil2").Select
    Range("G7").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").RefreshTable
End Sub

Il est important d’examiner les actions qui ont été enregistrées par Excel.

C’est d’autant plus important lorsque la macro ne s’exécute pas comme on le souhaite. On peut rapidement voir où se trouve l’erreur d’écriture.

De plus, analyser la macro permet également d’apporter des modifications. En effet, lors de l’enregistrement, on a tendance à vouloir aller trop vite, et on clique sur des boutons qu’il ne faut pas. Toutes ces manipulations inutiles peuvent être supprimées.

Remarque

Afin de désactiver la carte vidéo et améliorer les performances, il faut rajouter :

• En début de macro : Application.ScreenUpdating = False
• En fin de macro : Application.ScreenUpdating = True

La macro finale sera donc :

Sub ActualiserTCD()
'
' ActualiserTCD Macro
'
Application.ScreenUpdating = False
    Sheets("Feuil2").Select
    Range("G7").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique1").RefreshTable
Application.ScreenUpdating = True
End Sub

Création d’un bouton et affectation de la macro « Actualiser le TCD »

Pour finir, nous allons créer un bouton qui permettra à l’utilisateur d’actualiser le TCD uniquement en cliquant dessus.

La démarche est alors la suivante :

• Insérer une forme ou une image en cliquant sur l'onglet insertion, ex : un rectangle
• Modifier la mise en forme et ajouter un texte s'il s'agit d'une forme, ex de texte : « Cliquer ici pour actualiser le TCD »
• Faire un clic droit sur la forme et cliquer sur « Affecter une macro »
• Une boîte de dialogue va s'ouvrir (Annexe 3), sélectionner la macro « ActualiserTCD » et cliquer sur OK

Comment créer automatiquement un TCD à l’aide d’une macro ?

Test avec enregistrement

Démarche

Pour automatiser la création du TCD, on peut passer par la méthode de l’enregistreur.

La démarche est la suivante :

1ere étape : Création de la macro

• Activation de l’enregistreur
• Nommer la macro : « CREERTCD » ; « OK »
• Sélection des données : sélectionner les colonnes afin que cela prenne en compte toutes les lignes en cas d’ajout de données
• Insertion du TCD : « Insertion » ; « Tableau croisé dynamique »
• Choisir d’imposer ou non les champs du TCD à l’utilisateur :
  • Si oui, choisir les rubriques à mettre dans « Etiquettes de colonnes » ; « Etiquettes de lignes » , « Filtre du rapport » ; « Valeurs »
  • Si non, passer à l’étape suivante
• Actualiser le TCD : Clic droit ; « Actualiser » (cette étape permet de prendre en compte les lignes supplémentaires entre la saisie des données du tableau et la création du TCD)
• Choix des champs du TCD
• Arrêter l’enregistreur

2e étape : Exécution de la macro

Il y a une erreur qui survient lorsque l’on utilise l’enregistreur pour automatiser la création d’un TCD. En effet, si l’on exécute la macro, une boîte de dialogue s’ouvre (annexe 4).

Cette dernière a pour rôle d’indiquer lorsqu’il y a une erreur d’écriture dans la macro.

3e étape : Analyse de l’écriture dans Visual Basic

Lorsqu’il y a une erreur, il est nécessaire d’examiner ce que Excel a enregistré. En reprenant l’exemple de l’entreprise X, le code suivant apparaît sous Visual Basic :

Sub CREERTCD()
'
' CREERTCD Macro
'
' Sélection des données
    Sheets("BASE DE DONNEES").Select
    Columns("A:D").Select

' Création du TCD
    Sheets.Add
      ActiveWorkbook.Worksheets("ACTUALISER TCD").PivotTables( _
        "Tableau croisé dynamique7").PivotCache.CreatePivotTable TableDestination:= _
        "Feuil1!L3C1", TableName:="Tableau croisé dynamique1", DefaultVersion:= _
        xlPivotTableVersion12
    Sheets("Feuil1").Select
    Cells(3, 1).Select

' Actualiser
    Range("C9").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique4").PivotCache.Refresh

End Sub

Le problème vient de cette partie de l’écriture :

    ActiveWorkbook.Worksheets("ACTUALISER TCD").PivotTables( _
        "Tableau croisé dynamique7").PivotCache.CreatePivotTable TableDestination:= _
        "Feuil1!L3C1", TableName:="Tableau croisé dynamique1", DefaultVersion:= _
        xlPivotTableVersion12

Le problème est que nous spécifions la feuille de destination et également la plage du tableau croisé dynamique dans l’écriture. La conséquence de ce type d’écriture est que la macro n’est pas dynamique. Ce qui explique que la macro ne puisse s’exécuter.

4e étape : Résolution du problème Pour résoudre le problème, il faut supprimer quelques lignes :

• Il faut effacer la valeur qui se trouvait dans : TableDestination

Cela revient à : TableDestination = ""

• Supprimer les lignes :

Sheets.add ; Sheets (« sheetname »).Select ; Cells (x,y).Select

5e étape : L’écriture finale pour automatiser la création d’un TCD

• Sans contrainte des champs :

Sub CREERTCD()
'
' CREERTCD Macro
'
' Sélection des données
    Sheets("BASE DE DONNEES").Select
    Columns("A:D").Select

' Création du TCD
    ActiveWorkbook.Worksheets("ACTUALISER TCD").PivotTables( _
        "Tableau croisé dynamique7").PivotCache.CreatePivotTable TableDestination:= _
        "", TableName:="Tableau croisé dynamique4", DefaultVersion:= _
        xlPivotTableVersion12

' Actualiser
    Range("C9").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique4").PivotCache.Refresh

End Sub

Remarque

Il est possible de modifier les intitulés des colonnes uniquement lorsque les champs ne sont pas définis dans la macro.

• Avec contrainte des champs :

Sub CREERTCDCHAMPS()
'
' CREERTCDCHAMPS Macro
'
' Sélection des données
    Sheets("BASE DE DONNEES").Select
    Columns("A:D").Select

' Création du TCD
    ActiveWorkbook.Worksheets("ACTUALISER TCD").PivotTables( _
        "Tableau croisé dynamique7").PivotCache.CreatePivotTable TableDestination:= _
        "", TableName:="Tableau croisé dynamique3", DefaultVersion:= _
        xlPivotTableVersion12

' Choix des champs
    ActiveSheet.PivotTables("Tableau croisé dynamique3").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique3").PivotFields("Nom client"), _
        "Nombre de Nom client", xlCount
    With ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotFields( _
        "Motif de résiliation")
        .Orientation = xlRowField
        .Position = 1
    End With

' Filtrer
    With ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotFields( _
        "Motif de résiliation")
        .PivotItems("(blank)").Visible = False
    End With

' Actualiser
    Range("B5").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotCache.Refresh

End Sub

6e étape : Création d'un bouton

• Créer un bouton : Insertion d'une forme
• Ajouter un texte : clic droit ; "modifier le texte" ; exemple : "CREER UN TCD"
• Affecter la macro : clic droit ; "affecter une macro" ; choisir la macro ; "OK"

Avantages

• La macro est générée automatiquement par l’enregistreur
• Méthode simple lorsque l’on ne connaît pas le langage pour créer un TCD
• Permet de se familiariser avec le langage VBA
• Les champs en lignes et en colonnes ne sont pas imposés

Inconvénients

• La macro peut seulement faire les actions que l’on a enregistré
• Lorsqu’il y a une erreur, la macro ne fonctionne pas du tout
• Il faut parfois revenir sur l’écriture
• Il n’est pas évident de comprendre le problème de l’écriture lorsqu’il y a une erreur

Test avec langage Visual Basic

Démarches

Notre objectif est ici de créer un tableau croisé dynamique via le code VBA.

Cette macro permettra de créer un TCD avec des champs définis.
Elle impose donc à l'utilisateur de programmer dans la macro les champs voulus dans le TCD.

Début de l'exemple

Exemple

Pour cela, nous remplissons un tableau de base avec toutes les données, nommé "Adhérents" dans la feuille "Données".

Ensuite, nous créons une nouvelle feuille, nommée "TCD automatique" afin d'y faire apparaître le TCD.

Explications des champs de l’en-tête de colonne "réglé" :

• "O" pour réglé
• "N" pour non-réglé

Tableau de base "Adhérents"

La démarche de notre programmation est la suivante :

1. Dimensionner les variables utilisées dans le code (phase facultative mais recommandée pour limiter les erreurs).
2. Affecter le TCD à la feuille "TCD automatique" afin de programmer que toutes les actualisations se feront sur cette feuille.
3. Supprimer tous les TCD existants de la feuille par une boucle For Each afin de rafraîchir proprement notre TCD.
4. Ajouter le TCD sur la feuille "TCD automatique" en respectant le code ci-dessous.

Attention : ce code impose à l'utilisateur de nommer son tableau de base. Ici, nous l'avons nommé "Adhérents". Nous indiquons l'emplacement du TCD; ici en cellule B5 et nous décidons de nommer notre TCD "TCD_Adhérents".

Voici ci-dessous ce que nous obtenons :

TCD Adhérents vide

5. Ajouter les champs, une fois le TCD crée. L'utilisateur programme les champs qu’il veut insérer dans son TCD : en ligne, en colonne et en valeur.
Ici il s'agit du champ "Catégorie" en ligne, "Réglé" en colonne et "Cotisation" en valeur.

Ces noms de champs doivent correspondre aux entêtes de colonne du tableau de base.

Sub create_TCD()

'Dimension des variables 

Dim wshTCD      As Worksheet
Dim PvtTCD      As PivotTable
    
   'Affectation du TCD à la feuille "TCD automatique"
    Set wshTCD = Worksheets("TCD automatique")
    
   'Suppression de tous les TCD existants dans la feuille
    For Each PvtTCD In wshTCD.PivotTables
        PvtTCD.TableRange2.Clear
    Next PvtTCD
    
   'Ajout d'un TCD sur la feuille "TCD automatique"
    Set PvtTCD = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Adhérents") _
                .CreatePivotTable(tabledestination:=wshTCD.Range("B5"), TableName:="TCD_Adhérents")
    
   'Ajout des champs au TCD
       With PvtTCD        

        'Ajout d'une Ligne
        With .PivotFields("Catégorie")
            .Orientation = xlRowField
            .Position = 1
        End With
        
        'Ajout d'une Colonne
        With .PivotFields("Réglé")
            .Orientation = xlColumnField
            .Position = 1
        End With
        
        'Ajout d'une Valeur Cotisation
        With .PivotFields("Cotisation")
            .Orientation = xlDataField
        End With
    End With

End Sub

Voici le résultat attendu :

TCD Adhérents

Fin de l'exemple

Remarque

• L’utilisateur doit penser à nommer son tableau de base afin qu’il soit repris pour créer le TCD.
• Cette macro permet de créer un TCD avec des champs définis. Elle impose donc à l'utilisateur de programmer dans la macro les champs voulus dans le TCD.

Avantages

Les avantages de la création d'un TCD automatique avec le langage Visual Basic :

• Automatisation des tâches répétitives.
• Suppression des erreurs humaines dès lors que le programme est débogué et qu’il n'y a pas de modification de la structure des données de base.
• Une fois créé, c’est un gain de temps pour l'utilisateur.
• Simplification de la transmission des connaissances à d'autres utilisateurs.
• Harmonisation de la structure d'un TCD pour un ensemble d'utilisateurs. Le TCD "officiel" ne pourra pas prendre une autre forme que celui programmé.

Inconvénients

Les inconvénients de la création d'un TCD automatique avec le langage Visual Basic :

• Nécessite une connaissance technique d'Excel et VBA pour développer le programme et le déboguer.
• Demande un investissement en temps au départ pour mettre en œuvre la solution finalisée.
• Demande une transmission des savoirs lors des changements d'organisation du travail.

Comment peut-on paramétrer un TCD ?

Appliquer une fonction

Cette macro applique une fonction de synthèse dans un champ défini.

Elle permet de choisir un type de calcul (somme, nombre, moyenne,...) que l’on souhaite utiliser pour résumer les données du champ sélectionné de manière automatique (par macro).

Dans l'exemple ci-dessous elle modifie la fonction de synthèse du champ "Cotisations", afin d'afficher la moyenne et de la renommer "Cotisation moyenne".

Sub ModifierFonctionDuTCD()

    'Définition des variables
    Dim pvtTCD As PivotTable
    
    'Affectation du TCD sur lequel on souhaite travailler
    Set pvtTCD = ActiveSheet.PivotTables("TCD_Adhérents")

    'Remplacement de la fonction "Somme" affectée au champ "Cotisation" par la fonction "Moyenne"
    With pvtTCD.PivotFields("Somme de Cotisation")
    
        .Function = xlAverage
        .NumberFormat = "# ##0 €"
        .Caption = "Cotisation Moyenne"
        
    End With
    

End Sub

Voici les autres constantes disponibles pour la propriété Function, que l’on peut appliquer de la même manière que xlAverage ci-dessus :

• xlAverage - Moyenne
• xlCountNums - Nb
• xlMin - Min
• xlStDev - Ecartype
• xlSum - Somme
• xlVar - Var
• xlCount - Nombre
• xlMax - Max
• xlProduct - Produit

...

Remarque

Cette macro ne pourra être utilisée qu'une fois. Après, elle demandera une modification du code VBA.
En effet, la "Somme de Cotisation" va être modifiée par "Cotisation Moyenne".
Donc si l’on répète la macro il ne trouvera pas la "Somme de Cotisation" puisque cette fonction aura été modifiée.
Il faudra la remplacée par "Cotisation Moyenne" et indiquer la fonction que l’on souhaite appliquée.
À noter que la syntaxe à une très grande importance. Une "faute de frappe" empêchera la macro de fonctionner.

Paramétrer les sous-totaux

Nous avons la possibilité de paramétrer les sous-totaux de notre TCD. Cela permettra à l'utilisateur de figer ce mode de calcul et de l'automatiser.

• Nous pouvons tout d’abord enlever les sous-totaux de l’ensemble des champs du TCD :

'
' Cette procédure parcourt l’ensemble des champs d'un TCD afin de leur enlever les sous-totaux s'ils existent
'
 Sub EnleverSousTotauxDuTCD()
 
    Dim pvtTCD As PivotTable
    Dim pvfTCD As PivotField
    
    ThisWorkbook.Sheets("TCD automatique").Select    
    Set pvtTCD = ActiveSheet.PivotTables("TCD_Adhérents")
    For Each pvfTCD In pvtTCD.PivotFields  
        If pvfTCD.Subtotals(1) Then pvfTCD.Subtotals(1) = False
    Next pvfTCD
    
 End Sub

• Nous pouvons aussi décider d'appliquer des sous-totaux qu’à certains champs définis :

'
'Cette procédure paramètre les sous-totaux : n'applique des sous-totaux qu'aux champs définis
'
 Sub InitialiserSousTotauxDuTCD()
 
    Dim pvtTCD As PivotTable
    Dim pvfTCD As PivotField
    
    ThisWorkbook.Sheets("TCD automatique").Select
    Set pvtTCD = ActiveSheet.PivotTables("TCD_Adhérents")
    For Each pvfTCD In pvtTCD.PivotFields
        'Seuls les champs "Age" et "Catégorie" auront l’attribut subtotals activé
        Select Case pvfTCD.Name
            Case "Age", "Catégorie"
                pvfTCD.Subtotals(1) = True
            Case Else
                pvfTCD.Subtotals(1) = False
        End Select
    Next pvfTCD
    
 End Sub

Comment extraire une valeur d'un TCD ?

Cette méthode permet à l'utilisateur de repérer rapidement dans le TCD une valeur spécifique.
C'est très utile lorsque l'utilisateur répète toujours une même tâche et que le TCD est assez conséquent.

Début de l'exemple

Exemple

Par exemple ici, dans l'exemple 2 (voir ci-dessous), l'employé doit calculer chaque mois le montant des cotisations de catégorie "E" non réglées.
Il n'aura donc qu’à lancer cette macro chaque mois afin de voir apparaître par un message (MsgBox) le montant recherché.

Sub ExtraireValeurDuTCD()

    Dim pvtTCD As PivotTable
    Dim rngPlage As Range
    
    ' Défini le TCD sur lequel on souhaite récupérer la valeur
    Set pvtTCD = ActiveSheet.PivotTables("TCD_Adhérents")   
    ' Exemple 1 : Cette procédure affiche la valeur de la synthèse "Cotisations" pour l'élément "A" du champ "Catégorie" grâce à la méthode GetPivotData.
    ' Récupération de la valeur du champs "Cotisation" pour la ligne "Catégorie A" =>> Récupère le total de la ligne.
        Set rngPlage = pvtTCD.GetPivotData("Cotisation", "Catégorie", "A")
    MsgBox rngPlage.Value
   
    ' Exemple 2: Récupération de la valeur du champs "Cotisation" pour la ligne "Catégorie E" de la colonne "Réglé N" =>> Récupère la valeur de la jonction ligne et colonne.
    Set rngPlage = pvtTCD.GetPivotData("Cotisation", "Catégorie", "E", "Réglé", "N")
    MsgBox rngPlage.Value

End Sub

Voici ce que l’on obtient pour l'exemple 2 :

TCD - Extrait Valeur spécifique1

Fin de l'exemple

Remarque

Il faut que tous les champs saisis dans le code VBA correspondent à des champs existants du TCD. Sinon le programme n'aboutira pas.

Annexes

Bibliographie

Monier.C, 2013, Mon eFormation Excel-Tableaux Croisés Dynamiques, PEARSON

Monier.C, 2013, Mon eFormation Excel 2013 & VBA-Macros et Programmations, PEARSON

Liens externes

http://excel.developpez.com/faq/?page=TCD#CreationTCDMacro

http://support.microsoft.com/kb/940166/fr

Lectures et écritures

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Manipulation de répertoires

Pour connaitre le répertoire courant, la fonction dépend du logiciel utilisé :

Sub Repertoires1()
 ' Dans Excel :
 MsgBox ThisWorkbook.Path
 ' Dans Word :
 MsgBox ThisDocument.Path
End Sub

Pour créer un répertoire, il faut préalablement vérifier son inexistence :

Sub Repertoires2()
  ChDir ThisWorkbook.Path & "\Test"  ' Se rend dans le répertoire "test"
  If Dir("monDossier", vbDirectory) = "" Then MkDir ("monDossier")  ' Si le répertoire "monDossier" n'existe pas on le crée
End Sub

Pour détruire un répertoire, il faut préalablement vérifier son existence :

Sub Repertoires3()
  ChDir ThisWorkbook.Path & "\Test"  ' Se rend dans le répertoire "test"
  If Dir("monDossier", vbDirectory) <> "" Then RmDir("monDossier") ' Supprime le répertoire "monDossier" si le répertoire "monDossier" existe
End Sub

Pour connaitre le statut d'un objet (répertoire, fichier caché...), utiliser GetAttr() :

Sub Repertoires4()
    Statut = GetAttr(CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\monDossier")
    ' Si Statut = 16, l’objet "monDossier" qui est sur le bureau est un vrai répertoire
    If Statut = 16 Then MsgBox ("Le fichier monDossier est un répertoire", "Analyse de fichier dans répertoire", vbInformation)
End Sub

Pour parcourir un répertoire il faut l'ouvrir et analyser son contenu :

Sub Repertoires5()
    Dim repertoire As String
    Dim classeur As String
    Dim nbrFichiers As Integer
    ' initialise le répertoire de travail
    repertoire = "c:\tests\excel\"
    ' récupère le premier fichier du répertoire
    classeur = Dir(repertoire)  
    ' entame la boucle principale (tant qu’il y a des fichiers dans le répertoire)
    Do
        ' affiche les noms des fichiers trouvés
        MsgBox ("Fichier trouvé : " & classeur, "Parcours de répertoire", vbInformation)
        nbrFichiers = nbrFichiers + 1
        ' recherche du fichier suivant
        classeur = Dir
    Loop While classeur <> ""
    ' affiche le nombre de fichiers trouvés
    MsgBox ("Nombre de Fichiers trouvés : " & nbrFichiers, "Comptage dans répertoire", vbInformation)
End Sub

Manipulation de fichiers

Sélectionner un fichier ouvert

Sub MonFichier()
  Workbooks("Nom du fichier").Activate
  ' Choisir une cellule
  Workbooks("Nom du fichier").Worksheets(1).Cells(1, 1)
End Sub

Rechercher des fichiers

Pour rechercher des fichiers Excel en VB 6.3 depuis un fichier .xls :

Sub Liste()
  ligne = 2
  file = Dir(ThisWorkbook.Path & "\*.xls") 'Premier fichier dans l’ordre alphabétique, dans Windows
  Do While file <> ""  'Jusqu'à ce que la recherche soit vide
    Cells(ligne, 1) = file  'On écrit le nom du fichier dans une cellule
    file = Dir                             'Fichier suivant
    ligne = ligne + 1
  Loop
End Sub

Pour passer au fichier .xls suivant dans le répertoire, il suffit de rappeler Dir() sans paramètre :

 file = Dir()

Si le chemin dans la commande Dir() contient deux étoiles, elle retrouvera le même fichier lors de son rappel

La version postérieure propose un autre mode de recherche.

Copier des fichiers

Sub Copier()
  FileCopy "C:\Fichier.txt", "C:\Temp\Archive.txt"
End Sub

Avec la date du jour pour éviter d'écraser :

Sub Copier2()
  FileCopy "C:\Fichier.txt", "C:\Temp\Archive" & Date & ".txt"
End Sub

Déplacer des fichiers

Sub Deplacer()
    Dim FSO As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    FSO.MoveFile Source:="C:\Fichier.txt", Destination:="C:\Temp\Archive"
End Sub

PS : cela sert aussi à les renommer.

Supprimer des fichiers

Sub Supprimer()
    Dim FSO As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    FSO.DeleteFile "C:\Fichier.txt"
End Sub

Propriétés des fichiers

Pour modifier les propriétés d'un fichier, utiliser SetAttr :

Paramètre	Valeur alternative	Description
vbNormal	0	Fichier normal
vbReadOnly	1	Lecture seule
vbHidden	2	Fichier caché
vbSystem	4	Fichier système
vbArchive	32	Archive
vbAlias	64	Lien symbolique

Exemple :

SetAttr "C:\Temp\Test.xls", vbHidden

Fichier texte

Si le fichier text.txt n'existe pas le programme le crée, sinon il l'écrase :

Sub Texte()
  Open "C:\Users\login\Desktop\text.txt" For Input As #1
  MsgBox "Le fichier pèse : " & LOF(1) & " octets"
  NbLigne = 0
  While Not EOF(1)
    Line Input #1, Ligne
    MsgBox (Ligne)
    NbLigne = NbLigne + 1
  Wend
  MsgBox "Il contient : " & NbLigne & " lignes."
  Print #1, NbLigne
  Close #1
End Sub

Line Input est limité à 250 caractères par ligne si on ne déclare pas la variable. Pour éviter qu'elle soit limitée en type String il faut donc spécifier un type : Dim Ligne as Variant De plus, si le fichier texte lu est au format UNIX, la fonction Line Input lira tout le fichier en une seule fois, sans pouvoir distinguer les lignes. Il faut donc le convertir (généralement depuis PC ANSI) au format PC DOS au préalable, par exemple avec le freeware Textpad, ou en appliquant sur chaque ligne Replace(Ligne, Chr(10), vbCrLf).

Pour ajouter une ligne à la fin sans tout relire :

Sub Texte2()
  Open "C:\Users\login\Desktop\text.txt" For Binary As #1
  Put #1, LOF(1), "Dernière ligne"
  Close #1
End Sub

Tableau Excel

Avant de procéder à l'une des deux opération de lecture ou d'écriture, il faut ouvrir le fichier en spécifiant le mode d'ouverture avec la fonction fileopen.

Autre solution, en appelant une macro depuis un fichier Excel, il n’est pas nécessaire rouvrir le fichier :

Sub Tableurs()
    ' Création d'un classeur vierge
    Set FichierResultat = Workbooks.Add()

    ' Création d'une feuille à la fin du classeur
    ActiveWorkbook.Sheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
    ActiveSheet.Name = "Test"
    ' Création d'un classeur automatiquement en copiant une feuille
    ActiveSheet.Copy

    'Modification directe
    Sheets(1).Range("A1").Value = "Ce fichier est situé dans "
    Sheets(1).Range("B1").Value = ActiveWorkbook.Path  '(équivalent à : Sheets("feuille 1").Cells(1, 2).Value = ThisWorkbook.path)
    MsgBox(Sheets(1).Range("B1").Value)       'Affichage d'un champ dans une boite à valider

    ' Parcours de toutes les feuilles du fichier
    Dim F as WorkSheet
    For Each F in WorkSheets
      F.Range("A2").Value = "Feuille lue"
    Next F

    'Suppression de la ligne C
    Rows(3).Delete                  ' Décalage vers le haut
    Rows(3).Delete shift:=xlToLeft  ' Décalage vers la gauche

    'Copie de B dans C
     Rows(2).Select
     Selection.Copy
     Rows(3).Select
     ActiveSheet.Paste    ' Ces quatre lignes ne sont pas équivalentes à : Rows(3).Value = Rows(2).Value, car elles respectent les propriétés des cellules (taille, gras, soulignement...)
    
    'Filtre l’affichage des lignes nulles de la colonne 4
    ActiveSheet.Range("$A$4:$Z$1000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlAnd

    'Recherche du mot "numéro"
    Cells.Find(what:="numéro").Activate
    ' Pour une recherche plus ciblée, utiliser : Application.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

    'Récupération des coordonnées du mot
    Dim Recherche as Variant
    Set Recherche = Cells.Find("numéro")
    If Not Recherche Is Nothing Then
       MsgBox "En " & Recherche.Row & ", " & Recherche.Column & " : " & Cells(Recherche.Row, Recherche.Column).Value
    End if

    ' Sélection d'une plage de cellules
    ActiveSheet.Range("A1", "B2").Select ' ou
    ActiveSheet.Range(Cells(1, 1), Cells(2, 2)).Select

    'Sauvegarde
    ActiveWorkbook.Save

    'Sauvegarde ailleurs
    ActiveWorkbook.SaveAs(ActiveWorkbook.Path & "\" & "NouveauNomDuFichier")

End Sub

Remarque

Pour recherche plusieurs occurrences d'une chaine sur la même feuille, il faut enregistrer l'adresse de la première sans quoi le programme tourne en boucle.

Attention : la fonction Find() cherche un code contenu dans une cellule, même si elle est plus longue. Pour que la cellule contienne exactement le code recherché, ni plus ni moins, utiliser FindNext() en plus :

Set Recherche = Cells.Find("numéro")
While Not Recherche Is Nothing And Len(Recherche) <> Len("numéro")
  Set Recherche = Cells.FindNext(Recherche)
Wend

Sinon pour manipuler un autre fichier dans un deuxième processus Excel en même temps :

    Dim appExcel As Excel.Application
    Set appExcel = CreateObject("Excel.Application")
    Dim wbExcel As Excel.Workbook
    Dim wsExcel As Excel.Worksheet

    'Accès à un fichier dans le même répertoire que celui qui appelle le script (l'ActiveWorkbook)
    Set wbExcel = appExcel.Workbooks.Open(ActiveWorkbook.Path & "\" & "NomDuFichier") 
    Set wsExcel = wbExcel.Worksheets(1)

    'Traitement
    wsExcel.Sheets(1).Range("A1").Value = "Traité"

    'Sauvegarde et quitte
    wbExcel.Save
    wbExcel.Close
    appExcel.Quit

La communication inter-processus est 10 fois plus longue que si les deux fichiers sont ouverts avec Application.Workbooks.Open().

Accès aux bases de données

Références VBA Excel

Sous Excel, il faut au préalable cocher dans le menu Outils\Références, la bibliothèque ActiveX Data Objects, pour pouvoir stocker les extractions des bases de données dans des objets Recordset (littéralement "jeu d'enregistrement"), comme dans un tableau 2D.

On peut ensuite se connecter en utilisant différents pilotes.

Remarque

Les propriétés BOF et EOF d'un Recordset signifient "beginning of file" (début de fichier) et "end of file" (fin de fichier). Ils s'appliquent aussi aux jeux d'enregistrement (non sérialisés).

Public Function Extraire(Nom) As Boolean
    On Error Resume Next
    Dim Connection As New ADODB.Connection
    Dim Command As New ADODB.Command
    Dim Jeu As New ADODB.Recordset
    Dim Entetes As ADODB.Fields
    Dim Tableau As Variant

   ' Connexions avec le pilote ODBC
    ' Pour MySQL :
    Connection.ConnectionString = "driver={MySQL ODBC 3.51 Driver};server=MonServeur;uid=MonCompte;pwd=MonMotDePasse;database=MaBase"
    ' Pour MS-SQL : Connection.ConnectionString = "driver={SQL Server};server=MonServeur;uid=MonCompte;pwd=MonMotDePasse;database=MaBase"
    ' Pour MS-Access : Connection.ConnectionString = "driver={Microsoft Access Driver (*.mdb)};Dbq=CheminDatabase;Exclusive=0";"
   ' Connexions avec le pilote OLE DB :
    'Connection.ConnectionString = "Provider=SQLOLEDB.1;Data Source=MonServeur;Initial Catalog=MaBase;User ID=MonLogin;password=MonMotDePasse;"
    'Connection.ConnectionString = "Provider=SQLOLEDB;Data Source=MonServeur;Initial Catalog=MaBase;Integrated Security=SSPI;"
   ' Connexions avec le pilote SQL Server Native Client
    'Connection.ConnectionString = "Provider=SQLNCLI;Server=MonServeur;DATABASE=MaBase;Trusted_Connection=yes;"

    Connection.Open
    Command.ActiveConnection = Connection
    Command.CommandText = "SELECT MonChamp from MaTable where Nom = '" & Nom & "'"
    Set Jeu = Command.Execute
    If Jeu.BOF = False And Jeu.EOF = False Then
      Tableau = Jeu.GetRows
      Set Entetes = Jeu.Fields
    End If

    MsgBox ("Premier résultat, " & Entetes.Item(0).Name & " : " & Tableau(0, 0))

    For L = LBound(Tableau, 2) To UBound(Tableau, 2)
      For C = LBound(Tableau, 1) To UBound(Tableau, 1)
        ThisWorkbook.ActiveSheet.Cells(L + 1, C + 1).Value = Tableau(C, L)
      Next C
    Next L
    
End Function

Lancer une procédure stockée

On lance ici une procédure stockée avec une chaine de caractères et un entier en paramètres :

Sub SP
    Dim Connection As New ADODB.Connection
    Dim Command As New ADODB.Command
    Dim Jeu As New ADODB.Recordset
    Dim Param1, Param2 As ADODB.Parameter
    Dim Tableau As Variant

  Connection.ConnectionString = "driver={MySQL ODBC 3.51 Driver};server=MonServeur;uid=MonCompte;pwd=MonMotDePasse;database=MaBase"
  Connection.Open
  Command.ActiveConnection = Connection
  Command.CommandText = "MaProcédureStockée"
  Set Param1 = Command.CreateParameter("@Nom", adVarChar, adParamInput, 200)
  Set Param2 = Command.CreateParameter("@Age", adInteger, adParamInput, 10)
  Command.Parameters.Append Param1
  Command.Parameters.Append Param2
  Param1.Value = "MICHU"
  Param2.Value = 49
  Set Jeu = Command.Execute
  If Jeu.BOF = False And Jeu.EOF = False Then
    Tableau = Jeu.GetRows
  End If
  MsgBox Tableau(0,0)
End Sub

Importer un fichier texte dans une BDD

Le fichier doit être au format PC DOS. La commande dépend ensuite du SGBD, par exemple avec MS-SQL c'est BULK INSERT.

Remarque

Pour afficher une simple sélection de table sans paramètre, la liaison de données d'Excel est une solution plus rapide.

Références

• http://www.ozgrid.com/VBA/ExcelRanges.htm
• Translinguisme/Programmation#Manipuler_un_fichier_Excel sur Wikilivres

Gestion des droits et répertoires

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Cet article explique comment, à l'aide des interfaces de programmation de Windows (API Win32), il est possible de lire chaque entrée de la liste de contrôle d'accès (une ACE - Access Control Entry - de l'ACL - Access Control List) d'un répertoire local ou distant avec VBA.

Autrement dit, pour un répertoire donné, on affiche chaque utilisateur (ou groupe, ou alias) et les droits d'accès associés à cet utilisateur.

Description

Dans cet exemple, plusieurs interfaces de programmation de Windows sont utilisées :

• GetFileSecurity (...)  : Récupère le descripteur de sécurité d'un fichier (ou répertoire).
• GetSecurityDescriptorDacl (...)  : Récupère le descripteur de la liste de contrôle d'accès.
• GetAclInformation (...)  : Récupère la liste de contrôle d'accès.
• GetAce (...)  : Récupère une entrée de la liste (un SID, et un drapeau représentant les droits.
• LookupAccountSid (..)  : Récupère un nom de domaine, de compte et type de compte (utilisateur, alias, groupe, ...) à partir d'un SID (identificateur système).

Dans un premier temps on indique un répertoire (soit local : c:\tmp soit distant : \\serveur1\partage1). Ensuite on récupère pour ce répertoire, et à l'aide des API précédentes la liste de contrôle d'accès associée. Pour chaque entrée de la liste, on analyse le drapeau des droits, puis on récupère le nom du compte que l’on affiche avec une boîte de dialogue simple (message box).

Déclarations préalables

ModuleVBA

Public Const DACL_SECURITY_INFORMATION = &H4

Type Droit
   Nom As String
   Flag As Long
End Type

Public tabRights(21) As Droit

' Structures used by our API calls.
' Refer to the MSDN for more information on how/what these
' structures are used for.
Type ACE_HEADER
   AceType As Byte '1
   AceFlags As Byte '1
   AceSize As Integer '2
End Type

Public Type ACCESS_DENIED_ACE
  Header As ACE_HEADER
  Mask As Long '4
  SidStart As Long
End Type

Type ACCESS_ALLOWED_ACE
   Header As ACE_HEADER
   Mask As Long
   SidStart As Long
End Type

Type ACL_SIZE_INFORMATION
   AceCount As Long
   AclBytesInUse As Long
   AclBytesFree As Long
End Type

'' API calls used within this sample. Refer to the MSDN for more
'' information on how/what these APIs do.
'
'Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
'Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
'Declare Function LookupAccountName Lib "advapi32.dll" Alias "LookupAccountNameA" (lpSystemName As String, ByVal lpAccountName As String, Sid As Any, cbSid As Long, ByVal ReferencedDomainName As String, cbReferencedDomainName As Long, peUse As Long) As Long
'Declare Function InitializeSecurityDescriptor Lib "advapi32.dll" (pSecurityDescriptor As SECURITY_DESCRIPTOR, ByVal dwRevision As Long) As Long
Declare Function GetSecurityDescriptorDacl Lib "advapi32.dll" (pSecurityDescriptor As Byte, lpbDaclPresent As Long, pDacl As Long, lpbDaclDefaulted As Long) As Long
Declare Function GetFileSecurityN Lib "advapi32.dll" Alias "GetFileSecurityA" (ByVal lpFileName As String, ByVal RequestedInformation As Long, ByVal pSecurityDescriptor As Long, ByVal nLength As Long, lpnLengthNeeded As Long) As Long
Declare Function GetFileSecurity Lib "advapi32.dll" Alias "GetFileSecurityA" (ByVal lpFileName As String, ByVal RequestedInformation As Long, pSecurityDescriptor As Byte, ByVal nLength As Long, lpnLengthNeeded As Long) As Long
Declare Function GetAclInformation Lib "advapi32.dll" (ByVal pAcl As Long, pAclInformation As Any, ByVal nAclInformationLength As Long, ByVal dwAclInformationClass As Long) As Long
'Public Declare Function EqualSid Lib "advapi32.dll" (pSid1 As Byte, ByVal pSid2 As Long) As Long
'Declare Function GetLengthSid Lib "advapi32.dll" (pSID As Any) As Long
'Declare Function InitializeAcl Lib "advapi32.dll" (pAcl As Byte, ByVal nAclLength As Long, ByVal dwAclRevision As Long) As Long
Declare Function GetAce Lib "advapi32.dll" (ByVal pAcl As Long, ByVal dwAceIndex As Long, pace As Any) As Long
'Declare Function AddAce Lib "advapi32.dll" (ByVal pAcl As Long, ByVal dwAceRevision As Long, ByVal dwStartingAceIndex As Long, ByVal pAceList As Long, ByVal nAceListLength As Long) As Long
'Declare Function AddAccessAllowedAce Lib "advapi32.dll" (pAcl As Byte, ByVal dwAceRevision As Long, ByVal AccessMask As Long, pSID As Byte) As Long
'Public Declare Function AddAccessDeniedAce Lib "advapi32.dll" (pAcl As Byte, ByVal dwAceRevision As Long, ByVal AccessMask As Long, pSID As Byte) As Long
'Declare Function SetSecurityDescriptorDacl Lib "advapi32.dll" (pSecurityDescriptor As SECURITY_DESCRIPTOR, ByVal bDaclPresent As Long, pDacl As Byte, ByVal bDaclDefaulted As Long) As Long
'Declare Function SetFileSecurity Lib "advapi32.dll" Alias "SetFileSecurityA" (ByVal lpFileName As String, ByVal SecurityInformation As Long, pSecurityDescriptor As SECURITY_DESCRIPTOR) As Long
Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, ByVal hpvSource As Long, ByVal cbCopy As Long)
'Declare Function GetSecurityDescriptorOwner Lib "advapi32.dll" (pSecurityDescriptor As Any, pOwner As Long, lpbOwnerDefaulted As Long) As Long
Declare Function LookupAccountSid Lib "advapi32.dll" Alias "LookupAccountSidA" (ByVal lpSystemName As String, ByVal Sid As Long, ByVal name As String, cbName As Long, ByVal ReferencedDomainName As String, cbReferencedDomainName As Long, peUse As Long) As Long
'Declare Function GetWindowsDirectory Lib "kernel32" Alias "GetWindowsDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long

Fonction GetFolderInfo

GetFolderInfo

Public Sub GetFolderInfo(sFolderName As String)
    Dim lResult As Long            ' Result of various API calls.
    Dim I As Integer               ' Used in looping.
    Dim bSDBuf() As Byte           ' Buffer that holds the security
    Dim lSizeNeeded As Long           ' Size needed for SD for file.
    Dim lDaclPresent As Long          ' Used in grabbing the DACL from
    Dim lDaclDefaulted As Long        ' Used in grabbing the DACL from
    Dim sACLInfo As ACL_SIZE_INFORMATION  ' Used in grabbing the ACL
    Dim sCurrentACE As ACCESS_ALLOWED_ACE    ' Current ACE.
    Dim pCurrentAce As Long                  ' Our current ACE.
    
    Dim lMask As Long
    Dim pSID As Long
    
    Dim bSuccess As Variant ' Status variable
    
    Dim sAccName As String ' Name of the file owner
    Dim lAccName As Long
    
    Dim sDomName As String ' Name of the first domain for the owner
    Dim lDomName As Long
    
    Dim peUse As Long
    Dim peLbl As String
    
    Dim droits As String
    Dim lNbCar As Long
    
    Dim R
    
    tabRights(0).Nom = "ACCESS_READ"
    tabRights(0).Flag = &H1
    tabRights(1).Nom = "ACCESS_WRITE"
    tabRights(1).Flag = &H2
    tabRights(2).Nom = "ACCESS_CREATE"
    tabRights(2).Flag = &H4
    tabRights(3).Nom = "ACCESS_EXEC"
    tabRights(3).Flag = &H8
    tabRights(4).Nom = "ACCESS_DELETE"
    tabRights(4).Flag = &H10
    tabRights(5).Nom = "ACCESS_ATTRIB"
    tabRights(5).Flag = &H20
    tabRights(6).Nom = "ACCESS_PERM"
    tabRights(6).Flag = &H40
    tabRights(7).Nom = "ACCESS_GROUP"
    tabRights(7).Flag = 32768 ' &H8000
    tabRights(8).Nom = "DELETE"
    tabRights(8).Flag = &H10000
    tabRights(9).Nom = "READ_CONTROL"
    tabRights(9).Flag = &H20000
    tabRights(10).Nom = "WRITE_DAC"
    tabRights(10).Flag = &H40000
    tabRights(11).Nom = "WRITE_OWNER"
    tabRights(11).Flag = &H80000
    tabRights(12).Nom = "SYNCHRONIZE"
    tabRights(12).Flag = &H100000
    tabRights(13).Nom = "ACCESS_SYSTEM_SECURITY"
    tabRights(13).Flag = &H1000000
    tabRights(14).Nom = "MAXIMUM_ALLOWED"
    tabRights(14).Flag = &H2000000
    tabRights(15).Nom = "GENERIC_ALL"
    tabRights(15).Flag = &H10000000
    tabRights(16).Nom = "GENERIC_EXECUTE"
    tabRights(16).Flag = &H20000000
    tabRights(17).Nom = "GENERIC_WRITE"
    tabRights(17).Flag = &H40000000
    tabRights(18).Nom = "SPECIFIC_RIGHTS_ALL"
    tabRights(18).Flag = 65535 ' &HFFFF
    tabRights(19).Nom = "STANDARD_RIGHTS_REQUIRED"
    tabRights(19).Flag = &HF0000
    tabRights(19).Nom = "STANDARD_RIGHTS_ALL"
    tabRights(19).Flag = &H1F0000
    
    lResult = GetFileSecurityN(sFolderName, DACL_SECURITY_INFORMATION, _
       0, 0, lSizeNeeded)
    
    ' Redimension the Security Descriptor buffer to the proper size.
    ReDim bSDBuf(lSizeNeeded)
    
    ' Now get the actual Security Descriptor for the file.
    lResult = GetFileSecurity(sFolderName, DACL_SECURITY_INFORMATION, _
       bSDBuf(0), lSizeNeeded, lSizeNeeded)
       
    ' A return code of zero means the call failed; test for this
    ' before continuing.
    If (lResult = 0) Then
       MsgBox "Error: Unable to Get the File Security Descriptor for " & sFileName
       Exit Sub
    Else
           MsgBox "Success: Ok Getting the File Security Descriptor"
           
        ' You now have the file's SD and a new Security Descriptor
        ' that will replace the current one. Next, pull the DACL from
        ' the SD. To do so, call the GetSecurityDescriptorDacl API
        ' function.
     
        lResult = GetSecurityDescriptorDacl(bSDBuf(0), lDaclPresent, _
           pAcl, lDaclDefaulted)
     
        ' A return code of zero means the call failed; test for this
        ' before continuing.
        If (lResult = 0) Then
           MsgBox "Error: Unable to Get DACL from File Security " _
              & "Descriptor"
           Exit Sub
        End If
     
        ' You have the file's SD, and want to now pull the ACL from the
        ' SD. To do so, call the GetACLInformation API function.
        ' See if ACL exists for this file before getting the ACL
        ' information.
        If (lDaclPresent = False) Then
           MsgBox "Error: No ACL Information Available for this File"
           Exit Sub
        End If
     
        ' Attempt to get the ACL from the file's Security Descriptor.
        lResult = GetAclInformation(pAcl, sACLInfo, Len(sACLInfo), 2&)
     
        ' A return code of zero means the call failed; test for this
        ' before continuing.
        If (lResult = 0) Then
           MsgBox "Error: Unable to Get ACL from File Security Descriptor"
           Exit Sub
        End If
    
         For I = 0 To (sACLInfo.AceCount - 1)
                ' Attempt to grab the next ACE.
                lResult = GetAce(pAcl, I, pCurrentAce)
                
                ' Make sure you have the current ACE under question.
                If (lResult = 0) Then
                    MsgBox "Error: Unable to Obtain ACE (" & I & ")"
                    Exit Sub
                End If
                CopyMemory sCurrentACE, pCurrentAce, LenB(sCurrentACE)
                
                lNbCar = 128
                sAccName = Space(lNbCar)
                sDomName = Space(lNbCar)
                lAccName = lNbCar
                lDomName = lNbCar
                pSID = pCurrentAce + 8  ' sCurrentACE.SidStart
                lMask = sCurrentACE.Mask
                '            MsgBox "ACE" _
                '                 & Chr(13) & Chr(10) & "(Header) AceFlags: " & sCurrentACE.Header.AceFlags _
                '                 & Chr(13) & Chr(10) & "(Header) ACE size: " & sCurrentACE.Header.AceSize _
                '                 & Chr(13) & Chr(10) & "(Header) ACE type: " & sCurrentACE.Header.AceType _
                '                 & Chr(13) & Chr(10) & "MASK : " & lMask _
                '                 & Chr(13) & Chr(10) & "SIDSTART : " & pSID
                
                bSuccess = LookupAccountSid(vbNullString, pSID, sAccName, lAccName, sDomName, lDomName, peUse)
                
                Select Case peUse
                    Case 1: peLbl = "User"
                    Case 2: peLbl = "Group"
                    Case 3: peLbl = "Domain"
                    Case 4: peLbl = "Alias"
                    Case 5: peLbl = "WellKnownGroup"
                    Case 6: peLbl = "DeletedAccount"
                    Case 7: peLbl = "Invalid"
                    Case Else: peLbl = "Unknown"
                End Select
                
                
                droits = ""
                For R = 0 To 19
                    Dim hMaskComp
                    hMaskComp = &H0
                    hMaskComp = (lMask And tabRights(R).Flag)
                    If (hMaskComp = tabRights(R).Flag) Then droits = droits & "-" & tabRights(R).Nom & Chr(10) & Chr(13)
                Next R
                
                If (bSuccess = 0) Then
                    MsgBox ("ERROR : unable to LookupAccountSid " & Chr(13) & Chr(10) & "SID " & pSID)
                Else
                    MsgBox ("(Header) AceFlags: " & sCurrentACE.Header.AceFlags _
                            & Chr(13) & Chr(10) & "(Header) ACE size: " & sCurrentACE.Header.AceSize _
                            & Chr(13) & Chr(10) & "(Header) ACE type: " & sCurrentACE.Header.AceType _
                            & Chr(13) & Chr(10) & "MASK : " & lMask _
                            & Chr(13) & Chr(10) & "SIDSTART : " & pSID _
                            & Chr(13) & Chr(10) & "---------------" & Chr(13) & Chr(10) & _
                            "DOMAIN :" & Left(sDomName, lDomName) & Chr(13) & Chr(10) & _
                            "USER   :" & Left(sAccName, lAccName) & Chr(13) & Chr(10) & _
                            "TYPE   :" & peLbl & Chr(13) & Chr(10) & _
                            "RIGHTS :" & droits)
                End If
          Next I
    End If
    End Sub

NB: Pour sélectionner un répertoire, il est possible d’utiliser l'exemple suivant : mvps.org

Liens externes

• www.allapi.net et www.tek-tips.com Codes sources utilisés comme bases pour la réalisation de cet exemple]

Devenez un connaisseur

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Introduction

Après avoir utilisé l’enregistreur de macro, l'utilisateur s'aperçoit vite des limites de ce procédé de mémorisation de commandes qui a cependant les qualités de rapidité et de simplicité. Il va lui manquer très vite des techniques pour évaluer des bornes, effectuer des calculs conditionnels, des boucles de traitement... La programmation VBA via Visual Basic Editor (VBE) va lui apporter des solutions à ces manques.

Plus intéressante mais également plus compliquée, cette programmation nécessite de connaître les spécificités du langage VBA que ce soit dans la déclaration de variables, les types de données, les opérateurs, les instructions,… Son apprentissage est compliquée par le fait que le nombre d’objet s sur lesquels le VBA peut agir est important. Non ! Ne nous quittez pas tout de suite, avec quelque rudiments de langage VBA que nous verrons par la suite, il vous sera possible de créer des macros complexes. En effet, la liaison enregistreur-programmation sert aux utilisateurs les plus chevronnés pour réaliser leurs macros. Nous verrons ensemble comment il est possible de réussir avec un minimum de vocabulaire VBA. La programmation VBA est également intéressante pour réaliser des tâches que l’on ne peut pas faire avec le tableur en lui-même comme par exemple ouvrir une boite de dialogue ou créer un formulaire.

La programmation

Nous allons maintenant vous présenter la partie technique de la programmation en VBA. Mais tout d’abord, qu’est-ce que la programmation ? La programmation est une branche de l’informatique qui sert à créer des programmes. Tout ce que vous possédez sur votre ordinateur sont des programmes : votre navigateur Web (Internet Explorer, Firefox, Opera, etc.), votre système d’exploitation (Windows, GNU/Linux, etc.), votre lecteur MP3, votre logiciel de discussion instantanée, vos jeux vidéos, etc.

Ce cours se penchera plus particulièrement sur la programmation au sein du programme Excel, puisqu’il utilise le Visual Basic qui est un outil développé par Microsoft pour développer facilement des applications fonctionnant sous Microsoft Windows ©.

Visual Basic est, comme son nom l'indique, un outil visuel permettant de créer sans notion de programmation l'interface graphique (GUI - Graphical User Interface) en disposant à l'aide de la souris des éléments graphiques (boutons, images, champs de texte, menus déroulants,...).

L'intérêt de ce langage est de pouvoir associer aux éléments de l'interface des portions de code associées à des événements (clic de souris, appui sur une touche, ...). Pour cela, Visual Basic utilise un petit langage de programmation dérivé du BASIC (signifiant Beginners All-Purpose Symbolic Instruction Code, soit code d'instructions symboliques multi-usage pour les débutants). Le langage de script utilisé par Visual Basic est nommé à juste titre VBScript, il s'agit ainsi d'un sous-ensemble de Visual Basic.

Les données usuelles

Les nombres VBA

Définition : les nombres sont des variables contenant des chiffres et des séparateurs décimaux. Les deux types les plus usuels de nombres sont les suivants :

• Entier : Integer
• Décimal : Double

Dim monNombre As Double monNombre est une variable destinée à contenir des nombres réels (par exemple "10.455")

Les chaines de caractères VBA

Définition : les chaînes de caractères sont des variables contenant des suites de caractères (alphabétiques, numériques ou spéciaux)

Dim maChaine As String maChaine est une variable destinée à contenir des caractères (par exemple "bonjour, 10 bises à vous tous ♥")

Les dates VBA

Définition : les dates sont des variables contenant des horaires ou des dates

Dim maDate As Date

maDate est une variable destinée à contenir des nombres et des séparateurs calendaires (par exemple "10/10/2013 14:55")

Les fonctions de détermination :

MsgBox Now ' Date et heure actuelles
MsgBox Date ' Date du jour
MsgBox Time ' Heure, minute et seconde actuelles

Les constantes VBA

Définition : les constantes sont des éléments nommés contenant des valeurs qui n'évolueront pas durant le déroulement du programme. Le nom de la constante est utilisé à la place de la valeur qui lui est attribuée ; elle est en général déclarée pour être utilisée par plusieurs programmes, procédures ou fonctions.

CONST pour déclarer une variable constante

Exemple : CONST Pi=3.14, une instruction contenant Pi utilisera la valeur de la constante pour effectuer son calcul >> 
périmètre = 2 * Pi * rayon >> périmètre = 2 * 3.14 * rayon 

Les mots clés usuels du langage BASIC

Les mots-clés sont des mots réservés par le langage VBA ; ils ne doivent JAMAIS être employés pour nommer vos variables, constantes, procédures, fonctions et objets.

Exemple : Dim, As, If, Then, Else, Endif, For, Next, While, Function, Sub, End, With, etc.

Les objets usuels de la bibliothèque Excel

Un objet est une "entité" informatique qu'un programme informatique peut manipuler. Un objet est une entité interne ou externe à VBA.

Les objets usuels sont des mots réservés par la bibliothèque EXCEL, ils ne doivent JAMAIS être employés par le développeur pour nommer ou déclarer de nouvelles variables, constantes. L'instruction SET permet d'affecter une référence à un objet, le type d'un objet est objet.

Le cas le plus évident est une cellule de classeur. Pour la manipuler, VBA utilise une instance de la classe Range.

Exemple d'objets usuels : Application, Selection, ActiveCell, ActiveSheet, ActiveWorkBook, Range, Cells

• Application représente l’application en cours d’utilisation (Excel, Word, Access);
• Workbooks représente les classeurs Excel (Documents, les documents Word);
• Sheets pour les feuilles du classeur;
• Cells pour les cellules;
• Range pour une plage de cellules;

Utilisation VBA des Objets

• Sheets("maFeuille") ou Sheets(2) désigne une seule feuille de l’ensemble (collection) de feuilles
• Range("B2") désigne la cellule B2 (on peut écrire [B2] à la place de Range("B2"));
• Cells(3) désigne la 3^e cellule;
• Cells(2,3) désigne la cellule située à la 2^e ligne et 3^e colonne (dans une feuille de calcul, c’est la cellule $C$2)
• Workbooks("monClasseur").Sheets("maFeuille").Cells(3) désigne la 3^e cellule de la feuille maFeuille du classeur monClasseur

Les propriétés des objets

Il s'agit de nombres (dimensions, valeurs), de textes (adresses, nom), ou de variables booléennes (le fait d’être visible ou non, d’être verrouillé ou non) relatifs à un objet. Par exemple : le nom (Name), le chemin d'accès (Path) d'un fichier, le texte (Caption) d'un contrôle, l'adresse d'une cellule (Address), la valeur (Value) d'une cellule ou d'une barre de défilement, la visibilité (Visible), le verrouillage (Enabled), ...

Une propriété d'un objet peut être lue :

 'affiche la valeur de la cellule A1
 MsgBox(Cells(1,1).Value) 
 'affiche le chemin du classeur Excel actif
 MsgBox(ActiveWorkbook.Path)

Une propriété d'un objet peut être modifiée :

 'écrit 10 dans la cellule A1 
 Cells(1,1).Value = 10  
 'renomme "nouveauNom" la première feuille 
 Sheets(1).Name = "nouveauNom"  
 'cache le bouton CommandButton1
 CommandButton1.Visible = False 
 Label1.Caption = "Bonjour"

Les procédures usuelles

Une procédure permet d'exécuter une série d'instructions. C'est un sous-programme qui s'exécute par simple appel dans le corps du programme principal. Cette notion de sous-programme est généralement appelée procédure dans la plupart des langages de programmation, VBA l'appelle indifféremment procédure ou macro. En voici un exemple simple :

 Sub pDixAuCarre ()
  Dim resultat As Integer
  'Cette procédure pDixAuCarre affiche la valeur 100
  resultat = 10 ^ 2
  MsgBox resultat
 End Sub

Les fonctions usuelles

Une fonction est une procédure effectuant une action et renvoyant une valeur en sortie. En voici un exemple simple :

 Function fDixAuCarre() As Integer
  'Cette fonction dixAuCarre renvoie la valeur 100
  fdixAuCarre = 10 ^ 2 
 End Function

Les instructions de débranchement

Après exécution d'une ligne d'instruction, un programme VBA passe séquentiellement à l'exécution de la ligne suivante :

 Range("E6").Select
 a = 12
 ActiveCell.FormulaR1C1 = a
 Range("E7").Select
 b = 13
 ActiveCell.FormulaR1C1 = b
 Range("E8").Select
 ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"

Le programme commence par sélectionner la cellule E6, puis affecter la valeur 12 à la variable a, puis exécute toutes les lignes suivantes jusqu'à l'affectation d'une formule dans la cellule sélectionnée. Ce qui en limiterait très vite l’intérêt s'il n'existait pas des possibilités de débranchements. Il faudra donc, pour rompre ce chemin naturellement linéaire, utiliser des instructions spéciales :

• Débranchements directs ⇒ Goto (se déplacer à une étiquette), Exit (fin de boucle ou de fonction), End (fin de programme), Stop.
• Instructions conditionnelles ⇒ If, Then, Else, ElseIf, End If.
• Instructions de boucles ⇒ For... Next, For Each... Next, While... Wend, Do... Loop.
• Appels de procédures ⇒ Call.
• Appels de fonctions ⇒ prixToutesTaxesComprises = prixHorsTaxe + functionCalculerTVA(prixHorsTaxe).

Les instructions conditionnelles

Ces instructions sont utilisées lorsque le traitement à appliquer dépend d'une condition (d'un résultat de test), alors la suite séquentielle naturelle des instructions est rompue grâce à ce test.

Début de l'exemple

Exemple

 If Range("E8").Value = 20 then MsgBox "BRAVO !!!"

Fin de l'exemple

⇒ L'exemple affiche "BRAVO !!!" si la valeur de la cellule E8 est 20

Début de l'exemple

Exemple

 If Range("E8").Value = 20
  Then MsgBox "MILLE BRAVO !!!" 
  Else MsgBox "PEUT ÊTRE BRAVO !!!" 
 End If

Fin de l'exemple

⇒ L'exemple affiche "MILLE BRAVO !!!" si la valeur de la cellule E8 est 20 sinon affiche "PEUT ÊTRE BRAVO !!!"

Les Boucles

En programmation, une boucle, aussi appelée itération, permet d'effectuer une série d'actions de façon répétitive.

Il existe plusieurs solutions pour créer une boucle :

• For Each / Next: Boucle sur chaque objet d'une collection.
• For Next: Répète une action le nombre de fois spécifié par un compteur.
• While Wend: Répète une action tant qu'une condition est vraie.
• Do Loop: Itération pendant ou jusqu'à ce qu'une condition soit remplie.

Les Instructions de boucles avec bornes connues

Définition

Une boucle permet de répéter un certain nombre de fois les instructions qui sont comprises entre ses bornes; cet outil, présent dans tous les langages informatiques, combiné à la vitesse exceptionnelle des processeurs autorise des calculs numériques quasiment infinis.

La boucle For ... Next

La boucle de type For ... Next permet de répéter un nombre de fois défini un bloc d'instructions. Les boucles utilisent une variable qui est incrémentée ou décrémentée à chaque répétition. Vous devez spécifier une valeur de début [Numéro de départ] et une valeur de fin [Numéro d'arrivée]. La variable [compteur] va ensuite être incrémentée ou décrémentée à chaque itération.

Début de l'exemple

Exemple

For i=1 to 10

Range("A1").Offset(i - 1) = 3 * i 

Next i

Fin de l'exemple

⇒ L'exemple écrit la table des trois dans la plage de cellule "A1:A10". La variable i s'incrémente de 1 à chaque tour de boucle, c'est-à-dire prend successivement les valeurs 1, 2, 3, … , 9, 10. Arrivée à la valeur 10, la boucle s'arrête.

Ce second exemple boucle sur les cellules du de la feuille en cours :

 Sub boucleCellules()
  Dim i As Integer    
  'La variable i va successivement prendre les valeurs 3 à 10
  For i = 3 To 10
   'Écrit le libellé COUCOU successivement dans les cellules A3:A10
   Cells(i, 1) = "COUCOU !!!"
  Next i
 End Sub

Boucles de type ForEach ... Next

Elles permettent de répéter un bloc d'instructions pour chacun des objets appartenant à un ensemble : elles utilisent une variable objet qui sélectionne un par un les objets de l’ensemble (à chaque tour). Le principe de fonctionnement de l'instruction For Each Next consiste à boucler sur tous les objets d'une collection spécifique. Si la collection ne contient pas d'objet ou quand tous les objets ont été parcourus, la boucle se termine et l'exécution continue sur la ligne de code, juste après l'instruction Next.

Début de l'exemple

Exemple

For Each cellule In Selection

  cellule.Font.Color = RGB(0, 255, 0)

Next

Fin de l'exemple

⇒ L'exemple colorie la police de chaque cellule de la plage sélectionnée. La variable cellule parcourt une par une toutes les cellules sélectionnées et leur donne un fond vert. Arrivée à la dernière cellule de la sélection, la boucle s'arrête.

Ce second exemple boucle sur les classeurs ouverts dans l’application Excel:

			
 Sub boucleClasseurs()
  'Définit une variable qui va représenter un classeur à chaque itération.
  Dim Wb As Workbook
  'Boucle sur chaque classeur de l’application Excel
  For Each Wb In Application.Workbooks
   'Écrit le nom de chaque classeur dans la fenêtre d'exécution
    Debug.Print Wb.Name
  Next Wb
 End Sub

Exemple commenté

Cette macro permet la coloration du fond d'une cellule quand on rencontre une cellule contenant le mot "fauteuil" sur la ligne parcourue

Sub PeindreFondMarronLesFauteuils()
'
' Cette macro peint en marron les lignes comprenant le libellé "fauteuil"
'
    Dim i As Integer
    Dim produit As String
    For i = 2 To 9
        produit = Range("B" & i).Value
        If (produit = "fauteuil") Then
            Range("D" & i).Select
            With Selection.Interior
                .Color = -33333333
                .TintAndShade = 0
            End With
        End If
    Next i
End Sub

On peut ici observer une macro dont la fonction est de peindre en marron le fond d'une cellule située en colonne D, avec pour référence un mot situé en colonne B, dans un intervalle de lignes compris entre 2 et 9

Résultat de la Macro

Voici la signification de chacune des lignes de cette macro :

 Sub PeindreFondMarronLesFauteuils() 'Voici le début et le nom de la macro
 '
 ' Cette macro peint en marron les lignes comprenant le libellé "fauteuil" 'Voici le commentaire général de la macro
 '
    Dim i As Integer 'Cette déclaration permet de déclarer une variable i comme un nombre entier
    Dim produit As String 'Cette déclaration permet de déclarer une variable chaine de caractères
    For i = 2 To 9 'On fait varier successivement la variable i entre 2 et 9 à chaque tour de boucle 
        produit = Range("B" & i).Value 'Ici on affecte la valeur de chaque ligne de la colonne B à la variable produit
        If (produit = "fauteuil") Then 'Ici on teste l'égalité entre la valeur de la variable produit avec le libellé "fauteuil"
            Range("D" & i).Select 'Si l'égalité est constatée on sélectionne la colonne D de la même ligne
            With Selection.Interior 'Se positionne sur le fond de la cellule sélectionnée
                .Color = -33333333 'on colore le fond avec la couleur marron
                .TintAndShade = 0 'on règle le contraste de la cellule
            End With 'On sort du fond de la cellule sélectionnée
        End If 'On sort de la condition si, donc de l'égalité avec le libellé "fauteuil"
    Next i 'on retourne en début de boucle en demandant la prochaine valeur de i
 End Sub 'Il s'agit de la fin de la macro

Annexes

Bibliographie

• Amelot M., 2011, VBA Excel 2010, ENI, St Herblain
• Bidault M., 2000, Excel et VBA, CAMPUS, Paris

Liens internes

• Remplacement des hyperliens : exercice de parcours et de remplacement automatique de liens
• Consolidation de classeurs : exercice de consolidation automatique de classeurs
• QCM Les macros VBA
• Leçon VISUAL BASIC
• Leçon Tableur EXCEL

Liens externes

• Les boucles
• Les conditions

Devenez un expert

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Introduction

Le langage ou code VBA contient différentes entités : des données, des modules, des classes, des structures, des instructions, des objets, des procédures, des fonctions, … Ces entités représentées par des lignes de texte s’organisent de façon purement hiérarchique

• les classeurs contiennent des projets (1 projet par classeur)
• les projets contiennent les modules de code, de classe, de feuille, ou de formulaire, regroupés par catégories
• les modules contiennent des directives, des déclarations, des fonctions, des procédures
• les procédures et fonctions contiennent des instructions (lignes de code)

Les contenants de code VBA

Le code VBA est saisi et stocké dans 2 types de récepteurs sous un format uniquement textuel : des feuilles et des modules

• Feuille : objet Excel accueillant les procédures particulières à une feuille de calcul Excel, cet objet contient le plus souvent des procédures et fonctions concernant des objets s’appliquant à lui-même.
• Module de code standard : objet VBA accueillant les procédures générales, le plus souvent contiennent des fonctions dites "utilitaires".
• Module formulaire : objet VBA contenant les procédures événementielles propres au formulaire traité. Il peut également contenir des procédures générales (à éviter). Ex : une procédure de traitement de date serait stockée dans un module standard pour pouvoir être utilisée dans tous les formulaires concernés.
• Module de classe : objet VBA contenant les données et procédures pour un objet (on parle alors de propriétés et de méthodes). Ex : un objet compte en banque qui décrit ses données (mouvement, solde, …) et les opérations sur ses données (retrait, dépôt, virement, …).

Les données des experts

Les variables

Les variables permettent de stocker toutes sortes de données et de faire évoluer les valeurs notamment lors de l’utilisation de boucles. Les variables sont généralement déclarées en début de procédure puisqu’une variable doit être définie avant d’être utilisée.

Nom	Type	Détail
Byte	Numérique	Nombre entier de 0 à 255
Integer	Numérique	Nombre entier de -32'768 à 32'767
Long	Numérique	Nombre entier de -2'147'483'648 à 2'147'483'647
Currency	Numérique	Nombre à décimal fixe de -992'337'203'685'477.5808 à 992'337'203'685'477.5807
Single	Numérique	Nombre à virgule flottante de -3.402823E38 à 3.402823E38
Double	Numérique	Nombre à virgule flottante de -1,79769313486232D308 à 1,79769313486232D308
String	Texte	Texte
Date	Date	Date et heure
Boolean	Boolean	True (vrai) ou False (faux)
Object	Objet	Objet Microsoft
Variant	Tous	Tout type de données (type par défaut si la variable n’est pas déclarée)

Une variable est définie selon l’utilisation que l’on va en faire. Le type de variable choisi était auparavant fondamental au niveau de la mémoire utilisée par l’ordinateur, mais de nos jours ce problème n’est plus d’actualité car les ordinateurs ont de grandes mémoires.

Les nombres spéciaux VBA

Définition : les nombres sont des variables contenant des chiffres et des séparateurs décimaux, il y a 2 types expert de nombres

• Entier Long : Long
• Booléen : Boolean

Dim monNombre As Boolean monNombre est une variable destinée à contenir des nombres booléens(par exemple "0")

Les tableaux VBA

Définition : les tableaux sont des variables contenant d'autres variables de même type

Dim monTableau(10) As String monTableau est un tableau contenant 10 éléments dont le type est une chaîne de caractères.

Les constantes VBA intégrées

Définition : les constantes intégrées sont fournies par les applications OFFICE (Word, Excel, …) ou le langage Visual Basic :

Si la constante commence par vb alors c’est une constante intégrée à VBA Si la constante commence par xl alors c’est une constante intégrée à EXCEL Si la constante commence par wd alors c’est une constante intégrée à WORD …

Exemple : vbCrLf = retour à la ligne mais on ne peut pas écrire vbCrLf = Chr(13) + Chr(14)

Les mots clés des experts

Les mots-clés des experts permettent de programmer des problématiques délicates ou complexes

Exemple : ReDim, Static, Do, Loop, Break, On, Continue, Set, etc.

Les objets experts de la bibliothèque Excel

Les objets des experts permettent de programmer des problématiques délicates ou complexes

Exemples : Workbooks, Worksheets, Sheets, Rows, Columns, Object, Windows, Font , Interior , Offset, Calendar, Charts, Names, CommandBars, UserForms, ...

Les objets classeurs

Ouvrir un fichier avec trois feuilles déjà remplies pour y exécuter la macro :

Sub Feuilles()
    Dim Source, Destination As Excel.Workbook
    Set Source = ActiveWorkbook
    
    ' Création
    Workbooks.Add
    Workbooks.Add After:=Sheets(Sheets.count) ' à la fin
    Set Destination = ActiveWorkbook
    ' Copie d'une cellule dans un autre classeur
    Destination.Sheets(1).Cells(1, 1) = Source.Sheets(1).Cells(1, 1) ' Avec bordures
    Destination.Sheets(1).Cells(1, 2) = Source.Sheets(1).Cells(1, 2).Value ' Sans bordure
    ' Copie d'une feuille
    Source.Sheets(1).Copy After:=Sheets(1)
    ' Copie d'une feuille dans un autre classeur
    Source.Sheets(1).Copy After:=Destination.Sheets(1)
    ' Déplacement d'une feuille dans un autre classeur
    Source.Sheets(1).Move After:=Destination.Sheets(1)
    ' Suppression
    Source.Sheets(1).Delete
    ' Parcourir toutes les feuilles d'un fichier
    For Each workheets In ActiveWorkbook.Worksheets
        MsgBox workheets.Name
    Next
End Sub

Les objets feuilles

Il faut définir une première plage, sur laquelle trier une deuxième :

Sub classer()
    ' Tri sur la colonne C, du tableau AJ
    Plage1 = "C" & EnteteLigne & ":C" & LigneFin
    Plage2 = "A" & EnteteLigne & ":J" & LigneFin

    With ActiveWorkSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range(Plage1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range(Plage2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Les objets cellules

Ici on simule un copier-coller :

 ' Copie de la première case dans la deuxième
 Range("A1") = Range("B1")
 ' ou
 Cells(1,1) = Cells(1,2)
 ' ou
 Range("A1").copy
 Range("B1").paste
 Range("A2:C10").copy

 Cells(l, c).Font.Size = 10    ' Taille du texte
 Cells(l, c).Font.Bold = True  ' Mise en gras
 Cells(l, c).HorizontalAlignment = xlCenter    ' Alignement
 Cells(l, c).Font.Color = vbBlack    ' Couleur de la police
' ou
 Cells(l, c).Font.Colorindex = 2
 Cells(l, c).Interior.ColorIndex = 4    ' Couleur de la cellule
 Cells(l, c).Numberformat("$#,##0.00")    ' Format monétaire

Remarque

La commande .copy partage son presse papier avec l'utilisateur. Donc si la personne fait un copié-collé pendant que la macro tourne, il y aura des interférences.

For Each c In ActiveCell.CurrentRegion.Cells
   Cells(x + c.Row, y + c.Column).value = c.value
Next c

Remarque

Le problème du copy/paste est qu’il utilise le même presse-papier que l'utilisateur. Il suffit donc de changer de cellule manuellement (ou de copier du texte) pour que les données du programme lui échappent. La solution consiste ici à balayer les cellules une par une

Les objets lignes

Sub Lignes()
   ' Insertion d'une ligne 2 vierge
   Rows(2).Insert
   ' Insertion après la ou les cellules sélectionnées
   Selection.EntireRow.Insert
   ' Suppression
   Rows(2).Delete
   ' Copie de toutes les lignes jusqu'à la fin du tableau en cours
   ActiveCell.CurrentRegion.Copy
   ' Duplique la ligne L juste en dessous
   Rows(L + 1).Insert Shift:=xlDown
   Rows(L).Copy
   Rows(L + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Les objets colonnes

Sub Colonnes()
   ' Insertion colonne 2
   Columns(2).Insert
   ' Insertion après la ou les cellules sélectionnées
   Selection.EntireColumn.Insert
   ' Suppression
   Columns(2).Delete
End Sub

Les objets liens

La macro suivante placée dans une feuille crée automatiquement un hyperlien à chaque fois que l’on tape un mot dans une de ses cellules, vers sa définition du Wiktionnaire :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not IsNull(Target.Cells) And Not IsEmpty(Target.Cells) And TypeName(Target.Cells) = "Range" And Len(Trim(Target.Cells)) > 0 Then
       ActiveSheet.Hyperlinks.Add Anchor:=Target.Cells, _
        TextToDisplay:=Target.Cells.Value, _
        Address:="https://fr.wiktionary.org/w/index.php?title=" & Target.Cells.Value
    End If
End Sub

Les objets Word

La macro suivante placée dans une feuille ouvre automatiquement un fichier Word et écrit une nouvelle phrase :

Sub Edition()
dim ObjetWord as object
    Set ObjetWord = CreateObject("Word.Application")
    ObjetWord.Visible = True
    'Ouverture d'un fichier existant
    ObjetWord.Documents.Open App.Path & "\document.docx"                                         
    'Création d'un nouveau fichier
    ObjetWord.Documents.Add    
    'Ajout de texte ObjetWord.Selection.TypeText Text:="Texte écris."                                                                  
    ObjetWord.Selection.TypeText "J'écris une nouvelle phrase depuis EXCEL sur WORD."             
    ObjetWord.Selection.TypeText Text="J'écris une nouvelle phrase depuis EXCEL sur WORD."             
    'Sauvegarder
    NomDuDocumentWord.Save                                  
    'Imprimer
    ObjetWord.PrintOut 
    'Quitter                                     
    ObjetWord.Quit
End Sub

Les objets Outlook

La fonction createitem de l’objet Outlook génère les différentes entités du logiciel :

1. createitem(0) : un mail.
2. createitem(1) : un RDV.
3. createitem(2) : un contact.
4. createitem(3) : une tâche.
5. createitem(4) : un journal.
6. createitem(5) : une note.
7. createitem(6) : un post.

L'exemple ci-dessous envoi un email contenant un hyperlien et une pièce jointe :

Sub EnvoyerMail()
    Dim Outlook, Message As Object
    Set Outlook = CreateObject("Outlook.Application")
    Set Message = ObjOutlook.createitem(0)
    With Message
        .To = "moi@domaine.com"
        .Subject = "Lien vers le cours de VBA"
        .HTMLBody = "Bonjour,<br />Voici le lien vers le cours de VBA : <br /><A HREF=""http://fr.wikiversity.org/wiki/Visual_Basic"">"ici"</A>.<br />Cordialement."
        .Attachments.Add (cheminPJ)
        .Display (True)
        .Send
    End With
End Sub

Les objets ActiveX

On peut facilement insérer dans une feuille de calcul Excel ou dans une feuille Word divers objets (des "contrôles")comme

• des boutons :

  

  bouton

• des boutons d'options, des cases à cocher :

  

  Option button

• des zones de texte modifiables (textbox):

  

  Figure

• des barres de défilement (scrollBar):

  

  Barre crée pour aller de droite à gauche dans un tableur

• des boutons toupies (SpinButton), des zones de liste, des listes déroulantes :

  

  Barre crée pour aller de droite à gauche dans un tableur

• des zones de texte (label) :

  

  Texte "m"

Il suffit de cliquer sur l’icône "boîte à outils" de la barre d’outils VBA (ou bien Affichage / Barres d’outils / Commandes), de sélectionner le contrôle souhaité et de le glisser via la souris à l’endroit désiré.

On peut également utiliser tous ces "contrôles" dans une boite de dialogue (Userform) que l’on peut créer dans VBA par formulaire / Userform, puis faire apparaître à l'exécution d'une macro par: Userform("truc").Show et disparaître par Userform("truc").Hide.

Les procédures et les fonctions expertes

Usages complexes

• Les paramètres ou arguments
• Les fonctions prédéfinies VBA
• Disponibilité des fonctions VBA sous Excel
• Les fonctions récursives

Les arguments des procédures

Définition : les arguments passés aux procédures sont aussi nommés paramètres, ce sont des valeurs nécessaires au traitement de la procédure. Exemple : après l'appel de la procédure ⇒ afficherMessageCible "ToiKeuJème", le résultat affiché à l’écran est : "Bonjour à ToiKeuJème"

 Sub afficherMessageCible (cible As String)
  MsgBox "Bonjour à " & cible
 End Sub

Les arguments et le résultat des fonctions

Définition : les arguments passés aux fonctions fonctionnent comme pour les procédures, le résultat est rangé sous le nom de la fonction. Exemple : après l'appel de la fonction ⇒ monBenefice = calculerBenefice(150,100), la variable monBenefice prend la valeur 150

 Function calculerBenefice(recettes As Double, depenses As Double) As Double
  calculerBenefice = recettes - depenses
 End Function

Les instructions de débranchement

Les instructions conditionnelles complexes

Début de l'exemple

Exemple

 If Range("E8").Value = "20" 
  Then MsgBox "MILLE BRAVO !!!" 
  Else if  Range("E8").Value > "10" MsgBox "PETIT BRAVO !!!" 
  Else  MsgBox "PAS BRAVO DU TOUT !!!" 
 End If

Fin de l'exemple

⇒ L'exemple affiche "MILLE BRAVO !!!" si la valeur de la cellule E8 est 20 sinon affiche "PETIT BRAVO !!!" si la valeur de la cellule E8 est supérieure à 10, sinon affiche "PAS BRAVO DU TOUT !!!"

Les instructions de distinctions de cas

Cette instruction est utilisée lorsque le nombre de cas à tester(comme ci-dessus) devient important rendant difficiles les instructions employant des conditionnelles imbriquées (if...if...)

Selon une valeur de variable
  En cas d'une valeur alors effectuer un traitement 
  En cas d'une autre valeur alors effectuer un autre traitement
  Dans tous les autres cas alors effectuer le traitement encore un autre traitement
Fin Selon

Début de l'exemple

Exemple

Select Case Range("E8").Value
   Case 20 : MsgBox "MILLE BRAVO !!!" 
   Case > 10 : MsgBox "PETIT BRAVO !!!"
   Case else : "PAS BRAVO DU TOUT !!!"
End Select

Fin de l'exemple

⇒ Selon la valeur de la cellule E8 l'exemple affiche soit "MILLE BRAVO !!!" soit "PETIT BRAVO !!!" soit "PAS BRAVO DU TOUT !!!"

Impossible de placer plusieurs conditions après un case. Par exemple Case False and False peut très bien être considéré à tort comme Case True.

Les Instructions de boucles avec bornes inconnues

Les boucles non bornées le plus souvent utilisées sont les boucles de type « « While … Wend » », elles permettent de répéter un nombre de fois indéfini au départ un bloc d'instructions, elles utilisent une condition qui est testée au début de chaque répétition.

La boucle While / Wend

L'instruction While Wend répète une action tant qu'une condition est vraie.

 While [condition]
  [actions]
 Wend

Si la condition est vraie, les actions indiquées dans la procédure sont effectuées. Quand l'instruction Wend est atteinte, la procédure revient sur l'instruction While et la condition est de nouveau vérifiée. Si la condition est toujours vraie, le processus est répété. Si la condition est fausse, l'exécution passe directement à la première ligne de code qui suit l'instruction Wend.

Début de l'exemple

Exemple

 ' L'exemple colorie l'intérieur de toutes les cellules non vides de la colonne A en violet
 While not isEmpty(cellule)
   ligne = ligne + 1
   cellule = Range("A1").Offset(ligne - 1)
   cellule.Interior.Color = RGB(255, 0, 255)
 'La boucle s'arrête lorsque la cellule en cours est vide
 Wend

Fin de l'exemple

La boucle Do While / Loop

Cette boucle fonctionne de la même manière que While Wend (tant que la condition est vraie, la boucle est exécutée) :

 Do While [condition]
  'Instructions
 Loop

La boucle Do / Loop Until

Cette boucle fonctionne de la même manière que While Wend, exceptée la une condition qui est testée à la fin de chaque répétition.

 Do [condition]
  'Instructions
 Loop Until

Le corps de la boucle (instructions) est alors toujours exécuté au moins une fois.

REGEX

Nécessite de cocher "Microsoft VBScript Regular Expressions 5.5" dans "Outils\Références".

Expressions rationnelles courantes

Caractère	Type	Explication
.	Point	n’importe quel caractère
[...]	classe de caractères	tous les caractères énumérés dans la classe
[^...]	classe complémentée	Tous les caractères sauf ceux énumérés
^	circonflexe	marque le début de la chaine, la ligne...
$	dollar	marque la fin d'une chaine, ligne...
|	barre verticale	alternative - ou reconnaît l'un ou l'autre
(...)	parenthèse	utilisée pour limiter la portée d'un masque ou de l'alternative
*	astérisque	0, 1 ou plusieurs occurrences
+	le plus	1 ou plusieurs occurrence
?	interrogation	0 ou 1 occurrence

• .Test() : renvoie vrai si le pattern est trouvé dans la chaine.
• .Execute() : renvoie toutes les positions du pattern dans un tableau.
• .Replace() : remplace le pattern par le paramètre deux.

Recherche

Obtenir les emplacements dans une chaine :

Sub RegexRecherche()
    Dim RegEx As RegExp
    Set RegEx = New RegExp
    Dim matches As IMatchCollection2
    Dim match As Variant
    With RegEx
        .IgnoreCase = True
        .Global = True 'True matches all occurances, False matches the first occurance
        .Pattern = "[0-9]+"
    Set matches = .Execute("1 test 2 regex")
    End With
    For Each match In matches
        MsgBox ("Position : " & match.FirstIndex)
    Next
    ' ou
    MsgBox matches.Item(0).Value
End Sub

Extraire un mot

Public Sub RegexExtraction()
    Dim RegEx As RegExp
    Set RegEx = New RegExp
    With RegEx
        .IgnoreCase = True
        .Global = False
        .Pattern = "[a-z]* Wikibooks"
    End With
    chaine$ = "Test regex VB pour Wikibooks francophone."
    Set matches = RegEx.Execute(chaine$)
    MsgBox (Replace(matches(0).Value, " Wikibooks", ""))
    ' Affiche : "pour"
End Sub

Remplacement

Supprimer toutes les balises HTML :

Public Sub RegexChaine()
    Dim RegEx As RegExp
    Set RegEx = New RegExp
    With RegEx
        .IgnoreCase = True
        .Global = False
        .Pattern = "<.*>(.*)<.*>"
    End With
    chaine$ = "Test regex VB pour <balise1>Wikibooks</balise1> francophone."
    chaine$ = RegEx.Replace(chaine$, "$1")
    MsgBox (chaine$)
    ' Affiche : "Test regex VB pour Wikibooks francophone." (sans les balises)
End Sub

Références

Début de l'exemple

Exemple

 ' cet exemple cumule des nombres saisis à chaque tour de boucle par l'utilisateur dans une variable "tresor"
 Do 
   reponse = InputBox("Entrer un nombre entier à cumuler dans votre trésor ") 
   tresor = tresor + reponse
 Loop Until reponse = 0 
 ' la boucle s'arrête lorsque la réponse de l’utilisateur est 0

Fin de l'exemple

Annexes

Bibliographie

• Jensen O.G., 2003, Initiation à la programmation VBA Word, KNOWWARE, Ecuelles
• Greg Harvey et John Walkenbach, 2013, Excel 2013 et VBA Pour Les Nuls, FIRST, Hoboken
• Amelot M., 2011, VBA Excel 2010, ENI, St Herblain

Liens internes

• Leçon VISUAL BASIC
• Leçon Tableur EXCEL

Liens externes

• Les procédures et les fonctions
• Les fichiers
• [1]

Procédures et événements automatiques

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Introduction

Plusieurs moyens permettent d'exécuter une procédure Sub. L'une d'entre elles consiste à faire en sorte qu'elle le soit automatiquement. Dans cette leçon, vous découvrirez tout ce qu’il faut savoir pour exploiter ces fonctionnalités particulièrement puissantes. Nous vous expliquerons comment préparer le terrain pour qu'une macro soit déclenchée lorsqu'un événement se produit.

De quels types d’événements est-il question ici ? C'est une bonne question. Un événement est fondamentalement quelque chose qui se produit dans Excel. Voici quelques exemples :

• l'ouverture ou la fermeture d'un classeur ;
• l'activation ou la désactivation d'une fenêtre ;
• l'activation ou la désactivation d'une feuille de calcul
• l'entrée de données dans une cellule, ou la modification d'une cellule ;
• l'enregistrement du classeur ;
• le calcul dans la feuille ;
• un clic sur un objet (un bouton par exemple) ;
• l'appui sur une touche ou sur une combinaison de touches ;
• le double clic dans une cellule ;
• la survenance d'une heure du jour particulière ;
• une erreur.

La plupart des programmeurs ne se soucient pas de la majorité de ces événements. Vous devez cependant savoir qu’ils existent, car, un jour ou l'autre, ils pourront vous être utiles. Dans cette leçon nous utiliserons les événements les plus communément utilisés. Pour faire simple, nous n'en aborderons que deux : les classeurs et les feuilles de calcul.

Au terme de cette leçon vous saurez répondre aux questions suivantes :

• quels sont les événements qui peuvent déclencher une exécution ?
• où faut-il placer le gestionnaire d'événement dans le code VBA ?
• comment exécuter une macro à l'ouverture/fermeture d'un classeur ou quand un classeur/feuille de calcul est activé ?

Caractéristiques des procédures d'évenements

Un événement est toujours associé à un objet ou à une collection d'objets spécifique. Nous avons défini une procédure événementielle de niveau "feuille de calcul", attachée à la première feuille du classeur. Si nous voulions que cette procédure se déclenche quand l'utilisateur sélectionne de nouvelles cellules dans n’importe quelle feuille du classeur, il faudrait créer une procédure événementielle de niveau "classeur", s'appliquant à l’ensemble de ses feuilles.

La feuille de calcul représente ici l’objet associé à la procédure événementielle. Il peut s'agir soit d'un objet existant, créé par l'utilisateur, soit d'un objet créé par programmation.

Sous Excel, il existe cinq types principaux d'objets associés.

• Feuille de calcul
• Feuille graphique
• Classeur
• Application (instance d'Excel)
• Boîte de dialogue (UserForm)

Les quatre premiers types d'objets sont spécifiques à Excel, alors que les UserForms peuvent s'utiliser dans toutes les autres applications (Access, Word...) intégrant VBA.

À ces types d'objets, il faut ajouter ceux qui sont créés et définis par programmation par l'intermédiaire de modules de classe.

Pourquoi écrire une procédure d'événement ?

Laissez nous vous convaincre de l'utilité de ces procédures.

Voici un exemple : supposons que vous ayez un classeur dans lequel vous entrez des valeurs dans la colonne A. Votre chef, un gars très compulsif, vous dit qu’il a besoin de savoir quand chaque nombre a été saisi. Le fait d'entrer des données est un événement, plus précisément un événement appelé Worksheet_Change. Vous pouvez écrire une macro qui réagisse à cet événement. Cette macro sera déclenchée chaque fois que la feuille de calcul est modifiée. Si cette modification a été faite dans la colonne A, elle écrira la date et l’heure dans la colonne B, juste à droite de la cellule qui a été éditée.

À titre de curiosité, voyons à quoi une telle macro pourrait bien ressembler :

Private Sub Worksheet_Change (ByVal Target As Range)
    If Target.Column = 1 Then
      Target.Offset(0, 1) = Now
    End if
End Sub

Les macro qui répondent à des événement sont très attentives à l'endroit où elles sont enregistrées. Par exemple, cette macro Worksheet_Change doit être placée dans le module de code associé à cette feuille de calcul. Mettez-la ailleurs, et elle ne fonctionnera pas. Nous allons y revenir un peu plus loin, dans la section "Où placer le code VBA ?".

Attention ! Ce n’est pas parce que votre classeur contient des procédures qui répondent à des événements que celles-ci sont obligatoirement exécutées. Comme vous le savez, il est possible d'ouvrir un classeur dont les macros sont désactivées. Dans ce cas, aucune fonctionnera, événements ou pas. Vous ne devez pas oublier cela lorsque vous créez des classeurs qui sont basés sur des procédures de gestion d’événements.

Comment écrire une procédure d'événement ?

En langage VBA, une procédure de gestion d'événement, ou procédure d'événement, est une procédure qui s'exécute en réponse à un événement. Dont acte. Elle est toujours de type Sub (et non Function). Une fois que vous en aurez assimilé le principe, l'écriture d'une procédure d'événement ne posera pas de problème particulier. La programmation se réduit en effet à ces quelques étapes que nous détaillerons d'ici peu :

1. Identifiez l'événement qui doit déclencher la procédure.
2. Appuyez sur Alt+F11 pour activer l'éditeur VBE.
3. Dans la fenêtre Projet de l'éditeur VBE, double-cliquez sur l’objet approprié listé sous l'intitulé Microsoft Excel Objects. Pour un événement lié au classeur, l’objet est ThisWorkbook. Pour un événement lié à la feuille de calcul, l’objet est un objet de type WorkSheet (comme Feuil1).
4. Dans la fenêtre Code de l'objet, écrivez la procédure d'événement qui devra être exécutée lorsque ledit événement se produira. Cette procédure aura un nom spécial qui l'identifie en tant que procédure d'événement.

Ces étapes vous paraîtrons plus claires en progressant dans cette leçon.

.

Il est très important de comprendre où le code de votre gestionnaire d'événement doit être placé. Il doit résider dans la fenêtre Code d'un module Objet. La procédure ne fonctionnerait pas si elle était placée dans un module VBA standard. Et aucun message d'erreur ne viendrait attirer votre attention là-dessus.

.

Image 1o

.

Cette image montre la fenêtre VBE avec un projet affiché dans la fenêtre Projet. remarquez que le projet est constitué de plusieurs objets

• Un objet pour chacune des feuilles du classeur (en l’occurrence les trois objets Feuil1).
• Un objet nommé ThisWorkbook.
• Un module VBA inséré manuellement avec la commande Insertion/Module

Double-cliquez sur n’importe lequel de ces objets affiche le code associé à l'élément (s'il existe).

La procédure d'événement doit être écrite dans la fenêtre Code de l'élément ThisWorkbook (si l'événement concerne le classeur) ou dans l'un des objets Feuil (si l'événement concerne une feuille de calcul ou de graphique). Dans le cas de l'image précédente, il s'agit du classeur, et c’est d'ailleurs la seule procédure qui y soit définie. À nouveau, notez les deux listes déroulantes affichées en haut de la fenêtre Code. Ce sont vos allies.

.

Lors de l'écriture d'une procédure d'événement l'éditeur VBE vous vient en aide en affichant la liste de tous les événements disponibles pour l’objet sélectionné.

Quand vous sélectionnez un événement dans la liste de droite, l'éditeur VBE crée automatiquement une procédure d'événement à votre place. C'est tout à fait pratique, puisque vous savez immédiatement quels sont les arguments éventuels à fournir.

Il n'est cependant pas obligatoire d’utiliser les deux listes déroulantes disponibles en haut de la fenêtre Code. Mais c’est une aide précieuse, car la syntaxe du nom d'une procédure d'événement est d'une importance critique. De plus, l'instruction Sub de certaines procédures d'événement exige un ou plusieurs arguments. Et rien d’autre ne viendra vous rappeler de quoi il s'agit ! Par exemple, Ici, Sh est là si vous avez sélectionné SheetActivate dans la liste des événements d'un objet Workbook, l'éditeur VBE écrira cette instruction Sub :

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Ici, Sh est l'argument passé à la procédure. C'est une variable qui représente la feuille dans le classeur ainsi activé. Les exemples de ce chapitres aiderons à éclaircir ce point.

Les événements de classeur

Les événements de classeur (objet Workbook)

Événement	Lorsqu’il est déclenché :
Workbook_Activate	Le classeur est activé.
Workbook_BeforeClose	Le classeur est fermé.
Workbook_BeforePrint	Le classeur est imprimé.
Workbook_BeforeSave	Le classeur est enregistré.
Workbook_Deactivate	Le classeur est désactivé.
Workbook_NewSheet	Une nouvelle feuille de calcul est insérée dans le classeur.
Workbook_Open	Le classeur est ouvert.
Workbook_SheetActivate	Une feuille de calcul du classeur est activée.
Workbook_SheetBeforeRightClick	Un clic du bouton droit se produit dans une cellule de la feuille de calcul.
Workbook_SheetBeforeDoubleClick	Un double clic se produit dans une cellule de la feuille de calcul.
Workbook_SheetCalculate	Une feuille de calcul du classeur est recalculée.
Workbook_SheetChange	Une cellule du classeur est modifiée.
Workbook_SheetDeactivate	Une feuille de calcul du classeur est désactivée.
Workbook_SheetFollowHyperlink	Un clic se produit sur un lien hypertexte du classeur.
Workbook_SheetSelectionChange	La sélection est modifiée.
Workbook_WindowActivate	La fenêtre du classeur est activée.
Workbook_WindowDeactivate	La fenêtre du classeur est désactivée.
Workbook_WindowResize	La fenêtre du classeur est redimensionnée.

Workbook_Open

L'un des événements les plus communément utilisés est l'événement de classeur Open. Imaginons un classeur que vous utilisez quotidiennement. Dans l'exemple qui va suivre, la procédure Workbook_Open est exécutée chaque fois que le classeur est ouvert. Elle vérifie le jour de la semaine : si c’est vendredi, le code affiche un message de rappel.

Private Sub Workbook_Open()
    Dim Msg As String
    If WeekDay (Now) = 6 then
     Msg = "Nous sommes Vendredi. "
     Msg = Msg & "Pensez à sauvegarder votre travail."
     MsgBox Msg
    End If
End Sub

La fonction WorkBook_Open est exécutée automatiquement chaque fois que le classeur est ouvert. Elle utilise la fonction WeekDay de VBA pour déterminer le jour de la semaine. Si c’est vendredi (jour 6 chez les Anglo-saxons), un message rappelle à l'utilisateur qu’il doit effectuer sa sauvegarde hebdomadaire. Les autres jours, rien ne se produit.

Image 2o

Voyons un autre exemple d'utilisation de la procédure Workbook_Open. Elle se sert des fonctions GetSetting et SaveSetting pour mémoriser le nombre de fois où le classeur a été ouvert. La fonction SaveSetting enregistre une valeur dans le Registre de Windows, tandis que GetSetting retrouve cette valeur (voyez le système d'aide pour plus d'informations à ce sujet). Le code qui suit retrouve ce décompte en consultant le Registre, l'incrémente, puis le sauvegarde à nouveau. L'information, placée dans la variable Cnt, est également affichée à des fins de contrôle.

Private Sub Workbook_Open()
    Dim Cnt As Long
    Cnt = GetSetting("MyApp", "Settings", "Open", 0)
    Cnt = Cnt + 1
    SaveSetting "MyApp", "Settings", "Open", Cnt
    MsgBox "Ce classeur a été ouvert " & Cnt & " fois."
End Sub

Workbook_BeforeClose

Voyons maintenant la procédure d'événement BeforeClose. Exécutée juste avant que le classeur se ferme, elle est localisée dans la fenêtre code de l’objet ThisWorkbook. Par exemple :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Msg As string
    Dim Réponse As integer
    Dim FName As String
    Msg = "Désirez-vous sauvegarder ce fichier ?"
    Réponse = MsgBox(Msg vbYesNo)
    If Réponse = vbYes Then
     FName = "F\SAUVEGARDES\" & ThisWorkbook.Name
     ThisWorkbook.SaveCopyAs NomFichier
    End If
End Sub

Cette routine affiche une boîte de message demandant à l'utilisateur s'il désire effectuer une copie de sauvegarde du classeur. S'il clique sur le bouton Oui, le code utilise la méthode SaveCopyAs pour enregistrer le fichier sur le lecteur F (le lecteur te le chemin devraient bien sûr être adaptés à votre propre configuration).

Les programmeurs utilisent souvent la procédure Workbook_BeforeClose pour faire le ménage. Par exemple, après avoir modifié une option du classeur (comme masquer la barre d'état, par exemple), il est approprié de la rétablir au moment de quitter le classeur. Ce ménage électronique est typiquement une tâche à confier à la procédure Workbook_BeforeClose.

Cet événement présente tout de même un inconvénient. Si vous refermez Excel et qu'un fichier ouvert a été modifié depuis la dernière sauvegarde, l’application vous demandera comme d'habitude si vous voulez enregistrer les changements opérés. le fait de cliquer sur le bouton Annuler clôt le processus de fermeture d'Excel. Mais la procédure Workbook_BeforeClose aura tout de même été exécutée.

Workbook_BeforeSave

L'événement BeforeSave est déclenché avant l'enregistreur d'un classeur. Il se produit lorsque vous utilisez la commande Fichier/Enregistrer ou Fichier/Enregistrer sous.

Placée dans la fenêtre Code d'un objet ThisWorkbook, la procédure suivante démontre la fonctionnement de cet événement. La routine met à jour la valeur de la cellule A1 de Feuil1 chaque fois que le classeur est enregistré. En d'autres termes, la cellule A1 sert de compteur indiquant le nombre de fois que le fichier a été sauvegardé.

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
    Sheets("Feuil1").Range("A1").Value =_
     Sheets("Feuil1").Range("A1").Value + 1
End Sub

Notez que la procédure Workbook_BeforeSave a deux arguments : SaveAsUI et Cancel. Pour comprendre leur fonctionnement, examinez la macro suivante, qui est exécutée avant l'enregistreur du classeur. Elle essaie d'empêcher l'utilisateur de sauvegarder le classeur sous un autre nom. Si celui-ci choisit la commande Fichier/Enregistrer sous, l'argument SaveAsUI est True (vrai).

Lorsque le code est exécuté, il vérifie la valeur de SaveAsUI. Si sa valeur renvoie True, la procédure affiche un message et met Cancel également sur True, ce qui annule l'opérateur de sauvegarde.

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
    If SaveAsUI Then
      MsgBox "vous ne pouvez pas enregistrer de copie de ce classeur !")
      Cancel = True
    End If
End Sub

En fait, cette procédure n'empêche pas réellement quelqu’un d'enregistrer le classeur sous un nom différent. Il suffit d'ouvrir le classeur avec ses macros désactivées, et le tour est joué. En effet, dans ce cas, toutes les procédures de gestion d’événements sont elles aussi désactivées. Ce qui est parfaitement logique, puisque ce sont aussi des macros...

Les événements de feuille

.

Les événements de feuille (objet Worksheet)

Evénement	Lorsqu’il est déclenché :
Worksheet_Activate	La feuille est activée.
Worksheet_SheetActivate	Une feuille de calcul du classeur est activée.
Worksheet_BeforeRightClick	Un clic du bouton droit se produit dans la feuille de calcul.
Worksheet_BeforeDoubleClick	Un double clic se produit dans la feuille de calcul.
Worksheet_Deactivate	Une feuille de calcul est désactivée.
Worksheet_Calculate	La feuille de calcul est recalculée.
Worksheet_Change	Une cellule de la feuille de calcul est modifiée.
Worksheet_SelectionChange	La sélection est modifiée.

Worksheet_BeforeDoubleClick

Il est possible de définir une procédure VBA qui soit exécutée lorsque l'utilisateur double-clique dans une cellule. Dans l'exemple suivant, stocké dans la fenêtre Code d'un objet Feuil, double-cliquer dans une cellule met son contenu en gras s'il est en caractères maigres, et inversement.

La procédure WorkSheet_BeforeDoubleClick a deux arguments : Target et Cancel. Target est la cellule (un objet Range) qui est double-cliquée. Si Cancel est sur True, l'action par défaut du double clic ne se produit pas.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    Target.Font.Bold = Not Target.Font.Bold
    Cancel = True
End Sub

Notez que Cancel est défini avec la valeur True. Cela empêche l'action par défaut (activer le mode édition de cellule d'Excel) de se produire.

Worksheet_Activate

Excel peut détecter si une feuille est activée ou désactivée et exécuter une macro lorsque l'un ou l'autre de ces événements se produit. Ces procédures d'événement doivent être placées dans la fenêtre Code de l’objet Feuil. Pour accéder rapidement à la fenêtre de code d'une feuille, cliquez droit sur l'onglet de celle-ci et choisissez la commande Visualiser le code. L'exemple qui suit montre une procédure simple qui est exécutée chaque fois qu'un feuille donnée est activée. Elle ouvre une boîte de message qui affiche le nom de la feuille active :

Private Sub Worksheet_Activate()
    MsgBox "Vous venez d'activer la feuille "& ActiveSheet.Name
En Sub

Voici un autre exemple qui rend la cellule A1 courante chaque fois qu'une feuille est activée :

Private Sub Worksheet_Activate()
    Range("A1").Activate
End Sub

Ces exemples sont très élémentaires, mais une procédure d'événement peut être beaucoup plus complexe. La procédure qui suit - stockée dans la fenêtre Code de l’objet Feuil1 - utilise l'événement Deactivate pour empêcher l'utilisateur d'activer toute autre feuille du classeur. Lorsque Feuil1 est désactivée (c'est-à-dire qu'une autre feuille est activée), un message est affiché puis Feuil1 est de nouveau activé :

Private Sub Worksheet_Activate()
    MsgBox "Vous devez rester dans Feuil1."
    Sheets("Feuil1").Activate
End Sub

Pour autant je ne vous conseille pas d’utiliser ce genre de procédure pour essayer de court-circuiter Excel. Cela pourrait être très frustrant et source de confusion pour l'utilisateur d'une part, et d’autre part facile à contourner en désactivant les macros. Il vaut mieux profiter de ces possibilités pour aider vos utilisateurs à se servir correctement de votre application.

Worksheet_Change

Un événement Change se produit chaque fois qu'un cellule de la feuille de calcul est modifiée. Dans l'exemple qui suit, la procédure Worksheet_Change empêche effectivement un utilisateur d'entrer une valeur non numérique dans la cellule A1. Ce listing est stocké dans la fenêtre Code de l’objet Feuil.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
      If Not IsNumeric(Target) Then
         MsgBox "Entrez un nombre dans la cellule A1."
         Range("A1").ClearContents
         Range("A1").Activate
      End If
    End If
End Sub

L'unique argument de la procédure WorkSheet_Change (Target) représente la plage qui a été modifiée. La première instruction vérifie si l'adresse de la cellule est bien $A$1. Si oui, le code utilise la fonction IsNumeric pour déterminer si elle contient une valeur numérique. Si ce n’est pas le cas, un message apparaît et la valeur de la cellule est effacée. La cellule A1 est ensuite réactivée, ce qui est commode lorsque le pointeur de la cellule s'est déplacé après la saisie. Si une cellule autre que A1 est modifiée, il ne se passe rien.

Pourquoi ne pas utiliser la commande Validation ?

La commande Donnée/Outils de données/Validation des données vous est peut-être familière. C'est une fonction très commode qui permet de s'assurer facilement que les données entrées dans une plage sont bien du type requis. Elle ne met toutefois pas à l'abri de toutes les fausses manœuvres. Pour vous en convaincre, ouvrez une feuille de calcul vierge puis effectuez les étapes suivantes :

1. Sélectionnez la plage A1:C12
2. Ouvrez l'onglet Données, puis cliquez sur le bouton Validation des données dans le groupe Outils des données.
3. Configurez les critères de validation de manière à n'accepter que des nombres entiers entre 1 et 12, comme le montre l'image qui suit

.

Image 3o

.

Entrez maintenant des valeurs dans la plage A1:C12. La validation des données fonctionne comme prévu. Mais, pour voir ce qui ne va pas, essayez ceci :

1. Entrez -1 dans n’importe quelle cellule en dehors de la plage de validation.
2. Appuyez sur Ctrl+C afin de placer le nombre négatif dans le Presse papiers.
3. Sélectionnez une cellule dans la plage de validation.
4. Appuyez sur Ctrl+V.

Vous constatez que le collage est autorisé. Examinez la situation de plus près et vous remarquerez que la cellule dans laquelle vous avez collé la valeur négative n'a plus de critère de validation. Le collage l'a effacé !

Le collage efface les données de validation, car, pour Excel, la validation est la même chose qu'une mise en forme de cellule (ou, pour être plus précis, qu'une mise en forme conditionnelle). Elle est considérée au même titre que la police, la couleur au autre attribut. Quand vous collez une cellule, vous remplacez la mise en forme de la cellule de destination par celle de la cellule source. Il en va malheureusement de même pour les critères de validation.

Annexes

Bibliographie

• John Walkenbach, 2013, Programmation VBA pour Excel Pour Les Nuls, FIRST

Liens internes

• Leçon VISUAL BASIC
• Leçon Tableur EXCEL

Liens externes

• FAQ developpez VBA
• Une introduction au Visual Basic

Débogage

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Erreur

Toutes les macros, comme tous les codes informatiques, peuvent comporter des erreurs, on pourrait même affirmer : comportent des erreurs. Parmi ces erreurs, on peut trouver différentes causes et effets :

• Compilation : l'erreur de compilation (ou de syntaxe) survient lorsque VBA rencontre une instruction (ou une donnée) qu’il ne connait pas (par exemple une faute d'orthographe ou de ponctuation sur un mot clé, …). Dans ce cas VBA affiche un message d'erreur avant l'exécution directement sur la ligne comprenant l'erreur de syntaxe, c’est donc l'erreur la plus fréquente et la plus aisée à déceler et réparer.
• Exécution : l'erreur d'exécution survient lorsque VBA rencontre une instruction interdite (par exemple lancer une impression vers une imprimante qui n'existe pas). Dans ce cas VBA affiche un message d'erreur pendant l'exécution, c’est une erreur assez fréquente et assez aisée à déceler et réparer, par exemple : une division par zéro, une valeur recherchée au-delà d'une limite d'un tableau, …
• Logique : l'erreur de logique survient lorsque le programmeur constate un résultat inattendu suite à une série d'instructions qu’il a programmé (par exemple le fait de ne pas mettre en gras les bonnes cellules). La macro s'exécute mais ne réalise pas ce qu'elle est censée faire. C'est l'erreur la moins fréquente et la moins aisée à déceler et réparer, par exemple : un test si erroné, une boucle de trop dans un calcul, …

Exemple d'erreur d'exécution : erreur de débordement

On désire créer une macro qui supprime certaines lignes Excel en rencontrant la valeur "fauteuil" sur cette ligne, soit les données suivantes de la feuille : cellule A1 = "chaise", A2 = "fauteuil", A3 = "fauteuil", A4 = "tabouret"... L'algorithme suivant qui semble juste à première vue est cependant faux :

Pour i de 1 à 3
 Si A(i)= "fauteuil" Alors SupprimerLigne(i)
Suivant

Début de l'exemple

Exemple

 For i = 1 to 4
  If Range("A" & i).Value = "fauteuil" Then Rows(i).Delete
 Next i

Fin de l'exemple

POURQUOI : dès le second tour de boucle, la ligne 2 (avec "fauteuil") sera supprimée ce qui entrainera le passage de la ligne 3 (avec encore "fauteuil") en ligne 2, la ligne 2 étant déjà traitée par le programme ce nouveau "fauteuil" ne sera pas supprimé

SOLUTION : inverser le sens de parcours de la boucle en partant de la dernière ligne

Début de l'exemple

Exemple

 For i = 4 to 1 Step -1
  If Range("A" & i).Value = "fauteuil" Then Rows(i).Delete
 Next i

Fin de l'exemple

Exemple d'erreur de logique : boucle sans fin

On désire créer une boucle qui tourne tant qu'une condition de sortie n’est pas atteinte, ici la rencontre du libellé "fauteuil" sur une ligne, soit les données suivantes de la feuille (1000 lignes renseignées) : cellule A1 = "chaise", A2 = "tabouret", A3 = "tabouret", A4 = "tabouret"... L'algorithme suivant qui semble juste à première vue est cependant faux :

Tant que la ligne lue est différente de "fauteuil"
 Afficher "je n'ai pas encore trouvé un fauteuil"
 Passer à la ligne suivante
Fin Tant que
Afficher "j'ai trouvé un fauteuil"

Début de l'exemple

Exemple

 i = 1
 While (Range("A" & i).Value <> "fauteuil")
  MsgBox "je n'ai pas encore trouvé un fauteuil"
  i = i + 1
 Wend
 MsgBox "j'ai trouvé un fauteuil"

Fin de l'exemple

POURQUOI : le mot "fauteuil" n'existant pas dans la feuille Excel, la boucle deviendra infinie et visitera toutes les lignes du classeur (plusieurs millions). Une telle boucle est très souvent considérée comme un problème car elle sollicite trop de ressources ordinateur.

SOLUTION : ajouter une condition d'arrêt obligatoire, ici dès que le programme aura parcouru plus de 1000 lignes Excel

Début de l'exemple

Exemple

 i = 1
 While (Range("A" & i).Value <> "fauteuil") & (i < 1000)
  MsgBox "je n'ai pas encore trouvé un fauteuil"
  i = i + 1
 Wend
 If i <> 1000 Then MsgBox "j'ai trouvé un fauteuil"

Fin de l'exemple

Erreur d'encodage

Si une importation vers une base de données remplace tous les caractères non ANSI (ex : "é"), il faut convertir le fichier de PC ANSI en PC DOS.

Types d'erreurs

content_types.xml

Il s'agit d'une erreur apparaissant après un publipostage Word : résolue en désinstallant un Office 2003 qui était à côté d'un 2007.

Erreur d'exécution '7': Mémoire insuffisante

Une variable a dépassé la valeur prévue (listée dans le chapitre Les types en VB). Il convient donc de changer son type (par exemple passer d'Integer à Long), ou bien de diminuer sa valeur.

Erreur d'exécution '9': L'indice n'appartient pas à la sélection

Cela se produit par exemple :

• Quand on appelle une feuille qui n'existe pas (Worksheets("MaFeuille")), on peut donc rendre le traitement plus générique avec des numéros (Worksheets(1)).
• Quand on appelle un tableau avec un indice qui le dépasse, il faut donc le redimensionner :

 ReDim MonTableau(5)
 MsgBox IsEmpty(MonTableau(4))

Erreur d'exécution '28': Espace pile insuffisant

Peut survenir en cas de boucle infinie ou de récursion infinie.

Erreur d'exécution '70': Accès refusé

Un objet de formulaire possède une propriété ControlSource non vide, donc on ne peut pas le modifier en appelant sa méthode .AddItem.

Erreur d'exécution '70': Permission refusée

Un fichier ouvert est peut-être en train d’être rouvert.

Erreur d'exécution '75': Erreur d'accès Chemin/Fichier

Un fichier ouvert ne peut pas être supprimé avant sa fermeture.

Erreur d'exécution '91': Variable objet ou variable de bloc With non définie

Il se peut qu'une fonction soit appliquée à une variable vide. Ex :

 set searched = Nothing
 MsgBox searched   ' Erreur
 x = Len(searched) ' Erreur

Il faut donc lever l'exception.

Erreur d'exécution '424': Objet requis

Une méthode est appliquée sur un objet déclaré qui n'en dispose pas.

1. Est-il bien initialisé ?
2. Si oui, l'explorateur d'objets permet de vérifier l’existence de la méthode.
3. Si la méthode existe, c’est que l’objet est mal initialisé. Par exemple à cause de plusieurs déclarations sur la ligne du New :

' Pas bien :
Dim Command, Command2 As New ADODB.Command

' Bien :
Dim Command As New ADODB.Command
Dim Command2 As New ADODB.Command

Erreur d'exécution '462': Le serveur distant n'existe pas ou n’est pas défini

Il faut libérer la ressource avant sa deuxième exécution.

Erreur d'exécution '450': Nombre d'arguments incorrect ou affectation de propriété incorrecte

Se produit quand on tente d'affecter un objet à une variable dont le type est incompatible.

Erreur d'exécution '1004': Erreur définie par l’application ou par l’objet

Si cela se produit lors de la fermeture du classeur, il faut modifier l'évènement.

Sinon, regarder si le programme n'appelle pas une cellule Excel avec des coordonnées non entières (ex : B3,33).

Enfin, il faut savoir que le VBA ne peut pas ajouter de formule Excel en français. Celles en anglais fonctionnent avec une syntaxe un peu différente pour qu’elles soient interprétées pendant son exécution :

1. Positions absolues avec R pour row et C pour column, et pour les relatives entre crochets (ex : C[0] = C = colonne courante).
2. Virgule au lieu de point-virgule.
3. Caractère d'échappement pour les guillemets.

Par exemple :

 ' Affiche la valeur correspondant à la date d'une table liée
 Range("C7").Value = "=SUMIFS(Feuil2!C1,Feuil2!C3,TEXT(R5C[-1], ""aaaammjj""),Feuil2!C2, RC1)"

Affichera dans Excel :

=SOMME.SI.ENS(Feuil2!$A:$A;Feuil2!$C:$C;TEXTE(B$5; "aaaammjj")Feuil2!$B:$B; $A7)

Remarque : ne pas ajouter d'addition dans ces formules (R[5]C[-1+2]).

Erreur d'exécution '1004': La méthode 'Add' de l'objet 'Workbooks' a échoué

Si on ne peut plus créer de classeur Excel sans VBA, Excel n’a plus assez de ressources : redémarrer la machine.

Erreur d'exécution '1004': La méthode de la classe Range a échoué

Le type d'un argument placé dans Range() n'est pas une cellule.

Erreur d'exécution '3421': L'application utilise une valeur d'un type incorrect pour l'opération en cours

Une variable a dépassé la valeur prévue. Il convient donc de modifier sa valeur, gérer une exception, ou bien de changer son type (par exemple passer de Command.CreateParameter("@Variable1", adInteger, adParamInput) ou Command.CreateParameter("@Variable1", adVarChar, adParamInput, 4), à Command.CreateParameter("@Variable1", adVarChar, adParamInput, 10)).

Erreur d'exécution '3704': Cette opération n’est pas autorisée si l’objet est fermé

Apparait quand on cherche à accéder à un objet ADODB.Recordset vide, par exemple avec ResultSet.BOF.

Erreur d'exécution '-2147217900': La procédure ou fonction 'xxx' attend le paramètre 'xxx', qui n'a pas été fourni

Ce message peut survenir alors que le paramètre d'une procédure stockée est parfaitement bien renseigné ! On peut le vérifier avec :

 MsgBox Command.Parameters.Item(0).Name & " = " & Command.Parameters.Item(0).Value

Il faut juste éviter d'utiliser les ADODB.Parameter :

 Command.CommandText = "MaProcédureStockée @Param1=" & Valeur1 & ", @Param2=" & Valeur2

Erreur de compilation: Argument non facultatif

L'affectation en erreur doit être précédée de Set.

Erreur de compilation: Impossible d'affecter à un tableau

Le compilateur a trouvé une affectation à un tableau, il faut donc la remplacer. Ex :

 Dim Tableau(2)
 'Tableau = 1
 Tableau(0) = 1

Erreur de compilation: Procédure trop grande

Les Sub et Function sont limitées à 64 ko (environ 2 000 lignes, sans les commentaires, ni tabulations), en .xls et .xlsm. Il faut donc par exemple :

• Faire des appels (Call) à d'autres fonctions contenant des traitements séparés.
• S'orienter vers la pseudo programmation objet VBA (variable Collection).
• Remplacer les conditions récurrentes par un test de booléen.

Impossible de définir la propriété ControlSource. Valeur de propriété non valide.

Le champ ControlSource de la fenêtre Propriétés d'un formulaire pointe vers une feuille inexistante.

Impossible de définir un type Public défini par l'utilisateur à l'intérieur d'un module objet

Ajouter la visibilité Private avant la variable concernée.

Sinon, dans le module de classe au paramètre "Instancing" (à côté de son nom), choisir dans le menu déroulant "Private" au lieu de "Public".

Mode d'accès au fichier incorrect

Remplacer Input par Output ou Append lors de l'ouverture de fichier. Exemple sur la première ligne :

 Open "C:\Fichier1.csv" For Output As #1
 Print #1, Ligne1
 Close #1

Nom ambigu détecté

Une variable ou fonction de même nom et de même portée a été déclarée deux fois. Attention : ce n’est pas forcément celle sélectionnée par le message d'erreur.

Projet ou bibliothèque introuvable

Si cela porte sur des fonctions comme trim, mid, left et right, alors décocher la bibliothèque manquante dans l'éditeur de code, Outils, Références.

Sinon, une DLL est probablement à cocher dedans.

Type d'argument Byref incompatible

Il faut convertir la variable en cause, par exemple avec Cstr() ou Cint().

Type défini par l'utilisateur non défini

Il faut ajouter une bibliothèque contenant ce type (en cochant une case dans Outils\Références). Attention : si le programme ne coche pas cette case automatiquement chaque utilisateur de chaque ordinateur devra la cocher manuellement.

Une erreur s'est produite : External table is not in the expected format

Lors d'un publipostage VBA, la source de données n’est pas prise en compte par Word. Essayer de le faire manuellement pour en savoir plus. Si le message persiste, changer le format de la source (ex : Fichier.SaveAs test.xls, FileFormat:=xlNormal).

Variable non définie

La mention Option Explicit impose de déclarer chaque variable, on peut donc soit l'enlever, soit tout déclarer avec Dim.

Variable requise. Impossible de l'affecter à cette expression

Peut survenir quand on appelle len(Tableau) au lieu de UBound(Tableau).

Si en cas de division par zéro le compilateur renvoie bien cette erreur, en cas de modulo zéro il renvoie le premier nombre.

Quand on effectue des opérations mathématiques avec des chaines de caractères, VBA ne les convertit pas toujours automatiquement, il est donc plus sûr d’utiliser les fonctions de conversions.

Gestion d'exception par VBA

La VBA ne possède pas de levée d'exception proprement dite, il faut donc désactiver les messages d'erreur bloquants puis tester si l'opération à risque s'est bien déroulée :

On Error Resume Next ' Désactivation des erreurs
x = 1
On Error GoTo 0 ' Réactivation des erreurs
If IsNull(x) Or IsEmpty(x) Then Exit Sub

Débogage

Introduction

Toute correction de programme, de logiciel, ou d'application suite à une anomalie de fonctionnement s’appelle débogage. Dans notre cas, il s'agira donc de la correction d'une erreur liée à une Macro VBA. L'outil débogueur aide à corriger une anomalie empêchant le fonctionnement de la macro, son objectif est donc de cerner l'instruction en erreur ou responsable de l'erreur. Pour ce faire, l'outil utilise quelques modes de fonctionnement :

• Mode pas à pas => sur des portions courtes de code, l'assistant erreur propose le passage en mode pas à pas, si le programmeur choisit ce mode, le programme alors s'arrête sur chaque instruction pour permettre de repérer la cause de l'erreur grâce à de nombreuses options d'affichage. Ce mode "pas à pas" peut aussi être directement demandé par le concepteur dès le début de la procédure (touche F8).
• Point d'arrêt : pour éviter le fastidieux mode "pas à pas" sur des portions longues de code, il est possible de marquer par des points d'arrêt les lignes d'instruction sur lesquelles le programme doit s'arrêter, le programme alors s'arrête sur chaque arrêt positionné par le programmeur.

Début de l'exemple

Exemple

Lorsque que l’on crée une macro VBA, il est tout à fait possible d’avoir une erreur et que s'affiche ceci:

debogage vba

On observe que VBA nous précise l'erreur en affichant directement l'erreur de syntaxe. Ici on peut voir que l'erreur vient de la ligne surlignée en bleu et plus précisément, après analyse, du "non".

Fin de l'exemple

Outils de débogage

Il existe différentes fonctions permettant le débogage de macros créées par l'utilisateur. Ces outils permettent de vérifier le bon fonctionnement des macros une fois celles-ci créées. Nous avons par exemple, le message de syntaxe, le message d'exécution, l'instruction stop, le point d'arrêt, l'afficheur d’état d'une variable, la fenêtre variable locale, la fenêtre espion, la fenêtre exécution, ...

• CTRL + Pause envoie une interruption qui met en pause le programme en cours d'exécution (un peu comme CTRL + ALT + Suppr dans Windows).
• F8 lance le mode pas à pas (ligne par ligne).
• F5 lance le programme jusqu'à fin de l'exécution, ou ce qu’il rencontre un point d'arrêt.
• F9 définit un point d'arrêt, équivalent d'un clic gauche dans la marge.

Pendant le débogage, en sélectionnant une variable sur clic droit, il est possible d'y ajouter un espion. Cela permet d'afficher sa valeur variable dans une fenêtre en bas, et même de suspendre l'exécution si cette valeur change. Il est aussi possible de connaitre ces valeurs en passant la souris sur les variables pendant l'exécution.

Message d'erreur d'exécution

Lors de l’affichage du message d'exécution, VBA affiche une boite de dialogue à 2 boutons (Fin ou Débogage)

• Le bouton Fin fait en sorte de quitter la macro en erreur
• Le bouton Débogage met en surbrillance la ligne de la macro qui a décelé l'erreur.

Instruction Stop

Il suffit de l'insérer dans le code d'une macro. Lorsque de l'exécution de la macro, celle-ci s'arrêtera à l'endroit où le "Stop" est placé. Le but ici est de vérifier que la portion de code précédent le stop ne contient aucune erreur.

Début de l'exemple

Exemple

• Première étape :

Il suffit tout simplement d'écrire le mot "Stop" à l'endroit où l’on désire que la macro s'arrête lors de son exécution. Sur cette image on peut donc voir que la macro c’est bien exécutée jusqu'à l'instruction Stop

• Seconde étape :

Il lui restait 2 macros à exécuter, elle n'a pas été au bout à cause du "Stop". Tout ce qui a été écrit avant le "Stop" s'est exécuté parfaitement (coloriage du fond de la cellule sélectionnée en bleu et de la police en rouge).

Fin de l'exemple

Point d'arrêt

Le but du point d'arrêt est identique au stop, le but premier étant d'interrompre l'exécution de la macro et donc de vérifier si son exécution est correcte

Début de l'exemple

Exemple

• Première étape :

Pour insérer un point d'arrêt il suffit de faire un clic gauche dans la case grisée, la ligne correspondante dans le code apparaitra en rouge. La macro arrêtera de s'exécuter à partir de ce point permettant de vérifier que les instructions précédentes ne comprennent pas d'erreurs

• Seconde étape :

Tout ce qui a été écrit avant le "point d'arrêt " s'est exécuté parfaitement (coloriage en bleu du fond de la cellule sélectionnée et de la police en rouge), mais n'a pas mis en fond marron les recettes associées à la vente du produit "Fauteuil".

Fin de l'exemple

L'affichage de l'état de variable

L'état de variable permet de vérifier la valeur que prend chaque variable pendant l'exécution, extrêmement utile lorsque la découverte de l'erreur n’est pas triviale

Début de l'exemple

Exemple

• Étape 1 :

Il faut donc bloquer la ligne contenant l'opération de variable avec un point d'arrêt.

• Étape 2 :

Aller dans Affichage, puis Fenêtre Variables Locales. Une barre de dialogue apparait plus bas. Avec en titre de colonne "Expression", "Valeur", "Type".

• Étape 3 :

Nous pouvons donc voir les différentes valeurs contenues dans l'état de variable :

• • la valeur 10 a été saisie dans la boite de dialogue
  • la variable i contient la valeur à 6.55957
  • le type de variable correspond bien à Single défini précédemment.

Fin de l'exemple

Références

• Greg Harvey et John Walkenbach, 2013, Excel 2013 et VBA Pour Les Nuls, FIRST, Hoboken

Liens internes

• Leçon Tableur EXCEL

Liens externes

• Gérez vos erreurs
• Corrigez vos bogues
• Démonstration vidéo de debug
• Optimisation de code VBA
• Télécharger MZTools, utilitaire de débogage Visual Basic (ajout des n° de ligne, détection des variables inutilisées...)

Créer des macros complémentaires

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Introduction à la leçon : Macros Complémentaires

L'une des plus astucieuses fonctionnalités d'Excel - à mon avis, du moins - est la possibilité de créer des macros complémentaires, appelées aussi "compléments". Vous comprendrez leur intérêt dans ce chapitre, et vous apprendrez à les créer en utilisant les outils intégrés à Excel.

Qu'est-ce qu'une macro complémentaire ?

Définition

Une macro complémentaire est une sorte d'extension qui augmente les fonctionnalités d'Excel. Certaines fournissent de nouvelles fonctions de feuille de calcul utilisables dans les formules, d'autres de nouvelles commandes ou des utilitaires. Si la macro complémentaire a été conçue dans les règles de l'art, la nouvelle fonctionnalité s'intègre parfaitement dans l'interface d'Excel, comme si elle en avait toujours fait partie.

Excel est livré avec plusieurs macros complémentaires. Les plus connues sont l'Utilitaire d'analyse, l'Assistant Somme conditionnelle et le Complément Solveur. Vous pouvez en obtenir d'autres auprès de programmeurs et d'éditeurs tiers. Mon Power Utility Pak en est un (très bon...) exemple.

Tout utilisateur un tant soit peu averti peut créer des macros complémentaires, à condition bien sûr de savoir programmer en VBA. Une macro complémentaire d'Excel est fondamentalement une forme différente de fichier de classeur XLSM. Plus spécifiquement, une macro complémentaire est un classeur XLSM normal, à quelques différences près tout de même :

• la propriété IsAddin de l’objet Workbook est True.
• la fenêtre du classeur est masquée et ne peut pas être affichée avec les commandes d'Excel.
• Le classeur n’est pas un membre de la collection Workbook, mais de la collection AddIns.

Tout fichier XLSX peut être converti en macro complémentaire, mais tous ne sont pas de bons candidats. Comme les compléments sont toujours masqués, vous ne pouvez pas afficher leurs feuilles de calcul ou de graphique. Il est toutefois possible d'accéder aux procédures Sub et Function et d'afficher les boîtes de dialogue contenues dans les objets UserForm. Les fichiers des macros complémentaires d'Excel ont habituellement une extension XLAM (XLA avant Excel 2007 qui les distingue des classeurs XLSM).

Pourquoi créer des macros complémentaires ?

Vous pouvez décider de convertir une application Excel en macro complémentaire pour l'une des raisons suivantes :

• Rendre l'accès au code plus difficile : quand une application est distribuée sous la forme d'une macro complémentaire (et que vous protégez son projet VBA), l'utilisateur lambda ne peut pas voir ses feuilles dans le classeur. Si vous l'avez programmé avec des techniques bien à vous, il sera plus difficile à autrui de copier votre code. Les fonctions de protection d'Excel ne sont pas parfaites et il existe des utilisateurs conçus pour casser le mot de passe.
• Eviter la confusion : quand une application est chargée en tant que macro complémentaire, le fichier est invisible, ce qui évite à l'utilisateur novice de s'y perdre et de faire n’importe quoi. Contrairement à un classeur masqué, une macro complémentaire ne peut pas être révélée.
• Simplifier l'accès aux fonctions de la feuille de calcul : les fonctions de la feuille de calcul personnalisées stockées dans une macro complémentaire n'ont pas besoin d’être qualifiées par un nom de feuille. Par exemple, si vous stockez une fonction personnalisée nommée DEFMOY dans un classeur nommé NouvFonc.xlsm, vous devez utiliser la syntaxe suivante lorsque cette fonction est utilisée dans un autre classeur : =NOUVFONC.XLS!DEFMOY (A1:A50). En revanche, si la fonction est située dans un fichier de macro complémentaire ouvert, vous utiliserez une syntaxe plus simple, car vous n'avez plus à faire référence au fichier : =DEFMOY (A1:A50)
• Faciliter l'accès aux utilisateurs : une fois identifié l'emplacement de la macro complémentaire, celle-ci apparaît dans la boite de dialogue Macro complémentaire, avec un nom évocateur et une brève description de son usage. L'utilisateur peut facilement activer ou désactiver votre complément.
• Mieux contrôler le chargement : les macros complémentaires peuvent être ouvertes automatiquement au démarrage d'Excel, peu importe le dossier où elles sont stockées.
• Eviter l’affichage d'une invite au déchargement : quand une macro complémentaire est refermée, l'utilisateur ne voit jamais apparaître l'invite Enregistrer sous.

Travailler avec des macros complémentaires

Le moyen le plus simple et le plus efficace de charger et décharger les macros complémentaires consiste à suivre les instructions suivantes :

1. Fichier → Options → Compléments
   
2. Compléments Excel (qui se trouve en bas de la fenêtre Options Excel) → cliquer sur le bouton Atteindre
   

N.B. : on peut directement passer par l'onglet Développeur et cliquer sur le bouton Compléments

Une fois ces actions réalisées, la boîte de dialogue ci-dessous (figure 1) s'affiche :

Boîte de dialogue contenant toutes les macros complémentaires reconnues par Excel.

Elle recense toutes les macros complémentaires reconnues par Excel. Afin d'ouvrir ou fermer une macro complémentaire, il suffit seulement de les cocher ou décocher, directement depuis cette boîte de dialogue. De manière générale, lorsque vous allez ouvrir une macro complémentaire, le ruban rouge change quelque peu :

• Excel affiche un nouvel onglet
• Excel ajoute des commandes à un onglet existant

Par exemple, lors de l'ouverture de l'Utilitaire d'analyse, un nouvel élément Utilitaire d'analyse s'ajoutera à l'onglet Données. Cependant, il se peut également qu’il ne se passe rien, c’est notamment le cas lorsque la macro complémentaire ne contient que des fonctions personnalisées de feuille de calcul. En effet, ces dernières apparaîtront dans la boite de dialogue Insérer une fonction et il n'y aura donc aucun changement visible directement depuis l'interface utilisateur.

Notez qu’il existe un autre moyen pour utiliser les macros complémentaires, grâce à la commande Ouvrir. Cependant, en passant par celle-ci, vous ne les retrouverez pas dans la boîte de dialogue Macro complémentaire et il ne sera pas possible de les quitter avec la commande Fermer. En effet, la macro complémentaire ne pourra être supprimée qu'en quittant ou redémarrant Excel ou encore, en utilisant une macro qui aura pour but de fermer cette macro complémentaire.

Les bases des macros complémentaires

Par déduction, afin de créer des macros complémentaires il faut utiliser un classeur contenant des macros. En effet, bien que l’on puisse convertir n’importe quelle feuille de calcul en macro complémentaire, tous les classeurs ne peuvent pas en bénéficier. Ceux dont les feuilles ne contiennent pas de macros n'ont aucun intérêt puisque les macros complémentaire sont masquées. Du coup, l'idéal est d’utiliser un classeur avec des macros à usage général telles que les procédures Sub et Function. À partir de là, créer une macro complémentaire est assez simple, il suffit de suivre la procédure suivante :

1. Développez votre application et assurez-vous que tout fonctionne correctement. Attention ! N'oubliez pas d'inclure une méthode d'exécution des macros (ajout de nouvelles commandes dans le ruban ou création raccourci). Cependant, il est inutile d'inclure une méthode d'exécution si le complément ne contient que des fonctions. En effet, elles apparaîtront dans la boîte de dialogue Insérer une fonction.
2. Testez l’application en l'exécutant quand un classeur différent est actif. Cela permet de simuler le comportement de l’application lorsqu'elle est utilisée comme macro complémentaire. En effet, une telle macro n'est jamais le classeur actif.
3. Activez l'éditeur VBE → sélectionner le classeur (fenêtre Projet) → Outils/Propriétés de VBAProject → onglet Protection → Cochez "Verrouiller" le projet → Entrez un mot de passe → OK. Cette étape a pour objectif d'empêcher les autres utilisateurs de visualiser ou modifier vos macros et vos objets UserForm. Elle n’est pas obligatoire.
4. Dans Excel : ouvrir l'onglet Développeur → Panneau de document → OK. Le panneau apparaît sous le ruban.
5. Choisir un nom dans le champ "Titre" ainsi qu'une description précise dans le champ Commentaires. Encore une fois, ces étapes (4 et 5) ne sont pas obligatoires. Cependant, elles permettent une meilleure utilisation de la macro complémentaire, notamment votre description puisqu'elle sera affichée dans la boîte de dialogue Macro complémentaire, que l’on a vu un peu plus haut.
6. Fichier → Enregistrez sous.
7. Boîte de dialogue → Enregistrez sous → déroulez le menu Type de fichier → choisir Macro complémentaire Microsoft Excel (*.xlam).
8. Choisissez le dossier de stockage de la macro complémentaire. Excel propose par défaut un dossier nommé AddIns, mais la sauvegarde peut se faire dans n’importe quel autre emplacement.
9. Enregistrer. Et voila ! Vous avez créé une macro complémentaire !

Un exemple de macro complémentaire

Début de l'exemple

Exemple de création d'une macro complémentaire

L'ensemble de cette partie sera consacré à notre exemple. Nous allons étudier toutes les étapes fondamentales pour la création d'une macro complémentaire utile. Pour une compréhension simple de l'utilité des macros complémentaires, nous utiliserons un cas comprenant une macro avec un UserForm proposant plusieurs options de changement de texte et nous la transformerons en macro complémentaire.

Fin de l'exemple

Configurer le classeur

Le classeur est constitué d'une feuille de calcul vierge, d'un module VBA et d'un objet UserForm.

L'objet UserForm pour la macro complémentaire Changer la case

Prenons l'exemple simple d'un UserForm avec les options suivantes :

• Majuscules : met toutes les lettres en majuscules
• Minuscules : met toutes les lettres en minuscules
• 1ère lettre en majuscules : met la première lettre en majuscule, et toutes les autres en minuscules, et ce pour chaque mot d'un texte
• Phrase : seule la première lettre du texte est mise en majuscule, tout le reste en minuscules
• Inverser la casse : met les majuscules en minuscules, et vice versa

La figure 2 ci-contre montre à quoi va ressembler notre objet UserForm1. Les cinq contrôles OptionButton sont placés à l'intérieur d'un contrôle Frame. Vous trouvez aussi, comme il se doit, un bouton Annuler(appelé BoutonAnnuler) et un bouton OK (appelé BoutonOK).

Le code exécuté lorsque l'utilisateur clique sur le bouton Annuler est très simple :

Private Sub BoutonAnnuler_Click()
    Unload UserForm1
End Sub

Tout le travail est effectué par le code suivant, qui est exécuté lorsque l'utilisateur clique sur le bouton OK :

Private Sub BoutonOK_Click()
    Dim TextCells As Range
    Dim cell As Range
    Dim text As String
    Dim i As Long
    
    'Créer un objet avec juste des constantes de texte'
    On Error Resume Next
    Set TextCells = Selection.SpecialCells(xlConstants, xlTextValues)
    
    'Désactiver la mise à jour de l'écran'
    Application.ScreenUpdating = False

    'Boucle sur les cellules'
    For Each cell In TextCells
        text = cell.Value
        Select Case True
        Case OptionLower 'minuscules
            cell.Value = LCase(cell.Value)
        Case OptionUpper 'MAJUSCULES'
            cell.Value = UCase(cell.Value)
        Case OptionProper '1ère lettre en majuscule'
            cell.Value = Application.WorksheetFunction.Proper(cell.Value)
        Case OptionSentence 'Phrase'
            text = UCase(Left(cell.Value, 1))
            text = text & LCase(Mid(cell.Value, 2, Len(cell.Value)))
            cell.Value = text
        Case OptionToggle 'inverser la casse'
            For i = 1 To Len(text)
                If Mid(text, i, 1) Like "[A-Z]" Then
                    Mid(text, i, 1) = LCase(Mid(text, i, 1))
                Else
                    Mid(text, i, 1) = UCase(Mid(text, i, 1))
                End If
            Next i
            cell.Value = text
        End Select
    Next
    
    'Décharge la boîte de dialogue'
    Unload UserForm1

End Sub

Tester le classeur

Tester la macro complémentaire avant de convertir ce classeur. Pour simuler ce qui se produira lorsque le classeur sera devenu une macro complémentaire, vous devez le tester alors qu'un autre classeur est actif. Rappelez vous qu'une macro complémentaire n'est jamais le classeur actif. C'est donc uniquement en la testant depuis un autre classeur que vous pourrez détecter d'éventuelles erreurs.

1. Ouvrez un nouveau classeur et entrez des données dans ces cellules. Pour ce test, entrez divers types d'information : du texte bien sûr, mais aussi des formules ou des valeurs. Ou alors, ouvrez un classeur existant et utilisez-le pour vos essais. En fait, il vaut mieux partir dans ce cas d'une copie de classeur, car les modifications dues à la macro complémentaire ne pourront pas être annulées.
2. Sélectionnez une ou plusieurs cellules (voire des lignes ou des colonnes entières).
3. Exécutez la macro en choisissant la nouvelle commande Changer la casse dans le menu contextuel de votre sélection.

Si la commande Changer la casse n'apparaît pas en bas du menu Outils, c’est très certainement parce que vous n'avez pas autorisé l'activation des macros à l'ouverture du classeur contenant la macro complémentaire. Fermez-le puis rouvrez-le, en veillant cette fois à autoriser l'activation des macros.

Ajouter des informations utiles

Il est recommandé d'ajouter une description aux macros complémentaires, bien que ce ne soit pas obligatoire :

1. Activez le classeur contenant la macro complémentaire Changer la casse (change casse. xlsm)
2. Sous l'onglet Développeur, cliquez sur le bouton Panneau de documents dans la catégorie Modifier. Cliquez ensuite sur OK. Excel affiche le panneau Propriétés du document au-dessus de la barre de formule (comme l’on peut le voir sur la figure 3 ci-dessous).
3. Tapez un nom dans le champ Titre. Il apparaîtra dans la boite de dialogue Macro complémentaire. Tapez par exemple Changer la casse.
4. Entrez une description dans le champ Commentaires. Elle apparaîtra en bas de la boîte de dialogue Macro complémentaire lorsque la macro sera sélectionnée. Saisissez par exemple : Change la casse du texte sélectionné. Pour accéder à cet outil, cliquez droit pour ouvrir le menu contextuel.
5. Cliquez sur la croix qui se trouve à droite du panneau pour le refermer.

Entrez la description de la macro complémentaire dans le panneau Propriétés du document.

Pour protéger votre code VBA, procédez comme expliqué plus haut, au début de la section "Les bases des macros complémentaires"

Générer la macro complémentaire

À ce point, vous avez testé la macro Changer la casse et les essais sont concluants. Il s'agit à présent de créer la macro complémentaire :

1. Activez l'éditeur VBE et , dans la fenêtre Projet, sélectionnez le classeur contenant la macro Changer la casse (normalement, changer la casse.xslm)
2. Choisissez Outils/Propriétés de VBAProject puis cliquez sur l'onglet Protection.
3. Cochez la case Verrouillez le projet pour l'affichage. Entrez ensuite un mot de passe (à deux reprises pour le confirmer)
4. Cliquez sur OK.
5. Enregistrer le classeur.
6. Revenez sous Excel.
7. Activez le classeur contenant la macro complémentaire puis choisissez Fichier/Enregistrer-sous.
8. Déroulez la liste Type de fichier et choisissez Macro complémentaire Microsoft Excel (*.xlam)
9. Cliquez sur Enregistrer.

Une nouvelle macro complémentaire est créée, avec l'extension de fichier .xlam. Le fichier XLSM original reste ouvert.

Ouvrir la macro complémentaire

Pour éviter toute confusion, fermez le classeur XLSM avant d'ouvrir la macro complémentaire créée à partir de lui.

Ouvrez la macro complémentaire en procédant comme suit:

1. Sous l'onglet Développeur, cliquez sur le bouton Compléments dans la catégorie de même nom. Excel affiche la boîte de dialogue Macro complémentaire.
2. Cliquez sur le bouton Parcourir.
3. Localisez et sélectionner la macro complémentaire que vous venez de créer.
4. Cliquez sur OK pour fermer la boîte de dialogue Parcourir. La nouvelle macro complémentaire apparaît dans la boîte de dialogue Macro complémentaire comme on peut le remarquer sur la figure ci-dessous. Par ailleurs notez que la description que vous avez inscrit dans le panneau Propriétés du document (comme vu plus haut dans cette leçon), seront visible en bas de cette boîte de dialogue.
5. Vérifiez bien que la case qui se trouve devant le nom de votre macro complémentaire est bien cochée.
6. Cliquez sur OK pour fermer la boîte de dialogue et ouvrir la macro complémentaire.

La nouvelle macro complémentaire vient d’être ajoutée aux autres

Votre macro complémentaire est maintenant disponible dans tous vos classeurs. Tant que la case correspondante reste cochée dans la boîte de dialogue Macro complémentaire, elle sera disponible chaque fois que vous ouvrirez Excel (et que son emplacement sera accessible).

Distribuer la macro complémentaire

Votre macro complémentaire peut être distribuée auprès d'autres utilisateurs d'Excel (si vous le souhaitez). Il suffit pour cela de leur remettre un exemplaire du fichier XLAM (ils n'ont pas besoin de la version XLSM). Dès qu'ils ouvrent la macro complémentaire, l'option Changer la casse apparaît en bas du menu contextuel associé aux cellules, aux lignes et aux colonnes. Si, et comme, le fichier est protégé, le code de la macro ne peut être examiné (à moins évidemment de connaître le mot de passe).

Modifier la macro complémentaire

Une macro complémentaire enregistrée dans un fichier XLAM peut être éditée comme tout autre classeur (vous n'avez pas besoin du fichier XLSM). Il en conviendra qu’il faudra la déprotéger de la manière suivante :

1. Ouvrez le fichier XLA si ce n’est pas déjà fait.
2. Activez l'éditeur VBE
3. Dans la fenêtre Projet, double-cliquez sur le nom du projet. Le mot de passe vous est demandé
4. Entrez le mot de passe puis cliquez sur OK
5. Modifiez le code selon vos besoins
6. Enregistrez le fichier à partir de l'éditeur VBE, en cliquant sur Fichier/Enregistrer.

Conclusion

Avec cette leçon vous savez dorénavant comment créer des macros complémentaires ainsi que leur utilitées. Grâce à l'exemple proposé dans la leçon, vous avez pu apprendre les étapes à suivre afin de modifier la casse des caractères dans les cellules sélectionnées. Pour en apprendre davantage, le moyen le plus simple est de s'exercer en créant d'autres macros complémentaires par vous-même. Enfin, nous vous invitons à répondre au Quizz Macros Complémentaires, pour vous tester sur le sujet.

Annexes

Bibliographie

• John Walkenbach, 2013, Programmation VBA pour Excel Pour Les Nuls, FIRST

Liens internes

• Leçon VISUAL BASIC
• Leçon Tableur EXCEL

Liens externes

• FAQ developpez VBA
• Une introduction au Visual Basic

Consolider des classeurs

Début de la boite de navigation du chapitre

fin de la boite de navigation du chapitre

Introduction

La consolidation de classeurs Excel permet de regrouper sur un seul et même classeur diverses données provenant de plusieurs classeurs. Certains d’entre vous diront qu’il n’est pas nécessaire d’utiliser VBA pour effectuer ce genre de travail. Certes vous pouvez le faire via Microsoft Excel si vous n’avez que quelques documents à consolider. Toutefois, si vous êtes amenés à effectuer ce travail à partir de dizaine et de dizaine de classeurs Excel, vous verrez qu’il est préférable d’utiliser la fonction VBA pour économiser un maximum de temps.
Nous prendrons dans cette leçon l’exemple d’un groupe qui veut consolider les résultats de toutes ses filiales. En effet, il est fréquent dans un groupe d’avoir des fichiers Excel types que chaque filiale renvoie au siège qui doit consolider tous ces fichiers pour obtenir les résultats du groupe. Cependant la consolidation de plusieurs fichiers est chronophage et répétitive et peut rapidement être fastidieuse si elle n’est pas automatisée.
Il existe plusieurs façons de consolider des classeurs Excel selon le type de données et le format qu’ils contiennent. À travers des cas pratiques nous allons étudier deux d’entre elles que nous appellerons la consolidation par somme des données et la consolidation par regroupement de bases de données.

• La consolidation par somme des données consiste à sommer dans un seul classeur le total des données se trouvant dans les classeurs à consolider.
• La consolidation par regroupement de bases de données consiste à copier les différentes bases de données se trouvant dans les classeurs à consolider et les coller dans un seul classeur afin d’avoir toutes les données à un seul endroit.

Consolidation par somme de données

Utilité

Dans ce cas nous voulons sommer des tableaux identiques se trouvant dans différents classeurs sur un seul classeur. Ce type de consolidation, que l’on va appeler ici consolidation par somme de données, nécessite que les données de base soient constituées de chiffres et soient sous le même format.

Dans cet exercice nous allons prendre l’exemple d’une chaîne de magasin qui veut consolider les résultats de ses filiales. Chaque filiale renvoie le même fichier avec ses résultats sous forme de tableau qui comporte une liste de produits vendus, les quantités vendues et les chiffres d’affaires associés. Nous voulons sommer l’ensemble de ces résultats dans un seul tableau de synthèse afin d’obtenir le résultat consolidé du groupe. Manuellement il faudrait ouvrir tous les fichiers et sommer une à une chaque cellule dans un fichier de synthèse. Excel propose un outil appelé consolidateur de données qui permet de sommer toutes ces données cependant, cet outil n’est pas automatique et il faut sélectionner les bases à consolider à chaque fois que l’on veut faire la manipulation. Il est toutefois possible d’automatiser cette manipulation, grâce à VBA, afin de ne pas la reproduire à chaque fois que l’on veut consolider les données.

Application

Résultats du magasin de Grenoble	Résultats du magasin de Paris

Comme on peut le constater, les fichiers sont au même format, ils comportent tous les deux une liste de produits, les quantités vendues et les chiffres d’affaires correspondant. On observe toutefois que le magasin de Paris présente des produits qui ne sont pas présents dans le magasin de Grenoble. Cela ne pose pas de problème car VBA va recopier tous les produits et sommer les quantités vendues et les chiffres d’affaires.

Grâce à la consolidation on veut obtenir le tableau consolidé suivant regroupant tous les produits, les quantités vendues et le chiffre d’affaires :

Résultats consolidés des deux magasins

Décomposition du code VBA

Sub Consolidation_somme()

'Sélectionne la cellule de départ du tableau consolidé:
Range ("A1").Select

' Crée le tableau consolidé:
    '1) Sélectionne les classeurs de données à consolider
    '2) Détermine les conditions de consolidation des données. Ici:
        'a) on somme les données (Function:=xlSum)
        'b) on rapatrie la ligne de titres (TopRow) et la colonne des produits (LeftColumn)
        'c) on ne crée pas de liens entre les classeurs de données et le classeur de synthèse
        
    Selection.Consolidate Sources:=Array( _
        "'E:\Wiki\Consolidation par somme de données\[Grenoble.xlsx]Feuil1'!R1C1:R1000C200", _
        "'E:\Wiki\Consolidation par somme de données\[Paris.xlsx]Feuil1'!R1C1:R1000C200"), _
        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
        
End Sub

Explications et analyse de la macro

Conditions du tableau consolidé	Exemples
Function :=xlSum : Somme les données consolidées	Par exemple, le magasin de Grenoble a vendu 1 000 000 pommes et le magasin de Paris a vendu 1 210 000 pommes ce qui fait un total de ventes de 2 210 000 pommes
TopRow :=True : Rapatrie la ligne de titres dans le fichier consolidé. Si on met « False » on n’obtient pas la ligne de titres et on ne sait donc pas à quoi correspondent les chiffres
LeftColumn :=True : Rapatrie la colonne de gauche (ici les noms de produits). Si on met « False » on n’obtient pas la colonne de gauche et on ne sait donc pas à quels produits correspondent les quantités et les chiffres d’affaires consolidés
CreateLinks :=False : Ne crée pas de liens entre les fichiers. Si on met « True » on obtient le détail par magasin avec des liens entre les fichiers et des formules sommes pour calculer les résultats consolidés.
On note toutefois que le titre "Produits" n'apparaît pas lorsque l’on sélectionne à la fois la condition TopRow :=True et la condition LeftColumn :=True

Remarques

Pour éviter les erreurs et de devoir modifier le code VBA à chaque fois que l’on inclut des nouvelles lignes ou de nouvelles colonnes dans un des classeurs de données, il est conseillé de définir dès le départ une plage plus large que celle de votre tableau. Sinon vous risquez de ne pas prendre en compte toutes les données à consolider. Ici, nous avons défini une plage qui comprend toutes les cellules de la 1ère cellule de la première colonne du fichier (R1C1) jusqu’à la 1 000ème ligne de la 300ème colonne du fichier (R1000C200). Pour ajouter un nouveau classeur à la consolidation, il faut ajouter le lien d’accès à ce classeur et la plage de données dans le code (ex : "'E:\Wiki\Consolidation par magasins\[Grenoble.xlsx]Feuil1'!R1C1:R1000C200",)

Consolidation par regroupement de bases de données

Utilité

Dans ce cas, nous voulons regrouper plusieurs bases de données de même format dans une seule base de données globale. Ce type de consolidation, que l’on va appeler ici consolidation par regroupement de plusieurs bases de données, nécessite que les bases de données à consolider soient sous le même format. Les données qu’elles contiennent peuvent être de n’importe quel format (texte, nombre, date,…) excepté le format image car les données doivent être contenues dans des cellules.

Attention, si les cellules du fichier de synthèse dans lequel vous allez regrouper vos données ne sont pas au même format vous risquez d’avoir de petites surprises notamment si vous consolidez des données au format date. En effet si vous copiez une donnée sous format date dans une cellule au format standard votre date sera copiée en nombre. Par exemple la date 30.01.1993 deviendra 33999. Rassurez-vous vous ne perdez pas vos données, il suffit d’appliquer le bon format à vos cellules pour retrouver vos dates sous le bon format.

Dans cet exercice nous allons prendre l’exemple d’une entreprise qui possède plusieurs bases de données comprenant des coordonnées clients et qui veut regrouper toutes ces coordonnées dans une seule base de données.

Application

Première base de données	Deuxième base de données

Comme on peut le constater les données des deux classeurs sont sous le même format. Nous allons donc copier ces deux bases et les regrouper l’une à la suite de l’autre dans un seul classeur.

Grâce à la consolidation on veut obtenir la base de données consolidée suivante qui regroupe toutes les données contenues dans les différentes bases de données :

Base de données consolidée

Décomposition du code VBA

Sub consolidation_regroupement()

'Stoppe l'actualisation de l'écran. Cela sert à masquer les actions de la macro
Application.ScreenUpdating = False

'Détermine le chemin d'accès aux fichiers
Dim chemin As String
Dim fichier As String
Dim extension As String

chemin = "C:\Users\Wiki\Consolidation par regroupement de bases de données\"
fichier = "Coordonnées clients "
extension = ".xlsx"

'Indique le nombre de fichiers à consolider
nbfichiers = 2

   For i = 1 To nbfichiers
   
   'Ouvre le fichier à consolider
    Workbooks.Open (chemin & fichier & i & extension)
    
    'Sélectionne la feuille où se trouvent les données
    Sheets("Feuil1").Select

    'Compte le nombre de lignes à copier
    n = WorksheetFunction.CountA(Range("A:A"))
    
    'Compte le nombre de colonnes à copier
    m = ActiveSheet.UsedRange.Columns.Count
    
    'Copie les données
    Range(Cells(2, 1), Cells(n, m)).Copy
    
    'Active le classeur de synthèse
    Windows("Synthèse.xlsm").Activate
    
    'Sélectionne la feuille où on va coller les données
    Sheets("Synthèse").Select

    'Compte le nombre de lignes non vides et ajoute 1 pour avoir le numéro de la première ligne vide
    c = WorksheetFunction.CountA(Range("A:A")) + 1
    
    'Sélectionne la première cellule vide
    Range("A" & c).Select
    
    'Colle les données
    ActiveSheet.Paste
    
    'Ferme la base de données qui a été consolidée et passe à la suivante
    Windows(fichier & i & extension).Close
    
    Next i

'Réactive l'actualisation de l'écran
Application.ScreenUpdating = True


End Sub

Explications et analyse de la macro

1. La première étape de ce code est de définir le chemin d’accès aux classeurs à consolider, leur nom et leur extension. Afin d’alléger le code et de rendre la macro plus facilement modifiable nous avons décidé d’utiliser une boucle qui va copier les données des bases une par une pour les coller dans la base consolidée. Pour cela il faut que les classeurs à consolider se trouvent tous dans le même répertoire de notre ordinateur et portent tous le même nom à l’exception d’un nombre qui permet de distinguer les classeurs. Ici nous avons appelé notre première base de données « Coordonnées clients 1 », la seconde « Coordonnées clients 2 », etc… Nous avons donc défini quatre variables :

   • Chemin : cette variable indique le répertoire dans lequel se trouvent les classeurs à consolider
   • Fichier : cette variable indique le nom du classeur à consolider. Vous noterez que dans le code nous avons nommé le classeur « Coordonnées clients » mais nous n’avons pas indiqué de numéro 1, 2, etc… C’est parce que nous avons enregistré cette information dans une variable à part qui sera ensuite gérée par la boucle.
   • Extension : cette variable indique le type d’extension du classeur (xls, xlsx, xlsm). Dans ce code il faut que tous les classeurs aient la même extension pour que la boucle fonctionne toutefois si vous choisissez de noter les noms des classeurs un à un vous pouvez utiliser des classeurs qui n'ont pas la même extension.
   • Nbclasseurs : cette variable indique le nombre de classeurs à consolider

2. La seconde étape est donc la boucle qui est le cœur de la macro car c’est là que va se faire la consolidation des classeurs. La boucle est définie de 1 à nbclasseurs ce qui signifie qu’elle va tourner autant de fois qu’il y a de classeurs à consolider et répéter la même action pour chaque classeur.
3. Tout d’abord la macro va ouvrir le premier classeur à consolider. Elle va trouver ce classeur grâce aux variables que l’on a défini au début et qui, toutes assemblées, donnent l’accès au classeur. La variable i qui va de 1 à nbclasseurs indiquera donc le numéro du classeur à traiter en fonction de la boucle. Par exemple, le premier tour de boucle va traiter le classeur « Coordonnées clients 1 », le second tour de boucle va traiter le classeur « Coordonnées clients 2 » et ainsi de suite.
4. La macro sélectionne ensuite l’onglet du classeur où se trouvent les données à consolider. Attention, il faut penser à changer le nom de l’onglet s’il ne correspond pas à celui de votre classeur. Bien entendu tous les classeurs doivent avoir un onglet du même nom dans lequel se trouveront les données
5. On compte ensuite le nombre de lignes et de colonnes qui comprennent des données afin de définir la plage de données à consolider et on copie ces données. Attention: dans cet exemple, la macro compte le nombre de cellules non vides pour calculer la plage de données à copier. Cela signifie que s'il y a des trous dans la base (lignes vides), la plage de données qui sera prise en compte par la macro risque de ne pas comprendre toutes les données. Exemple: Si j’ai de données sur la colonne de la ligne 1 à la ligne 5 et de la ligne 7 à la ligne 9 j'aurais au total de 8 lignes remplies. Or si je donne cette valeur à ma variable qui calcule la plage de données ma macro va sélectionner la plage A1 à A8 alors que j’ai des données en A9, je ne vais donc pas récupérer toutes mes données. NB: Cette problématique est typique à ce code, il est toutefois possible de l'adapter pour contourner le problème.
6. On active le classeur où l’on va consolider les données et on sélectionne l’onglet où se trouve la base de données consolidées
7. On compte alors le nombre de lignes qui ne sont pas vides dans cet onglet et on ajoute 1 à ce nombre afin d’obtenir le numéro de la première ligne vide à partir de laquelle on va pouvoir coller les données et on colle les données.
8. On ferme le classeur duquel on a extrait les données.
9. On fait un tour de boucle afin de traiter le prochain fichier

Remarques

Dans notre exemple tous les classeurs de base de données à consolider se trouvent dans le même répertoire, portent le même nom et sont seulement différenciés par un numéro accolé à la fin du nom du classeur. Ici nos classeurs sont nommés « Coordonnées clients 1 » et « Coordonnées clients 2 » ce qui permet de faire une boucle dans la macro avec les noms de fichiers. Chaque classeur a également la même extension. Si un des fichiers a un nom ou une extension différente ou se trouve dans un autre répertoire il ne sera pas consolidé par la macro.

Cette consolidation ne fait que regrouper plusieurs classeurs. Si des mêmes données sont présentes dans plusieurs classeurs vous aurez alors des doublons dans votre classeur consolidé. Vous pourrez supprimer ces doublons soit manuellement soit en créant une nouvelle macro que vous exécuterez à la suite de votre macro de consolidation.

Conclusion

En conclusion, la consolidation de classeurs via VBA permet un gain de temps considérable à condition que les bases de données à consolider respectent toutes le même format.
Nous avons tenté de simplifier les processus au maximum cependant il est possible de les modifier et de les améliorer. Notamment, dans la consolidation que l’on a appelée consolidation par regroupement de bases de données il est possible de rapatrier les données sans ouvrir les classeurs sources mais cela complique le code c’est pourquoi nous avons choisi dans notre exemple d'ouvrir les fichiers un à un puis de le refermer.
Il existe également d'autres méthodes pour consolider des classeurs mais nous avons voulu nous concentrer sur ces deux méthodes qui sont très couramment utilisées dans les entreprises et qui sont faciles et rapides à exécuter.
NB: les noms que nous avons attribué aux méthodes de consolidation (consolidation par somme de données et consolidation par regroupement de bases de données) sont purement de notre fait afin d'illustrer leur action, vous ne retrouverez probablement ces appellations nulle part ailleurs.

GFDL

Vous avez la permission de copier, distribuer et/ou modifier ce document selon les termes de la licence de documentation libre GNU, version 1.2 ou plus récente publiée par la Free Software Foundation ; sans sections inaltérables, sans texte de première page de couverture et sans texte de dernière page de couverture.