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 qu'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 qu'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()