I have a macro that runs several times and each time the macro restarts,
Excel's VM Size (as displayed in Task Manager) continues to increase.

When the Pagefile usage reaches 2.2 GB, OFFLB.EXE starts up and requires
that Excel be restarted.

After the macro finishes each time, is there a way to programmatically
flush out Excel's usage of Virtual Memory ?

And is there a way to determine what Excel is storing in the Pagefile -
the workbook is only 52 KB ?



- Ronald K.

kittronald formulated on Monday :
I have a macro that runs several times and each time the macro restarts,
Excel's VM Size (as displayed in Task Manager) continues to increase.

When the Pagefile usage reaches 2.2 GB, OFFLB.EXE starts up and requires
that Excel be restarted.

After the macro finishes each time, is there a way to programmatically
flush out Excel's usage of Virtual Memory ?

And is there a way to determine what Excel is storing in the Pagefile -
the workbook is only 52 KB ?



- Ronald K.

Is your macro creating objects or static values that remain in memory?
Can you post the macro?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 06/09/2011 01:00, kittronald wrote:
I have a macro that runs several times and each time the macro restarts,
Excel's VM Size (as displayed in Task Manager) continues to increase.

Sounds like it is allocating memory and never releasing it. Orphanned
graphics objects and images tend to do this sort of thing.

When the Pagefile usage reaches 2.2 GB, OFFLB.EXE starts up and requires
that Excel be restarted.

After the macro finishes each time, is there a way to programmatically
flush out Excel's usage of Virtual Memory ?

And is there a way to determine what Excel is storing in the Pagefile -
the workbook is only 52 KB ?

Something large that your macro has defined or created but never
released. Each run slams another one into the page file.

Regards,
Martin Brown

Garry and Martin,

Think I figured it out, although I'm not sure if this is performing as
designed or whether it's an Excel bug.

The macro in question fills a formula in A1 to CV10000 and then saves
the worksheet.

When the macro runs again, it changes a function name in A1 and again
fills A1 to CV10000.

Excel's ever increasing VM Size appears to be related to the fill
process.

Filling over cells appears to abandon the data paged to VM.

However, if I configure the macro to first select all cells except A1
and clear their contents, much of the paged data is recovered from VM.

Does this make sense ?



- Ronald K.

kittronald laid this down on his screen :
Garry and Martin,

Think I figured it out, although I'm not sure if this is performing as
designed or whether it's an Excel bug.

The macro in question fills a formula in A1 to CV10000 and then saves the
worksheet.

When the macro runs again, it changes a function name in A1 and again
fills A1 to CV10000.

Excel's ever increasing VM Size appears to be related to the fill
process.

Filling over cells appears to abandon the data paged to VM.

However, if I configure the macro to first select all cells except A1 and
clear their contents, much of the paged data is recovered from VM.

Does this make sense ?



- Ronald K.

Try not filling and just enter the formula directly to the entire range
in one step:

Range("A1:CV10000").Formula = "=blah"

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

When using ...

Range("A1:CV10000").Formula = "=blah"

Is there a way to reference a formula in a cell ?

I looked in the Excel Developer Reference Help, but it appears that VBA
is filling the range with the formula's result instead of the formula.

For example, Sheet2!A1 contains =SUM(Sheet2!B1,Sheet2!C1) and has a name
of "Fill_Formula".

Sheet1.Range("A1:CV10000").Formula = <the formula in the name
"Fill_Formula"

... which would evaluate to ...

Sheet1.Range("A1:CV10000").Formula = "=SUM(Sheet2!B1,Sheet2!C1)"

When the range on Sheet1 is filled with this formula, would the formula
also auto adjust for relative row and column references ?




- Ronald K.

kittronald formulated on Wednesday :
Garry,

When using ...

Range("A1:CV10000").Formula = "=blah"

Is there a way to reference a formula in a cell ?

I looked in the Excel Developer Reference Help, but it appears that VBA
is filling the range with the formula's result instead of the formula.

For example, Sheet2!A1 contains =SUM(Sheet2!B1,Sheet2!C1) and has a name
of "Fill_Formula".

Sheet1.Range("A1:CV10000").Formula = <the formula in the name
"Fill_Formula"

... which would evaluate to ...

Sheet1.Range("A1:CV10000").Formula = "=SUM(Sheet2!B1,Sheet2!C1)"

When the range on Sheet1 is filled with this formula, would the formula
also auto adjust for relative row and column references ?

Yes! As long as the formula uses relative refs it will adjust
appropriately for row/col, *IF* the defined name formula is properly
constructed. For example,

I have an expenses ledger in a project that collects amounts in each
month column based on transaction date entered in ColA. Between ColA
and the 1st month col (Jan) there's several distribution cols for what
the expense is for as well as the taxes associated. The formula in the
month cols is the same (=PeriodAmount) in every cell and can go on for
1000's of rows. It adjusts appropriately for the 12 month cols and for
every row of the ledger.




- Ronald K.

Ok, so then instead of "=blah"...

Sheet1.Range("A1:CV10000").Formula = "=Fill_Formula"

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 06/09/2011 23:14, kittronald wrote:
Garry and Martin,

Think I figured it out, although I'm not sure if this is performing as
designed or whether it's an Excel bug.

The macro in question fills a formula in A1 to CV10000 and then saves
the worksheet.

When the macro runs again, it changes a function name in A1 and again
fills A1 to CV10000.

Excel's ever increasing VM Size appears to be related to the fill
process.

Mickeysoft XL is *EXTREMELY* bad about orphanned resources when certain
operations are performed that copy new entities over existing ones the
old data storage is not always correctly deallocated. In other words the
system loses track of it and doesn't set it unused so your workset and
swap file grows in an unbounded fashion each time around the loop.

It would probably work a lot better if you did a delete operation to
free all allocated cells entirely at the start of your macro.

Cells.Delete

The worst faults of this kind I have seen involved corporate documents
created in Word2002/3 migrated onto Word2007 and containing a series of
images that were regularly updated by drag and drop. The record so far
was a 60MB file containing just 5MB of real data and 55MB of orphanned
dross. I would argue that these are quite serious bugs in Office but you
would be hard pushed to find any reference to these faults in MSKB.

Filling over cells appears to abandon the data paged to VM.

However, if I configure the macro to first select all cells except A1
and clear their contents, much of the paged data is recovered from VM.

Does this make sense ?

Yes although Cells.Delete would be quicker.
as would assigning the formula directly to the destination range.
I do have to wonder why anyone would stuff a huge range with the same
formula and how slow the runtime for this monstrosity ends up.

Regards,
Martin Brown

Martin Brown wrote :
On 06/09/2011 23:14, kittronald wrote:
Garry and Martin,

Think I figured it out, although I'm not sure if this is performing as
designed or whether it's an Excel bug.

The macro in question fills a formula in A1 to CV10000 and then saves
the worksheet.

When the macro runs again, it changes a function name in A1 and again
fills A1 to CV10000.

Excel's ever increasing VM Size appears to be related to the fill
process.

Mickeysoft XL is *EXTREMELY* bad about orphanned resources when certain
operations are performed that copy new entities over existing ones the old
data storage is not always correctly deallocated. In other words the system
loses track of it and doesn't set it unused so your workset and swap file
grows in an unbounded fashion each time around the loop.

It would probably work a lot better if you did a delete operation to free all
allocated cells entirely at the start of your macro.

Cells.Delete

The worst faults of this kind I have seen involved corporate documents
created in Word2002/3 migrated onto Word2007 and containing a series of
images that were regularly updated by drag and drop. The record so far was a
60MB file containing just 5MB of real data and 55MB of orphanned dross. I
would argue that these are quite serious bugs in Office but you would be hard
pushed to find any reference to these faults in MSKB.

Yep, I can confirm the same nonsense! I had a client send me 4 manuals
as Word docs totalling 428MB that were to be converted to e-manuals. I
redid them in Excel and ended up with a single file that was 4.25MB.
The manuals contained tonnes of images, which is what I account the
gross file size to. Unbelievable 'bloat'! The 4 manuals totalled 2.2MB
in searchable e-manual format.

Filling over cells appears to abandon the data paged to VM.

However, if I configure the macro to first select all cells except A1
and clear their contents, much of the paged data is recovered from VM.

Does this make sense ?

Yes although Cells.Delete would be quicker.
as would assigning the formula directly to the destination range.
I do have to wonder why anyone would stuff a huge range with the same formula
and how slow the runtime for this monstrosity ends up.

It's not that uncommon to see this where the formulas auto-adjust for
row/col relativity. It would be prudent to turn automatic calculation
off while the formulas are being placed, then turn it back on
afterward. Another prudent thing to do is disable/enable events in a
similar fashion.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Martin,

Thanks for sharing your real world experience.

At least it's not something anomalous that can't be duplicated by
others.

The reason the macro is filling A1 to CV10000, is to support the
formula's cell addresses to auto adjust for relative column and row
references.

Prior to each fill process, the following appears at the top of the
macro:

Application.Goto Reference:="Clear_Formula_Range"
Selection.ClearContents

What is the difference between "Selection.ClearContents" and
"Cells.Delete" ?



- Ronald K.

kittronald was thinking very hard :
Martin,

Thanks for sharing your real world experience.

At least it's not something anomalous that can't be duplicated by others.

The reason the macro is filling A1 to CV10000, is to support the
formula's cell addresses to auto adjust for relative column and row
references.

Prior to each fill process, the following appears at the top of the
macro:

Application.Goto Reference:="Clear_Formula_Range"
Selection.ClearContents

What is the difference between "Selection.ClearContents" and
"Cells.Delete" ?



- Ronald K.

Why not just...

Range("Clear_Contents_Range").ClearContents

...when selecting anything is *RARELY* necessary?

Also, why are you still filling? Why not just...

Range("Clear_Contents_Range").Formula = "=blah"

...without ClearContents?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 07/09/2011 22:28, kittronald wrote:
Martin,

Thanks for sharing your real world experience.

At least it's not something anomalous that can't be duplicated by
others.

The reason the macro is filling A1 to CV10000, is to support the
formula's cell addresses to auto adjust for relative column and row
references.

Prior to each fill process, the following appears at the top of the
macro:

Application.Goto Reference:="Clear_Formula_Range"
Selection.ClearContents

What is the difference between "Selection.ClearContents" and
"Cells.Delete" ?

I am down to guessing now.

There is a fairly good chance that Cells.Delete which zaps the entire
worksheet was properly tested for memory leaks. Nothing beats doing the
experiment. I could easily be wrong and it may also fail :(

It is clear from your experience above that .ClearContents leaves a load
of orphanned rubble allocated somewhere. I had assumed originally that
you were just refilling the same range again and again in a loop.

I have seen a few similar unbounded growth of spreadsheet behaviours and
they usually share a lot of similarities.

Regards,
Martin Brown