Prev: Excel Web Query
Next: distribution problems; users don't have this and misc
From: Spiggy Topes on 17 Dec 2009 18:55
Here's the scenario, in Excel 2007 (of course)I have a sheet with 365
rows, 253 columns. I have a macro that's going to build a chart based
on SOME of those columns. My code says

Set oActChart = Charts.Add

Used to work just fine in Excel 2003, now it takes 2.5 minutes and
sucks up half a gig of memory, because it thinks I want to chart
everything on the data sheet. I can get around this by selecting a
small range on the data sheet, but I'd far prefer to be able to tell
Excel not to try to anticipate what I want to do. Is there any simple
way - other than that above - to do this?
From: Peter T on 18 Dec 2009 05:35
Maybe something like this

Sub test()
Dim rSource As Range, rCol As Range
Dim cht As Chart
Dim sr As Series

Set rSource = ActiveSheet.Range("A1:D10")

Call getNewChart(cht)
cht.SetSourceData rSource

' add more series and format the chart, eg
' Set sr = cht.SeriesCollection.NewSeries
' etc

End Sub

Sub getNewChart(chtSht As Chart)
Dim rOrig As Range
Set rOrig = ActiveWindow.VisibleRange
Application.EnableEvents = False
With ActiveSheet
Application.Goto .Cells(rOrig.Row, .Cells.Columns.Count), True
Set chtSht = Charts.Add
End With
Application.Goto rOrig(1), True

chtSht.Activate
Application.EnableEvents = True
End Sub

point being, ensure the activecell is empty and not part of some other
larger current-region.

Regards,
Peter T

"Spiggy Topes" <ubik(a)shaw.ca> wrote in message
news:d255fb05-94db-46c9-bc87-94849e12475d(a)15g2000prz.googlegroups.com...
> Here's the scenario, in Excel 2007 (of course)I have a sheet with 365
> rows, 253 columns. I have a macro that's going to build a chart based
> on SOME of those columns. My code says
>
> Set oActChart = Charts.Add
>
> Used to work just fine in Excel 2003, now it takes 2.5 minutes and
> sucks up half a gig of memory, because it thinks I want to chart
> everything on the data sheet. I can get around this by selecting a
> small range on the data sheet, but I'd far prefer to be able to tell
> Excel not to try to anticipate what I want to do. Is there any simple
> way - other than that above - to do this?


 | 
Pages: 1
Prev: Excel Web Query
Next: distribution problems; users don't have this and misc