Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
Typo!!! I hate when my fingers don't engage the keys...
all instances of 'sNewFormula' should be 'msNewFormula'. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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. |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
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. |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Purging Excel's VM Usage
kittronald explained :
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. That's a judgement call YOU have to make based on all what else your code is doing, and the nature of the formula. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
purging empty excel files | Excel Worksheet Functions | |||
Purging ZIPCODES from a string of cities and zips | Excel Worksheet Functions | |||
Excel should allow purging since-deleted PivotTable field values. | Excel Discussion (Misc queries) | |||
Add a Excel's references from VB .Net | Excel Programming | |||
Excel's Dialog Box | Excel Discussion (Misc queries) |