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

"Roger" wrote in message ...
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

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
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
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

matter of course


lines to free up memory? You should put them inside loops
that create
objects as well as at the end of subs.
regards
Paul

"Roger" wrote in
message ...
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
.

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.AutoScaleFo nt =
False
ActiveChart.Axes(xlValue).AxisTitle.AutoScaleFont = False
ActiveChart.Axes(xlCategory).TickLabels.AutoScaleF ont =
False
ActiveChart.ChartTitle.AutoScaleFont = False
ActiveChart.SeriesCollection(1).Trendlines
(1).DataLabel.AutoScaleFont = False

etc...


-----Original Message-----
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

"Roger" wrote in
message ...
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
.