< Macros-commandes VBA
fin de la boite de navigation du chapitre

Définitions

Qu’est-ce qu’un graphique ?

Un graphique permet de représenter des données chiffrées pour qu’elles soient plus lisibles. Il s'agit d'une "représentation visuelle".

Ainsi, un graphique permet de mieux comprendre les chiffres et de mieux les analyser. Cela permet également d'expliquer certains phénomènes. On voit plus facilement les relations entres plusieurs données.

Excel donne la possibilité à ses utilisateurs de créer un graphique à partir d'une base de données.

Excel permet à ses utilisateurs d’avoir un large choix dans les types de graphiques :

  • Histogramme
  • Courbes
  • Secteurs
  • Barres
  • Aires
  • Nuage de points
  • Boursier
  • Surface
  • Anneau
  • Bulles
  • Radar
Remarque

Sous Excel, une représentation graphique est issue d'un tableau réalisé au préalable.

Qu’est-ce qu’une macro ?

Une macro ou macro-commande permet d'enregistrer une suite d'instructions écrites les unes après les autres. L'enregistrement d'une macro donne naissance à un code qui se fait en langage VBA (Visual Basic for Applications). Le langage VBA est un dérivé du langage Visual Basic, spécialement développé pour les applications du pack Office (Word, Excel, PowerPoint).

Lorsque l’on exécute la macro, le tableur va interpréter les différentes instructions sauvegardées dans le code de la macro à exécuter. Cette exécution permettra de reproduire l'intégralité des commandes enregistrées. Chaque étape sera répétée à l'identique à chaque exécution de la macro.

La macro a pour finalité d'automatiser des tâches afin de les rendre beaucoup plus rapides que si elles avaient été effectuées manuellement.

Dans le cadre d'un projet d'entreprise, elles peuvent aussi avoir un intérêt lors de tests répétitifs de contrôle de qualité des données et/ou de tests de non-régression.

Son rôle est aussi de simplifier la vie de l'utilisateur. La macro est souvent utilisée lorsqu'une tâche est répétitive, ce qui permet à l'utilisateur de travailler beaucoup plus rapidement et efficacement sans erreurs manuelles.

Comment créer un graphique à partir d’un tableau créé manuellement ?

Créer une base de données

Pour créer un graphique, il faut dans un premier temps créer une base de données dans un tableur pouvant faire appel aux graphiques.

Cette base de données devra alors contenir des titres en colonnes et/ou en lignes, puis devra être alimentée selon les besoins de l’utilisateur qui crée le tableau.

Les titres présents en colonnes ou en lignes sont utilisés afin d’avoir une légende claire et précise.

Exemple

Prenons l'exemple d'une entreprise X, admettons que celle-ci souhaite étudier l'évolution de son chiffre d'affaires sur 5 ans.

Elle va alors faire un tableau de ce type sur Excel :
Annexe 1 : Base de données
Fin de l'exemple

Création manuelle du graphique

Après avoir créé une base de données, il devient alors facile de lui associer un graphique. Pour ce faire, il suffit de :

  • Sélectionner la base de données
  • Cliquer sur la rubrique "Insertion"
  • Cliquer sur "Graphiques"
  • Choisir le type de graphique le plus adapté
Conseil

Lors de la sélection des données, il est conseillé de sélectionner la totalité des colonnes (jusqu'en bas de la feuille). Cela évite de devoir redéfinir la source du graphique à chaque nouvelle saisie dans la base de données.

Configurer le graphique

Pour configurer le graphique, il suffit d'aller dans les différentes rubriques :

  • Création : elle a pour but de modifier le type de graphique ; de changer la source des données ; de configurer la disposition du graphique ; de modifier les couleurs du graphique etc.
  • Disposition : elle a pour but d'insérer des objets ; d'ajouter des étiquettes de données ; d'ajouter des titres, etc.
  • Mise en forme : elle a pour but de modifier la mise en forme

Comment créer automatiquement un graphique à l’aide d’une macro ?

Test avec enregistrement

Démarche

Pour automatiser la création d'un graphique, on peut utiliser la méthode de l’enregistreur.

La démarche est la suivante :

1ère étape : Création de la macro

  • Activation de l’enregistreur
  • Dénomination de la macro : « CREERGRAPHIQUE » ; « OK »
  • Sélection des données : sélectionner les colonnes afin que cela prenne en compte toutes les lignes en cas d’ajout de données
  • Insertion du graphique : « Insertion » ; « graphique »
  • Choix du type de graphique
  • Choix de la mise en forme
  • Ajout des étiquettes de données
  • Arrêt de l’enregistreur

2ème étape : Exécution de la macro

Lors de l'exécution de la macro, il peut se produire deux choses :

  • Soit la macro s'exécute parfaitement
  • Soit une boîte de dialogue s’ouvre pour indiquer une erreur (Annexe 2)

Dans ce cas présent, la macro s'effectue correctement.

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

Il est intéressant d'analyser l'écriture Visal Basic, surtout en cas d'erreur. La méthode de l'enregistreur pour créer un graphique ne génère pas d'erreur contrairement à d'autres créations. Il n'y a donc pas besoin de retoucher l'écriture.

Remarque

Lorsqu’il y a une erreur dans la macro, une boîte de dialogue apparaît pour nous l'indiquer (Annexe 2). Il est alors facile de localiser la partie du code incorrecte car celle-ci est surlignée en jaune.

En reprenant l'exemple de l'entreprise X, suite à l'enregistrement, nous obtenons l'écriture suivant sous Visual Basic :

Sub CREERGRAPHIQUE()
'
' CREERGRAPHIQUE Macro
'
    Columns("A:B").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Feuil1'!$A:$B")
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.ApplyLayout (3)
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ApplyDataLabels

End Sub


4ème étape : Masquer les étapes de l'exécution de la macro

Afin de ne pas voir les différentes étapes et d'améliorer les performances de la macro lors de son exécution, il est possible de les masquer (désactiver la carte vidéo de l'ordinateur) :

  • Ajouter au début de la macro :
 Application.ScreenUpdating=False
  • Ajouter à la fin de la macro :
 Application.ScreenUpdating=True

5ème étape : L'écriture finale pour créer un graphique automatiquement

En reprenant l’exemple de l’entreprise X, l'écriture finale qui apparaît sous Visual Basic est :

Sub CREERGRAPHIQUE()
'
' CREERGRAPHIQUE Macro

'
Application.ScreenUpdating=False
    Columns("A:B").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Feuil1'!$A:$B")
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.ApplyLayout (3)
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).ApplyDataLabels
Application.ScreenUpdating=True
End Sub

6ème étape : Création d'un bouton

  • Créer un bouton : Insertion d'une forme
  • Ajouter un texte : clic droit ; "modifier le texte" ; exemple : "CREER UN GRAPHIQUE"
  • Affecter la macro : clic droit ; "affecter une macro" ; choisir la macro "CREERGRAPHIQUE" ; "OK"
Conseil

Pour pouvoir comprendre les étapes de la macro, il est possible de suivre l'écriture Visual Basic en utilisant la touche F8.

Avantages

Les avantages de la création d'un graphique automatique avec l'enregistreur :

  • Macro générée automatiquement par l’enregistreur
  • Méthode simple lorsque l’on ne connaît pas le langage VBA pour créer un graphique
  • Permet de se familiariser avec le langage VBA

Inconvénients

Les inconvénients de la création d'un graphique automatique avec l'enregistreur :

  • Méthode peu souple
  • Restreint les utilisateurs
  • Source d'erreur
  • La macro peut seulement faire les actions que l’on a enregistré
  • De ce fait, elle ne laisse pas le choix du type de graphiques

De manière générale :

  • Lorsqu’il y a une erreur, la macro ne fonctionne pas du tout
  • Il faut parfois revenir sur le code
  • Il n’est pas évident de comprendre les erreurs de programmation

Test avec langage Visual Basic

Démarches


Notre objectif est ici de créer un graphique via le code VBA.
Nous avons deux possibilités :

  • Créer un graphique sur une nouvelle feuille (feuille de graphique)
  • Créer un graphique sur notre feuille de calcul (feuille des données)


Nous allons expliquer les deux démarches ci-dessous.

Exemple

Nous remplissons un tableau de base avec toutes les données.
Par exemple le tableau ci-contre concernant l'évolution des ventes de noël de 2012 à 2015 :

Annexe 3 : Tableau de base sur les ventes de noël




  • 1ère possibilité : Créer un graphique sur une nouvelle feuille (feuille de graphique) :


La démarche de notre programmation est la suivante :

1. Dimensionner les variables utilisées dans le code (phase facultative mais recommandée pour limiter les erreurs)
2. Définir la plage des données sources du graphique
3. Supprimer le graphique si déjà existant afin de rafraîchir proprement notre graphique
4. Créer le graphique en indiquant le type de graphique (ici histogramme), la plage du graphique, le titre, l'intitulé du titre s'il y a et enfin le nom de la feuille qui recevra le graphique
5. Afficher la feuille où se situe le graphique (facultatif mais pratique afin de voir directement le résultat de notre graphique)
Ci-dessous le code utilisé : 

Sub CreerGraphiqueNvelleFeuille()

    Const sheDonnéesSource  As String = "DataSource"
    Const sheGraphique      As String = "Graphique"
    
    Dim chGraph             As Chart
    Dim rPlage              As Range

    ' Définition de la plage des données source du graphique 
    Set rPlage = Sheets(sheDonnéesSource).Range("A:E")

    ' Suppression du graphique si déjà existant
    On Error Resume Next
    Sheets(sheGraphique).Delete
    On Error GoTo 0
    
    ' Création du graphique
    Set chGraph = Charts.Add
    With chGraph
        ' Type histogramme
        .ChartType = xlColumnClustered
        ' Source du graphique
        .SetSourceData Source:=rPlage, PlotBy:=xlColumns
        ' Affichage du titre
        .HasTitle = True
        ' Intitulé
        .ChartTitle.Characters.Text = rPlage.Cells(1, 1)
        ' Nom de la feuille recevant le graphique
        .Name = sheGraphique
    End With
    
    Sheets(sheGraphique).Select
    
End Sub


Et voici ce que nous obtenons comme graphique :
Annexe 4 : Graphique ventes noël sur nouvelle feuille


  • 2ème possibilité : Créer un graphique sur notre feuille de calcul (feuille des données) :


La démarche de notre programmation est la suivante :
1. Dimensionner les variables utilisées dans le code
2. Dans la feuille de donnée (avec "With"), définir la plage accueillant le graphique, créer le graphique et mettre la source des données du graphique
3. Dans le graphique (avec "With"), définir le type de graphique (ici barre empilée), la source du graphique, la présence ou non de titre avec l'intitulé et enfin la position de la légende
5. Afficher la feuille où se situe le graphique
Ci-dessous le code utilisé : 

Sub CreerGraphiqueFeuilleDonnees()

    Const sheDonnéesSource  As String = "DataSource"

    Dim chGraph         As Chart
    Dim rPlageAcceuil   As Range
    Dim rPlageSource    As Range
    
    With Sheets(sheDonnéesSource)
        ' Plage devant accueuillir le graphique
        Set rPlageAcceuil = .Range("A10:F20").Offset(0, 1)
        ' Création du graphique, ne pas oublier le .Chart final
        ' L'objet graphique se place sur la plage et à sa taille
        Set chGraph = .ChartObjects.Add(rPlageAcceuil.Left, rPlageAcceuil.Top, rPlageAcceuil.Width, rPlageAcceuil.Height).Chart            
        ' Source du graphique
        Set rPlageSource = .Range("A:E")
    End With
      
    With chGraph
        ' Type barre empilée
        .ChartType = xlBarStacked
        ' Source du graphique
        .SetSourceData Source:=rPlageSource, PlotBy:=xlColumns
        ' Affichage du titre
        .HasTitle = True
        ' Intitulé
        .ChartTitle.Characters.Text = rPlageSource.Cells(1, 1)
        ' Légende en position haute
        .Legend.Position = xlLegendPositionTop     
     End With

    Sheets(sheDonnéesSource).Select
    
End Sub


Et voici ce que nous obtenons comme graphique :
Annexe 5 : Graphique ventes noël sur feuille de données


Fin de l'exemple

Avantages

Les avantages de la création d'un graphique automatique avec le langage Visual Basic :

  • Suppression des erreurs humaines dès lors que le programme est débogué et qu’il n'y a pas de modification de la structure des données de base
  • Automatisation des tâches répétitives
  • Une fois créé, c’est un gain de temps pour l'utilisateur
  • Simplification de la transmission des connaissances à d'autres utilisateurs
  • Choix de n’importe quel type de graphique présent dans Excel
  • Harmonisation de la structure d'un graphique pour un ensemble d'utilisateurs. Le graphique "officiel" ne pourra pas prendre une autre forme que celui programmé

Inconvénients

Les inconvénients de la création d'un graphique automatique avec le langage Visual Basic :

  • Nécessite une connaissance technique d'Excel et VBA pour développer le programme et le déboguer
  • Demande un investissement en temps au départ pour mettre en 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 ?

Les constantes utilisées pour choisir un type de graphique

Lorsque nous créons un graphique il faut indiquer "ChartType= XXX"
"XXX" étant l'une des constante ci-dessous (principaux types de graphiques) :

  • xlArea - Aires
  • xlAreaStacked - Aires empilées
  • xl3DArea - Aires 3D
  • xlBarOfPie - Barres de secteurs
  • xlBarStacked - Barres empilées
  • xl3DBarStacked - Barres 3D empilées
  • xlBubble - Bulles
  • xlBubble3DEffect - Bulles 3D
  • xlLine - Courbes
  • xlLineMarkersStacked - Courbes empilées avec marqueurs
  • xl3DLine - Courbes 3D
  • xlColumnClustered - Histogramme en cluster
  • xlColumnStacked - Histogramme empilé
  • xl3DColumn - Histogramme 3D
  • xl3DColumnClustered - Histogramme 3D en cluster
  • xl3DColumnStacked - Histogramme 3D empilé
  • xlXYScatter - Nuages de points
  • xlXYScatterLines - Nuages de points avec courbes
  • xlRadar - Radar
  • xlPie - Secteurs
  • xl3DPie - Secteurs 3D
  • xlSurface - Surface 3D


Par exemple si nous utilisons le type de graphique "secteur" correspondant à la constante xlPie, nous obtenons (en reprenant notre exemple du dessus) :

Annexe 6 : Graphique secteur ventes nöel



Manipuler la taille du graphique

Il vous est possible de changer la taille du graphique, pour cela, il faut utiliser ce langage :

 
     ActiveSheet.ChartObjects(1).Activate
     ActiveChart.ChartArea.Select
     With ActiveSheet.Shapes(1)
        .ScaleWidth 1.48, msoFalse, msoScaleFromBottomRight
        .ScaleHeight 1.49, msoFalse, msoScaleFromBottomRight
        .ScaleWidth 1.32, msoFalse, msoScaleFromTopLeft
        .ScaleHeight 1.26, msoFalse, msoScaleFromTopLeft
     End With

Ce code va vous permettre d'agrandir votre graphique.

ScaleHeigt va être la mesure pour redimensionner la hauteur du graphique et ScaleWidth la largeur.

Manipuler les couleurs d'un graphique

Il est possible de changer les couleurs des courbes d'un graphique, pour cela il faut utiliser le code suivant (mettre la courbe en rouge par exemple) :

 
 With ActiveChart.SeriesCollection(1)
   .Border.Color = RGB(255, 0, 0)
 End with

Voici en langage VBA, les codes couleurs pour les principales couleurs utilisées :

 
   RGB(0, 0, 0) : noir
   RGB(255, 255, 255) : blanc
   RGB(255, 0, 0) : rouge
   RGB(0, 255, 0) : vert
   RGB(0, 0, 255) : bleu

Manipuler les axes d'un graphique

Il est possible via le langage VBA de manipuler les axes d'un graphique :

En reprenant l'exemple ci-dessus de la macro "CreerGraphiqueFeuilleDonnees" :

On peux rajouter au code du dessus ce code : 

 ' Axe des catégories
        With .Axes(xlCategory)
        
            ' Inversé
            .ReversePlotOrder = True
            ' Coupe catégorie max
            .Crosses = xlMaximum
            ' Toutes les étiquettes
            .TickLabelSpacing = 1
            ' Titre de l'axe
            ' Affichage du titre
            .HasTitle = True
            .AxisTitle.Text = "Produits"
             ' Police des étiquettes
             With .TickLabels.Font
               .Bold = True
               .Color = RGB(85, 130, 50)
               .Size = 14
             End With
        
        End With


Ce code permet :

  • D'inverser l'axe xlCategory
  • De classer les catégories par grandeur
  • De donner un titre à l'axe "Produits"
  • De mettre en gras et de colorier les étiquettes

Ce qui donne : 

Sub CreerGraphiqueFeuilleDonnees()

    Const sheDonnéesSource  As String = "DataSource"

    Dim chGraph         As Chart
    Dim rPlageAcceuil   As Range
    Dim rPlageSource    As Range
    
    With Sheets(sheDonnéesSource)
        ' Plage devant acceuillir le graphique
        Set rPlageAcceuil = .Range("A10:F20").Offset(0, 1)
        ' Création du graphique, ne pas oublier le .Chart final
        ' L'objet graphique se place sur la plage et à sa taille
        Set chGraph = .ChartObjects.Add(rPlageAcceuil.Left, rPlageAcceuil.Top, rPlageAcceuil.Width, rPlageAcceuil.Height).Chart          
        ' Source du graphique
        Set rPlageSource = .Range("A1:E7")
    End With
      
    With chGraph
        ' Type barre empilée
        .ChartType = xlBarStacked
        ' Source du graphique
        .SetSourceData Source:=rPlageSource, PlotBy:=xlColumns
        ' Affichage du titre
        .HasTitle = True
        ' Intitulé
        .ChartTitle.Characters.Text = rPlageSource.Cells(1, 1)
        ' Légende en position haute
        .Legend.Position = xlLegendPositionTop
        
        ' Axe des catégories
        With .Axes(xlCategory)   
            ' Inversé
            .ReversePlotOrder = True
            ' Coupe catégorie max
            .Crosses = xlMaximum
            ' Toutes les étiquettes
            .TickLabelSpacing = 1
            ' Titre de l'axe
            ' Affichage du titre
            .HasTitle = True
            .AxisTitle.Text = "Produits"
             ' Police des étiquettes
             With .TickLabels.Font
               .Bold = True
               .Color = RGB(85, 130, 50)
               .Size = 14
             End With   
        End With
     End With

    Sheets(sheDonnéesSource).Select
    
End Sub

Voici ce que nous obtenons en manipulant le graphique :

Annexe 7 : Graphique ventes noël manipulation des axes


Ajouter un texte sur le graphique

Pour ajouter un texte sur le graphique, il faut utiliser le langage suivant :

   ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 1, 1, 89.25, 21.75).Select
   Selection.Characters.Text = "Le nom du texte que vous voulez mettre"

Modifier le nom du graphique

Pour modifier le nom du graphique, il faut utiliser le langage suivant :

   ActiveChart.SeriesCollection(1).Name = "Nouveau nom du graphique"

Remplacer le graphique par une image

Il est possible de remplacer le graphique par une image.
Voici le code nécessaire ci-dessous :

Sub RemplaceGraphiqueParImage()

    With ActiveSheet
        .ChartObjects(1).CopyPicture
        .ChartObjects(1).Delete
        .Paste .Range("A1")
    End With
    
End Sub

Annexes

Bibliographie

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

Liens internes

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

Liens externes

http://excel.developpez.com/faq/?page=Graphique#GraphCreate
http://doc.frapp.fr/doku.php?id=programmation:vba:graphiques

Cet article est issu de Wikiversity. Le texte est sous licence Creative Commons - Attribution - Partage dans les Mêmes. Des conditions supplémentaires peuvent s'appliquer aux fichiers multimédias.