All posts in VBAExcel

Cet article va vous montrer comment changer la couleur de certaines cellules en fonction de leur valeur. Nous allons partir de la feuille suivante :

A titre d’exemple, nous allons parcourir les cellules de la colonne G. Lorsque la valeur d’une de ces cellules sera supérieure ou égale à 10.5, la ligne correspondante sera colorée en orange. Dans le cas contraire, la ligne correspondante sera colorée en vert.

Voici le code utilisé :

Dim i As Integer

For i = 2 To 9

  If Cells(i, 7) >= 10.5 Then

    Range(Cells(i, 1), Cells(i, 7)).Interior.Color = RGB(255, 128, 128)

  Else

    Range(Cells(i, 1), Cells(i, 7)).Interior.Color = RGB(128, 255, 128)

  End If

Next i

Une boucle parcourt les cellules de la plage G2:G9. Si la valeur contenue dans une de ces cellules est supérieure ou égale à 10.5 :

For i = 2 To 9

  If Cells(i, 7) >= 10.5 Then

La plage comprise entre les colonnes A et G de la ligne concernée est colorée en orange :

Range(Cells(i, 1), Cells(i, 7)).Interior.Color = RGB(255, 128, 128)

Dans le cas contraire, cette même plage est colorée en vert :

Else

  Range(Cells(i, 1), Cells(i, 7)).Interior.Color = RGB(128, 255, 128)

Voici le résultat :

Dans l’article « Tracé d’un graphique en VBA« , vous avez appris à insérer un graphique dans la feuille courante. Cet article va aller beaucoup plus loin. En effet, vous allez y apprendre comment choisir l’emplacement, la taille, la position et le type du graphique dans la feuille.

Voici le code utilisé :

Dim feuille As Worksheet

Dim graphique As ChartObject

Set feuille = Sheets("Feuil1")

Set graphique = feuille.ChartObjects.Add(60, 50, 500, 300)

With graphique.Chart

  .ChartType = xlLineMarkers

  .SeriesCollection.NewSeries

  With .SeriesCollection(1)
 
    .Values = feuille.Range("A2:C2")

    .XValues = feuille.Range("A1:C1")

  End With

End With

Set graphique = Nothing

Set feuille = Nothing

Les deux premières instructions définissent les variables feuille et graphique qui représenteront respectivement la feuille dans laquelle le graphique sera inséré et le graphique à insérer :

Dim feuille As Worksheet

Dim graphique As ChartObject

L’instruction suivante affecte la feuille Feuil1 à la variable feuille :

Set feuille = Sheets("Feuil1")

L’instruction suivante ajoute à la feuille Feuil1 un graphique :

• de largeur 500 pixels ;
• de hauteur 500 pixels ;
• dont l’angle supérieur gauche se trouve à 60 pixels du bord gauche et à 50 pixels du bord supérieur.

Pour faciliter l’écriture du code, ce graphique est affecté à la variable graphique :

Set graphique = feuille.ChartObjects.Add(60, 50, 500, 300)

L’instruction With … End With suivante définit les caractéristiques du graphique. On commence par le type du graphique, via la propriété ChartType :

With graphique.Chart

  .ChartType = xlLineMarkers

Comme le montre le tableau suivant, de très nombreux graphiques peuvent être définis dans Excel 2016 :

L’instruction suivante ajoute une collection de séries :

.SeriesCollection.NewSeries

Cette collection est utilisée pour définir les abscisses (XValues) et les ordonnées (Values) correspondantes :

With .SeriesCollection(1)

  .Values = feuille.Range("A2:C2")

  .XValues = feuille.Range("A1:C1")

End With

Le code se termine par la suppression des variables objet graphique et feuille :

Set graphique = Nothing

Set feuille = Nothing

Voici un exemple d’exécution :

Pour créer un nouveau classeur, utilisez les instructions suivantes :

Dim classeur As Workbook
Set classeur = Workbooks.Add

Pour insérer une feuille dans le classeur en ours d’utilisation, plusieurs instructions peuvent être utilisées, en fonction de la position de l’insertion.

Ajout d’une feuille avant la feuille active :

Sheets.Add.Name = "NouvelleFeuille"

Ajout d’une feuille en dernière position :

Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "DerniereFeuille"

Ajout d’une feuille en première position :

Sheets.Add(Before:=Worksheets(1)).Name = "PremiereFeuille"

Ajout d’une feuille en troisième position :

Sheets.Add(After:=Worksheets(2)).Name = "TroisiemeFeuille"

VBA permet d’afficher du texte dans la barre d’état d’Excel via la propriété StatusBar de l’objet Application. A titre d’exemple, cette instruction :

Application.StatusBar = "Ce message est affiché par VBA"

Produit l’affichage suivant dans la barre d’état :

Bien entendu, vous pouvez afficher la valeur d’une propriété dans la barre d’état. Par exemple, cette instruction affiche la propriété Author de l’objet Application du classeur dans la barre d’état, précédée du libellé « Auteur :  » :

Application.StatusBar = "Auteur : " & ActiveWorkbook.BuiltinDocumentProperties("Author")

Ou encore, le contenu d’une cellule :

Application.StatusBar = "La cellule B2 vaut " & Cells(2,2)

Si la barre d’état n’est pas visible chez vous, vous pouvez la faire apparaître en affectant la valeur True à la propriété DisplayStatusBar de l’objet Application :

Application.DisplayStatusBar = True

Dans l’article « Variables et constantes« , vous avez appris à déclarer vos variables avec l’instruction Dim :

Dim entier As Integer

Dim texte As String

Pour affecter une valeur aux variables que vous venez de définir, il suffit d’utiliser le signe « = » :

entier = 12

texte = "Un texte"

L’instruction Dim permet également de définir des variables qui contiendront des objets. Par exemple :

Dim feuille as Worksheet

Cette instruction définit la variable feuille de type Worksheet. Pour pouvoir travailler avec la variable feuille, vous allez lui affecter une feuille avec l’instruction Set :

Set feuille = Sheets("Feuil1")

Ou encore :

Set feuille = Sheets(1)

Vous utiliserez systématiquement l’instruction Set pour affecter un objet à une variable. Voici quelques exemples :

Dim plage As Range

Set plage = Range("A2:A9") 'plage représente la plage A2:A9

Dim wb As Workbook

Set wb = ActiveWorkbook 'wb représente le classeur actif

Dim wb2 As Workbook

Set wb2 = Workbooks.Add 'Ajout d'un classeur

Lorsque vous avez défini un objet, pensez à le libérer à la fin de la procédure en lui affectant la valeur Nothing :

Set feuille = Nothing

Set plage = Nothing

Set wb = Nothing

Set wb2 = Nothing

Tous les classeurs possèdent des propriétés qui donnent des informations complémentaires sur son contenu, son auteur ou sa catégorie.

Pour accéder aux propriétés d’un classeur Excel 2016, basculez sur l’onglet Fichier dans le ruban puis cliquez sur Informations. Dans la vue backstage, cliquez sur Propriétés puis sur Propriétés avancées :

La boîte de dialogue des propriétés s’affiche et donne accès aux principales propriétés du classeur sous l’onglet Résumé :

Toutes ces propriétés peuvent être lues et modifiées en VBA. Pour cela, vous préciserez la propriété à laquelle vous voulez accéder dans la fonction ActiveWorkbook.BuiltinDocumentProperties(« p ») (où p est la propriété concernée). Voici quelques-unes des propriétés utilisables :

Ainsi par exemple, cette instruction affichera le nom de l’auteur du classeur dans une boîte de dialogue :

MsgBox ActiveWorkbook.BuiltinDocumentProperties("Author")

Où encore, cette instruction affectera la chaîne « Cette propriété a été définie en VBA » à la propriété Subject du classeur :

ActiveWorkbook.BuiltinDocumentProperties("Subject") = "Cette propriété a été définie en VBA"

Pour accéder à toutes les propriétés du classeur, il n’est pas nécessaire de connaitre leur nom : vous pouvez parcourir la collection BuiltinDocumentsProperties avec une boucle For Each. Ici par exemple, nous affichons le nom des propriétés dans la colonne 1 et les valeurs correspondantes dans la colonne 2 :

On Error Resume Next

i = 1

Worksheets(1).Activate

For Each p In ActiveWorkbook.BuiltinDocumentProperties

    Cells(i, 1) = p.Name

    Cells(i, 2) = p.Value

    i = i + 1

Next

Remarque

L’instruction On Error Resume Next évite l’arrêt du code et l’affichage d’un message d’erreur lorsqu’une propriété n’est pas définie.

Voici le résultat :

Cet article va vous montrer comment créer un graphique à partir de données numériques.
Nous allons partir de ces données :





 
Ces instructions :
Range("A1:C2").Select
ActiveSheet.Shapes.AddChart.Select

Produisent le résultat suivant :

Vous voulez un autre type de graphique ? Utilisez l’enregistreur de macros.
Sélectionnez la plage A1-C3, basculez sur l’onglet Développeur du ruban et cliquez sur l’icône Enregistrer une macro dans le groupe Code. Basculez sur l’onglet Insérer du ruban et insérez le graphique qui vous convient. Ici par exemple, nous choisissons un graphique combiné :

Arrêtez l’enregistrement de la macro en cliquant sur l’icône Arrêter l’enregistrement dans le groupe Code, sous l’onglet Développeur du ruban. Voici le code qui a été généré :
ActiveSheet.Shapes.AddChart2(322, xlColumnClustered).Select
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(2).ChartType = xlLine
ActiveChart.FullSeriesCollection(2).AxisGroup = 2
Complétez ce code en ajoutant au début l’instruction qui sélectionne la plage A1-C2 :
Range(« A1:C2 »).Select
ActiveSheet.Shapes.AddChart2(322, xlColumnClustered).Select
ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.FullSeriesCollection(2).ChartType = xlLine
ActiveChart.FullSeriesCollection(2).AxisGroup = 2
Et le tour est joué :

N’hésitez pas à utiliser l’enregistreur de macros en renfort du VBA. Le code généré sera directement utilisable.