Leçons de niveau 14

Macros-commandes VBA/Version imprimable

Une page de Wikiversité.
Sauter à la navigation Sauter à la recherche
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 ».

Sommaire

Les Macros Excel

Début de la boite de navigation du chapitre
Version imprimable
Icône de la faculté
Chapitre no 1
Leçon : Macros-commandes VBA
Retour auSommaire
Chap. suiv. :Pour démarrer
fin de la boite de navigation du chapitre
Icon falscher Titel.svg
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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]


Un heureux mariage[modifier | modifier le wikicode]

Un Langage VISUAL BASIC[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

  1. (anglais) 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%C3%A9er-et-enregistrer-toutes-vos-macros-dans-un-classeur-unique-aa439b90-f836-4381-97f0-6e4c3f5ee566
  5. http://www.cpearson.com/excel/installinganxla.aspx

Annexes[modifier | modifier le wikicode]

Bibliographie[modifier | modifier le wikicode]

  • 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 | modifier le wikicode]

Liens externes[modifier | modifier le wikicode]




Pour démarrer

Début de la boite de navigation du chapitre
Version imprimable
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]


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 | modifier le wikicode]


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 | modifier le wikicode]


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 | modifier le wikicode]


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 | modifier le wikicode]

Bibliographie[modifier | modifier le wikicode]

  • 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 | modifier le wikicode]



Architecture

Début de la boite de navigation du chapitre
Version imprimable
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

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 | modifier le wikicode]

Les procédures[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

  • 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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Searchtool.svg Article détaillé : 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 | modifier le wikicode]

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 | modifier le wikicode]

Searchtool.svg Article détaillé : Manipulation des variables.

Les commentaires[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Searchtool.svg Article détaillé : 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 | modifier le wikicode]

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 | modifier le wikicode]

Bibliographie[modifier | modifier le wikicode]

  • 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 | modifier le wikicode]

Liens externes[modifier | modifier le wikicode]




Les types en VB

Début de la boite de navigation du chapitre
Version imprimable
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

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 | modifier le wikicode]

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 Non
IsDate Oui Oui Oui Oui Oui
IsDBNull Non Non Non Non Non Non Oui Non Non
IsEmpty Oui Oui Oui Non Non Non Non
IsError Oui Oui Non Non Oui Non Non
IsMissing Oui Oui Non Non Non Non Non Non
IsNothing Non Non Non Non Non Non Oui Non Non
IsNull Oui Oui Oui Non Non Oui
IsNumeric Oui Oui Oui Oui Oui
IsObject Oui Oui Oui Non Non Non Non
IsReference Non Non Non Non Non Non Oui Non Non

Déclaration des types[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

  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
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

Syntaxe[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Affectation[modifier | modifier le wikicode]

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

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


Opérations mathématiques[modifier | modifier le wikicode]

Usuelles[modifier | modifier le wikicode]

  • 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 | modifier le wikicode]

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

Arrondir un nombre[modifier | modifier le wikicode]

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

Opérations logiques[modifier | modifier le wikicode]

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 | modifier le wikicode]

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


Panneau d’avertissement 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 | modifier le wikicode]

Longueur de la chaîne[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

  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
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

Test conditionnel simple : le bloc If[modifier | modifier le wikicode]

Exemple :

if (testCondition) then
   instruction1
else
   instruction2
end if

Distinction de cas : Select Case[modifier | modifier le wikicode]

'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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

À 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
Panneau d’avertissement 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
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]


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 | modifier le wikicode]

Les avantages[modifier | modifier le wikicode]

  • 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 | modifier le wikicode]

  • 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 | modifier le wikicode]


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 | modifier le wikicode]

Bibliographie[modifier | modifier le wikicode]

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

Liens internes[modifier | modifier le wikicode]

Liens externes[modifier | modifier le wikicode]




Boite de dialogue

Début de la boite de navigation du chapitre
Version imprimable
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

VBA - boite de dialogue - choix de l'imprimante.png

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 | modifier le wikicode]

Bibliographie[modifier | modifier le wikicode]

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

Liens internes[modifier | modifier le wikicode]

Liens externes[modifier | modifier le wikicode]



Création de Formulaire

Début de la boite de navigation du chapitre
Version imprimable
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

Définition du formulaire[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Accès à l'éditeur Visual Basic[modifier | modifier le wikicode]

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 | modifier le wikicode]

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

Userform Vierge

Les propriétés[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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.

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

SpecialEffect[modifier | modifier le wikicode]

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 | modifier le wikicode]

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

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

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

Le label[modifier | modifier le wikicode]

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

Le TextBox[modifier | modifier le wikicode]

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.

Aperçu avec les TextBox

La ComboBox[modifier | modifier le wikicode]

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 | modifier le wikicode]

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
Aperçu final du formulaire

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Comment afficher un formulaire grâce à un bouton ?[modifier | modifier le wikicode]

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 Developpeur 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 | modifier le wikicode]

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 le wikicode]

Modifier les couleurs d'un bouton[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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

Intitulé[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

 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 | modifier le wikicode]

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 | modifier le wikicode]

Boîtes de texte[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Exemple d'initialisation d'un formulaire[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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

Les formulaires Access et Excel[modifier | modifier le wikicode]

Description[modifier | modifier le wikicode]

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 | modifier le wikicode]

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.

Panneau d’avertissement 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 | modifier le wikicode]

ControlesCommuns VB2005.png

Frame[modifier | modifier le wikicode]

Cadre permettant de subdiviser le formulaire en plusieurs sous-parties. Icone Frame VB.JPG

Button[modifier | modifier le wikicode]

Icone CommandButton VB.JPG

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

Label[modifier | modifier le wikicode]

Icone Label VB.JPG Intitulé.

LinkLabel[modifier | modifier le wikicode]

Hyperlien.

TextBox[modifier | modifier le wikicode]

Icone TextBox VB.JPG Champ pouvant être complété par l'utilisateur.

CheckBox[modifier | modifier le wikicode]

Icone CheckBox VB.JPG Case à cocher pour questionnaire à choix multiple.

OptionButton[modifier | modifier le wikicode]

Icone OptionButton VB.JPG

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 | modifier le wikicode]

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

Image[modifier | modifier le wikicode]

Icone Image VB.JPG Icone Image VB.JPG

PictureBox[modifier | modifier le wikicode]

Icone PictureBox VB.JPG

Ligne - Line[modifier | modifier le wikicode]

Icone Line VB.JPG

Forme géométrique - Shape[modifier | modifier le wikicode]

Icone Shape VB.JPG

Explorateur de disques[modifier | modifier le wikicode]

Icone DriveListBox VB.JPG

Explorateur de dossiers[modifier | modifier le wikicode]

Icone DirListBox VB.JPG

Explorateur de fichiers[modifier | modifier le wikicode]

Icone FileListBox VB.JPG

Panneau d’avertissement 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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Annexes[modifier | modifier le wikicode]

Bibliographie[modifier | modifier le wikicode]

  • 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 | modifier le wikicode]

Liens externes[modifier | modifier le wikicode]



Création de Fonction

Début de la boite de navigation du chapitre
Version imprimable
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]


Différence entre les mots clés Sub et Fonction[modifier | modifier le wikicode]

  • 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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 = multiplier(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 = multiplier(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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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

  • 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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 le 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é 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 | modifier le wikicode]

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 | modifier le wikicode]

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

Calcul des intérêts[modifier | modifier le wikicode]

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 10000€ 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 | modifier le wikicode]

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 divisé le résultat par 100. Par exemple pour un montant de 12000€ 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 | modifier le wikicode]

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 multiplié le montant HT par le taux de TVA puis va ajouté 100 au résultat obtenu, et enfin va divisé le résultat par 100. Par exemple pour un montant HT de 1000€ 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 | modifier le wikicode]

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 20000€ et une Valeur ajoutée de 10000, la fonction RentabilitéGlobale trouve une rentabilité de 2. Cela facilitera donc le travail de tous les comptables et autres gestionnaires.



Annexes[modifier | modifier le wikicode]

Bibliographie[modifier | modifier le wikicode]

  • 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 | modifier le wikicode]

Liens externes[modifier | modifier le wikicode]



Création de Graphique

Début de la boite de navigation du chapitre
Version imprimable
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

Qu’est-ce qu’un graphique ?[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Créer une base de donnée[modifier | modifier le wikicode]

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 utiles 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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Test avec enregistrement[modifier | modifier le wikicode]

Démarche[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Démarches[modifier | modifier le wikicode]


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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Les constantes utilisées pour choisir un type de graphique[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Bibliographie[modifier | modifier le wikicode]

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

Liens internes[modifier | modifier le wikicode]

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

Liens externes[modifier | modifier le wikicode]

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
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

Qu’est-ce qu’un tableau croisé dynamique ?[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Renseignement des données[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Test avec enregistrement[modifier | modifier le wikicode]

Démarche[modifier | modifier le wikicode]

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 | modifier le wikicode]

  • 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 | modifier le wikicode]

  • 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 | modifier le wikicode]

Démarches[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Appliquer une fonction[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Bibliographie[modifier | modifier le wikicode]

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 | modifier le wikicode]

Liens externes[modifier | modifier le wikicode]

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
Icône de la faculté
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
Icon falscher Titel.svg
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.
Wikilivres possède un livre à propos de « Modifier des fichiers en VBS ».

Manipulation de répertoires[modifier | modifier le wikicode]

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 | modifier le wikicode]

Sélectionner un fichier ouvert[modifier | modifier le wikicode]

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 | modifier le wikicode]

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()
Panneau d’avertissement 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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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

Propriétés des fichiers[modifier | modifier le wikicode]

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 | modifier le wikicode]

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
Panneau d’avertissement 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 | modifier le wikicode]

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
Panneau d’avertissement 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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

  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
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

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 | modifier le wikicode]

Fonction GetFolderInfo[modifier | modifier le wikicode]

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

Liens externes[modifier | modifier le wikicode]



Devenez un connaisseur

Début de la boite de navigation du chapitre
Version imprimable
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Les nombres VBA[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

  • 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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]


La boucle For ... Next[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Bibliographie[modifier | modifier le wikicode]

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

Liens internes[modifier | modifier le wikicode]

Liens externes[modifier | modifier le wikicode]




Devenez un expert

Début de la boite de navigation du chapitre
Version imprimable
Icône de la faculté
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
Icon falscher Titel.svg
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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Les variables[modifier | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

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 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 | modifier le wikicode]

Usages complexes[modifier | modifier le wikicode]

  • 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 | modifier le wikicode]

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 | modifier le wikicode]

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 | modifier le wikicode]

Les instructions conditionnelles complexes[modifier | modifier le wikicode]

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 | modifier le wikicode]

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