TP Sur VBA Pour Excel Livre 2021 [PDF]

Zitiervorschau

TP sur VBA pour Excel (version 2021 et Microsoft 365) Créez des applications professionnelles : Exercices et corrigés Ce livre est destiné aux utilisateurs d’Excel 2021 ou d’Excel Microsoft 365 voulant se former à une véritable utilisation pratique du langage VBA appliqué à Excel. Il est donc recommandé d’aborder l’ouvrage en ayant une connaissance suffisante des fondamentaux du langage. Avec plus de 230 exercices de difficulté croissante, basés sur des exemples concrets, l’ouvrage couvre les domaines suivants : l’automatisation des tâches répétitives, l’ajout de fonctions personnalisées, le contrôle des principaux objets d’Excel (classeur, feuille, cellule, graphique), la collaboration et l’échange de données avec les autres applications de la suite Office (2021 ou Microsoft 365), la prise en charge des informations au format XML, la publication de documents Excel sur le Web, l’analyse de données, la gestion du multimédia, la gestion du système de fichiers, ou encore la mise en oeuvre de requêtes basées sur la technologie Power Query. Au terme de cette mise en pratique, vous pourrez étendre les fonctionnalités d’Excel pour répondre à vos besoins. Vous disposerez de tous les éléments qui vous permettront de développer une application professionnelle avec VBA pour Excel 2021 ou Microsoft 365. Les fichiers nécessaires à la réalisation des exercices, ainsi que les corrigés, sont en téléchargement depuis cette page. 179 QCM - 238 travaux pratiques et leurs corrigés - Plus de 40 H de mise en pratique. Auteur(s) Claude DUIGOU

Ingénieur et MBA HEC, Claude DUIGOU exerce ses activités de Formateur et de Consultant en entreprise depuis plus de 20 ans. Ses différentes missions le conduisent à intervenir auprès d'entreprises de tailles diverses pour la réalisation, notamment en VBA, de logiciels dans des domaines tels que l'aide à la décision, le contrôle de gestion, la gestion des stocks, la gestion du temps, le contrôle budgétaire. Les systèmes qu'il développe font largement appel aux possibilités et fonctionnalités avancées d'Excel. Cette expérience complète et variée lui permet de proposer un livre très pragmatique sur VBA Excel. Réf. ENI : TP21EXCV | ISBN : 9782409033353

Public du livre Vous êtes utilisateur d’Excel Microsoft 365 ou d’Excel 2021 et vous souhaitez vous initier à VBA (Visual Basic for Application) pour Excel ou approfondir vos connaissances de ce langage, ce livre vous est destiné. Des notions élémentaires en programmation sont recommandées mais non indispensables dans la mesure où la première partie de l’ouvrage est consacrée aux fondamentaux et propose une introduction à la programmation objet. S’agissant d’un recueil d’exercices pratiques basés sur des exemples concrets, nous vous recommandons pour tout complément théorique de vous reporter au livre VBA Excel (versions 2019 et Office 365) - Programmer sous Excel : macros et langage VBA, dans la collection Ressources informatiques des Éditions ENI.

Objectif du livre Au terme de votre pratique, vous serez en mesure d’automatiser des tâches répétitives, d’ajouter des fonctions et caractéristiques répondant à des besoins spécifiques, de personnaliser totalement votre espace de travail et d’ouvrir Excel à son environnement (applications Microsoft et Internet). Vous disposerez en définitive de tous les éléments qui permettent de développer une application "clés en main".

Utilisation du livre Bien qu’il soit possible d’aborder chaque chapitre indépendamment, l’ordre prévu influe sur le bon déroulement de l’apprentissage. Il est donc conseillé de suivre la chronologie du livre pour obtenir une meilleure efficacité. Chaque chapitre présente différentes sections :     

Objectifs Prérequis Énoncés Indices Corrigés (en fin d’ouvrage)

1. Objectifs Cette section décrit le contenu des exercices qui vont être abordés tout au long du chapitre.

2. Prérequis

Cette section permet de vérifier vos connaissances théoriques. Elle se compose de questions à choix multiples ou de questions ouvertes portant sur les notions qui doivent être acquises avant d’aborder la réalisation des exercices. Chacune des questions de cette section est numérotée afin de retrouver plus facilement la réponse à la fin de l’ouvrage.

3. Énoncés et corrigés Cette section regroupe les énoncés des exercices. Comme pour les prérequis, chaque énoncé et sa correction sont numérotés. Pour réaliser certains exercices, vous devez télécharger ou visualiser des éléments. Les fichiers correspondants sont disponibles en téléchargement depuis la page Informations générales. Pour la suite de cet ouvrage, cette information est précédée du symbole

.

4. Indices Les indices fournis pour certains points de l’énoncé rappellent les points importants de théorie à connaître. Ils permettent d’avancer plus vite dans la suite de l’exercice sans toutefois dévoiler les solutions.

Aide à la réalisation des travaux pratiques 1. Écriture du code VBA L’interface de programmation : Visual Basic Editor Pour écrire vos procédures, vous devez accéder à l’éditeur de Visual Basic, Visual Basic Editor (VBE). Sous Excel, choisissez dans le ruban l’onglet Développeur puis l’icône Visual Basic, ou utilisez le raccourci-clavier : [Alt][F11]. Attention ! si l’onglet Développeur n’apparaît pas dans le ruban, faites Fichier - Options Personnaliser le ruban et cochez la case Développeur dans la liste des onglets à droite de la boîte de dialogue.

L’espace où vous saisissez votre code VBA est appelé "fenêtre de code". Elle comporte deux parties : la partie déclaration et la partie procédures. Il faut écrire toutes les procédures après les déclarations. Vous pouvez constater que VBE n’est qu’une partie de l’environnement de développement VBA pour Excel, l’IDE (Integrated Development Environment). La fenêtre Projet affiche les projets ouverts avec trois types de dossiers contenant respectivement : les objets Excel (feuilles de calcul et classeur), les formulaires (maladroitement désignés par "feuilles") et les modules. La fenêtre Propriétés présente les propriétés de l’objet sélectionné, ici l’objet ThisWorkBook. La fenêtre Exécution permet de tester du code et d’observer le résultat en mode "console". D’autres fenêtres sont disponibles permettant de personnaliser son environnement de développement. Les intitulés de ces fenêtres sont disponibles dans le menu Affichage.

Les modules Pour créer un module, choisissez dans le menu Insertion - Module. Les modules sont destinés à recevoir les procédures et fonctions publiques. La déclaration des variables Pour éviter les erreurs de dénomination et programmer efficacement, il faut déclarer explicitement toutes les variables. Pour étendre cette déclaration à l’ensemble du projet, choisissez dans le menu Outils - Options puis l’onglet Éditeur. Choisissez l’option Déclaration des variables obligatoire.

Les types de données La précision (ou déclaration) du type de données que peuvent stocker les variables ne fait pas partie des options proposées. Toujours dans un souci d’efficacité, lors de l’écriture de votre code toutes les variables doivent être déclarées en précisant le type de données. Choisissez le plus spécifique. Par exemple, pour une variable destinée à stocker des entiers courts, le type Integer (-32 768 à 32 767) convient mieux que le type Long (-2 147 483 648 à 2 147 483 647). Le caractère de continuation Une instruction VBA peut être longue et dépasser la largeur d’une page écran. Pour l’écrire sur plusieurs lignes, il faut insérer le caractère de soulignement à l’endroit où vous voulez effectuer une coupure, précédé d’un espace, puis appuyer sur la touche [Entrée]. Exemple : Private Sub cCalendrier_Click()      Dim . . .      . . .      With . . .        For . . .          . . .          If . . .            frmCalendrier.Label1 = strMessage & " : " _             & CDate(frmCalendrier.lstDates. _             List(intCompteur, 1))          End If        Next      End With   End Sub

L’indentation

L’exemple précédent vous montre la structure d’une procédure avec indentation. Les décalages correspondent à des blocs logiques de prise de décision ou de structures répétitives. Le code est ainsi plus lisible et plus aisé à corriger en cas d’erreur. Pour indenter une ligne ou plusieurs lignes (à sélectionner auparavant), appuyez sur la touche [Tab] pour une tabulation et sur la combinaison de touches [Shift][Tab] pour effectuer un retrait. Vous pouvez préciser la largeur de la tabulation par le menu Outils - Options.

2. Aide à la saisie du code VBA Lors de la saisie d’un mot-clé, vous pouvez saisir les premières lettres suivies de la combinaison de touches [Ctrl][Espace]. VBE complète votre saisie ou vous propose une liste.

L’appui sur la touche [Tab] reporte votre choix à la suite du curseur. La touche [Entrée] effectue la même action avec en plus un saut de ligne. Pour bénéficier de l’aide complète, accédez à la boîte d’options (cf. ci-dessus) puis vérifiez que toutes les options de la partie Paramètres du code sont cochées. La Vérification automatique de la syntaxe et le Complément automatique des instructions sont des assistances particulièrement utiles. Exemple :

3. Test du code VBA Pour tester votre code, placez le curseur dans une procédure (Sub) et appuyez sur la touche [F5]. Une fois le code validé, vous pouvez si nécessaire créer un bouton formulaire dans une feuille de calcul Excel qui appelle cette procédure lorsque vous cliquez sur le bouton. Pour cela, dans l’onglet Développeur, groupe Contrôles, cliquez sur Insérer, puis choisissez le bouton dans la liste des Contrôles de formulaire.

Une fois le bouton de formulaire implanté par tracé de sa diagonale, VBA vous propose de lui affecter une procédure :

Vous pouvez aussi créer un bouton de type Contrôle ActiveX dans une feuille de calcul. Une fois le bouton placé sur la feuille de calcul, cliquez à l’aide du bouton droit et choisissez l’option de menu Visualiser le code. Vous accédez alors à la procédure événementielle Click du bouton. À l’intérieur de la procédure Click vous pouvez soit inclure directement le code VBA du traitement voulu, soit appeler une procédure déjà existante. Exemple :

En cas d’erreur lors du test, VBE bloque le déroulement de la procédure et vous informe de la nature du problème par un message. Exemple :

VBE vous signale ensuite la procédure où l’erreur s’est produite.

Pour arrêter le contrôle et procéder à la correction, choisissez dans le menu Exécution Réinitialiser ou cliquez sur le bouton Réinitialiser de la barre d’outils standards :

4. Débogage Il est possible de procéder à une vérification syntaxique globale du code, en choisissant dans la barre de menus Débogage - Compiler VBAProject. Ceci est particulièrement intéressant pour des projets conséquents comportant de nombreuses procédures. Vous disposez également d’un environnement complet pour le débogage de vos procédures. Nous allons utiliser le débogueur au travers d’un exemple simple. Le tableau suivant doit être rempli avec des nombres de 1 à 5. Ensuite le total est affiché. 1 2 3 4 5 15 Ligne Instruction 1 Sub Exemple() 2 Dim intCompteur As Integer 3 Dim lngTotal As Long 4 Dim intTableau(0 To 5) As Integer 5 For intCompteur = 1 To 5 6    intTableau(intCompteur-1) = intCompteur 7    lngTotal = lngTotal + intCompteur 8 Next intCompteur 9 End Sub

Affichage simple des données Pour vérifier que le total est bien égal à 15, on peut faire appel aux instructions MsgBox ou Debug.Print. Pour contrôler la valeur de chaque case du tableau, Debug.Print, qui affiche en une seule fois les résultats dans la fenêtre d’exécution, est plus approprié.

Ligne Instruction 1 Sub Exemple() 2 Dim intCompteur As Integer 3 Dim lngTotal As Long 4 Dim intTableau(0 To 5) As Integer 5 For intCompteur = 1 To 5 6    intTableau(intCompteur-1) = intCompteur 7    Debug.Print intCompteur 8    lngTotal = lngTotal + intCompteur 9 Next intCompteur 10 MsgBox lngTotal 11 End Sub Interrompre le programme Pour interrompre l’exécution d’un programme, appuyez sur les touches [Ctrl][Pause/Attn]. Faites un essai par exemple avec : Dim intTableau (0 To 5000) As Integer   For intCompteur = 1 To 5000

Cet arrêt forcé est bien utile pour sortir d’une boucle sans fin. L’IDE affiche une boîte de dialogue.

Le bouton Débogage permet de revenir au code VBA, à l’instruction où l’arrêt s’est produit, que cet arrêt soit forcé ou qu’il soit dû à une erreur. Barre d’outils de débogage Pour faire apparaître cette barre d’outils, utilisez le menu Affichage - Barre d’outils.

Placer un point d’arrêt Un point d’arrêt permet d’interrompre le programme à une instruction précise de la procédure.

Placez le curseur devant l’instruction Next intCompteur puis cliquez sur le bouton

.

Le point d’arrêt apparaît sous forme d’un cercle marron dans la barre grise à gauche de la fenêtre de code.

La procédure s’arrête à l’instruction comportant le point d’arrêt (celle-ci n’est pas exécutée). Pour poursuivre le programme, appuyez sur [F5] ou cliquez sur le bouton . Pour supprimer le point d’arrêt, le curseur étant sur la même ligne, cliquez à nouveau sur le bouton

.

Pas à pas détaillé Cette fonctionnalité permet d’exécuter la procédure instruction par instruction. Ainsi, pour la boucle For-Next de la procédure Exemple, l’exécution va se répéter 5000 fois ! Pour arrêter l’exécution, cliquez sur le bouton

.

Testez à nouveau la procédure en limitant, cette fois-ci, le compteur à 5. Placer un espion (espion express) Un espion permet de connaître la valeur de la variable sélectionnée. Désactivez ou supprimez les instructions permettant l’affichage des données. ‘Debug.Print intCompteur     ...  ‘MsgBox lngTotal

Nous allons recourir à un espion pour connaître la valeur des variables intCompteur et lngTotal.  Lancez une exécution pas à pas détaillée. Le programme s’arrête une première fois à la ligne 5. Sélectionnez la variable intCompteur puis cliquez sur le bouton Espion Express . Une boîte de dialogue indique la valeur contenue dans la variable intCompteur.

Si en plus vous désirez visualiser les différentes valeurs prises par les variables, cliquez sur le bouton Ajouter. La fenêtre Espions est affichée.

Poursuivez le pas à pas. Faites de même avec la variable lngTotal.

Le principe est le même qu’il s’agisse d’une variable de type scalaire ou de type objet. Il est ainsi possible de connaître les valeurs des propriétés d’un objet si celles-ci sont appelées à être modifiées. Il est également possible de sélectionner une variable et de la faire glisser dans la fenêtre Espions. Consultez l’aide en ligne de VBA pour plus d’informations sur l’utilisation du débogueur.

5. Commenter le code Il est particulièrement recommandé de commenter le code. Les commentaires facilitent la compréhension du code, sa maintenance et les évolutions possibles. VBA utilise l’apostrophe pour ouvrir une ligne de commentaire.

6. L’onglet Développeur Nous avons vu précédemment dans ce chapitre comment faire apparaître l’onglet Développeur si celui-ci n’est pas affiché.

7. Ajouter un contrôle à une feuille de calcul Cliquez sur l’onglet Développeur puis sur le bouton suivant :

Vous pouvez maintenant placer facilement un contrôle de type formulaire ou un contrôle ActiveX sur la feuille de calcul.  Contrôles de formulaire Les contrôles de formulaire requièrent l’affectation d’une macro-commande. Placez un contrôle sur la feuille de calcul puis choisissez une macro.

Contrôles ActiveX Placez un contrôle sur la feuille de calcul puis effectuez un clic droit sur celui-ci. Choisissez ensuite Visualiser le code. Vous basculez alors dans la fenêtre de VBE.

Complétez la procédure en saisissant votre code.

Pour tester le contrôle, désactivez le mode Mode Création en cliquant sur le bouton

.

Après utilisation, pour modifier le contrôle, cliquez sur le même bouton.

8. Création de formulaire Accédez à l’IDE et créez un nouveau formulaire.

Au besoin, affichez la boîte à outils par le menu Affichage. Choisissez ensuite les contrôles à ajouter et déposez-les dans le formulaire.

9. Ajout de contrôles supplémentaires à la boîte à outils

La boîte à outils contient par défaut un certain nombre de contrôles : ce sont les contrôles VBA. Pour en ajouter d’autres, effectuez un clic droit sur celle-ci puis cochez les nouveaux contrôles à ajouter à la boîte.

10. Aide sur les API Windows Téléchargez l’application ApiViewer, les données ainsi que le Language Pack French sur le site ActiveVB : http://www.activevb.de/rubriken/apiviewer/index-apiviewereng.html

Lancez l’exécutable apv2004-v310.exe.

Décompressez le fichier apvdata19122008.zip. Lancez l’application. Sélectionnez le fichier de données : Win32api.apv, menu Fichier - Options - Démarrage. Testez la visionneuse.

11. Ajout de nouvelles librairies au projet Librairies par défaut Pour fonctionner, VBA dispose d’un minimum de librairies (ou bibliothèques) référencées par défaut.

Référence pour les formulaires Lors de la création d’un formulaire, la DLL Microsoft Forms 2.0 Object Library est automatiquement ajoutée :

Ajout de nouvelles références Il suffit de cocher, dans la boîte de références, celles qui sont utiles au projet.

12. Enregistrement des classeurs Les classeurs Excel Microsoft 365 comportant du code VBA doivent être enregistrés dans un format particulier dont l’extension est .xlsm.

Introduction Durée : 55 minutes Mots-clés procédure générale, procédure événementielle, fonction, portée, argument, paramètre, passage d’arguments par référence, par valeur, appel d’une procédure Objectifs Connaître les différents types de procédure et leur portée. Savoir les utiliser pour structurer le code en unités logiques pour une meilleure maintenance et évolutivité. Découvrir la possibilité de constituer des bibliothèques de procédures pouvant être utilisées dans différents projets.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1.         2.       3.       4.       5.       6.  

Les trois types de procédures sont : a. Sub b. Call c. Function d. Property Les procédures sont généralement classées en : a. procédures générales. b. procédures événementielles. c. procédures objet. Une procédure débute toujours par : a. Public b. Private c. Sub Une procédure Sub peut : a. retourner une valeur. b. modifier la valeur d’une variable qui lui est passée. c. être imbriquée avec d’autres procédures. Une procédure Function : a. retourne une valeur. b. peut être utilisée par Excel. c. réagit à un événement. Par défaut, les procédures sont : a. publiques.

  b. privées.   c. locales. 7. Les procédures suivantes peuvent être appelées par toutes les procédures du projet :   a. MaProcédure1()   b. Private Sub MaProcédure2()   c. Public Sub MaProcédure3() 8. La procédure ci-après n’est pas correcte car : Public Sub MaProcédureA()     MaProcédureB()  End Sub

     

a. MaProcédureB doit être précédé du mot Sub. b. MaProcédureB ne doit pas avoir de parenthèses. c. il est interdit d’inclure le nom d’une procédure dans le corps d’une autre procédure. 9. Quand une variable est passée par valeur à une procédure, celle-ci :   a. dispose seulement d’une copie de cette variable.   b. peut accéder au contenu de la variable proprement dite et modifier sa valeur. 10. Les arguments (ou paramètres) d’une procédure peuvent parfois être très nombreux. Lors de l’appel d’une procédure, il est possible de spécifier uniquement ceux dont on a besoin en utilisant :   a le passage par référence.   b les paramètres nommés.   c le passage par valeur.

Énoncé 1.1 Créer et utiliser une procédure privée Durée estimative : 5 minutes Ajoutez dans l’environnement VBE, un module nommé Module1. Créez dans ce module une procédure privée nommée DemandeNom. Récupérez la saisie de l’utilisateur dans une variable. Voici le résultat à obtenir.

Indice Dans la partie déclaration, saisissez : Dim strNom As String

Pour afficher la boîte de dialogue et récupérer le nom saisi : strNom = InputBox("Quel est votre nom ?")

Note Le curseur étant placé dans la procédure, appuyez sur la touche [F5] pour l’exécuter.

Énoncé 1.2 Créer et utiliser une procédure publique Durée estimative : 5 minutes Créez la procédure publique BonjourVous, affichant la boîte suivante :

Indice Pour l’affichage : MsgBox ("Bonjour. Bonne lecture.")

Énoncé 1.3 Appeler une procédure à partir d’une autre procédure Durée estimative : 5 minutes Créez la procédure BonjourVousAppelProc qui appelle la procédure DemandeNom puis qui affiche un message personnalisé de bienvenue. Exemple :

Indice Pour l’affichage : MsgBox ("Bonjour") & strNom & (". Bonne lecture.")

Énoncé 1.4 Appeler une procédure à partir d’un autre module Durée estimative : 5 minutes Créez un nouveau module nommé Module2. Créez dans ce module une procédure nommée TestAppelExtérieur qui appelle la procédure BonjourVous du module Module1. Exemple :

Énoncé 1.5 Appeler une procédure à partir d’un contrôle VBA Durée estimative : 10 minutes Créez dans la feuille Feuil1 un bouton de commande VBA. Complétez le code du bouton VBA ci-après avec un appel à la procédure BonjourVous.

Indice Private Sub CommandButton1_Click()    . . .   End Sub

Énoncé 1.6 Utiliser une procédure à partir d’une feuille de calcul Excel Durée estimative : 5 minutes Créez une procédure événementielle qui affiche le message de bienvenue grâce à un appel à la procédure BonjourVous à chaque fois que la deuxième feuille du classeur (Feuil2) est activée. Indice Private Sub Worksheet_Activate()    . . .   End Sub

La procédure doit être saisie dans la zone de code de la feuille de calcul concernée. Effectuez un clic droit sur l’onglet de la feuille Feuil2.

Énoncé 1.7 Utiliser une procédure à partir d’un classeur Excel Durée estimative : 5 minutes Créez une procédure événementielle qui, à chaque ouverture du classeur, affiche le message de bienvenue. Indice Private Sub Workbook_Open()    . . .   End Sub

La procédure doit être saisie dans la zone de code de ThisWorkbook.

Énoncé 1.8 Créer une fonction Durée estimative : 5 minutes Créez dans Module1 une fonction nommée CalculCarré qui calcule le carré d’un nombre. Indice Utilisez le passage d’arguments par valeur.

Énoncé 1.9 Utiliser une fonction Durée estimative : 5 minutes Créez dans Module1 une procédure nommée RechercheCarré qui permet de calculer le carré d’un nombre saisi par l’utilisateur et qui affiche le résultat.

Énoncé 1.10 Utiliser des paramètres nommés Durée estimative : 5 minutes Créez dans Module1 une procédure nommée TestParamètresNommés qui appelle la fonction suivante en utilisant les noms des paramètres : Function Message(strDébutPhrase As String, Optional strNom As _  String, Optional strFinPhrase As String) As String     Message = strDébutPhrase & strNom & vbCrLf & vbCrLf & strFinPhrase  End Function

N’utilisez que les premier et dernier paramètres en les nommant. Résultat :

Indice Pensez à saisir ou à copier au préalable cette fonction.

Prérequis 1. a., c. et d.

2. a. et b.     La procédure générale est appelée explicitement par le programme et réalise une tâche particulière. La procédure événementielle est appelée par un objet qui est programmé pour réagir à un événement précis suite à une action de l’utilisateur. La meilleure technique de programmation consiste à placer des instructions communes dans une procédure générale qui peut être appelée par des procédures événementielles. 3. c. 4. b. 5. a. et b. 6. a. 7. a. et c. 8. b. Sans paramètres attendus, la procédure MaProcédureB, appelée par la procédure MaProcédureA, ne doit pas comporter de parenthèses. Il est possible aussi d’utiliser l’instruction facultative Call. Les deux instructions cidessous sont équivalentes : Call MaProcédureB MaProcédureB Si vous utilisez l’instruction Call, obligatoirement entre parenthèses.

les

éventuels

arguments

sont

9. a. 10. b. En outre, les paramètres nommés peuvent être mentionnés dans un ordre quelconque. 

Corrigé 1.1 Créer et utiliser une procédure privée Dans la section Déclarations du module, saisissez : Private strNom As String

Saisissez ensuite la procédure : Private Sub DemandeNom()     strNom = InputBox("Quel est votre nom ?")   End Sub

Commentaire Par défaut les procédures sont de type Public. On peut aussi le mentionner explicitement :

Public Sub DemandeNom()

Le mot-clé Private indique que la procédure ne peut être appelée que par les procédures de son module.

Corrigé 1.2 Créer et utiliser une procédure publique Sub BonjourVous()     MsgBox ("Bonjour. Bonne lecture.")   End Sub

Précédent Corrigé 1.1 Créer et utiliser une procédure privée Suivant

Corrigé 1.3 Appeler une procédure à partir d’une autre procédure Sub BonjourVousAppelProc()     Call DemandeNom     MsgBox ("Bonjour ") & strNom & (". Bonne lecture.")   End Sub

Commentaire La syntaxe suivante est équivalente à Call DemandeNom : DemandeNom

Pour une meilleure lisibilité du code VBA, il est toutefois conseillé d’utiliser le mot-clé Call. Les procédures d’un même module peuvent s’appeler mutuellement quel que soit leur type (Public ou Private).

Corrigé 1.4 Appeler une procédure à partir d’un autre module

Sub TestAppelExtérieur()     Call BonjourVous   End Sub

Commentaire La procédure BonjourVous du module Module1 est une procédure publique. Toutes les procédures du projet peuvent donc l’appeler. Notez qu’elle contient elle-même un appel à la procédure privée DemandeNom appartenant aussi au module Module1. Ceci ne pose aucun problème de portée puisque la procédure TestAppelExtérieur n’appelle pas directement cette dernière.

Corrigé 1.5 Appeler une procédure à partir d’un contrôle VBA Private Sub CommandButton1_Click()     Call BonjourVous   End Sub

Commentaire L’appel d’une procédure à partir d’un contrôle VBA obéit aux même règles que celles vues précédemment (exercice 1.4). Le code est à placer dans le bouton VBA. Il peut être lu et modifié aussi à partir du code de la feuille de calcul Excel (clic droit sur l’onglet de la feuille Feuil1).

Corrigé 1.6 Utiliser une procédure à partir d’une feuille de calcul Excel Private Sub Worksheet_Activate()     Call BonjourVous   End Sub

Commentaire À placer dans la zone de code de la feuille Feuil2. L’appel d’une procédure à partir d’une feuille de calcul obéit aussi aux mêmes règles vues précédemment (exercice 1.4).

Corrigé 1.7 Utiliser une procédure à partir d’un classeur Excel Private Sub Workbook_Open()     Call BonjourVous   End Sub

Commentaire Les exercices 1.5, 1.6 et 1.7 font intervenir le concept d’événement, concept très important dans la programmation orientée objet. Il est l’objet de nombreux exercices dans les chapitres Formulaires et Contrôles.

Corrigé 1.8 Créer une fonction Function CalculCarré(ByVal dblNombre As Double) As Double     CalculCarré = dblNombre * dblNombre   End Function

Commentaire Lorsqu’on utilise des valeurs décimales, il est préférable de les typer en Double plutôt qu’en Single. On obtient ainsi la meilleure précision possible.

Il est impossible d’exécuter directement une procédure Function. Elle doit être appelée par une procédure Sub, ou par une autre Function.

Corrigé 1.9 Utiliser une fonction Sub RechercheCarré()     Dim dblNombre As Double     dblNombre = InputBox("Entrer un nombre.")     dblNombre = CalculCarré(dblNombre)     MsgBox dblNombre   End Sub

Commentaire Pour toute demande de saisie, le risque d’erreur est présent. Les chapitres suivants traitent le contrôle de saisie et la gestion des erreurs.

Corrigé 1.10 Utiliser des paramètres nommés Fonction Message : Function Message(strDébutPhrase As String, Optional strNom As _   String, Optional strFinPhrase As String) As String     Message = strDébutPhrase & strNom & vbCrLf & vbCrLf & strFinPhrase   End Function

Commentaire La constante Visual Basic vbCrLf permet de générer à l’affichage un saut de ligne forcé. Procédure qui appelle la fonction Message : Sub TestParamètresNommés()     Dim strMessageAffiché As String     strMessageAffiché = Message(strDébutPhrase:="Bonjour ", _     strFinPhrase:="à tous")     MsgBox strMessageAffiché   End Sub

Commentaire Appel de la fonction sans arguments nommés :

Sub TestParamètresNommés()     Dim strMessageAffiché As String     ' Les trois arguments sont fournis.     strMessageAffiché = Message("Bonjour ", "à tous", "Au revoir")     MsgBox strMessageAffiché   End Sub

Également : Sub                 End

TestParamètresNommés()   Dim strMessageAffiché As String   ' Le deuxième argument facultatif est omis.   strMessageAffiché = Message("Hello", , "A bientôt")   MsgBox strMessageAffiché   Sub

Chapitre 2 : Variables - Constantes - Types de données

Introduction Durée : 1 heure 05 Mots-clés déclaration, portée, durée de vie, type, affectation, argument, type de données VBA, type de données utilisateur, membre, conversion, variable de type objet Objectifs Maîtriser l’emploi des variables et des constantes pour l’écriture des procédures et la réalisation de programmes.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1. La déclaration des variables dans VBA :   a. est réalisée avec l’instruction Option Explicit dans la partie déclaration du module.   b. peut être étendue à l’ensemble des modules.

    2.       3.         4.       5.       6.       7.       8.       9.       10.       11.      

c. est obligatoire. d. doit être suivie obligatoirement du type de données. Un nom de variable : a. peut contenir un espace. b. doit commencer par une lettre. c. doit être unique au sein d’une même portée. Une variable est accessible uniquement par les procédures de son module quand elle est déclarée avec l’instruction : a. Dim, au sein de la procédure. b. Dim, dans la partie déclaration du module. c. Private. d. Public. Lorsqu’une variable perd sa portée : a. elle devient accessible à toutes les procédures. b elle n’a plus de valeur sauf si elle est déclarée statique. c elle perd son type. Les déclarations de variables suivantes sont correctes dans la partie déclaration du module : a. Public varTest1 b. Private dblTest2 As Double c. dblTest3 As Double Dans l’instruction suivante Dim strMot, strPhrase as String, la variable strMot est de type : a. String b. Variant c. inconnu Pour déclarer la constante publique Pi on écrit : a. Public Const Pi As Double = 3.1415926 b. Const Pi Public = 3.1415926 As Double c. Const Pi = 3.1415926 Le type de données par défaut des variables est : a. Byte b. String c. Variant La déclaration du type s’effectue avec l’instruction : a. To b. As c. VarType Une variable peut être de type : a. tableau b. objet c. feuille de calcul Excel Une variable de type objet : a. contient une référence à l’objet. b. contient l’objet lui-même. c. contient la valeur de l’objet.

12.       13.       14.       15.      

Le type de données numériques le plus précis est : a. Single b. Currency c. Double La conversion des données est possible : a. oui b. non c. uniquement pour les chaînes de caractères. La création de ses propres types de données est possible : a. oui b. non c. seulement pour les tableaux ou collections. Pour connaître le type de données d’une variable, on utilise le mot réservé : a. Is b. Cvar c. VarType

Énoncé 2.1 Déclarer et utiliser une variable Durée estimative : 20 minutes 2.1.1 : Déclarer et utiliser une variable de niveau projet Dans le module Module1, déclarez une variable publique nommée strMessagePublic de type String. Créez une procédure nommée Message qui affiche le message suivant :

Indice Pour afficher la boîte de dialogue, utilisez la fonction MsgBox. 2.1.2 : Utiliser une variable de niveau projet Dans un deuxième module nommé Module2, créez une procédure nommée AppelExtérieur qui utilise la variable strMessagePublic du Module1 pour afficher le message suivant :

Indice Pour forcer un saut de ligne : vbCrLf. Utilisez l’opérateur de concaténation & pour composer le message. 2.1.3 : Déclarer et utiliser une variable de niveau module Dans le module Module1, déclarez une variable privée nommée strMessagePrivé de type String. Créez une procédure nommée MessageInterne qui affiche le message suivant :

2.1.4 : Déclarer et utiliser une variable de niveau procédure Dans le module Module1, créez une procédure nommée MessageLocal. Celle-ci utilise une variable locale nommée strMessageLocal de type String. Résultat :

2.1.5 : Déclarer et utiliser plusieurs variables de même type Dans le module Module1, créez une procédure nommée VariablesLigne dont voici le code : Sub VariablesLigne()    Dim intv1, intv2, intv3 As Integer    MsgBox ("intv1 : " & VarType(intv1) & vbCrLf & "intv2 : " _    & VarType(intv2) & vbCrLf & "intv3 : " & VarType(intv3)) 

End Sub

Résultat :

Corrigez la déclaration de sorte que les trois variables soient effectivement de type entier.

Énoncé 2.2 Déclarer et utiliser une constante Durée estimative : 10 minutes 2.2.1 : Conversion Dans le module Module1, déclarez une constante publique nommée sglPouce de type Single. Affectez-lui la valeur 25,4. Créez une procédure nommée ConversionPouceEnmm qui convertit les pouces en millimètres. Exemple :

Indice Faites attention au séparateur décimal. 2.2.2 : Utiliser des constantes VBA pour le choix des boutons des boîtes de messages

VBA possède de nombreuses constantes internes qui simplifient la programmation. Avec elles, il est possible par exemple de choisir facilement les boutons de la boîte de dialogue standard. Affichage seulement du bouton OK : Sub ConstanteVBA()     Dim intRésultat As Integer     intRésultat = MsgBox("Bouton OK seul", vbOKOnly)   End Sub

Indice Voici un extrait de l’aide en ligne de VBA Excel :

Énoncé 2.3 Utiliser la date système Durée estimative : 10 minutes Créez la procédure ConversionDate qui effectue une conversion de la date système en type String. Procédez aux deux affichages suivants. Exemple :

Indice Utilisez la fonction VBA Date pour avoir la date système. Utilisez la fonction VBA CStr pour la conversion. Syntaxe : CStr(expression) Utilisez la fonction VBA Format pour la présentation de la date après conversion en texte. Syntaxe simplifiée : Format(expression)

Énoncé 2.4 Créer un type de données "Région" défini par l’utilisateur Durée estimative : 5 minutes Créez un type public nommé Région. Les membres de ce nouveau type sont : Nom, Superficie, ChefLieu et Population. Indice Public . . . Région    NomRégion As . . .    SuperficieRégion. . .    ChefLieuRégion . . . 

  PopulationRégion . . .  End Type

Énoncé 2.5 Utiliser le type "Région" Durée estimative : 10 minutes Complétez la procédure NouvelleRégion ci-après. Elle permet de saisir une nouvelle région et affecte chaque saisie à une cellule de la première feuille de calcul Excel. Sub NouvelleRégion()      Dim uneRégion As ...      On Error GoTo Erreur        With Worksheets("Feuil1")          uneRégion.NomRégion = InputBox("Saisir le nom")          .Range("I2").Value = ...          ... = InputBox("Saisir le chef-lieu")          .Range("I3").Value = uneRégion.ChefLieuRégion            .            .            .      End With  Erreur:  End Sub

Énoncé 2.6 Renommer une feuille de calcul Excel Durée estimative : 10 minutes Créez la procédure RenommerFeuille qui renomme la deuxième feuille de calcul du classeur par un nom saisi.

Indice Utilisez l’instruction Set pour l’affectation de la deuxième feuille de calcul à la variable objet. 

Prérequis 1. a. et b.     Il est toutefois fortement recommandé de déclarer toutes les variables explicitement et de les typer pour disposer d’un code rigoureux et du contrôle de VBE. 2. b. et c. 3. b. et c.     Préférez toutefois l’instruction Private à Dim dans la partie déclaration. 4. b. La déclaration Static concerne habituellement des variables locales (au sein d’une procédure). 5. a. et b.     Dans la partie déclaration, les variables doivent être précédées par les instructions Public, Private ou Dim. 6. b. Lorsque plusieurs variables sont présentes dans une même déclaration, le type de données ne concerne que la dernière variable. Les autres reçoivent le type par défaut, à savoir le type Variant. 7. a. 8. c. 9. b. 10. Les trois réponses sont bonnes. 11. a. 12. b. Le type de données Currency est utile pour les calculs monétaires et pour les calculs à virgule fixe dans lesquels la précision revêt une importance

particulière.

13. a. 14. a. 15. c. Par défaut, les constantes sont de type Private.

Corrigé 2.1 Déclarer et utiliser une variable 2.1.1 : Déclarer et utiliser une variable de niveau projet Déclaration de la variable publique dans la partie déclaration du module. Public strMessagePublic As String

Écriture de la procédure. Sub Message()     strMessagePublic = "Utilisation d'une variable publique"     MsgBox strMessagePublic   End Sub

2.1.2 : Utiliser une variable de niveau projet Sub AppelExtérieur()     strMessagePublic = "Utilisation d'une variable publique" & _     vbCrLf & vbCrLf & "Appel à partir d'un autre module"     MsgBox strMessagePublic   End Sub

2.1.3 : Déclarer et utiliser une variable de niveau module Déclaration de la variable privée dans la partie déclaration du module. Private strMessagePrivé As String

Écriture de la procédure. Sub MessageInterne     strMessagePrivé = "Utilisation d'une variable privée"     MsgBox strMessagePrivé   End Sub

2.1.4 : Déclarer et utiliser une variable de niveau procédure Sub MessageLocal()     Dim strMessageLocal As String     strMessageLocal = "Utilisation d'une variable locale"     MsgBox strMessageLocal   End Sub

2.1.5 : Déclarer et utiliser plusieurs variables de même type

Sub VariablesLigne()     Dim intv1 As Integer, intv2 As Integer, intv3 As Integer     MsgBox ("intv1 : " & VarType(intv1) & vbCrLf & "intv2 : " _     & VarType(intv2) & vbCrLf & "intv3 : " & VarType(intv3))   End Sub

Corrigé 2.2 Déclarer et utiliser une constante 2.2.1 : Conversion Déclaration de la constante dans la partie déclaration du module. Public Const sglPouce As Double = 25.4

Écriture de la procédure. Sub ConversionPouceEnmm()     Dim dblNBPouce As Double     Dim dblRésultatmm As Double     On Error GoTo Sortie_Sur_Erreur     dblNBPouce = InputBox("Nombre de pouces")     dblRésultatmm = dblNBPouce * dblPouce     MsgBox dblNBPouce & " pouces = " & dblRésultatmm & " mm"   Sortie_Sur_Erreur:   End Sub

2.2.2 : Utiliser des constantes VBA pour le choix des boutons des boîtes de messages Sub ConstanteVBA()     Dim intRésultat As Integer     intRésultat = MsgBox("Bouton OK seul", vbOKOnly)     intRésultat = MsgBox("Boutons Oui et Non", vbYesNo)     intRésultat = MsgBox("Boutons Recommencer et Annuler", _     vbRetryCancel)   End Sub

Corrigé 2.3 Utiliser la date système Sub ConversionDate()     Dim datDate As Date     Dim strChaîne1 As String     Dim strChaîne2 As String     Dim strChaîne3 As String     datDate = Date     MsgBox "Affichage de la date système : " & datDate     strChaîne1 = "Conversion de la date en texte" & vbCrLf     strChaîne2 = "Nous sommes le "  

  strChaîne3 = Cstr(datDate)     strChaîne3 = Format(datDate, "dd mmmm yyyy")     MsgBox strChaîne1 & strChaîne2 & strChaîne3   End Sub

L’écriture peut être plus concise. Celle qui est présentée ici permet de bien dissocier la conversion du formatage du texte.

Corrigé 2.4 Créer un type de données "Région" défini par l’utilisateur Déclaration du type dans la partie déclaration du module. Public Type Région    NomRégion As String * 30    SuperficieRégion As Long    ChefLieuRégion As String * 20    PopulationRégion As Long    End Type

Corrigé 2.5 Utiliser le type "Région" Sub NouvelleRégion()      Dim uneRégion As Région      On Error GoTo Erreur        With Worksheets("Feuil1")          uneRégion.NomRégion = InputBox("Saisir le nom")          .Range("I2").Value = uneRégion.NomRégion          uneRégion.ChefLieuRégion = InputBox("Saisir le chef-lieu")          .Range("I3").Value = uneRégion.ChefLieuRégion          uneRégion.PopulationRégion = InputBox("Saisir la population")          .Range("I4").Value = uneRégion.PopulationRégion          uneRégion.SuperficieRégion = InputBox("Saisir la superficie (en km²)")          .Range("I5").Value = uneRégion.SuperficieRégion      End With  Erreur:  End Sub

Corrigé 2.6 Renommer une feuille de calcul Excel

Sub RenommerFeuille()     On Error GoTo Sortie_Sur_Erreur     Dim wksFeuille As Worksheet     Dim strNom As String     Set wksFeuille = ActiveWorkbook.Worksheets(2)     strNom = InputBox("Saisir le nouveau nom pour la 2ème feuille")     wksFeuille.Name = strNom   Sortie_Sur_Erreur:   End Sub

Introduction Durée : 1 heure 15 Mots-clés appel d’une fonction, passage de paramètres, opérande, ordre de priorité, opération, concaténation, comparaison Objectifs Utiliser les fonctions et connaître les principaux opérateurs pour effectuer des calculs, des comparaisons, des concaténations et des opérations logiques.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1. Les signes suivants sont des opérateurs arithmétiques VBA :   a. ^   b. \   c. § 2. Résultat du calcul : (10 - 8) / 2 + 4 - 3 * 2   a. 0   b. 4   c. -1 3. La variable strRésultat suivante a pour valeur : Dim strVar1 As String  Dim intVar2 As Integer 

Dim strRésultat As String  strVar1 = "2"  intVar2 = 2  strRésultat = Var1 & Var2

  a. 0   b. 22   c. 4 4. La variable blnC suivante a pour valeur : Dim intA Dim intB Dim blnC intA = 5 blnC = A

As Integer   As Integer   As Boolean   * 2 : intB = 8   > B Xor A < 12

  a. vrai   b. faux 5. L’opérateur Is compare :   a. des références d’objets   b. des objets   c. des valeurs d’objets 6. Opérateur qui divise deux nombres en ne renvoyant que le reste :   a. Modulo   b. Mod   c. Div 7. Opérateurs logiques VBA :   a. &   b. Not   c. Xor

1. Note préliminaire Dans ce chapitre, ainsi que dans toute la suite de l’ouvrage, les fonctions intégrées de VBA seront préfixées par le nom de leur bibliothèque. Ainsi, on écrit VBA.Right plutôt que Right. La deuxième syntaxe, bien que correcte, peut, et de façon aléatoire, ne pas fonctionner sur certaines installations d’Excel. L’utilisateur prudent, amené à distribuer ses développements sur plusieurs postes de travail, s’imposera donc la discipline d’écriture du code spécifiée ici.

Énoncé 3.1 Diviser deux nombres Durée estimative : 5 minutes

Créez la procédure Division qui demande de saisir deux nombres puis divise le premier par le second. Affichez le résultat. Exemple : 60 : 50

Énoncé 3.2 Obtenir le reste d’une division entière Durée estimative : 5 minutes À partir des deux dialogues de départ de l’énoncé précédent, créez la procédure DivisionEntièreReste qui effectue la division entière de deux nombres et qui affiche également le reste. Exemple :

Indice

Utilisez la constante vbCrLf pour obtenir un saut de ligne

Énoncé 3.3 Comparer des nombres et rechercher le plus petit d’entre eux Durée estimative : 10 minutes Créez une fonction TrouvePetit qui compare deux nombres et affiche le plus petit d’entre eux. Testez ensuite cette fonction dans une procédure RéponsePetit. Exemple :

Indice Utilisez l’opérateur logique And pour traiter le cas de nombres égaux. Pour la fonction, utilisez la structure conditionnelle : If ... Else ... End If.

Énoncé 3.4 Donner le résultat d’un nombre élevé à la puissance N Durée estimative : 10 minutes

Créez une procédure Puissance qui élève un nombre à une puissance donnée et qui affiche le résultat. Exemple : 7,48

Indice Utilisez la fonction VBA.Format pour la présentation du résultat.

Énoncé 3.5 Comparer deux chaînes de caractères Durée estimative : 10 minutes Créez une fonction TestChaîne qui compare deux chaînes de caractères. Testez ensuite cette fonction dans une procédure EX5. Exemple, saisissez les mots suivants : SOLEIL et Soleil.

Note VBA fait une distinction entre minuscules et majuscules, de même pour les accents.

Énoncé 3.6 Effectuer un calcul factoriel Durée estimative : 10 minutes Créez une fonction nommée Factorielle qui calcule la factorielle d’un nombre entier. Testez ensuite cette fonction dans une procédure qui demande la saisie d’un nombre entre 1 et 20 puis affiche le résultat. Exemple :

Indice Le résultat retourné par la fonction doit être de type Double car le calcul factoriel donne rapidement de très grands nombres.

Note Ne saisissez pas de nombres entiers trop grands pour ne pas arriver en dépassement de capacité.

Énoncé 3.7 Calculer le pourcentage Durée estimative : 10 minutes Créez une fonction nommée CalculPourcentage qui exprime le pourcentage d’un nombre par rapport à un deuxième nombre. Créez ensuite une procédure AppelPourcentage qui appelle cette fonction et demande à l’utilisateur d’entrer le premier nombre (numérateur), puis le second (dénominateur). Exemple pour une classe de 30 élèves dont 18 garçons, trouvez le pourcentage de garçons.

Indice Pour la fonction, utilisez le passage d’arguments par valeur.

Énoncé 3.8 Formater un mot Durée estimative : 5 minutes

Créez une procédure FormateMot qui demande la saisie d’un nom et qui le retourne converti en majuscules et sans espaces avant et après. Exemple :

Indice Utilisez les fonctions de gestion de chaînes de caractères VBA.LTrim(), VBA.RTrim et VBA.UCase().

Énoncé 3.9 Rechercher un mot Durée estimative : 5 minutes Créez une procédure ChercheMot qui permet de vérifier la présence d’un mot dans une phrase ou dans un texte. Un message est affiché en fonction du résultat de la recherche. Exemples :

Indice

Utilisez la fonction VBA.InStr().

Énoncé 3.10 Extraire une information d’une chaîne de caractères Durée estimative : 5 minutes La cotation suivante d’un des titres du CAC 40 a été importée d’un site boursier : « 2021-0908,5052.879883,5061.600098,4935.379883,4973.520020,4973.520020,85589800 ». Elle est structurée selon sept champs : Date, Open, High, Low, Close, Adj Close, Volume. Créez la procédure ExtraireInfo permettant d’extraire la date de cette chaîne de caractères.

Indice Utilisez les fonctions VBA.Left() et VBA.CDate().

Prérequis 1. 2. 3. 4. 5. 6. 7.

a.et b. c. b. b. a. b. b. et c.

Corrigé 3.1 Diviser deux nombres Sub Division()     Dim dblNombre1 As Double     Dim dblNombre2 As Double     Dim dblRésultat As Double     On Error GoTo Sortie_Sur_Erreur     dblNombre1 = InputBox("Entrez le dividende")     dblNombre2 = InputBox("Entrez le diviseur")     dblRésultat = dblNombre1 / dblNombre2     MsgBox "Résultat : " & dblRésultat   Sortie_Sur_Erreur:   End Sub

Commentaire Pour toute demande de saisie, le risque d’erreur existe. Les chapitres Structures de contrôle et Tableaux traitent du contrôle de saisie. La gestion des erreurs est traitée au chapitre Introduction à la programmation objet.

Corrigé 3.2 Obtenir le reste d’une division entière Sub DivisionEntièreReste()     Dim dblNombre1 As Double     Dim dblNombre2 As Double     Dim dblReste As Double     Dim dblRésultat As Double     Dim strChaîne1 As String     Dim strChaîne2 As String     Dim strChaîne3 As String     On Error GoTo Sortie_Sur_Erreur     dblNombre1 = InputBox("Entrez le dividende")     dblNombre2 = InputBox("Entrez le diviseur")     dblReste = dblNombre1 Mod dblNombre2     dblRésultat = dblNombre1 \ dblNombre2     strChaîne1 = "Division de " & dblNombre1 & " par " & dblNombre2     strChaîne2 = dblNombre1 & ":" & dblNombre2 & "=" & dblRésultat     strChaîne3 = "   reste : " & dblReste     MsgBox strChaîne1 & vbCrLf & strChaîne2 & vbCrLf & strChaîne3   Sortie_Sur_Erreur:   End Sub

Corrigé 3.3 Comparer des nombres et rechercher le plus petit d’entre eux

Code de la fonction : Function TrouvePetit(ByVal dblN1 As Double, ByVal dblN2 As Double) _  As Double     If dblN1 < dblN2 Then       TrouvePetit = dblN1     Else       TrouvePetit = dblN2     End If   End Function

Code de la procédure : Sub RéponsePetit()     Dim dblNombre1 As Double     Dim dblNombre2 As Double     Dim dblNBPetit As Double     On Error GoTo Sortie_Sur_Erreur     dblNombre1 = InputBox("Entrez le 1er nombre")     dblNombre2 = InputBox("Entrez le 2ème nombre")     dblNBPetit = TrouvePetit(dblNombre1, dblNombre2)     If dblNombre1 = dblNBPetit And dblNombre2 = dblNBPetit Then       MsgBox "Nombres égaux"     ElseIf dblNombre1 = dblNBPetit Then       MsgBox dblNombre1 & " < " & dblNombre2     ElseIf dblNombre2 = dblNBPetit Then       MsgBox dblNombre2 & " < " & dblNombre1     End If   Sortie_Sur_Erreur:   End Sub

Corrigé 3.4 Donner le résultat d’un nombre élevé à la puissance N Sub Puissance()     Dim dblNombre As Double     Dim dblPuissance As Double     Dim dblRésultat As Double     Dim strChaîne As String     On Error GoTo Sortie_Sur_Erreur     dblNombre = InputBox("Entrer le nombre")     dblPuissance = InputBox("Entrer la puissance")     dblRésultat = dblNombre ˆ dblPuissance     strChaîne = dblNombre & " ˆ " & dblPuissance & " = "     MsgBox strChaîne & VBA.Format(dblRésultat, "### ### ###.00")   Sortie_Sur_Erreur:   End Sub

Corrigé 3.5 Comparer deux chaînes de caractères Code de la fonction : Function TestChaîne(strT1 As String, strT2 As String) As String     If strT1 = strT2 Then       TestChaîne = "Ces deux mots sont identiques."     Else       TestChaîne = "Ces deux mots ne sont pas identiques."     End If   End Function

Code de la procédure : Sub EX5()     Dim strMot1 As String     Dim strMot2 As String     Dim strRésultat As String     strMot1 = InputBox("Entrer le premier mot")     strMot2 = InputBox("Entrer le deuxième mot")     strRésultat = TestChaîne(strMot1, strMot2)     MsgBox strRésultat   End Sub

Corrigé 3.6 Effectuer un calcul factoriel Code de la fonction : Function Factorielle(intNombre As Integer) As Double     If intNombre > 0 Then       Factorielle = Factorielle(intNombre - 1) * intNombre     Else       Factorielle = 1     End If   End Function

Commentaire Cette fonction est dite récursive puisque la fonction Factorielle s’appelle elle-même. Elle peut être écrite de manière plus classique : Function Factorielle(intNombre As Integer) As Double     Dim intNombreEnCours As Integer     Dim dblRésultat As Double     dblRésultat = 1     For intNombreEnCours = 1 To intNombre       Factorielle = dblRésultat * intNombreEnCours       intRésultat = Factorielle     Next intNombreEnCours   End Function

Code de la procédure : Sub AppelFactorielle()     Dim intNombreEntier As Integer     Dim dblRésultat As Double     On Error GoTo Sortie_Sur_Erreur     intNombreEntier = _     InputBox("Saisir un nombre entier entre 1 et 20")     dblRésultat = Factorielle(intNombreEntier)     MsgBox intNombreEntier & "! =" & dblRésultat   Sortie_Sur_Erreur:   End Sub

Note Pour formater le nombre, utiliser la fonction VBA.Format. VBA.Format(dblRésultat, "##,##0")

Exemple : MsgBox intNombreEntier &"!="&VBA.Format(dblRésultat, "##,##0")

Corrigé 3.7 Calculer le pourcentage Code de la fonction : Function CalculPourcentage(ByVal dblN1 As Double, ByVal dblN2 As _   Double) As Double     CalculPourcentage = dblN1 / dblN2 * 100   End Function

Code de la procédure : Sub AppelPourcentage()     Dim dblNumérateur As Double     Dim dblDénominateur As Double     Dim dblPourcentage As Double     On Error GoTo Sortie_Sur_Erreur     dblNumérateur = InputBox("Numérateur")     dblDénominateur = InputBox("Dénominateur")     dblPourcentage = CalculPourcentage(dblNumérateur, dblDénominateur)    dblPourcentage = VBA.Round(dblPourcentage, 2)     MsgBox "Pourcentage :" & dblPourcentage & "%"   Sortie_Sur_Erreur:   End Sub

Corrigé 3.8 Formater un mot

Sub FormateMot()     Dim strMot As String, strMotFormate As String     strMot = InputBox("Saisir un nom :")     strMotFormate = VBA.LTrim(strMot)     strMotFormate = VBA.RTrim(strMotFormate)     strMotFormate = VBA.UCase(strMotFormate)     MsgBox "Nom saisi : '" & strMot & "'" & vbCrLf & _     "Nom converti : '" & strMotFormate & "'"   End Sub

Corrigé 3.9 Rechercher un mot Sub ChercheMot()     Dim strPhrase As String, strMot As String     Dim intPositionDebut As Integer     strPhrase = InputBox("Saisir une phrase :")     strMot = InputBox("Saisir le mot à chercher :")     intPositionDebut = VBA.InStr(strPhrase, strMot)     If intPositionDebut = 0 Then       MsgBox "Le mot " & strMot & " n'est pas présent."       Else       MsgBox "Le mot " & strMot & " est présent."     End If   End Sub

Corrigé 3.10 Extraire une information d’une chaîne de caractères Sub ExtraireInfo()    Dim strChaine As String    Dim datDate As Date    strChaine = "2021-09-08,5052.879883, 5061.600098,4935.379883,4973.520020,4973.520020,85589800"    datDate =VBA.CDate(VBA.Left(strChaine, 10))    MsgBox datDate  End Sub

Introduction Durée : 1 heure 55 Mots-clés

condition, choix, alternative, branchement conditionnel, compteur, itération, incrémenter, décrémenter, sortie, boucle, test Objectifs Maîtriser les structures de décision afin de tester des conditions puis effectuer des actions différentes selon le résultat obtenu. Maîtriser les instructions d’itération qui, associées aux instructions conditionnelles, permettent d’écrire du code Visual Basic pour la prise de décision et la répétition des actions. Vous retrouverez ces structures dans la suite du livre. Dans ce chapitre, nous nous limitons à l’emploi des boîtes de dialogue déjà rencontrées.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1 .       2 .

Les structures suivantes sont des structures de décision : a. If ... Then ... Else ... End If b. Do ... Loop c. Select Case ... Case ... End Select Résultat = IIf(7 / 2 > 3, IIf(2.8 * 3.3< 11, "X", "Y"), "Z") La variable Résultat contient la valeur : a. X b. Y c. Z Les instructions suivantes appartenant chacune à une structure de contrôle différente sont correctes : a. Case If N1 > N2 b. Case A, B, C c. Case 1 to 10 d. Case Nombre, Is > 50 Le mot-clé ElseIf :

      3 .         4 .   a. peut apparaître de suite après une clause Else.   b. est facultatif.

  5 .           6 .           7 .           8 .           9 .          

c. peut être utilisé plusieurs fois dans un bloc If. Répétition d’instructions tant qu’une condition a la valeur True : a. For Each ... Next b. For ... Next c. Do ... Loop d. While ... Wend e. With ... End With Utilisation d’un compteur pour exécuter des instructions un certain nombre de fois : a. For Each ... Next b. For ... Next c. Do ... Loop d. While ... Wend e. With ... End With Répéter un groupe d’instructions pour chaque élément d’un tableau ou d’une collection : a. For Each ... Next b. For ... Next c. Do ... Loop d. While ... Wend e. With ... End With Répéter un groupe d’instructions le nombre de fois indiqué : a. For Each ... Next b. For ... Next c. Do ... Loop d. While ... Wend e. With ... End With Exécuter une série d’instructions appliquées à un seul objet ou à un type défini par l’utilisateur : a. For Each ... Next b. For ... Next c. Do ... Loop d. While ... Wend e. With ... End With

Énoncé 4.1 Vérifier que la saisie est numérique Exercice 1 Durée estimative : 10 minutes Créez la procédure ContrôleSaisie qui propose une boîte de saisie et vérifiez qu’il s’agit d’un nombre. Si c’est le cas, effectuez l’opération qui consiste à multiplier le nombre par lui-même, sinon affichez un message. Exemple :

Indice Utilisez la fonction VBA.IsNumeric pour tester la saisie. Exercice 2 Durée estimative : 5 minutes Complétez la procédure précédente qui vérifie que l’utilisateur a cliqué sur le bouton OK. Dans le cas contraire, affichez un message. Exemple :

Énoncé 4.2 Dire au revoir Durée estimative : 5 minutes Complétez la procédure AuRevoir. Celle-ci propose une boîte de dialogue demandant si l’on souhaite ou non quitter Excel. Elle affiche le message "A bientôt" si l’utilisateur clique sur le bouton Oui, et le message "Poursuivons" sinon. Exemple :  

Sub AuRevoir()     Dim strMessage As String     Dim intStyle As Integer     Dim intChoix As Integer     strMessage = "Souhaitez-vous quitter Excel ?"     intStyle = vbYesNo + vbDefaultButton2     ...  = MsgBox(strMessage, intStyle)     If ... = vbYes Then       MsgBox "A bientôt."     ...       MsgBox "Poursuivons."  

  ...   End Sub

Indice Voici un extrait de l’aide VBA en ligne. L’aide complète se trouve à l’adresse suivante : https://docs.microsoft.com/fr-fr/office/vba/language/reference/user-interfacehelp/msgbox-function

Énoncé 4.3 Contrôler la saisie d’une consonne ou voyelle Durée estimative : 10 minutes Créez la procédure ConsonneVoyelle qui demande de saisir une voyelle ou une consonne. Affichez un message selon la saisie : "Voyelle", "Consonne" ou un message approprié en cas d’erreur. Exemple :

Indice Pour tester la lettre en majuscule, on effectuera la transformation suivante : varChoix = VBA.UCase(varChoix)

Énoncé 4.4 Afficher un message selon l’âge et le genre Durée estimative : 15 minutes Créez la procédure VotreAge qui demande le sexe et l’âge de l’utilisateur. Les messages affichés seront différents selon l’âge et selon qu’il s’agisse d’un homme

ou d’une femme. Prenez également en considération l’abandon de la saisie et la saisie de lettres pour l’âge. Pour les hommes : 0 - 17 ans : "Gamin" 18 - 30 ans : "Vous êtes jeune" 31 - 50 ans : "Vous êtes encore jeune" > 50 ans : "Vous commencez à vieillir" Pour les femmes, un seul message : "On ne demande pas son âge à une femme". Exemple :

Indices Déclaration des variables :   Dim   Dim   Dim   Dim

strHomme As String * 1   strFemme As String * 1   intChoixSexe As Integer   dblAge As Double

Pour afficher la première boîte de dialogue : intChoixSexe = MsgBox("Vous êtes un homme ?", vbYesNo, "Votre âge")

Pour afficher la deuxième boîte de dialogue : dblAge = InputBox("Quel âge avez-vous ?", , 0)

Énoncé 4.5 Dire bonjour N fois Durée estimative : 10 minutes 4.5.1 : Dire bonjour N fois avec la structure For ... Next Créez la procédure Bonjour10FoisAvecFor avec la structure For ... Next qui dit 10 fois bonjour.

4.5.2 : Dire bonjour N fois avec la structure Do ... Loop Créez la procédure Bonjour10FoisAvecDo qui réalise la même action que précédemment mais avec une structure différente. Note Avec les structures répétitives pour un énoncé donné, il existe souvent plusieurs solutions possibles. Les solutions dans la partie Corrigés ne sont donc pas uniques.

Énoncé 4.6 Compter de N en N jusqu’à M Durée estimative : 10 minutes 4.6.1 : Compter de 13 en 13 jusqu’à 100 Créez la procédure CompterDe13en13 qui compte de 13 en 13 jusqu’à 100 en utilisant la structure Do While.

4.6.2 : Paramétrer une procédure pour compter de N en N jusqu’à M À partir de la procédure précédente, créez la procédure CompterDeNenN de sorte qu’elle propose à l’utilisateur le nombre à ajouter (N) et le nombre limite (M). Exemple :

Énoncé 4.7 Rendre obligatoire une saisie et contrôler la sortie Durée estimative : 15 minutes Créez la procédure paramétrée SaisieObligatoire utilisant une structure répétitive de votre choix qui effectue des conversions de pouces en millimètres (1 pouce = 25,4 mm). L’opération se poursuit tant que l’utilisateur ne clique pas sur le bouton Non. En

outre, dès que l’utilisateur a cliqué sur le bouton Oui, il ne peut poursuivre le traitement que si un nombre est saisi. Dans le cas contraire, il "boucle" indéfiniment. Exemple :

Note Saisissez d’abord des lettres (exemple : ENI) pour tester le contrôle puis un nombre (exemple : 13).

Énoncé 4.8 Inverser l’ordre des caractères Durée estimative : 10 minutes Créez la procédure TexteALenvers qui renvoie les caractères saisis en ordre inverse.

Exemple :

Énoncé 4.9 Vérifier si un nombre est premier Durée estimative : 15 minutes Créez la fonction DétermineNombrePremier qui détermine si un nombre est premier. Testez ensuite cette fonction dans une procédure nommée NombrePremier. La procédure doit être répétitive jusqu’à ce que l’utilisateur décide de cliquer sur le bouton Annuler.

Énoncé 4.10 Compter le nombre d’occurrences d’un nombre Durée estimative : 10 minutes

Créez la procédure OccurrencesNombre qui compte le nombre de fois où un nombre entier choisi entre 1 et 100 par l’utilisateur correspond à un tirage aléatoire d’un nombre entier entre 1 et 100, tirage effectué 500 fois.

Note Utilisez des nombres aléatoires pour générer une suite de nombres. Voir dans l’aide : l’instruction Randomize et la fonction VBA.Rnd.

Prérequis 1. 2. 3. 4. 5. 6. 7. 8. 9.

a. et c. a. b., c. et d. b. et c. c. et d. b. a. b. e.

Corrigé 4.1 Vérifier que la saisie est numérique Exercice 1

Sub ContrôleSaisie()     Dim varTestSaisie As Variant     Dim dblNombre As Double     varTestSaisie = InputBox(prompt:="Entrer un nombre", _     Title:="Multiplication du nombre par lui-même")     If varTestSaisie "" Then       If VBA.IsNumeric(varTestSaisie) Then         dblNombre = varTestSaisie * varTestSaisie         MsgBox "C'est un nombre : " _         & varTestSaisie & " x " & varTestSaisie _         & " = " & dblNombre       Else         MsgBox "Vérifier votre saisie"       End If     End If   End Sub

Commentaire Au chapitre précédent, nous avons utilisé la méthode Inputbox qui permet également de contrôler la saisie. Exercice 2 Sub ContrôleSaisiebis()     . . .         MsgBox "Vérifier votre saisie"       End If     Else       MsgBox "Annulation de l'opération"     End If   End Sub

Corrigé 4.2 Dire au revoir Sub AuRevoir()     Dim strMessage As String     Dim intStyle As Integer     Dim intChoix As Integer     strMessage = "Souhaitez-vous quitter Excel ?"     intStyle = vbYesNo + vbDefaultButton2     intChoix = MsgBox(strMessage, intStyle)     If intChoix = vbYes Then       MsgBox "A bientôt."     Else       MsgBox "Poursuivons."     End If   End Sub

Corrigé 4.3 Contrôler la saisie d’une consonne ou voyelle

Sub ConsonneVoyelle()     Dim strConsonne As String * 1     Dim strVoyelle As String * 1     Dim varTestSaisie As Variant     Dim varChoix As Variant     varTestSaisie = InputBox("Saisir une consonne ou voyelle")     If Not VBA.IsNumeric(varTestSaisie) Then     varChoix = VBA.UCase(varTestSaisie)     Select Case varChoix       Case "A", "E", "I", "O", "U", "Y"         MsgBox "Voyelle"       Case "B", "C", "D", "F", "G", "H", "J", "K", "L", "M", _       "N", "P", "Q", "R", "S", "T", "V", "W", "X", "Z"         MsgBox "Consonne"       Case Else         If varChoix "" Then             MsgBox "Saisissez une consonne ou une voyelle (et une seule !)"      End If     End Select     Else       MsgBox "Vous avez saisi un nombre !"     End If   End Sub

Corrigé 4.4 Afficher un message selon l’âge et le genre Sub VotreAge()     Dim strHomme As String * 1     Dim strFemme As String * 1     Dim intChoixSexe As Integer     Dim dblAge As Double     Dim varTestSaisieAge As Variant     intChoixSexe = MsgBox("Vous êtes un homme ?", _     vbYesNo, "Votre âge")     If intChoixSexe = vbYes Then       varTestSaisieAge = InputBox("Quel âge avez-vous ?", , 0)       If varTestSaisieAge "" Then         If VBA.IsNumeric(varTestSaisieAge) Then           dblAge = varTestSaisieAge           Select Case dblAge               Case 0 To 17                   MsgBox "Gamin !"               Case 18 To 30                   MsgBox "Vous êtes jeune."               Case 31 To 50                   MsgBox "Vous êtes encore jeune."               Case Else                   MsgBox "Vous commencez à vieillir..."           End Select         Else           MsgBox "Vous n'avez pas saisi un nombre"         End If       Else       MsgBox "Vous n'avez pas répondu à la question..."       End If     Else       MsgBox "On ne demande pas son âge à une femme..."     End If   End Sub

Corrigé 4.5 Dire bonjour N fois 4.5.1 : Dire bonjour N fois avec avec la structure For ... Next Sub Bonjour10FoisAvecFor()     Dim intCompteur As Integer     Dim strMot As String     Dim strMessage As String     intCompteur = 0     strMot = "Bonjour..."     For intCompteur = 1 To 10       strMessage = strMessage & " " & strMot     Next intCompteur     intCompteur = intCompteur - 1     MsgBox strMessage & vbCrLf & vbCrLf _     & "Je vous ai dit " & _     intCompteur & " fois bonjour."   End Sub

4.5.2 : Dire bonjour N fois avec avec la structure Do ... Loop Sub Bonjour10FoisAvecDo()     Dim intNombre As Integer     Dim strMot As String     Dim strMessage As String     intNombre = 0     strMot = "Bonjour..."     Do While intNombre < 10       intNombre = intNombre + 1       strMessage = strMessage & " " & strMot     Loop     MsgBox strMessage & vbCrLf & vbCrLf _     & "Je vous ai dit " & intNombre & " fois bonjour."   End Sub

Corrigé 4.6 Compter de N en N jusqu’à M 4.6.1 : Compter de 13 en 13 jusqu’à 100 Sub CompterDe13en13()     Dim intNombreDépart As Integer     Dim intAjout As Integer     Dim intNewNombre As Integer     Dim strMessage As String     strMessage = "0"     intNombreDépart = 0     intAjout = 13     Do While intNombreDépart ", _        VBA.Format(intTab2D(intLigne, intColonne), "@@@")       Next intColonne     Next intLigne  End Sub

Commentaire Le premier argument représente les lignes, le second les colonnes. Par défaut on a : Option Base 0. La déclaration suivante est donc aussi correcte : Dim intTab2D(4, 4)

Cette déclaration implique une modification des indices. For intLigne = 0 To 4      For intColonne = 0 To 4

Cette déclaration est cependant moins élégante que celle utilisant le mot-clé To. Une autre possibilité serait de changer d’option de base (à déclarer impérativement dans la partie Déclarations du module). Auquel cas, une troisième écriture est possible : Option Base 1   Dim intTab2D(5, 5)

Le parcours au niveau des indices reste identique à la solution donnée. For intLigne = 1 To 5      For intColonne = 1 To 5

Corrigé 5.7 Déclarer et utiliser un tableau à plus de deux dimensions Exercice 1 Déclaration du tableau dans la partie Déclarations du module. Private intTab3D(1 To 10, 1 To 10, 1 To 50) As Integer

La déclaration suivante est équivalente avec l’option base par défaut. Private intTab3D(9, 9, 49) As Integer

Exercice 2 Sub EX7()     intTab3D(10, 10, 50) = 777     Debug.Print "5000ème élément :"; intTab3D(10, 10, 50)   End SubI

Corrigé 5.8 Déclarer et utiliser un tableau dynamique Déclaration du tableau dans la partie Déclarations du module. Private dblTabDynamique() As Double

Commentaire En déclarant un tableau dynamique vous pouvez redimensionner le tableau pendant l’exécution du code. Notez que les parenthèses sont vides. Code de la procédure : Sub EX8()     Dim intTaille As Integer     On Error GoTo Erreur     intTaille = Application.InputBox(prompt:="Indiquer la taille du tableau" _ 

  & "(entre 1 et 10 000)", Type:=1)       intTaille = VBA.CInt(intTaille)       ReDim dblTabDynamique(1 To intTaille)       dblTabDynamique(intTaille) = intTaille       Debug.Print "dernier élément avec taille du tableau :"; _       dblTabDynamique(intTaille)   Erreur:   End Sub

Commentaire La taille maximale d’un tableau varie en fonction de votre système d’exploitation et de la mémoire disponible. L’utilisation d’un tableau dépassant la quantité de RAM disponible ralentit le traitement. VBA peut aussi arrêter l’exécution de la procédure.

ReDim permet de modifier la taille d’un tableau dynamique aussi souvent que nécessaire. Attention, ReDim ne peut être utilisé que dans une procédure.

Corrigé 5.9 Agrandir un tableau dynamique en préservant les valeurs initiales Sub EX9()     Dim intTaille1 As Integer     Dim intTaille2 As Integer       intTaille1 = Application.InputBox _     (prompt:="Première taille du tableau", _     Type:=1, Title:=" Nombre entier positif < 32 767")     ReDim dblTabDynamique(1 To intTaille1)     dblTabDynamique(intTaille1) = intTaille1     Debug.Print "Première taille:"; dblTabDynamique(intTaille1)     intTaille2 = Application.InputBox _     (prompt:="Deuxième taille du tableau", _     Type:=1, Title:="Nombre entier positif < 32 767")     ReDim Preserve dblTabDynamique(1 To intTaille2)     dblTabDynamique(intTaille2) = intTaille2     Debug.Print "Deuxième taille:"; dblTabDynamique(intTaille2)  

End Sub

Commentaire Le mot-clé Preserve permet de conserver la valeur des éléments du tableau déjà existants. Sans lui, ces éléments seraient réinitialisés. À savoir également : 

Preserve ne permet de redimensionner un tableau qu’en modifiant la dernière dimension.



Preserve ne permet pas de modifier le nombre de dimensions.

Corrigé 5.10 Exploiter un tableau à l’aide d’une boucle Sub EX10()     Dim dblNombre As Double     Dim intCompteur As Integer     Dim dblTableMultiplication(1 To 10) As Double     dblNombre = Application. _     InputBox(prompt:="Entrez le nombre pour la table" _     & " de multiplication.", Type:=1)     If dblNombre 0 Then       dblNombre = VBA.CDbl(dblNombre)       For intCompteur = 1 To 10           dblTableMultiplication(intCompteur) = intCompteur * dblNombre           Debug.Print dblNombre, "x", VBA.Format(intCompteur, "@@"), "=", _          VBA.Format(dblTableMultiplication(intCompteur), "@@@@")       Next intCompteur     End If   End Sub

Corrigé 5.11 Afficher un tableau dans un formulaire Private Sub UserForm_Initialize()    ' Déclaration du tableau    Dim strMois(1 To 12) As String    ' Déclaration du compteur    Dim intMois As Integer    ' Mise en place de la boucle    For intMois = 1 To 12      strMois(intMois) = VBA.Format(VBA.DateSerial(1, intMois, 1), "mmmm")      ' Ajout du mois dans le ComboBox      frmMois.cboMois.AddItem strMois(intMois)      ' Ajout du mois dans la zone de liste 

    frmMois.lstMois.AddItem strMois(intMois)    Next intMois    cboMois.ListIndex = 0    lstMois.ListIndex = 0  End Sub

Introduction Durée : 1 heure 50 Mots-clés objet, constructeur, accesseur, propriété, méthode, événement, classe, collection Objectifs Ce chapitre est une première approche de la programmation objet. Il s’agit de se familiariser d’une part avec la syntaxe générale des instructions et d’autre part avec les principaux objets d’Excel : les classeurs, les feuilles de calcul et les plages de cellules. Les exercices portent sur les points suivants : accès à l’objet, interrogation et modification des propriétés, utilisation des méthodes et gestion des événements. La gestion des erreurs provoquées par l’absence d’un objet est ensuite abordée. Le chapitre se termine par la création et l’utilisation d’une classe et d’une collection d’objets.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1 .       2 .    

Méthode pour passer un classeur au premier plan lorsque plusieurs classeurs sont ouverts : a. Select b. Activate c. Open Méthode pour activer une feuille de calcul : a. b.

Select Activate

  3 .       4 .       5 .     6 .       7 .     8 .        

c. Move Sont des collections d’objets : a. Worksheets b. Worksheet c. Sheets Sont des propriétés de Worksheets : a. Count b. Visible c. Add De manière générale, la valeur d’une propriété d’un objet : a. est modifiable. b. ne peut être modifiée. Instruction pour modifier la propriété privée d’un objet : a. Public Property Get b. Public Property Let c. Public Property Set Le constructeur de la classe doit obligatoirement être défini : a. oui b. non Procédures ou fonctions par défaut d’une collection : a. b. c. d.

Add Count Item Delete

Note Vérifiez avant de commencer que les options Déclaration des variables obligatoire et Complément automatique des instructions sont bien cochées dans les options de VBE. Pour accéder à la fenêtre d’options dans l’environnement VBE : Outils - Options.

Énoncé 6.1 Accéder à un objet Durée estimative : 5 minutes Classeurs Introduction Programmation Objet.xlsm et Classeur N2.xlsx.

6.1.1 : Accéder à un classeur Ouvrez ces deux classeurs. Sélectionnez (activez) le classeur Introduction Programmation Objet.xlsm. Créez dans un module de ce classeur une procédure qui active le second classeur Classeur N2.xlsx. Note Pour les énoncés de 6.1.2 à 6.1.4, laissez ces deux classeurs ouverts lors de l’écriture et du test de vos procédures. 6.1.2 : Accéder à une feuille d’un classeur À partir du classeur Introduction Programmation Objet.xlsm, activez la feuille Feuil3 du classeur Classeur N2.xlsx.

6.1.3 : Accéder à une cellule À partir du classeur Introduction Programmation Objet.xlsm, activez la cellule B2 de la feuille Feuil1 du classeur Classeur N2.xlsx.

6.1.4 : Sélectionner une plage de cellules À partir du classeur Introduction Programmation Objet.xlsm, sélectionnez la plage de cellules de A1 à C3 de la feuille Feuil1 du classeur Classeur N2.xlsx et rendez active la cellule B3.

Énoncé 6.2 Lire les propriétés d’un objet Durée estimative : 5 minutes 6.2.1 : Connaître le nom d’une feuille de calcul

Sous Excel, renommez la première feuille du classeur Introduction Programmation Objet.xlsm avec le mot Accueil. Créez une procédure qui affiche le nouveau nom de cette feuille.

6.2.2 : Connaître le nom d’une feuille de calcul en utilisant une variable objet Recréez la procédure précédente en utilisant les déclarations de variables suivantes : Dim wksFeuille As Worksheet   Dim strNom As String

6.2.3 : Connaître le nombre de feuilles de calcul Affichez le nombre de feuilles de calcul du premier classeur.

Énoncé 6.3 Modifier les propriétés d’un objet Durée estimative : 15 minutes 6.3.1 : Renommer une feuille de classeur Par programmation et sans instruction d’activation, renommez la deuxième feuille du premier classeur avec le nom Essai.

6.3.2 : Affecter une nouvelle valeur à une cellule Sous Excel, saisissez la valeur 789 dans la cellule B2 de la feuille Accueil du premier classeur. Par programmation et sans instruction d’activation, transformez la valeur de cette cellule en texte VBA Excel.

6.3.3 : Mettre en gras une cellule Par programmation et sans instruction d’activation, mettez en gras la cellule B2 de la feuille Accueil du premier classeur.

6.3.4 : Modifier plusieurs propriétés d’un objet avec l’instruction With Modifiez les propriétés de la cellule B2 de sorte que le contenu soit en gras italique et de couleur bleue, que la police soit Comic Sans MS et que le contexte apparaisse en bleu italique. Note Vous pouvez agir simplement sur la couleur de la police avec la propriété ColorIndex. Voici les dix premières constantes pouvant être affectées à cette propriété :

Énoncé 6.4 Utiliser les méthodes d’un objet Durée estimative : 10 minutes 6.4.1 : Ajouter une nouvelle feuille au premier classeur Par programmation et sans instruction d’activation, ajoutez au premier classeur une nouvelle feuille derrière la dernière feuille. Renommez-la Tempo.

6.4.2 : Supprimer une feuille Supprimez la feuille Tempo. Excel vous demandera automatiquement une confirmation de suppression que vous n’aurez pas à gérer.

Énoncé 6.5 Gérer les collections Durée estimative : 10 minutes 6.5.1 : Connaître les noms de toutes les feuilles de calcul du classeur Affichez dans une boîte tous les noms des feuilles du classeur en utilisant la collection Worksheets. 

6.5.2 : Modifier une ligne sur deux d’une plage de cellules Écrivez la procédure qui sélectionne la plage de cellules de B2 à D20 de la feuille Accueil et qui formate une ligne sur deux de cette plage selon les caractéristiques suivantes : 

Police : Arial, taille 10 en gras et couleur bleu



Couleur de fond : jaune

Indice Pour la gestion des lignes alternées, vous pouvez faire appel à l’opérateur Mod.

Collections utiles : Range, Rows, Columns

Énoncé 6.6 Gérer les événements Durée estimative : 10 minutes 6.6.1 : Afficher le contenu d’une cellule Créez la procédure qui affiche le contenu d’une cellule par un double clic sur une cellule non vide de la feuille Accueil. Note Sélectionnez au préalable, dans l’IDE, l’objet Accueil puis le type d’objet et l’événement adéquats. 

6.6.2 : Afficher un message de bienvenue Créez la procédure qui affiche un message de bienvenue à l’ouverture du classeur. Note Sélectionnez au préalable, dans l’IDE, l’objet ThisWorkbook puis le type d’objet et l’événement adéquats.

Énoncé 6.7 Gérer les erreurs

Durée estimative : 15 minutes 6.7.1 : Gérer simplement les erreurs Refermez le classeur Classeur N2.xlsx. Relancez la procédure de l’énoncé 6.1.1. Un message d’erreur apparaît puisque le deuxième classeur a été refermé. Créez une procédure simple pour gérer ce type d’erreur.

6.7.2 : Gérer les erreurs avec un message Améliorez la procédure de l’énoncé précédent de sorte qu’un message s’affiche en fonction du résultat de l’opération.

Note Les numéros d’erreurs et leurs significations sont consultables dans l’aide en ligne. En voici un extrait :

6.7.3 : Gérer les erreurs avec poursuite du traitement Améliorez encore la procédure de l’énoncé 6.7.1 de sorte que le traitement se poursuive si aucune erreur ne s’est produite.

Énoncé 6.8 Créer une classe avec un événement de classe Durée estimative : 5 minutes Mise en place Insérez un module de classe et renommez-le FordMustang.

Complétez la classe en créant : 

les propriétés Immatriculation de type String, Puissance de type Integer et Vitesse de type Integer. Les propriétés Immatriculation et Vitesse seront en lecture/écriture et la propriété Puissance en lecture seule.



les procédures de classe Let Property et Get Property pour gérer les propriétés spécifiées ci-dessus.



la procédure Class_Initialize() déclenchée à l’instanciation de l’objet et permettant l’affichage d’un message de bienvenue.



l’événement EclatementPneu.

Note Vous pouvez utiliser la boîte de dialogue d’ajout de procédures de l’IDE.

Énoncé 6.9 Utiliser la classe créée Durée estimative : 20 minutes Note Attention ! Pour pouvoir utiliser un événement personnalisé (EclatementPneu), les procédures de tests doivent se trouver dans un module prenant en charge les événements. Un module standard ne peut donc pas convenir. On pourra par exemple choisir un module de feuille. Déclarez un objet de portée module, maMustang, de classe FordMustang.

Créez la procédure TestClasseMustang permettant de tester la classe créée. Affichez des boîtes de dialogue pour suivre la démarche de création des objets. Cette procédure comprendra une boucle Do... Loop dans laquelle sera effectuée une saisie de la variation de vitesse. Exemple :

  Après un clic sur Annuler :

Essayez d’utiliser la propriété Puissance en écriture et vérifiez que vous obtenez bien un code d’erreur.

Énoncé 6.10 Déclencher l’événement de classe Durée estimative : 5 minutes Complétez la procédure d’écriture de la propriété Vitesse (Property Let Vitesse) dans le module de classe FordMustang pour que l’événement EclatementPneu se déclenche lorsque la vitesse dépasse 200 km/h. Complétez le module de feuille en programmant la procédure événementielle correspondant à cet événement. L’affichage d’un simple message signalant l’événement sera suffisant.

Énoncé 6.11 Créer et utiliser une collection d’objets Durée estimative : 10 minutes Créez une procédure Collection_ClubMustang() qui constitue, par saisies d’immatriculations, une collection de véhicules de classe FordMustang. Cette procédure affichera ensuite le nombre de membres du club ainsi que les immatriculations saisies au moment de la création des instances. Cette procédure peut être implantée dans un module standard. Note Pour éviter d’avoir un message d’accueil à chaque nouvelle instanciation d’objet, on peut mettre en commentaire le contenu de la procédure Class_Initialize() du module de classe FordMustang.  Indice Pour le parcours de la collection, utilisez la boucle For Each ... Next.

Prérequis 1 . 2 .   3 . 4 . 5 . 6 .

b. a. Le fait de sélectionner une feuille la rend active a. et c. a. et b. a. b. et c.

Utiliser Property Set pour attribuer par référence un objet à une propriété. 7 b. . Si aucun constructeur n’est explicitement défini, le constructeur par défaut est appelé lors de l’instanciation de la classe. 8 a., b. et c. .

Corrigé 6.1 Accéder à un objet 6.1.1 : Accéder à un classeur Sub ActiveClasseurN2()     Workbooks("Classeur N2.xlsx").Activate   End Sub

ou : Sub ActiveClasseurN2Bis()     Workbooks(2).Activate   End Sub

Commentaire Le numéro d’index indique l’ordre dans lequel les classeurs ont été ouverts ou créés. Après une suppression, un numéro d’index peut alors référencer un autre classeur.

6.1.2 : Accéder à une feuille d’un classeur Sub SélectionFeuille()     Workbooks("Classeur N2.xlsx").Activate     Worksheets("Feuil3").Select   End Sub

6.1.3 : Accéder à une cellule Sub SélectionCellule()    Workbooks("Classeur N2.xlsx").Activate    Worksheets("Feuil1").Select    Range("B2").Select  End Sub

Commentaire À la place de Range("B2"), vous pouvez écrire Cells(2, 2). 6.1.4 : Sélectionner une plage de cellules Sub SélectionPlage()     Workbooks("Classeur N2.xlsx").Activate     Worksheets("Feuil1").Select     Range("A1:C3").Select     Range("B3").Activate   End Sub

Corrigé 6.2 Lire les propriétés d’un objet 6.2.1 : Connaître le nom d’une feuille de calcul Sub NomFeuille(     MsgBox "Nom de la première feuille : " _     & Workbooks("Introduction Programmation Objet.xlsm"). _     Worksheets(1).Name   End Sub

6.2.2 : Connaître le nom d’une feuille de calcul en utilisant une variable objet Sub NomFeuilleDéclaration()     Dim wksFeuille As Worksheet     Dim strNom As String     Set wksFeuille = Workbooks("Introduction Programmation Objet.xlsm") _    .Worksheets(1)     strNom = wksFeuille.Name     MsgBox "Nom de la première feuille : " & strNom   End Sub

6.2.3 : Connaître le nombre de feuilles de calcul Sub NombreFeuilles().Activate     MsgBox "Nombre de feuilles de calcul : " _  

  &  Workbooks("Introduction Programmation Objet.xlsm"). _     Worksheets.Count   End Sub

Corrigé 6.3 Modifier les propriétés d’un objet 6.3.1 : Renommer une feuille de classeur Sub RenommeFeuilleCalcul()     Dim wksFeuille As Worksheet     Set wksFeuille = Workbooks("Introduction Programmation Objet.xlsm") _    .Worksheets(2)     wksFeuille.Name = "Essai"   End Sub

6.3.2 : Affecter une nouvelle valeur à une cellule Sub ChangeValeur()     Dim wksFeuille As Worksheet     Dim rngPlage As Range     Set wksFeuille = Workbooks("Introduction Programmation Objet.xlsm") _    .Worksheets("Accueil")     Set rngPlage = wksFeuille.Range("B2")     rngPlage.Value = "VBA Excel"   End Sub

6.3.3 : Mettre en gras une cellule Sub EnGras()     Dim wksFeuille As Worksheet     Dim rngPlage As Range     Set wksFeuille = Workbooks("Introduction Programmation Objet.xlsm") _    .Worksheets("Accueil")     Set rngPlage = wksFeuille.Range("B2")     rngPlage.Font.Bold = True   End Sub

6.3.4 : Modifier plusieurs propriétés d’un objet avec l’instruction With Sub ModifProprietes()     Dim wksFeuille As Worksheet     Dim rngPlage As Range     Set wksFeuille = Workbooks("Introduction Programmation Objet.xlsm") _    .Worksheets("Accueil")     Set rngPlage = wksFeuille.Range("B2")     With rngPlage.Font           .Italic = True           .ColorIndex = 5           .Name = "Comic Sans MS"     End With   End Sub

Corrigé 6.4 Utiliser les méthodes d’un objet 6.4.1 : Ajouter une nouvelle feuille au premier classeur Sub AjoutFeuille()     Dim wkbClasseur As Workbook     Dim wksFeuille As Worksheet     Set wkbClasseur = Workbooks("Introduction Programmation Objet.xlsm")    wkbClasseur.Sheets.Add After:=Worksheets(Worksheets.Count)     Set wksFeuille = wkbClasseur.Worksheets(Worksheets.Count)     wksFeuille.Name = "Tempo"   End Sub

6.4.2 : Supprimer une feuille Sub SupprimeFeuille()     Sheets("Tempo").Delete   End Sub

Corrigé 6.5 Gérer les collections 6.5.1 : Connaître les noms de toutes les feuilles de calcul du classeur Sub NomsDesFeuilles()     Dim wkbClasseur As Workbook     Dim wksFeuille As Worksheet     Dim strNoms As String     Set wkbClasseur = Workbooks("Introduction Programmation Objet.xlsm")    For Each wksFeuille In wkbClasseur.Worksheets       strNoms = strNoms & wksFeuille.Name & vbCrLf     Next     MsgBox strNoms   End Sub

6.5.2 : Modifier une ligne sur deux d’une plage de cellules Sub ModifLigne_1sur2()     Dim rngPlage As Range     Dim intLigne As Integer, intColonne As Integer     WorkSheets("Accueil").Select     Set rngPlage = Range("B2:D20")     For intLigne = 1 To rngPlage.Rows.Count        If (intLigne Mod 2 = 0) Then             For intColonne = 1 To rngPlage.Columns.Count                 With rngPlage.Cells(intLigne, intColonne)                             .Interior.ColorIndex = 6                             With .Font                               .Name = "Arial"                               .Size = 10                               .ColorIndex = 5                               .Bold = True                             End With                 End With             Next intColonne        End If     Next intLign  

End Sub

Corrigé 6.6 Gérer les événements 6.6.1 : Afficher le contenu d’une cellule Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _  Cancel As Boolean)    If ActiveCell.Value "" Then       MsgBox ActiveCell.Value    End If   End Sub

6.6.2 : Afficher un message de bienvenue Private Sub Workbook_Open()    MsgBox "Bonjour"   End Sub

Corrigé 6.7 Gérer les erreurs 6.7.1 : Gérer simplement les erreurs Sub GèreErreurSimple()     On Error GoTo Sortie_Sur_Erreur     Workbooks("Classeur N2.xlsx").Activate   Sortie_Sur_Erreur:   End Sub

6.7.2 : Gérer les erreurs avec un message Sub GèreErreurNumMessage()     On Error GoTo Sortie_Sur_Erreur     Workbooks("Classeur N2.xlsx").Activate   Sortie_Sur_Erreur:     If Err.Number = 9 Then       MsgBox "Le classeur N2.xlsx n'est pas ouvert"     Else       MsgBox "Activation du deuxième classeur"     End If   End Sub

Commentaire Il est possible de reprendre l’exécution de la procédure à partir de la ligne qui a généré l’erreur avec l’instruction Resume.

Il est possible également d’éviter le blocage de la procédure en cas d’erreur, en ignorant totalement cette dernière. Ceci est réalisé simplement avec l’instruction On Error Resume Next. Cette instruction est à utiliser avec parcimonie et prudence car elle peut induire d’autres erreurs et, en définitive, bloquer tout un programme. Si vous l’utilisez néanmoins, pensez à restaurer ensuite le traitement d’erreur avec On Error Goto 0. 6.7.3 : Gérer les erreurs avec poursuite du traitement Sub GèreErreurNumMessage2()     On Error GoTo Sortie_Sur_Erreur     Workbooks("Classeur N2.xlsx").Activate     ' poursuite du traitement       MsgBox "Je poursuis d'autres actions car aucune erreur ne s'est produite."    Exit Sub 'Arrêt de la procédure --> si OK, le message d'erreur n'est pas affiché  Sortie_Sur_Erreur:     MsgBox "Ce classeur n'est pas ouvert"   End Sub

Corrigé 6.8 Créer une classe avec un événement de classe Option Explicit    'Déclaration de l'événement EclatementPneu  Public Event EclatementPneu()    ' Déclarations des variables de travail  Private mImmatriculation As String  Private mPuissance As Integer  Private mVitesse As Integer  Property Get Immatriculation() As String      ' Propriété en lecture      Immatriculation = mImmatriculation  End Property  Property Let Immatriculation(Immat As String)      ' Propriété en écriture      mImmatriculation = Immat  End Property  Property Get Puissance() As Integer      ' Propriété en lecture seule      Puissance = 280  End Property  Property Get Vitesse() As Integer      ' Propriété en lecture      Vitesse = mVitesse  End Property  Property Let Vitesse(speed As Integer)      ' Propriété en écriture      mVitesse = speed  End Property  Private Sub Class_Initialize()      MsgBox "Une Ford Mustang vient de sortir de l'usine !" & vbCrLf & _      "Elle ne roule pas encore !"      mVitesse = 0

Corrigé 6.9 Utiliser la classe créée Attention ! Ce code est implanté dans un module de feuille. Option Explicit  Private WithEvents maMustang As FordMustang  Sub TestClasseMustang()      Dim variationVitesse As String        ' Instanciation d'un objet de la classe FordMustang      Set maMustang = New FordMustang      With maMustang          '.Puissance = 200 ' erreur car propriété en lecture seule          .Immatriculation = InputBox("Quelle est l'immatriculation de cette merveille ?")          MsgBox Prompt:="La Ford Mustang immatriculée " & .Immatriculation & " est prête à démarrer"            ' Boucle sur les changements de vitesse          Do While True              variationVitesse = InputBox(Prompt:="Accélérez ou ralentissez par paliers (en km/h)" & vbCrLf & _              "nombre positif pour accélérer, négatif pour ralentir" & vbCrLf & _              "Cliquez sur Annuler pour terminer le voyage" & vbCrLf & _              VBA.IIf(.Vitesse = 0, "Vous êtes à l'arrêt", "Vous roulez actuellement à " & _              .Vitesse & " km/h"), _              Title:="Conduite de la Mustang " & .Immatriculation, _              Default:=20)                If variationVitesse = "" Then                  Exit Do              Else                  .Vitesse = .Vitesse + VBA.CInt(variationVitesse)                  If .Vitesse < 0 Then maMustang.Vitesse = 0                    MsgBox Prompt:="La Ford Mustang " & .Immatriculation & VBA.IIf(.Vitesse = 0, " est à l'arrêt", _                  " roule à " & .Vitesse & " km/h."), Title:="Conduite de la Mustang " & .Immatriculation              End If          Loop          .Vitesse = 0          MsgBox Prompt:="Le voyage est terminé. La Mustang " & .Immatriculation & " est maintenant à l'arrêt", _          Title:="Conduite de la Mustang " & .Immatriculation      End With  End Sub

Corrigé 6.10 Déclencher l’événement de classe

Private Sub maMustang_EclatementPneu()      ' Gestion de l'événement      MsgBox "Vous rouliez à " & maMustang.Vitesse & vbCrLf & _      "Un pneu a éclaté !"      maMustang.Vitesse = 0  End Sub    Property Let Vitesse(speed As Integer)      ' Propriété en écriture      mVitesse = speed      If mVitesse > 200 Then RaiseEvent EclatementPneu  End Property

Corrigé 6.11 Créer et utiliser une collection d’objets Sub Collection_ClubMustang()    Dim ClubMustang As New Collection    Dim uneMustang As FordMustang      ' Création d'un membre de la collection    Set uneMustang = New FordMustang    uneMustang.Immatriculation = "FV-341-KL"    ClubMustang.Add uneMustang      ' Création d'un membre de la collection    Set uneMustang = New FordMustang    uneMustang.Immatriculation = "BJ-384-EF"    ClubMustang.Add uneMustang      ' Création d'un membre de la collection    Set uneMustang = New FordMustang    uneMustang.Immatriculation = "NB-715-UF"    ClubMustang.Add uneMustang      ' Création d'un membre de la collection    Set uneMustang = New FordMustang    uneMustang.Immatriculation = "PM-123-JM"      ' Création d'un membre de la collection    Set uneMustang = New FordMustang    uneMustang.Immatriculation = "GJ-234-HN"    ClubMustang.Add uneMustang      MsgBox "Nombre de membres du Club Mustang : " & ClubMustang.Count    ' Affichage des immatriculations    For Each uneMustang In ClubMustang      MsgBox uneMustang.Immatriculation    Next  End Sub

Introduction Durée : 2 heure 05

Mots-clés activer, sélectionner, réorganiser, méthode, propriété, procédure événementielle, déclenchement, liaison dynamique, report, mise à jour Objectifs Manipuler le classeur actif par programmation. Différencier le classeur actif de la fenêtre active. Utiliser les méthodes et les propriétés des classeurs pour réaliser les principales opérations sur ceux-ci : ouvrir, fermer, imprimer... Connaître et gérer les principaux événements auxquels réagissent les classeurs. Gérer plusieurs classeurs pour consolider des données. Établir des liens dynamiques.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1 .       2 .       3 .       4 .       5

Il peut y avoir : a. plusieurs classeurs actifs en même temps. b. un et un seul classeur actif. c. au maximum 20 classeurs actifs en même temps. Le classeur actif peut être contenu dans plusieurs fenêtres dont une seule est active : a. oui b. non c. oui mais toutes les fenêtres sont actives Un classeur peut être désigné par : a. son index b. son nom c. une combinaison index - nom La méthode Open pour ouvrir un classeur appartient : a. à l’objet Workbook. b. à la collection Workbooks. c. à l’objet Application. Les procédures d’événements des classeurs sont placées dans :

.       6 .       7 .     8 .       9 .    

a. les modules. b. la partie code des feuilles. c. la partie déclaration des feuilles. L’événement VBA pour exécuter du code automatiquement à l’ouverture du classeur est : a. Workbooks_Open b. Workbook_Open c. Workbooks_Open_Auto Il est possible de forcer l’enregistrement d’un classeur avant sa fermeture en gérant l’événement : a. Workbook_BeforeClose b. Workbooks_BeforeClose La liaison des données peut être réalisée : a. par Excel. b. par programmation. c. uniquement par programmation. Les mises à jour sont possibles si les classeurs : a. sont ouverts. b. sont fermés.

Énoncé 7.1 Connaître le classeur actif Durée estimative : 5 minutes Dans Excel, créez deux nouveaux classeurs. Enregistrez l’un d’entre eux sous le nom de Classeurs. Créez la procédure NomClasseurActif qui affiche le nom du classeur actif. Exemple :

Énoncé 7.2 Afficher le classeur actif Exercice 1 Durée estimative : 5 minutes Créez la procédure NouvelleFenêtre qui affiche le classeur actif dans une deuxième fenêtre sans aucune réorganisation particulière.

Exercice 2 Durée estimative : 5 minutes À partir de la procédure précédente, créez la procédure NouvelleFenêtreMosaique qui propose un affichage avec une réorganisation mosaïque, mais uniquement pour le classeur actif.

Énoncé 7.3 Changer de classeur actif Durée estimative : 5 minutes Dans Excel, créez un nouveau classeur. Créez la procédure ClasseurOrdreOuverture qui active le troisième classeur dans l’ordre d’ouverture.

Énoncé 7.4 Enregistrer le classeur actif Durée estimative : 5 minutes Créez la procédure qui enregistre le classeur actif. Indice Utilisez la méthode Save de l’objet WorkBook.

Énoncé 7.5 Ajouter un nouveau classeur Durée estimative : 5 minutes Créez la procédure NouveauClasseur qui crée un nouveau classeur. Indice Utilisez la méthode Add. Vérifiez dans l’aide en ligne de VBA s’il s’agit d’une méthode de l’objet Workbook ou de la collection Workbooks.

Énoncé 7.6 Enregistrer un classeur en précisant le dossier de destination Durée estimative : 15 minutes Créez l’arborescence ci-dessous sur votre disque dur. Créez la procédure EnregistreCheminSimple qui enregistre un classeur déjà ouvert. Ce classeur est enregistré sous le nom TestSimple dans le dossier "0.TEMPO".

Indice Pour consulter la liste des classeurs ouverts, choisissez l’onglet Affichage, groupe Fenêtre, bouton Changer de fenêtre. Précisez le chemin d’accès : C:\0.TEMPO\TestSimple.xlsx

Énoncé 7.7 Obtenir le chemin complet où se trouve le classeur Durée estimative : 5 minutes Dans un module du classeur principal Classeurs, créez la procédure qui donne le chemin complet de ce classeur. Exemple :

Indice Utilisez la propriété suivante : ThisWorkbook.Path

Énoncé 7.8 Fermer tous les classeurs en proposant de les enregistrer Durée estimative : 5 minutes

Créez une procédure FermerEnregistrerTout qui ferme en une seule fois tous les classeurs et qui propose d’effectuer les enregistrements nécessaires.

Énoncé 7.9 Accueillir l’utilisateur par un message de bienvenue à l’ouverture du classeur Durée estimative : 5 minutes Créez une procédure événementielle qui, à l’ouverture du classeur, affiche un message de bienvenue. 

Indice Pour afficher l’heure, utilisez la fonction VBA Time.

Énoncé 7.10 Choisir la feuille de calcul à l’ouverture du classeur

Durée estimative : 5 minutes Modifiez la procédure précédente de sorte qu’après le message de bienvenue, la feuille de calcul Feuil3 du classeur soit sélectionnée.

Indice Pour sélectionner la troisième feuille : WorkSheets("Feuil3").Select

ou : WorkSheets(3).Select

Énoncé 7.11 Mettre à jour tous les calculs avant la fermeture du classeur Durée estimative : 5 minutes Excel permet de lancer la mise à jour des calculs à la demande. Pour cet exercice, choisissez l’option manuelle en sélectionnant l’onglet Fichier - Options - Formules puis :

Créez une procédure événementielle qui force la mise à jour des calculs avant la fermeture du classeur. Exemple :

Indice Pour forcer le recalcul avant l’enregistrement : Application.CalculateBeforeSave = True

Énoncé 7.12 Mettre à jour tous les calculs avant l’impression Durée estimative : 5 minutes Dans le même ordre d’idée que l’énoncé précédent, créez la procédure événementielle qui force au calcul avant impression. Exemple :

Indice Pour forcer le recalcul avant l’impression : Application.Calculate

Énoncé 7.13 Consolider des données à partir de plusieurs classeurs Durée estimative : 15 minutes Fermez tous les classeurs. Créez trois nouveaux classeurs nommés Classeur N1.xlsx, Classeur N2.xlsx et Classeur principal.xlsm. Bâtissez les tableaux suivants sans données numériques pour Classeur principal.xlsm. Créez dans un module du classeur Classeur principal la procédure ReportDonnées qui reporte les totaux du deuxième et troisième classeur dans le premier. Classeur principal

Classeur N1

Classeur N2

Contraintes Les trois classeurs sont créés dans le même dossier. Les classeurs sont ouverts. Indice Pour référencer une feuille : Worksheets("NomDeLaFeuille") Pour référencer une plage de cellules : Range("Adresse").Value Exemple : Range ("C8")

Énoncé 7.14 Mettre à jour des données consolidées à l’ouverture du classeur Durée estimative : 10 minutes Créez la procédure événementielle qui effectue la mise à jour des calculs dès l’ouverture du classeur de consolidation.

Indice Cette procédure peut appeler la procédure ReportDonnées créée précédemment. Pour obtenir le chemin du classeur actif : strChemin = ThisWorkbook.Path

Énoncé 7.15 Mettre à jour des données à partir de plusieurs classeurs de manière permanente Durée estimative : 5 minutes Créez la procédure LienPermanent qui établit une mise à jour permanente des calculs. Indice Utilisez la propriété Formula de l’objet Range.

Énoncé 7.16 Créer une page de garde permettant d’accéder à plusieurs classeurs Durée estimative : 10 minutes Dans Excel, ajoutez un nouveau classeur nommé Menu et bâtissez le menu ci-après dans la première feuille. Pour le premier élément, ajoutez directement avec Excel un lien hypertexte. Pour les deux autres éléments, créez une procédure événementielle sans lien hypertexte mais qui exécute la même action que celui-ci.

Indice Pour obtenir l’adresse de la cellule sélectionnée : strAdresse = ActiveCell.Address

Énoncé 7.17 Rechercher les références externes à d’autres classeurs Durée estimative : 10 minutes Dans le classeur Classeurs, créez la procédure RéférencesExternes qui donne la liste des classeurs Excel liés au classeur principal. Auparavant, créez ces références, par exemple en incluant dans une formule des informations présentes dans les classeurs N1 et N2. Indice Utilisez la collection LinkSources de l’objet ThisWorkBook.

Prérequis 1. 2. 3. 4. 5. 6. 7. 8. 9.

b. a. a. et b. b. b. b. a. a. et b. a.

Corrigé 7.1 Connaître le classeur actif

Sub NomClasseurActif()     MsgBox "Le classeur actif est : " & _     ActiveWorkbook.Name   End Sub

Corrigé 7.2 Afficher le classeur actif Exercice 1 Sub NouvelleFenêtre()     ActiveWorkbook.NewWindow   End Sub

Exercice 2 Sub NouvelleFenêtreMosaique()     ActiveWorkbook.NewWindow     Windows.Arrange ActiveWorkbook:=True   End Sub

Important Pour retrouver l’option par défaut de réaffichage en mosaïque pour tous les classeurs ouverts, modifiez la valeur du paramètre ActiveWorkbook et relancez la procédure. Windows.Arrange.ActiveWorkbook:=False

Corrigé 7.3 Changer de classeur actif Sub ClasseurOrdreOuverture()   On Error GoTo Sortie_Sur_Erreur     Workbooks(3).Activate   Sortie_Sur_Erreur :   End Sub

Corrigé 7.4 Enregistrer le classeur actif Sub EnregistreClasseurActif()     ActiveWorkbook.Save   End Sub

Corrigé 7.5 Ajouter un nouveau classeur Sub NouveauClasseur()     Workbooks.Add   End Sub

Corrigé 7.6 Enregistrer un classeur en précisant le dossier de destination Sub EnregistreCheminSimple()     Dim strNomClasseur As String     On Error GoTo Sortie_Sur_Erreur     strNomClasseur = _     InputBox("Entrez le nom du classeur à enregistrer")     Workbooks(strNomClasseur).SaveAs "C:\0.TEMPO\TestSimple.xlsx"     Exit Sub   Sortie_Sur_Erreur:     MsgBox "Problème d'enregistrement"   End Sub

Corrigé 7.7 Obtenir le chemin complet où se trouve le classeur Sub CheminComplet()     Dim strChemin As String     strChemin = ThisWorkbook.Path     MsgBox "Chemin du classeur " & ActiveWorkbook.Name & " : " _     & strChemin   End Sub

Corrigé 7.8 Fermer tous les classeurs en proposant de les enregistrer Sub FermerEnregistrerTout()     Workbooks.Close   End Sub

Corrigé 7.9 Accueillir l’utilisateur par un message de bienvenue à l’ouverture du classeur Sub Workbook_Open()     MsgBox ("Bonjour. Il est " & Time)   End Sub

Commentaire Placez cette procédure dans la zone de code de ThisWorkbook.

Corrigé 7.10 Choisir la feuille de calcul à l’ouverture du classeur Sub Workbook_Open()     MsgBox ("Bonjour. Il est " & Time)     WorkSheets("Feuil3").Select   End Sub

Commentaire Une technique, désormais obsolète mais néanmoins toujours utilisable, permet de gérer l’événement d’ouverture par une simple procédure qu’il faut alors nommer Auto_Open. Sub Auto_Open()    MsgBox ("Bonjour. Il est " & Time)    WorkSheets("Feuil3").Select   End Sub

Corrigé 7.11 Mettre à jour tous les calculs avant la fermeture du classeur Private Sub Workbook_BeforeClose(Cancel As Boolean)     MsgBox "Recalcul si sauvegarde avant de fermer le document"     Application.CalculateBeforeSave = True   End Sub

Corrigé 7.12 Mettre à jour tous les calculs avant l’impression Private Sub Workbook_BeforePrint(Cancel As Boolean)     MsgBox "Recalcul avant impression du document"     Application.Calculate   End Sub

Commentaire La procédure peut être testée aussi bien avec l’impression directe qu’en mode aperçu.

Corrigé 7.13 Consolider des données à partir de plusieurs classeurs Sub ReportDonnées()     Dim wksFeuillePrincipal As Worksheet     Dim wksFeuilleN1 As Worksheet     Dim wksFeuilleN2 As Worksheet     Dim strChemin As String     strChemin = ThisWorkbook.Path     Set wksFeuillePrincipal = _     Workbooks("Classeur principal.xlsm").Worksheets("Feuil1")     Set wksFeuilleN1 = _     Workbooks("Classeur N1.xlsx").Worksheets("Feuil1")     Set wksFeuilleN2 = _     Workbooks("Classeur N2.xlsx").Worksheets("Feuil1")     wksFeuillePrincipal.Range("C5").Value = _     wksFeuilleN1.Range("C8").Value     wksFeuillePrincipal.Range("C6").Value = _     wksFeuilleN2.Range("C8").Value   End Sub

Commentaire Écrivez cette procédure dans un module du classeur Classeur Principal.

Corrigé 7.14 Mettre à jour des données consolidées à l’ouverture du classeur Sub Workbook_Open()     Dim strChemin As String     strChemin = ThisWorkbook.Path     Workbooks.Open Filename:=strChemin & "\Classeur N1.xlsx"     Workbooks.Open Filename:=strChemin & "\Classeur N2.xlsx"     ThisWorkBook.Activate     WorkSheets("Feuil1").Activate   Range("B2").Select    MsgBox "Mise à jour à l'ouverture du dossier de consolidation"     Call ReportDonnées   End Sub

Commentaire À mettre dans la partie code de l’objet ThisWorkbook du projet VBA appartenant au classeur Classeur principal.

Corrigé 7.15 Mettre à jour des données à partir de plusieurs classeurs de manière permanente Sub LienPermanent()     Workbooks("Classeur Principal.xlsm").Activate     Range("G5").Formula = "='[Classeur N1.xlsx]Feuil1'!$C$8"     Range("G6").Formula = "='[Classeur N2.xlsx]Feuil1'!$C$8"   End Sub

Note Le tableau Total des ventes a été dupliqué en F4 (plage occupée : F4:G7).

Corrigé 7.16 Créer une page de garde permettant d’accéder à plusieurs classeurs Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _  ByVal Target As Range)     Dim strAdresse As String  

  strAdresse = Selection.Address     Select Case strAdresse       Case "$B$4"          Range("B2").Select          Workbooks("Classeur N1.xlsx").WorkSheets("Feuil1").Activate       Case "$B$5"          Range("B2").Select          Workbooks("Classeur N2.xlsx").WorkSheets("Feuil1").Activate     End Select   End Sub

Commentaire À mettre dans la partie code de l’objet ThisWorkbook du classeur Menu.

Corrigé 7.17 Rechercher les références externes à d’autres classeurs Sub RéférencesExternes()      Dim TableauRéférences() As Variant      Dim intRéf As Integer      TableauRéférences = ThisWorkbook.LinkSources(1)      If Not VBA.IsEmpty(TableauRéférences) Then          For intRéf = 1 To Ubound(TableauRéférences)          MsgBox "Lien " & intRéf & " : " & TableauRéférences(intRéf)          Next intRéf      Else          MsgBox ThisWorkbook.Name & " ne possède pas de références externes à d'autres classeurs"      End If  End Sub

Commentaire Avec les réponses obtenues, il est facile de retrouver toutes les cellules où les références externes sont utilisées.

Introduction Durée : 1 heure 40 Mots-clés activer, sélectionner, propriété, méthode, procédure événementielle, déclenchement Objectifs

Savoir manipuler la feuille active par programmation. Différencier feuille active et classeur actif. Connaître et gérer les principaux événements auxquels réagissent les feuilles de calcul. Se familiariser avec les principales propriétés et les méthodes des feuilles de calcul.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent avoir plusieurs réponses) : 1.       2.       3.       4.       5.       6.       7.

Il peut y avoir : a. plusieurs feuilles actives en même temps. b. une et une seule feuille active. c. au maximum 20 feuilles actives en même temps. Pour connaître le nom de la feuille active : a. Sheet.Name b. ActiveSheet.Name c. ActiveSheet.Caption La feuille active peut être contenue dans plusieurs fenêtres dont une seule est active : a. oui. b. non. c. oui mais toutes les fenêtres sont actives. Méthode pour activer une feuille de calcul : a. Activate b. Open c. First Les procédures d’événements des feuilles de calcul sont placées dans : a. les modules b. la partie code des feuilles de calcul. c. dans la partie code de l’objet ThisWorkbook. L’événement feuille de calcul qui réagit dès qu’une cellule quelconque est modifiée est : a. Worksheet_SelectionChange b. Worksheet_Change c. Worksheet_Deactivate Il est possible avec une procédure d’événements de feuille de calcul d’écrire du code pour connaître :

      8.

a. l’adresse de la cellule qui a été modifiée. b. l’adresse de la cellule active. c. l’adresse de la future cellule active. L’événement feuille de calcul Worksheet_Deactivate se produit lorsque l’on désactive :   a. un classeur.   b. une feuille de calcul.   c. une feuille graphique. 9. Pour créer une nouvelle feuille de calcul, le type d’objet le plus approprié est :   a. Worksheets   b. Sheets   c. Worksheet 10 Pour supprimer une feuille de calcul, on utilise : .   a. Delete   b. Erase   c. Del 11 Une feuille est dupliquée avec la méthode : .   a. Move   b. Copy   c. Add 12 Pour connaître le nombre de feuilles de calcul d’un classeur, il faut : .   a. utiliser une boucle avec la propriété Count.   b. écrire la propriété Count après l’objet Worksheets.   c. écrire la propriété Count après l’objet Worksheet.

Énoncé 8.1 Connaître le nom de la feuille active Durée estimative : 5 minutes Dans Excel, créez un classeur nommé Feuilles de calcul. Créez ensuite la procédure NomFeuilleActive qui donne le nom de la feuille de calcul active. Exemple :

Énoncé 8.2 Renommer la feuille active Durée estimative : 5 minutes Dans Excel, renommez les deux premières feuilles respectivement Note et Moyenne. Créez ensuite la procédure Renommer qui renomme la troisième feuille de calcul selon un nom saisi par l’utilisateur. Exemple :

Énoncé 8.3 Prévenir lors du changement de feuille active en rappelant le nom de l’ancienne feuille active Durée estimative : 5 minutes Créez deux procédures événementielles qui affichent respectivement le nom de l’ancienne feuille active et celui de la nouvelle feuille active. Exemple :

Indice Placez les procédures dans la zone de code de l’objet ThisWorkbook. Utilisez les événements SheetDeactivate et SheetActivate.

Énoncé 8.4 Effectuer un aperçu de la feuille active Durée estimative : 5 minutes Créez une procédure Aperçu qui présente un aperçu avant impression de la feuille active.

Indice

Ne pas oublier de saisir une donnée quelconque dans la feuille avant de tester la procédure.

Énoncé 8.5 Déclencher une action dès qu’une feuille est activée Durée estimative : 5 minutes Fermez le classeur Feuilles de calcul. Ajoutez un nouveau classeur sous Excel et enregistrez-le sous Suite Feuilles de calcul.xlsm. Créez une procédure événementielle qui affiche le nom de la première feuille dès que l’on clique sur son onglet.

Indice Écrire la procédure dans la zone de code de la première feuille.

Énoncé 8.6 Déclencher une action dès qu’une cellule de la feuille active est activée Durée estimative : 5 minutes Créez une procédure événementielle concernant la première feuille qui réagit dès que l’on clique sur une cellule. Exemple :

Indice Pour la présentation de la cellule :  With ActiveCell.Font      .Bold = True      .Size = 14      .ColorIndex = 5    End With    With Selection      .Interior.ColorIndex = 36      .HorizontalAlignment = xlCenter      .VerticalAlignment = xlBottom    End With

Énoncé 8.7 Déclencher une action dès qu’une cellule de la feuille active est modifiée Durée estimative : 10 minutes Créez une procédure événementielle concernant la deuxième feuille qui réagit seulement si l’on modifie l’une de ses cellules. Cette procédure indique également l’adresse de la cellule qui a été modifiée et l’adresse de la cellule active. Exemple :

Énoncé 8.8 Déclencher une action dès que l’on double clique sur une cellule de la feuille active Durée estimative : 5 minutes Créez une procédure événementielle concernant la troisième feuille qui réagit seulement si l’on double clique sur l’une de ses cellules. Résultat :

Énoncé 8.9 Ajouter une nouvelle feuille de calcul Durée estimative : 5 minutes Fermez le classeur Suite Feuilles de calcul. Ajoutez un nouveau classeur sous Excel et enregistrez-le sous le nom Fin Feuilles de calcul. Créez la procédure Ajout qui ajoute à ce classeur une nouvelle feuille de calcul nommée Nouvelle ; celle-ci est placée en dernière position.

Énoncé 8.10 Supprimer une feuille de calcul Durée estimative : 5 minutes Créez une procédure qui supprime la nouvelle feuille de calcul.

Énoncé 8.11 Copier une feuille de calcul Durée estimative : 5 minutes Créez une procédure qui copie la première feuille après la dernière feuille.

Énoncé 8.12 Connaître le nombre de feuilles du classeur Durée estimative : 5 minutes Créez la procédure NombreFeuilles qui donne le nombre de feuilles de calcul d’un classeur.

Énoncé 8.13 Connaître les noms de toutes les feuilles de calcul Durée estimative : 5 minutes Créez la procédure NomsFeuilles qui affiche le nom de chaque feuille du classeur. Exemple :

, etc.

Indice Utilisez une structure répétitive : For Each . . . Next

Énoncé 8.14 Afficher les noms de toutes les feuilles de calcul sous forme de liste Durée estimative : 10 minutes

Un classeur peut parfois contenir de très nombreuses feuilles de calcul. Créez la procédure ListeNomsFeuilles qui : 

renomme la première feuille en "Accueil".



affiche dans cette feuille les noms de toutes les feuilles de calcul sous forme de liste pour faciliter la lecture.

Exemple :

Indice Utilisez l’index pour désigner la première feuille : Worksheets(1)

Énoncé 8.15 Rechercher une feuille de calcul Durée estimative : 10 minutes Créez la procédure ChercherFeuille qui recherche une feuille dont on spécifie le nom. Si la feuille est trouvée, elle devient la feuille active. Exemple :

Énoncé 8.16 Protéger toutes les feuilles de calcul Durée estimative : 5 minutes Créez la procédure Protection qui protège toutes les feuilles de calcul d’un classeur.

Énoncé 8.17 Déprotéger toutes les feuilles de calcul Durée estimative : 5 minutes Créez la procédure RetirerProtection correspondante.

Prérequis

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.

b. b. a. a. b. b. a. et b. b. c. a. b. b.

Corrigé 8.1 Connaître le nom de la feuille active Sub NomFeuilleActive()     Dim strNom As String     strNom = ActiveSheet.Name     MsgBox "Nom de la feuille active : " & strNom   End Sub

Corrigé 8.2 Renommer la feuille active Sub Renommer()    Dim strNom As String    strNom = InputBox("Nouveau nom pour la feuille active")    ActiveSheet.Name = strNom  End Sub

Corrigé 8.3 Prévenir lors du changement de feuille active en rappelant le nom de l’ancienne feuille active ' Première procédure événementielle   Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)  

    MsgBox "Ancienne feuille active : " & Sh.Name   End Sub   ------------------------------------------------------------   ' Deuxième procédure événementielle   Private Sub Workbook_SheetActivate(ByVal Sh As Object)       MsgBox "Nouvelle feuille active : " & Sh.Name   End Sub

Corrigé 8.4 Effectuer un aperçu de la feuille active Sub Aperçu()     ActiveSheet.PrintPreview   End Sub

Corrigé 8.5 Déclencher une action dès qu’une feuille est activée Private Sub Worksheet_Activate()     MsgBox "Je suis la feuille " & ActiveSheet.Name _     & ". Vous allez travailler avec moi."   End Sub

Corrigé 8.6 Déclencher une action dès qu’une cellule de la feuille active est activée Private Sub Worksheet_SelectionChange(ByVal Target As Range)     ActiveCell.Value = "ici"     With ActiveCell.Font       .Bold = True       .Size = 14       .ColorIndex = 5     End With     With Selection       .Interior.ColorIndex = 36       .HorizontalAlignment = xlCenter       .VerticalAlignment = xlBottom     End With   End Sub

Corrigé 8.7 Déclencher une action dès qu’une cellule de la feuille active est modifiée Private Sub Worksheet_Change(ByVal Target As Range)     Dim strAdresseModifiée As String     Dim strAdresseNouvelle As String     strAdresseModifiée = Target.Address     MsgBox "Je suis la cellule que vous venez de modifier : " _     & strAdresseModifiée     strAdresseNouvelle = Selection.Address     MsgBox "Je suis la cellule où vous êtes actuellement : " _     & strAdresseNouvelle   End Sub

Corrigé 8.8 Déclencher une action dès que l’on double clique sur une cellule de la feuille active Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _  Cancel As Boolean)     MsgBox "Vous passez en mode édition."   End Sub

Corrigé 8.9 Ajouter une nouvelle feuille de calcul Sub Ajout()     Worksheets.Add after:=Sheets((Sheets.Count))     ActiveSheet.Name = "Nouvelle"   End Sub

Corrigé 8.10 Supprimer une feuille de calcul Sub Suppression()     Worksheets("Nouvelle").Delete   End Sub

Corrigé 8.11 Copier une feuille de calcul Sub Copier()     Worksheets(1).Copy after:=Worksheets(Sheets.Count)   End Sub

Corrigé 8.12 Connaître le nombre de feuilles du classeur Sub NombreFeuilles()     Dim intNombre As Integer     intNombre = Sheets.Count     MsgBox "Il y a " & intNombre & " feuilles dans le classeur."   End Sub

Corrigé 8.13 Connaître les noms de toutes les feuilles de calcul Sub NomsFeuilles()     Dim wksFeuille As Worksheet     For Each wksFeuille In Worksheets       MsgBox wksFeuille.Name     Next wksFeuille   End Sub

Corrigé 8.14 Afficher les noms de toutes les feuilles de calcul sous forme de liste Sub ListeNomsFeuilles()     Dim wksFeuille As Worksheet     Dim i As Integer     On Error GoTo Sortie_Sur_Erreur     Worksheets(1).Name = "Accueil"     i = 4     For Each wksFeuille In Worksheets       Worksheets(1).Cells(i, 2).Value = wksFeuille.Name       i = i + 1     Next wksFeuille     Exit Sub   Sortie_Sur_Erreur:  

  MsgBox "Vérifiez si la feuille Accueil est présente en 1ère position"  End Sub

Corrigé 8.15 Rechercher une feuille de calcul Sub ChercherFeuille()     Dim wksFeuilleCherchée As Worksheet     Dim strNomFeuille As String     strNomFeuille = _     InputBox("Entrez le nom de la feuille à chercher")     For Each wksFeuilleCherchée In Worksheets       If VBA.UCase(wksFeuilleCherchée.Name) = VBA.UCase(strNomFeuille) Then        wksFeuilleCherchée.Activate         Exit Sub      End If     Next wksFeuilleCherchée   End Sub

Corrigé 8.16 Protéger toutes les feuilles de calcul Sub Protection()     Dim wksFeuille As Worksheet     For Each wksFeuille In Worksheets       wksFeuille.Protect     Next wksFeuille   End Sub

Corrigé 8.17 Déprotéger toutes les feuilles de calcul Sub RetirerProtection()       Dim wksFeuille As Worksheet     For Each wksFeuille In Worksheets       wksFeuille.Unprotect     Next wksFeuille   End Sub

Introduction Durée : 3 heure 35 Mots-clés plage, cellule, activer, désactiver, sélectionner, propriété, méthode, adressage direct, adressage indirect, zone, liste, formule, référence absolue, référence relative, liaison, lien dynamique Objectifs Connaître les principales propriétés et méthodes permettant de gérer les plages de cellules. Modifier la valeur ou l’aspect d’une cellule ou d’une plage de cellules avec ou sans sélection préalable.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1.         2.     3.         4.      

Les expressions suivantes renvoient la cellule active : a. ActiveCell b. Application.ActiveCell c. ActiveWindow.ActiveCell d. ActiveCells Pour une sélection comportant plusieurs cellules : a. toutes les cellules sont actives. b. une seule cellule est active. Pour sélectionner la cellule B6 : a. Range("B6") b. Range(B6) c. Cells(6,2) d. Cell(6,2) Pour sélectionner toutes les cellules d’une feuille : a. Range.Select b. Cells.Select c. Columns.Select

  5.       6.       7.       8.

d. Rows.Select L’instruction Range("A1").Offset(4, 2).Select sélectionne : a. la cellule A4. b. active la cellule C5. c. active la cellule C2. Les instructions suivantes sont équivalentes : a. Range("C1").Value b. Cells(1,3).Value c. Range("A1").Select L’instruction fausse est : a. Range("A1").Formula = "=SUM(A1:A10)" b. Range("A1").FormulaLocal = "=SOMME(A1:A10)" c. Range("A1").Formula = SUM(A1:A10) L’instruction Range("E1").FormulaR1C1 = "=R1C1" donne pour formule en E1 :   a. = A1   b. = $A$1   c. = $A1   d. = A$1 9. Il est possible de modifier la police d’une plage de cellules :   a. obligatoirement après sa sélection.   b. obligatoirement après activation d’une des cellules de cette plage.   c. sans sélection ou activation. 10 Méthode(s) de l’objet Range : .   a. AddComment   b. Paste   c. Add 11 Propriété(s) de l’objet Range : .   a. Columns   b. Sheets   c. Interior 12 La méthode Clear : .   a. efface seulement les formules.   b. efface les formules et la mise en forme des cellules.   c. efface seulement la mise en forme des cellules. 13 Dans l’instruction Set vTableau = Range("A10:C15"), la variable vTableau . est de type :

     

a. variant b. objet c. tableau

Énoncé 9.1 Activer une cellule Durée estimative : 5 minutes Créez un classeur avec Excel. Créez la procédure Activer qui active la cellule K2 de la première feuille et y inscrit le texte "Cellule K2".

Énoncé 9.2 Connaître l’adresse de la cellule active Durée estimative : 5 minutes Créez la procédure AdresseCelluleActive qui affiche l’adresse de la cellule active. Exemple :

Énoncé 9.3 Récupérer la valeur d’une cellule

Durée estimative : 5 minutes Classeur CellulesEtPlages.xlsm

Ouvrez le classeur CellulesEtPlages.xlsm. Créez la procédure RécupValeur qui donne la valeur contenue dans une cellule donnée. Exemple pour la cellule K4 qui contient le texte "Les Editions ENI" :

Énoncé 9.4 Récupérer la formule d’une cellule Durée estimative : 10 minutes Créez la procédure RécupFormule qui donne la formule contenue dans une cellule donnée. Exemple pour la cellule K9 qui donne le cosinus d’un angle :

Énoncé 9.5 Modifier le contenu d’une cellule

Durée estimative : 5 minutes En référence à la procédure précédente, créez la procédure ModificationValeur qui modifie la valeur de l’angle dans la cellule K7. Nouvelle valeur : 45.

Énoncé 9.6 Modifier le format d’une cellule Durée estimative : 5 minutes Créez la procédure FormatCellule qui applique à la cellule K4 un fond bleu, une couleur jaune pour les caractères et la police Comic Sans MS avec une taille 14. Résultat :

Indice Fond bleu : Interior.ColorIndex = 5 Caractères en jaune : Font.ColorIndex = 36 Nom de la police : Font.Name = "Comic Sans MS" Taille de la police : Font.Size = 14

Énoncé 9.7 Sélectionner la colonne où se trouve la cellule active Durée estimative : 5 minutes

Créez la procédure Colonne qui renvoie la colonne où se trouve la cellule active puis sélectionnez cette colonne. Exemple :

Indice Pour obtenir la lettre de la colonne à la place du numéro : VBA.Chr(ActiveCell.Column + 64)

Énoncé 9.8 Sélectionner toutes les cellules d’une feuille de calcul Durée estimative : 5 minutes Créez la procédure Tout qui sélectionne toutes les cellules d’une feuille de calcul.

Énoncé 9.9 Sélectionner toutes les cellules d’une plage définie Exercice 1 Durée estimative : 5 minutes Créez la procédure Plage qui sélectionne toutes les cellules de la plage de cellules B2 à E6 dans la feuille Feuil2.

Exercice 2 Durée estimative : 10 minutes Idem que l’exercice 1 mais en utilisant une variable objet nommée. Créez pour cela la procédure Plage2. Commentaire Les objets, notamment les collections, entraînent une occupation mémoire importante. L’utilisation d’une variable objet permet de contourner ce problème.

Énoncé 9.10 Sélectionner tout un tableau à partir d’une de ses cellules Durée estimative : 5 minutes La cellule C4 du tableau étant la cellule active, créez la procédure TableauSélection qui sélectionne ce tableau dans sa totalité. Exemple :

Hypothèse La cellule C4 du tableau n’est pas vide.

Énoncé 9.11 Sélectionner un tableau ou une liste nommée à partir de son nom Durée estimative : 5 minutes Créez la procédure TableauSélectionNom qui nomme le tableau de l’énoncé précédent "MonTableau". Sélectionnez ce tableau à partir de son nom. Indice Pour atteindre un tableau ou une liste à partir de leur nom : Application.Goto Reference:="Nom_Choisi"

Énoncé 9.12 Indiquer l’adresse de la cellule sous la cellule active puis la sélectionner Durée estimative : 10 minutes

Créez la procédure Bas qui indique l’adresse de la cellule sous la cellule active puis la sélectionne. Exemple :

Indice Utilisez la propriété Offset.

Énoncé 9.13 Trouver la dernière cellule contenant une valeur d’une colonne Durée estimative : 15 minutes Créez la procédure DernièreCellule qui demande à l’utilisateur de saisir une lettre de l’alphabet. La procédure doit ensuite trouver puis sélectionner la dernière cellule qui contient une valeur dans la colonne repérée par cette lettre. Exemple :

Énoncé 9.14 Attribuer la formule d’une cellule à une autre cellule Durée estimative : 5 minutes Dans la feuille Feuil1, saisissez les nombres ci-après. Utilisez dans Excel la fonction SOMME pour obtenir le total dans la cellule K16. Créez ensuite la procédure FormuleAutreCellule qui écrit en cellule K19 la même formule qu’en K16.

Énoncé 9.15 Attribuer une formule à une cellule Durée estimative : 5 minutes À partir de la procédure de l’énoncé précédent, créez la procédure FormuleDansCode qui intègre la formule du total dans le code VBA sans faire référence à la cellule K16.

Commentaire Le résultat est identique à l’exercice précédent.

Énoncé 9.16 Attribuer la référence absolue d’une cellule à une autre cellule Durée estimative : 5 minutes Créez la procédure ReférenceAbsolue qui reporte l’adresse absolue de la cellule K16 dans la cellule K20. Résultat : =$K$16

Énoncé 9.17 Attribuer une valeur à une plage de cellules Durée estimative : 5 minutes Créez la procédure AttribuerValeurPlage qui remplit la plage de cellules de M2 à N4 avec le mot "AZERTY".

Énoncé 9.18 Ajouter un commentaire à une cellule Durée estimative : 5 minutes Créez la procédure Commentaire qui ajoute le commentaire ci-après à la cellule K29.

Énoncé 9.19 Remplir un tableau avec des nombres Durée estimative : 10 minutes Créez la procédure RemplirNombres qui remplit le tableau ci-après avec des nombres de 1 à 12.

Indice Utilisez une boucle For Each ... Next.

Énoncé 9.20 Afficher le nombre de colonnes, de lignes et de cellules d’une plage

Durée estimative : 10 minutes Créez la procédure NombreCoLiCellules qui affiche pour le tableau précédent le nombre de colonnes, de lignes et de cellules. Exemple :

Énoncé 9.21 Rechercher une valeur Exercice 1 Durée estimative : 15 minutes Créez la procédure RechercheDansPlage qui recherche, en utilisant une structure répétitive, un nombre saisi par l’utilisateur dans le tableau précédent. Mettez le nombre en gras. Exemple :

Exercice 2 Durée estimative : 10 minutes

Créez la procédure RechercheDansPlageFind qui cherche un nombre du tableau de l’énoncé 9.19 saisi par l’utilisateur en utilisant cette fois-ci la méthode Find. Mettez également le nombre en gras.

Énoncé 9.22 Créer une série de données Durée estimative : 10 minutes Créez la procédure SériesChronologique qui établit une série chronologique journalière dans la plage de P2 à P11 à compter du 1er novembre 2021.

Indice Utilisez la méthode DataSeries.

Énoncé 9.23 Trier une plage de données Durée estimative : 5 minutes Créez la procédure TrierTableauNombres qui trie le tableau de l’énoncé 9.19 dans l’ordre décroissant. 

Indice Utilisez la méthode Sort.

Énoncé 9.24 Copier une plage de données avec liaison aux données sources Durée estimative : 5 minutes Créez la procédure CopieDynamique qui copie le tableau de l’énoncé 9.19 dans la troisième feuille du classeur en conservant le lien dynamique (la liaison) avec les données sources.

Énoncé 9.25 Effacer les valeurs d’une plage de données avec demande de confirmation Durée estimative : 10 minutes Créez la procédure EffacerValeur qui efface uniquement les valeurs du tableau de l’énoncé 9.19 en affichant auparavant une boîte de confirmation.

Indice Pour afficher la boîte de demande de confirmation :   intBouton = vbYesNo + vbDefaultButton2     intRéponse = _     MsgBox("Supprimer les valeurs du tableau ?", intBouton)

Énoncé 9.26 Ajouter des graphiques sparkline à des plages de cellules Durée estimative : 10 minutes Créez la procédure AjouteSparklines qui crée des graphiques de type sparkline (courbes). On suppose qu’avant de lancer la procédure, la cellule active appartient à un tableau de données, que ce tableau contient une ligne et une colonne de titre, que les graphiques seront basés sur les données en lignes et qu’ils seront implantés à droite en fin de ligne, de façon à obtenir le résultat tel que le suivant, en prenant pour exemple le tableau de l’exercice 9.10. On peut, comme indiqué sur l’exemple, agrémenter le graphique par une couleur et une épaisseur différentes du standard. Indice Utilisez l’enregistreur de macros pour créer la trame de la macro AjouteSparklines.

Énoncé 9.27 Lister les zones d’une plage à sélections multiples Durée estimative : 10 minutes Créez la procédure ListeZones qui affiche successivement les adresses de toutes les zones d’une plage de cellules à sélections multiples. Indice Utilisez la collection Areas.

Prérequis 1. a., b. et c. 2. b. 3. a. et c. 4. b., c. et d. 5. b. Par rapport à la cellule A1, Offset permet de sélectionner la cellule se trouvant quatre lignes plus bas et deux colonnes à droite. 6. a. et b. 7. c. 8. b. 9. c.

10 . 11 . 12 . 13 .

a. et b. a. et c. b. b.

Corrigé 9.1 Activer une cellule Sub Activer()     Worksheets("Feuil1").Activate     Range("K2").Activate     ActiveCell.Value = "Cellule K2"   End Sub

Corrigé 9.2 Connaître l’adresse de la cellule active Sub AdresseCelluleActive()     Dim strAdresse As String     strAdresse = ActiveCell.Address     MsgBox "Adresse de la cellule active : " & strAdresse   End Sub

Corrigé 9.3 Récupérer la valeur d’une cellule Sub RécupValeur()     Dim strFormule As String     Dim strAdresse     strAdresse = Worksheets(1).Range("K4").Address     strFormule = Worksheets(1).Range("K4").Value     MsgBox "La cellule " & strAdresse & " contient : " & strFormule   End Sub

Corrigé 9.4 Récupérer la formule d’une cellule Sub RécupFormule()     Dim strFormule As String     Dim strAdresse As String     strAdresse = Worksheets(1).Range("K9").Address     strFormule = Worksheets(1).Range("K9").Formula     MsgBox "La cellule " & strAdresse & " contient la formule " _     & strFormule   End Sub

Corrigé 9.5 Modifier le contenu d’une cellule Sub ModificationValeur()     Dim strAdresse As String     Worksheets(1).Range("K7").Value = 45   End Sub

Corrigé 9.6 Modifier le format d’une cellule Sub FormatCellule()     With Worksheets(1).Range("K4")       .Interior.ColorIndex = 5       With .Font         .ColorIndex = 36         .Name = "Comic Sans MS"         .Size = 14       End With     End With   End Sub

Corrigé 9.7 Sélectionner la colonne où se trouve la cellule active Sub Colonne()     Dim intColonne As Integer     Dim strLettre As String     intColonne = ActiveCell.Column     strLettre = VBA.Chr(ActiveCell.Column + 64)  

  MsgBox "La cellule active se trouve dans la colonne " _     & strLettre     Columns(intColonne).Select   End Sub

Corrigé 9.8 Sélectionner toutes les cellules d’une feuille de calcul Sub Tout()     Cells.Select   End Sub

Commentaire La cellule active par défaut est la cellule A1.

Corrigé 9.9 Sélectionner toutes les cellules d’une plage définie Exercice 1 Sub Plage()     Worksheets(2).Activate     Range("B2:E6").Select   End Sub

Exercice 2 Sub Plage2()     Dim rngZone As Range     Worksheets(2).Activate     Set rngZone = Range("B2:E6")     rngZone.Select   End Sub

Corrigé 9.10 Sélectionner tout un tableau à partir d’une de ses cellules Sub TableauSélection()  

  Selection.CurrentRegion.Select   End Sub

Commentaire La propriété CurrentRegion renvoie un objet Range qui représente la zone en cours. Celle-ci est une plage limitée par toute combinaison de lignes et de colonnes vides. Attention, cela signifie que la cellule active du tableau ne doit pas être entourée de cellules vides. Exemple :

Avec la cellule active E6, la procédure devient inopérante.

Corrigé 9.11 Sélectionner un tableau ou une liste nommée à partir de son nom Sub TableauSélectionNom()     Dim rngZone As Range     Dim strNom As String     Worksheets(2).Activate     Set rngZone = Range("B2:E6")     rngZone.Name = "MonTableau"     Application.Goto Reference:="MonTableau"   End Sub

Corrigé 9.12 Indiquer l’adresse de la cellule sous la cellule active puis la sélectionner Sub Bas()     MsgBox "Adresse de la cellule active : " & _     ActiveCell.Address & vbCrLf & _     "Adresse de la cellule sous la cellule active." _     & ActiveCell.Offset(1, 0).Address     ActiveCell.Offset(1, 0).Select  

  MsgBox "Sélection de cette dernière"   End Sub

Corrigé 9.13 Trouver la dernière cellule contenant une valeur d’une colonne Sub DernièreCellule()     Dim strLettreColonne As String     Dim intNuméroColonne As Integer     Dim strMessage As String     On Error GoTo Sortie_Sur_Erreur     strLettreColonne = _     VBA.UCase(InputBox("Entrer la lettre de la colonne (de A à Z)"))     intNuméroColonne = Asc(strLettreColonne) - 64     Cells(1048576, intNuméroColonne).End(xlUp).Select     strMessage = "La colonne " & strLettreColonne & " correspond" _     & " à la colonne n° " & intNuméroColonne     strMessage = strMessage & vbCrLf _     & "Adresse de la dernière cellule non vide : " _     & Selection.Address     strMessage = strMessage & vbCrLf _     & "Valeur contenue : " & Selection.Value     MsgBox strMessage   Sortie_Sur_Erreur:   End Sub

Corrigé 9.14 Attribuer la formule d’une cellule à une autre cellule Sub FormuleAutreCellule()     Worksheets(1).Range("K19").Value = Range("K16").Formula   End Sub

Autre écriture possible : Range("K19").Formula = Range("K16").Formula

Corrigé 9.15 Attribuer une formule à une cellule Sub FormuleDansCode()     Worksheets(1).Range("K19").Formula = "=SUM(K12:K15)"   End Sub

Commentaire Pour écrire la fonction Excel en français dans le code VBA, utilisez la propriété FormulaLocal.  Range("K19").FormulaLocal = "=SOMME(K12:K15)"

Corrigé 9.16 Attribuer la référence absolue d’une cellule à une autre cellule Sub ReférenceAbsolue()     Worksheets(1).Range("K20").FormulaR1C1 = "=R16C11"   End Sub

Corrigé 9.17 Attribuer une valeur à une plage de cellules Sub AttribuerValeurPlage()     Worksheets(1).Range("M2:N4").Value = "AZERTY"   End Sub

Autre écriture possible : Range(Cells(2, 13), Cells(4, 14)).Value = "AZERTY"

Corrigé 9.18 Ajouter un commentaire à une cellule Sub Commentaire()     Dim strCommentaire As String     Worksheets(1).Range("K29").ClearComments     strCommentaire = "Voir la procédure DernièreCellule"     Worksheets(1).Range("K29").AddComment strCommentaire   End Sub

Commentaire La procédure échoue s’il existe déjà un commentaire. Il faut d’abord l’effacer.

Corrigé 9.19 Remplir un tableau avec des nombres Sub RemplirNombres()     Dim rngTableau As Range     Dim rngCellule As Range     Dim intCompteur As Integer     Worksheets(1).Activate     Set rngTableau = Range("M6:N11")     For Each rngCellule In rngTableau.Cells       intCompteur = intCompteur + 1       rngCellule.Value = intCompteur     Next rngCellule   End Sub

Corrigé 9.20 Afficher le nombre de colonnes, de lignes et de cellules d’une plage Sub NombreCoLiCellules()     Dim rngTableau As Range     Dim intNBColonne As Integer     Dim intNBLigne As Integer     Dim intNBCellule As Integer     Worksheets(1).Activate     Set rngTableau = Range("M6:N11")     With rngTableau       intNBColonne = .Columns.Count       intNBLigne = .Rows.Count       intNBCellule = .Cells.Count     End With     MsgBox "Nombre de colonnes : " & intNBColonne _     & vbCrLf & "Nombre de lignes : " & intNBLigne _     & vbCrLf & "Nombre de cellules : " & intNBCellule   End Sub

Corrigé 9.21 Rechercher une valeur Exercice 1 Sub RechercheDansPlage()     Dim rngTableau As Range     Dim rngCellule As Range     Dim intNombre As Integer     Dim blnTrouvé As Boolean     Worksheets(1).Activate  

  Set rngTableau = Range("M6:N11")     intNombre = InputBox("Saisir le nombre à chercher")     For Each rngCellule In rngTableau.Cells       If rngCellule.Value = intNombre Then         rngCellule.Font.Bold = True         blnTrouvé = True       End If     Next rngCellule     If Not blnTrouvé Then       MsgBox ("Le nombre " & intNombre & " n'est pas dans le tableau.")    End If   End Sub

Commentaire La recherche avec une structure répétitive est très lente pour de grandes plages de données. Exercice 2 Sub RechercheDansPlageFind()     Dim rngTableau As Range     Dim intNombre As Integer     Worksheets(1).Activate     Set rngTableau = Range("M6:N11")     intNombre = InputBox("Saisir le nombre à chercher")     rngTableau.Find(what:=intNombre, LookIn:=xlValues, _     lookat:=xlWhole).Select     ActiveCell.Font.Bold = True   End Sub

Commentaire La méthode Find est très rapide.

Corrigé 9.22 Créer une série de données Sub SériesChronologique()     Dim rngPlageDate As Range     Set rngPlageDate = Worksheets(1).Range("P2:P11")     Worksheets(1).Range("P2").Formula = "1-NOV-2018"     rngPlageDate.DataSeries Type:=xlChronological, Date:=xlDay   End Sub

Corrigé 9.23 Trier une plage de données Sub TrierTableauNombres()     Worksheets(1).Range("M6:N11").Sort _     Key1:=Worksheets(1).Range("M6"), _  

  Order1:=xlDescending, Orientation:=xlSortColumns   End Sub

Corrigé 9.24 Copier une plage de données avec liaison aux données sources Sub CopieDynamique()     Worksheets(1).Range("M6:N11").Copy     Worksheets(3).Select     Range("A1").Select     ActiveSheet.Paste Link:=True   End Sub

Corrigé 9.25 Effacer les valeurs d’une plage de données avec demande de confirmation Sub AjouteSparklines()     Dim rngTableau As Range     Dim rngDonnées As Range     Dim rngGraphiques As Range     Dim intNbLignes As Integer     Dim intNbColonnes As Integer     Set rngTableau = Selection.CurrentRegion       With rngTableau         '=====================================================         ' Calcul du nombre de lignes et de colonnes         ' du tableau de base         '=====================================================         intNbLignes = .Rows.Count         intNbColonnes = .Columns.Count           '=====================================================         ' rngDonnées est le tableau des données i.e le tableau         ' de base sans sa 1ère ligne et sa 1ère colonne         '=====================================================         Set rngDonnées = ActiveSheet.Range(.Cells(2, 2), .Cells(intNbLignes, intNbColonnes))           '=====================================================         ' rngGraphiques est le champ d'implantation        ' des graphiques sparkline         '=====================================================         Set rngGraphiques = ActiveSheet.Range(.Cells(2, intNbColonnes + 1),  .Cells(intNbLignes, intNbColonnes + 1))     End With       With rngGraphiques.SparklineGroups         .Add Type:=xlSparkLine, SourceData:=rngDonnées.Address         .Item(1).SeriesColor.Color = 255         .Item(1).LineWeight = 2.25     End With  End Sub

Corrigé 9.26 Ajouter des graphiques sparkline à des plages de cellules Sub AjouteSparklines()      Dim rngTableau As Range      Dim rngDonnées As Range      Dim rngGraphiques As Range      Dim intNbLignes As Integer      Dim intNbColonnes As Integer      Set rngTableau = Selection.CurrentRegion        With rngTableau          '=====================================================          ' Calcul du nombre de lignes et de colonnes          ' du tableau de base          '=====================================================          intNbLignes = .Rows.Count          intNbColonnes = .Columns.Count            '=====================================================          ' rngDonnées est le tableau des données i.e le tableau          ' de base sans sa 1ère ligne et sa 1ère colonne          '=====================================================          Set rngDonnées = ActiveSheet.Range(.Cells(2, 2), .Cells(intNbLignes,  intNbColonnes))            '=====================================================          ' rngGraphiques est le champ d'implantation           ' des graphiques sparkline          '=====================================================          Set rngGraphiques = ActiveSheet.Range(.Cells(2, intNbColonnes + 1),  .Cells(intNbLignes, intNbColonnes + 1))      End With        With rngGraphiques.SparklineGroups          .Add Type:=xlSparkLine, SourceData:=rngDonnées.Address          .Item(1).SeriesColor.Color = 255          .Item(1).LineWeight = 2.25      End With  End Sub

Corrigé 9.27 Lister les zones d’une plage à sélections multiples Sub ListeZones()      Dim uneZone As Range      For Each uneZone In Selection.Areas          MsgBox uneZone.Address      Next uneZone        MsgBox "Fin de la liste des zones de la sélection"  End Sub

Introduction Durée : 1 heure 55 Mots-clés propriété, méthode, événement, feuille graphique, graphique incorporé, module de classe Objectifs Maîtriser les opérations de base et se familiariser avec les événements portant sur les graphiques. 

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1 .     2 .       3 .       4 .

Dans la collection ChartObjects, le numéro d’index indique : a. la position du graphique dans le classeur. b. l’ordre de création du graphique incorporé. Pour ajouter un graphique : a. Charts.Add b. Add.Charts c. Charts.New La méthode pour renvoyer une série ou plusieurs séries dans un graphique : a. CollectionSeries b. SeriesCollection c. Series Avec la méthode ChartWizard, l’argument pour indiquer le type de graphique est :

      5 .       6 .       7 .     8 .     9 .        

a. Gallery b. Format c. ChartType L’objet suivant dispose d’une méthode d’exportation de graphique : a. ChartObjects b. Chart c. ChartObject Méthode pour préciser qu’un nouveau graphique de type Chart est un graphique incorporé : a. Location b. Where c. ChartObject Avant de pouvoir utiliser des événements avec un objet Chart qui représente un graphique incorporé, il faut : a. créer un module de classe. b. créer une nouvelle feuille graphique. Les événements de feuilles graphiques sont activés : a. par défaut. b. impliquent la création d’un module de classe. Sont des événements de graphiques : a. b. c. d.

Select MouseMove Calculate SelectionChange

Énoncé 10.1 Créer un graphique dans une feuille dédiée Durée estimative : 10 minutes Dans Excel, ouvrez un nouveau classeur puis implantez le tableau ci-après dans la feuille Feuil1. Créez ensuite la procédure CréerGraphiqueFeuille qui crée le graphique suivant dans une feuille graphique :

Indice Pour obtenir des histogrammes : ChartType = xlColumnClustered

Énoncé 10.2 Renommer une feuille contenant un graphique Durée estimative : 10 minutes Créez la procédure RenommerFeuilleGraphique qui renomme la feuille graphique Graphique1 en Graphe Ventes.

Énoncé 10.3 Déplacer une feuille contenant un graphique Durée estimative : 5 minutes Créez la procédure DéplacerFeuilleGraphique qui déplace la feuille graphique à la dernière position du classeur quel que soit son nombre de feuilles (de calcul ou graphiques).

Énoncé 10.4 Créer un graphique incorporé dans une feuille de calcul Durée estimative : 15 minutes Créez la procédure GraphiqueIncorporé qui, à partir du tableau des ventes, crée un graphique incorporé à la feuille de calcul Feuil1.

Énoncé 10.5 Étendre la source de données d’un graphique incorporé Durée estimative : 10 minutes Dans Excel, complétez le tableau avec les données du mois d’avril. Créez ensuite la procédure EtendreSérieGrapheIncorporé qui ajoute cette nouvelle série au graphique incorporé dans la feuille Feuil1.

Énoncé 10.6 Étendre la source de données d’un graphique implanté dans une feuille graphique Durée estimative : 10 minutes À partir du tableau complété, créez ensuite la procédure EtendreSérieGrapheFeuille qui ajoute les données du mois de mars au graphique dans la feuille Graphe Ventes.

Énoncé 10.7 Modifier la présentation d’un graphique Durée estimative : 10 minutes Créez la procédure ModifPrésentation qui modifie le graphique contenu dans la feuille Graphe Ventes selon le modèle ci-après.

Indice Vous pouvez utiliser ChartWizard, méthode simple pour modifier les propriétés d’un graphique.  Histogrammes 3D avec ChartWizard : Gallery:=xl3DBar

Énoncé 10.8 Exporter un graphique dans un fichier image Durée estimative : 10 minutes Créez la procédure ExporterGraphique qui exporte au format GIF le graphique incorporé dans la feuille de calcul Feuil1 et qui nomme celui-ci VENTES.

Indice

Utilisez la propriété Path pour obtenir le chemin du dossier où se trouve le dossier Excel.

Énoncé 10.9 Déclencher une action quand le graphique est sélectionné Durée estimative : 5 minutes Créez la procédure événementielle qui affiche le message suivant dès que la feuille graphique Graphe Ventes est activée.

Énoncé 10.10 Déclencher une action suite à un clic sur le graphique Durée estimative : 10 minutes Créez la procédure événementielle qui affiche les coordonnées du pointeur de la souris lorsqu’on clique sur une zone quelconque du graphique.

Indice Créez la procédure dans la zone de code de la feuille graphique Graphe Ventes.

Énoncé 10.11 Construire un testeur de type de graphique Durée estimative : 20 minutes Classeur Graphiques.xlsm

On utilise le graphique situé sur la feuille Graphe Ventes. Programmez la procédure événementielle qui, par clics successifs sur le graphique, passe en revue les types de graphiques possibles. On utilisera pour cela la table des types située sur la feuille Tables du classeur et portant le nom Table_types_graphiques. On affichera dans le titre du graphique le descriptif du type, pris dans la 3ème colonne de la table des types.

Prérequis 1. 2. 3. 4. 5. 6. 7. 8. 9.

b. a. b. a. b. a. a. a. a., b. et c.

Corrigé 10.1 Créer un graphique dans une feuille dédiée Sub CréerGraphiqueFeuille()     Dim Graphique As Chart     Dim rngPlage As Range     Set rngPlage = Worksheets("Feuil1").Range("A2:C5")     Set Graphique = ThisWorkbook.Charts.Add     With Graphique       .SetSourceData Source:=rngPlage, PlotBy:=xlRows       .ChartType = xlColumnClustered       .HasTitle = True       .ChartTitle.Characters.Text = "VENTES REALISEES PAR MOIS"     End With   End Sub

Corrigé 10.2 Renommer une feuille contenant un graphique Sub RenommerFeuilleGraphique()     Charts("Graphique1").Name = "Graphe Ventes"   End Sub

Corrigé 10.3 Déplacer une feuille contenant un graphique Sub DéplacerFeuilleGraphique()     Charts("Graphe Ventes").Move After:=Sheets(Sheets.Count)   End Sub

Corrigé 10.4 Créer un graphique incorporé dans une feuille de calcul Sub GraphiqueIncorporé()     Dim Graphique As Chart     Dim rngPlage As Range     Set rngPlage = Worksheets("Feuil1").Range("A2:C5")     Set Graphique = ThisWorkbook.Charts.Add     With Graphique       .SetSourceData Source:=rngPlage, PlotBy:=xlRows      . ChartType = xlColumnClustered       .HasTitle = True       .ChartTitle.Characters.Text = "VENTES REALISEES PAR MOIS"       .Location Where:=xlLocationAsObject, Name:="Feuil1"     End With   End Sub

Commentaire Pour une position particulière du graphique, utilisez : Add(Left, Top, Width, Height)

Corrigé 10.5 Étendre la source de données d’un graphique incorporé Sub EtendreSérieGrapheIncorporé()     Worksheets("Feuil1").ChartObjects("Graphique 1").Select     ActiveChart.SetSourceData Source:=Range("A2:E5")   End Sub

Corrigé 10.6 Étendre la source de données d’un graphique implanté dans une feuille graphique Sub EtendreSérieGrapheFeuille()     Sheets("Graphe Ventes").SetSourceData _     Source:=Sheets("Feuil1").Range("A2:E5")   End Sub

Corrigé 10.7 Modifier la présentation d’un graphique Sub ModifPrésentation()     Charts("Graphe Ventes").ChartWizard _     Gallery:=xl3DBar, _     Title:="VENTES REALISEES PAR REGION", PlotBy:=xlColumns   End Sub

Corrigé 10.8 Exporter un graphique dans un fichier image Sub ExporterGraphique()     Dim strCheminExport As String     On Error GoTo Sortie_Sur_Erreur     strCheminExport = ThisWorkbook.Path & "\"     Worksheets("Feuil1").ChartObjects(1) _     .Chart.Export Filename:=strCheminExport & "VENTES.gif"       MsgBox "Exportation réalisée"     Exit Sub   Sortie_Sur_Erreur:     MsgBox "Le graphique n'existe pas"   End Sub

Corrigé 10.9 Déclencher une action quand le graphique est sélectionné Private Sub Chart_Activate()     MsgBox "Résultat trimestriel des agences"   End Sub

Corrigé 10.10 Déclencher une action suite à un clic sur le graphique Private Sub Chart_MouseDown(ByVal Button As Long, _   ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)     If Button = xlPrimaryButton Then       MsgBox "Coordonnées : " & "X = " & X & " Y = " & Y     End If   End Sub

Corrigé 10.11 Construire un testeur de type de graphique Option Explicit  Private Const strTitre As String = "VENTES REALISEES PAR MOIS"  Private Sub Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)      Dim intNbTypes As Integer      Static intNbClics As Integer      On Error Resume Next        intNbTypes = Worksheets("Tables").Range("Table_types_graphiques").Rows.Count      intNbClics = intNbClics + 1      If intNbClics > intNbTypes Then          intNbClics = 1  Else          ActiveChart.ChartType = Worksheets("Tables").Range("Table_types_ graphiques").Cells(intNbClics, 2).Value          ActiveChart.ChartTitle.Text = strTitre & " (" & _           Worksheets("Tables").Range("Table_types_graphiques"). Cells(intNbClics, 3).Value & ")"      End If  End Sub

Introduction Durée : 1 heure 05 Mots-clés formule VBA, formule Excel, formule locale, argument, séparateur

Objectifs Utiliser des fonctions VBA dans les feuilles de calcul et utiliser les fonctions Excel dans le code VBA.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1 .     2 .     3 .    

La plupart des fonctions de calcul Excel peuvent être utilisées avec VBA : a. directement. b. par le biais de l’objet WorksheetFunction. Propriété qui permet d’utiliser les fonctions Excel en français dans le code VBA : a. Formula b. FormulaLocal Certaines fonctions de calcul Excel en français :

a. ont leur équivalent dans Visual Basic. b. ont le même nom qu’une fonction VBA mais produisent des résultats différents. 4 Séparateur d’arguments des fonctions VBA : .   a. le point-virgule.   b. le trait d’union.   c. la virgule. 5 Dans le code VBA, il est possible d’utiliser les fonctions Excel : .   a. uniquement en anglais.   b. uniquement en français.   c. en anglais ou en français.

Énoncé 11.1 Calculer une commission dans une feuille de calcul Excel avec une fonction VBA Exercice 1 Durée estimative : 5 minutes Construisez dans Excel le tableau ci-dessous, sans aucune saisie dans la cellule B3. Créez ensuite la fonction VBA Commission. Utilisez cette fonction dans le tableau Excel.

Exercice 2 Durée estimative : 5 minutes Construisez dans Excel le tableau ci-dessous, sans aucune saisie dans la cellule B8. Créez ensuite la fonction VBA CommissionAvecFixe. Utilisez cette fonction dans le tableau Excel.

Énoncé 11.2 Calculer le prix HT à partir du taux de TVA et du prix TTC avec une fonction VBA Durée estimative : 5 minutes Construisez dans Excel le tableau ci-dessous, sans aucune saisie dans la cellule B12. Créez ensuite la fonction VBA PrixHT. Utilisez cette fonction dans le tableau Excel.

Énoncé 11.3 Calculer la valeur acquise d’un placement avec une fonction VBA Durée estimative : 15 minutes Construisez dans Excel le tableau ci-dessous, sans aucune saisie dans la cellule E4. Créez ensuite la fonction VBA Placement. Utilisez cette fonction dans la cellule E4.

Énoncé 11.4 Rechercher le minimum dans une plage de cellules avec une fonction Excel dans du code VBA Durée estimative : 10 minutes Créez une procédure ProcMinimum qui recherche la valeur minimum dans un tableau de nombres et affiche le résultat. Exemple :

Énoncé 11.5 Générer un nombre aléatoire à partir de code VBA utilisant une formule Excel Exercice 1 Durée estimative : 5 minutes Créez une procédure nommée NombreAléatoireFr qui génère un nombre aléatoire compris entre 0 et 100, à partir de code VBA, utilisant une formule Excel avec les fonctions en français. Elle affecte ensuite à la cellule H10 ce nombre aléatoire. Exemple :

Indice Saisissez en G10 le mot ALEA. Utilisez dans la cellule H10 la fonction Excel en français : ALEA. Exercice 2 Durée estimative : 10 minutes Créez une procédure nommée NombreAléatoireAng qui génère un nombre aléatoire compris entre 0 et 100, à partir de code VBA, utilisant une formule Excel avec les fonctions en anglais. Elle affecte ensuite à la cellule H11 ce nombre aléatoire. Exemple :

Indice Saisissez en G11 le mot RAND. Utilisez dans la cellule H11 la fonction Excel en anglais : RAND.

Énoncé 11.6 Calculer l’annuité constante avec Excel à partir d’une fonction VBA Durée estimative : 5 minutes

Créez une fonction VBA nommée AnnuitéConstante qui calcule l’annuité constante d’un emprunt en appliquant la formule située dans la cellule D16 ci-dessous. Utilisez cette fonction dans le tableau Excel ci-après.

Énoncé 11.7 Supprimer tous les espaces inutiles et redondants d’une plage de cellules contenant du texte Durée estimative : 5 minutes Dans une feuille de calcul vierge, saisissez les cellules A1, A2 et A3 en insérant volontairement des espaces supplémentaires entre les mots. Exemple :

Créez une fonction VBA nommée SupprimeEspaces() qui : 

1) Inscrit à droite de chaque cellule le libellé absolu de l’adresse de la cellule traitée. Exemple : $A$1 en B1.



2) Inscrit en colonne C la formule Excel de suppression des espaces inutiles.

Indice

Utilisez la fonction locale Excel SUPPRESPACE().

Prérequis 1. 2. 3.

b. b. a. b.

4. 5.

c. c.

(exemple : Alea - Rnd) (exemple : Log)

Corrigé 11.1 Calculer une commission dans une feuille de calcul Excel avec une fonction VBA Exercice 1 Function Commission(dblCA As Double, dblPourcentage As Double) _   As Double     Commission = dblCA * dblPourcentage   End Function

Commentaire N’oubliez pas que le séparateur d’arguments d’Excel est le point virgule (sauf pour les plages qui requièrent le séparateur :). Pour utiliser la fonction VBA dans Excel, vous devez donc saisir : =Commission(B1;B2)

Exercice 2 Function CommissionAvecFixe(dblCA As Double, _  dblPourcentage As Double, dblFixe As Double) As Double     CommissionAvecFixe = (dblCA * dblPourcentage) + dblFixe   End Function

Corrigé 11.2 Calculer le prix HT à partir du taux de TVA et du prix TTC avec une fonction VBA Function PrixHT(dblPrixTTC As Double, dblTauxTVA As Double) As Double    PrixHT = dblPrixTTC / (1 + dblTauxTVA)   End Function

Corrigé 11.3 Calculer la valeur acquise d’un placement avec une fonction VBA Function Placement(dblCapital As Double, dblTaux As Double, _   intAnnées As Double) As Double     Placement = dblCapital * (1 + dblTaux) ^ intAnnées   End Function

Corrigé 11.4 Rechercher le minimum dans une plage de cellules avec une fonction Excel dans du code VBA Sub ProcMinimum()     Dim dblMinimum As Double     dblMinimum = Application.WorksheetFunction.Min( _     Worksheets(1).Range("G1:J6"))     MsgBox "Le nombre minimum dans la plage [G1:J6] est " _     & dblMinimum   End Sub

Corrigé 11.5 Générer un nombre aléatoire à partir de code VBA utilisant une formule Excel Exercice 1 Sub NombreAléatoireFr()     Worksheets("Feuil1").Range("H10").FormulaLocal = "=ALEA()*100"   End Sub

Exercice 2 Sub NombreAléatoireAng()     Worksheets("feuil1").Range("H11").Formula = "=RAND()*100"   End Sub

Commentaire Il existe aussi la fonction VBA Rnd : Sub NombreAléatoire3()     Worksheets("feuil1").Range("H12").Formula = VBA.Rnd() * 100   End Sub

Corrigé 11.6 Calculer l’annuité constante avec Excel à partir d’une fonction VBA Function AnnuitéConstante(dblCapital As Double, dblTaux As Double, _   intAnnées As Double) As Double     AnnuitéConstante = dblCapital * dblTaux _     / (1 - (1 + dblTaux) ^ -intAnnées)   End Function

Corrigé 11.7 Supprimer tous les espaces inutiles et redondants d’une plage de cellules contenant du texte Sub SupprimeEspaces()     Dim rngCellule As Range     For Each rngCellule In Worksheets(2).Range("A1:A3").Cells  

    With rngCellule           .Offset(0, 1).Value = rngCellule.Address           .Offset(0, 2).FormulaLocal = _           "=SUPPRESPACE(" & rngCellule.Address & ")"       End With     Next rngCellule   End Sub

Introduction Durée : 1 heure 45 Mots-clés bouton, méthode, fonction, paramètre nommé, constante VBA, type de données Objectifs Utiliser les boîtes de dialogue standards d’Excel et de Windows. Contrôler la saisie de l’utilisateur. Effectuer une action selon ses choix si la saisie est correcte.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1 .       2 .       3 .  

Pour afficher un message dans une boîte de dialogue sans demande de saisie, il faut utiliser : a. MsgBox b. Debug.Print c. InputBox La ou les instructions suivantes sont correctes : a. MsgBox ("Bonjour. Nous sommes le" & Date, title = "Essai") b. MsgBox "Bonjour. Nous sommes le" & Date, title:="Essai" c. MsgBox prompt:="Bonjour. Nous sommes le " & Date, title:="Essai" Sont des constantes VBA des boîtes de dialogue : a. vbOKOnly

    4 .       5 .       6 .     7 .    

b. vbUseSystem c. vbDefaultButton2 La valeur renvoyée par les boutons est de type : a. texte b. numérique c. booléen Ces constantes ont les valeurs suivantes : a. vbOKOnly 0 b. vbYesNo 4 c. vbDefaultButton2 5 L’instruction Application.InputBox appelle : a. la méthode InputBox b. la fonction InputBox Pour proposer une boîte de saisie avec contrôle du type de données, il faut utiliser : a. la fonction InputBox b. la méthode InputBox

Énoncé 12.1 Afficher un message simple Durée estimative : 5 minutes Créez la procédure AffichageSimple qui affiche un simple message avec le seul bouton OK par défaut. Changez le titre "Microsoft Excel" par défaut. Exemple :

Énoncé 12.2 Demander une saisie sans contrôle particulier Durée estimative : 5 minutes Créez la procédure DemandeSaisie qui affiche la boîte ci-dessous demandant une saisie sans effectuer de contrôle particulier.

Énoncé 12.3 Appeler la boîte de dialogue "Ouvrir" de Windows Durée estimative : 5 minutes Classeur Boîtes de dialogue standards.xlsm - formulaire frmBoîtesWindows

Utilisez le formulaire frmBoîtesWindows. Créez la procédure événementielle qui affiche la boîte d’ouverture de fichiers de Windows quand on clique sur le bouton Ouvrir un document.

Indice Pour afficher le formulaire frmBoîtesWindows : Sub AfficheFormulaire()     frmBoîtesWindows.Show   End Sub

Énoncé 12.4 Appeler la boîte de dialogue "Couleurs" de Windows Durée estimative : 10 minutes Classeur Boîtes de dialogue standards.xlsm - formulaire frmBoîtesWindows

Utilisez le formulaire frmBoîtesWindows. Créez la procédure événementielle qui affiche la boîte de choix des couleurs de Windows quand on clique sur le bouton Choix des couleurs. Le choix d’une couleur change la couleur de fond du formulaire.

Note Pour réaliser et comprendre ce TP, consultez la partie Aide sur les API Windows du chapitre Introduction. Indice Pour la déclaration de la fonction ChooseColorA, reportez-vous à la documentation Microsoft fournie dans le fichier texte Win32API_PtrSafe.TXT, téléchargeable sur le site de l’éditeur. Ce fichier fait également partie des téléchargements proposés pour cet ouvrage.

Pour avoir toutes les informations sur le type de la structure attendu, consultez la page suivante : https://msdn.microsoft.com/en-us/library/windows/desktop/ms646830%28v=vs.85 %29.aspx

Énoncé 12.5 Choisir des boutons pour les boîtes de dialogue Durée estimative : 10 minutes Créez la procédure ChoixBoutons qui affiche la boîte de dialogue ci-après avec les boutons Oui - Non.

Énoncé 12.6 Déterminer le bouton par défaut parmi plusieurs boutons proposés Durée estimative : 10 minutes Créez la procédure BoutonSupprime qui affiche la boîte de dialogue ci-après avec le bouton Non comme bouton par défaut.

Énoncé 12.7 Choisir et ajouter une icône à une boîte de dialogue Durée estimative : 5 minutes Modifiez la procédure précédente de sorte qu’une icône appropriée au contexte soit ajoutée à la boîte. Résultat :

Énoncé 12.8 Contrôler la saisie Durée estimative : 5 minutes Créez la procédure DemandeSaisieContrôle qui propose une boîte de saisie et contrôle que la valeur saisie est bien un nombre.

Énoncé 12.9 Effectuer une action selon le choix de l’utilisateur Durée estimative : 15 minutes Créez la procédure ActionSelonChoix qui ajoute une nouvelle feuille de calcul si l’utilisateur valide cette option.

Énoncé 12.10 Effectuer un traitement tant que l’utilisateur réalise la saisie demandée Durée estimative : 15 minutes Créez la procédure BoucleSaisie qui calcule la racine carrée d’un nombre tant que l’utilisateur entre un nombre strictement positif. Si la saisie est correcte, la procédure affiche la racine carrée du nombre entré. Si l’utilisateur saisit un nombre négatif ou clique sur le bouton Annuler, la procédure s’arrête. Exemple :

Indice Fonction VBA pour calculer la racine carrée : Sqr

Énoncé 12.11 Enregistrer un classeur Durée estimative : 5 minutes Créez la procédure EnregistreClasseur qui présente la boîte de dialogue intégrée d’Excel permettant d’enregistrer un classeur.

Note La collection Dialogs permet d’accéder à toutes les boîtes de dialogue intégrées d’Excel. Il existe des dizaines de constantes, chacune correspondant à une boîte de dialogue intégrée. Ces constantes sont décrites dans le lien suivant : https://docs.microsoft.com/frFR/office/vba/api/excel.xlbuiltindialog

Énoncé 12.12 Modifier la police de la feuille de calcul Durée estimative : 5 minutes Créez la procédure PoliceFeuille qui présente la boîte de dialogue intégrée permettant de choisir une nouvelle police pour une feuille de calcul.

Énoncé 12.13 Modifier la couleur d’une plage de cellules Durée estimative : 10 minutes Créez la procédure CouleurCellules qui présente, à partir d’un bouton placé sur une feuille de calcul, la boîte de dialogue intégrée permettant de choisir une nouvelle couleur pour une plage de cellules. 

Indice Insérez auparavant un nouveau module et recopiez tout le code de la partie déclarations du code utilisé dans le module frmBoîtesWindows (exercice 12.4). Inspirez-vous de ce code pour traiter le présent exercice.

Prérequis 1. 2. 3. 4. 5. 6. 7.

a. b. et c. a. et c. b. a. et b. a. b.

Corrigé 12.1 Afficher un message simple

Sub AffichageSimple()     MsgBox prompt:="Bonjour. " & "Il est" & Time, _     Title:="Accueil"   End Sub

Commentaire Avec utilisation des paramètres nommés.

Corrigé 12.2 Demander une saisie sans contrôle particulier Sub DemandeSaisie()     Dim dblRéponse As Double     dblRéponse = InputBox("Saisissez un nombre", "Calcul")   End Sub

Corrigé 12.3 Appeler la boîte de dialogue "Ouvrir" de Windows Private Sub cmdOuvrirFichier_Click()     Application.Dialogs(xlDialogOpen).Show   End Sub

Commentaire À mettre obligatoirement dans la zone de code du formulaire.

Corrigé 12.4 Appeler la boîte de dialogue "Couleurs" de Windows Zone des déclarations : Option Explicit    ' CHOOSECOLOR structure    ' https://msdn.microsoft.com/en-us/library/windows/desktop/ms646830(v=vs.85).aspx  Private Type StructureChoixCouleur ' Structure utilisée comme argument                                      ' de la fonction ChooseColorA      lStructSize As Long    ' longueur de la structure en octets (calculée)      hwndOwner As LongPtr   ' Le handle de la UserForm      hInstance As LongPtr   ' par défaut 0      rgbResult As Long      ' résultat de la fonction = valeur de la couleur 

    lpCustColors As String ' chaîne Unicode = les couleurs de la palette      flags As Long          ' constantes fixant le style de la boîte de dialogue      lCustData As LongPtr      lpfnHook As LongPtr      lpTemplateName As String    End Type      ' Constantes permettant de fixer le style de la boîte de dialogue standard  Private Const CC_RGBINIT = &H1  Private Const CC_FULLOPEN = &H2  Private Const CC_PREVENTFULLOPEN = &H4  Private Const CC_SHOWHELP = &H8    Private Declare PtrSafe Function APIChoixCouleur Lib "comdlg32.dll" _  Alias "ChooseColorA" (pChoosecolor As StructureChoixCouleur) As Long  Private CustomColors() As Byte ' déclaration du tableau des couleurs

Commentaire À placer obligatoirement dans la zone de code du formulaire. Code des procédures : Private Sub UserForm_Initialize()     ' initialisation du tableau des couleurs à 0     ReDim CustomColors(0 To 16 * 4 - 1) As Byte      Dim i As Integer     For i = LBound(CustomColors) To UBound(CustomColors)         CustomColors(i) = 0     Next i  End Sub  Private Sub cmdCouleurs_Click()     Dim MaStructureChoixCouleur As StructureChoixCouleur     Dim lReturn As LongPtr     With MaStructureChoixCouleur      .lStructSize = LenB(MaStructureChoixCouleur)      .hwndOwner = Application.Hwnd      .hInstance = 0      .lpCustColors = StrConv(CustomColors, vbUnicode)      .flags = CC_FULLOPEN     End With     lReturn = APIChoixCouleur(MaStructureChoixCouleur)     If lReturn 0 Then         Me.BackColor = MaStructureChoixCouleur.rgbResult     Else         MsgBox "Abandon du choix de couleur"     End If  End Sub

Corrigé 12.5 Choisir des boutons pour les boîtes de dialogue Sub ChoixBoutons()     Dim strMessage As String     Dim intBouton As Integer     Dim intChoix As Integer  

  strMessage = "Voulez-vous continuer ?"     intBouton = vbYesNo     intChoix = MsgBox(strMessage, intBouton)   End Sub

Note La croix de fermeture de la fenêtre n’est plus accessible.

Corrigé 12.6 Déterminer le bouton par défaut parmi plusieurs boutons proposés Sub BoutonSupprime()     Dim strMessage As String     Dim intBouton As Integer     Dim strTitre As String     Dim intChoix As Integer     strMessage = "Confirmez la suppression de la dernière feuille"     intBouton = vbYesNo + vbDefaultButton2 + vbMsgBoxHelpButton     strTitre = "Danger !!!"     intChoix = MsgBox(strMessage, intBouton, strTitre)   End Sub

Corrigé 12.7 Choisir et ajouter une icône à une boîte de dialogue Modifiez la procédure précédente en changeant la ligne : intBouton = vbYesNo + vbDefaultButton2 + vbMsgBoxHelpButton

par : intBouton = vbYesNo + vbDefaultButton2 + vbMsgBoxHelpButton + vbCritical

Corrigé 12.8 Contrôler la saisie Sub DemandeSaisieContrôle()     Dim dblRéponse As Double     dblRéponse = Application.InputBox("Entrez un nombre SVP", _     "Vérification de la saisie", Type:=1)   End Sub

Corrigé 12.9 Effectuer une action selon le choix de l’utilisateur Sub ActionSelonChoix()     Dim strMessage As String     Dim intBoutonIcône As Integer     Dim strTitre As String     Dim intChoix As Integer     strMessage = "Ajout d'une nouvelle feuille de calcul"     intBoutonIcône = vbQuestion + vbYesNo + vbDefaultButton2     strTitre = "Action"     intChoix = MsgBox(strMessage, intBoutonIcône, strTitre)     If intChoix = vbYes Then       ActiveWorkbook.Sheets.Add _       After:=Worksheets(Worksheets.Count)       MsgBox "Ajout de feuille réalisé", vbOKOnly, "Fin"     Else       MsgBox "Opération annulée", vbOKOnly, "Fin"     End If   End Sub

Corrigé 12.10 Effectuer un traitement tant que l’utilisateur réalise la saisie demandée Sub BoucleSaisie()     Dim dblSaisie As Double     dblSaisie = Application.InputBox("Entrez un nombre > 0", _     "Calcul de la racine carrée", Type:=1)     Do While dblSaisie > 0       MsgBox "La racine carrée de " & dblSaisie & "est :" _       & Sqr(dblSaisie)       dblSaisie = Application.InputBox("Entrer un nombre > 0", _       "Calcul de la racine carré", Type:=1)     Loop     MsgBox "Saisie annulée", vbOKOnly, "Choix refusé"   End Sub

Corrigé 12.11 Enregistrer un classeur Public Sub EnregistreClasseur     Application.Dialogs(xlDialogSaveWorkbook).Show   End Sub

Corrigé 12.12 Modifier la police de la feuille de calcul Public Sub PoliceFeuille()       Application.Dialogs(xlDialogFormatFont).Show   End Sub

Corrigé 12.13 Modifier la couleur d’une plage de cellules Dans un nouveau module, reprendre à l’identique la partie déclaration du corrigé 12.4. Code des procédures : Private Sub Initialise_Couleurs()     ' initialisation du tableau des couleurs à 0     ReDim CustomColors(0 To 16 * 4 - 1) As Byte     Dim i As Integer     For i = LBound(CustomColors) To UBound(CustomColors)         CustomColors(i) = 0     Next i  End Sub    Sub CouleurCellules()     Dim MaStructureChoixCouleur As StructureChoixCouleur     Dim lReturn As LongPtr     Call Initialise_Couleurs     With MaStructureChoixCouleur       .lStructSize = LenB(MaStructureChoixCouleur)       .hwndOwner = Application.Hwnd       .hInstance = 0       .lpCustColors = StrConv(CustomColors, vbUnicode)       .flags = CC_FULLOPEN     End With     lReturn = APIChoixCouleur(MaStructureChoixCouleur)     If lReturn 0 Then          Selection.Interior.Color = MaStructureChoixCouleur.rgbResult     Else         MsgBox "Abandon du choix de couleur"     End If  End Sub

Note Pour que l’accès soit possible à partir de tout module du projet, il suffit de rendre tous les accès publics.

Introduction Durée : 1 heure 35 Mots-clés UserForm, méthode, propriété, événement, charger, libérer, masquer, label, bouton de commande, zone de texte, contrôle Objectifs Utiliser des formulaires personnalisés. Découvrir quelques méthodes, propriétés et événements de l’objet UserForm.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1 Un objet UserForm est : .   a. une fenêtre ou une boîte de dialogue entrant dans la composition d’une interface utilisateur.   b. une feuille de calcul avec des boutons.   c. un classeur de feuilles de différents types. 2 Un objet UserForm comporte : .   a. des propriétés.   b. des méthodes.   c. des événements. 3 Sont des méthodes de l’objet UserForm : .   a. Show   b. Forms   c. Unload   d. PrintForm 4 Sont des événements de l’objet UserForm :

.       5 .       6 .      

a. MouseMove b. Initialize c. Change L’instruction suivante est correcte : a. Private Sub UserForm_Initialize b. Private Sub UserFormInitialize() c. Private Sub UserForm_Initialize() BackColor = VBA.RGB(0, 128, 64) donne au formulaire un fond : a. bleu b. jaune c. vert

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1 Un objet UserForm est : .   a. une fenêtre ou une boîte de dialogue entrant dans la composition d’une interface utilisateur.   b. une feuille de calcul avec des boutons.   c. un classeur de feuilles de différents types. 2 Un objet UserForm comporte : .   a. des propriétés.   b. des méthodes.   c. des événements. 3 Sont des méthodes de l’objet UserForm : .   a. Show   b. Forms   c. Unload   d. PrintForm 4 Sont des événements de l’objet UserForm :

.       5 .       6 .      

a. MouseMove b. Initialize c. Change L’instruction suivante est correcte : a. Private Sub UserForm_Initialize b. Private Sub UserFormInitialize() c. Private Sub UserForm_Initialize() BackColor = VBA.RGB(0, 128, 64) donne au formulaire un fond : a. bleu b. jaune c. vert

Énoncé 13.1 Afficher un formulaire Durée estimative : 10 minutes Dans l’IDE, dessinez un formulaire. Nommez-le frmVide. Créez ensuite la procédure AfficherfrmVide qui l’affiche.

Note Pour insérer un formulaire (UserForm), basculez dans l’IDE (raccourci-clavier [Alt] [F11]).

Énoncé 13.2 Afficher un formulaire en mode non modal Durée estimative : 5 minutes Rendre le formulaire précédent non modal (possibilité d’accéder à un autre formulaire ou à une fenêtre avec le formulaire ouvert).

Énoncé 13.3 Afficher un formulaire en détaillant son contenu Durée estimative : 20 minutes Classeur Formulaires.xlsm - formulaire frmContrôles

Dans l’IDE, dessinez un second formulaire tel que ci-après (ou téléchargez-le). Nommez-le frmContrôles. Créez la procédure AfficherfrmContrôles qui l’affiche avec le nombre et les noms des contrôles du formulaire.

Note Pour modifier le nom du formulaire, changez la valeur de la propriété Name. Pour modifier le titre du formulaire, changez la valeur de la propriété Caption. Pour ajouter les contrôles au formulaire, utilisez la boîte à outils.

Pour le titre "Le formulaire comporte …", utilisez le contrôle Intitulé. Pour la zone de texte, utilisez le contrôle Zone de texte. Pour les boutons, utilisez le contrôle Bouton de commande.

Indice Outre la procédure AfficherfrmContrôles, vous devez créer une procédure événementielle placée dans la zone de code du formulaire. Pour un contrôle Label se nommant lblControlLabel, utilisez sa propriété Caption pour lui affecter un libellé. Me.lblControlLabel.Caption = "Le formulaire comporte" _   & Me.Controls.Count & "contrôles"

Énoncé 13.4 Fermer un formulaire et libérer la mémoire Durée estimative : 5 minutes Créez une procédure événementielle qui ferme le formulaire et libère la mémoire quand on clique sur le bouton Quitter. Indice Vous pouvez nommer le bouton Quitter : cmdQuitter. Private Sub cmdQuitter_Click()    . . .   End Sub

Énoncé 13.5 Gérer le déplacement de la souris sur le formulaire Durée estimative : 15 minutes Ajoutez une procédure événementielle qui affiche les coordonnées du pointeur de la souris au-dessus du formulaire frmContrôles. Exemple :

Indice Pour l’affichage dans un contrôle Label nommé lblControlLabel : lblControlLabel.Caption = " . . . "

Pour l’affichage dans un contrôle TextBox nommé txtTexte : txtTexte.Text = " . . . "

Énoncé 13.6 Gérer le double clic de la souris sur le formulaire Durée estimative : 5 minutes Créez une procédure événementielle qui affiche le message ci-dessous quand on effectue un double clic sur le formulaire frmContrôles.

Indice Pour gérer l’événement double clic : UserForm_DblClick

Énoncé 13.7 Afficher un formulaire à partir d’un autre formulaire Durée estimative : 5 minutes Créez une procédure événementielle qui affiche le premier formulaire quand on effectue un clic sur le bouton 1er formulaire.

Indice Vous pouvez nommer le bouton 1er formulaire : cmdAfficheFrmVide Private Sub cmdAfficheFrmVide_Click()   ...   End Sub

Énoncé 13.8 Modifier le formulaire par programmation Durée estimative : 15 minutes Créez la procédure ModifFormulaire qui modifie le formulaire frmContrôles selon le modèle ci-après.

Indice Pour la couleur de fond du formulaire : RGB(0, 0, 255)

Pour la couleur de fond de la zone de texte : txtTexte.BackColor = RGB(0, 128, 64)

Pour la couleur du texte de la zone de texte : txtTexte.ForeColor = RGB(255, 255, 0)

Pour placer la barre de défilement : fmScrollBarsVertical

Énoncé 13.9 Désactiver la croix de fermeture Durée estimative : 15 minutes

Insérez un nouveau formulaire. Créez, dans la procédure événementielle UserForm_Initialize, le code qui désactive la croix de fermeture de ce formulaire.

Note N’oubliez pas d’ajouter un bouton Quitter pour refermer le formulaire. Indice Pour la déclaration des fonctions FindWindowA, GetSystemMenu et RemoveMenu, reportez-vous à la documentation Microsoft fournie dans le fichier texte Win32API_PtrSafe.TXT, téléchargeable sur le site de l’éditeur. Ce fichier fait également partie des téléchargements proposés pour cet ouvrage.

Prérequis 1. 2. 3. 4. 5. 6.

a. a., b. et c. a., c. et d. a. et b. c. c.

Corrigé 13.1 Afficher un formulaire Sub AfficherFrmVide()     frmVide.Show   End Sub

Commentaire La procédure doit être écrite dans la zone de code du formulaire. Dans l’IDE, effectuer un double clic sur le formulaire ou un clic droit puis choisir Code. Par défaut, un formulaire est affiché en mode modal. Il faut alors le fermer pour pouvoir accéder à un autre formulaire ou une autre fenêtre.

Corrigé 13.2 Afficher un formulaire en mode non modal Sub AfficherFrmVideNonModal()    frmVide.Show vbModeless   End Sub

Corrigé 13.3 Afficher un formulaire en détaillant son contenu Pour initialiser le formulaire : Private Sub UserForm_Initialize()     Dim ctlControle As Control     Dim strMessage As String     strMessage = "Le formulaire comporte " _     & Me.Controls.Count & " contrôles : "     For Each ctlControle In Me.Controls       strMessage = strMessage & " " & ctlControle.Name     Next     Me.lblControlLabel.Caption = strMessage   End Sub

Pour afficher le formulaire : Sub AfficherFrmContrôles()    frmContrôles.Show   End Sub

Corrigé 13.4 Fermer un formulaire et libérer la mémoire Private Sub cmdQuitter_Click()     Unload Me   End Sub

Commentaire La procédure doit être écrite dans la zone de code du formulaire contenant le bouton. En mode VBE, effectuez un double clic sur le bouton Quitter ou un clic droit puis choisissez Code.

Corrigé 13.5 Gérer le déplacement de la souris sur le formulaire Private Sub UserForm_MouseMove(ByVal Button As Integer, _  ByVal Shift As Integer, ByVal X As Single, _  ByVal Y As Single)    txtTexte.Text = "Vous avez bougé la souris. " _    & "Ce texte s'affiche. " _    & "Le formulaire réagit à des événements. " _    & ("Essayez aussi le double clic.")    Me.lblControlLabel.Caption = "X : " _    & X & vbCrLf & "Y : " & Y  End Sub

Corrigé 13.6 Gérer le double clic de la souris sur le formulaire Private Sub UserForm_DblClick(ByVal Cancel As MSForms.ReturnBoolean)     MsgBox "Je suis sensible au double clic"   End Sub

Corrigé 13.7 Afficher un formulaire à partir d’un autre formulaire Private Sub cmdAfficheFrmVide_Click()     Call AfficherFrmVide   End Sub

Corrigé 13.8 Modifier le formulaire par programmation Sub ModifFormulaire()     With frmContrôles       .BackColor = VBA.RGB(0, 0, 255)       .txtTexte.BackColor = VBA.RGB(0, 128, 64)       .txtTexte.ForeColor = VBA.RGB(255, 255, 0)       .ScrollBars = fmScrollBarsVertical       .Show     End With   End Sub

Commentaire Il est aussi possible d’effectuer les changements à partir de l’événement Initialize. Pour la gestion des couleurs, utilisez la fonction VBA.RGB : Rouge-Vert-Bleu de 0 à 255 pour l’intensité de chaque couleur. Il ne s’agit pas d’une procédure événementielle. On peut donc l’écrire dans n’importe quel module. 

Corrigé 13.9 Désactiver la croix de fermeture Option Explicit  ' Déclaration des fonctions API    Private Declare PtrSafe Function FindWindow Lib "user32.dll" _  Alias "FindWindowA" (ByVal lpClassName As String, _  ByVal lpWindowName As String) As LongPtr    Private Declare PtrSafe Function GetSystemMenu Lib "user32.dll" _  (ByVal hwnd As LongPtr, ByVal bRevert As Long) As LongPtr    Private Declare PtrSafe Function RemoveMenu Lib "user32.dll" ( _ 

  ByVal hMenu As LongPtr, _    ByVal nPosition As Long, _  ByVal wFlags As Long) As Long  ----------------------------------------------------------------   Private Sub cmdQuitter_Click()       Unload Me   End Sub   ----------------------------------------------------------------   ' Initialisation du formulaire   Private Sub UserForm_Initialize()     Dim hwnd As Long, hSysMenu As Long     hwnd = FindWindow(vbNullString, Me.Caption)     hSysMenu = GetSystemMenu(hwnd, 0)     ' désactive la croix de fermeture     RemoveMenu hSysMenu, &HF060, 0   End Sub

Introduction Durée : 3 heures 30 Mots-clés contrôle, bouton de commande, zone de liste, liste modifiable, zone de texte, intitulé, label, bouton à option, case à cocher, objet multipage, image, méthode, propriété, événement, contrôle ActiveX Objectifs Utiliser les principaux contrôles et se familiariser avec leurs propriétés, méthodes et événements.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1.       2.  

Un contrôle formulaire possède : a. des événements. b. des propriétés. c. des méthodes. La valeur d’index du premier contrôle est : a. 0

  b. 1 3. La valeur d’index indique l’ordre :   a. de haut en bas.   b. de gauche à droite.   c. dans lequel les contrôles ont été ajoutés à la collection. 4. La collection Controls d’un formulaire particulier permet :   a. d’énumérer les contrôles.   b. de définir les propriétés des contrôles.   c. de modifier le formulaire lui-même. 5. Méthodes communes aux contrôles TextBox, ListBox, ComboBox :   a. Move   b. SetFocus   c. Clear 6. Pour changer le texte d’un contrôle Label, il faut utiliser la propriété :   a. Caption   b. Value   c. Name 7. Dans une ListBox, la propriété ListCount permet de préciser :   a. le maximum de lignes à afficher en une seule fois.   b. le nombre d’entrées dans la liste.   c. le nombre de lignes sur lesquelles on peut effectuer un défilement. 8. Dans une ListBox ou ComboBox, la propriété ListIndex :   a. a pour valeur 0, si aucun élément n’est sélectionné.   b. a pour valeur -1, si aucun élément n’est sélectionné.   c. contient un index de la ligne sélectionnée dans la liste. 9. La propriété Text permet de modifier :   a. la valeur d’un contrôle ComboBox ou ListBox.   b. le texte d’un contrôle TextBox.   c. la valeur de la ligne sélectionnée d’un contrôle ComboBox ou ListBox. 10. Pour insérer une image, le contrôle nécessaire est :   a. Image   b. Picture   c. Shape Note Tous les formulaires sont créés dans l’environnement graphique de développement (IDE). Téléchargez le dossier Chapitre 14. Contrôles puis ouvrez le classeur Contrôles.xlsm. Basculez dans l’IDE par [Alt][F11].

Énoncé 14.1 Ajouter des contrôles par programmation Durée estimative : 15 minutes formulaire frmAjout

Vous disposez du formulaire frmAjout. Ouvrez le classeur Contrôles.xlsm. Créez deux procédures événementielles qui ajoutent respectivement un bouton de commande cmdAjoutBttCommande et un contrôle multipage (mpAjoutControle). Exemple :

Indice Noms des boutons : cmdAjoutBttCommande et cmdAjoutControle Private Sub cmdAjoutBttCommande_Click()   ...  End Sub Private Sub cmdAjoutControle_Click()   ...  End Sub

Énoncé 14.2 Masquer puis réafficher des contrôles Durée estimative : 15 minutes Formulaire frmGénéral

Vous disposez du formulaire frmGénéral. Créez trois procédures événementielles qui permettent respectivement de masquer tous les contrôles sauf le contrôle "Afficher" (cmdMasquer), d’afficher tous les contrôles masqués (cmdAfficher) et de fermer le formulaire (cmdQuitter).

Indice Nom des contrôles utilisés : 

la zone d’intitulé : lbl1 (Label)



les zones de texte : txt1, txt2 (TextBox)



les boutons de commande : cmdDescendre, cmdMonter, cmdAfficher, cmdMasquer, cmdQuitter (CommandButton)

Énoncé 14.3 Déplacer les contrôles Durée estimative : 10 minutes

À partir du formulaire précédent et des boutons Descendre et Monter, créez les procédures événementielles qui permettent de faire descendre ou monter tous les contrôles (respectivement cmdDescendre et cmdMonter). Exemple :

Énoncé 14.3 Déplacer les contrôles Durée estimative : 10 minutes À partir du formulaire précédent et des boutons Descendre et Monter, créez les procédures événementielles qui permettent de faire descendre ou monter tous les contrôles (respectivement cmdDescendre et cmdMonter). Exemple :

Énoncé 14.5 Faire réagir les contrôles à la frappe clavier Durée estimative : 10 minutes Créez la procédure événementielle qui affiche un message dès que la lettre "a" est saisie dans l’une des zones de texte.

Énoncé 14.6 Faire réagir les contrôles à la souris Durée estimative : 10 minutes

Créez la procédure événementielle qui fait apparaître un message dès que le pointeur de la souris survole le premier contrôle du formulaire (lbl1).

Énoncé 14.7 Afficher une colonne de données d’Excel avec le titre en en-tête dans une zone de liste Durée estimative : 15 minutes Tableau et formulaire frmListBox

Le tableau ci-après se trouve dans la feuille Feuil1 du classeur Excel Contrôles.xlsm. Créez la procédure événementielle qui transfère, en conservant le titre, les données dans la zone de liste lstRégions.

Indice

Noms des contrôles utilisés : 

la zone de liste : lstRégions (ListBox)



le bouton Valider : cmdValider (CommandButton)



le bouton Quitter : cmdQuitter (CommandButton)

Énoncé 14.8 Afficher dans Excel une donnée sélectionnée dans une zone de liste Durée estimative : 15 minutes Créez la procédure événementielle qui transfère la donnée sélectionnée de la zone de liste lstRégions dans la cellule E3 suite à un clic sur le bouton Valider. Exemple :

Énoncé 14.9 Reporter plusieurs lignes d’une zone de liste dans des cellules d’Excel Durée estimative : 15 minutes

Modifiez la procédure événementielle précédente de telle sorte qu’elle transfère toutes les données sélectionnées de la zone de liste lstRégions dans des cellules Excel à partir de E3. Exemple :

Énoncé 14.10 Affecter à un tableau Excel la saisie effectuée dans une liste déroulante modifiable Durée estimative : 15 minutes Formulaire frmComboBox

Vous disposez du formulaire frmComboBox. Créez la procédure événementielle qui permet à partir du formulaire l’ajout d’une région au tableau Excel. Exemple :

Indice Noms de certains contrôles utilisés : 

la liste déroulante ou zone de liste modifiable : cboRégions (ComboBox)



le bouton Ajouter : cmdAjout



le bouton Trier : cmdTrier

Énoncé 14.11 Trier un tableau Excel à partir d’une liste déroulante modifiable Durée estimative : 15 minutes À partir du bouton Trier du formulaire frmComboBox, créez la procédure événementielle qui trie le tableau Excel. Résultat :

Énoncé 14.12 Utiliser une liste déroulante à deux colonnes et affecter le choix à une plage Excel Durée estimative : 15 minutes Formulaire frmLD2Colonnes

Vous disposez du formulaire frmLD2Colonnes. Créez la procédure événementielle qui permet, après validation du choix, de reporter les données des deux colonnes dans des cellules Excel. Exemple :

Indice Noms de certains contrôles utilisés : 

la liste déroulante ou zone de liste modifiable : cboListeDéroulante2C (ComboBox)



le bouton Valider : cmdValider

Énoncé 14.13 Utiliser des boutons d’option Durée estimative : 10 minutes Formulaire frmOptionàCocher

Vous disposez du formulaire frmOptionàCocher. Créez la procédure événementielle qui affiche un libellé correspondant au choix. Exemple :

Indice Noms de certains contrôles utilisés : 

les zones d’intitulé : lbl1, lbl2 (Label)



les boutons d’option : optSucre, optSansSucre, optPasDeCafé (OptionButton)

Énoncé 14.14 Utiliser des cases à cocher Durée estimative : 15 minutes Formulaire frmCase, fichiers Banane.gif, Poire.gif et Pomme.gif (les images se trouvent dans le sous-dossier Images Sub)

Vous disposez du formulaire frmCase. Créez la procédure événementielle qui affiche les choix réalisés dans des cellules Excel et les images correspondantes dans le formulaire. Pour cet exercice, vous pouvez télécharger les images depuis le sousdossier Images Sub. Exemple :

Indice Noms de certains contrôles utilisés : 

les cases à cocher : chk1, chk2 et chk3



le bouton Valider : cmdValider

Énoncé 14.15 Intercepter une combinaison de touches Durée estimative : 5 minutes Formulaire frmGénéral

Dans le formulaire frmGénéral, programmez la combinaison de touches [Ctrl][Alt] [F12] sur la zone de texte txt1 pour déclencher une procédure connue du seul développeur.

Indice Rédigez la procédure "secrète" Sub Maintenance() dont le contenu peut être, dans le cas de l’exercice, un simple MsgBox.

Énoncé 14.16 Utiliser un bouton à bascule Durée estimative : 10 minutes Formulaire frmGénéral

Dans le formulaire frmGénéral, implantez un bouton à bascule qui permet d’alterner le mode activé/désactivé sur l’ensemble des contrôles implantés (sauf lui-même !). On modifiera également le libellé du bouton en "Activer"/"Désactiver".

Indice Nom du contrôle utilisé : tglActiver

Énoncé 14.17 Préparer une zone de saisie préremplie et sélectionnée Durée estimative : 5 minutes Formulaire frmGénéral

Dans le formulaire frmGénéral, remplissez la zone de texte txt1 avec la date du jour et l’heure avant l’affichage du formulaire et présentez ce contenu présélectionné à l’utilisateur. Placez le focus sur txt1 pour pouvoir effectuer une modification éventuelle dès l’affichage du formulaire.

Indice Propriétés à utiliser : SelStart et SelLength

Énoncé 14.18 Gérer des images

Durée estimative : 10 minutes Formulaire frmImages

dossier des images Images Sub Créez ou utilisez le formulaire frmImages. Le bouton Charger la liste reporte les noms des images au format JPG. L’image dont le nom a été sélectionné apparaît dans la partie droite du formulaire. Le bouton Afficher l’image dans une feuille de calcul recopie l’image affichée dans une feuille de calcul nommée Images.

Indice Pour gérer l’événement déclencheur de l’aperçu de l’image suite à un clic de souris :

Prérequis 1. 2. 3. 4. 5. 6. 7.

a., b. et c. a. c. a. et b. a. et b. a. b.

8. 9. 10.

b. et c. b. et c. a.

Corrigé 14.1 Ajouter des contrôles par programmation Déclaration : Private ctlNouveauContrôle As Control

Code du bouton Ajouter un bouton de commande : Private Sub cmdAjoutBttCommande_Click()     Set ctlNouveauContrôle = Controls.Add("Forms.CommandButton.1")     With ctlNouveauContrôle       .Left = 18       .Top = 10       .Width = 175       .Height = 40       .Caption = "Bouton ou Contrôle CommandButton"     End With   End Sub

Code du bouton Ajouter un contrôle multipage : Private Sub cmdAjoutControle_Click()     Set ctlNouveauContrôle = Controls.Add("Forms.MultiPage.1")     With ctlNouveauContrôle       .Left = 18       .Top = 60       .Width = 175       .Height = 40     End With   End Sub

Commentaire À mettre dans la zone de code du formulaire frmAjout.

Corrigé 14.2 Masquer puis réafficher des contrôles

Déclaration : Private ctlControl As Control

Code du bouton Masquer : Private Sub cmdMasquer_Click()    For Each ctlControl In Me.Controls       If ctlControl.Name "cmdAfficher" Then         ctlControl.Visible = False       End If     Next ctlControl  End Sub

Code du bouton Afficher : Private Sub cmdAfficher_Click()    For Each ctlControl In Me.Controls       ctlControl.Visible = True     Next ctlControl End Sub

Code du bouton Quitter : Private Sub cmdQuitter_Click()     Unload Me   End Sub

Commentaire À mettre dans la zone de code du formulaire frmGénéral.

Corrigé 14.3 Déplacer les contrôles Déclaration déjà effectuée à l’exercice 14.2 : Private ctlControl As Control

Code du bouton Descendre : Private Sub cmdDescendre_Click()     Controls.Move 0, 5   End Sub

Code du bouton Monter : Private Sub cmdMonter_Click()     Controls.Move 0, -5   End Sub

Corrigé 14.4 Rendre un contrôle dépendant d’un autre Private Sub txt1_Change()     txt2.Text = txt1.Text   End Sub

Corrigé 14.5 Faire réagir les contrôles à la frappe clavier Private Sub txt1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)     If KeyAscii = Asc("a") Then       MsgBox "Vous avez saisi la lettre a"     End If   End Sub

Corrigé 14.6 Faire réagir les contrôles à la souris Private Sub lbl1_MouseMove(ByVal Button As Integer, ByVal _   Shift As Integer, ByVal X As Single, ByVal Y As Single)     lbl1.Caption = "Je suis le contrôle lbl1"     MsgBox "Vous avez survolé le contrôle lbl1"   End Sub

Corrigé 14.7 Afficher une colonne de données d’Excel avec le titre en en-tête dans une zone de liste Private Sub UserForm_Activate()    Dim strAdresseDernièreRégion As String    strAdresseDernièreRégion = _    Worksheets("Feuil1").Range("B3").End(xlDown).Address    With lstRégions      .ColumnHeads = True      .RowSource = "B3:" & strAdresseDernièreRégion      .ListIndex = 0 

    .MultiSelect = fmMultiSelectExtended    End With  End sub

Corrigé 14.8 Afficher dans Excel une donnée sélectionnée dans une zone de liste Private Sub cmdValider_Click()       Dim intIndex As Integer     intIndex = lstRégions.ListIndex     With Worksheets("Feuil1")       .Range("E3:E12").ClearContents       .Range("E3") = lstRégions.ListIndex(intIndex)     End With   End sub

Corrigé 14.9 Reporter plusieurs lignes d’une zone de liste dans des cellules d’Excel Private Sub cmdValider_Click()     Dim intCompteurLignesListe As Integer     Dim intCompteurLignesExcel As Integer     Worksheets("Feuil1").Range("E3:E12").ClearContents     intCompteurLignesExcel = 3     With lstRégions      For intCompteurLignesListe = 0 To .ListCount - 1         If .Selected(intCompteurLignesListe) = True Then          Worksheets("Feuil1").Cells(intCompteurLignesExcel, 5) = _           .List(intCompteurLignesListe)           intCompteurLignesExcel = intCompteurLignesExcel + 1         End If       Next     End With   End Sub

Corrigé 14.10 Affecter à un tableau Excel la saisie effectuée dans une liste déroulante modifiable Initialisation avant affichage : Private Sub UserForm_Initialize() 

    Dim strAdresseDernièreRégion As String      strAdresseDernièreRégion = _       Worksheets("Feuil1").Range("B100").End(xlUp).Address      With cboRégions          .RowSource = "B3:" & strAdresseDernièreRégion          .ListIndex = 0      End With  End Sub

Code du bouton Ajouter : Private Sub cmdAjout_Click()       Dim strNouvelleCellule As String       strNouvelleCellule = _      Worksheets("Feuil1").Range("B3").End(xlDown).Offset(1, 0).Address       Worksheets("Feuil1").Range(strNouvelleCellule) = cboRégions.Text   End Sub

Corrigé 14.11 Trier un tableau Excel à partir d’une liste déroulante modifiable Private Sub cmdTrier_Click()       With Worksheets("Feuil1").Sort           .SortFields.Clear           .SortFields.Add Key:=Worksheets("Feuil1").Range("B2")           .SetRange Worksheets("Feuil1").Range("B2").CurrentRegion           .Header = xlYes           .MatchCase = False           .Orientation = xlTopToBottom           .Apply       End With   End Sub

Corrigé 14.12 Utiliser une liste déroulante à deux colonnes et affecter le choix à une plage Excel Sur activation du formulaire : Private Sub UserForm_Activate()    Dim strAdresseDernièreRégion As String    strAdresseDernièreRégion = _    Worksheets("Feuil1").Range("C3").End(xlDown).Address    With cboListeDéroulante2      .ColumnCount = 2      .ColumnHeads = True      .RowSource = "B3:" & strAdresseDernièreRégion      .ListIndex = 0    End With 

End Sub

Code du bouton Valider : Private Sub cmdValider_Click()     With Worksheets("Feuil1")       .Range("E3:F12").ClearContents       .Range("E3") = cboListeDéroulante2C.Column(0, _       cboListeDéroulante2C.ListIndex)       .Range("F3") = cboListeDéroulante2C.Column(1, _       cboListeDéroulante2C.ListIndex)     End With   End Sub

Corrigé 14.13 Utiliser des boutons d’option Private Sub cmdQuitter_Click()     Unload Me   End Sub   ---------------------------------------------------   Private Sub optPasDeCafé_Click()     lbl2.Caption = "non merci, pas de café"   End Sub   ---------------------------------------------------   Private Sub optSansSucre_Click()     lbl2.Caption = "sans sucre"   End Sub   ---------------------------------------------------   Private Sub optSucre_Click()     lbl2.Caption = "avec du sucre, svp"   End Sub   ---------------------------------------------------   Private Sub UserForm_Initialize()     lbl1.Caption = "Votre café, vous le voulez ?"   End Sub

Corrigé 14.14 Utiliser des cases à cocher Initialisation : Private Sub UserForm_Initialize()     With Worksheets("Feuil1")       chk1.Caption = .Range("E16")       chk2.Caption = .Range("E17")       chk3.Caption = .Range("E18")       With .Range("D16:D18")         .Font.Name = "Wingdings 3"         .Font.Size = 14         .VerticalAlignment = xlCenter         .HorizontalAlignment = xlRight       End With     End With     strChemin = ThisWorkbook.Path & "\Images Sub\"   End Sub

Code du bouton Valider : Private Sub cmdValider_Click()     With Worksheets("Feuil1")       .Range("D16:D18").ClearContents       If chk1.Value = True Then .Range("D16") = "a"       If chk2.Value = True Then .Range("D17") = "a"       If chk3.Value = True Then .Range("D18") = "a"     End With   End Sub

Code pour les images : Private Sub chk1_Click()     If chk1.Value = True Then       imgFruit1.Picture = LoadPicture(strChemin & "\Pomme.gif")     Else       imgFruit1.Picture = LoadPicture("")     End If   End Sub   ---------------------------------------------------   Private Sub chk2_Click()     If chk2.Value = True Then       imgFruit2.Picture = LoadPicture(strChemin & "\Poire.gif")     Else       imgFruit2.Picture = LoadPicture("")     End If   End Sub   ---------------------------------------------------   Private Sub chk3_Click()     If chk3.Value = True Then       imgFruit3.Picture = LoadPicture(strChemin & "\Banane.gif")     Else       imgFruit3.Picture = LoadPicture("")     End If   End Sub

Corrigé 14.15 Intercepter une combinaison de touches Private Sub txt1_KeyUp(ByVal KeyCode As _  MSForms.ReturnInteger, ByVal Shift As Integer)      If KeyCode = vbKeyF12 And Shift = 6 Then          Call Maintenance       End If   End Sub  Sub Maintenance()      MsgBox "Vous avez bien effectué [Ctrl][Alt][F8]" & _      vbCrLf & "Vous êtes passé en mode Maintenance"  End Sub

Corrigé 14.16 Utiliser un bouton à bascule

Private Sub tglActiver_Click()       For Each ctlControl In Me.Controls           If ctlControl.Name "tglActiver" Then             ctlControl.Enabled = Not ctlControl.Enabled           End If       Next ctlControl       With tglActiver           If .Caption = "Désactiver" Then               .Caption = "Activer"           Else               .Caption = "Désactiver"           End If       End With   End Sub

Corrigé 14.17 Préparer une zone de saisie préremplie et sélectionnée Private Sub UserForm_Initialize()       With txt1           .Text = Now()           .SelStart = 0           .SelLength = Len(.Text)           .SetFocus       End With   End Sub

Corrigé 14.18 Gérer des images Private strImageChoisie As String   --------------------------------------------------   Private Sub cmdAfficheImage_Click()     Worksheets("Images").Activate     Range("A1").Select     ActiveSheet.Pictures.Insert (strImageChoisie)   End Sub   --------------------------------------------------   Private Sub cmdChargeImages_Click()     Dim strImage As String     Dim strChemin As String       strChemin = ThisWorkbook.Path & "\Images Sub\"     ChDir strChemin     strImage = Dir("*.jpg")     lstImages.Clear     Do While strImage ""       lstImages.AddItem strImage       strImage = Dir()     Loop   End Sub   --------------------------------------------------   Private Sub lstImages_MouseUp(ByVal Button As Integer, _   ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)     With Me.lstImages  

    strImageChoisie = .List(.ListIndex)       imgImage.Picture = LoadPicture(strImageChoisie)     End With   End Sub

Introduction Durée : 1 heure 30 Mots-clés liaison, incorporation, OLE, propriété, méthode, verbe, classe, icône Objectifs Enrichir son application avec des ressources externes. Exploiter les possibilités des autres applications Microsoft dans Excel. Distinguer objet incorporé et objet lié.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1 .       2 .       3 .      

La collection OLEObjects contient tous les objets OLE : a. d’un formulaire. b. d’un classeur. c. d’une feuille de calcul. Sont des méthodes de la collection OLEObjects : a. Activate b. Add c. Select L’argument pour indiquer le fichier à utiliser lors de la création d’un objet OLE est : a. FileName b. ClassType c. Link

4 .       5 .     6 .      

Lors de la création d’un objet OLE, si l’argument ClassType est spécifié, les arguments suivants sont ignorés : a. FileName b. IconFileName c. Link L’argument IconFileName de la méthode Add permet d’afficher une icône pour représenter l’objet OLE si l’argument DisplayAsIcon a la valeur : a. True b. False Méthode pour déclencher une action de la part du serveur de l’objet OLE : a. b. c.

Run Execute Verb

Note Pour réaliser les exercices, ouvrez le classeur Objets Liés Incorporés.xlsm fourni en téléchargement. Le dossier contenant ce classeur possède un sous-dossier Ressources Projet. Le sous-dossier Ressources Projet contient toutes les ressources indispensables à la réalisation des exercices proposés : photo, vidéos, texte. Ressources : 

Deux vidéos : Danse du feu.wmv et Paysage Polynésien.wmv



Une photo : Paradis.jpg



Un document Word : Animaux.docx

Énoncé 15.1 Incorporer une image ou une photo Durée estimative : 15 minutes Paradis.jpg

Ouvrez un nouveau classeur. Créez la procédure PhotoIncorporée qui incorpore cette photo (Paradis.jpg dans le dossier Ressources Projet) d’un petit coin de paradis dans la feuille Feuil1. Pour le coin supérieur gauche, prenez la cellule B2 pour positionner la photo.

Énoncé 15.2 Incorporer WordPad dans Excel Durée estimative : 10 minutes Créez la procédure IncorporeWordPad qui incorpore le traitement de texte WordPad dans une feuille de calcul Excel. Pour le coin supérieur gauche de l’objet OLE, prenez la cellule B13. Exemple :

Note Pour obtenir l’image de l’icône de WordPad, vous devez indiquer où se trouve l’application. Lancez ou effectuez vous-même une recherche puis consultez les propriétés de l’application. Il ne vous reste plus qu’à copier le chemin et à l’insérer dans le code.

Énoncé 15.3 Incorporer un document Word dans Excel Durée estimative : 10 minutes Disposez de toute la puissance du traitement de texte de Microsoft Word dans une feuille de calcul Excel. Créez la procédure IncorporeWord qui réalise cette action. Pour le coin supérieur gauche de l’objet OLE, prenez la cellule B19. Résultat :

Note Ce document étant incorporé dans Excel, il n’est pas visible à l’extérieur de celui-ci.

Énoncé 15.4 Lier un document externe à Excel Durée estimative : 10 minutes Document Word Animaux.docx

Créez la procédure DocumentWordLié qui ouvre à partir d’Excel un document Word externe. Pour le coin supérieur gauche de l’objet OLE, prenez la cellule B25.

Note Le document est externe à Excel et visible par les autres applications. Celui-ci peut être modifié de manière interne ou externe.

Énoncé 15.5 Incorporer une vidéo Durée estimative : 10 minutes Pour cet exercice, utilisez la séquence vidéo Danse du feu.wmv.

Créez la procédure VidéoIncorporée qui incorpore une vidéo à Excel avec une simple représentation sous forme d’icône. Un double clic sur l’icône déclenche la séquence vidéo. Pour le coin supérieur gauche de l’objet OLE, prenez la cellule E2.

Énoncé 15.6 Lier une vidéo Durée estimative : 10 minutes Utilisez pour cet exercice la séquence vidéo Paysage Polynésien.wmv.

Créez la procédure VidéoLiée qui lie une vidéo à Excel avec présentation de la première image. Un double clic sur l’image déclenche la séquence vidéo. Pour le coin supérieur gauche de l’objet OLE, prenez la cellule E8.

Énoncé 15.7 Créer un menu pour les objets liés ou incorporés Durée estimative : 25 minutes Formulaire frmMenu

Vous disposez du formulaire frmMenu. Créez la procédure Menu qui affiche ce formulaire. Le fait de cliquer sur les boutons lance les vidéos ou présente le document Word sur les animaux.

Prérequis 1. 2. 3. 4. 5. 6.

c. b. et c. a. a. et c. a. c.

Corrigé 15.1 Incorporer une image ou une photo Dans la partie déclaration : Private strChemin As String

Procédure commune pour le chemin : Sub CheminRessources()     strChemin = ThisWorkbook.Path & "\Ressources Projet\"   End Sub

Commentaire Le chemin indiqué ici précise que les ressources (photos, vidéos) sont dans le sousdossier Ressources Projet. Code pour incorporer la photo : Sub PhotoIncorporée()     Call CheminRessources     With Worksheets(1)       .Range("B2").Select       .OLEObjects.Add Filename:=strChemin & "Paradis.jpg", _       Link:=False     End With   End Sub

Corrigé 15.2 Incorporer WordPad dans Excel Sub IncorporeWordPad()    Dim strCheminWordPad As String    '====================================================    ' La valeur de la variable strCheminWordPad    ' doit être adaptée à votre installation    '====================================================    strCheminWordPad = "C:\Windows\WinSxS\wow64_microsoft-windows-wordpad_ 31bf3856ad364e35_10.0.19041.1_none_ee00310940a3cd37\wordpad.exe"  Worksheets(1).Range("B13").Select    Worksheets(1).OLEObjects. _    Add(ClassType:="Wordpad.Document.1", _    DisplayAsIcon:=True, IconFileName:=strCheminWordPad, _    IconIndex:=0, IconLabel:="Document WordPad").Activate  End Sub

Corrigé 15.3 Incorporer un document Word dans Excel Sub IncorporeWord(      Dim strCheminWord As String    '====================================================    ' La valeur de la variable strCheminWord     ' doit être adaptée à votre installation    '====================================================    strCheminWord = "C:\Program Files (x86)\Microsoft Office\root\Office16\ WINWORD.EXE"    Worksheets(1).Range("B19").Select    Worksheets(1).OLEObjects.Add(ClassType:="Word.Document", _    DisplayAsIcon:=True, _    IconFileName:=strCheminWord, _    IconIndex:=1, IconLabel:="Microsoft Word").Activate  End Sub

Corrigé 15.4 Lier un document externe à Excel Sub DocumentWordLié()     Dim strCheminWord As String 

  '====================================================    ' La valeur de la variable strCheminWord     ' doit être adaptée à votre installation    '====================================================    strCheminWord = "C:\Program Files (x86)\Microsoft Office\root\Office16\ WINWORD.EXE"      Call CheminRessources    On Error GoTo SortieErreur    Worksheets(1).Range("B25").Select    '====================================================================    Worksheets(1).OLEObjects.Add(Filename:=strChemin & "Animaux.docx", _    DisplayAsIcon:=True,    IconFileName:=strCheminWord,    Link:=True,    IconIndex:=0,    IconLabel:="Document Word Externe Lié").Activate    '===========================================================    Worksheets(1).Range("J2") = Selection.Name      Exit Sub  SortieErreur:    MsgBox "Vérifier le chemin de l'icône"  End Sub

Corrigé 15.5 Incorporer une vidéo Sub VidéoIncorporée()     Call CheminRessources    Worksheets(1).Range("E2").Select    ActiveSheet.OLEObjects.Add(Filename:= _      strChemin & "Danse du feu.wmv", Link:=False, _     DisplayAsIcon:=True, IconFileName:=" C:\Program Files\VideoLAN\VLC", _      IconIndex:=0, IconLabel:="Danse du feu").Select    Selection.Verb  End Sub

Corrigé 15.6 Lier une vidéo Sub VidéoLiée()    Call CheminRessources    Worksheets(1).Range("E8").Select    ActiveSheet.OLEObjects.Add(Filename:= _    strChemin & "Paysage Polynésien.wmv", Link:=True, _    DisplayAsIcon:=False).Select    Selection.Verb  End Sub

Corrigé 15.7 Créer un menu pour les objets liés ou incorporés Code pour afficher le formulaire : Sub Menu()     frmMenu.Show   End Sub

Code des événements Click des étiquettes de la UserForm frmMenu : Private Sub lblDanseFeu_Click()      Call VidéoIncorporée  End Sub    Private Sub lbllLecture_Click()   Call DocumentWordLié  End Sub    Private Sub lblPaysage_Click()   Call VidéoLiée  End Sub

Introduction Durée : 2 heures 45 Mots-clés référence, bibliothèque, classe, SGBDR, table, enregistrement, requête, SQL, ADO, ODBC, connexion, Access, Word, Outlook Objectifs Piloter des applications Microsoft depuis Excel et échanger des données.

Prérequis

Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après (certaines questions peuvent nécessiter plusieurs réponses) : 1. Le mot-clé Set permet :   a. d’affecter une référence d’objet à une variable déclarée de type Object.   b. de créer une copie de l’objet. 2. Le mot-clé Nothing permet :   a. de prévenir qu’une variable objet n’a pas été déclarée.   b. de libérer les ressources mémoire et système associées à un objet. 3. Types d’objets permettant de déclarer des variables objets en référence aux applications Microsoft :   a. Word.Application   b. Outlook.Application   c. Access.Application   d. MSAccess.Application 4. Fonction pour accéder à un objet Automation à partir d’un projet VBA :   a. GetObject   b. Activate   c. Select 5. Pour ajouter un nouveau document Word, il faut utiliser la collection :   a. Documents   b. Workbooks   c. Document 6. La méthode CreateItem crée :   a. une nouvelle page Word.   b. un nouvel élément Outlook.   c. un nouveau titre d’onglet pour une feuille Excel. 7. Un objet QueryTable est un objet :   a. Access   b. Word   c. Excel 8. La méthode Add pour créer une table de requêtes QueryTable a besoin obligatoirement des paramètres suivants :   a. Connection   b. Destination   c. SQL 9. Les chaînes de connexion OLE DB ou ODBC sont de type :   a. Byte   b. Double

  10 .     11 .       12 .    

c. String SQL permet à Excel : a. de se connecter à une base de données. b. d’extraire des données d’une base selon des critères précisés. Affectation à une variable d’une connexion avec le modèle d’accès aux données ADO : a. Set strConnexion = ADODB b. Set strConnexion = New ADODB.Connection c. Set strConnexion = Connection L’objet Recordset peut représenter : a. tous les enregistrements d’une table d’une base de données. b. un jeu d’enregistrements résultant d’une requête SQL.

Note Avant de réaliser les exercices, enregistrez un classeur Excel (classeur prenant en charge les macros) nommé Collaboration avec les Applications MS.xlsm. Effectuez vos exercices à partir de ce classeur. Le sous-dossier Ressources contient toutes les ressources indispensables à la réalisation des exercices proposés : 

Une base de données : BDClients.accdb



Un document Word servant d’exemple : Bilan.docx

Enfin, référencez au préalable la bibliothèque de Microsoft Word Microsoft 365 (Outils - Références).

Énoncé 16.1 Lancer Word Durée estimative : 10 minutes Créez la procédure LancementWord qui lance Word à partir d’Excel. Note Aucun document n’est proposé à l’ouverture de Word. Indice Pensez à libérer la mémoire. Pour cela, en fin de procédure, affectez la valeur Nothing aux variables objets.

Énoncé 16.2 Créer un document Word

Durée estimative : 20 minutes Bilan.docx

Créez la procédure CréerDocWord qui crée un document Word BilanCréé.docx à partir d’Excel. Reproduire l’exemple fourni dans le document Bilan.docx. Société FMP Indice À l’ouverture un document Word ne dispose que d’une section. Format Word piloté depuis Excel : 

Titre : centré



Style de police : gras



Taille de police : 20

Énoncé 16.3 Ouvrir un document Word Durée estimative : 10 minutes Refermez avec le menu Word le document Bilan.docx. Créez ensuite la procédure OuvrirDocWord qui ouvre ce document depuis Excel. Indice Pensez à l’extension .docx pour l’ouverture du document.

Énoncé 16.4 Modifier un document Word Durée estimative : 15 minutes

Créez la procédure ModifierDocWord qui modifie le document Bilan.docx en insérant une nouvelle ligne. Exemple : Société FMP Ceci est la suite

Énoncé 16.5 Lancer Outlook Durée estimative : 10 minutes Créez la procédure LancementOutlook qui lance Outlook depuis Excel. Référencez au préalable la bibliothèque de Microsoft Outlook (Outils - Références).

Énoncé 16.6 Envoyer un mail avec Outlook

Durée estimative : 15 minutes Créez la procédure EnvoiMail qui envoie un e-mail directement depuis Excel en pilotant Outlook Office 365.

Note Selon votre version de Windows et les protections installées, vous aurez à confirmer l’envoi.

Énoncé 16.7 Lancer Access Durée estimative : 5 minutes Créez la procédure LancementAccess qui lance Access à partir d’Excel. Note Référencez au préalable la bibliothèque de Microsoft Access (Outils - Références).

Énoncé 16.8 Créer une base de données Access Durée estimative : 10 minutes Créez la procédure CréationBDAccess qui crée une base de données Access nommée NouvelleBD.accdb à partir d’Excel.

Énoncé 16.9 Importer une table entière Access (avec QueryTables) Durée estimative : 15 minutes BDClients.accdb

Créez la procédure ImportEnrBDdansExcel qui importe dans une feuille Excel uniquement les données de la table Clients de la base BDClients. Créez ensuite un bouton qui met à jour les données externes. Exemple :

Note Des connaissances élémentaires du langage de requêtes SQL sont nécessaires.

Énoncé 16.10 Importer certains champs d’une table Access (avec ADO)

Durée estimative : 20 minutes Créez la procédure ImportEnrBDADOdansExcel qui effectue la même importation mais en utilisant les bibliothèques ADO de Microsoft. Note Référencez au préalable la bibliothèque des objets ADO (Outils - Références).

Énoncé 16.11 Importer certains enregistrements d’une table Access Durée estimative : 10 minutes À partir de la table Clients de la base de données BDClients.accdb, créez la procédure ImportSQLA qui importe uniquement le(s) enregistrement(s) pour le(s) client(s) dont le nom est Durand. Créez ensuite un bouton qui met à jour les données externes. Exemple :

Énoncé 16.12 Importer certains enregistrements de plusieurs tables Access Durée estimative : 10 minutes À partir de la base de données BDClients.accdb, créez la procédure ImportSQLB qui importe la liste des clients ayant passé une commande pour plus de 1000 Euros avec la date et le montant de celle-ci. Créez ensuite un bouton qui met à jour les données externes. Exemple :

Énoncé 16.13 Importer les enregistrements d’une base de données en utilisant une liaison ODBC Durée estimative : 15 minutes Aide ODBC.pdf

Créez la procédure ConnexionODBC qui importe tous les enregistrements de la table Commandes en utilisant une liaison ODBC. Prenez pour faciliter les tests le pilote du SGBDR Access. Créez ensuite un bouton qui met à jour les données externes. Indice Une QueryTable doit être créée avec deux arguments obligatoires, Connection et Destination. 

Prérequis 1. 2. 3. 4. 5. 6. 7. 8. 9.

a. b. a., b. et c. a. a. b. c. a. et b. c.

10. 11. 12.

b. b. a. et b.

Corrigé 16.1 Lancer Word Sub LancementWord()     Dim wd As Word.Application     Set wd = New Word.Application     wd.Visible = True     Set wd = Nothing    End Sub

Commentaire Le mot-clé New est utilisé pour créer une nouvelle instance de la classe.

Corrigé 16.2 Créer un document Word Partie Déclarations : Private strChemin As String

Code pour le chemin : Sub Chemin()     strchemin = ThisWorkbook.Path & "\Ressources\"   End Sub

Code pour créer un document Word : Sub CréerDocWord()     Dim wd As Word.Application    Dim docWord As Word.Document    Call Chemin    Set wd = New Word.Application    Set docWord = wd.Documents.Add    With docWord.Sections(1).Range      .Text = "Société FMP" & Chr(10)      .Font.Bold = True      .Font.Size = 20      .ParagraphFormat.Alignment = wdAlignParagraphCenter    End With    With docWord 

    .SaveAs (strchemin & "Bilancréé.docx")      .Close savechanges:=wdDoNotSaveChanges    End With    wd.Quit    Set docWord = Nothing    Set wd = Nothing    MsgBox strchemin & "Bilancréé.docx a été créé"  End Sub

Note N’oubliez pas de libérer la mémoire. Pour cela, affectez la valeur Nothing aux variables objets.

Corrigé 16.3 Ouvrir un document Word Sub OuvrirDocWord()     Dim wd As Word.Application     Call Chemin     Set wd = New Word.Application     With wd       .Visible = True       .Documents.Open Filename:=strchemin & "Bilan.docx"     End With     Set wd = Nothing   End Sub

Corrigé 16.4 Modifier un document Word Sub ModifierDocWord()     Dim wd As Word.Application     Call Chemin     Set wd = New Word.Application     With wd       .Visible = True       .Documents.Open Filename:=strchemin & "Bilan.docx"       .ActiveDocument.Paragraphs(1).Range. _       InsertAfter "Ceci est la suite."     End With     Set wd = Nothing   End Sub

Corrigé 16.5 Lancer Outlook Sub LancementOutlook()     Dim ol As Outlook.Application     Set ol = New Outlook.Application     Set ol = Nothing   End Sub

Note Une occurrence d’Outlook est créée mais n’est pas visible.

Corrigé 16.6 Envoyer un mail avec Outlook Sub EnvoiMail()     Dim strMail As String     Dim ol As Outlook.Application     Dim olMessage As Outlook.MailItem     Set ol = New Outlook.Application     Set olMessage = ol.CreateItem(olMailItem)     On Error GoTo SortieErreur     strMail = InputBox("Saisir l'adresse e-mail")     If strMail "" Then          With olMessage               .To = strMail               .Subject = "Résultat du Loto"               .Body = "Bravo, vous avez gagné le gros lot"               .Importance = olImportanceHigh               .Send          End With     Else          MsgBox "L'adresse e-mail n'a pas été saisie"     End If   SortieErreur:     Set ol = Nothing   End Sub

Corrigé 16.7 Lancer Access Sub LancementAccess()     Dim ac As Access.Application     Set ac = New Access.Application     Set ac = Nothing   End Sub

Note

Une occurrence d’Access est créée mais n’est pas visible. Dans la pratique, la création d’une instance d’Access est peu utilisée. On préfère en général utiliser le moteur Jet grâce à la technique ADO (cf. ci-après).

Corrigé 16.8 Créer une base de données Access Sub CréationBDAccess()     Dim strDB As String    Dim ac As Access.Application      Call Chemin    strDB = strchemin & "NouvelleBD.accdb"    Set ac = New Access.Application    ac.NewCurrentDatabase strDB    ac.CloseCurrentDatabase    Set ac = Nothing    MsgBox "NouvelleBD.accdb a été créée"  End Sub

Corrigé 16.9 Importer une table entière Access (avec QueryTables) Code de l’importation : Sub ImportEnrBDdansExcel()     Dim qtRésultatRequête As QueryTable    Dim strSQL As String    Dim strConnexion As String      Call Chemin    '=========================================================    strSQL = "Select * From Clients"    strConnexion = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;" _    & "Data Source=" & strchemin & "BDClients.accdb"    '=========================================================    With ActiveSheet.QueryTables. _      Add(Connection:=strConnexion, _      Destination:=Range("E2"), Sql:=strSQL)      .Refresh    End With    MsgBox "Requête " & strSQL & " effectuée"  End Sub

Note

La valeur de l’argument Provider dépend de votre version d’Access.

Corrigé 16.10 Importer certains champs d’une table Access (avec ADO) Sub ImportEnrBDADOdansExcel()     Dim cn As ADODB.Connection    Dim rs As New ADODB.Recordset    Dim intLigneExcel As Integer    Dim strCheminBase As String      Call Chemin    ' Connexion à la base de données Comptoir.mdb    Set cn = New ADODB.Connection    Set rs = New ADODB.Recordset    With cn      .Provider = "Microsoft.ACE.OLEDB.12.0"      .Open strchemin & "BDClients.accdb" ' N'oubliez pas l'extension    End With    intLigneExcel = 2    With rs      .ActiveConnection = cn      .CursorType = adOpenForwardOnly      .LockType = adLockOptimistic      .Open ("Clients")      Range("A1").Value = "N°"      Range("B1").Value = "Nom"      Range("C1").Value = "Prénom"      Do While Not .EOF        Cells(intLigneExcel, 1).Value = .Fields("Numéro").Value        Cells(intLigneExcel, 2).Value = .Fields("Nom").Value        Cells(intLigneExcel, 3).Value = .Fields("Prénom").Value        .MoveNext        intLigneExcel = intLigneExcel + 1      Loop    End With    rs.Close    ' Fermeture de la connexion    cn.Close    Set rs = Nothing    Set cn = Nothing    MsgBox "Requête ADO effectuée"  End Sub

Corrigé 16.11 Importer certains enregistrements d’une table Access

Seule la requête SQL change. Pour le reste, la procédure ImportSQLA est identique à celle du corrigé 16.9 (ImportEnrBDdansExcel). '===================================================================   strSQL = "Select Numéro, Nom, Prénom From Clients    'Where Nom Like Durand'"   '===================================================================

Corrigé 16.12 Importer certains enregistrements de plusieurs tables Access Seule la requête SQL change. Pour le reste, la procédure ImportSQLB est identique à celle du corrigé 16.9 (ImportEnrBDdansExcel).              

'========================================================  strSQL = "Select Clients.Numéro, Clients.Nom," _  & "Clients.Prénom, Commandes.Montant, Commandes.Date" _  & " From Clients, Commandes" _  & " Where Clients.Numéro = Commandes.NuméroClient" _  & " And Commandes.Montant > 1000"  '========================================================

Corrigé 16.13 Importer les enregistrements d’une base de données en utilisant une liaison ODBC Sub ConnexionODBC()       Dim qt As QueryTable      Dim strSQL As String      Dim strConnexion As String      Call Chemin      strSQL = "SELECT * FROM Commandes"      strConnexion = "ODBC;DSN=MS Access Database;DBQ=" & strchemin _      & "\BDClients.accdb;"      Set qt = ActiveSheet.QueryTables.Add( _      Connection:=strConnexion, Destination:=Range("$A$15"))      With qt          .CommandText = strSQL          .Refresh      End With      Range("G27:G31").NumberFormat = "dd/mm/yy;@"  End Sub

Introduction Durée : 1 heure 20 Mots-clés page statique, page dynamique, actualiser, Excel, publication Web Objectifs Investir le domaine du Web en créant des documents web statiques ou dynamiques à partir des documents Excel.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après : 1 .       2 .       3 .       4

Paramètre et type de données pour enregistrer un classeur Excel au format HTML : a. Filename := xlHtml b. FileFormat:=xlHtml c. FileFormat:=xlXMLSpreadsheet Objet pour représenter un classeur Excel ou un élément du classeur qui a été enregistré dans une page web et qui peut être actualisé : a. Publish b. Object c. PublishObject Argument de la méthode Add qui précise quel élément du classeur Excel est enregistré en une page web : a. SourceType b. FileName c. DivID Lorsque le fichier HTML existe, la méthode Publish avec la valeur False :

.     5 .

a. remplace le fichier HTML. b. insère le ou les éléments à la fin du fichier HTML. Argument de la méthode Add qui spécifie si un élément est enregistré en tant que composant Microsoft Office Web interactif ou en tant que texte et images statiques :   a. SourceType   b. Source   c. HtmlType

Énoncé 17.1 Créer une page web statique à partir d’un classeur Excel Durée estimative : 15 minutes Classeurs Excel Web.xlsm et Exemple.xlsx

Ouvrez le classeur Web.xlsm. À partir de celui-ci, créez la procédure WebStatique1 qui ouvre le classeur Exemple.xlsx situé dans le sous-dossier \Ressources\Web Statique\ et l’enregistre en tant que page web sous le nom Classeur Web Statique 1.htm.

Note

Le classeur ne doit pas être protégé. Dans le cas contraire, VBA vous informe du problème.

Énoncé 17.2 Créer une page web statique à partir d’un classeur Excel chargé en mémoire Durée estimative : 5 minutes À partir du classeur Web.xlsm, créez la procédure WebStatique2 qui crée un nouveau classeur puis l’enregistre en page HTML. Note Pour les exercices qui suivent, nous continuerons à utiliser le classeur Web.xlsm.

Énoncé 17.3 Afficher la boîte de dialogue "Publier en tant que page web" Durée estimative : 5 minutes

Le classeur Web.xlsm comporte quatre éléments qui ont déjà été publiés. Pour les voir, vous devez afficher la boîte de dialogue intégrée Publier en tant que page web. Créez la procédure PublieBoite qui affiche cette boîte de dialogue. Ajoutez à la première feuille du classeur un bouton qui fait appel à cette procédure.

Note Cliquez sur Parcourir pour mettre à jour le chemin du fichier .htm, en fonction de l’arborescence de votre disque dur. Rappel de la structure des dossiers à créer :

Ne cochez pas le bouton Republier automatiquement…. Cette option peut être contraignante car de multiples sauvegardes intermédiaires peuvent être nécessaires pendant les modifications. Nous gérerons par programmation la publication.

Énoncé 17.4 Publier le classeur Durée estimative : 5 minutes Créez la procédure PublieMAJ_Classeur qui met à jour le classeur publié en tant que page web. Testez la procédure en suivant les étapes suivantes :

1. Faites Fichier - Enregistrer sous… et choisissez Type : Page Web (*.htm, *.html). Cochez le bouton Classeur entier puis cliquez sur le bouton Modifier le 2. titre. 3. Saisissez ClasseurWeb comme titre. 4. Cliquez sur le bouton Publier. Dans la nouvelle boîte de dialogue, décochez la case Republier 5. automatiquement.... 6. Cliquez sur le bouton Publier. 7. Effectuez des modifications sur les différentes feuilles. 8. Enregistrez le classeur. 9. Lancez la procédure PublieMAJ_Classeur. 10 Vérifiez dans votre navigateur Internet que les mises à jour ont bien été . effectuées sur la page web (appuyez sur [F5] pour l’actualiser).

Énoncé 17.5 Publier une feuille du classeur Durée estimative : 5 minutes Créez la procédure PublieMAJ_Feuille qui met à jour la page web liée à la première feuille du classeur. Pour tester la procédure, procédez comme précédemment, en donnant comme titre PageExcelWeb.

Énoncé 17.6 Publier une plage de données d’une feuille du classeur Durée estimative : 5 minutes La première feuille comporte deux tableaux. Créez la procédure PublieMAJ_PlagePiscine qui publie le tableau correspondant à la plage de données Piscine. Testez la procédure.

Énoncé 17.7 Mettre à jour toutes les pages web Durée estimative : 5 minutes À ce stade, trois pages web sont présentes. La première est liée au classeur, la deuxième à la première feuille de ce classeur et la troisième à la plage de données du tableau Piscine. Créez la procédure PublieMAJBoucle qui met à jour en même temps ces trois pages web. Testez la procédure. N’oubliez pas d’appuyer sur la touche [F5] pour réactualiser l’affichage. Indice Utilisez la boucle For Each.

Énoncé 17.8 Mettre à jour toutes les pages web selon certains critères Durée estimative : 5 minutes Créez la procédure PublieMAJBoucle_LesPlages qui met à jour uniquement les pages web qui correspondent à des plages de données publiées du classeur.

Indice La propriété SourceType de l’objet à publier pour spécifier une plage de données est xlSourceRange. 

Énoncé 17.9 Ajouter un lien hypertexte à une cellule Durée estimative : 10 minutes Créez la procédure LienInternet qui ajoute à la cellule E29 un lien hypertexte vers le site des Éditions ENI (http://www.editions-eni.fr/).

Énoncé 17.10 Extraire une information d’une page web Durée estimative : 20 minutes Créez la procédure ExtraireCours qui extrait un cours de bourse, mis à jour en temps réel, sur une page web. On choisit la page web suivante : https://www.boursorama.com/cours/1rPRAL/ Les principaux navigateurs permettent d’examiner le code source d’un élément de la page (par exemple, clic droit sur l’élément pour Mozilla Firefox). On obtient une information du type : 5.69

qui permet de déterminer le nom de la classe de l’information. On référence les bibliothèques « Microsoft HTML Object Library » et « Microsoft Internet Controls ». Indice La méthode getElementsByClassName renvoie une collection d’informations ayant toutes le même nom de classe. Un essai sera donc nécessaire au préalable pour déterminer la position de l’information recherchée.

Prérequis 1. 2. 3. 4. 5.

b. c. a. b. c.

Corrigé 17.1 Créer une page web statique à partir d’un classeur Excel Dans la partie déclaration : Private strChemin As String

Code du chemin du dossier qui contient le classeur Excel : Sub Chemin()     strChemin = ActiveWorkbook.Path   End Sub

Code pour obtenir un document HTML : Sub WebStatique1()     Dim strCheminRessources As String     Call Chemin     strCheminRessources = strChemin & _  

  "\Ressources\Web Statique\"       ' ouverture du classeur à transformer en page html     Workbooks.Open Filename:=strCheminRessources _     & "\Exemple.xlsx"      ActiveWorkbook.SaveAs Filename:=strCheminRessources _     & "Classeur Web Statique 1.htm", _     FileFormat:=xlHtml     MsgBox "Classeur " & ActiveWorkbook.Name & " enregistré."     ActiveWorkbook.Close savechanges:=False   End Sub

Note Les chemins correspondent à l’organisation des dossiers sur le disque dur. Un clic droit sur le tableau du document HTML permet d’exporter les données vers un classeur Excel.

Corrigé 17.2 Créer une page web statique à partir d’un classeur Excel chargé en mémoire Sub WebStatique2()     Dim strCheminRessources As String     Call Chemin     strCheminRessources = strChemin & _     "\Ressources\Web Statique\"       Workbooks.Add       ActiveWorkbook.SaveAs Filename:=strCheminRessources _     & "Classeur Web Statique 2", _     FileFormat:=xlHtml     ActiveWorkbook.Close savechanges:=False   End Sub

Corrigé 17.3 Afficher la boîte de dialogue "Publier en tant que page web" Public Sub PublieBoite()     Application.Dialogs(xlDialogPublishAsWebPage).Show  

End Sub

Corrigé 17.4 Publier le classeur Public Sub PublieMAJ_Classeur()       Dim po As PublishObject       With ActiveWorkbook           For Each po In .PublishObjects               If po.Title = "ClasseurWeb" Then                  po.Publish                  MsgBox "Classeur " & ActiveWorkbook.Name & " republié"                 Exit For               End If           Next po       End With  End Sub

Corrigé 17.5 Publier une feuille du classeur Public Sub PublieMAJ_Feuille()       Dim po As PublishObject       With ActiveWorkbook           For Each po In .PublishObjects               If po.Title = "PageExcelWeb" Then                  po.Publish                  MsgBox "première feuille republiée"                  Exit For               End If           Next po       End With   End Sub

Corrigé 17.6 Publier une plage de données d’une feuille du classeur Public Sub PublieMAJ_PlagePiscine()       Dim po As PublishObject       With ActiveWorkbook           For Each po In .PublishObjects               If po.Title = "Piscine" Then                  po.Publish                  MsgBox "Plage Piscine republiée"  

                End

            End Sub

       Exit For       End If   Next po   With  

Corrigé 17.7 Mettre à jour toutes les pages web Public Sub PublieMAJBoucle()       Dim po As PublishObject       With ActiveWorkbook           For Each po In .PublishObjects              po.Publish           Next po       End With       MsgBox "Republications effectuées..."   End Sub

Corrigé 17.8 Mettre à jour toutes les pages web selon certains critères Public Sub PublieMAJBoucle_LesPlages()       Dim po As PublishObject       With ActiveWorkbook           For Each po In .PublishObjects               If po.SourceType = xlSourceRange Then                  po.Publish               End If           Next po       End With       MsgBox "Plage de données republiées"   End Sub

Corrigé 17.9 Ajouter un lien hypertexte à une cellule Sub LienInternet()  

            End

ActiveSheet.Hyperlinks.Add Anchor:=Range("E29"), Address:= _   "http://www.editions-eni.fr/", _   TextToDisplay:="Editions ENI"   Sub

Corrigé 17.10 Extraire une information d’une page web Sub ExtraireCours()  '===============================================================  ' Référencement des bibliothèques Microsoft Internet Controls  ' et Microsoft HTML Object Library  '===============================================================      Dim IE As SHDocVw.InternetExplorer      Dim laPage As HTMLDocument      Dim contenu As IHTMLElementCollection      Dim article As IHTMLElement      Set IE = CreateObject("InternetExplorer.Application")        With IE          .navigate "https://www.boursorama.com/cours/1rPRAL/"          WaitIE IE          Set laPage = .document          Set contenu = laPage.getElementsByClassName("c-instrument c-instrument--last")    '=========================================  '  Code pour tester l'emplacement   '  de l'information recherchée  '  Note : Les index commencent à 0  '=========================================  '        For Each article In contenu  '         Debug.Print article.outerText  '        Next article  '=========================================          .Quit End With        MsgBox contenu(9).outerText      Set IE = Nothing  End Sub    Sub WaitIE(IE As SHDocVw.InternetExplorer)     'On boucle tant que la page n'est pas totalement chargée     Do Until IE.readyState = READYSTATE_COMPLETE        DoEvents     Loop  End Sub

Introduction

Durée : 1h30 Mots-clés fichier, dossier, dictionnaire, unité logique, FileSystemObject, fichier texte Objectifs Poursuivre la démarche systématique d’utilisation des objets par VBA en fonction d’un nouveau besoin. Dans ce chapitre, on laisse volontairement de côté les instructions natives de VBA concernant la gestion des fichiers (Kill, Open…) pour traiter le problème avec la bibliothèque d’objets adéquate.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après : 1 .       2 .       3 .         4 .        

L’objet racine de la bibliothèque permettant la gestion des fichiers sous forme d’objets est : a. FileList b. FileSearch c. FileSystemObject Pour utiliser les objets de gestion du système de fichiers, il faut référencer dans VBA la bibliothèque : a. Microsoft Scripting RunTime b. Microsoft File System Library c. Microsoft System Object Le ou les objets suivants sont accessibles dans la bibliothèque de gestion des fichiers : a. File b. SubFolder c. TextFile d. BinaryFile En programmation VBA, le nom de la bibliothèque des objets de gestion du système de fichiers apparaît comme : a. FileSystemObject b. Scripting c. Files d. Rien de tout ça.

5 .         6 .       7 .

Dans la bibliothèque d’objets, l’objet représentant une unité logique (un disque, une clé USB, un DVD..) est : a. Disk b. Unit c. Device d. Drive Dans la bibliothèque d’objets, l’objet SubFolder :

a. N’existe pas b. Représente un sous-dossier d’un dossier existant. c. Représente l’un des dossiers attachés à la racine d’une unité logique. Dans l’utilisation de la bibliothèque des objets de gestion des fichiers, les opérations de lecture/écriture d’un fichier texte s’effectuent de la manière suivante :   a. Avec l’objet TextStream   b. Avec l’objet TextFile   c. Ces opérations sont impossibles car le système ne traite pas le contenu des fichiers.

Énoncé 18.1 Gérer et utiliser les unités logiques Durée estimative : 15 minutes Listez, dans la procédure ListeUnitésLogiques, l’ensemble des unités logiques du système, avec pour chacune d’entre elles ses caractéristiques. Les résultats seront présentés dans une feuille Excel dédiée, sous forme d’un tableau comme dans l’exemple ci-dessous :

Indice Utilisez les propriétés DriveLetter, DriveType, FileSystem, AvailableSpace, IsReady.

Énoncé 18.2 Créer, gérer et supprimer les dossiers et les sous-dossiers Durée estimative : 15 minutes Classeur Excel GestionFichiers.xlsx

La feuille Pays contient un tableau, commençant en cellule A1, où chaque colonne représente un continent. Le nom du continent se trouve dans la 1 ère cellule de la colonne. Les cellules du dessous contiennent chacune le nom d’un pays du continent. Créez la procédure CréerMonde qui génère une arborescence de dossiers et sousdossiers (un dossier par continent puis un sous-dossier pour chaque pays). Les dossiers des continents seront placés dans un dossier maître nommé Monde, luimême créé en tant que sous-dossier du dossier contenant le fichier Excel GestionFichiers.xlsx.

Énoncé 18.3 Copier, déplacer et supprimer les fichiers Durée estimative : 20 minutes Créez la procédure CopieDéplace qui effectue les manipulations suivantes : 1 Copiez, dans le dossier CopieRessources, les fichiers situés dans le . dossier Ressources, lui-même sous-dossier du dossier contenant le fichier Excel GestionFichiers.xlsx. 2 Renommez les fichiers du dossier CopieRessources en leur ajoutant les . trois lettres "BIS" à la fin de chaque nom de fichier. 3 Déplacez ces fichiers "BIS" du dossier CopieRessources vers le dossier . Ressources. 4 Listez les fichiers du dossier Ressources. . 5 Effacez les fichiers "BIS" du dossier Ressources. .

Énoncé 18.4 Créer et utiliser un fichier texte Durée estimative : 20 minutes  Fichier texte English.ini.

Analysez le fichier de configuration English.ini dont la structure est la suivante :

Le fichier est divisé en sections (libellés entre […]). Chaque section contient des lignes avec une syntaxe paramètre = valeur. Créez la procédure AnalyseINI qui construit, à partir du fichier de données English.ini, dans une feuille Excel BddINI une base de données sous forme d’une table à trois colonnes : Section, Paramètre, Valeur suivant le modèle ci-dessous :

Énoncé 18.5 Créer et utiliser un dictionnaire Durée estimative : 20 minutes Le but de l’exercice est de construire un dictionnaire de traduction des fonctions Excel du français vers l’anglais. Dans la UserForm frmFonctionsExcel, effectuez les traitements suivants : 

Programmez l’événement Initialize de la UserForm pour créer un objet Dictionary dont les couples (clé-valeur) sont disponibles dans la feuille FonctionsExcel. Dans la même procédure, garnissez la zone de liste lstFonctions avec les fonctions Excel en français (colonne A).



Programmez l’événement Click de cette même zone de liste pour déclencher l’affichage de la fonction traduite en anglais dans le libellé lblFonctionAnglais.

Exemple de résultat :

Prérequis 1. 2. 3. 4. 5. 6. 7.

c. a. a. d. a. b. c.

Corrigé 18.1 Gérer et utiliser les unités logiques Sub ListeUnitésLogiques()     Dim fso As Scripting.FileSystemObject     Dim intLigne As Integer     Dim drvUnité As Scripting.Drive       ' Instanciation de l'objet FileSystemObject     Set fso = New Scripting.FileSystemObject       With Worksheets("Unités")         .Range("A1").CurrentRegion.Offset(1, 0).ClearContents         intLigne = 2           ' Balayage de la collection des unités logiques         For Each drvUnité In fso.Drives             .Cells(intLigne, 1).Value = drvUnité.DriveLetter               ' Test pour déterminer si l'unité logique est prête             If drvUnité.IsReady Then                 .Cells(intLigne, 2).Value = drvUnité.AvailableSpace                 .Cells(intLigne, 3).Value = drvUnité.FileSystem                 .Cells(intLigne, 4).Value = drvUnité.VolumeName                   ' Détermination du type d'unité logique                 ' Les types sont récapitulés à l'adresse suivante :                 ' https://docs.microsoft.com/en-us/office/vba/ language/reference/user-interface-help/drivetype-constants                 Select Case drvUnité.DriveType                     Case 0                         .Cells(intLigne, 5).Value = "Inconnu"                     Case 1                         .Cells(intLigne, 5).Value = "Disque mobile"                     Case 2                         .Cells(intLigne, 5).Value = "Disque fixe"                     Case 3                         .Cells(intLigne, 5).Value = "Disque réseau"                     Case 4                         .Cells(intLigne, 5).Value = "CD ROM"                     Case 5                         .Cells(intLigne, 5).Value = "Disque RAM"                 End Select             End If             intLigne = intLigne + 1         Next drvUnité     End With     MsgBox "Listage des unités terminé !"  End Sub

Commentaire Le mot-clé New est utilisé pour créer une nouvelle instance de la classe.

Corrigé 18.2 Créer, gérer et supprimer les dossiers et les sous-dossiers Sub CréerMonde()     Dim fso As Scripting.FileSystemObject     Dim drvUnité As Scripting.Drive     Dim strRacine As String     Dim intNbContinents As Integer     Dim intNoContinent As Integer     Dim intNoPays As Integer     Dim intNbPays As Integer     Dim rngPays As Range     Dim strContinent As String     Dim strPays As String       Set fso = New Scripting.FileSystemObject     Set rngPays = Worksheets("Pays").Range("A1").CurrentRegion     intNbContinents = rngPays.Columns.Count     intNbPays = rngPays.Rows.Count       ' Création du dossier Monde     fso.CreateFolder (ThisWorkbook.Path & "\Monde")       ' Balayage des continents       For intNoContinent = 1 To intNbContinents         ' Création du dossier continent         strContinent = rngPays.Cells(1, intNoContinent).Value         fso.CreateFolder (ThisWorkbook.Path & "\Monde\" & strContinent)           ' Balayage des pays         For intNoPays = 2 To intNbPays             strPays = rngPays.Cells(intNoPays, intNoContinent).Value             If strPays "" Then                 fso.CreateFolder (ThisWorkbook.Path & "\Monde\" & strContinent & "\" & strPays)             End If         Next intNoPays       Next intNoContinent     MsgBox "Création de l'arborescence des pays terminée !"     End Sub

Note N’oubliez pas de libérer la mémoire. Pour cela, affectez la valeur Nothing aux variables objets.

Corrigé 18.3 Copier, déplacer et supprimer les fichiers  Sub CopieDéplace() 

   Dim fso As Scripting.FileSystemObject     Dim fldAppli As Scripting.Folder     Dim ficFichier As Scripting.File     Dim strFichiers As String       Set fso = New Scripting.FileSystemObject     Set fldAppli = fso.GetFolder(ThisWorkbook.Path)          ' 1. Copie des fichiers du dossier Ressources     '    vers le dossier CopieRessources     For Each ficFichier In fldAppli.SubFolders("Ressources").Files         ficFichier.Copy Destination:=fldAppli.SubFolders("CopieRessources").Path & "\"         ' Remarque : le \ final est nécessaire !!!     Next ficFichier     MsgBox "Copie des fichiers effectuée..."       '2. Renommage     For Each ficFichier In fldAppli.SubFolders("CopieRessources").Files         ' Remplace le . par BIS.         ficFichier.Name = VBA.Replace(ficFichier.Name, ".", "BIS.")     Next ficFichier     MsgBox "Renommage des fichiers effectué..."       '3. Déplacement des fichiers BIS vers le dossier Ressources     For Each ficFichier In fldAppli.SubFolders("CopieRessources").Files         ficFichier.Move Destination:=fldAppli.SubFolders("Ressources").Path & "\"     Next ficFichier     MsgBox "Déplacement des fichiers BIS effectué..."       '4. Listage des fichiers du dossier Ressources     For Each ficFichier In fldAppli.SubFolders("Ressources").Files         strFichiers = strFichiers & ficFichier.Name & vbCrLf     Next ficFichier     MsgBox strFichiers       '5. Effacement des fichiers BIS du dossier Ressources     For Each ficFichier In fldAppli.SubFolders("Ressources").Files         'Recherche de la chaîne BIS. dans le nom du fichier         If VBA.InStr(Start:=1, String1:=ficFichier.Name, String2:="BIS.", compare:=vbBinaryCompare) > 0 Then             ficFichier.Delete         End If     Next ficFichier     MsgBox "Fichiers BIS effacés"    End Sub

Corrigé 18.4 Créer et utiliser un fichier texte Sub AnalyseINI()     Dim fso As Scripting.FileSystemObject     Dim txtINI As Scripting.TextStream     Dim strLigne As String     Dim strSection As String     Dim strParamètre As String     Dim strValeur As String     Dim strParamValeur() As String     Dim intLigneExcel As Integer 

        Set fso = New Scripting.FileSystemObject     Set txtINI = fso.OpenTextFile(Filename:=ThisWorkbook.Path & "\Ressources\EnglishBIS.ini", IOMode:=ForReading)     intLigneExcel = 2     ' Lecture séquentielle des lignes     Do While Not txtINI.AtEndOfStream         ' Lecture de la ligne         strLigne = txtINI.ReadLine         If VBA.Len(strLigne) > 0 Then             ' Analyse de la ligne             If VBA.Left(strLigne, 1) = "[" Then                 ' Nouvelle section                 strSection = VBA.Replace(VBA.Replace(strLigne, "[", ""), "]", "")             Else                 ' Ligne Paramètre=valeur                 ' Découpage de la ligne par le signe =                 ' Obtention d'un tableau à deux éléments                 strParamValeur = VBA.Split(strLigne, "=")                 strParamètre = strParamValeur(0)                 strValeur = strParamValeur(1)                 Worksheets("BddINI").Cells(intLigneExcel, 1).Value = strSection                 Worksheets("BddINI").Cells(intLigneExcel, 2).Value = strParamètre                 Worksheets("BddINI").Cells(intLigneExcel, 3).Value = strValeur                 intLigneExcel = intLigneExcel + 1             End If         End If     Loop   End Sub

Corrigé 18.5 Créer et utiliser un dictionnaire Le code suivant doit être placé dans le module de la UserForm frmFonctionsExcel. Option Explicit  Private dicFonctions As Scripting.Dictionary  ______________________________________________________________  Private Sub cmdQuitter_Click()     Unload Me  End Sub  ______________________________________________________________  Private Sub lstFonctions_Click()     lblFonctionAnglais.Caption = dicFonctions(lstFonctions.List(lstFonctions.ListIndex))  End Sub  ______________________________________________________________  Private Sub UserForm_Initialize()     Dim rngFonction As Range     Set dicFonctions = New Scripting.Dictionary       ' Balayage des fonctions Excel en français     For Each rngFonction In Worksheets("FonctionsExcel").Range("A1"). CurrentRegion.Columns(1).Cells         ' Garniture de la zone de liste         lstFonctions.AddItem rngFonction.Value                  ' Garniture du dictionnaire         dicFonctions.Add Key:=rngFonction.Value,

Item:=rngFonction.Offset(0, 1).Value     Next rngFonction            ' On pointe sur le 1er élément de la liste          lstFonctions.ListIndex = 0      lblFonctionAnglais.Caption = dicFonctions(lstFonctions.List(0))       End Sub

Durée : 2 heures Mots-clés balise, XML, validation, requête, GPX Objectifs Utiliser une application VBA Excel pour tirer parti des fichiers utilisant le format d’échange de données XML.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après : 1 .       2 .       3 .     4

Type de données attendu par l’argument FileFormat pour enregistrer un classeur Excel au format XML : a. XML b. xlXML c. xlXMLSpreadsheet Objet représentant un mappage XML ajouté à un classeur : a. XmlMap b. WebOptions c. Map Méthode pour importer des données XML pour une variable de type String : a. Import b. XmlImport Méthode pour ouvrir un fichier XML dans Excel :

.       5 .       6 .      

a. XMLOpen b. OpenXML c. Open FileFormat:=XML Nom d’objet pour un document XML : a. XMLDocument b. DocumentXML c. DOMDocument60 Méthode pour charger un fichier XML en mémoire : a. b. c.

Load LoadXML XMLLoad

Énoncé 19.1 Enregistrer un classeur Excel au format XML Durée estimative : 10 minutes Fichier Exemple.xlsx

Créez la procédure EnregistreEnXML qui enregistre le classeur Exemple.xlsx au format XML. Le document enregistré est nommé Classeur Exemple.xml. Le classeur Exemple.xlsx se trouve dans le dossier Documents XML.

Énoncé 19.2 Importer des données au format XML Durée estimative : 15 minutes Fichier Clients.xml

Utilisez le fichier Clients.xml placé dans le sous-dossier \Ressources\Documents XML\. Créez la procédure ImportDonnéesXML qui réalise l’importation des données de ce fichier dans la feuille de calcul Données XML Importées.

Note Avant l’importation, il faut supprimer tous les mappages et toutes les listes concernés du classeur. 

Énoncé 19.3 Exporter des données Excel au format XML Durée estimative : 10 minutes À la liste précédemment importée, ajoutez le client suivant : RL01, BOUVET, Annick Créez la procédure ExportXLSenXML qui exporte cette liste modifiée au format XML dans le dossier Documents XML. Le document exporté est nommé ClientsExport.xml.

Énoncé 19.4 Ouvrir un fichier XML dans Excel Microsoft 365 Durée estimative : 5 minutes Créez la procédure OuvreXML qui ouvre directement dans Excel Microsoft 365 un fichier natif XML. On prendra comme exemple le fichier RANDO.gpx présent dans le dossier Documents XML.

Énoncé 19.5 Créer un fichier GPX Durée estimative : 45 minutes GPX (GPS eXchange Format) est le format de référence de fichiers cartographiques utilisé dans les outils tels que Google Earth ou Google Maps. Le but de l’exercice est de créer directement à l’aide de la procédure CréeGPX un fichier texte, dont le nom sera choisi par l’utilisateur, tel que le montre le fichier Exemple.gpx fourni dans le dossier Documents XML.

On utilisera comme données tout ou partie du fichier Excel tel qu’il a été créé à l’exercice 19.4, ces données étant placées dans la feuille Données GPX du classeur. Indices Utilisez la bibliothèque d’objets Microsoft Scripting Runtime, à référencer dans VBA par Outils - Références, pour créer dynamiquement un fichier texte sur le modèle suivant :

Les contenus des balises et attributs , lat (latitude en degrés décimaux), lon (longitude en degrés décimaux), (altitude) et (date et heure) sont à la discrétion du programmeur. Ces données seront prises dans des cellules Excel d’une page dédiée Les balises , et (précisions) seront systématiquement valorisées à 1.0.

Énoncé 19.6 Lire directement un fichier XML Durée estimative : 15 minutes Créez la procédure NomRando qui lit la valeur renseignée dans la balise contenue dans la balise elle-même contenue dans la balise . On utilisera les données du fichier RANDO.gpx. Indice Utilisez la bibliothèque d’objets Microsoft XML V6.0, à référencer dans VBA par Outils - Références, pour utiliser les objets XML.

Énoncé 19.7 Effectuer une requête sur un fichier XML Durée estimative : 20 minutes Créez la procédure SélectionneAltitude qui filtre les points d’altitude supérieure à une hauteur saisie par l’utilisateur. On utilisera les données du fichier RANDO.gpx. Les résultats seront inscrits dans la feuille Altitudes.

Prérequis 1. 2. 3. 4. 5. 6.

c. a. b. b. c. a.

Corrigé 19.1 Enregistrer un classeur Excel au format XML Option Explicit  Private strChemin As String  -----------------------------------------------------------------  Sub Chemin()    strChemin = ActiveWorkbook.Path  End Sub  -----------------------------------------------------------------  Sub EnregistrerEnXML()   Dim strCheminRessources As String    Call Chemin    strCheminRessources = strChemin & _    "\Documents XML\"    ' ouverture du classeur à enregistrer au format XML    Workbooks.Open Filename:=strCheminRessources _    & "Exemple.xlsx"    ActiveWorkbook.SaveAs Filename:=strCheminRessources & _    "Classeur Exemple.xml", FileFormat:=xlXMLSpreadsheet    ActiveWorkbook.Close savechanges:=False    MsgBox "Classeur Exemple.xlsx enregistré en Classeur Exemple.xml"  End Sub

Corrigé 19.2 Importer des données au format XML Suppression des mappages et des listes concernés avant importation : Sub                        

SupprimeMapEtListeXML()   Dim MapXML As XmlMap  Dim lo As ListObject    ' Supprime tous les mappages du classeur    ' et toutes les listes de la feuille Données XML Importées    For Each MapXML In ActiveWorkbook.XmlMaps      MapXML.Delete 

                      End

  Next MapXML    For Each lo In ActiveWorkbook. _    Worksheets("Données XML Importées").ListObjects      lo.Delete    Next lo  Sub

Importation des données XML : Sub                                                 End

ImportDonnéesXML()   Dim MapXML As XmlMap  Dim strCheminRessources As String  Call Chemin  strCheminRessources = strChemin & _  "\Documents XML\"  Call SupprimeMapEtListeXML  ActiveWorkbook.XmlImport _  URL:=strCheminRessources & "Clients.xml", _  ImportMap:=MapXML, Overwrite:=True, _  Destination:=Worksheets("Données XML Importées").Range("A1")  MapXML.Name = "Clients"  MsgBox "Données du fichier clients.xml importées"  Sub

Corrigé 19.3 Exporter des données Excel au format XML Sub                                 End

ExportXLSenXML()   Dim strCheminRessources As String  Call Chemin  strCheminRessources = strChemin & _  "\Documents XML\"  ActiveWorkbook.SaveAsXMLData _  Filename:=strCheminRessources & "\ClientsExport.xml", _  Map:=ActiveWorkbook.XmlMaps("Clients")  MsgBox "Données du mappage Clients exportées"  Sub

Corrigé 19.4 Ouvrir un fichier XML dans Excel Microsoft 365 Sub                              

OuvreXML()  Dim strCheminRessources As String  Application.DisplayAlerts = False  Call Chemin  strCheminRessources = strChemin & "\Documents XML\"  Workbooks.OpenXML _  Filename:=strCheminRessources & "RANDO.gpx" _      , LoadOption:=xlXmlLoadImportToList 

End Sub

Corrigé 19.5 Créer un fichier GPX Sub CréeGPX()      Dim strCheminRessources As String      Dim fso As Scripting.FileSystemObject      Dim strFichierGPX As String      Dim fluxTexte As Scripting.TextStream      Dim intLigne As Integer      Dim intNbLignes As Integer      Dim f As Worksheet      Call Chemin      strCheminRessources = strChemin & "\Documents XML\"      strFichierGPX = Application.InputBox("Entrez le nom du fichier GPX _      à créer" & vbCrLf & "SANS l'extension .gpx", 2)      Set f = Worksheets("Données GPX")      Set fso = New FileSystemObject      Set fluxTexte = fso.OpenTextFile(Filename:=strCheminRessources & _      strFichierGPX & ".gpx", IOMode:=ForWriting, Create:=True)      With fluxTexte          intNbLignes = Worksheets("Données GPX").Range("A1").CurrentRegion.Rows.Count          .WriteLine ""          .WriteLine ""          .WriteLine ""          .WriteLine "Saint-Herblain le 15/11/2018"          .WriteLine ""          .WriteLine ""          .WriteLine "Sequence 1"          .WriteLine ""          For intLigne = 2 To intNbLignes              .WriteLine ""              .WriteLine "" & Replace(f.Cells(intLigne, 8).Value, _              ",",".") & ""              .WriteLine "" & f.Cells(intLigne, 9).Value & ""              .WriteLine "1.0"              .WriteLine "1.0"              .WriteLine "1.0"              .WriteLine ""          Next intLigne          .WriteLine ""          .WriteLine ""          .WriteLine ""          .Close      End With        MsgBox "Création de " & strFichierGPX & ".gpx terminée..."      Set fso = Nothing  End Sub

Corrigé 19.6 Lire directement un fichier XML Sub                                                                         End

NomRando()  Dim strNomRando As String  Dim objetXML As MSXML2.DOMDocument60  Dim strCheminRessources As String  Dim noeudname As MSXML2.IXMLDOMElement  Dim blnOK As Boolean  Call Chemin  strCheminRessources = strChemin & _  "\Documents XML\"  Set objetXML = New MSXML2.DOMDocument60  blnOK = objetXML.Load(strCheminRessources & "RANDO.gpx")  If blnOK Then      Set noeudname = objetXML.ChildNodes(1).ChildNodes(0).ChildNodes(0)      MsgBox "Référence de la rando : " & noeudname.Text  Else      MsgBox "Impossible de charger de document xml RANDO.gpx"  End If  Set objetXML = Nothing  Sub

Corrigé 19.7 Effectuer une requête sur un fichier XML Sub                                                                                                          

SélectionneAltitude()  Dim dblAltitudeMini As Double  Dim dblAltitude As Double  Dim point As MSXML2.IXMLDOMElement  Dim tracksegment As MSXML2.IXMLDOMElement  Dim blnOK As Boolean  Dim objetXML As MSXML2.DOMDocument60  Dim strCheminRessources As String  Dim intNbPoints As Integer  Dim intNoPoint As Integer  Dim intL As Integer  Call Chemin  strCheminRessources = strChemin & _  "\Documents XML\"  dblAltitudeMini = Application.InputBox("Choix de l'altitude minimum", 1)  Set objetXML = New MSXML2.DOMDocument60  blnOK = objetXML.Load(strCheminRessources & "RANDO.gpx")  If blnOK Then      Set tracksegment = objetXML.ChildNodes(1).ChildNodes(1).ChildNodes(2)      intNbPoints = tracksegment.ChildNodes.Length      With Worksheets("altitudes")          .Range("A1").CurrentRegion.Offset(1, 0).Clear          intL = 2          For intNoPoint = 1 To intNbPoints              dblAltitude = CDbl(tracksegment.ChildNodes(intNoPoint - _              1).ChildNodes(0).Text)              If dblAltitude >= dblAltitudeMini Then 

                                                                  End

                .Cells(intL, 1).Value = _                 tracksegment.ChildNodes(intNoPoint - 1).Attributes(0).Text                  .Cells(intL, 2).Value = _                  tracksegment.ChildNodes(intNoPoint - 1).Attributes(1).Text                  .Cells(intL, 3).Value = dblAltitude                  .Cells(intL, 4).Value = _                  tracksegment.ChildNodes(intNoPoint - 1).ChildNodes(1).Text                  intL = intL + 1              End If          Next intNoPoint      End With      MsgBox "Fin de sélection"  Else      MsgBox "Impossible de charger de document XML RANDO.gpx"  End If  Set objetXML = Nothing  Sub

Introduction Durée : 1 heure 40 Mots-clés API, système, Lib, DLL Objectifs Utiliser des API Windows dans une application VBA Excel Microsoft 365 pour accéder aux fonctionnalités les plus fines du système d’exploitation.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après : 1 .       2 .  

La déclaration d’une API Windows se fait avec l’instruction : a. Declare b. Call c. Shell L’argument Lib permet d’indiquer : a. la procédure ou la fonction employée dans VBA.

    3 .    

b. le type de données de la valeur renvoyée par une procédure Function. c. la bibliothèque de liaisons dynamiques (DLL). Le nom d’une fonction API disposant d’un alias peut-il être choisi par l’utilisateur ? a. Oui b. Non

Les déclarations des fonctions API sont disponibles en téléchargement sous le nom de fichier : Win32API_PtrSafe.TXT

Énoncé 20.1 Générer un son bref (bip) Durée estimative : 10 minutes Fermez tous les classeurs ouverts. Ouvrez un nouveau classeur. Créez une procédure événementielle faisant en sorte qu’un bip soit émis chaque fois que l’on clique sur une cellule de la feuille Feuil1.

Énoncé 20.2 Connaître les types des unités logiques Durée estimative : 15 minutes Créez la procédure Type_Unités_Logiques qui précise le type de toutes les unités logiques disponibles.

Énoncé 20.3 Masquer la croix de fermeture des formulaires personnalisés Durée estimative : 30 minutes Formulaire frmPageMotdePasse

Vous disposez du formulaire frmPageMotdePasse ci-après. Ce formulaire est situé dans le classeur Système.xlsm.

Créez la procédure événementielle qui masque la croix de fermeture et empêche de déplacer le formulaire.

Énoncé 20.4 Calculer le temps d’exécution d’une procédure Durée estimative : 20 minutes Procédures Sub RemplitUne_a_Une et Sub Remplit_Global.

Complétez ces deux procédures de façon à afficher pour chacune d’entre elles le temps d’exécution.  Ces procédures sont situées dans le classeur Système.xlsm.

Énoncé 20.5 Récupérer le dossier temporaire et le dossier système Windows Durée estimative : 10 minutes Créez une procédure Dossiers_système qui affiche le dossier temporaire et le dossier système Windows.

Énoncé 20.6 Lister l’ensemble des fenêtres Windows ouvertes Durée estimative : 15 minutes Créez une procédure Liste_fenêtres qui inscrit dans une feuille Excel vierge et active : 

Dans la 1ère colonne le handle de la fenêtre.



Dans la 2ème colonne le titre de cette fenêtre.

Le résultat obtenu pourra comporter plusieurs centaines de lignes et ressemblera à la capture ci-dessous :

Indice On utilisera les fonctions API EnumWindows, GetWindowText et GetWindowTextLength.

Prérequis 1. 2. 3.

a. c. a.

Corrigé 20.1 Générer un son bref (bip) Dans la partie déclarations du module de la feuille Feuil1 : private Declare PtrSafe Function Beep Lib "kernel32" (ByVal dwFreq As Long, _  ByVal dwDuration As Long) As Long

Commentaire La déclaration peut aussi être mise dans un module standard. Il faut alors la déclarer publique pour qu’elle puisse être atteinte par la procédure événementielle de la feuille Feuil1. Dans la partie code de Feuil1 : Private Sub Worksheet_SelectionChange(ByVal Target As Range)        Beep dwFreq:=1000, dwDuration:=500   End Sub

Corrigé 20.2 Connaître les types des unités logiques Dans la partie déclarations du module standard : Private Declare Function GetLogicalDrives Lib "kernel32" () As Long  Private Declare Function GetDriveType Lib "kernel32" Alias "GetDriveTypeA" (ByVal nDrive As String) As Long

Code de la procédure : Sub Types_Unités_Logiques()      Dim lngMasqueUnités As Long      Dim strUnités As String      Dim strUneUnité As String      Dim intNoUnité As Integer      Dim strtype As String        lngMasqueUnités = GetLogicalDrives      strUnités = "Unités logiques:" & vbCrLf        For intNoUnité = 0 To 25          If (lngMasqueUnités And 2 ^ intNoUnité) 0 Then              strUneUnité = VBA.Chr$(65 + intNoUnité)              ' Détermination du type d'unité              '-------------------------------------------              Select Case GetDriveType(strUneUnité & ":\") 

                Case 2                      strtype = "disque amovible"                  Case 3                      strtype = "Disque fixe"                  Case 4                      strtype = "Disque réseau"                  Case 5                      strtype = "Cd-Rom"                  Case 6                      strtype = "Disque Mémoire"                  Case Else                      strtype = "Type inconnu"              End Select                strUnités = strUnités & strUneUnité & " : " & _                  strtype & vbCrLf          End If      Next intNoUnité      MsgBox strUnités  End Sub

Corrigé 20.3 Masquer la croix de fermeture des formulaires personnalisés Dans la partie déclarations du formulaire frmPageMotdePasse : Private Declare PtrSafe Function GetWindowLongA Lib "user32" _  (ByVal hwnd As LongPtr, ByVal nIndex As Long) As LongPtr    Private Declare PtrSafe Function SetWindowLongA Lib "user32" (ByVal hwnd As _  LongPtr, ByVal nIndex As Long, ByVal dwNewLong As LongPtr) As LongPtr    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _  (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr

Commentaire Les trois déclarations ci-dessus sont obligatoires. Dans la partie code du formulaire : Private Sub UserForm_Initialize()         Dim hwnd As LongPtr      Dim lngStyle As LongPtr      '========================================================      ' La fonction FindWindow récupère le handle (entier long)      ' de la UserForm, repérée par son intitulé (Me.Caption)      '========================================================      hwnd = FindWindow("Thunder" & IIf(Application.Version Like "8*", _      "X", "D") & "Frame", Me.Caption)        '==================================================================      ' La fonction GetWindowLongA récupère le "style" (entier long) 

    ' de la fenêtre contenant la UserForm, repérée par son handle (hwnd)      '==================================================================      lngStyle = GetWindowLongA(hwnd, -16)        '============================================================      ' La fonction SetWindowLongA modifie le "style" (entier long)      ' c'est-à-dire supprime la croix de la fenêtre      ' contenant la UserForm, repérée par son handle (hwnd)      '========================================================      SetWindowLongA hwnd, -16, lngStyle And &HFFF7FFFF  End Sub   -----------------------------------------------   Private Sub cmdEntrer_Click()       If txtPassword = "secret" Then         Unload Me         Worksheets("feuil2").Visible = True         Worksheets("feuil2").Activate       Else         Beep         MsgBox ("Mot de passe incorrect"), vbExclamation         Label1 = "Ressaisissez votre mot de passe"         txtPassword.SetFocus       End If     End Sub   ----------------------------------------------   Private Sub cmdQuitter_Click()       Unload Me   End Sub

Corrigé 20.4 Calculer le temps d’exécution d’une procédure Dans la partie déclarations du formulaire : Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long

Code des procédures : Sub                                         End Sub                

RemplitUne_a_Une()  ' Remplir une à une les cellules A1 à A10000  Dim une_cellule As Range  Dim topDépart As Long  Dim topFin As Long  topDépart = GetTickCount  For Each une_cellule In Range("A1:A10000").Cells      une_cellule.Value = "Editions ENI"  Next une_cellule  topFin = GetTickCount64  MsgBox "durée remplissage une à une : " & (topFin - topDépart) / 1000 & " sec."  Sub  Remplit_Global()  ' Remplir globalement les cellules A1 à A10000  Dim topDépart As Long  Dim topFin As Long  topDépart = GetTickCount 

              End

Range("A1:A10000").Value = "Editions ENI"  topFin = GetTickCount  MsgBox "durée remplissage global : " & (topFin - topDépart) / 1000 & " sec."  Sub

Corrigé 20.5 Récupérer le dossier temporaire et le dossier système Windows Dans la partie déclarations du formulaire : Declare PtrSafe Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _  (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long    Declare PtrSafe Function GetSystemDirectory Lib "kernel32" Alias _  "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long

Code de la procédure Dossiers_système : Sub                                             End

Dossiers_système()  Dim strTEMP As String  Dim strSystème As String  strTEMP = Space$(250)  GetTempPath 251, strTEMP  strTEMP = Left(strTEMP, InStr(strTEMP, Chr(0)) - 1)  strSystème = Space$(250)  GetSystemDirectory strSystème, 251  strSystème = Split(strSystème, Chr(0))(0)  MsgBox "Dossier temporaire : " & strTEMP & vbCrLf & _  "Dossier système : " & strSystème  Sub

Corrigé 20.6 Lister l’ensemble des fenêtres Windows ouvertes Dans la partie déclarations du module, on place les déclarations suivantes : ' Déclaration des API utilisées pour le listage des fenêtres  Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String,  ByVal cch As Long) As Long  Private Declare Function EnumWindows Lib "user32" (ByVal lpEnumFunc  As Long, ByVal lParam As Long) As Long 

Private Declare Function GetWindowTextLength Lib "user32.dll"  Alias "GetWindowTextLengthA" (ByVal hwnd As Long) As Long  Private intLigne As Integer 

Code de la procédure Liste_fenêtres et de la fonction associée : Sub Liste_fenêtres()      intLigne = 1      Call EnumWindows(AddressOf EnumérationFenêtres, 0)      MsgBox "Fin d'énumération"  End Sub    Function EnumérationFenêtres(ByVal lngHandle As Long, ByVal lngParamètres As Long) As Long      Dim strTitre  As String      Dim lngLongueurTitre As Long        ' Valeur du handle de la fenêtre      ActiveSheet.Cells(intLigne, 1).Value = lngHandle        ' Détermination du titre      strTitre = VBA.Space(GetWindowTextLength(lngHandle) + 1)      lngLongueurTitre = GetWindowText(lngHandle, strTitre,  VBA.Len(strTitre))      strTitre = VBA.Left(strTitre, lngLongueurTitre)            ActiveSheet.Cells(intLigne, 2).Value = strTitre      intLigne = intLigne + 1      ' Résultat de la fonction forcé à True    ' pour continuer l'énumération      EnumérationFenêtres = True  End Function

Introduction Durée : 1 heure 10 Mots-clés fonctions matricielles, tableau croisé dynamique, fonctions Excel, doublon, étiquette, champ Objectifs Combiner la puissance du code VBA et les possibilités de l’application Excel (feuilles de calcul, fonctions matricielles) pour effectuer des recherches dans des plages de données et analyser les données à partir des tableaux croisés dynamiques.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après : 1 .     2 .     3 .     4 .     5 .       6 .    

Propriété qui permet d’utiliser les fonctions Excel en français dans le code VBA : a. Formula b. FormulaLocal La propriété Address de l’objet Range renvoie une valeur de type : a. Integer b. String PivotTables représente la collection des : a. tableaux croisés dynamiques. b. objets contenus dans un tableau croisé dynamique. Name est une propriété de : a. PivotTables b. PivotTable Présentation d’un champ d’un tableau croisé dynamique en tant qu’étiquette de colonne : a. ColumnField b. xlColumnField c. ColumnHeads Méthode pour ajouter un champ de données à un tableau croisé dynamique : a. AddDataField b. AddField

Énoncé 21.1 Rechercher une information dans une plage de données

Durée estimative : 5 minutes Classeur Exemple.xlsm

Sélectionnez la feuille Recherche du classeur. Au bouton Simple, attachez une procédure RechercheSimple qui écrit dans une cellule la formule Excel permettant de reporter le nom du mois correspondant à la vente maximale.

Indice Différentes fonctions Excel sont disponibles pour effectuer une recherche. Vous pouvez par exemple faire appel aux fonctions INDEX et EQUIV. Utilisez la propriété FormulaLocal pour pouvoir intégrer les fonctions Excel dans le code VBA. Revoyez si nécessaire le chapitre « Échanges de fonctions entre Excel et VBA ».

Énoncé 21.2 Rechercher une valeur avec doublons dans une plage de données Durée estimative : 15 minutes Classeur Exemple.xlsm

Saisissez la valeur 88000 pour janvier et testez à nouveau votre procédure RechercheSimple. Vous constatez que le mois de juillet est cette fois-ci ignoré. L’exercice précédent ne permet pas de traiter le cas des doublons. Écrivez une nouvelle procédure RechercheElaborée qui reporte pour le tableau ci-après tous les mois et leur point de vente dont le total est égal au maximum. Faites aussi apparaître les adresses des cellules concernées.

Indice Utilisez la propriété Address et les fonctions de manipulation de chaînes de VBA.

Énoncé 21.3 Établir le chiffre d’affaires par représentant et par pays Durée estimative : 15 minutes Classeur Exemple.xlsm, formulaire frmCommandes.

Vous disposez dans la feuille Commandes d’un tableau (5 colonnes par 800 lignes) regroupant les commandes réalisées sur trois ans. Écrivez les procédures permettant d’afficher dans le formulaire le total en fonction du choix du représentant et du pays.

Indice Dans votre code VBA, utilisez la fonction Excel SOMMEPROD.

Énoncé 21.4 Créer un tableau croisé dynamique Durée estimative : 15 minutes Classeur Exemple.xlsm

Nous désirons proposer un tableau croisé dynamique personnalisé à partir des données de la feuille Commandes. Créez la procédure CreeTCD qui propose le TCD minimaliste suivant :

Votre procédure devra comporter un appel à la procédure EffaceTCD, chargée d’effacer auparavant les cellules de la feuille comportant un éventuel tableau croisé dynamique. Elle devra, en outre, présenter la boîte de choix ci-après :

Énoncé 21.5 Gérer les étiquettes Durée estimative : 10 minutes Créez la procédure Pays qui ajoute au TCD le champ Pays en tant qu’étiquette de colonne. Votre procédure devra comporter un appel à la fonction ExisteTCD, chargée de vérifier auparavant la présence effective du TCD.

Énoncé 21.6 Gérer les champs de valeurs Durée estimative : 5 minutes Créez la procédure AjoutMontant qui ajoute au TCD le champ Montant de la commande en tant que champ de valeur, à partir duquel est effectuée la somme des ventes par représentant et par pays.

Énoncé 21.7 Mettre à jour un tableau croisé dynamique Durée estimative : 5 minutes Créez la procédure MAJ_Stats qui met à jour le tableau croisé dynamique

Prérequis 1. 2. 3. 4. 5. 6.

b. b. a. b. b. a.

Corrigé 21.1 Rechercher une information dans une plage de données Sub RechercheSimple()     Worksheets("Recherche").Range("C20").FormulaLocal = _     "=INDEX(B4:C15;EQUIV(C18;C4:C15;0);1)"    End Sub

Corrigé 21.2 Rechercher une valeur avec doublons dans une plage de données Sub RechercheElaborée()     Dim dblMax As Double     Dim rngCellule As Range     Dim intLigne As Integer  

  dblMax = Range("C18").Value     intLigne = 4     With Worksheets("Recherche")       For Each rngCellule In Worksheets("Recherche").Range("B4:E15")         If rngCellule.Value = dblMax Then           .Cells(intLigne, 7).Value = rngCellule.Address           .Cells(intLigne, 8).Value = .Cells(3, rngCellule.Column).Value          .Cells(intLigne, 9).Value = .Cells(rngCellule.Row, 2).Value           intLigne = intLigne + 1         End If       Next     End With   End Sub

Corrigé 21.3 Établir le chiffre d’affaires par représentant et par pays À mettre dans la zone de code du formulaire : Private Sub UserForm_Initialize()     With Me.lstReprésentants       .ColumnHeads = True       .RowSource = "J10:J17"       .ListIndex = 0     End With     With Me.lstPays       .ColumnHeads = True       .RowSource = "L10:L11"       .ListIndex = 0     End With     Range("G13").Value = Me.lstPays.Text     Range("G12").Value = Me.lstReprésentants.Text     Call Calcul   End Sub   -------------------------------------------------------------   Public Sub Calcul()     Range("G14").FormulaLocal = _     "=SOMMEPROD((A2:A798=G13)*(C2:C798)*(B2:B798=G12)*(E2:E798))"     Me.lblTotal.Caption = Format(Range("G14").Value, "##,##0.00")    End Sub   -------------------------------------------------------------   Private Sub lstPays_MouseUp(ByVal Button As Integer, _   ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)     Range("G13").Value = Me.lstPays.Text     Call Calcul   End Sub   ---------------------------------------------------------------   Private Sub lstReprésentants_MouseUp(ByVal Button As Integer, _   ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)     Range("G12").Value = Me.lstReprésentants.Text     Call Calcul   End Sub

Corrigé 21.4 Créer un tableau croisé dynamique ' On décide de mettre un seul TCD sur la feuille   Sub EffaceTCD()       Worksheets("Analyse").Cells.Clear   End Sub   --------------------------------------------------------------   ' Création du TCD   Sub CreeTCD()     Dim intChoix As Integer     intChoix = MsgBox("Attention !!! Si le TCD existe déjà," _     & " il sera supprimé" & vbCrLf & _     "Voulez-vous vraiment poursuivre ?", _     vbYesNo, "Création du TCD")     If intChoix = vbYes Then       Call EffaceTCD       On Error GoTo sortie       ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _      Worksheets("Commandes").Range("A1").CurrentRegion, _       Version:=xlPivotTableVersion15).CreatePivotTable _       TableDestination:=Worksheets("Analyse").Range("A1"), _       TableName:="TCD", DefaultVersion:=xlPivotTableVersion15       With Worksheets("Analyse").PivotTables("TCD"). _           PivotFields("Représentant")           .Orientation = xlRowField           .Position = 1       End With       ActiveWorkbook.ShowPivotTableFieldList = False       MsgBox "Création du TCD réalisée"     Else       MsgBox "Opération annulée."     End If     Exit Sub   sortie:     MsgBox "Le TCD existe déjà !"   End Sub

Corrigé 21.5 Gérer les étiquettes ' Vérification de la présence du TCD   Function ExisteTCD() As Boolean     On Error GoTo sortie     If Worksheets("Analyse"). _       PivotTables("TCD") _       .Name = "TCD" Then       ExisteTCD = True     End If     Exit Function   sortie:     ExisteTCD = False   End Function   ------------------------------------------------------------   ' Ajout du champ Pays au TCD   Sub Pays()     If ExisteTCD Then       With Worksheets("Analyse"). _       PivotTables("TCD").PivotFields("Pays")  

        .Orientation = xlColumnField           .Position = 1      End With     Else       MsgBox "Créer d'abord le TCD !"     End If   End Sub

Corrigé 21.6 Gérer les champs de valeurs Sub AjoutMontant()     On Error GoTo Sortie_Sur_Erreur     Worksheets("Analyse").PivotTables("TCD").AddDataField _     Worksheets("Analyse").PivotTables("TCD"). _     PivotFields("Montant de la commande"), _     "Somme de Montant de la commande", xlSum     Exit Sub   Sortie_Sur_Erreur:     MsgBox "Le champ ""Montant de la commande"" a déjà été ajouté au TCD"  End Sub

Corrigé 21.7 Mettre à jour un tableau croisé dynamique Sub MAJ_Stats()     Worksheets("Analyse").PivotTables("TCD"). _     PivotCache.Refresh    End Sub

Introduction Mots-clés Web, requêtes, filtre, Access, Excel, CSV Objectifs Utiliser le code VBA pour augmenter la performance de l’outil Power Query.

Prérequis Pour valider les prérequis nécessaires, avant d’aborder le TP, répondez aux questions ci-après : 1.       2.       3.       4.      

L’ensemble des requêtes d’un classeur Excel est : a. La collection Queries b. La collection WorkBookQueries c. La collection QueryTables Une requête est un objet de classe : a. Query b. QueryTable c. WorkBookQuery Le résultat visuel d’une requête est un objet de classe : a. ListObject b. QueryTable c. ListView Power Query spécifie ses requêtes : a. par le Power Query Formula Language. b. par le langage SQL classique. c. par paramétrage des options d’un Éditeur avancé.

Note importante Power Query est intégré à Microsoft 365.

Énoncé 22.1 Créer une requête simple sur une page web Durée estimative : 20 minutes Créez la procédure Tout_CAC40 qui récupère dans la cellule active le contenu d’une partie de page web correspondant au tableau instantané des valeurs du CAC40. L’adresse de la page web est la suivante : https://fr.finance.yahoo.com/q/cp? s=^FCHI Le résultat obtenu aura l’aspect du tableau suivant :

Indice Utiliser Power Query en passant par la commande Données - À partir du Web.

Énoncé 22.2 Créer une requête avec filtre sur une page web Durée estimative : 15 minutes Créez la procédure CréeRequêteCAC_40 similaire à la procédure précédente Tout_CAC40 mais qui sélectionne les valeurs dont le volume est supérieur ou égal à une valeur choisie par l’utilisateur en fonction des résultats obtenus dans l’exercice précédent. Dans l’exemple ci-dessous, le volume choisi est de 800 000.

Indice Nommez la requête CAC40 3M.

Énoncé 22.3 Généraliser une requête avec filtre sur une page web Durée estimative : 5 minutes Créez la procédure FiltrageRequêteCAC40 qui permet à l’utilisateur de choisir luimême le volume minimum du filtre à appliquer.

Indice On réutilisera la requête TableCACWeb de l’exercice précédent. On veillera donc à ne pas appeler la procédure effaceRequetes.

Énoncé 22.4 Créer une requête Power Query sur une base Access Base de données Northwind 2007.accdb

Durée estimative : 20 minutes Créez la procédure Requête_Access qui extrait l’ensemble des informations de la table Clients de la base de données Access Northwind 2007.accdb. Cette base de données est située dans le sous-dossier Ressources du dossier Chapitre 22. Nommez cette requête Clients_Access et implantez le résultat sur une feuille Excel intitulée Clients.

Énoncé 22.5 Créer une requête Power Query sur une feuille Excel Classeur Excel Commandes.xlsx

Durée estimative : 20 minutes Créez la procédure Requête_Excel qui extrait l’ensemble des informations de la 1ère feuille du classeur Excel Commandes.xlsx. Ce classeur est situé dans le sous-dossier Ressources du dossier Chapitre 22. Nommez cette requête CommandesExcel et implantez le résultat sur une feuille Excel intitulée Commandes.

Énoncé 22.6 Créer une requête Power Query sur un fichier texte CSV Fichier Commandes.csv

Durée estimative : 20 minutes Créez la procédure Requête_CSV qui extrait les commandes de l’année 2020 présentes dans le fichier texte Commandes.csv. Ce fichier est situé dans le sousdossier Ressources du dossier Chapitre 22. Nommez cette requête CommandesCSV et implantez le résultat sur une feuille Excel intitulée Commandes2020.

Prérequis 1. 2. 3. 4.

a. c. a. a.

Corrigé 22.1 Créer une requête simple sur une page web Sub Tout_CAC40()  '   22.1 Récupérer un tableau du CAC40      Call effaceRequêtes        ActiveWorkbook.Queries.Add Name:="CAC40", Formula:= _          "let Source = Web.Page(Web.Contents(""https://fr.finance.yahoo.com/ q/cp?s=^FCHI""))," & _          " Data0 = Source{0}[Data] , #""Type modifié"" = Table.TransformColumnTypes(Data0,{{""Symbole"", type text}, {""Nom de la société"", type text}," & _          "{""Dernier prix"", type number}, {""Variation"", type number},

{""% de variation"", Percentage.Type}, {""Volume"", Int64.Type}}) in #""Type modifié"""        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _          "OLEDB;Provider=Microsoft.Mashup.OleDb.1; Data Source=$Workbook$;Location=""CAC40"";Extended Properties=""""" _          , Destination:=Range("$A$1")).QueryTable          .CommandType = xlCmdSql          .CommandText = Array("SELECT * FROM CAC40")          .ListObject.DisplayName = "TableCAC40"          .Refresh BackgroundQuery:=False      End With  End Sub     Sub effaceRequetes()      Dim intQ As Integer      On Error Resume Next      For intQ = 1 To 100          ActiveWorkbook.Queries(intQ).Delete          ActiveSheet.ListObjects(intQ).Delete      Next intQ  End Sub  -----------------------------------------------------------------

Corrigé 22.2 Créer une requête avec filtre sur une page web Sub CréeRequêteCAC_40()  ' 22.2 Créer une requête avec filtre sur une page web        Dim strFormule As String      Call effaceRequêtes        strFormule = "let Source = Web.Page(Web.Contents(""https://fr.finance. yahoo.com/q/cp?s=^FCHI""))," & _          " Data0 = Source{0}[Data] , #""Type modifié"" = Table.TransformColumnTypes(Data0,{{""Symbole"", type text}, {""Nom de la société"", type text}," & _          " {""Dernier prix"", type number}, {""Variation"", type number}, {""% de variation"", Percentage.Type}, {""Volume"", Int64.Type}})" & _          ", #""Lignes filtrées"" = Table.SelectRows(#""Type modifié"", each [Volume] > 3000000) in #""Lignes filtrées"""        ActiveWorkbook.Queries.Add Name:="CAC40_SELECT", Formula:=strFormule        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _          "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source= $Workbook$;Location=""CAC40_SELECT"";Extended Properties=""""" _          , Destination:=Range("$A$1")).QueryTable          .CommandType = xlCmdSql          .CommandText = Array("SELECT * FROM CAC40_SELECT")          .ListObject.DisplayName = "TableCAC_SELECT"          .Refresh BackgroundQuery:=False      End WithEnd Sub

Corrigé 22.3 Généraliser une requête avec filtre sur une page web Sub FiltrageRequêteCAC40()  ' 22.3 Généraliser une requête avec filtre sur une page web      Dim dblVolume As Double      Dim strFormule As String        ' Saisie du volume minimum des transactions      dblVolume = Application.InputBox(Prompt:="Entrez le volume minimum", Type:=2)        ' Mise à jour de la formule de la requête web, incluant dblVolume      strFormule = "let Source = Web.Page(Web.Contents(""https://fr.finance. yahoo.com/q/cp?s=^FCHI""))," & _  " Data0 = Source{0}[Data] , #""Type modifié"" = Table.TransformColumnTypes(Data0,{{""Symbole"", type text}, {""Nom de la société"", type text}," & _          " {""Dernier prix"", type number}, {""Variation"", type number}, {""% de variation"", Percentage.Type}, {""Volume"", Int64.Type}})" & _          ", #""Lignes filtrées"" = Table.SelectRows(#""Type modifié"", each [Volume] > " & dblVolume & ") in #""Lignes filtrées"""        ' Réaffectation de la formule à la requête TableCACWeb      ActiveWorkbook.Queries("CAC40_SELECT").Formula = strFormule        ' Réactualisation de la table Excel      ActiveSheet.ListObjects("TableCAC_SELECT").QueryTable.Refresh BackgroundQuery:=False  End Sub

Corrigé 22.4 Créer une requête Power Query sur une base Access Sub Requête_Access()   ' 22.4    Créer une requête Power Query sur une base Access      Dim qtClient As QueryTable      Dim strFormule As String      Call effaceRequêtes        strFormule = "let" & vbCrLf & _      "    Source = Access.Database(File.Contents(""" & ThisWorkbook.Path & "\Ressources\Northwind 2007.accdb""))," & vbCrLf _      & "    ListeClients = Source{[Schema="""",Item=""Clients""]}[Data]" & vbCrLf & _      "in" & vbCrLf & "    ListeClients"        ' Création de la requête Clients_Access      ActiveWorkbook.Queries.Add Name:="Clients_Access", Formula:=strFormule        ' Création de la table de requête qtClient associée à Clients_Access      Set qtClient = Worksheets("Clients").ListObjects.Add(SourceType:=0, Source:= _          "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Clients_Access" _          , Destination:=Worksheets("Clients").Range("$A$1")).QueryTable      With qtClient          .CommandType = xlCmdSql 

                End

            End Sub

.CommandText = Array("SELECT * FROM [Clients_Access]")  .ListObject.DisplayName = "ListeClientsAccess"  .Refresh BackgroundQuery:=False  With  

Corrigé 22.5 Créer une requête Power Query sur une feuille Excel Sub Requête_Excel()   ' 22.5 Requête sur une feuille Excel      ' Création d'une requête sur la 1ère feuille d'un fichier Excel Commandes.xlsx        Dim qtCommandes As QueryTable      Dim strFormule As String        strFormule = "let" & vbCrLf & _      "    Source = Excel.Workbook(File.Contents(""" & ThisWorkbook.Path & "\ Ressources\Commandes.xlsx""), null, true)," & vbCrLf & _      "    Feuil1_Sheet = Source{[Item=""Feuil1"",Kind=""Sheet""]}[Data]," & vbCrLf & _      "    #""En-têtes promus"" = Table.PromoteHeaders(Feuil1_Sheet)," & vbCrLf & _      "    #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes " & _          "promus"",{{""Idclient"", Int64.Type}, {""NoCmde"", type text}, {""DateCmde"", type date}, {""Montant"", Int64.Type}})" & vbCrLf & _           "in" & vbCrLf & "    #""Type modifié"""        ActiveWorkbook.Queries.Add Name:="CommandesExcel", Formula:=strFormule        Set qtCommandes = Worksheets("Commandes").ListObjects.Add(SourceType:=0,  Source:= _          "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=CommandesExcel" _          , Destination:=Worksheets("Commandes").Range("$A$1")).QueryTable        With qtCommandes          .CommandType = xlCmdSql          .CommandText = Array("SELECT * FROM [CommandesExcel]")           .ListObject.DisplayName = "ListeCommandesExcel"           .Refresh BackgroundQuery:=False      End With  End Sub

Corrigé 22.6 Créer une requête Power Query sur un fichier texte CSV Sub Requête_CSV()  ' 22.6 Requête sur un fichier texte CSV        Dim qtCommandesCSV As QueryTable      Dim strFormule As String   

    Call effaceRequêtes      strFormule = "let Source = Csv.Document(File.Contents(""" & ThisWorkbook.Path & "\Ressources\Commandes.csv"")," & _      "[Delimiter="";"", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]), #""En-têtes promus"" = Table.PromoteHeaders(Source, " & _      "[PromoteAllScalars=true]), #""Type modifié"" = Table.TransformColumnTypes" & _      "(#""En-têtes promus"",{{""Idclient"", Int64.Type}, {""NoCmde"", type text}," & _      "{""DateCmde"", type date}, {""Montant"", Int64.Type}})," & _      "#""Lignes filtrées"" = Table.SelectRows(#""Type modifié"", each [DateCmde] >= #date(2020, 1, 1) and [DateCmde]