Problems with ungroup

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

Hi,
I've coded a small xla (7 MB), but i got a mysterious
problem. The users are creating workbook with some tables
and a grafic sheet. In this grafic sheet, the xla insert
some diagrams. The diags are some textboxes and a
chartobject grouped to a shape. After some evaluations
(fill tables with values and formatting the diags) the
Workbook is damaged. After saving the workbook, I couldn't
open it. I analysed this and found, that the workbook get
sometimes damaged, when I ungroup one diag to get the
chartobject during the init of the worksheets by VBA code.
Before Diag.ungroup I activate the grafic sheet and select
the diag and bringtoFront and so on. This happens not
always, only sometimes and sometimes after 35 diags and
sometimes after 40 diags. Where is the problem????

thx
Roger
 
Hi,
You probably know all these but:
7mb is pretty big for an Add-In? How large are your code modules? 64Kb
seems to be the accepted maximum, so if they are bigger than that, try
rearranging your code into extra modules.
Have you used Rob Bovey's code cleaner to clean your project
(www.appspro.com). Easy to use and cuts out a lot of odd behavior from
your project.
Last thing I can think of is are you cleaning up your Set statements?
Sounds like you are creating a lot of objects. Do you have enough
Set Object = Nothing

lines to free up memory? You should put them inside loops that create
objects as well as at the end of subs.
regards
Paul
 
Ok,
I put the part where the Diagrams crashes into a new vba
project. The modul and the class are small enough. The
workbook has 43 diags. Diag 40 damage the workbook. I
thought, that this diag is damaged, but when I delete the
first 3 diags in the grafic sheet, the code runs and the
workbook is Ok after saving. When I delete only one Diag,
it also crashes by diag 41. Ok, there must be a maximun by
40 diag. No, the next workbook with this problem has this
border with 35 diags. Whats wrong ???? Ok its Excel ;-)

thx for response

xlRoger

-----Original Message-----
Hi,
You probably know all these but:
7mb is pretty big for an Add-In? How large are your code
Click to expand...
modules? 64Kb

Huh, thats new
seems to be the accepted maximum, so if they are bigger than that, try
rearranging your code into extra modules.
Have you used Rob Bovey's code cleaner to clean your
Click to expand...
project

nice tool, my xla has only 5 MB
(www.appspro.com). Easy to use and cuts out a lot of odd behavior from
your project.
Last thing I can think of is are you cleaning up your Set statements?
Sounds like you are creating a lot of objects. Do you have enough
Set Object = Nothing
Click to expand...

matter of course
 
Ensure to turn off font autoscaling on all chart text
objects. Somehow Excel runs out of memory when > 30
chart are created with autoscale fonts enabled:

ActiveChart.Axes(xlCategory).AxisTitle.AutoScaleFont =
False
ActiveChart.Axes(xlValue).AxisTitle.AutoScaleFont = False
ActiveChart.Axes(xlCategory).TickLabels.AutoScaleFont =
False
ActiveChart.ChartTitle.AutoScaleFont = False
ActiveChart.SeriesCollection(1).Trendlines
(1).DataLabel.AutoScaleFont = False

etc...
 
Back
Top