1 / 13

Working with Charts

Working with Charts. ISYS 562. 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.

colleen-anthony
Download Presentation

Working with Charts

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related