1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
| Option Explicit
Option Base 1
'Création de l'histogramme
Public Sub CreationGraphique()
Dim tblDonnees As Variant
Dim lngLigFinTab As Integer
Dim c As Range
Dim i As Integer
Dim varChart As Variant
tblDonnees = RecuperationDonnees
'Traitement des données
With Worksheets("Feuil2")
.Cells.Clear
.Range(.[A1], .Cells(UBound(tblDonnees), 3)) = tblDonnees
'Suppression des valeurs <= 1
lngLigFinTab = .Range("A" & .Rows.Count).End(xlUp).Row
For i = lngLigFinTab To 1 Step -1
If .Cells(i, 2).Value <= 1 Then .Cells(i, 2).EntireRow.Delete
Next i
lngLigFinTab = .Range("A" & .Rows.Count).End(xlUp).Row
'Filtrage des donnes par ordre croissant
.Range(.[A1], .Cells(lngLigFinTab, 3)).Sort Key1:=.Columns(2), order1:=xlAscending, Header:=xlNo
End With
'Création du graphique
'Suppression du graphique existant
Application.DisplayAlerts = False
For Each varChart In ActiveWorkbook.Charts
If varChart.Name = "Graphique" Then varChart.Delete
Next varChart
Application.DisplayAlerts = True
'Ajout du graphique
With Sheets("Feuil2")
Set c = .Range(.[A1], .Cells(lngLigFinTab, 2))
With .ChartObjects.Add(Left:=10, Width:=10, Top:=10, Height:=10)
.Chart.ChartType = xlColumnClustered
.Chart.SetSourceData Source:=c
.Chart.Legend.Delete
.Chart.Location Where:=xlLocationAsNewSheet
End With
End With
ActiveChart.Name = "Graphique"
'Mise en forme des points (couleurs)
i = 1
With ActiveChart
For Each varChart In .SeriesCollection(1).Points
With varChart.Format.Fill
Select Case Sheets("Feuil2").Cells(i, 3).Value
Case "DD"
.ForeColor.RGB = RGB(255, 153, 0)
Case "DND"
.ForeColor.RGB = RGB(255, 255, 153)
Case "DI"
.ForeColor.RGB = RGB(0, 255, 0)
Case "DEEE"
.ForeColor.RGB = RGB(0, 255, 255)
End Select
i = i + 1
End With
Next varChart
End With
End Sub
'Récupération du tableau avec une plage dynamique
Public Function RecuperationDonnees() As Variant
Dim lngLigFinTab As Long
With Sheets("Feuil1")
lngLigFinTab = .[A29].End(xlDown).Row
RecuperationDonnees = .Range(.[A29], .Cells(lngLigFinTab, 3))
End With
End Function |
Partager