Macros-commandes VBA/Création de Graphique
Définitions
[modifier | modifier le wikicode]Qu’est-ce qu’un graphique ?
[modifier | modifier le wikicode]Un graphique permet de représenter des données chiffrées pour qu’elles soient plus lisibles. Il s'agit d'une "représentation visuelle".
Ainsi, un graphique permet de mieux comprendre les chiffres et de mieux les analyser. Cela permet également d'expliquer certains phénomènes. On voit plus facilement les relations entres plusieurs données.
Excel donne la possibilité à ses utilisateurs de créer un graphique à partir d'une base de données.
Excel permet à ses utilisateurs d’avoir un large choix dans les types de graphiques :
- Histogramme
- Courbes
- Secteurs
- Barres
- Aires
- Nuage de points
- Boursier
- Surface
- Anneau
- Bulles
- Radar
Qu’est-ce qu’une macro ?
[modifier | modifier le wikicode]Une macro ou macro-commande permet d'enregistrer une suite d'instructions écrites les unes après les autres. L'enregistrement d'une macro donne naissance à un code qui se fait en langage VBA (Visual Basic for Applications). Le langage VBA est un dérivé du langage Visual Basic, spécialement développé pour les applications du pack Office (Word, Excel, PowerPoint).
Lorsque l’on exécute la macro, le tableur va interpréter les différentes instructions sauvegardées dans le code de la macro à exécuter. Cette exécution permettra de reproduire l'intégralité des commandes enregistrées. Chaque étape sera répétée à l'identique à chaque exécution de la macro.
La macro a pour finalité d'automatiser des tâches afin de les rendre beaucoup plus rapides que si elles avaient été effectuées manuellement.
Dans le cadre d'un projet d'entreprise, elles peuvent aussi avoir un intérêt lors de tests répétitifs de contrôle de qualité des données et/ou de tests de non-régression.
Son rôle est aussi de simplifier la vie de l'utilisateur. La macro est souvent utilisée lorsqu'une tâche est répétitive, ce qui permet à l'utilisateur de travailler beaucoup plus rapidement et efficacement sans erreurs manuelles.
Comment créer un graphique à partir d’un tableau créé manuellement ?
[modifier | modifier le wikicode]Créer une base de données
[modifier | modifier le wikicode]Pour créer un graphique, il faut dans un premier temps créer une base de données dans un tableur pouvant faire appel aux graphiques.
Cette base de données devra alors contenir des titres en colonnes et/ou en lignes, puis devra être alimentée selon les besoins de l’utilisateur qui crée le tableau.
Les titres présents en colonnes ou en lignes sont utilisés afin d’avoir une légende claire et précise.
Prenons l'exemple d'une entreprise X, admettons que celle-ci souhaite étudier l'évolution de son chiffre d'affaires sur 5 ans.
Elle va alors faire un tableau de ce type sur Excel :
Création manuelle du graphique
[modifier | modifier le wikicode]Après avoir créé une base de données, il devient alors facile de lui associer un graphique. Pour ce faire, il suffit de :
- Sélectionner la base de données
- Cliquer sur la rubrique "Insertion"
- Cliquer sur "Graphiques"
- Choisir le type de graphique le plus adapté
Lors de la sélection des données, il est conseillé de sélectionner la totalité des colonnes (jusqu'en bas de la feuille). Cela évite de devoir redéfinir la source du graphique à chaque nouvelle saisie dans la base de données.
Configurer le graphique
[modifier | modifier le wikicode]Pour configurer le graphique, il suffit d'aller dans les différentes rubriques :
- Création : elle a pour but de modifier le type de graphique ; de changer la source des données ; de configurer la disposition du graphique ; de modifier les couleurs du graphique etc.
- Disposition : elle a pour but d'insérer des objets ; d'ajouter des étiquettes de données ; d'ajouter des titres, etc.
- Mise en forme : elle a pour but de modifier la mise en forme
Comment créer automatiquement un graphique à l’aide d’une macro ?
[modifier | modifier le wikicode]Test avec enregistrement
[modifier | modifier le wikicode]Démarche
[modifier | modifier le wikicode]Pour automatiser la création d'un graphique, on peut utiliser la méthode de l’enregistreur.
La démarche est la suivante :
1ère étape : Création de la macro
- Activation de l’enregistreur
- Dénomination de la macro : « CREERGRAPHIQUE » ; « OK »
- Sélection des données : sélectionner les colonnes afin que cela prenne en compte toutes les lignes en cas d’ajout de données
- Insertion du graphique : « Insertion » ; « graphique »
- Choix du type de graphique
- Choix de la mise en forme
- Ajout des étiquettes de données
- Arrêt de l’enregistreur
2ème étape : Exécution de la macro
Lors de l'exécution de la macro, il peut se produire deux choses :
- Soit la macro s'exécute parfaitement
- Soit une boîte de dialogue s’ouvre pour indiquer une erreur (Annexe 2)
Dans ce cas présent, la macro s'effectue correctement.
3ème étape : Analyse de l’écriture dans Visual Basic
Il est intéressant d'analyser l'écriture Visal Basic, surtout en cas d'erreur. La méthode de l'enregistreur pour créer un graphique ne génère pas d'erreur contrairement à d'autres créations. Il n'y a donc pas besoin de retoucher l'écriture.
Lorsqu’il y a une erreur dans la macro, une boîte de dialogue apparaît pour nous l'indiquer (Annexe 2). Il est alors facile de localiser la partie du code incorrecte car celle-ci est surlignée en jaune.
En reprenant l'exemple de l'entreprise X, suite à l'enregistrement, nous obtenons l'écriture suivant sous Visual Basic :
Sub CREERGRAPHIQUE()
'
' CREERGRAPHIQUE Macro
'
Columns("A:B").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Feuil1'!$A:$B")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.ApplyLayout (3)
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ApplyDataLabels
End Sub
4ème étape : Masquer les étapes de l'exécution de la macro
Afin de ne pas voir les différentes étapes et d'améliorer les performances de la macro lors de son exécution, il est possible de les masquer (désactiver la carte vidéo de l'ordinateur) :
- Ajouter au début de la macro :
Application.ScreenUpdating=False
- Ajouter à la fin de la macro :
Application.ScreenUpdating=True
5ème étape : L'écriture finale pour créer un graphique automatiquement
En reprenant l’exemple de l’entreprise X, l'écriture finale qui apparaît sous Visual Basic est :
Sub CREERGRAPHIQUE()
'
' CREERGRAPHIQUE Macro
'
Application.ScreenUpdating=False
Columns("A:B").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Feuil1'!$A:$B")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.ApplyLayout (3)
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ApplyDataLabels
Application.ScreenUpdating=True
End Sub
6ème étape : Création d'un bouton
- Créer un bouton : Insertion d'une forme
- Ajouter un texte : clic droit ; "modifier le texte" ; exemple : "CREER UN GRAPHIQUE"
- Affecter la macro : clic droit ; "affecter une macro" ; choisir la macro "CREERGRAPHIQUE" ; "OK"
Pour pouvoir comprendre les étapes de la macro, il est possible de suivre l'écriture Visual Basic en utilisant la touche F8.
Avantages
[modifier | modifier le wikicode]Les avantages de la création d'un graphique automatique avec l'enregistreur :
- Macro générée automatiquement par l’enregistreur
- Méthode simple lorsque l’on ne connaît pas le langage VBA pour créer un graphique
- Permet de se familiariser avec le langage VBA
Inconvénients
[modifier | modifier le wikicode]Les inconvénients de la création d'un graphique automatique avec l'enregistreur :
- Méthode peu souple
- Restreint les utilisateurs
- Source d'erreur
- La macro peut seulement faire les actions que l’on a enregistré
- De ce fait, elle ne laisse pas le choix du type de graphiques
De manière générale :
- Lorsqu’il y a une erreur, la macro ne fonctionne pas du tout
- Il faut parfois revenir sur le code
- Il n’est pas évident de comprendre les erreurs de programmation
Test avec langage Visual Basic
[modifier | modifier le wikicode]Démarches
[modifier | modifier le wikicode]
Notre objectif est ici de créer un graphique via le code VBA.
Nous avons deux possibilités :
- Créer un graphique sur une nouvelle feuille (feuille de graphique)
- Créer un graphique sur notre feuille de calcul (feuille des données)
Nous allons expliquer les deux démarches ci-dessous.
Nous remplissons un tableau de base avec toutes les données.
Par exemple le tableau ci-contre concernant l'évolution des ventes de noël de 2012 à 2015 :
- 1ère possibilité : Créer un graphique sur une nouvelle feuille (feuille de graphique) :
La démarche de notre programmation est la suivante :
1. Dimensionner les variables utilisées dans le code (phase facultative mais recommandée pour limiter les erreurs)
2. Définir la plage des données sources du graphique
3. Supprimer le graphique si déjà existant afin de rafraîchir proprement notre graphique
4. Créer le graphique en indiquant le type de graphique (ici histogramme), la plage du graphique, le titre, l'intitulé du titre s'il y a et enfin le nom de la feuille qui recevra le graphique
5. Afficher la feuille où se situe le graphique (facultatif mais pratique afin de voir directement le résultat de notre graphique)
Ci-dessous le code utilisé :
Sub CreerGraphiqueNvelleFeuille()
Const sheDonnéesSource As String = "DataSource"
Const sheGraphique As String = "Graphique"
Dim chGraph As Chart
Dim rPlage As Range
' Définition de la plage des données source du graphique
Set rPlage = Sheets(sheDonnéesSource).Range("A:E")
' Suppression du graphique si déjà existant
On Error Resume Next
Sheets(sheGraphique).Delete
On Error GoTo 0
' Création du graphique
Set chGraph = Charts.Add
With chGraph
' Type histogramme
.ChartType = xlColumnClustered
' Source du graphique
.SetSourceData Source:=rPlage, PlotBy:=xlColumns
' Affichage du titre
.HasTitle = True
' Intitulé
.ChartTitle.Characters.Text = rPlage.Cells(1, 1)
' Nom de la feuille recevant le graphique
.Name = sheGraphique
End With
Sheets(sheGraphique).Select
End Sub
- 2ème possibilité : Créer un graphique sur notre feuille de calcul (feuille des données) :
La démarche de notre programmation est la suivante :
1. Dimensionner les variables utilisées dans le code
2. Dans la feuille de donnée (avec "With"), définir la plage accueillant le graphique, créer le graphique et mettre la source des données du graphique
3. Dans le graphique (avec "With"), définir le type de graphique (ici barre empilée), la source du graphique, la présence ou non de titre avec l'intitulé et enfin la position de la légende
5. Afficher la feuille où se situe le graphique
Ci-dessous le code utilisé :
Sub CreerGraphiqueFeuilleDonnees()
Const sheDonnéesSource As String = "DataSource"
Dim chGraph As Chart
Dim rPlageAcceuil As Range
Dim rPlageSource As Range
With Sheets(sheDonnéesSource)
' Plage devant accueuillir le graphique
Set rPlageAcceuil = .Range("A10:F20").Offset(0, 1)
' Création du graphique, ne pas oublier le .Chart final
' L'objet graphique se place sur la plage et à sa taille
Set chGraph = .ChartObjects.Add(rPlageAcceuil.Left, rPlageAcceuil.Top, rPlageAcceuil.Width, rPlageAcceuil.Height).Chart
' Source du graphique
Set rPlageSource = .Range("A:E")
End With
With chGraph
' Type barre empilée
.ChartType = xlBarStacked
' Source du graphique
.SetSourceData Source:=rPlageSource, PlotBy:=xlColumns
' Affichage du titre
.HasTitle = True
' Intitulé
.ChartTitle.Characters.Text = rPlageSource.Cells(1, 1)
' Légende en position haute
.Legend.Position = xlLegendPositionTop
End With
Sheets(sheDonnéesSource).Select
End Sub
Avantages
[modifier | modifier le wikicode]Les avantages de la création d'un graphique automatique avec le langage Visual Basic :
- Suppression des erreurs humaines dès lors que le programme est débogué et qu’il n'y a pas de modification de la structure des données de base
- Automatisation des tâches répétitives
- Une fois créé, c’est un gain de temps pour l'utilisateur
- Simplification de la transmission des connaissances à d'autres utilisateurs
- Choix de n’importe quel type de graphique présent dans Excel
- Harmonisation de la structure d'un graphique pour un ensemble d'utilisateurs. Le graphique "officiel" ne pourra pas prendre une autre forme que celui programmé
Inconvénients
[modifier | modifier le wikicode]Les inconvénients de la création d'un graphique automatique avec le langage Visual Basic :
- Nécessite une connaissance technique d'Excel et VBA pour développer le programme et le déboguer
- Demande un investissement en temps au départ pour mettre en oeuvre la solution finalisée
- Demande une transmission des savoirs lors des changements d'organisation du travail
Comment personnaliser son graphique via le langage VBA ?
[modifier | modifier le wikicode]Les constantes utilisées pour choisir un type de graphique
[modifier | modifier le wikicode]Lorsque nous créons un graphique il faut indiquer "ChartType= XXX"
"XXX" étant l'une des constante ci-dessous (principaux types de graphiques) :
- xlArea - Aires
- xlAreaStacked - Aires empilées
- xl3DArea - Aires 3D
- xlBarOfPie - Barres de secteurs
- xlBarStacked - Barres empilées
- xl3DBarStacked - Barres 3D empilées
- xlBubble - Bulles
- xlBubble3DEffect - Bulles 3D
- xlLine - Courbes
- xlLineMarkersStacked - Courbes empilées avec marqueurs
- xl3DLine - Courbes 3D
- xlColumnClustered - Histogramme en cluster
- xlColumnStacked - Histogramme empilé
- xl3DColumn - Histogramme 3D
- xl3DColumnClustered - Histogramme 3D en cluster
- xl3DColumnStacked - Histogramme 3D empilé
- xlXYScatter - Nuages de points
- xlXYScatterLines - Nuages de points avec courbes
- xlRadar - Radar
- xlPie - Secteurs
- xl3DPie - Secteurs 3D
- xlSurface - Surface 3D
Par exemple si nous utilisons le type de graphique "secteur" correspondant à la constante xlPie, nous obtenons (en reprenant notre exemple du dessus) :
Manipuler la taille du graphique
[modifier | modifier le wikicode]Il vous est possible de changer la taille du graphique, pour cela, il faut utiliser ce langage :
ActiveSheet.ChartObjects(1).Activate
ActiveChart.ChartArea.Select
With ActiveSheet.Shapes(1)
.ScaleWidth 1.48, msoFalse, msoScaleFromBottomRight
.ScaleHeight 1.49, msoFalse, msoScaleFromBottomRight
.ScaleWidth 1.32, msoFalse, msoScaleFromTopLeft
.ScaleHeight 1.26, msoFalse, msoScaleFromTopLeft
End With
Ce code va vous permettre d'agrandir votre graphique.
ScaleHeigt va être la mesure pour redimensionner la hauteur du graphique et ScaleWidth la largeur.
Manipuler les couleurs d'un graphique
[modifier | modifier le wikicode]Il est possible de changer les couleurs des courbes d'un graphique, pour cela il faut utiliser le code suivant (mettre la courbe en rouge par exemple) :
With ActiveChart.SeriesCollection(1)
.Border.Color = RGB(255, 0, 0)
End with
Voici en langage VBA, les codes couleurs pour les principales couleurs utilisées :
RGB(0, 0, 0) : noir
RGB(255, 255, 255) : blanc
RGB(255, 0, 0) : rouge
RGB(0, 255, 0) : vert
RGB(0, 0, 255) : bleu
Manipuler les axes d'un graphique
[modifier | modifier le wikicode]Il est possible via le langage VBA de manipuler les axes d'un graphique :
En reprenant l'exemple ci-dessus de la macro "CreerGraphiqueFeuilleDonnees" :
On peux rajouter au code du dessus ce code :
' Axe des catégories
With .Axes(xlCategory)
' Inversé
.ReversePlotOrder = True
' Coupe catégorie max
.Crosses = xlMaximum
' Toutes les étiquettes
.TickLabelSpacing = 1
' Titre de l'axe
' Affichage du titre
.HasTitle = True
.AxisTitle.Text = "Produits"
' Police des étiquettes
With .TickLabels.Font
.Bold = True
.Color = RGB(85, 130, 50)
.Size = 14
End With
End With
Ce code permet :
- D'inverser l'axe xlCategory
- De classer les catégories par grandeur
- De donner un titre à l'axe "Produits"
- De mettre en gras et de colorier les étiquettes
Ce qui donne :
Sub CreerGraphiqueFeuilleDonnees()
Const sheDonnéesSource As String = "DataSource"
Dim chGraph As Chart
Dim rPlageAcceuil As Range
Dim rPlageSource As Range
With Sheets(sheDonnéesSource)
' Plage devant acceuillir le graphique
Set rPlageAcceuil = .Range("A10:F20").Offset(0, 1)
' Création du graphique, ne pas oublier le .Chart final
' L'objet graphique se place sur la plage et à sa taille
Set chGraph = .ChartObjects.Add(rPlageAcceuil.Left, rPlageAcceuil.Top, rPlageAcceuil.Width, rPlageAcceuil.Height).Chart
' Source du graphique
Set rPlageSource = .Range("A1:E7")
End With
With chGraph
' Type barre empilée
.ChartType = xlBarStacked
' Source du graphique
.SetSourceData Source:=rPlageSource, PlotBy:=xlColumns
' Affichage du titre
.HasTitle = True
' Intitulé
.ChartTitle.Characters.Text = rPlageSource.Cells(1, 1)
' Légende en position haute
.Legend.Position = xlLegendPositionTop
' Axe des catégories
With .Axes(xlCategory)
' Inversé
.ReversePlotOrder = True
' Coupe catégorie max
.Crosses = xlMaximum
' Toutes les étiquettes
.TickLabelSpacing = 1
' Titre de l'axe
' Affichage du titre
.HasTitle = True
.AxisTitle.Text = "Produits"
' Police des étiquettes
With .TickLabels.Font
.Bold = True
.Color = RGB(85, 130, 50)
.Size = 14
End With
End With
End With
Sheets(sheDonnéesSource).Select
End Sub
Voici ce que nous obtenons en manipulant le graphique :
Ajouter un texte sur le graphique
[modifier | modifier le wikicode]Pour ajouter un texte sur le graphique, il faut utiliser le langage suivant :
ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 89.25, 21.75).Select
Selection.Characters.Text = "Le nom du texte que vous voulez mettre"
Modifier le nom du graphique
[modifier | modifier le wikicode]Pour modifier le nom du graphique, il faut utiliser le langage suivant :
ActiveChart.SeriesCollection(1).Name = "Nouveau nom du graphique"
Remplacer le graphique par une image
[modifier | modifier le wikicode]Il est possible de remplacer le graphique par une image.
Voici le code nécessaire ci-dessous :
Sub RemplaceGraphiqueParImage()
With ActiveSheet
.ChartObjects(1).CopyPicture
.ChartObjects(1).Delete
.Paste .Range("A1")
End With
End Sub
Annexes
[modifier | modifier le wikicode]Bibliographie
[modifier | modifier le wikicode]Monier.C, 2013, Mon eFormation Excel 2013 & VBA-Macros et Programmations, PEARSON
Liens internes
[modifier | modifier le wikicode]Leçon : Macros-commandes VBA
Chap. Création de Tableau croisé
Liens externes
[modifier | modifier le wikicode]http://excel.developpez.com/faq/?page=Graphique#GraphCreate
http://doc.frapp.fr/doku.php?id=programmation:vba:graphiques