Creating "dead" chart with VBA

Posted by Denis Kertz on March 21, 2001 3:25 PM

I'm trying to create a "dead" chart with Excel VBA where the chart's data is actual values and not linked to the spreadsheet where the data values were stored. Supposedly this should work (Excel 97 SR-2):

ValArr = Range(...) # vals like 97.123456789
WkArr = Range(...) # dates like 3/21/2001
With ActiveChart.SeriesCollection.NewSeries
.XValues = WkArr
.Values = ValArr
End With

I get an "Unable to set XValues property of Series Class" error. I found a .Values assignment workaround when I truncated the many decimal places to only 4 (97.1234) but my .XValues assignment fails when I use more than about 12 dates (my example has 23 values/dates). I have tried converting the dates explicitly to dates (CDate) and strings (CStr) but nothing works. I believe this is a bug and am looking for a workaround. Any help or suggestions?

Posted by David Hawley on March 21, 2001 6:32 PM

Hi Denis

Try creating a chart that is reading from a range then copy it as a picture and delete the original. Something like:

ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:= _
xlPicture
ActiveWindow.Visible = False
Windows("Book2.xls").Activate
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Delete

Haven't tested this, but should put you on the right track.

Dave

OzGrid Business Applications

Posted by Denis on March 22, 2001 7:41 AM

Unfortunately, I need this "dead" chart to be a dead chart in Excel. I'm pretty sure I could copy it as a picture but then it's no longer an Excel chart. Sigh...

Denis