Download
working with charts n.
Skip this Video
Loading SlideShow in 5 Seconds..
Working with Charts PowerPoint Presentation
Download Presentation
Working with Charts

Working with Charts

125 Vues Download Presentation
Télécharger la présentation

Working with Charts

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Working with Charts ISYS 562

  2. Chart Locations • As an embedded object on a worksheet: • Worksheet • ChartObjects • ChartObject: A container for a chart • Chart • Chart properties and methods • In a separate chart sheet • Workbook • Charts or Sheet • Chart • Chart properties and methods

  3. Referring to a Chart • In a worksheet: • MsgBox(Worksheets(“sheet1”).ChartObjects(1).ChartTitle.Text) • In a chart sheet: • MsgBox(thisworkbook.Sheets("chart1").chartTitle.Text)

  4. Chart Example: Comparing the Charges of Three Service Plans

  5. Recording Chart Macro Sub ChartMC() ' ' ChartMC Macro ' Macro recorded 11/16/2005 by cob Charts.Add ActiveChart.ChartType = xlLine ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D20"), PlotBy _ :=xlColumns ActiveChart.SeriesCollection(1).Delete ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R2C1:R20C1" ActiveChart.SeriesCollection(2).XValues = "=Sheet1!R2C1:R20C1" ActiveChart.SeriesCollection(3).XValues = "=Sheet1!R2C1:R20C1" ActiveChart.Location Where:=xlLocationAsNewSheet With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Plans Comparsion" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Hours Used" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Charges" End With End Sub

  6. Chart’s SeriesCollection • A collection of data Series for Y axis • Series object: • XValues property: the series for X-axis

  7. Adding a New Data Series to an Existing Chart activechart.SeriesCollection.add source:=Worksheets("sheet1").Range("e1:e20")

  8. Deleting a Data Series from An Existing Chart activechart.SeriesCollection(1).delete

  9. SeriesCollection Demo

  10. Private Sub CommandButton1_Click() 'ActiveChart.SeriesCollection.Add Source:=Worksheets("sheet1").Range(RefEdit1.Text) ThisWorkbook.Sheets("chart1").SeriesCollection.Add Source:=Range(RefEdit1.Text) ListBox1.Clear Dim i As Integer For i = 1 To ThisWorkbook.Sheets("chart1").SeriesCollection.Count ListBox1.AddItem (ThisWorkbook.Sheets("chart1").SeriesCollection(i).Name) Next i End Sub Private Sub CommandButton2_Click() Dim i As Integer For i = 1 To ThisWorkbook.Sheets("chart1").SeriesCollection.Count If ThisWorkbook.Sheets("chart1").SeriesCollection(i).Name = ListBox1.Value Then ThisWorkbook.Sheets("chart1").SeriesCollection(i).Delete Exit For End If Next i ListBox1.Clear For i = 1 To ThisWorkbook.Sheets("chart1").SeriesCollection.Count ListBox1.AddItem (ThisWorkbook.Sheets("chart1").SeriesCollection(i).Name) Next i End Sub Private Sub UserForm_Initialize() Dim i As Integer For i = 1 To ThisWorkbook.Sheets("chart1").SeriesCollection.Count ListBox1.AddItem (ThisWorkbook.Sheets("chart1").SeriesCollection(i).Name) Next i End Sub

  11. An MIS Query Downloaded from a Server

  12. Macro to create a column chart to compare the projected sales and actual sales Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Sheet4").Range("A1:E5"), PlotBy:= _ xlColumns ActiveChart.SeriesCollection(3).Delete ActiveChart.SeriesCollection(1).XValues = "=Sheet4!R2C2:R5C2" ActiveChart.SeriesCollection(2).XValues = "=Sheet4!R2C2:R5C2" ActiveChart.Location Where:=xlLocationAsNewSheet With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Comparison of Actual Sales with Projection" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dollar Amount" .Axes(xlValue, xlPrimary).HasTitle = False End With

  13. Rewrite the macro to generalize the worksheet name Sub MyChart() Dim r As Range ActiveSheet.Range("a1").Select Set r = ActiveCell.CurrentRegion Dim sheetName As String sheetName = ActiveSheet.Name Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=r, PlotBy:=xlColumns ActiveChart.SeriesCollection(3).Delete ActiveChart.SeriesCollection(1).XValues = "=" & sheetName & "!R2C2:R5C2" ActiveChart.SeriesCollection(2).XValues = "=" & sheetName & "!R2C2:R5C2" ActiveChart.Location Where:=xlLocationAsNewSheet With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Comparison of Actual Sales with Projection" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dollar Amount" .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub