Charts sous VBA: comment faire ?? [Résolu]

Messages postés
56
Date d'inscription
vendredi 4 mars 2005
Dernière intervention
4 octobre 2006
- 13 avril 2005 à 16:07 - Dernière réponse :
Messages postés
56
Date d'inscription
vendredi 4 mars 2005
Dernière intervention
4 octobre 2006
- 13 avril 2005 à 17:33
Bonjour,

je me bats depuis près d'une semaine avec VBE pour la programmation de charts. Mais toujours des erreurs de partout.
J'ai bien essayé de chercher sur les différents forums, mais rien.
Est-ce que quelqu'un aurait une idée ou un code-type sur lequel je pourrais me baser?

Merci beaucoup d'avance.


Amateurement vôtre...
Afficher la suite 

3 réponses

Meilleure réponse
Messages postés
56
Date d'inscription
vendredi 4 mars 2005
Dernière intervention
4 octobre 2006
- 13 avril 2005 à 17:33
3
Merci
Merci à tous,

vos conseils m'ont été très précieux.

J'a également de mon côté trouvé un site intéressant:
http://peltiertech.com/ (site en anglais)

Encore merci.


Amateurement vôtre...

Merci cs_nico39 3

Avec quelques mots c'est encore mieux Ajouter un commentaire

Codes Sources a aidé 88 internautes ce mois-ci

Messages postés
106
Date d'inscription
lundi 11 avril 2005
Dernière intervention
16 juillet 2010
- 13 avril 2005 à 16:55
0
Merci
Sous VBA, je ne sais pas (jamais utilisé...je suis un débutant) mais avec Visual Basic, je viens de terminer un programme de calcul matriciel permettant aussi de visualiser les résultats.
J'imagine que ça doit marcher à peu près de la même façon.

Dans la Toolbox de VB, tu as un contrôle (si tu ne l'as pas, clic droit + "Components..." et charge le) appelé Microsoft Chart ou MSChart

Dans mon programme, ça donne ça :
Il s'agit de représenter le spectre d'un signal périodique faisant suite à une Transformée de Fourier Discrète :
-------------------------------------------
With MSChart1
.TitleText = "Transformée de Fourier Discrète des résidus après régression linéaire"
' Titre
.chartType = VtChChartType2dBar
' type de graphe
.ColumnCount = 1
' Paramètre de la série de données n°1
.RowCount = nhf
' Nombre de points
For i = 1 To nhf
' Définition de chaque point de la série n°1
.Row = i
.Column = 1
.RowLabel = tfd_t(i - 1)
' En abscisse, ici les périodes
.Data = tfd_ampl(i - 1)
' En ordonnée, ici les amplitudes
Next
End With

MSChart1.ShowLegend = True
With MSChart1.Plot.SeriesCollection(1)
.LegendText = "Spectre du signal"
' Texte de la légende
End With

With MSChart1.Plot.Axis(0, 1)
' Def de l'axe des abscisses
.AxisTitle.VtFont.Size = 14
.AxisTitle.Text = "Période (m)"
.CategoryScale.DivisionsPerLabel = Fix(nhf / 20)
.CategoryScale.DivisionsPerTick = Fix(nhf / 20)
' Pour le nombre de graduations
End With

With MSChart1.Plot.Axis(1, 1)
.AxisTitle.VtFont.Size = 14
.AxisTitle.Text = "Amplitude (m)"
End With

-------------------------------------------
C'est certainement pas le méthode la plus élégante, mais dans mon cas, ça rend assez bien....

J'espère que ça pourra t'aider.


Marin Marais
Messages postés
23
Date d'inscription
mercredi 9 février 2005
Dernière intervention
22 août 2005
- 13 avril 2005 à 17:00
0
Merci
Voici plusieur exemple de programme VBA EXCEL et ACCESS: il faut pas oublier de placer sur la page d'interface un chartspace qui se trouve dans la liste des composant : Microsoft office chartspace et un spreadsheet1
Sub Window_Onload()


' This example starts by putting the data into Spreadsheet1. Normally,


' the data would already be loaded, but this code was added for completeness.


Spreadsheet1.ActiveSheet.Cells.Clear


Spreadsheet1.ActiveSheet.Cells(2, 1).Value = "White"


Spreadsheet1.ActiveSheet.Cells(3, 1).Value = "Black"


Spreadsheet1.ActiveSheet.Cells(4, 1).Value = "Asian"


Spreadsheet1.ActiveSheet.Cells(5, 1).Value = "Latino"





Spreadsheet1.ActiveSheet.Cells(1, 2).Value = "Perot"


Spreadsheet1.ActiveSheet.Cells(2, 2).Value = 0.2


Spreadsheet1.ActiveSheet.Cells(3, 2).Value = 0.06


Spreadsheet1.ActiveSheet.Cells(4, 2).Value = 0.17


Spreadsheet1.ActiveSheet.Cells(5, 2).Value = 0.13





Spreadsheet1.ActiveSheet.Cells(1, 3).Value "Clinton"


Spreadsheet1.ActiveSheet.Cells(2, 3).Value = 0.38


Spreadsheet1.ActiveSheet.Cells(3, 3).Value = 0.82


Spreadsheet1.ActiveSheet.Cells(4, 3).Value = 0.28


Spreadsheet1.ActiveSheet.Cells(5, 3).Value = 0.62





Spreadsheet1.ActiveSheet.Cells(1, 4).Value = "Bush"


Spreadsheet1.ActiveSheet.Cells(2, 4).Value = 0.42


Spreadsheet1.ActiveSheet.Cells(3, 4).Value = 0.12


Spreadsheet1.ActiveSheet.Cells(4, 4).Value = 0.55


Spreadsheet1.ActiveSheet.Cells(5, 4).Value = 0.25





' Clear the contents of the chart workspace. This removes


' any old charts that may already exist and leaves the chart workspace


' completely empty. One chart object is then added.


ChartSpace1.Clear


ChartSpace1.Charts.Add


Set c = ChartSpace1.Constants





' Set the chart DataSource property to the spreadsheet.


' It is possible to specify multiple data sources, but this example uses only one.


ChartSpace1.DataSource = Spreadsheet1





' Add three series to the chart.


ChartSpace1.Charts(0).SeriesCollection.Add


ChartSpace1.Charts(0).SeriesCollection.Add


ChartSpace1.Charts(0).SeriesCollection.Add





' Connect the chart to data by specifying spreadsheet cell references


' for the different data dimensions. Notice that the SetData method uses


' a data source index of 0; this is the first data source, which was previously


' set to the spreadsheet. If you had created multiple data sources,


' you could specify the index to any item in the WCDataSources collection for the


' data source index. For example, if two spreadsheet controls were attached to this


' chart workspace, you could set data from the first control using index 0


' and set data from the second control using index 1.





' Notice that the series name is also bound to a spreadsheet cell. Changing


' the contents of the cell "B1" will also change the name that appears in the legend.


' If you don't want this behavior, set SeriesCollection(0).Caption instead of


' using the SetData method to bind the series name to the spreadsheet.





' Series one contains election data for Perot.


' Bind the series name, the category names, and the values.


ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimSeriesNames, 0, "B1"


ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimCategories, 0, "A2:A5"


ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, 0, "B2:B5"





' Series two contains election data for Clinton.


ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimSeriesNames, 0, "C1"


ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimCategories, 0, "A2:A5"


ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimValues, 0, "C2:C5"





' Series two contains election data for Bush.


ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimSeriesNames, 0, "D1"


ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimCategories, 0, "A2:A5"


ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimValues, 0, "D2:D5"





' Make the chart legend visible, format the left value axis as percentage,


' and specify that value gridlines are at 10% intervals.


ChartSpace1.Charts(0).HasLegend = True


ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).NumberFormat = "0%"


ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).MajorUnit = 0.1


End Sub





Sub Window_Onload()





Dim asSeriesNames(1)


Dim asCategories(3)


Dim aiSeries1(3)


Dim alSeries2(3)


Dim chConstants


Dim chtNewChart


Dim serUnitSales


Dim serDispInc


Dim axIncomeAxis





asSeriesNames(0) = "UnitSales"


asSeriesNames(1) = "Disposable Income"





asCategories(0) = "Item 1"


asCategories(1) = "Item 2"


asCategories(2) = "Item 3"


asCategories(3) = "Item 4"





aiSeries1(0) = 75


aiSeries1(1) = 84


aiSeries1(2) = 30


aiSeries1(3) = 94





alSeries2(0) = 14522


alSeries2(1) = 17321


alSeries2(2) = 9424


alSeries2(3) = 41782





Set chConstants = ChartSpace1.Constants





' Enagble the display of the legend.


ChartSpace1.HasChartSpaceLegend = True





' Add a new chart to Chartspace1.


Set chtNewChart = ChartSpace1.Charts.Add





' Specify that the chart is a column chart.


chtNewChart.Type = chConstants.chChartTypeLineMarkers





' Bind the chart to the arrays.


chtNewChart.SetData chConstants.chDimSeriesNames, chConstants.chDataLiteral, asSeriesNames


chtNewChart.SetData chConstants.chDimCategories, chConstants.chDataLiteral, asCategories





Set serUnitSales = chtNewChart.SeriesCollection(0)





serUnitSales.SetData chConstants.chDimValues, chConstants.chDataLiteral, aiSeries1





Set serDispInc = chtNewChart.SeriesCollection(1)





serDispInc.SetData chConstants.chDimValues, chConstants.chDataLiteral, alSeries2





' Ungroup the series.


serDispInc.Ungroup True





' Add a new value axis to the chart based on the values in the series.


Set axIncomeAxis = chtNewChart.Axes.Add(serDispInc.Scalings(chConstants.chDimValues))





' Place the axis on the right side of the chart.


axIncomeAxis.Position = chConstants.chAxisPositionRight





' Display the series as columns.


serDispInc.Type = chConstants.chChartTypeColumnClustered





End Sub





Sub Window_Onload()





Dim oChart


Dim oSeries1, oSeries2


Dim oConst





'Ensure ChartSpace1 is empty:


ChartSpace1.Clear





Set oConst = ChartSpace1.Constants





'Create a new chart in the ChartSpace


Set oChart = ChartSpace1.Charts.Add





'Add a series of type Column


Set oSeries1 = oChart.SeriesCollection.Add


With oSeries1


.Caption = "Sales"


.SetData oConst.chDimCategories, oConst.chDataLiteral, _


Array("1994", "1995", "1996", "1997")


.SetData oConst.chDimValues, oConst.chDataLiteral, _


Array(50, 60, 55, 59)


.Type = oConst.chChartTypeColumnClustered


End With





'Add a second series of type Line


Set oSeries2 = oChart.SeriesCollection.Add


With oSeries2


.Caption = "Profit"


.SetData oConst.chDimCategories, oConst.chDataLiteral, _


Array("1994", "1995", "1996", "1997")


.SetData oConst.chDimValues, oConst.chDataLiteral, _


Array(39, 47, 52, 46)


.Type = oConst.chChartTypeLine


End With





'Add a second value axis to the Chart


oChart.Axes.Add oChart.Scalings(oConst.chDimValues), _


oConst.chAxisPositionRight, oConst.chValueAxis





'Display the legend


oChart.HasLegend = True


oChart.Legend.Position = oConst.chLegendPositionBottom





'Display the title for the chart


oChart.HasTitle = True


oChart.title.Caption = "Four Year Overview"





End Sub





oChart.Charts(0).SeriesCollection(0).Caption = "Nitrogen Dioxide"


oChart.Charts(0).SeriesCollection(1).Caption = "Sulfur Dioxide"


oChart.Charts(0).SeriesCollection(2).Caption = "Carbon Monoxide"


'Format title


oChart.Charts(0).HasTitle = True


oChart.Charts(0).title.Caption = "The Level of Air Pollutants"


Set fnt = oChart.Charts(0).title.Font


fnt.Name = "arial"


fnt.Size = 14


fnt.Bold = True


'Format legend font and position


oChart.Charts(0).PlotArea.Interior.Color = "#CCCC99"


oChart.Charts(0).HasLegend = True


oChart.Charts(0).Legend.Position = c.chLegendPositionBottom


oChart.Charts(0).Legend.Font = "Tahoma"


oChart.Charts(0).Legend.Font.Size = 7


oChart.Charts(0).Legend.LegendEntries(0).Visible = True





Dim ax


Set ax = oChart.Charts(0).Axes(0)


ax.Scaling.HasSplit = True


ax.Scaling.SplitMaximum = 300


ax.Scaling.SplitMinimum = 200











'Add data label for the first series


oChart.Charts(0).SeriesCollection(0).DataLabelsCollection.Add


'Add data label for the second series


oChart.Charts(0).SeriesCollection(1).DataLabelsCollection.Add


'Add data label for the third series


oChart.Charts(0).SeriesCollection(2).DataLabelsCollection.Add








Sub Window_Onload()


' Initialize the DSC


DSCInit dscSample





' Draw the chart


DrawChart csSample, dscSample





End Sub





' Initializes the DSC by setting Connection String and RecordSetDef


Sub DSCInit(dsc)


' Add a RecordsetDef with name ChartData to the dsc


If Len(dsc.ConnectionString) = 0 Then


dsc.ConnectionString = "provider=mspersist"


dsc.RecordsetDefs.AddNew "GenerateData.ASP", _


dsc.Constants.dscCommandFile, "ChartData"


Else


Window.Status = "DSC ConnectionString is already set!"


End If


End Sub





' Draws the chart using the RecordSetDef data


Sub DrawChart(cspace, dsc)


Dim c 'Constants object


Dim cht 'Temp WCChart object


Dim ser 'Temp WCSeries object


Dim ax 'Temp WCAxis object





Set c = cspace.Constants





' Clear the Chartspace


cspace.Clear





' Load the chart data sources


Dim cds 'Temp WCChartDataSource object





' Add a DataSource to the Chart and set it to be the dsc


Set cds = cspace.ChartDataSources.Add()


Set cds.DataSource = dsc





' Set the Data Member to be the RecordsetDef


cds.DataMember = "ChartData"


cds.CacheSize = 400





' Draw the Chart


Set cht = cspace.Charts.Add()


cht.Type = c.chChartTypeLineMarkers


cht.HasLegend = True


cht.Legend.Position = c.chLegendPositionTop


cht.HasTitle = True


cht.title.Caption = "CPU Utilizations"





' Add a series


Set ser = cht.SeriesCollection.Add()


ser.Name = "Utilization(%)"


ser.Caption = ser.Name


ser.Marker.Size = 4





' Set the Categories to the first field (YValues)in the


' RecordSetDef of the DataSource - dsc


ser.SetData c.chDimCategories, 0, 0





' Set the Values to the second field (XValues)in the


' RecordSetDef of the DataSource - dsc


ser.SetData c.chDimValues, 0, 1








' Set the tick label spacing depending on the number of points plotted


Set ax = cht.Axes(c.chAxisPositionBottom)


ax.TickLabelSpacing = cht.SeriesCollection(0).Points.Count / 10


End Sub





Sub Window_Onload()





Dim asSeriesNames(1)


Dim asCategories(3)


Dim aiSeries1(3)


Dim alSeries2(3)


Dim chConstants


Dim chtNewChart


Dim serUnitSales


Dim serDispInc


Dim axIncomeAxis





asSeriesNames(0) = "UnitSales"


asSeriesNames(1) = "Disposable Income"





asCategories(0) = "Item 1"


asCategories(1) = "Item 2"


asCategories(2) = "Item 3"


asCategories(3) = "Item 4"





aiSeries1(0) = 75


aiSeries1(1) = 84


aiSeries1(2) = 30


aiSeries1(3) = 94





alSeries2(0) = 14522


alSeries2(1) = 17321


alSeries2(2) = 9424


alSeries2(3) = 41782





Set chConstants = ChartSpace1.Constants





' Enagble the display of the legend.


ChartSpace1.HasChartSpaceLegend = True





' Add a new chart to Chartspace1.


Set chtNewChart = ChartSpace1.Charts.Add





' Specify that the chart is a column chart.


chtNewChart.Type = chConstants.chChartTypeLineMarkers





' Bind the chart to the arrays.


chtNewChart.SetData chConstants.chDimSeriesNames, chConstants.chDataLiteral, asSeriesNames


chtNewChart.SetData chConstants.chDimCategories, chConstants.chDataLiteral, asCategories





Set serUnitSales = chtNewChart.SeriesCollection(0)





serUnitSales.SetData chConstants.chDimValues, chConstants.chDataLiteral, aiSeries1





Set serDispInc = chtNewChart.SeriesCollection(1)





serDispInc.SetData chConstants.chDimValues, chConstants.chDataLiteral, alSeries2





' Ungroup the series.


serDispInc.Ungroup True





' Add a new value axis to the chart based on the values in the series.


Set axIncomeAxis = chtNewChart.Axes.Add(serDispInc.Scalings(chConstants.chDimValues))





' Place the axis on the right side of the chart.


axIncomeAxis.Position = chConstants.chAxisPositionRight





' Display the series as columns.


serDispInc.Type = chConstants.chChartTypeColumnClustered





End Sub





Private Sub UserForm_Initialize()


Dim varCategories As Variant


Dim varCloseValues As Variant


Dim varLoValues As Variant


Dim varHiValues As Variant


Dim chtStock As OWC.WCChart





varCategories = Array("1/3", "1/4", "1/5", "1/6", "1/7")


varCloseValues = Array(116.5625, 112.625, 113.8125, 110, 111.4375)


varLoValues = Array(112, 112.25, 109.375, 108.375, 107.4375)


varHiValues = Array(118.625, 117.125, 116.375, 113.875, 112.25)





' Adds a chart title.


With ChartSpace1


.HasChartSpaceTitle = True


.ChartSpaceTitle.Caption = "Microsoft Stock 1/3 - 1/7"


End With





' Adds a chart to the ChartSpace object.


Set chtStock = ChartSpace1.Charts.Add


chtStock.Type = chChartTypeStockHLC





' Specifies which fields in the recordset are values and labels.


chtStock.SetData chDimCategories, chDataLiteral, varCategories





With chtStock.SeriesCollection(0)


.SetData chDimCloseValues, chDataLiteral, varCloseValues


.SetData chDimHighValues, chDataLiteral, varLoValues


.SetData chDimLowValues, chDataLiteral, varHiValues


End With





' Specifies scaling for axis.


With chtStock.Axes(chAxisPositionLeft).Scaling


.Maximum = 120


.Minimum = 105


End With





' Specifies gridlines.


chtStock.Axes(chAxisPositionLeft).HasMinorGridlines = True


End Sub





Private Sub UserForm_Initialize()


Dim cnn As ADODB.Connection


Dim rst As ADODB.Recordset


Dim chtTopTen As OWC.WCChart


Dim strPath As String





Set cnn = New ADODB.Connection


Set rst = New ADODB.Recordset


strPath = "C:\Program Files\Microsoft Office\Office\Samples\NorthWind.mdb"


cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _


"Data Source=" & strPath





' Creates the recordset.


rst.CursorLocation = adUseClient


rst.CursorType = adOpenDynamic


rst.Open "SELECT DISTINCTROW TOP 10 [Products].[ProductName] " & _


"AS TenMostExpensiveProducts, Products.UnitPrice FROM Products " & _


"ORDER BY [Products].[UnitPrice] DESC;", cnn





' Connects the chart to the recordset and creates a title.


With ChartSpace1


.DataSource = rst


.HasChartSpaceTitle = True


.ChartSpaceTitle.Caption = "Ten Most Expensive Products"


End With





' Adds a chart to the ChartSpace object.


Set chtTopTen = ChartSpace1.Charts.Add





' Specifies which fields in the recordset are values and labels.


With chtTopTen


.HasLegend = True


.SetData chDimCategories, 0, rst.Fields(0).Name


.SetData chDimValues, 0, rst.Fields(1).Name


End With


End Sub





Private Sub UserForm_Initialize()


Dim cnn As ADODB.Connection


Dim rst As ADODB.Recordset


Dim chtTopTen As OWC.WCChart





Set cnn = New ADODB.Connection


Set rst = New ADODB.Recordset


cnn.Open "NorthWind"





rst.CursorLocation = adUseClient


rst.CursorType = adOpenDynamic


rst.Open "SELECT DISTINCTROW TOP 10 [Products].[ProductName] " & _


"AS TenMostExpensiveProducts, Products.UnitPrice FROM Products " & _


"ORDER BY [Products].[UnitPrice] DESC;", cnn





' Populates the spreadsheet with data from the recordset.


Spreadsheet1.CSVData = rst.GetString(adClipString, , ",")





' Adds a chart title.


With ChartSpace1


.HasChartSpaceTitle = True


.ChartSpaceTitle.Caption = "Ten Most Expensive Products"


End With





' Assigns the chart's data source to the spreadsheet.


ChartSpace1.DataSource = Spreadsheet1





' Adds a chart to the ChartSpace object.


Set chtTopTen = ChartSpace1.Charts.Add





' Specifies which fields in the recordset are values and labels.


With chtTopTen


.HasLegend = True


.SetData chDimCategories, 0, "A1:A10"


.SetData chDimValues, 0, "B1:B10"


End With


End Sub

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.