Macros-commandes VBA/Version imprimable

Image logo
Ceci est la version imprimable de Macros-commandes VBA.
  • Si vous imprimez cette page, choisissez « Aperçu avant impression » dans votre navigateur, ou cliquez sur le lien Version imprimable dans la boîte à outils, vous verrez cette page sans ce message, ni éléments de navigation sur la gauche ou en haut.
  • Cliquez sur Rafraîchir cette page pour obtenir la dernière version du cours.
  • Pour plus d'informations sur les version imprimables, y compris la manière d'obtenir une version PDF, vous pouvez lire l’article Versions imprimables.


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
Version imprimable
 
Chapitre no 1
Leçon : Macros-commandes VBA
Retour auSommaire
Chap. suiv. :Pour démarrer
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.


Introduction modifier

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.


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é


Historique modifier

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 modifier


Un heureux mariage modifier

Un Langage VISUAL BASIC modifier

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 modifier

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 modifier

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

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

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

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

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 modifier

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 modifier

 
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[1], 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[2].

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 modifier

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[3]. 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 modifier

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\[4].
  • Cliquer dans l'onglet "Affichage" sur "Masquer".

Chargement automatique de bibliothèques modifier

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

  • Copier le fichier dans C:\Users\%USERNAME%\AppData\Roaming\Microsoft\AddIns\[5]. 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.

Références modifier

  1. ((en)) http://college.yukondude.com/2004_09_comp219/html/note.php?note=07^Handout^Project_Estimation.tpl
  2. http://www.business-spreadsheets.com/vba2oo.asp
  3. http://www.microsoft.com/visualstudio/en-us/products/2010-editions/visual-basic-express
  4. https://support.office.com/fr-fr/article/Créer-et-enregistrer-toutes-vos-macros-dans-un-classeur-unique-aa439b90-f836-4381-97f0-6e4c3f5ee566
  5. http://www.cpearson.com/excel/installinganxla.aspx

Annexes modifier

Bibliographie modifier

  • 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 modifier

Liens externes modifier




Pour démarrer

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 2
Leçon : Macros-commandes VBA
Chap. préc. :Les Macros Excel
Chap. suiv. :Architecture
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Un Onglet masqué en standard par EXCEL modifier

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.


Intérêts des macros pour les novices modifier

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 ! modifier

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 modifier


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 modifier


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 modifier


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 modifier


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 modifier

Bibliographie modifier

  • 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 modifier



Architecture

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 3
Leçon : Macros-commandes VBA
Chap. préc. :Pour démarrer
Chap. suiv. :Les types en VB
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Introduction modifier

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 modifier

Les procédures modifier

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 modifier

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 modifier

  • 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 modifier

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 modifier

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 modifier

  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 modifier

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 modifier

  Pour aller plus loin, voir : Manipulation des variables.

Les commentaires modifier

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 modifier

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 modifier

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 modifier

  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 modifier

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 modifier

Bibliographie modifier

  • 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 modifier

Liens externes modifier




Les types en VB

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 4
Leçon : Macros-commandes VBA
Chap. préc. :Architecture
Chap. suiv. :Manipulation des variables
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Différents types modifier

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[1] :

Types VB les plus couramment rencontrés
Type Description Valeurs possibles Encombrement mémoire[2] 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  ) à 32 767 ( ) 2 o CInt()
Long Entier long −2 147 483 648 (soit  ) à 2 147 483 647 ( ) 4 o CLng()
Single Variable à virgule flottante : simple précision de -3.402823   à -1.401298   pour les nombres négatifs

de 1.401298   à 3.402823   pour les nombres positifs

4 o CSng()
Double Variable à virgule flottante : double précision de -1.79769313486232   à -4.94065645841247   pour les nombres négatifs

de 4.94065645841247   à 1.79769313486232   pour les nombres positifs

8 o CDbl()
String Chaîne de caractères (texte unicode) * < 255 o[3] 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 modifier

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[4][5] VBA[6] VBScript[7] 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 modifier

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 modifier

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

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[9] :

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[10] :

  • 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 modifier

Pour rechercher dans un tableau[11] :

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

Test si nul modifier

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 modifier

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[12] :

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[13].

POO modifier

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 modifier

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

Références modifier

  1. http://www.chennaiiq.com/developers/reference/visual_basic/functions/type_conversion_functions.asp
  2. https://msdn.microsoft.com/en-us/library/aa263420%28v=vs.60%29.aspx
  3. http://support.microsoft.com/kb/105416/fr
  4. Marcus et Levy 2002, p. 750–751.
  5. Roman, Petrusha et Lomax 2002b, p. 394 et seq..
  6. Harris 1999, p. 402–403.
  7. Knittel 2002, p. 609.
  8. http://msdn.microsoft.com/fr-fr/library/2k7ayc03%28v=vs.80%29.aspx
  9. http://silkyroad.developpez.com/vba/tableaux/
  10. http://ericrenaud.fr/droitetableaux.htm
  11. http://www.excelabo.net/excel/trouver_valeur_array
  12. http://msdn.microsoft.com/fr-fr/library/sect4ck6%28v=vs.80%29.aspx
  13. http://www.excel-pratique.com/fr/vba/evenements_classeur.php



Manipulation des variables

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 5
Leçon : Macros-commandes VBA
Chap. préc. :Les types en VB
Chap. suiv. :Conditionnelles et boucles
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Déclaration des variables modifier

Syntaxe modifier

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
Fin de l'exemple


Portée modifier

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
Fin de l'exemple


Opérations possibles sur les variables modifier

Affectation modifier

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

Début de l'exemple
Fin de l'exemple


Opérations mathématiques modifier

Usuelles modifier

  • Les opérations usuelles (addition, soustraction, multiplication, division) s'écrivent en VB avec leurs symboles usuels.
Début de l'exemple
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
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 modifier

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

Arrondir un nombre modifier

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 modifier
a = 1,33
msgbox round(a+0.5)
À l'inférieur modifier
a = 1,66
msgbox round(a-0.5)

Opérations logiques modifier

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
Fin de l'exemple


Comparaison modifier

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).

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
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 modifier

Longueur de la chaîne modifier

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

Début de l'exemple
Fin de l'exemple


Concaténation modifier

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
Fin de l'exemple


Suppression des espaces latéraux modifier

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
Fin de l'exemple


Changement de casse modifier

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
Fin de l'exemple


Extraction d'une sous-chaîne modifier

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
Fin de l'exemple


Recherche d'une sous-chaîne modifier

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
Fin de l'exemple


Valeur numérique modifier

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
Fin de l'exemple


Opérations sur les dates modifier

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] :

  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 modifier

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
Fin de l'exemple


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

Début de l'exemple
Fin de l'exemple


Variables publiques connues modifier

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).

Références modifier

  1. http://www.commentcamarche.net/contents/vbscript/vbs-fonctions-date.php3




Conditionnelles et boucles

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 6
Leçon : Macros-commandes VBA
Chap. préc. :Manipulation des variables
Chap. suiv. :Enregistreur
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Structures de test modifier

Test conditionnel simple : le bloc If modifier

Exemple :

if (testCondition) then
   instruction1
else
   instruction2
end if

Distinction de cas : Select Case modifier

'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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

À 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
Version imprimable
 
Chapitre no 7
Leçon : Macros-commandes VBA
Chap. préc. :Conditionnelles et boucles
Chap. suiv. :Boite de dialogue
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Un outil magique pour l'apprentissage modifier

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)


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 ?"


Enregistrer une macro modifier

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 modifier

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
Fin de l'exemple


Associer une combinaison de touches à la macro modifier

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
Fin de l'exemple


Choisir le lieu de stockage de la macro modifier

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
Fin de l'exemple


Commenter la macro modifier

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
Fin de l'exemple


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

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


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


 
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
Fin de l'exemple


Les avantages et les inconvénients de l'enregistreur modifier

Les avantages modifier

  • 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 modifier

  • 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 modifier


 
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
Fin de l'exemple


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

Annexes modifier

Bibliographie modifier

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

Liens internes modifier

Liens externes modifier




Boite de dialogue

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 8
Leçon : Macros-commandes VBA
Chap. préc. :Enregistreur
Chap. suiv. :Création de Formulaire

Exercices :

Convertisseur Euro-Franc
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

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

La boite de dialogue MsgBox modifier

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 modifier

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 modifier

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 modifier

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 modifier

 

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[1].

Références modifier

Bibliographie modifier

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

Liens internes modifier

Liens externes modifier



Création de Formulaire

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 9
Leçon : Macros-commandes VBA
Chap. préc. :Boite de dialogue
Chap. suiv. :Création de Fonction
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

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 modifier

Définition du formulaire modifier

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 modifier

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 modifier

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 modifier

Accès à l'éditeur Visual Basic modifier

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 modifier

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 modifier

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
  • BackcolorBordercolor - Border style
  • Picture

Pour afficher les propriétés modifier

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 modifier

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


Caption modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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

  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 modifier

  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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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,
    • 2e option : un nom défini (onglet formules/définir un nom) faisant référence à la plage souhaitée ou,
    • 3e 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 modifier

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 modifier

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 2e colonne ou autre, il faut le préciser en utilisant la propriété BoundColumn

Illustration :

  Projet.boundColumn = 2

Les listes à choix multiples modifier

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 modifier

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 modifier

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 modifier

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 modifier

Comment afficher un formulaire grâce à un bouton ? modifier

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.


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 modifier

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

Modifier les couleurs d'un bouton modifier

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 modifier

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) modifier

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".

 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)

 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" modifier

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) modifier

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

Intitulé modifier

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) modifier

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

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

Dans les propriétés ⇒ Données ⇒ RowSource, rentrer la zone ciblée : Feuil2!A1:A15 ⇒ '13e image' « http://imageshack.com/a/img801/245/3pfh.jpg » (ArchiveWikiwixQue 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 modifier

 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é ⇒ '16e image' « http://imageshack.com/a/img191/7605/nwl1.jpg » (ArchiveWikiwixQue faire ?). Consulté le 2017-07-23

Contrôler une saisie dans une TextBox modifier

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 ⇒ '17e image' « http://imageshack.com/a/img41/4899/bcy4.jpg » (ArchiveWikiwixQue 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 ⇒ '18e image' « http://imageshack.com/a/img51/1829/2z3r.jpg » (ArchiveWikiwixQue faire ?). Consulté le 2017-07-23

Transfert de données du formulaire dans la feuille modifier

Boîtes de texte modifier

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 modifier

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 modifier

Exemple d'initialisation d'un formulaire modifier

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 modifier

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é modifier

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

Les formulaires Access et Excel modifier

Description modifier

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 modifier

 
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 modifier

 

Frame modifier

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

Button modifier

 

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

Label modifier

  Intitulé.

LinkLabel modifier

Hyperlien.

TextBox modifier

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

CheckBox modifier

  Case à cocher pour questionnaire à choix multiple.

OptionButton modifier

 

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 modifier

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

Image modifier

   

PictureBox modifier

 

Ligne - Line modifier

 

Forme géométrique - Shape modifier

 

Explorateur de disques modifier

 

Explorateur de dossiers modifier

 

Explorateur de fichiers modifier

 

  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 modifier

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 modifier

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 modifier

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 modifier

Annexes modifier

Bibliographie modifier

  • 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 modifier

Liens externes modifier



Création de Fonction

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 10
Leçon : Macros-commandes VBA
Chap. préc. :Création de Formulaire
Chap. suiv. :Création de Graphique

Quiz :

QCM Les fonctions VBA
Exercices :Calcul d'une commission
Annexe :La gestion des erreurs
Annexe :Quelques fonctions Financières sous VBA
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.


Création d'une fonction modifier


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

  • 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
Fin de l'exemple


Utilité des fonctions personnalisées modifier

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
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 modifier

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


Utilisation d'une fonction personnalisée sous VBA modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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 modifier

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

Calcul des intérêts modifier

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 modifier

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 modifier

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 modifier

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 modifier

Bibliographie modifier

  • 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 modifier

Liens externes modifier



Création de Graphique

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 11
Leçon : Macros-commandes VBA
Chap. préc. :Création de Fonction
Chap. suiv. :Création de Tableau croisé
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Définitions modifier

Qu’est-ce qu’un graphique ? modifier

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


Qu’est-ce qu’une macro ? modifier

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 ? modifier

Créer une base de données modifier

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
Fin de l'exemple


Création manuelle du graphique modifier

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é


Configurer le graphique modifier

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 ? modifier

Test avec enregistrement modifier

Démarche modifier

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.


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"


Avantages modifier

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 modifier

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 modifier

Démarches modifier


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
Fin de l'exemple


Avantages modifier

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 modifier

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 oeuvre la solution finalisée
  • Demande une transmission des savoirs lors des changements d'organisation du travail

Comment personnaliser son graphique via le langage VBA ? modifier

Les constantes utilisées pour choisir un type de graphique modifier

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 modifier

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 modifier

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 modifier

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 acceuillir 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 modifier

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 modifier

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 modifier

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 modifier

Bibliographie modifier

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

Liens internes modifier

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

Liens externes modifier

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
Version imprimable
 
Chapitre no 12
Leçon : Macros-commandes VBA
Chap. préc. :Création de Graphique
Chap. suiv. :Lectures et écritures
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Définitions modifier

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

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
Fin de l'exemple


Qu’est-ce qu’une macro ? modifier

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? modifier

Renseignement des données modifier

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.


Début de l'exemple
Fin de l'exemple


Création manuelle du TCD modifier

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"


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 modifier

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.



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

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 ? modifier

Test avec enregistrement modifier

Démarche modifier

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

La démarche est la suivante :

1ere étape : Création de la macro

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

2e étape : Exécution de la macro

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

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


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

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

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

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

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

End Sub


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

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

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

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

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

Cela revient à : TableDestination = ""

  • Supprimer les lignes :

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


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

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

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

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

End Sub


  • 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 modifier

  • 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 modifier

  • 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 modifier

Démarches modifier

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
Fin de l'exemple



Avantages modifier

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 modifier

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 oeuvre la solution finalisée.
  • Demande une transmission des savoirs lors des changements d'organisation du travail.

Comment peut-on paramétrer un TCD ? modifier

Appliquer une fonction modifier

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

...


Paramétrer les sous-totaux modifier

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 ? modifier

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
Fin de l'exemple



Annexes modifier

Bibliographie modifier

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

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

Liens internes modifier

Liens externes modifier

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
Version imprimable
 
Chapitre no 13
Leçon : Macros-commandes VBA
Chap. préc. :Création de Tableau croisé
Chap. suiv. :Gestion des droits et répertoires
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.
 
Wikibooks-logo.svg
Wikilivres possède un manuel à propos de « Modifier des fichiers en VBS ».

Manipulation de répertoires modifier

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()[1] :

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 modifier

Sélectionner un fichier ouvert modifier

Sub MonFichier()
  Workbooks("Nom du fichier").Activate
  ' Choisir une cellule
  Workbooks("Nom du fichier").Worksheets(1).Cells(1, 1)
End Sub

Rechercher des fichiers modifier

Pour rechercher des fichiers Excel en VB 6.3 depuis un fichier .xls[2] :

Sub Liste()
  ligne = 2
  file = Dir(ThisWorkbook.Path & "\*.xls") 'Premier fichier dans l’ordre alphabétique, dans Windows
  Do While file <> ""  'Jusqu'à ce que la recherche soit vide
    Cells(ligne, 1) = file  'On écrit le nom du fichier dans une cellule
    file = Dir                             'Fichier suivant
    ligne = ligne + 1
  Loop
End Sub

Pour passer au fichier .xls suivant dans le répertoire, il suffit de rappeler Dir() sans paramètre :

 file = Dir()
  Si le chemin dans la commande Dir() contient deux étoiles, elle retrouvera le même fichier lors de son rappel

La version postérieure propose un autre mode de recherche[3].

Copier des fichiers modifier

Sub Copier()
  FileCopy "C:\Fichier.txt", "C:\Temp\Archive.txt"
End Sub

Avec la date du jour pour éviter d'écraser :

Sub Copier2()
  FileCopy "C:\Fichier.txt", "C:\Temp\Archive" & Date & ".txt"
End Sub

Déplacer des fichiers modifier

Sub Deplacer()
    Dim FSO As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    FSO.MoveFile Source:="C:\Fichier.txt", Destination:="C:\Temp\Archive"
End Sub

PS : cela sert aussi à les renommer[4].

Supprimer des fichiers modifier

Sub Supprimer()
    Dim FSO As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    FSO.DeleteFile "C:\Fichier.txt"
End Sub

Propriétés des fichiers modifier

Pour modifier les propriétés d'un fichier, utiliser SetAttr[5] :

Paramètre Valeur alternative Description
vbNormal 0 Fichier normal
vbReadOnly 1 Lecture seule
vbHidden 2 Fichier caché
vbSystem 4 Fichier système
vbArchive 32 Archive
vbAlias 64 Lien symbolique

Exemple :

SetAttr "C:\Temp\Test.xls", vbHidden

Fichier texte modifier

Si le fichier text.txt n'existe pas le programme le crée, sinon il l'écrase :

Sub Texte()
  Open "C:\Users\login\Desktop\text.txt" For Input As #1
  MsgBox "Le fichier pèse : " & LOF(1) & " octets"
  NbLigne = 0
  While Not EOF(1)
    Line Input #1, Ligne
    MsgBox (Ligne)
    NbLigne = NbLigne + 1
  Wend
  MsgBox "Il contient : " & NbLigne & " lignes."
  Print #1, NbLigne
  Close #1
End Sub
  Line Input est limité à 250 caractères par ligne si on ne déclare pas la variable. Pour éviter qu'elle soit limitée en type String il faut donc spécifier un type :
Dim Ligne as Variant

De plus, si le fichier texte lu est au format UNIX, la fonction Line Input lira tout le fichier en une seule fois, sans pouvoir distinguer les lignes. Il faut donc le convertir (généralement depuis PC ANSI) au format PC DOS au préalable, par exemple avec le freeware Textpad, ou en appliquant sur chaque ligne Replace(Ligne, Chr(10), vbCrLf).


Pour ajouter une ligne à la fin sans tout relire :

Sub Texte2()
  Open "C:\Users\login\Desktop\text.txt" For Binary As #1
  Put #1, LOF(1), "Dernière ligne"
  Close #1
End Sub

Tableau Excel modifier

Avant de procéder à l'une des deux opération de lecture ou d'écriture, il faut ouvrir le fichier en spécifiant le mode d'ouverture avec la fonction fileopen.

Autre solution, en appelant une macro depuis un fichier Excel, il n’est pas nécessaire rouvrir le fichier[6] :

Sub Tableurs()
    ' Création d'un classeur vierge
    Set FichierResultat = Workbooks.Add()

    ' Création d'une feuille à la fin du classeur
    ActiveWorkbook.Sheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
    ActiveSheet.Name = "Test"
    ' Création d'un classeur automatiquement en copiant une feuille
    ActiveSheet.Copy

    'Modification directe
    Sheets(1).Range("A1").Value = "Ce fichier est situé dans "
    Sheets(1).Range("B1").Value = ActiveWorkbook.Path  '(équivalent à : Sheets("feuille 1").Cells(1, 2).Value = ThisWorkbook.path)
    MsgBox(Sheets(1).Range("B1").Value)       'Affichage d'un champ dans une boite à valider

    ' Parcours de toutes les feuilles du fichier
    Dim F as WorkSheet
    For Each F in WorkSheets
      F.Range("A2").Value = "Feuille lue"
    Next F

    'Suppression de la ligne C
    Rows(3).Delete                  ' Décalage vers le haut
    Rows(3).Delete shift:=xlToLeft  ' Décalage vers la gauche

    'Copie de B dans C
     Rows(2).Select
     Selection.Copy
     Rows(3).Select
     ActiveSheet.Paste    ' Ces quatre lignes ne sont pas équivalentes à : Rows(3).Value = Rows(2).Value, car elles respectent les propriétés des cellules (taille, gras, soulignement...)
    
    'Filtre l’affichage des lignes nulles de la colonne 4
    ActiveSheet.Range("$A$4:$Z$1000").AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlAnd

    'Recherche du mot "numéro"
    Cells.Find(what:="numéro").Activate
    ' Pour une recherche plus ciblée, utiliser : Application.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

    'Récupération des coordonnées du mot
    Dim Recherche as Variant
    Set Recherche = Cells.Find("numéro")
    If Not Recherche Is Nothing Then
       MsgBox "En " & Recherche.Row & ", " & Recherche.Column & " : " & Cells(Recherche.Row, Recherche.Column).Value
    End if

    ' Sélection d'une plage de cellules
    ActiveSheet.Range("A1", "B2").Select ' ou
    ActiveSheet.Range(Cells(1, 1), Cells(2, 2)).Select

    'Sauvegarde
    ActiveWorkbook.Save

    'Sauvegarde ailleurs
    ActiveWorkbook.SaveAs(ActiveWorkbook.Path & "\" & "NouveauNomDuFichier")

End Sub

Attention : la fonction Find() cherche un code contenu dans une cellule, même si elle est plus longue. Pour que la cellule contienne exactement le code recherché, ni plus ni moins, utiliser FindNext() en plus[8] :

Set Recherche = Cells.Find("numéro")
While Not Recherche Is Nothing And Len(Recherche) <> Len("numéro")
  Set Recherche = Cells.FindNext(Recherche)
Wend

Sinon pour manipuler un autre fichier dans un deuxième processus Excel en même temps :

    Dim appExcel As Excel.Application
    Set appExcel = CreateObject("Excel.Application")
    Dim wbExcel As Excel.Workbook
    Dim wsExcel As Excel.Worksheet

    'Accès à un fichier dans le même répertoire que celui qui appelle le script (l'ActiveWorkbook)
    Set wbExcel = appExcel.Workbooks.Open(ActiveWorkbook.Path & "\" & "NomDuFichier") 
    Set wsExcel = wbExcel.Worksheets(1)

    'Traitement
    wsExcel.Sheets(1).Range("A1").Value = "Traité"

    'Sauvegarde et quitte
    wbExcel.Save
    wbExcel.Close
    appExcel.Quit
  La communication inter-processus est 10 fois plus longue que si les deux fichiers sont ouverts avec Application.Workbooks.Open().

Accès aux bases de données modifier

 
Références VBA Excel

Sous Excel, il faut au préalable cocher dans le menu Outils\Références, la bibliothèque ActiveX Data Objects, pour pouvoir stocker les extractions des bases de données dans des objets Recordset (littéralement "jeu d'enregistrement"), comme dans un tableau 2D.

On peut ensuite se connecter en utilisant différents pilotes[9].

Public Function Extraire(Nom) As Boolean
    On Error Resume Next
    Dim Connection As New ADODB.Connection
    Dim Command As New ADODB.Command
    Dim Jeu As New ADODB.Recordset
    Dim Entetes As ADODB.Fields
    Dim Tableau As Variant

   ' Connexions avec le pilote ODBC
    ' Pour MySQL :
    Connection.ConnectionString = "driver={MySQL ODBC 3.51 Driver};server=MonServeur;uid=MonCompte;pwd=MonMotDePasse;database=MaBase"
    ' Pour MS-SQL : Connection.ConnectionString = "driver={SQL Server};server=MonServeur;uid=MonCompte;pwd=MonMotDePasse;database=MaBase"
    ' Pour MS-Access : Connection.ConnectionString = "driver={Microsoft Access Driver (*.mdb)};Dbq=CheminDatabase;Exclusive=0";"
   ' Connexions avec le pilote OLE DB :
    'Connection.ConnectionString = "Provider=SQLOLEDB.1;Data Source=MonServeur;Initial Catalog=MaBase;User ID=MonLogin;password=MonMotDePasse;"
    'Connection.ConnectionString = "Provider=SQLOLEDB;Data Source=MonServeur;Initial Catalog=MaBase;Integrated Security=SSPI;"
   ' Connexions avec le pilote SQL Server Native Client
    'Connection.ConnectionString = "Provider=SQLNCLI;Server=MonServeur;DATABASE=MaBase;Trusted_Connection=yes;"

    Connection.Open
    Command.ActiveConnection = Connection
    Command.CommandText = "SELECT MonChamp from MaTable where Nom = '" & Nom & "'"
    Set Jeu = Command.Execute
    If Jeu.BOF = False And Jeu.EOF = False Then
      Tableau = Jeu.GetRows
      Set Entetes = Jeu.Fields
    End If

    MsgBox ("Premier résultat, " & Entetes.Item(0).Name & " : " & Tableau(0, 0))

    For L = LBound(Tableau, 2) To UBound(Tableau, 2)
      For C = LBound(Tableau, 1) To UBound(Tableau, 1)
        ThisWorkbook.ActiveSheet.Cells(L + 1, C + 1).Value = Tableau(C, L)
      Next C
    Next L
    
End Function

Lancer une procédure stockée modifier

On lance ici une procédure stockée avec une chaine de caractères et un entier en paramètres :

Sub SP
    Dim Connection As New ADODB.Connection
    Dim Command As New ADODB.Command
    Dim Jeu As New ADODB.Recordset
    Dim Param1, Param2 As ADODB.Parameter
    Dim Tableau As Variant

  Connection.ConnectionString = "driver={MySQL ODBC 3.51 Driver};server=MonServeur;uid=MonCompte;pwd=MonMotDePasse;database=MaBase"
  Connection.Open
  Command.ActiveConnection = Connection
  Command.CommandText = "MaProcédureStockée"
  Set Param1 = Command.CreateParameter("@Nom", adVarChar, adParamInput, 200)
  Set Param2 = Command.CreateParameter("@Age", adInteger, adParamInput, 10)
  Command.Parameters.Append Param1
  Command.Parameters.Append Param2
  Param1.Value = "MICHU"
  Param2.Value = 49
  Set Jeu = Command.Execute
  If Jeu.BOF = False And Jeu.EOF = False Then
    Tableau = Jeu.GetRows
  End If
  MsgBox Tableau(0,0)
End Sub

Importer un fichier texte dans une BDD modifier

Le fichier doit être au format PC DOS. La commande dépend ensuite du SGBD, par exemple avec MS-SQL c'est BULK INSERT.

Références modifier

  1. http://www.techonthenet.com/excel/formulas/getattr.php
  2. http://groupes.codes-sources.com/article-recherche-macro-excel-lister-fichiers-dossier-246973.aspx
  3. http://msdn.microsoft.com/fr-fr/library/6zwyt2y8.aspx
  4. http://www.commentcamarche.net/contents/1174-objet-filesystemobject-fso
  5. http://www.techonthenet.com/excel/formulas/setattr.php
  6. http://www.clubic.com/forum/programmation/fonction-recherche-vba-excel-id401170-page1.html
  7. http://msdn.microsoft.com/en-us/library/office/aa195732%28v=office.11%29.aspx
  8. http://msdn.microsoft.com/fr-fr/library/office/ff196143%28v=office.15%29.aspx
  9. https://technet.microsoft.com/fr-fr/library/ms131291(v=sql.110).aspx




Gestion des droits et répertoires

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 14
Leçon : Macros-commandes VBA
Chap. préc. :Lectures et écritures
Chap. suiv. :Devenez un connaisseur
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Cet article explique comment, à l'aide des interfaces de programmation de Windows (API Win32), il est possible de lire chaque entrée de la liste de contrôle d'accès (une ACE - Access Control Entry - de l'ACL - Access Control List) d'un répertoire local ou distant avec VBA.

Autrement dit, pour un répertoire donné, on affiche chaque utilisateur (ou groupe, ou alias) et les droits d'accès associés à cet utilisateur.

Description modifier

Dans cet exemple, plusieurs interfaces de programmation de Windows sont utilisées :

  • GetFileSecurity (...)  : Récupère le descripteur de sécurité d'un fichier (ou répertoire).
  • GetSecurityDescriptorDacl (...)  : Récupère le descripteur de la liste de contrôle d'accès.
  • GetAclInformation (...)  : Récupère la liste de contrôle d'accès.
  • GetAce (...)  : Récupère une entrée de la liste (un SID, et un drapeau représentant les droits.
  • LookupAccountSid (..)  : Récupère un nom de domaine, de compte et type de compte (utilisateur, alias, groupe, ...) à partir d'un SID (identificateur système).

Dans un premier temps on indique un répertoire (soit local : c:\tmp soit distant : \\serveur1\partage1). Ensuite on récupère pour ce répertoire, et à l'aide des API précédentes la liste de contrôle d'accès associée. Pour chaque entrée de la liste, on analyse le drapeau des droits, puis on récupère le nom du compte que l’on affiche avec une boîte de dialogue simple (message box).

Déclarations préalables modifier

Fonction GetFolderInfo modifier

NB: Pour sélectionner un répertoire, il est possible d’utiliser l'exemple suivant : mvps.org

Liens externes modifier



Devenez un connaisseur

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 15
Leçon : Macros-commandes VBA
Chap. préc. :Gestion des droits et répertoires
Chap. suiv. :Devenez un expert
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Introduction modifier

Après avoir utilisé l’enregistreur de macro, l'utilisateur s'aperçoit vite des limites de ce procédé de mémorisation de commandes qui a cependant les qualités de rapidité et de simplicité. Il va lui manquer très vite des techniques pour évaluer des bornes, effectuer des calculs conditionnels, des boucles de traitement... La programmation VBA via Visual Basic Editor (VBE) va lui apporter des solutions à ces manques.

Plus intéressante mais également plus compliquée, cette programmation nécessite de connaître les spécificités du langage VBA que ce soit dans la déclaration de variables, les types de données, les opérateurs, les instructions,… Son apprentissage est compliquée par le fait que le nombre d’objet s sur lesquels le VBA peut agir est important. Non ! Ne nous quittez pas tout de suite, avec quelque rudiments de langage VBA que nous verrons par la suite, il vous sera possible de créer des macros complexes. En effet, la liaison enregistreur-programmation sert aux utilisateurs les plus chevronnés pour réaliser leurs macros. Nous verrons ensemble comment il est possible de réussir avec un minimum de vocabulaire VBA. La programmation VBA est également intéressante pour réaliser des tâches que l’on ne peut pas faire avec le tableur en lui-même comme par exemple ouvrir une boite de dialogue ou créer un formulaire.

La programmation modifier

Nous allons maintenant vous présenter la partie technique de la programmation en VBA. Mais tout d’abord, qu’est-ce que la programmation ? La programmation est une branche de l’informatique qui sert à créer des programmes. Tout ce que vous possédez sur votre ordinateur sont des programmes : votre navigateur Web (Internet Explorer, Firefox, Opera, etc.), votre système d’exploitation (Windows, GNU/Linux, etc.), votre lecteur MP3, votre logiciel de discussion instantanée, vos jeux vidéos, etc.

Ce cours se penchera plus particulièrement sur la programmation au sein du programme Excel, puisqu’il utilise le Visual Basic qui est un outil développé par Microsoft pour développer facilement des applications fonctionnant sous Microsoft Windows ©.

Visual Basic est, comme son nom l'indique, un outil visuel permettant de créer sans notion de programmation l'interface graphique (GUI - Graphical User Interface) en disposant à l'aide de la souris des éléments graphiques (boutons, images, champs de texte, menus déroulants,...).

L'intérêt de ce langage est de pouvoir associer aux éléments de l'interface des portions de code associées à des événements (clic de souris, appui sur une touche, ...). Pour cela, Visual Basic utilise un petit langage de programmation dérivé du BASIC (signifiant Beginners All-Purpose Symbolic Instruction Code, soit code d'instructions symboliques multi-usage pour les débutants). Le langage de script utilisé par Visual Basic est nommé à juste titre VBScript, il s'agit ainsi d'un sous-ensemble de Visual Basic.

Les données usuelles modifier

Les nombres VBA modifier

Définition : les nombres sont des variables contenant des chiffres et des séparateurs décimaux. Les deux types les plus usuels de nombres sont les suivants :

  • Entier : Integer
  • Décimal : Double

Dim monNombre As Double monNombre est une variable destinée à contenir des nombres réels (par exemple "10.455")

Les chaines de caractères VBA modifier

Définition : les chaînes de caractères sont des variables contenant des suites de caractères (alphabétiques, numériques ou spéciaux)

Dim maChaine As String maChaine est une variable destinée à contenir des caractères (par exemple "bonjour, 10 bises à vous tous ♥")

Les dates VBA modifier

Définition : les dates sont des variables contenant des horaires ou des dates

Dim maDate As Date

maDate est une variable destinée à contenir des nombres et des séparateurs calendaires (par exemple "10/10/2013 14:55")

Les fonctions de détermination :

MsgBox Now ' Date et heure actuelles
MsgBox Date ' Date du jour
MsgBox Time ' Heure, minute et seconde actuelles

Les constantes VBA modifier

Définition : les constantes sont des éléments nommés contenant des valeurs qui n'évolueront pas durant le déroulement du programme. Le nom de la constante est utilisé à la place de la valeur qui lui est attribuée ; elle est en général déclarée pour être utilisée par plusieurs programmes, procédures ou fonctions.

CONST pour déclarer une variable constante

Exemple : CONST Pi=3.14, une instruction contenant Pi utilisera la valeur de la constante pour effectuer son calcul >> 
périmètre = 2 * Pi * rayon >> périmètre = 2 * 3.14 * rayon 

Les mots clés usuels du langage BASIC modifier

Les mots-clés sont des mots réservés par le langage VBA ; ils ne doivent JAMAIS être employés pour nommer vos variables, constantes, procédures, fonctions et objets.

Exemple : Dim, As, If, Then, Else, Endif, For, Next, While, Function, Sub, End, With, etc.

Les objets usuels de la bibliothèque Excel modifier

Un objet est une "entité" informatique qu'un programme informatique peut manipuler. Un objet est une entité interne ou externe à VBA.

Les objets usuels sont des mots réservés par la bibliothèque EXCEL, ils ne doivent JAMAIS être employés par le développeur pour nommer ou déclarer de nouvelles variables, constantes. L'instruction SET permet d'affecter une référence à un objet, le type d'un objet est objet.

Le cas le plus évident est une cellule de classeur. Pour la manipuler, VBA utilise une instance de la classe Range.

Exemple d'objets usuels : Application, Selection, ActiveCell, ActiveSheet, ActiveWorkBook, Range, Cells

  • Application représente l’application en cours d’utilisation (Excel, Word, Access);
  • Workbooks représente les classeurs Excel (Documents, les documents Word);
  • Sheets pour les feuilles du classeur;
  • Cells pour les cellules;
  • Range pour une plage de cellules;

Utilisation VBA des Objets modifier

  • Sheets("maFeuille") ou Sheets(2) désigne une seule feuille de l’ensemble (collection) de feuilles
  • Range("B2") désigne la cellule B2 (on peut écrire [B2] à la place de Range("B2"));
  • Cells(3) désigne la 3e cellule;
  • Cells(2,3) désigne la cellule située à la 2e ligne et 3e colonne (dans une feuille de calcul, c’est la cellule $C$2)
  • Workbooks("monClasseur").Sheets("maFeuille").Cells(3) désigne la 3e cellule de la feuille maFeuille du classeur monClasseur

Les propriétés des objets modifier

Il s'agit de nombres (dimensions, valeurs), de textes (adresses, nom), ou de variables booléennes (le fait d’être visible ou non, d’être verrouillé ou non) relatifs à un objet. Par exemple : le nom (Name), le chemin d'accès (Path) d'un fichier, le texte (Caption) d'un contrôle, l'adresse d'une cellule (Address), la valeur (Value) d'une cellule ou d'une barre de défilement, la visibilité (Visible), le verrouillage (Enabled), ...

Une propriété d'un objet peut être lue :

 'affiche la valeur de la cellule A1
 MsgBox(Cells(1,1).Value) 
 'affiche le chemin du classeur Excel actif
 MsgBox(ActiveWorkbook.Path)

Une propriété d'un objet peut être modifiée :

 'écrit 10 dans la cellule A1 
 Cells(1,1).Value = 10  
 'renomme "nouveauNom" la première feuille 
 Sheets(1).Name = "nouveauNom"  
 'cache le bouton CommandButton1
 CommandButton1.Visible = False 
 Label1.Caption = "Bonjour"

Les procédures usuelles modifier

Une procédure permet d'exécuter une série d'instructions. C'est un sous-programme qui s'exécute par simple appel dans le corps du programme principal. Cette notion de sous-programme est généralement appelée procédure dans la plupart des langages de programmation, VBA l'appelle indifféremment procédure ou macro. En voici un exemple simple :

 Sub pDixAuCarre ()
  Dim resultat As Integer
  'Cette procédure pDixAuCarre affiche la valeur 100
  resultat = 10 ^ 2
  MsgBox resultat
 End Sub

Les fonctions usuelles modifier

Une fonction est une procédure effectuant une action et renvoyant une valeur en sortie. En voici un exemple simple :

 Function fDixAuCarre() As Integer
  'Cette fonction dixAuCarre renvoie la valeur 100
  fdixAuCarre = 10 ^ 2 
 End Function

Les instructions de débranchement modifier

Après exécution d'une ligne d'instruction, un programme VBA passe séquentiellement à l'exécution de la ligne suivante :

 Range("E6").Select
 a = 12
 ActiveCell.FormulaR1C1 = a
 Range("E7").Select
 b = 13
 ActiveCell.FormulaR1C1 = b
 Range("E8").Select
 ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"

Le programme commence par sélectionner la cellule E6, puis affecter la valeur 12 à la variable a, puis exécute toutes les lignes suivantes jusqu'à l'affectation d'une formule dans la cellule sélectionnée. Ce qui en limiterait très vite l’intérêt s'il n'existait pas des possibilités de débranchements. Il faudra donc, pour rompre ce chemin naturellement linéaire, utiliser des instructions spéciales :

  • Débranchements directs ⇒ Goto (se déplacer à une étiquette), Exit (fin de boucle ou de fonction), End (fin de programme), Stop.
  • Instructions conditionnelles ⇒ If, Then, Else, ElseIf, End If.
  • Instructions de boucles ⇒ For... Next, For Each... Next, While... Wend, Do... Loop.
  • Appels de procédures ⇒ Call.
  • Appels de fonctions ⇒ prixToutesTaxesComprises = prixHorsTaxe + functionCalculerTVA(prixHorsTaxe).

Les instructions conditionnelles modifier

Ces instructions sont utilisées lorsque le traitement à appliquer dépend d'une condition (d'un résultat de test), alors la suite séquentielle naturelle des instructions est rompue grâce à ce test.

Début de l'exemple
Fin de l'exemple


⇒ L'exemple affiche "BRAVO !!!" si la valeur de la cellule E8 est 20

Début de l'exemple
Fin de l'exemple


⇒ L'exemple affiche "MILLE BRAVO !!!" si la valeur de la cellule E8 est 20 sinon affiche "PEUT ÊTRE BRAVO !!!"

Les Boucles modifier

En programmation, une boucle, aussi appelée itération, permet d'effectuer une série d'actions de façon répétitive.

Il existe plusieurs solutions pour créer une boucle :

  • For Each / Next: Boucle sur chaque objet d'une collection.
  • For Next: Répète une action le nombre de fois spécifié par un compteur.
  • While Wend: Répète une action tant qu'une condition est vraie.
  • Do Loop: Itération pendant ou jusqu'à ce qu'une condition soit remplie.

Les Instructions de boucles avec bornes connues modifier


La boucle For ... Next modifier

La boucle de type For ... Next permet de répéter un nombre de fois défini un bloc d'instructions. Les boucles utilisent une variable qui est incrémentée ou décrémentée à chaque répétition. Vous devez spécifier une valeur de début [Numéro de départ] et une valeur de fin [Numéro d'arrivée]. La variable [compteur] va ensuite être incrémentée ou décrémentée à chaque itération.

Début de l'exemple
Fin de l'exemple


⇒ L'exemple écrit la table des trois dans la plage de cellule "A1:A10". La variable i s'incrémente de 1 à chaque tour de boucle, c'est-à-dire prend successivement les valeurs 1, 2, 3, … , 9, 10. Arrivée à la valeur 10, la boucle s'arrête.

Ce second exemple boucle sur les cellules du de la feuille en cours :

 Sub boucleCellules()
  Dim i As Integer    
  'La variable i va successivement prendre les valeurs 3 à 10
  For i = 3 To 10
   'Écrit le libellé COUCOU successivement dans les cellules A3:A10
   Cells(i, 1) = "COUCOU !!!"
  Next i
 End Sub

Boucles de type ForEach ... Next modifier

Elles permettent de répéter un bloc d'instructions pour chacun des objets appartenant à un ensemble : elles utilisent une variable objet qui sélectionne un par un les objets de l’ensemble (à chaque tour). Le principe de fonctionnement de l'instruction For Each Next consiste à boucler sur tous les objets d'une collection spécifique. Si la collection ne contient pas d'objet ou quand tous les objets ont été parcourus, la boucle se termine et l'exécution continue sur la ligne de code, juste après l'instruction Next.

Début de l'exemple
Fin de l'exemple


⇒ L'exemple colorie la police de chaque cellule de la plage sélectionnée. La variable cellule parcourt une par une toutes les cellules sélectionnées et leur donne un fond vert. Arrivée à la dernière cellule de la sélection, la boucle s'arrête.

Ce second exemple boucle sur les classeurs ouverts dans l’application Excel:

			
 Sub boucleClasseurs()
  'Définit une variable qui va représenter un classeur à chaque itération.
  Dim Wb As Workbook
  'Boucle sur chaque classeur de l’application Excel
  For Each Wb In Application.Workbooks
   'Écrit le nom de chaque classeur dans la fenêtre d'exécution
    Debug.Print Wb.Name
  Next Wb
 End Sub

Exemple commenté modifier

Cette macro permet la coloration du fond d'une cellule quand on rencontre une cellule contenant le mot "fauteuil" sur la ligne parcourue

Sub PeindreFondMarronLesFauteuils()
'
' Cette macro peint en marron les lignes comprenant le libellé "fauteuil"
'
    Dim i As Integer
    Dim produit As String
    For i = 2 To 9
        produit = Range("B" & i).Value
        If (produit = "fauteuil") Then
            Range("D" & i).Select
            With Selection.Interior
                .Color = -33333333
                .TintAndShade = 0
            End With
        End If
    Next i
End Sub

On peut ici observer une macro dont la fonction est de peindre en marron le fond d'une cellule située en colonne D, avec pour référence un mot situé en colonne B, dans un intervalle de lignes compris entre 2 et 9

 
Résultat de la Macro

Voici la signification de chacune des lignes de cette macro :

 Sub PeindreFondMarronLesFauteuils() 'Voici le début et le nom de la macro
 '
 ' Cette macro peint en marron les lignes comprenant le libellé "fauteuil" 'Voici le commentaire général de la macro
 '
    Dim i As Integer 'Cette déclaration permet de déclarer une variable i comme un nombre entier
    Dim produit As String 'Cette déclaration permet de déclarer une variable chaine de caractères
    For i = 2 To 9 'On fait varier successivement la variable i entre 2 et 9 à chaque tour de boucle 
        produit = Range("B" & i).Value 'Ici on affecte la valeur de chaque ligne de la colonne B à la variable produit
        If (produit = "fauteuil") Then 'Ici on teste l'égalité entre la valeur de la variable produit avec le libellé "fauteuil"
            Range("D" & i).Select 'Si l'égalité est constatée on sélectionne la colonne D de la même ligne
            With Selection.Interior 'Se positionne sur le fond de la cellule sélectionnée
                .Color = -33333333 'on colore le fond avec la couleur marron
                .TintAndShade = 0 'on règle le contraste de la cellule
            End With 'On sort du fond de la cellule sélectionnée
        End If 'On sort de la condition si, donc de l'égalité avec le libellé "fauteuil"
    Next i 'on retourne en début de boucle en demandant la prochaine valeur de i
 End Sub 'Il s'agit de la fin de la macro

Annexes modifier

Bibliographie modifier

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

Liens internes modifier

Liens externes modifier




Devenez un expert

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 16
Leçon : Macros-commandes VBA
Chap. préc. :Devenez un connaisseur
Chap. suiv. :Procédures et événements automatiques
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Introduction modifier

Le langage ou code VBA contient différentes entités : des données, des modules, des classes, des structures, des instructions, des objets, des procédures, des fonctions, … Ces entités représentées par des lignes de texte s’organisent de façon purement hiérarchique

  • les classeurs contiennent des projets (1 projet par classeur)
  • les projets contiennent les modules de code, de classe, de feuille, ou de formulaire, regroupés par catégories
  • les modules contiennent des directives, des déclarations, des fonctions, des procédures
  • les procédures et fonctions contiennent des instructions (lignes de code)

Les contenants de code VBA modifier

Le code VBA est saisi et stocké dans 2 types de récepteurs sous un format uniquement textuel : des feuilles et des modules

  • Feuille : objet Excel accueillant les procédures particulières à une feuille de calcul Excel, cet objet contient le plus souvent des procédures et fonctions concernant des objets s’appliquant à lui-même.
  • Module de code standard : objet VBA accueillant les procédures générales, le plus souvent contiennent des fonctions dites "utilitaires".
  • Module formulaire : objet VBA contenant les procédures événementielles propres au formulaire traité. Il peut également contenir des procédures générales (à éviter). Ex : une procédure de traitement de date serait stockée dans un module standard pour pouvoir être utilisée dans tous les formulaires concernés.
  • Module de classe : objet VBA contenant les données et procédures pour un objet (on parle alors de propriétés et de méthodes). Ex : un objet compte en banque qui décrit ses données (mouvement, solde, …) et les opérations sur ses données (retrait, dépôt, virement, …).

Les données des experts modifier

Les variables modifier

Les variables permettent de stocker toutes sortes de données et de faire évoluer les valeurs notamment lors de l’utilisation de boucles. Les variables sont généralement déclarées en début de procédure puisqu’une variable doit être définie avant d’être utilisée.

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

Une variable est définie selon l’utilisation que l’on va en faire. Le type de variable choisi était auparavant fondamental au niveau de la mémoire utilisée par l’ordinateur, mais de nos jours ce problème n’est plus d’actualité car les ordinateurs ont de grandes mémoires.

Les nombres spéciaux VBA modifier

Définition : les nombres sont des variables contenant des chiffres et des séparateurs décimaux, il y a 2 types expert de nombres

  • Entier Long : Long
  • Booléen : Boolean

Dim monNombre As Boolean monNombre est une variable destinée à contenir des nombres booléens(par exemple "0")

Les tableaux VBA modifier

Définition : les tableaux sont des variables contenant d'autres variables de même type

Dim monTableau(10) As String monTableau est un tableau contenant 10 éléments dont le type est une chaîne de caractères.

Les constantes VBA intégrées modifier

Définition : les constantes intégrées sont fournies par les applications OFFICE (Word, Excel, …) ou le langage Visual Basic :

Si la constante commence par vb alors c’est une constante intégrée à VBA Si la constante commence par xl alors c’est une constante intégrée à EXCEL Si la constante commence par wd alors c’est une constante intégrée à WORD …

Exemple : vbCrLf = retour à la ligne mais on ne peut pas écrire vbCrLf = Chr(13) + Chr(14)

Les mots clés des experts modifier

Les mots-clés des experts permettent de programmer des problématiques délicates ou complexes

Exemple : ReDim, Static, Do, Loop, Break, On, Continue, Set, etc.

Les objets experts de la bibliothèque Excel modifier

Les objets des experts permettent de programmer des problématiques délicates ou complexes

Exemples : Workbooks, Worksheets, Sheets, Rows, Columns, Object, Windows, Font , Interior , Offset, Calendar, Charts, Names, CommandBars, UserForms, ...

Les objets classeurs modifier

Ouvrir un fichier avec trois feuilles déjà remplies pour y exécuter la macro :

Sub Feuilles()
    Dim Source, Destination As Excel.Workbook
    Set Source = ActiveWorkbook
    
    ' Création
    Workbooks.Add
    Workbooks.Add After:=Sheets(Sheets.count) ' à la fin
    Set Destination = ActiveWorkbook
    ' Copie d'une cellule dans un autre classeur
    Destination.Sheets(1).Cells(1, 1) = Source.Sheets(1).Cells(1, 1) ' Avec bordures
    Destination.Sheets(1).Cells(1, 2) = Source.Sheets(1).Cells(1, 2).Value ' Sans bordure
    ' Copie d'une feuille
    Source.Sheets(1).Copy After:=Sheets(1)
    ' Copie d'une feuille dans un autre classeur
    Source.Sheets(1).Copy After:=Destination.Sheets(1)
    ' Déplacement d'une feuille dans un autre classeur
    Source.Sheets(1).Move After:=Destination.Sheets(1)
    ' Suppression
    Source.Sheets(1).Delete
    ' Parcourir toutes les feuilles d'un fichier
    For Each workheets In ActiveWorkbook.Worksheets
        MsgBox workheets.Name
    Next
End Sub

Les objets feuilles modifier

Il faut définir une première plage, sur laquelle trier une deuxième :

Sub classer()
    ' Tri sur la colonne C, du tableau AJ
    Plage1 = "C" & EnteteLigne & ":C" & LigneFin
    Plage2 = "A" & EnteteLigne & ":J" & LigneFin

    With ActiveWorkSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range(Plage1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range(Plage2)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Les objets cellules modifier

Ici on simule un copier-coller :

 ' Copie de la première case dans la deuxième
 Range("A1") = Range("B1")
 ' ou
 Cells(1,1) = Cells(1,2)
 ' ou
 Range("A1").copy
 Range("B1").paste
 Range("A2:C10").copy

 Cells(l, c).Font.Size = 10    ' Taille du texte
 Cells(l, c).Font.Bold = True  ' Mise en gras
 Cells(l, c).HorizontalAlignment = xlCenter    ' Alignement
 Cells(l, c).Font.Color = vbBlack    ' Couleur de la police
' ou
 Cells(l, c).Font.Colorindex = 2
 Cells(l, c).Interior.ColorIndex = 4    ' Couleur de la cellule
 Cells(l, c).Numberformat("$#,##0.00")    ' Format monétaire


For Each c In ActiveCell.CurrentRegion.Cells
   Cells(x + c.Row, y + c.Column).value = c.value
Next c


Les objets lignes modifier

Sub Lignes()
   ' Insertion d'une ligne 2 vierge
   Rows(2).Insert
   ' Insertion après la ou les cellules sélectionnées
   Selection.EntireRow.Insert
   ' Suppression
   Rows(2).Delete
   ' Copie de toutes les lignes jusqu'à la fin du tableau en cours
   ActiveCell.CurrentRegion.Copy
   ' Duplique la ligne L juste en dessous
   Rows(L + 1).Insert Shift:=xlDown
   Rows(L).Copy
   Rows(L + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Les objets colonnes modifier

Sub Colonnes()
   ' Insertion colonne 2
   Columns(2).Insert
   ' Insertion après la ou les cellules sélectionnées
   Selection.EntireColumn.Insert
   ' Suppression
   Columns(2).Delete
End Sub

Les objets liens modifier

La macro suivante placée dans une feuille crée automatiquement un hyperlien à chaque fois que l’on tape un mot dans une de ses cellules, vers sa définition du Wiktionnaire :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not IsNull(Target.Cells) And Not IsEmpty(Target.Cells) And TypeName(Target.Cells) = "Range" And Len(Trim(Target.Cells)) > 0 Then
       ActiveSheet.Hyperlinks.Add Anchor:=Target.Cells, _
        TextToDisplay:=Target.Cells.Value, _
        Address:="https://fr.wiktionary.org/w/index.php?title=" & Target.Cells.Value
    End If
End Sub

Les objets Word modifier

La macro suivante placée dans une feuille ouvre automatiquement un fichier Word et écrit une nouvelle phrase :

Sub Edition()
dim ObjetWord as object
    Set ObjetWord = CreateObject("Word.Application")
    ObjetWord.Visible = True
    'Ouverture d'un fichier existant
    ObjetWord.Documents.Open App.Path & "\document.docx"                                         
    'Création d'un nouveau fichier
    ObjetWord.Documents.Add    
    'Ajout de texte ObjetWord.Selection.TypeText Text:="Texte écris."                                                                  
    ObjetWord.Selection.TypeText "J'écris une nouvelle phrase depuis EXCEL sur WORD."             
    ObjetWord.Selection.TypeText Text="J'écris une nouvelle phrase depuis EXCEL sur WORD."             
    'Sauvegarder
    NomDuDocumentWord.Save                                  
    'Imprimer
    ObjetWord.PrintOut 
    'Quitter                                     
    ObjetWord.Quit
End Sub

Les objets Outlook modifier

La fonction createitem de l’objet Outlook génère les différentes entités du logiciel[1] :

  1. createitem(0) : un mail.
  2. createitem(1) : un RDV.
  3. createitem(2) : un contact.
  4. createitem(3) : une tâche.
  5. createitem(4) : un journal.
  6. createitem(5) : une note.
  7. createitem(6) : un post.

L'exemple ci-dessous envoi un email contenant un hyperlien et une pièce jointe :

Sub EnvoyerMail()
    Dim Outlook, Message As Object
    Set Outlook = CreateObject("Outlook.Application")
    Set Message = ObjOutlook.createitem(0)
    With Message
        .To = "moi@domaine.com"
        .Subject = "Lien vers le cours de VBA"
        .HTMLBody = "Bonjour,<br />Voici le lien vers le cours de VBA : <br /><A HREF=""http://fr.wikiversity.org/wiki/Visual_Basic"">"ici"</A>.<br />Cordialement."
        .Attachments.Add (cheminPJ)
        .Display (True)
        .Send
    End With
End Sub

Les objets ActiveX modifier

On peut facilement insérer dans une feuille de calcul Excel ou dans une feuille Word divers objets (des "contrôles")comme

  • des boutons :
     
    bouton
  • des boutons d'options, des cases à cocher :
     
    Option button
  • des zones de texte modifiables (textbox):
     
    Figure
  • des barres de défilement (scrollBar):
     
    Barre crée pour aller de droite à gauche dans un tableur
  • des boutons toupies (SpinButton), des zones de liste, des listes déroulantes :
     
    Barre crée pour aller de droite à gauche dans un tableur
  • des zones de texte (label) :
     
    Texte "m"

Il suffit de cliquer sur l’icône "boîte à outils" de la barre d’outils VBA (ou bien Affichage / Barres d’outils / Commandes), de sélectionner le contrôle souhaité et de le glisser via la souris à l’endroit désiré.

On peut également utiliser tous ces "contrôles" dans une boite de dialogue (Userform) que l’on peut créer dans VBA par formulaire / Userform, puis faire apparaître à l'exécution d'une macro par: Userform("truc").Show et disparaître par Userform("truc").Hide.

Les procédures et les fonctions expertes modifier

Usages complexes modifier

  • Les paramètres ou arguments
  • Les fonctions prédéfinies VBA
  • Disponibilité des fonctions VBA sous Excel
  • Les fonctions récursives

Les arguments des procédures modifier

Définition : les arguments passés aux procédures sont aussi nommés paramètres, ce sont des valeurs nécessaires au traitement de la procédure. Exemple : après l'appel de la procédure ⇒ afficherMessageCible "ToiKeuJème", le résultat affiché à l’écran est : "Bonjour à ToiKeuJème"

 Sub afficherMessageCible (cible As String)
  MsgBox "Bonjour à " & cible
 End Sub

Les arguments et le résultat des fonctions modifier

Définition : les arguments passés aux fonctions fonctionnent comme pour les procédures, le résultat est rangé sous le nom de la fonction. Exemple : après l'appel de la fonction ⇒ monBenefice = calculerBenefice(150,100), la variable monBenefice prend la valeur 150

 Function calculerBenefice(recettes As Double, depenses As Double) As Double
  calculerBenefice = recettes - depenses
 End Function

Les instructions de débranchement modifier

Les instructions conditionnelles complexes modifier

Début de l'exemple
Fin de l'exemple

⇒ L'exemple affiche "MILLE BRAVO !!!" si la valeur de la cellule E8 est 20 sinon affiche "PETIT BRAVO !!!" si la valeur de la cellule E8 est supérieure à 10, sinon affiche "PAS BRAVO DU TOUT !!!"

Les instructions de distinctions de cas modifier

Cette instruction est utilisée lorsque le nombre de cas à tester(comme ci-dessus) devient important rendant difficiles les instructions employant des conditionnelles imbriquées (if...if...)

Selon une valeur de variable
  En cas d'une valeur alors effectuer un traitement 
  En cas d'une autre valeur alors effectuer un autre traitement
  Dans tous les autres cas alors effectuer le traitement encore un autre traitement
Fin Selon
Début de l'exemple
Fin de l'exemple


⇒ Selon la valeur de la cellule E8 l'exemple affiche soit "MILLE BRAVO !!!" soit "PETIT BRAVO !!!" soit "PAS BRAVO DU TOUT !!!"

  Impossible de placer plusieurs conditions après un case. Par exemple Case False and False peut très bien être considéré à tort comme Case True.


Les Instructions de boucles avec bornes inconnues modifier

Les boucles non bornées le plus souvent utilisées sont les boucles de type « « While … Wend » », elles permettent de répéter un nombre de fois indéfini au départ un bloc d'instructions, elles utilisent une condition qui est testée au début de chaque répétition.

La boucle While / Wend modifier

L'instruction While Wend répète une action tant qu'une condition est vraie.

 While [condition]
  [actions]
 Wend

Si la condition est vraie, les actions indiquées dans la procédure sont effectuées. Quand l'instruction Wend est atteinte, la procédure revient sur l'instruction While et la condition est de nouveau vérifiée. Si la condition est toujours vraie, le processus est répété. Si la condition est fausse, l'exécution passe directement à la première ligne de code qui suit l'instruction Wend.

Début de l'exemple
Fin de l'exemple


La boucle Do While / Loop modifier

Cette boucle fonctionne de la même manière que While Wend (tant que la condition est vraie, la boucle est exécutée) :

 Do While [condition]
  'Instructions
 Loop

La boucle Do / Loop Until modifier

Cette boucle fonctionne de la même manière que While Wend, exceptée la une condition qui est testée à la fin de chaque répétition.

 Do [condition]
  'Instructions
 Loop Until

Le corps de la boucle (instructions) est alors toujours exécuté au moins une fois.

REGEX modifier

Nécessite de cocher "Microsoft VBScript Regular Expressions 5.5" dans "Outils\Références".

Expressions rationnelles courantes
Caractère Type Explication
. Point n’importe quel caractère
[...] classe de caractères tous les caractères énumérés dans la classe
[^...] classe complémentée Tous les caractères sauf ceux énumérés
^ circonflexe marque le début de la chaine, la ligne...
$ dollar marque la fin d'une chaine, ligne...
| barre verticale alternative - ou reconnaît l'un ou l'autre
(...) parenthèse utilisée pour limiter la portée d'un masque ou de l'alternative
* astérisque 0, 1 ou plusieurs occurrences
+ le plus 1 ou plusieurs occurrence
? interrogation 0 ou 1 occurrence
  • .Test() : renvoie vrai si le pattern est trouvé dans la chaine.
  • .Execute() : renvoie toutes les positions du pattern dans un tableau.
  • .Replace() : remplace le pattern par le paramètre deux.

Recherche modifier

Obtenir les emplacements dans une chaine :

Sub RegexRecherche()
    Dim RegEx As RegExp
    Set RegEx = New RegExp
    Dim matches As IMatchCollection2
    Dim match As Variant
    With RegEx
        .IgnoreCase = True
        .Global = True 'True matches all occurances, False matches the first occurance
        .Pattern = "[0-9]+"
    Set matches = .Execute("1 test 2 regex")
    End With
    For Each match In matches
        MsgBox ("Position : " & match.FirstIndex)
    Next
    ' ou
    MsgBox matches.Item(0).Value
End Sub

Extraire un mot modifier

Public Sub RegexExtraction()
    Dim RegEx As RegExp
    Set RegEx = New RegExp
    With RegEx
        .IgnoreCase = True
        .Global = False
        .Pattern = "[a-z]* Wikibooks"
    End With
    chaine$ = "Test regex VB pour Wikibooks francophone."
    Set matches = RegEx.Execute(chaine$)
    MsgBox (Replace(matches(0).Value, " Wikibooks", ""))
    ' Affiche : "pour"
End Sub

Remplacement modifier

Supprimer toutes les balises HTML :

Public Sub RegexChaine()
    Dim RegEx As RegExp
    Set RegEx = New RegExp
    With RegEx
        .IgnoreCase = True
        .Global = False
        .Pattern = "<.*>(.*)<.*>"
    End With
    chaine$ = "Test regex VB pour <balise1>Wikibooks</balise1> francophone."
    chaine$ = RegEx.Replace(chaine$, "$1")
    MsgBox (chaine$)
    ' Affiche : "Test regex VB pour Wikibooks francophone." (sans les balises)
End Sub

Références modifier

  1. http://msdn.microsoft.com/en-us/library/office/aa255722%28v=office.10%29.aspx
Début de l'exemple
Fin de l'exemple


Annexes modifier

Bibliographie modifier

  • Jensen O.G., 2003, Initiation à la programmation VBA Word, KNOWWARE, Ecuelles
  • Greg Harvey et John Walkenbach, 2013, Excel 2013 et VBA Pour Les Nuls, FIRST, Hoboken
  • Amelot M., 2011, VBA Excel 2010, ENI, St Herblain

Liens internes modifier

Liens externes modifier




Procédures et événements automatiques

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 17
Leçon : Macros-commandes VBA
Chap. préc. :Devenez un expert
Chap. suiv. :Débogage
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Introduction modifier

Plusieurs moyens permettent d'exécuter une procédure Sub. L'une d'entre elles consiste à faire en sorte qu'elle le soit automatiquement. Dans cette leçon, vous découvrirez tout ce qu’il faut savoir pour exploiter ces fonctionnalités particulièrement puissantes. Nous vous expliquerons comment préparer le terrain pour qu'une macro soit déclenchée lorsqu'un événement se produit.

De quels types d’événements est-il question ici ? C'est une bonne question. Un événement est fondamentalement quelque chose qui se produit dans Excel. Voici quelques exemples :

  • l'ouverture ou la fermeture d'un classeur ;
  • l'activation ou la désactivation d'une fenêtre ;
  • l'activation ou la désactivation d'une feuille de calcul
  • l'entrée de données dans une cellule, ou la modification d'une cellule ;
  • l'enregistrement du classeur ;
  • le calcul dans la feuille ;
  • un clic sur un objet (un bouton par exemple) ;
  • l'appui sur une touche ou sur une combinaison de touches ;
  • le double clic dans une cellule ;
  • la survenance d'une heure du jour particulière ;
  • une erreur.

La plupart des programmeurs ne se soucient pas de la majorité de ces événements. Vous devez cependant savoir qu’ils existent, car, un jour ou l'autre, ils pourront vous être utiles. Dans cette leçon nous utiliserons les événements les plus communément utilisés. Pour faire simple, nous n'en aborderons que deux : les classeurs et les feuilles de calcul.

Au terme de cette leçon vous saurez répondre aux questions suivantes :

  • quels sont les événements qui peuvent déclencher une exécution ?
  • où faut-il placer le gestionnaire d'événement dans le code VBA ?
  • comment exécuter une macro à l'ouverture/fermeture d'un classeur ou quand un classeur/feuille de calcul est activé ?

Caractéristiques des procédures d'évenements modifier

Un événement est toujours associé à un objet ou à une collection d'objets spécifique. Nous avons défini une procédure événementielle de niveau "feuille de calcul", attachée à la première feuille du classeur. Si nous voulions que cette procédure se déclenche quand l'utilisateur sélectionne de nouvelles cellules dans n’importe quelle feuille du classeur, il faudrait créer une procédure événementielle de niveau "classeur", s'appliquant à l’ensemble de ses feuilles.

La feuille de calcul représente ici l’objet associé à la procédure événementielle. Il peut s'agir soit d'un objet existant, créé par l'utilisateur, soit d'un objet créé par programmation.

Sous Excel, il existe cinq types principaux d'objets associés.

  • Feuille de calcul
  • Feuille graphique
  • Classeur
  • Application (instance d'Excel)
  • Boîte de dialogue (UserForm)

Les quatre premiers types d'objets sont spécifiques à Excel, alors que les UserForms peuvent s'utiliser dans toutes les autres applications (Access, Word...) intégrant VBA.

À ces types d'objets, il faut ajouter ceux qui sont créés et définis par programmation par l'intermédiaire de modules de classe.

Pourquoi écrire une procédure d'événement ? modifier

Laissez nous vous convaincre de l'utilité de ces procédures.

Voici un exemple : supposons que vous ayez un classeur dans lequel vous entrez des valeurs dans la colonne A. Votre chef, un gars très compulsif, vous dit qu’il a besoin de savoir quand chaque nombre a été saisi. Le fait d'entrer des données est un événement, plus précisément un événement appelé Worksheet_Change. Vous pouvez écrire une macro qui réagisse à cet événement. Cette macro sera déclenchée chaque fois que la feuille de calcul est modifiée. Si cette modification a été faite dans la colonne A, elle écrira la date et l’heure dans la colonne B, juste à droite de la cellule qui a été éditée.

À titre de curiosité, voyons à quoi une telle macro pourrait bien ressembler :

Private Sub Worksheet_Change (ByVal Target As Range)
    If Target.Column = 1 Then
      Target.Offset(0, 1) = Now
    End if
End Sub

Les macro qui répondent à des événement sont très attentives à l'endroit où elles sont enregistrées. Par exemple, cette macro Worksheet_Change doit être placée dans le module de code associé à cette feuille de calcul. Mettez-la ailleurs, et elle ne fonctionnera pas. Nous allons y revenir un peu plus loin, dans la section "Où placer le code VBA ?".

Attention ! Ce n’est pas parce que votre classeur contient des procédures qui répondent à des événements que celles-ci sont obligatoirement exécutées. Comme vous le savez, il est possible d'ouvrir un classeur dont les macros sont désactivées. Dans ce cas, aucune fonctionnera, événements ou pas. Vous ne devez pas oublier cela lorsque vous créez des classeurs qui sont basés sur des procédures de gestion d’événements.

Comment écrire une procédure d'événement ? modifier

En langage VBA, une procédure de gestion d'événement, ou procédure d'événement, est une procédure qui s'exécute en réponse à un événement. Dont acte. Elle est toujours de type Sub (et non Function). Une fois que vous en aurez assimilé le principe, l'écriture d'une procédure d'événement ne posera pas de problème particulier. La programmation se réduit en effet à ces quelques étapes que nous détaillerons d'ici peu :

  1. Identifiez l'événement qui doit déclencher la procédure.
  2. Appuyez sur Alt+F11 pour activer l'éditeur VBE.
  3. Dans la fenêtre Projet de l'éditeur VBE, double-cliquez sur l’objet approprié listé sous l'intitulé Microsoft Excel Objects. Pour un événement lié au classeur, l’objet est ThisWorkbook. Pour un événement lié à la feuille de calcul, l’objet est un objet de type WorkSheet (comme Feuil1).
  4. Dans la fenêtre Code de l'objet, écrivez la procédure d'événement qui devra être exécutée lorsque ledit événement se produira. Cette procédure aura un nom spécial qui l'identifie en tant que procédure d'événement.

Ces étapes vous paraîtrons plus claires en progressant dans cette leçon.

.

Il est très important de comprendre où le code de votre gestionnaire d'événement doit être placé. Il doit résider dans la fenêtre Code d'un module Objet. La procédure ne fonctionnerait pas si elle était placée dans un module VBA standard. Et aucun message d'erreur ne viendrait attirer votre attention là-dessus.

.

 
Image 1o

.

Cette image montre la fenêtre VBE avec un projet affiché dans la fenêtre Projet. remarquez que le projet est constitué de plusieurs objets

  • Un objet pour chacune des feuilles du classeur (en l’occurrence les trois objets Feuil1).
  • Un objet nommé ThisWorkbook.
  • Un module VBA inséré manuellement avec la commande Insertion/Module

Double-cliquez sur n’importe lequel de ces objets affiche le code associé à l'élément (s'il existe).

La procédure d'événement doit être écrite dans la fenêtre Code de l'élément ThisWorkbook (si l'événement concerne le classeur) ou dans l'un des objets Feuil (si l'événement concerne une feuille de calcul ou de graphique). Dans le cas de l'image précédente, il s'agit du classeur, et c’est d'ailleurs la seule procédure qui y soit définie. À nouveau, notez les deux listes déroulantes affichées en haut de la fenêtre Code. Ce sont vos allies.

.

Lors de l'écriture d'une procédure d'événement l'éditeur VBE vous vient en aide en affichant la liste de tous les événements disponibles pour l’objet sélectionné.

Quand vous sélectionnez un événement dans la liste de droite, l'éditeur VBE crée automatiquement une procédure d'événement à votre place. C'est tout à fait pratique, puisque vous savez immédiatement quels sont les arguments éventuels à fournir.

Il n'est cependant pas obligatoire d’utiliser les deux listes déroulantes disponibles en haut de la fenêtre Code. Mais c’est une aide précieuse, car la syntaxe du nom d'une procédure d'événement est d'une importance critique. De plus, l'instruction Sub de certaines procédures d'événement exige un ou plusieurs arguments. Et rien d’autre ne viendra vous rappeler de quoi il s'agit ! Par exemple, Ici, Sh est là si vous avez sélectionné SheetActivate dans la liste des événements d'un objet Workbook, l'éditeur VBE écrira cette instruction Sub :

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Ici, Sh est l'argument passé à la procédure. C'est une variable qui représente la feuille dans le classeur ainsi activé. Les exemples de ce chapitres aiderons à éclaircir ce point.

Les événements de classeur modifier

Les événements de classeur (objet Workbook)
Événement Lorsqu’il est déclenché :
Workbook_Activate Le classeur est activé.
Workbook_BeforeClose Le classeur est fermé.
Workbook_BeforePrint Le classeur est imprimé.
Workbook_BeforeSave Le classeur est enregistré.
Workbook_Deactivate Le classeur est désactivé.
Workbook_NewSheet Une nouvelle feuille de calcul est insérée dans le classeur.
Workbook_Open Le classeur est ouvert.
Workbook_SheetActivate Une feuille de calcul du classeur est activée.
Workbook_SheetBeforeRightClick Un clic du bouton droit se produit dans une cellule de la feuille de calcul.
Workbook_SheetBeforeDoubleClick Un double clic se produit dans une cellule de la feuille de calcul.
Workbook_SheetCalculate Une feuille de calcul du classeur est recalculée.
Workbook_SheetChange Une cellule du classeur est modifiée.
Workbook_SheetDeactivate Une feuille de calcul du classeur est désactivée.
Workbook_SheetFollowHyperlink Un clic se produit sur un lien hypertexte du classeur.
Workbook_SheetSelectionChange La sélection est modifiée.
Workbook_WindowActivate La fenêtre du classeur est activée.
Workbook_WindowDeactivate La fenêtre du classeur est désactivée.
Workbook_WindowResize La fenêtre du classeur est redimensionnée.

Workbook_Open modifier

L'un des événements les plus communément utilisés est l'événement de classeur Open. Imaginons un classeur que vous utilisez quotidiennement. Dans l'exemple qui va suivre, la procédure Workbook_Open est exécutée chaque fois que le classeur est ouvert. Elle vérifie le jour de la semaine : si c’est vendredi, le code affiche un message de rappel.

Private Sub Workbook_Open()
    Dim Msg As String
    If WeekDay (Now) = 6 then
     Msg = "Nous sommes Vendredi. "
     Msg = Msg & "Pensez à sauvegarder votre travail."
     MsgBox Msg
    End If
End Sub

La fonction WorkBook_Open est exécutée automatiquement chaque fois que le classeur est ouvert. Elle utilise la fonction WeekDay de VBA pour déterminer le jour de la semaine. Si c’est vendredi (jour 6 chez les Anglo-saxons), un message rappelle à l'utilisateur qu’il doit effectuer sa sauvegarde hebdomadaire. Les autres jours, rien ne se produit.


 
Image 2o


Voyons un autre exemple d'utilisation de la procédure Workbook_Open. Elle se sert des fonctions GetSetting et SaveSetting pour mémoriser le nombre de fois où le classeur a été ouvert. La fonction SaveSetting enregistre une valeur dans le Registre de Windows, tandis que GetSetting retrouve cette valeur (voyez le système d'aide pour plus d'informations à ce sujet). Le code qui suit retrouve ce décompte en consultant le Registre, l'incrémente, puis le sauvegarde à nouveau. L'information, placée dans la variable Cnt, est également affichée à des fins de contrôle.

Private Sub Workbook_Open()
    Dim Cnt As Long
    Cnt = GetSetting("MyApp", "Settings", "Open", 0)
    Cnt = Cnt + 1
    SaveSetting "MyApp", "Settings", "Open", Cnt
    MsgBox "Ce classeur a été ouvert " & Cnt & " fois."
End Sub

Workbook_BeforeClose modifier

Voyons maintenant la procédure d'événement BeforeClose. Exécutée juste avant que le classeur se ferme, elle est localisée dans la fenêtre code de l’objet ThisWorkbook. Par exemple :

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Msg As string
    Dim Réponse As integer
    Dim FName As String
    Msg = "Désirez-vous sauvegarder ce fichier ?"
    Réponse = MsgBox(Msg vbYesNo)
    If Réponse = vbYes Then
     FName = "F\SAUVEGARDES\" & ThisWorkbook.Name
     ThisWorkbook.SaveCopyAs NomFichier
    End If
End Sub

Cette routine affiche une boîte de message demandant à l'utilisateur s'il désire effectuer une copie de sauvegarde du classeur. S'il clique sur le bouton Oui, le code utilise la méthode SaveCopyAs pour enregistrer le fichier sur le lecteur F (le lecteur te le chemin devraient bien sûr être adaptés à votre propre configuration).

Les programmeurs utilisent souvent la procédure Workbook_BeforeClose pour faire le ménage. Par exemple, après avoir modifié une option du classeur (comme masquer la barre d'état, par exemple), il est approprié de la rétablir au moment de quitter le classeur. Ce ménage électronique est typiquement une tâche à confier à la procédure Workbook_BeforeClose.

Cet événement présente tout de même un inconvénient. Si vous refermez Excel et qu'un fichier ouvert a été modifié depuis la dernière sauvegarde, l’application vous demandera comme d'habitude si vous voulez enregistrer les changements opérés. le fait de cliquer sur le bouton Annuler clôt le processus de fermeture d'Excel. Mais la procédure Workbook_BeforeClose aura tout de même été exécutée.

Workbook_BeforeSave modifier

L'événement BeforeSave est déclenché avant l'enregistreur d'un classeur. Il se produit lorsque vous utilisez la commande Fichier/Enregistrer ou Fichier/Enregistrer sous.

Placée dans la fenêtre Code d'un objet ThisWorkbook, la procédure suivante démontre la fonctionnement de cet événement. La routine met à jour la valeur de la cellule A1 de Feuil1 chaque fois que le classeur est enregistré. En d'autres termes, la cellule A1 sert de compteur indiquant le nombre de fois que le fichier a été sauvegardé.

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
    Sheets("Feuil1").Range("A1").Value =_
     Sheets("Feuil1").Range("A1").Value + 1
End Sub

Notez que la procédure Workbook_BeforeSave a deux arguments : SaveAsUI et Cancel. Pour comprendre leur fonctionnement, examinez la macro suivante, qui est exécutée avant l'enregistreur du classeur. Elle essaie d'empêcher l'utilisateur de sauvegarder le classeur sous un autre nom. Si celui-ci choisit la commande Fichier/Enregistrer sous, l'argument SaveAsUI est True (vrai).

Lorsque le code est exécuté, il vérifie la valeur de SaveAsUI. Si sa valeur renvoie True, la procédure affiche un message et met Cancel également sur True, ce qui annule l'opérateur de sauvegarde.

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
    If SaveAsUI Then
      MsgBox "vous ne pouvez pas enregistrer de copie de ce classeur !")
      Cancel = True
    End If
End Sub

En fait, cette procédure n'empêche pas réellement quelqu’un d'enregistrer le classeur sous un nom différent. Il suffit d'ouvrir le classeur avec ses macros désactivées, et le tour est joué. En effet, dans ce cas, toutes les procédures de gestion d’événements sont elles aussi désactivées. Ce qui est parfaitement logique, puisque ce sont aussi des macros...

Les événements de feuille modifier

.

Les événements de feuille (objet Worksheet)
Evénement Lorsqu’il est déclenché :
Worksheet_Activate La feuille est activée.
Worksheet_SheetActivate Une feuille de calcul du classeur est activée.
Worksheet_BeforeRightClick Un clic du bouton droit se produit dans la feuille de calcul.
Worksheet_BeforeDoubleClick Un double clic se produit dans la feuille de calcul.
Worksheet_Deactivate Une feuille de calcul est désactivée.
Worksheet_Calculate La feuille de calcul est recalculée.
Worksheet_Change Une cellule de la feuille de calcul est modifiée.
Worksheet_SelectionChange La sélection est modifiée.

Worksheet_BeforeDoubleClick modifier

Il est possible de définir une procédure VBA qui soit exécutée lorsque l'utilisateur double-clique dans une cellule. Dans l'exemple suivant, stocké dans la fenêtre Code d'un objet Feuil, double-cliquer dans une cellule met son contenu en gras s'il est en caractères maigres, et inversement.

La procédure WorkSheet_BeforeDoubleClick a deux arguments : Target et Cancel. Target est la cellule (un objet Range) qui est double-cliquée. Si Cancel est sur True, l'action par défaut du double clic ne se produit pas.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    Target.Font.Bold = Not Target.Font.Bold
    Cancel = True
End Sub

Notez que Cancel est défini avec la valeur True. Cela empêche l'action par défaut (activer le mode édition de cellule d'Excel) de se produire.

Worksheet_Activate modifier

Excel peut détecter si une feuille est activée ou désactivée et exécuter une macro lorsque l'un ou l'autre de ces événements se produit. Ces procédures d'événement doivent être placées dans la fenêtre Code de l’objet Feuil. Pour accéder rapidement à la fenêtre de code d'une feuille, cliquez droit sur l'onglet de celle-ci et choisissez la commande Visualiser le code. L'exemple qui suit montre une procédure simple qui est exécutée chaque fois qu'un feuille donnée est activée. Elle ouvre une boîte de message qui affiche le nom de la feuille active :

Private Sub Worksheet_Activate()
    MsgBox "Vous venez d'activer la feuille "& ActiveSheet.Name
En Sub

Voici un autre exemple qui rend la cellule A1 courante chaque fois qu'une feuille est activée :

Private Sub Worksheet_Activate()
    Range("A1").Activate
End Sub

Ces exemples sont très élémentaires, mais une procédure d'événement peut être beaucoup plus complexe. La procédure qui suit - stockée dans la fenêtre Code de l’objet Feuil1 - utilise l'événement Deactivate pour empêcher l'utilisateur d'activer toute autre feuille du classeur. Lorsque Feuil1 est désactivée (c'est-à-dire qu'une autre feuille est activée), un message est affiché puis Feuil1 est de nouveau activé :

Private Sub Worksheet_Activate()
    MsgBox "Vous devez rester dans Feuil1."
    Sheets("Feuil1").Activate
End Sub

Pour autant je ne vous conseille pas d’utiliser ce genre de procédure pour essayer de court-circuiter Excel. Cela pourrait être très frustrant et source de confusion pour l'utilisateur d'une part, et d’autre part facile à contourner en désactivant les macros. Il vaut mieux profiter de ces possibilités pour aider vos utilisateurs à se servir correctement de votre application.

Worksheet_Change modifier

Un événement Change se produit chaque fois qu'un cellule de la feuille de calcul est modifiée. Dans l'exemple qui suit, la procédure Worksheet_Change empêche effectivement un utilisateur d'entrer une valeur non numérique dans la cellule A1. Ce listing est stocké dans la fenêtre Code de l’objet Feuil.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
      If Not IsNumeric(Target) Then
         MsgBox "Entrez un nombre dans la cellule A1."
         Range("A1").ClearContents
         Range("A1").Activate
      End If
    End If
End Sub

L'unique argument de la procédure WorkSheet_Change (Target) représente la plage qui a été modifiée. La première instruction vérifie si l'adresse de la cellule est bien $A$1. Si oui, le code utilise la fonction IsNumeric pour déterminer si elle contient une valeur numérique. Si ce n’est pas le cas, un message apparaît et la valeur de la cellule est effacée. La cellule A1 est ensuite réactivée, ce qui est commode lorsque le pointeur de la cellule s'est déplacé après la saisie. Si une cellule autre que A1 est modifiée, il ne se passe rien.

Pourquoi ne pas utiliser la commande Validation ?

La commande Donnée/Outils de données/Validation des données vous est peut-être familière. C'est une fonction très commode qui permet de s'assurer facilement que les données entrées dans une plage sont bien du type requis. Elle ne met toutefois pas à l'abri de toutes les fausses manœuvres. Pour vous en convaincre, ouvrez une feuille de calcul vierge puis effectuez les étapes suivantes :

  1. Sélectionnez la plage A1:C12
  2. Ouvrez l'onglet Données, puis cliquez sur le bouton Validation des données dans le groupe Outils des données.
  3. Configurez les critères de validation de manière à n'accepter que des nombres entiers entre 1 et 12, comme le montre l'image qui suit

.

 
Image 3o

.

Entrez maintenant des valeurs dans la plage A1:C12. La validation des données fonctionne comme prévu. Mais, pour voir ce qui ne va pas, essayez ceci :

  1. Entrez -1 dans n’importe quelle cellule en dehors de la plage de validation.
  2. Appuyez sur Ctrl+C afin de placer le nombre négatif dans le Presse papiers.
  3. Sélectionnez une cellule dans la plage de validation.
  4. Appuyez sur Ctrl+V.

Vous constatez que le collage est autorisé. Examinez la situation de plus près et vous remarquerez que la cellule dans laquelle vous avez collé la valeur négative n'a plus de critère de validation. Le collage l'a effacé !

Le collage efface les données de validation, car, pour Excel, la validation est la même chose qu'une mise en forme de cellule (ou, pour être plus précis, qu'une mise en forme conditionnelle). Elle est considérée au même titre que la police, la couleur au autre attribut. Quand vous collez une cellule, vous remplacez la mise en forme de la cellule de destination par celle de la cellule source. Il en va malheureusement de même pour les critères de validation.

Annexes modifier

Bibliographie modifier

  • John Walkenbach, 2013, Programmation VBA pour Excel Pour Les Nuls, FIRST

Liens internes modifier

Liens externes modifier



Débogage

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 18
Leçon : Macros-commandes VBA
Chap. préc. :Procédures et événements automatiques
Chap. suiv. :Créer des macros complémentaires
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Erreur modifier

Toutes les macros, comme tous les codes informatiques, peuvent comporter des erreurs, on pourrait même affirmer : comportent des erreurs. Parmi ces erreurs, on peut trouver différentes causes et effets :

  • Compilation : l'erreur de compilation (ou de syntaxe) survient lorsque VBA rencontre une instruction (ou une donnée) qu’il ne connait pas (par exemple une faute d'orthographe ou de ponctuation sur un mot clé, …). Dans ce cas VBA affiche un message d'erreur avant l'exécution directement sur la ligne comprenant l'erreur de syntaxe, c’est donc l'erreur la plus fréquente et la plus aisée à déceler et réparer.
  • Exécution : l'erreur d'exécution survient lorsque VBA rencontre une instruction interdite (par exemple lancer une impression vers une imprimante qui n'existe pas). Dans ce cas VBA affiche un message d'erreur pendant l'exécution, c’est une erreur assez fréquente et assez aisée à déceler et réparer, par exemple : une division par zéro, une valeur recherchée au-delà d'une limite d'un tableau, …
  • Logique : l'erreur de logique survient lorsque le programmeur constate un résultat inattendu suite à une série d'instructions qu’il a programmé (par exemple le fait de ne pas mettre en gras les bonnes cellules). La macro s'exécute mais ne réalise pas ce qu'elle est censée faire. C'est l'erreur la moins fréquente et la moins aisée à déceler et réparer, par exemple : un test si erroné, une boucle de trop dans un calcul, …

Exemple d'erreur d'exécution : erreur de débordement modifier

On désire créer une macro qui supprime certaines lignes Excel en rencontrant la valeur "fauteuil" sur cette ligne, soit les données suivantes de la feuille : cellule A1 = "chaise", A2 = "fauteuil", A3 = "fauteuil", A4 = "tabouret"... L'algorithme suivant qui semble juste à première vue est cependant faux :

Pour i de 1 à 3
 Si A(i)= "fauteuil" Alors SupprimerLigne(i)
Suivant
Début de l'exemple
Fin de l'exemple


POURQUOI : dès le second tour de boucle, la ligne 2 (avec "fauteuil") sera supprimée ce qui entrainera le passage de la ligne 3 (avec encore "fauteuil") en ligne 2, la ligne 2 étant déjà traitée par le programme ce nouveau "fauteuil" ne sera pas supprimé

SOLUTION : inverser le sens de parcours de la boucle en partant de la dernière ligne

Début de l'exemple
Fin de l'exemple


Exemple d'erreur de logique : boucle sans fin modifier

On désire créer une boucle qui tourne tant qu'une condition de sortie n’est pas atteinte, ici la rencontre du libellé "fauteuil" sur une ligne, soit les données suivantes de la feuille (1000 lignes renseignées) : cellule A1 = "chaise", A2 = "tabouret", A3 = "tabouret", A4 = "tabouret"... L'algorithme suivant qui semble juste à première vue est cependant faux :

Tant que la ligne lue est différente de "fauteuil"
 Afficher "je n'ai pas encore trouvé un fauteuil"
 Passer à la ligne suivante
Fin Tant que
Afficher "j'ai trouvé un fauteuil"
Début de l'exemple
Fin de l'exemple


POURQUOI : le mot "fauteuil" n'existant pas dans la feuille Excel, la boucle deviendra infinie et visitera toutes les lignes du classeur (plusieurs millions). Une telle boucle est très souvent considérée comme un problème car elle sollicite trop de ressources ordinateur.

SOLUTION : ajouter une condition d'arrêt obligatoire, ici dès que le programme aura parcouru plus de 1000 lignes Excel

Début de l'exemple
Fin de l'exemple


Erreur d'encodage modifier

Si une importation vers une base de données remplace tous les caractères non ANSI (ex : "é"), il faut convertir le fichier de PC ANSI en PC DOS.

Types d'erreurs modifier

content_types.xml modifier

Il s'agit d'une erreur apparaissant après un publipostage Word : résolue en désinstallant un Office 2003 qui était à côté d'un 2007.

Erreur d'exécution '7': Mémoire insuffisante modifier

Une variable a dépassé la valeur prévue (listée dans le chapitre Les types en VB). Il convient donc de changer son type (par exemple passer d'Integer à Long), ou bien de diminuer sa valeur.

Erreur d'exécution '9': L'indice n'appartient pas à la sélection modifier

Cela se produit par exemple :

  • Quand on appelle une feuille qui n'existe pas (Worksheets("MaFeuille")), on peut donc rendre le traitement plus générique avec des numéros (Worksheets(1)).
  • Quand on appelle un tableau avec un indice qui le dépasse, il faut donc le redimensionner :
 ReDim MonTableau(5)
 MsgBox IsEmpty(MonTableau(4))

Erreur d'exécution '28': Espace pile insuffisant modifier

Peut survenir en cas de boucle infinie ou de récursion infinie.

Erreur d'exécution '70': Accès refusé modifier

Un objet de formulaire possède une propriété ControlSource non vide, donc on ne peut pas le modifier en appelant sa méthode .AddItem.

Erreur d'exécution '70': Permission refusée modifier

Un fichier ouvert est peut-être en train d’être rouvert.

Erreur d'exécution '75': Erreur d'accès Chemin/Fichier modifier

Un fichier ouvert ne peut pas être supprimé avant sa fermeture.

Erreur d'exécution '91': Variable objet ou variable de bloc With non définie modifier

Il se peut qu'une fonction soit appliquée à une variable vide. Ex :

 set searched = Nothing
 MsgBox searched   ' Erreur
 x = Len(searched) ' Erreur

Il faut donc lever l'exception.

Erreur d'exécution '424': Objet requis modifier

Une méthode est appliquée sur un objet déclaré qui n'en dispose pas.

  1. Est-il bien initialisé ?
  2. Si oui, l'explorateur d'objets permet de vérifier l’existence de la méthode.
  3. Si la méthode existe, c’est que l’objet est mal initialisé. Par exemple à cause de plusieurs déclarations sur la ligne du New :
' Pas bien :
Dim Command, Command2 As New ADODB.Command

' Bien :
Dim Command As New ADODB.Command
Dim Command2 As New ADODB.Command

Erreur d'exécution '462': Le serveur distant n'existe pas ou n’est pas défini modifier

Il faut libérer la ressource avant sa deuxième exécution.

Erreur d'exécution '450': Nombre d'arguments incorrect ou affectation de propriété incorrecte modifier

Se produit quand on tente d'affecter un objet à une variable dont le type est incompatible.

Erreur d'exécution '1004': Erreur définie par l’application ou par l’objet modifier

Si cela se produit lors de la fermeture du classeur, il faut modifier l'évènement[1].

Sinon, regarder si le programme n'appelle pas une cellule Excel avec des coordonnées non entières (ex : B3,33).

Enfin, il faut savoir que le VBA ne peut pas ajouter de formule Excel en français. Celles en anglais fonctionnent avec une syntaxe un peu différente pour qu’elles soient interprétées pendant son exécution :

  1. Positions absolues avec R pour row et C pour column, et pour les relatives entre crochets (ex : C[0] = C = colonne courante).
  2. Virgule au lieu de point-virgule.
  3. Caractère d'échappement pour les guillemets.

Par exemple :

 ' Affiche la valeur correspondant à la date d'une table liée
 Range("C7").Value = "=SUMIFS(Feuil2!C1,Feuil2!C3,TEXT(R5C[-1], ""aaaammjj""),Feuil2!C2, RC1)"

Affichera dans Excel :

=SOMME.SI.ENS(Feuil2!$A:$A;Feuil2!$C:$C;TEXTE(B$5; "aaaammjj")Feuil2!$B:$B; $A7)

Remarque : ne pas ajouter d'addition dans ces formules (R[5]C[-1+2]).

Erreur d'exécution '1004': La méthode 'Add' de l'objet 'Workbooks' a échoué modifier

Si on ne peut plus créer de classeur Excel sans VBA, Excel n’a plus assez de ressources : redémarrer la machine.

Erreur d'exécution '1004': La méthode de la classe Range a échoué modifier

Le type d'un argument placé dans Range() n'est pas une cellule.

Erreur d'exécution '3421': L'application utilise une valeur d'un type incorrect pour l'opération en cours modifier

Une variable a dépassé la valeur prévue. Il convient donc de modifier sa valeur, gérer une exception, ou bien de changer son type (par exemple passer de Command.CreateParameter("@Variable1", adInteger, adParamInput) ou Command.CreateParameter("@Variable1", adVarChar, adParamInput, 4), à Command.CreateParameter("@Variable1", adVarChar, adParamInput, 10)).

Erreur d'exécution '3704': Cette opération n’est pas autorisée si l’objet est fermé modifier

Apparait quand on cherche à accéder à un objet ADODB.Recordset vide, par exemple avec ResultSet.BOF.

Erreur d'exécution '-2147217900': La procédure ou fonction 'xxx' attend le paramètre 'xxx', qui n'a pas été fourni modifier

Ce message peut survenir alors que le paramètre d'une procédure stockée est parfaitement bien renseigné ! On peut le vérifier avec :

 MsgBox Command.Parameters.Item(0).Name & " = " & Command.Parameters.Item(0).Value

Il faut juste éviter d'utiliser les ADODB.Parameter :

 Command.CommandText = "MaProcédureStockée @Param1=" & Valeur1 & ", @Param2=" & Valeur2

Erreur de compilation: Argument non facultatif modifier

L'affectation en erreur doit être précédée de Set.

Erreur de compilation: Impossible d'affecter à un tableau modifier

Le compilateur a trouvé une affectation à un tableau, il faut donc la remplacer. Ex :

 Dim Tableau(2)
 'Tableau = 1
 Tableau(0) = 1

Erreur de compilation: Procédure trop grande modifier

Les Sub et Function sont limitées à 64 ko (environ 2 000 lignes, sans les commentaires, ni tabulations), en .xls et .xlsm. Il faut donc par exemple :

  • Faire des appels (Call) à d'autres fonctions contenant des traitements séparés.
  • S'orienter vers la pseudo programmation objet VBA (variable Collection).
  • Remplacer les conditions récurrentes par un test de booléen.

Impossible de définir la propriété ControlSource. Valeur de propriété non valide. modifier

Le champ ControlSource de la fenêtre Propriétés d'un formulaire pointe vers une feuille inexistante.


Impossible de définir un type Public défini par l'utilisateur à l'intérieur d'un module objet modifier

Ajouter la visibilité Private avant la variable concernée.

Sinon, dans le module de classe au paramètre "Instancing" (à côté de son nom), choisir dans le menu déroulant "Private" au lieu de "Public".

Mode d'accès au fichier incorrect modifier

Remplacer Input par Output ou Append lors de l'ouverture de fichier. Exemple sur la première ligne :

 Open "C:\Fichier1.csv" For Output As #1
 Print #1, Ligne1
 Close #1

Nom ambigu détecté modifier

Une variable ou fonction de même nom et de même portée a été déclarée deux fois. Attention : ce n’est pas forcément celle sélectionnée par le message d'erreur.

Projet ou bibliothèque introuvable modifier

Si cela porte sur des fonctions comme trim, mid, left et right, alors décocher la bibliothèque manquante dans l'éditeur de code, Outils, Références.

Sinon, une DLL est probablement à cocher dedans.

Type d'argument Byref incompatible modifier

Il faut convertir la variable en cause, par exemple avec Cstr() ou Cint().

Type défini par l'utilisateur non défini modifier

Il faut ajouter une bibliothèque contenant ce type (en cochant une case dans Outils\Références). Attention : si le programme ne coche pas cette case automatiquement chaque utilisateur de chaque ordinateur devra la cocher manuellement.

Une erreur s'est produite : External table is not in the expected format modifier

Lors d'un publipostage VBA, la source de données n’est pas prise en compte par Word. Essayer de le faire manuellement pour en savoir plus. Si le message persiste, changer le format de la source (ex : Fichier.SaveAs test.xls, FileFormat:=xlNormal).

Variable non définie modifier

La mention Option Explicit impose de déclarer chaque variable, on peut donc soit l'enlever, soit tout déclarer avec Dim.

Variable requise. Impossible de l'affecter à cette expression modifier

Peut survenir quand on appelle len(Tableau) au lieu de UBound(Tableau).

  Si en cas de division par zéro le compilateur renvoie bien cette erreur, en cas de modulo zéro il renvoie le premier nombre.
  Quand on effectue des opérations mathématiques avec des chaines de caractères, VBA ne les convertit pas toujours automatiquement, il est donc plus sûr d’utiliser les fonctions de conversions.

Gestion d'exception par VBA modifier

La VBA ne possède pas de levée d'exception proprement dite, il faut donc désactiver les messages d'erreur bloquants puis tester si l'opération à risque s'est bien déroulée :

On Error Resume Next ' Désactivation des erreurs
x = 1
On Error GoTo 0 ' Réactivation des erreurs
If IsNull(x) Or IsEmpty(x) Then Exit Sub

Débogage modifier

Introduction modifier

Toute correction de programme, de logiciel, ou d'application suite à une anomalie de fonctionnement s’appelle débogage. Dans notre cas, il s'agira donc de la correction d'une erreur liée à une Macro VBA. L'outil débogueur aide à corriger une anomalie empêchant le fonctionnement de la macro, son objectif est donc de cerner l'instruction en erreur ou responsable de l'erreur. Pour ce faire, l'outil utilise quelques modes de fonctionnement :

  • Mode pas à pas => sur des portions courtes de code, l'assistant erreur propose le passage en mode pas à pas, si le programmeur choisit ce mode, le programme alors s'arrête sur chaque instruction pour permettre de repérer la cause de l'erreur grâce à de nombreuses options d'affichage. Ce mode "pas à pas" peut aussi être directement demandé par le concepteur dès le début de la procédure (touche F8).
  • Point d'arrêt : pour éviter le fastidieux mode "pas à pas" sur des portions longues de code, il est possible de marquer par des points d'arrêt les lignes d'instruction sur lesquelles le programme doit s'arrêter, le programme alors s'arrête sur chaque arrêt positionné par le programmeur.
Début de l'exemple
Fin de l'exemple


Outils de débogage modifier

Il existe différentes fonctions permettant le débogage de macros créées par l'utilisateur. Ces outils permettent de vérifier le bon fonctionnement des macros une fois celles-ci créées. Nous avons par exemple, le message de syntaxe, le message d'exécution, l'instruction stop, le point d'arrêt, l'afficheur d’état d'une variable, la fenêtre variable locale, la fenêtre espion, la fenêtre exécution, ...

  • CTRL + Pause envoie une interruption qui met en pause le programme en cours d'exécution (un peu comme CTRL + ALT + Suppr dans Windows).
  • F8 lance le mode pas à pas (ligne par ligne).
  • F5 lance le programme jusqu'à fin de l'exécution, ou ce qu’il rencontre un point d'arrêt.
  • F9 définit un point d'arrêt, équivalent d'un clic gauche dans la marge.

Pendant le débogage, en sélectionnant une variable sur clic droit, il est possible d'y ajouter un espion. Cela permet d'afficher sa valeur variable dans une fenêtre en bas, et même de suspendre l'exécution si cette valeur change. Il est aussi possible de connaitre ces valeurs en passant la souris sur les variables pendant l'exécution.

Message d'erreur d'exécution modifier

Lors de l’affichage du message d'exécution, VBA affiche une boite de dialogue à 2 boutons (Fin ou Débogage)

  • Le bouton Fin fait en sorte de quitter la macro en erreur
  • Le bouton Débogage met en surbrillance la ligne de la macro qui a décelé l'erreur.

Instruction Stop modifier

Il suffit de l'insérer dans le code d'une macro. Lorsque de l'exécution de la macro, celle-ci s'arrêtera à l'endroit où le "Stop" est placé. Le but ici est de vérifier que la portion de code précédent le stop ne contient aucune erreur.

Début de l'exemple
Fin de l'exemple


Point d'arrêt modifier

Le but du point d'arrêt est identique au stop, le but premier étant d'interrompre l'exécution de la macro et donc de vérifier si son exécution est correcte

Début de l'exemple
Fin de l'exemple


L'affichage de l'état de variable modifier

L'état de variable permet de vérifier la valeur que prend chaque variable pendant l'exécution, extrêmement utile lorsque la découverte de l'erreur n’est pas triviale

Début de l'exemple
Fin de l'exemple


Références modifier

  • Greg Harvey et John Walkenbach, 2013, Excel 2013 et VBA Pour Les Nuls, FIRST, Hoboken

Liens internes modifier

Liens externes modifier




Créer des macros complémentaires

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 19
Leçon : Macros-commandes VBA
Chap. préc. :Débogage
Chap. suiv. :Consolider des classeurs
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Introduction modifier

    Introduction à la leçon : Macros Complémentaires

L'une des plus astucieuses fonctionnalités d'Excel - à mon avis, du moins - est la possibilité de créer des macros complémentaires, appelées aussi "compléments". Vous comprendrez leur intérêt dans ce chapitre, et vous apprendrez à les créer en utilisant les outils intégrés à Excel.

Qu'est-ce qu'une macro complémentaire ? modifier


Excel est livré avec plusieurs macros complémentaires. Les plus connues sont l'Utilitaire d'analyse, l'Assistant Somme conditionnelle et le Complément Solveur. Vous pouvez en obtenir d'autres auprès de programmeurs et d'éditeurs tiers. Mon Power Utility Pak en est un (très bon...) exemple.

Tout utilisateur un tant soit peu averti peut créer des macros complémentaires, à condition bien sûr de savoir programmer en VBA. Une macro complémentaire d'Excel est fondamentalement une forme différente de fichier de classeur XLSM. Plus spécifiquement, une macro complémentaire est un classeur XLSM normal, à quelques différences près tout de même :

  • la propriété IsAddin de l’objet Workbook est True.
  • la fenêtre du classeur est masquée et ne peut pas être affichée avec les commandes d'Excel.
  • Le classeur n’est pas un membre de la collection Workbook, mais de la collection AddIns.

Tout fichier XLSX peut être converti en macro complémentaire, mais tous ne sont pas de bons candidats. Comme les compléments sont toujours masqués, vous ne pouvez pas afficher leurs feuilles de calcul ou de graphique. Il est toutefois possible d'accéder aux procédures Sub et Function et d'afficher les boîtes de dialogue contenues dans les objets UserForm. Les fichiers des macros complémentaires d'Excel ont habituellement une extension XLAM (XLA avant Excel 2007 qui les distingue des classeurs XLSM).

Pourquoi créer des macros complémentaires ? modifier

Vous pouvez décider de convertir une application Excel en macro complémentaire pour l'une des raisons suivantes :

  • Rendre l'accès au code plus difficile : quand une application est distribuée sous la forme d'une macro complémentaire (et que vous protégez son projet VBA), l'utilisateur lambda ne peut pas voir ses feuilles dans le classeur. Si vous l'avez programmé avec des techniques bien à vous, il sera plus difficile à autrui de copier votre code. Les fonctions de protection d'Excel ne sont pas parfaites et il existe des utilisateurs conçus pour casser le mot de passe.
  • Eviter la confusion : quand une application est chargée en tant que macro complémentaire, le fichier est invisible, ce qui évite à l'utilisateur novice de s'y perdre et de faire n’importe quoi. Contrairement à un classeur masqué, une macro complémentaire ne peut pas être révélée.
  • Simplifier l'accès aux fonctions de la feuille de calcul : les fonctions de la feuille de calcul personnalisées stockées dans une macro complémentaire n'ont pas besoin d’être qualifiées par un nom de feuille. Par exemple, si vous stockez une fonction personnalisée nommée DEFMOY dans un classeur nommé NouvFonc.xlsm, vous devez utiliser la syntaxe suivante lorsque cette fonction est utilisée dans un autre classeur : =NOUVFONC.XLS!DEFMOY (A1:A50). En revanche, si la fonction est située dans un fichier de macro complémentaire ouvert, vous utiliserez une syntaxe plus simple, car vous n'avez plus à faire référence au fichier : =DEFMOY (A1:A50)
  • Faciliter l'accès aux utilisateurs : une fois identifié l'emplacement de la macro complémentaire, celle-ci apparaît dans la boite de dialogue Macro complémentaire, avec un nom évocateur et une brève description de son usage. L'utilisateur peut facilement activer ou désactiver votre complément.
  • Mieux contrôler le chargement : les macros complémentaires peuvent être ouvertes automatiquement au démarrage d'Excel, peu importe le dossier où elles sont stockées.
  • Eviter l’affichage d'une invite au déchargement : quand une macro complémentaire est refermée, l'utilisateur ne voit jamais apparaître l'invite Enregistrer sous.

Travailler avec des macros complémentaires modifier

Le moyen le plus simple et le plus efficace de charger et décharger les macros complémentaires consiste à suivre les instructions suivantes :

  1. Fichier → Options → Compléments
  2. Compléments Excel (qui se trouve en bas de la fenêtre Options Excel) → cliquer sur le bouton Atteindre

N.B. : on peut directement passer par l'onglet Développeur et cliquer sur le bouton Compléments


Une fois ces actions réalisées, la boîte de dialogue ci-dessous (figure 1) s'affiche :

 
Boîte de dialogue contenant toutes les macros complémentaires reconnues par Excel.

Elle recense toutes les macros complémentaires reconnues par Excel. Afin d'ouvrir ou fermer une macro complémentaire, il suffit seulement de les cocher ou décocher, directement depuis cette boîte de dialogue. De manière générale, lorsque vous allez ouvrir une macro complémentaire, le ruban rouge change quelque peu :

  • Excel affiche un nouvel onglet
  • Excel ajoute des commandes à un onglet existant

Par exemple, lors de l'ouverture de l'Utilitaire d'analyse, un nouvel élément Utilitaire d'analyse s'ajoutera à l'onglet Données. Cependant, il se peut également qu’il ne se passe rien, c’est notamment le cas lorsque la macro complémentaire ne contient que des fonctions personnalisées de feuille de calcul. En effet, ces dernières apparaîtront dans la boite de dialogue Insérer une fonction et il n'y aura donc aucun changement visible directement depuis l'interface utilisateur.

Notez qu’il existe un autre moyen pour utiliser les macros complémentaires, grâce à la commande Ouvrir. Cependant, en passant par celle-ci, vous ne les retrouverez pas dans la boîte de dialogue Macro complémentaire et il ne sera pas possible de les quitter avec la commande Fermer. En effet, la macro complémentaire ne pourra être supprimée qu'en quittant ou redémarrant Excel ou encore, en utilisant une macro qui aura pour but de fermer cette macro complémentaire.


Les bases des macros complémentaires modifier

Par déduction, afin de créer des macros complémentaires il faut utiliser un classeur contenant des macros. En effet, bien que l’on puisse convertir n’importe quelle feuille de calcul en macro complémentaire, tous les classeurs ne peuvent pas en bénéficier. Ceux dont les feuilles ne contiennent pas de macros n'ont aucun intérêt puisque les macros complémentaire sont masquées. Du coup, l'idéal est d’utiliser un classeur avec des macros à usage général telles que les procédures Sub et Function. À partir de là, créer une macro complémentaire est assez simple, il suffit de suivre la procédure suivante :


  1. Développez votre application et assurez-vous que tout fonctionne correctement. Attention ! N'oubliez pas d'inclure une méthode d'exécution des macros (ajout de nouvelles commandes dans le ruban ou création raccourci). Cependant, il est inutile d'inclure une méthode d'exécution si le complément ne contient que des fonctions. En effet, elles apparaîtront dans la boîte de dialogue Insérer une fonction.
  2. Testez l’application en l'exécutant quand un classeur différent est actif. Cela permet de simuler le comportement de l’application lorsqu'elle est utilisée comme macro complémentaire. En effet, une telle macro n'est jamais le classeur actif.
  3. Activez l'éditeur VBE → sélectionner le classeur (fenêtre Projet) → Outils/Propriétés de VBAProject → onglet Protection → Cochez "Verrouiller" le projet → Entrez un mot de passe → OK. Cette étape a pour objectif d'empêcher les autres utilisateurs de visualiser ou modifier vos macros et vos objets UserForm. Elle n’est pas obligatoire.
  4. Dans Excel : ouvrir l'onglet Développeur → Panneau de document → OK. Le panneau apparaît sous le ruban.
  5. Choisir un nom dans le champ "Titre" ainsi qu'une description précise dans le champ Commentaires. Encore une fois, ces étapes (4 et 5) ne sont pas obligatoires. Cependant, elles permettent une meilleure utilisation de la macro complémentaire, notamment votre description puisqu'elle sera affichée dans la boîte de dialogue Macro complémentaire, que l’on a vu un peu plus haut.
  6. Fichier → Enregistrez sous.
  7. Boîte de dialogue → Enregistrez sous → déroulez le menu Type de fichier → choisir Macro complémentaire Microsoft Excel (*.xlam).
  8. Choisissez le dossier de stockage de la macro complémentaire. Excel propose par défaut un dossier nommé AddIns, mais la sauvegarde peut se faire dans n’importe quel autre emplacement.
  9. Enregistrer. Et voila ! Vous avez créé une macro complémentaire !

Un exemple de macro complémentaire modifier

Début de l'exemple
Fin de l'exemple


Configurer le classeur modifier

Le classeur est constitué d'une feuille de calcul vierge, d'un module VBA et d'un objet UserForm.

 
L'objet UserForm pour la macro complémentaire Changer la case

Prenons l'exemple simple d'un UserForm avec les options suivantes :

  • Majuscules : met toutes les lettres en majuscules
  • Minuscules : met toutes les lettres en minuscules
  • 1ère lettre en majuscules : met la première lettre en majuscule, et toutes les autres en minuscules, et ce pour chaque mot d'un texte
  • Phrase : seule la première lettre du texte est mise en majuscule, tout le reste en minuscules
  • Inverser la casse : met les majuscules en minuscules, et vice versa

La figure 2 ci-contre montre à quoi va ressembler notre objet UserForm1. Les cinq contrôles OptionButton sont placés à l'intérieur d'un contrôle Frame. Vous trouvez aussi, comme il se doit, un bouton Annuler(appelé BoutonAnnuler) et un bouton OK (appelé BoutonOK).


Le code exécuté lorsque l'utilisateur clique sur le bouton Annuler est très simple :

Private Sub BoutonAnnuler_Click()
    Unload UserForm1
End Sub

Tout le travail est effectué par le code suivant, qui est exécuté lorsque l'utilisateur clique sur le bouton OK :

Private Sub BoutonOK_Click()
    Dim TextCells As Range
    Dim cell As Range
    Dim text As String
    Dim i As Long
    
    'Créer un objet avec juste des constantes de texte'
    On Error Resume Next
    Set TextCells = Selection.SpecialCells(xlConstants, xlTextValues)
    
    'Désactiver la mise à jour de l'écran'
    Application.ScreenUpdating = False

    'Boucle sur les cellules'
    For Each cell In TextCells
        text = cell.Value
        Select Case True
        Case OptionLower 'minuscules
            cell.Value = LCase(cell.Value)
        Case OptionUpper 'MAJUSCULES'
            cell.Value = UCase(cell.Value)
        Case OptionProper '1ère lettre en majuscule'
            cell.Value = Application.WorksheetFunction.Proper(cell.Value)
        Case OptionSentence 'Phrase'
            text = UCase(Left(cell.Value, 1))
            text = text & LCase(Mid(cell.Value, 2, Len(cell.Value)))
            cell.Value = text
        Case OptionToggle 'inverser la casse'
            For i = 1 To Len(text)
                If Mid(text, i, 1) Like "[A-Z]" Then
                    Mid(text, i, 1) = LCase(Mid(text, i, 1))
                Else
                    Mid(text, i, 1) = UCase(Mid(text, i, 1))
                End If
            Next i
            cell.Value = text
        End Select
    Next
    
    'Décharge la boîte de dialogue'
    Unload UserForm1

End Sub

Tester le classeur modifier

Tester la macro complémentaire avant de convertir ce classeur. Pour simuler ce qui se produira lorsque le classeur sera devenu une macro complémentaire, vous devez le tester alors qu'un autre classeur est actif. Rappelez vous qu'une macro complémentaire n'est jamais le classeur actif. C'est donc uniquement en la testant depuis un autre classeur que vous pourrez détecter d'éventuelles erreurs.

  1. Ouvrez un nouveau classeur et entrez des données dans ces cellules. Pour ce test, entrez divers types d'information : du texte bien sûr, mais aussi des formules ou des valeurs. Ou alors, ouvrez un classeur existant et utilisez-le pour vos essais. En fait, il vaut mieux partir dans ce cas d'une copie de classeur, car les modifications dues à la macro complémentaire ne pourront pas être annulées.
  2. Sélectionnez une ou plusieurs cellules (voire des lignes ou des colonnes entières).
  3. Exécutez la macro en choisissant la nouvelle commande Changer la casse dans le menu contextuel de votre sélection.

Si la commande Changer la casse n'apparaît pas en bas du menu Outils, c’est très certainement parce que vous n'avez pas autorisé l'activation des macros à l'ouverture du classeur contenant la macro complémentaire. Fermez-le puis rouvrez-le, en veillant cette fois à autoriser l'activation des macros.

Ajouter des informations utiles modifier

Il est recommandé d'ajouter une description aux macros complémentaires, bien que ce ne soit pas obligatoire :

  1. Activez le classeur contenant la macro complémentaire Changer la casse (change casse. xlsm)
  2. Sous l'onglet Développeur, cliquez sur le bouton Panneau de documents dans la catégorie Modifier. Cliquez ensuite sur OK. Excel affiche le panneau Propriétés du document au-dessus de la barre de formule (comme l’on peut le voir sur la figure 3 ci-dessous).
  3. Tapez un nom dans le champ Titre. Il apparaîtra dans la boite de dialogue Macro complémentaire. Tapez par exemple Changer la casse.
  4. Entrez une description dans le champ Commentaires. Elle apparaîtra en bas de la boîte de dialogue Macro complémentaire lorsque la macro sera sélectionnée. Saisissez par exemple : Change la casse du texte sélectionné. Pour accéder à cet outil, cliquez droit pour ouvrir le menu contextuel.
  5. Cliquez sur la croix qui se trouve à droite du panneau pour le refermer.


 
Entrez la description de la macro complémentaire dans le panneau Propriétés du document.


Pour protéger votre code VBA, procédez comme expliqué plus haut, au début de la section "Les bases des macros complémentaires"

Générer la macro complémentaire modifier

À ce point, vous avez testé la macro Changer la casse et les essais sont concluants. Il s'agit à présent de créer la macro complémentaire :

  1. Activez l'éditeur VBE et , dans la fenêtre Projet, sélectionnez le classeur contenant la macro Changer la casse (normalement, changer la casse.xslm)
  2. Choisissez Outils/Propriétés de VBAProject puis cliquez sur l'onglet Protection.
  3. Cochez la case Verrouillez le projet pour l'affichage. Entrez ensuite un mot de passe (à deux reprises pour le confirmer)
  4. Cliquez sur OK.
  5. Enregistrer le classeur.
  6. Revenez sous Excel.
  7. Activez le classeur contenant la macro complémentaire puis choisissez Fichier/Enregistrer-sous.
  8. Déroulez la liste Type de fichier et choisissez Macro complémentaire Microsoft Excel (*.xlam)
  9. Cliquez sur Enregistrer.

Une nouvelle macro complémentaire est créée, avec l'extension de fichier .xlam. Le fichier XLSM original reste ouvert.

Ouvrir la macro complémentaire modifier

Pour éviter toute confusion, fermez le classeur XLSM avant d'ouvrir la macro complémentaire créée à partir de lui.

Ouvrez la macro complémentaire en procédant comme suit:

  1. Sous l'onglet Développeur, cliquez sur le bouton Compléments dans la catégorie de même nom. Excel affiche la boîte de dialogue Macro complémentaire.
  2. Cliquez sur le bouton Parcourir.
  3. Localisez et sélectionner la macro complémentaire que vous venez de créer.
  4. Cliquez sur OK pour fermer la boîte de dialogue Parcourir. La nouvelle macro complémentaire apparaît dans la boîte de dialogue Macro complémentaire comme on peut le remarquer sur la figure ci-dessous. Par ailleurs notez que la description que vous avez inscrit dans le panneau Propriétés du document (comme vu plus haut dans cette leçon), seront visible en bas de cette boîte de dialogue.
  5. Vérifiez bien que la case qui se trouve devant le nom de votre macro complémentaire est bien cochée.
  6. Cliquez sur OK pour fermer la boîte de dialogue et ouvrir la macro complémentaire.


 
La nouvelle macro complémentaire vient d’être ajoutée aux autres


Votre macro complémentaire est maintenant disponible dans tous vos classeurs. Tant que la case correspondante reste cochée dans la boîte de dialogue Macro complémentaire, elle sera disponible chaque fois que vous ouvrirez Excel (et que son emplacement sera accessible).

Distribuer la macro complémentaire modifier

Votre macro complémentaire peut être distribuée auprès d'autres utilisateurs d'Excel (si vous le souhaitez). Il suffit pour cela de leur remettre un exemplaire du fichier XLAM (ils n'ont pas besoin de la version XLSM). Dès qu'ils ouvrent la macro complémentaire, l'option Changer la casse apparaît en bas du menu contextuel associé aux cellules, aux lignes et aux colonnes. Si, et comme, le fichier est protégé, le code de la macro ne peut être examiné (à moins évidemment de connaître le mot de passe).

Modifier la macro complémentaire modifier

Une macro complémentaire enregistrée dans un fichier XLAM peut être éditée comme tout autre classeur (vous n'avez pas besoin du fichier XLSM). Il en conviendra qu’il faudra la déprotéger de la manière suivante :

  1. Ouvrez le fichier XLA si ce n’est pas déjà fait.
  2. Activez l'éditeur VBE
  3. Dans la fenêtre Projet, double-cliquez sur le nom du projet. Le mot de passe vous est demandé
  4. Entrez le mot de passe puis cliquez sur OK
  5. Modifiez le code selon vos besoins
  6. Enregistrez le fichier à partir de l'éditeur VBE, en cliquant sur Fichier/Enregistrer.



Annexes modifier

Bibliographie modifier

  • John Walkenbach, 2013, Programmation VBA pour Excel Pour Les Nuls, FIRST

Liens internes modifier

Liens externes modifier




Consolider des classeurs

Début de la boite de navigation du chapitre
Version imprimable
 
Chapitre no 20
Leçon : Macros-commandes VBA
Chap. préc. :Créer des macros complémentaires
Chap. suiv. :Sommaire
fin de la boite de navigation du chapitre
En raison de limitations techniques, la typographie souhaitable du titre, « Macros-commandes VBA : Version imprimable
Macros-commandes VBA/Version imprimable
 », n'a pu être restituée correctement ci-dessus.

Introduction modifier

La consolidation de classeurs Excel permet de regrouper sur un seul et même classeur diverses données provenant de plusieurs classeurs. Certains d’entre vous diront qu’il n’est pas nécessaire d’utiliser VBA pour effectuer ce genre de travail. Certes vous pouvez le faire via Microsoft Excel si vous n’avez que quelques documents à consolider. Toutefois, si vous êtes amenés à effectuer ce travail à partir de dizaine et de dizaine de classeurs Excel, vous verrez qu’il est préférable d’utiliser la fonction VBA pour économiser un maximum de temps.
Nous prendrons dans cette leçon l’exemple d’un groupe qui veut consolider les résultats de toutes ses filiales. En effet, il est fréquent dans un groupe d’avoir des fichiers Excel types que chaque filiale renvoie au siège qui doit consolider tous ces fichiers pour obtenir les résultats du groupe. Cependant la consolidation de plusieurs fichiers est chronophage et répétitive et peut rapidement être fastidieuse si elle n’est pas automatisée.
Il existe plusieurs façons de consolider des classeurs Excel selon le type de données et le format qu’ils contiennent. A travers des cas pratiques nous allons étudier deux d’entre elles que nous appellerons la consolidation par somme des données et la consolidation par regroupement de bases de données.

  • La consolidation par somme des données consiste à sommer dans un seul classeur le total des données se trouvant dans les classeurs à consolider.
  • La consolidation par regroupement de bases de données consiste à copier les différentes bases de données se trouvant dans les classeurs à consolider et les coller dans un seul classeur afin d’avoir toutes les données à un seul endroit.

Consolidation par somme de données modifier

Utilité modifier

Dans ce cas nous voulons sommer des tableaux identiques se trouvant dans différents classeurs sur un seul classeur. Ce type de consolidation, que l’on va appeler ici consolidation par somme de données, nécessite que les données de base soient constituées de chiffres et soient sous le même format.

Dans cet exercice nous allons prendre l’exemple d’une chaîne de magasin qui veut consolider les résultats de ses filiales. Chaque filiale renvoie le même fichier avec ses résultats sous forme de tableau qui comporte une liste de produits vendus, les quantités vendues et les chiffres d’affaires associés. Nous voulons sommer l’ensemble de ces résultats dans un seul tableau de synthèse afin d’obtenir le résultat consolidé du groupe. Manuellement il faudrait ouvrir tous les fichiers et sommer une à une chaque cellule dans un fichier de synthèse. Excel propose un outil appelé consolidateur de données qui permet de sommer toutes ces données cependant, cet outil n’est pas automatique et il faut sélectionner les bases à consolider à chaque fois que l’on veut faire la manipulation. Il est toutefois possible d’automatiser cette manipulation, grâce à VBA, afin de ne pas la reproduire à chaque fois que l’on veut consolider les données.

Application modifier

Résultats du magasin de Grenoble Résultats du magasin de Paris
   

Comme on peut le constater, les fichiers sont au même format, ils comportent tous les deux une liste de produits, les quantités vendues et les chiffres d’affaires correspondant. On observe toutefois que le magasin de Paris présente des produits qui ne sont pas présents dans le magasin de Grenoble. Cela ne pose pas de problème car VBA va recopier tous les produits et sommer les quantités vendues et les chiffres d’affaires.

Grâce à la consolidation on veut obtenir le tableau consolidé suivant regroupant tous les produits, les quantités vendues et le chiffre d’affaires :

Résultats consolidés des deux magasins
 

Décomposition du code VBA modifier

Sub Consolidation_somme()

'Sélectionne la cellule de départ du tableau consolidé:
Range ("A1").Select

' Crée le tableau consolidé:
    '1) Sélectionne les classeurs de données à consolider
    '2) Détermine les conditions de consolidation des données. Ici:
        'a) on somme les données (Function:=xlSum)
        'b) on rapatrie la ligne de titres (TopRow) et la colonne des produits (LeftColumn)
        'c) on ne crée pas de liens entre les classeurs de données et le classeur de synthèse
        
    Selection.Consolidate Sources:=Array( _
        "'E:\Wiki\Consolidation par somme de données\[Grenoble.xlsx]Feuil1'!R1C1:R1000C200", _
        "'E:\Wiki\Consolidation par somme de données\[Paris.xlsx]Feuil1'!R1C1:R1000C200"), _
        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
        
End Sub

Explications et analyse de la macro modifier

Conditions du tableau consolidé Exemples
Function :=xlSum : Somme les données consolidées Par exemple, le magasin de Grenoble a vendu 1 000 000 pommes et le magasin de Paris a vendu 1 210 000 pommes ce qui fait un total de ventes de 2 210 000 pommes
TopRow :=True : Rapatrie la ligne de titres dans le fichier consolidé. Si on met « False » on n’obtient pas la ligne de titres et on ne sait donc pas à quoi correspondent les chiffres  
LeftColumn :=True : Rapatrie la colonne de gauche (ici les noms de produits). Si on met « False » on n’obtient pas la colonne de gauche et on ne sait donc pas à quels produits correspondent les quantités et les chiffres d’affaires consolidés  
CreateLinks :=False : Ne crée pas de liens entre les fichiers. Si on met « True » on obtient le détail par magasin avec des liens entre les fichiers et des formules sommes pour calculer les résultats consolidés.  
On note toutefois que le titre "Produits" n'apparaît pas lorsque l’on sélectionne à la fois la condition TopRow :=True et la condition LeftColumn :=True

Remarques modifier

Pour éviter les erreurs et de devoir modifier le code VBA à chaque fois que l’on inclut des nouvelles lignes ou de nouvelles colonnes dans un des classeurs de données, il est conseillé de définir dès le départ une plage plus large que celle de votre tableau. Sinon vous risquez de ne pas prendre en compte toutes les données à consolider. Ici, nous avons défini une plage qui comprend toutes les cellules de la 1ère cellule de la première colonne du fichier (R1C1) jusqu’à la 1 000ème ligne de la 300ème colonne du fichier (R1000C200). Pour ajouter un nouveau classeur à la consolidation, il faut ajouter le lien d’accès à ce classeur et la plage de données dans le code (ex : "'E:\Wiki\Consolidation par magasins\[Grenoble.xlsx]Feuil1'!R1C1:R1000C200",)

Consolidation par regroupement de bases de données modifier

Utilité modifier

Dans ce cas, nous voulons regrouper plusieurs bases de données de même format dans une seule base de données globale. Ce type de consolidation, que l’on va appeler ici consolidation par regroupement de plusieurs bases de données, nécessite que les bases de données à consolider soient sous le même format. Les données qu’elles contiennent peuvent être de n’importe quel format (texte, nombre, date,…) excepté le format image car les données doivent être contenues dans des cellules.

Attention, si les cellules du fichier de synthèse dans lequel vous allez regrouper vos données ne sont pas au même format vous risquez d’avoir de petites surprises notamment si vous consolidez des données au format date. En effet si vous copiez une donnée sous format date dans une cellule au format standard votre date sera copiée en nombre. Par exemple la date 30.01.1993 deviendra 33999. Rassurez-vous vous ne perdez pas vos données, il suffit d’appliquer le bon format à vos cellules pour retrouver vos dates sous le bon format.

Dans cet exercice nous allons prendre l’exemple d’une entreprise qui possède plusieurs bases de données comprenant des coordonnées clients et qui veut regrouper toutes ces coordonnées dans une seule base de données.

Application modifier

Première base de données Deuxième base de données
   

Comme on peut le constater les données des deux classeurs sont sous le même format. Nous allons donc copier ces deux bases et les regrouper l’une à la suite de l’autre dans un seul classeur.

Grâce à la consolidation on veut obtenir la base de données consolidée suivante qui regroupe toutes les données contenues dans les différentes bases de données :

Base de données consolidée
 

Décomposition du code VBA modifier

Sub consolidation_regroupement()

'Stoppe l'actualisation de l'écran. Cela sert à masquer les actions de la macro
Application.ScreenUpdating = False

'Détermine le chemin d'accès aux fichiers
Dim chemin As String
Dim fichier As String
Dim extension As String

chemin = "C:\Users\Wiki\Consolidation par regroupement de bases de données\"
fichier = "Coordonnées clients "
extension = ".xlsx"

'Indique le nombre de fichiers à consolider
nbfichiers = 2

   For i = 1 To nbfichiers
   
   'Ouvre le fichier à consolider
    Workbooks.Open (chemin & fichier & i & extension)
    
    'Sélectionne la feuille où se trouvent les données
    Sheets("Feuil1").Select

    'Compte le nombre de lignes à copier
    n = WorksheetFunction.CountA(Range("A:A"))
    
    'Compte le nombre de colonnes à copier
    m = ActiveSheet.UsedRange.Columns.Count
    
    'Copie les données
    Range(Cells(2, 1), Cells(n, m)).Copy
    
    'Active le classeur de synthèse
    Windows("Synthèse.xlsm").Activate
    
    'Sélectionne la feuille où on va coller les données
    Sheets("Synthèse").Select

    'Compte le nombre de lignes non vides et ajoute 1 pour avoir le numéro de la première ligne vide
    c = WorksheetFunction.CountA(Range("A:A")) + 1
    
    'Sélectionne la première cellule vide
    Range("A" & c).Select
    
    'Colle les données
    ActiveSheet.Paste
    
    'Ferme la base de données qui a été consolidée et passe à la suivante
    Windows(fichier & i & extension).Close
    
    Next i

'Réactive l'actualisation de l'écran
Application.ScreenUpdating = True


End Sub

Explications et analyse de la macro modifier

  1. La première étape de ce code est de définir le chemin d’accès aux classeurs à consolider, leur nom et leur extension. Afin d’alléger le code et de rendre la macro plus facilement modifiable nous avons décidé d’utiliser une boucle qui va copier les données des bases une par une pour les coller dans la base consolidée. Pour cela il faut que les classeurs à consolider se trouvent tous dans le même répertoire de notre ordinateur et portent tous le même nom à l’exception d’un nombre qui permet de distinguer les classeurs. Ici nous avons appelé notre première base de données « Coordonnées clients 1 », la seconde « Coordonnées clients 2 », etc… Nous avons donc défini quatre variables :
    • Chemin : cette variable indique le répertoire dans lequel se trouvent les classeurs à consolider
    • Fichier : cette variable indique le nom du classeur à consolider. Vous noterez que dans le code nous avons nommé le classeur « Coordonnées clients » mais nous n’avons pas indiqué de numéro 1, 2, etc… C’est parce que nous avons enregistré cette information dans une variable à part qui sera ensuite gérée par la boucle.
    • Extension : cette variable indique le type d’extension du classeur (xls, xlsx, xlsm). Dans ce code il faut que tous les classeurs aient la même extension pour que la boucle fonctionne toutefois si vous choisissez de noter les noms des classeurs un à un vous pouvez utiliser des classeurs qui n'ont pas la même extension.
    • Nbclasseurs : cette variable indique le nombre de classeurs à consolider
  2. La seconde étape est donc la boucle qui est le cœur de la macro car c’est là que va se faire la consolidation des classeurs. La boucle est définie de 1 à nbclasseurs ce qui signifie qu’elle va tourner autant de fois qu’il y a de classeurs à consolider et répéter la même action pour chaque classeur.
  3. Tout d’abord la macro va ouvrir le premier classeur à consolider. Elle va trouver ce classeur grâce aux variables que l’on a défini au début et qui, toutes assemblées, donnent l’accès au classeur. La variable i qui va de 1 à nbclasseurs indiquera donc le numéro du classeur à traiter en fonction de la boucle. Par exemple, le premier tour de boucle va traiter le classeur « Coordonnées clients 1 », le second tour de boucle va traiter le classeur « Coordonnées clients 2 » et ainsi de suite.
  4. La macro sélectionne ensuite l’onglet du classeur où se trouvent les données à consolider. Attention, il faut penser à changer le nom de l’onglet s’il ne correspond pas à celui de votre classeur. Bien entendu tous les classeurs doivent avoir un onglet du même nom dans lequel se trouveront les données
  5. On compte ensuite le nombre de lignes et de colonnes qui comprennent des données afin de définir la plage de données à consolider et on copie ces données. Attention: dans cet exemple, la macro compte le nombre de cellules non vides pour calculer la plage de données à copier. Cela signifie que s'il y a des trous dans la base (lignes vides), la plage de données qui sera prise en compte par la macro risque de ne pas comprendre toutes les données. Exemple: Si j’ai de données sur la colonne de la ligne 1 à la ligne 5 et de la ligne 7 à la ligne 9 j'aurais au total de 8 lignes remplies. Or si je donne cette valeur à ma variable qui calcule la plage de données ma macro va sélectionner la plage A1 à A8 alors que j’ai des données en A9, je ne vais donc pas récupérer toutes mes données. NB: Cette problématique est typique à ce code, il est toutefois possible de l'adapter pour contourner le problème.
  6. On active le classeur où l’on va consolider les données et on sélectionne l’onglet où se trouve la base de données consolidées
  7. On compte alors le nombre de lignes qui ne sont pas vides dans cet onglet et on ajoute 1 à ce nombre afin d’obtenir le numéro de la première ligne vide à partir de laquelle on va pouvoir coller les données et on colle les données.
  8. On ferme le classeur duquel on a extrait les données.
  9. On fait un tour de boucle afin de traiter le prochain fichier

Remarques modifier

Dans notre exemple tous les classeurs de base de données à consolider se trouvent dans le même répertoire, portent le même nom et sont seulement différenciés par un numéro accolé à la fin du nom du classeur. Ici nos classeurs sont nommés « Coordonnées clients 1 » et « Coordonnées clients 2 » ce qui permet de faire une boucle dans la macro avec les noms de fichiers. Chaque classeur a également la même extension. Si un des fichiers a un nom ou une extension différente ou se trouve dans un autre répertoire il ne sera pas consolidé par la macro.

Cette consolidation ne fait que regrouper plusieurs classeurs. Si des mêmes données sont présentes dans plusieurs classeurs vous aurez alors des doublons dans votre classeur consolidé. Vous pourrez supprimer ces doublons soit manuellement soit en créant une nouvelle macro que vous exécuterez à la suite de votre macro de consolidation.

Conclusion modifier

En conclusion, la consolidation de classeurs via VBA permet un gain de temps considérable à condition que les bases de données à consolider respectent toutes le même format.
Nous avons tenté de simplifier les processus au maximum cependant il est possible de les modifier et de les améliorer. Notamment, dans la consolidation que l’on a appelée consolidation par regroupement de bases de données il est possible de rapatrier les données sans ouvrir les classeurs sources mais cela complique le code c’est pourquoi nous avons choisi dans notre exemple d'ouvrir les fichiers un à un puis de le refermer.
Il existe également d'autres méthodes pour consolider des classeurs mais nous avons voulu nous concentrer sur ces deux méthodes qui sont très couramment utilisées dans les entreprises et qui sont faciles et rapides à exécuter.
NB: les noms que nous avons attribué aux méthodes de consolidation (consolidation par somme de données et consolidation par regroupement de bases de données) sont purement de notre fait afin d'illustrer leur action, vous ne retrouverez probablement ces appellations nulle part ailleurs.


  GFDL Vous avez la permission de copier, distribuer et/ou modifier ce document selon les termes de la licence de documentation libre GNU, version 1.2 ou plus récente publiée par la Free Software Foundation ; sans sections inaltérables, sans texte de première page de couverture et sans texte de dernière page de couverture.