CODEWITHSUNDEEP
vbaexcelcharts
JazZeus
JazZeus

Reputation: 180

How do I get charts.add command to deliver an empty chart?

The command charts.add in VBA adds a new chart object to the workbook. I think hitting F11 in Excel does exactly the same.

The problem is, that this command uses the data around the at that point selected worksheet and cell to populate the chart. If there is no data or no usable data, it returns an empty chart.

My question is: "How can I force the command to deliver an empty chart?". I intend to populate the chart with VBA code thereafter.

A simple answer to the question is to create a new empty worksheet and select cell A1 on that worksheet and then create a new chart, but that is a rather ugly solution. Any help on elegant solutions would be appreciated.

Upvotes: 4

Views: 15277

Answers (5)

TheDiesel
TheDiesel

Reputation: 51

I had the same issue. I tried using the ActiveChart.ChartArea.Clear property and it resulted in crashes. Then I tried ActiveChart.ChartArea.ClearContents after adding the chart and it gave me the desired result, which was a blank chart that I could add series to.

Upvotes: 5

ron
ron

Reputation: 995

i like the ActiveChart.ChartArea.Clear answer.

I had been doing the following, back when i did a macro in excel 2003. It will manually delete all series in the selected chart.

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.Location Where:=xlLocationAsNewSheet

' manually delete every series that may be in a new chart
On Error GoTo 30
   yy = ActiveChart.SeriesCollection.Count
   For xx = yy To 1 Step -1
      ActiveChart.SeriesCollection(xx).Delete
30 Next xx

Upvotes: 1

Jon Peltier
Jon Peltier

Reputation: 6063

Excel tries to populate a brand new chart with the selected data, or with the block of data that contains the active cell.

Select a blank cell in the middle of an unused range. Without data to plot, Excel will insert a blank chart.

Upvotes: 2

PavDub
PavDub

Reputation: 71

I have just spent a pretty tough time with solving this. Whatever I did, the bloody excel took some data from somewhere, which was really anoying. The .ChartArea.Clear method resulted in a crash whenever I manipulated the chart afterwards (even filled with new series)

Here is my final solution from a real working project (a fragment, sorry for some user-specific variables)

Dim Chrt as Chart

' This is "the elegant" method
' Parameters of Add do not actually matter, we will specify all details later
Set Chrt = MySheet.ChartObjects.Add(0, 0, 100, 100).Chart

' And here we start to fill the empty (!) Chart with some useful series... :)
With Chrt

  ' Specifying chart type
  .ChartType = xlXYScatterLinesNoMarkers

  ' Positioning (Page is a range - fragment of my code)
  With .Parent
    .Top = Page(3, 1).Top
    .Left = Page(3, 1).Left
    .Width = Page.Width
    .Height = (Page.Height - (Page(4, 1).Top - Page(1, 1).Top)) ' / 2
  End With

  ' Adding a new serie
  With .SeriesCollection.NewSeries
    .Name = "D" & Work.Cells(iFileRow, 2)
    .XValues = Range(MySheet.Cells(2, 1), MySheet.Cells(DataEnd, 1))
    .Values = Range(MySheet.Cells(2, 10), MySheet.Cells(DataEnd, 10))

    .MarkerStyle = -4142

    ' Formating the series
    With .Format.Line

    ...

Upvotes: 3

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Try to add additional line which will immediately remove data right after the chart appears:

Charts.Add 
ActiveChart.ChartArea.Clear

EDIT Alternative solution, but this will jump back to data sheet:

'select last cell in the sheet which is usually empty
Dim tmpSel As Range
Set tmpSel = Selection
Cells(Rows.Count, Columns.Count).Select
'add chart
Charts.Add
'back to base sheet and select range previously selected
tmpSel.Parent.Activate
tmpSel.Select

Upvotes: 5

Related Questions