Resetting the chart count

[Ben]

Hi,
I've written some code that automatically generates about 30 small graphs.
Periodically these have to be deleted and regenerated. This could happen as
many as 20 times a day. I've noticed that each new chart is assigned a number
which increments by 1 but it does not reset when I close Excel. Although it
does not affect the program, I'm concerned that within a year the count may
grow to a ridiculously large number. Is there any way I can reset the count
back to zero when i close the program.

Thank you

 

[joel]

From my experience with excel 2003 restarts numbering when excel close
and reopens. If I have sheets : sheet2, sheet3, sheet4 in a workbook
and close excel then reopen excel. I next add a new sheet it get
assigned sheet1. If I add another sheet it will get assigned sheet5.
The graphs work the same way.

I'm not sure if you are really deleting the graphs or not closin
excel. Closing the workbook without closing excel the numberin
probably will continue to count to higher numbers.

 

[Peter T]

The Sheet's object id counter increments each time any sort of object is
added to the sheet, it doesn't decrement when items are deleted. There's no
problem if the id increases to 64k, even after then for most situations
there's no problem.

The only way to reset the object counter is to delete all objects (not only
chartobjects), eg
Activesheet.Drawingobjects.Delete
then save, close and reopen the file.

Maybe in your situation if/when all the charts need to be deleted, also
delete the sheet and add a new one. But as I say, although intuitively the
high number looks problematic, in practice it is very unlikely ever to
become so.

Regards,
Peter T

 

[Ben]

Thank you for your comments but there seems to be another related problem.
The line of code Charts.add is causing a compile error whenever it is
encountered. The error message is "Method or data member not found"
Here is the snippet of code which I've extracted to use as a test

Sub Testgraph()
Range("E6:E22").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("30Graphs").Range("E6:E22")
ActiveChart.Location Where:=xlLocationAsObject, Name:="30Graphs"
Range("H10").Select
End Sub

If I copy the exact code onto a new workbook it will work without a compile
error.

Has the first workbook been corrupted in some way or have I just added and
deleted too many charts for it to handle ?

 

[Peter T]

I really don't know what the problem is there.
Charts.Add
This adds a Chart-sheet (later changed to an embedded chart), how many
chart-sheets do you have in the workbook?
Did this problem suddenly start happening after changing something else?

FWIW there is a different and I think better way to add the chart which
might avoid the problem, however best first to clear this up.

Regards,
Peter T

 

[Ben]

The problem with Charts.add is solved. The reason the code did not run is
that I renamed the module name from the default "Module1" to "Charts". This
must be a reserved word because as soon as it encountered the line
"Charts.add" it would stop with a compile error. Renaming the module from
"Charts" to "DoGraphs" has solved the problem but it took hours to discover
that.
Thanks for your help. You suggested that there is a better alternative to
Charts.add. I would be very interested in that.

 

[Peter T]

I'm glad you found that, we could have gone on a long time trying to track
that one down! FWIW if the problem could also have been resolved had you
done -
ActiveWorkbook.Chart.Add

But best never to use any Keyword either as a module or a variable name.
Another thing to keep in the back of your mind never use the same name as a
Defined Name and a procedure name.

Here's another way to add your chart

Sub Testgraph2()
Dim rngPos As Range
Dim ws As Worksheet
Dim chtObj As ChartObject
Dim cht As Chart

Set ws = ActiveWorkbook.Worksheets("30Graphs")
Set rngPos = ws.Range("F6:M21")

With rngPos
Set chtObj = ws.ChartObjects.Add( _
.Left + 9, .Top + 3, _
.Width, .Height)
End With

Set cht = chtObj.Chart
cht.SetSourceData ws.Range("E6:E22")
cht.ChartType = xlLineMarkers

End Sub


Note there's no need to select anything, even the sheet does not need to be
active. However if the workbook is not active change Activeworkbook.etc to
Workbooks("wbName.xls").etc. To assist with more changes, type "cht." and
look for intellisense after the dot.

Regards,
Peter T

 

[Peter T]

Typo -

I'm glad you found that, we could have gone on a long time trying to track
that one down! FWIW if the problem could also have been resolved had you
done -
ActiveWorkbook.Chart.Add

<snip>

should read
ActiveWorkbook.Charts.Add

Peter T

 

[Ben]

Thank you so much for your help

Typo -

<snip>

should read
ActiveWorkbook.Charts.Add

Peter T


.