Purging Excel's VM Usage

K

kittronald

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

GS

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

Martin Brown

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
 
K

kittronald

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

GS

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

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.

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
 
G

GS

Martin Brown wrote :
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.
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.
 
K

kittronald

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

GS

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

kittronald

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

GS

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

Martin Brown

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
 
K

kittronald

Garry,

Apparently, I'm doing something wrong.

Here's an example:

Named Ranges:

$B$2:$B$4=Fill_Data

$A$7=Fill_Formula


Macro code:

Sheet1.Range("Fill_Data").Formula = "=Fill_Formula"

A B C
D
1 Item Total_Inventory Inventory_Store1
Inventory_Store2
2 Apple 1
4
3 Orange 2
5
4 Grape 3
6
5
6
7 =SUM(C2,D2)

****************************************

When the macro runs, B2:B4 is filled with the values 5,5,5 instead of
5,7,9.

How does the macro fill process account for relative row and column
differences ?




- Ronald K.
 
G

GS

kittronald brought next idea :
Garry,

Apparently, I'm doing something wrong.

Here's an example:

Named Ranges:

$B$2:$B$4=Fill_Data

$A$7=Fill_Formula


Macro code:

Sheet1.Range("Fill_Data").Formula = "=Fill_Formula"

A B C D
1 Item Total_Inventory Inventory_Store1
Inventory_Store2
2 Apple 1 4
3 Orange 2 5
4 Grape 3 6
5
6
7 =SUM(C2,D2)

****************************************

When the macro runs, B2:B4 is filled with the values 5,5,5 instead of
5,7,9.

How does the macro fill process account for relative row and column
differences ?

The $ symbol is what makes refs ABSOLUTE, so...

$A$7 is fully absolute
$A7 is column-absolute/row-relative
A$7 is column-relative/row-absolute
A7 is fully relative

$B$2:$B$4 is a range named "Fill_Data". It's fully absolute.
$A$7 is a range name "Fill_Formula". It's fully absolute.

To give a formula a defined named that applies to your scenario here...

1. Select B2
2. Open the DefineName dialog
3. Enter the following in the Name box:
'Sheet1'!TotalInventory
..where 'Sheet1' needs to be the sheetname this will be scoped
to.
4. Enter the following in the RefersTo box:
=SUM($C2:$D2)
..where this is locked to cols C/D
..but will auto-adjust for the row where being used.
5. In B2:B4 enter the following:
=TotalInventory

HTH
 
K

kittronald

Garry,

Ok, what I was doing wrong was entering the formula in a cell and naming
it "Fill_Formula" instead of entering the formula in Fill_Formula's Refers
to: field in the Name Manager.

Your fill process works now and is very fast ! 8)

Unfortunately, storing the formula in the Name Manager complicates a
process we touched on in the thread "Iterating through a ComboBox's values".

A macro called Macro_Automate would iterate through a list of ComboBox
values and end when the last value was evaluated.

In each iteration, the Macro_Change_Function would change the function
in the Fill_Formula to the next value in the ComboBox (i.e., from SUM to
PRODUCT).

Before when I was storing the Fill_Formula in a cell, the
Macro_Change_Function was changing the formula's function via:

With Sheets("Data")
x = .Range("First_Data_Cell").Formula
y = Application.Substitute(x,
Sheets("Settings").Range("Current_Function"),
Sheets("Settings").Range("Selected_Function"))
.Range("First_Data_Cell").Formula = y
End With

Now that the Fill_Formula is stored in the Name Manager, I'm not sure
how I can change the Fill_Formula function programmatically.

Is there a VBA method to modify a name's Refers to: formula similar to
the Application.Substitute code above ?




- Ronald K.
 
G

GS

kittronald expressed precisely :
Garry,

Ok, what I was doing wrong was entering the formula in a cell and naming
it "Fill_Formula" instead of entering the formula in Fill_Formula's Refers
to: field in the Name Manager.

Your fill process works now and is very fast ! 8)

Unfortunately, storing the formula in the Name Manager complicates a
process we touched on in the thread "Iterating through a ComboBox's values".

A macro called Macro_Automate would iterate through a list of ComboBox
values and end when the last value was evaluated.

In each iteration, the Macro_Change_Function would change the function in
the Fill_Formula to the next value in the ComboBox (i.e., from SUM to
PRODUCT).

Before when I was storing the Fill_Formula in a cell, the
Macro_Change_Function was changing the formula's function via:

With Sheets("Data")
x = .Range("First_Data_Cell").Formula
y = Application.Substitute(x,
Sheets("Settings").Range("Current_Function"),
Sheets("Settings").Range("Selected_Function"))
.Range("First_Data_Cell").Formula = y
End With

Now that the Fill_Formula is stored in the Name Manager, I'm not sure how
I can change the Fill_Formula function programmatically.

Is there a VBA method to modify a name's Refers to: formula similar to
the Application.Substitute code above ?

That's not how I'd do it! That said, I propose a different approach:
put your various formulas into string constants (with global scope) in
your VBA project. However you get the next item in the combobox, use a
Select Case construct to modify the RefersTo property of "Fill_Formula"
as follows...

Module level variable declaration:
Dim msNewFormula As String

In the sub to change the formula:
ActiveSheet.Names("Fill_Formula").RefersTo = sNewFormula

...where your Macro_Change_Function (or however you do this) loads the
appropriate string constant into sNewFormula...

However you get the next item in the combobox:
Select Case ComboBox1.Text
Case "Item0": msNewformula = gsFILL_FORMULA_0
Case "Item1": msNewformula = gsFILL_FORMULA_1
'...
End Select

...where the constants prefixed with 'gs' (g=global,s=string) are
declared as follows:

In a standard module:
Public Const gsFILL_FORMULA_0 As String = "=blah0"
Public Const gsFILL_FORMULA_1 As String = "=blah1"
...and so on.

I suggest that you turn automatic calculation off BEFORE updating
RefersTo, then turn it back on when you're done making changes. This
means you won't have to re-place the existing cell formulas as they
will automaticall update when you change RefersTo. However, cells won't
update until you turn automatic calculation back on.

Optionally, you can force recalc by...

Range(<targetcells>).Calculate
...where it suits what you're doing so Excel doen't have to recalc the
entire workbook.<g>
 
G

GS

Typo!!! I hate when my fingers don't engage the keys...

all instances of 'sNewFormula' should be 'msNewFormula'.
 
K

kittronald

Garry,

When I manually run the "Macro_Change_Function" macro, the
"Fill_Formula" name's Refers to: value is set correctly with relative
addresses.

However, when I assign that macro to a button, the relative addresses
are affected by the selected cell on the sheet where the button is located.

Is there a way to have the macro ignore the relative position of the
selected cell ?

I tried the following at the top of the macro, but it doesn't affect the
outcome. The "First_Data_Cell" name is the top left first location for the
"Fill_Formula" value.

Application.Goto Reference:="First_Data_Cell"



- Ronald K.
 
G

GS

kittronald used his keyboard to write :
Garry,

When I manually run the "Macro_Change_Function" macro, the "Fill_Formula"
name's Refers to: value is set correctly with relative addresses.

However, when I assign that macro to a button, the relative addresses are
affected by the selected cell on the sheet where the button is located.

Is there a way to have the macro ignore the relative position of the
selected cell ?

I tried the following at the top of the macro, but it doesn't affect the
outcome. The "First_Data_Cell" name is the top left first location for the
"Fill_Formula" value.

Application.Goto Reference:="First_Data_Cell"



- Ronald K.

In your formulas put $ in front of both col and row. Example, A1
relative; $A$1 absolute.
 
K

kittronald

Garry,

The problem ended up being incorrect relative addresses in the
"Fill_Formula" name's Refers to: value.

Since I'm using "=Fill_Formula" with relative addresses, do you still
recommend disabling automatic calculation before setting the Refers to:
value.




- Ronald K.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Running VB Code 3

Top