Add a new chart *without* default data?

  • Thread starter Thread starter Lars Uffmann
  • Start date Start date
L

Lars Uffmann

Hi everyone!

I have a workbook in Excel 2003 with 2 worksheets with some ~6000 rows
each. Now I want to add some charts. On the first one, this seems to
work fine, and I set a bunch of properties (including the data source)
manually. Then when I add the 2nd chart, it predraws the whole thing
with all 6000 lines of data, even though I haven't yet defined any data
source. It just takes some default source and paints it. Of course this
is *very* annoying and unnecessarily slowing the whole thing down. Is
there any way to configure Excel so that a new chart will be a "naked"
chart, without attempting to display *any* data until I manually set up
a data source?

Grateful for any tips!

Lars
 
Apparently, when you create a new chart, this useless Excel copies the
data source settings from the last chart you created. So the workaround
(when you start with zero charts) is: Create as many charts as you need
(empty) before doing anything to them.

*Then* fill them with data and set their attributes as you would like to
have them.

Very annoying and very very poor thinking on M$ side. I am amazed at how
they could possibly create such a shitty software when they have done
such a powerful and infinitely more useful tool as Access.

And no, I am not voluntarily using Excel, I am in the state of migrating
someone else's application to Access or something better.

Best Regards,

Lars
 
Hi,

You could try making sure the active cell is not within your data set.

Cheers
Andy
 
Hi Andy,

Andy said:
You could try making sure the active cell is not within your data set.
Click to expand...
Thank you, that did help me to understand where Excel is getting it's
data from. And it works. The "active cell" after I add and manipulate a
chart is the complete source data range of the current chart, so that's
why when adding a new chart then, I get this chart full of unwanted data.

Sadly, your tip does not solve my problem, since I want to do everything
in the background, i.e. I want to completely avoid that the current
selection or the current focus have any influence on the behaviour of my
VBA script. This is so that the user doesn't mess up the script with an
accidental click on some cell/sheet in the middle of processing - and I
do want to allow the user to maybe continue working on a different Excel
sheet. I'll just go with my workaround unless something better comes up.

Thank you!

Lars
 
Hi,

You did mention you where using code. Try this to create a blank chart
sheet.

Sub x()

Dim chtTemp As Chart

Set chtTemp = Sheet1.ChartObjects.Add(1, 1, 200, 200).Chart
chtTemp.Location Where:=xlLocationAsNewSheet

End Sub

Cheers
Andy
 
Hi Andy!

Andy said:
You did mention you where using code. Try this to create a blank chart
sheet.
Set chtTemp = Sheet1.ChartObjects.Add(1, 1, 200, 200).Chart
chtTemp.Location Where:=xlLocationAsNewSheet
Click to expand...


Thanks a lot! This is 95% of the solution I was looking for, and
probably the best Excel can do :)
Unfortunately, the Location method creates a copy of the chart object
and renders the old chart object returned by Add().Chart
obsolete/invalid. Also, the documentation does not say where the
"xlLocationAsNewSheet" inserts the chart. I *assume* it will always be
at Workbook.Charts(Workbook.Charts.Count + 1) and of course increase the
Charts Count. So I have to get a new handle to my chart by accessing
Workbook.Charts(Workbook.Charts.Count) after moving the chart.

What I did in the end was this:

Dim dummyWs As WorkSheet
Dim myChart As Chart

Set dummyWs = ActiveWorkbook.Worksheets.Add

dummyWs.ChartObjects.Add(1, 50, 100, 50).Chart.Location
xlLocationAsNewSheet, "my chart in a new sheet"
Set myChart = ActiveWorkbook.Charts(ActiveWorkbook.Charts.Count)

' do stuff with myChart

This gets the job done, so thank you very much!

Best Regards,

Lars
 
My standard approach to losing the reference after changing the
location is

set chtTemp=chtTemp.Location(Where:=xlLocationAsNewSheet)

In your case, what I would do is just use the Charts.Add method. Also,
I have learnt not to trust Excel's decisions about what it puts in a
new chart. So, I always go through and delete any existing series.

Option Explicit

Sub doChart()
Dim aChart As Chart
Set aChart = Charts.Add()
With aChart.SeriesCollection
Do While .Count > 0: .Item(1).Delete: Loop
End With
End Sub
 
Tushar,
My standard approach to losing the reference after changing the
location is
set chtTemp=chtTemp.Location(Where:=xlLocationAsNewSheet)
Click to expand...

I might try again, but I tried this and I could swear that for the Chart
object, .Location did not return a new reference to a Chart object -
sadly.
In your case, what I would do is just use the Charts.Add method. Also,
I have learnt not to trust Excel's decisions about what it puts in a
new chart. So, I always go through and delete any existing series.
Click to expand...

You kinda missed the beginning of the discussion :) I decided to
explicitely *not* use the Charts.Add method because of what Excel puts
in that new chart. Andy's solution actually enables you to create a
*really* empty chart (without even a Series in it).

Thank you for your additions though!

Best Regards,

Lars
 
Lars,

I've been using the Location method to get a reference to the new
object for several versions of Excel including 2007. Not to mention,
as is almost always the case, I tested the code before posting. {grin}
 
Good morning Tushar,
I've been using the Location method to get a reference to the new
object for several versions of Excel including 2007. Not to mention,
as is almost always the case, I tested the code before posting. {grin}
Click to expand...

You were right. I have no idea what I was trying before that did not
work, but the Location object does indeed return the correct reference
to the new chart object, as you said. I can do without my previous
workaround now, thanks a lot! :)

Best Regards,

Lars
 
Back
Top