PDA

View Full Version : Memory issues (#7)



chamster
10-04-2007, 02:44 AM
I have very strong implications that the operation i perform uses memory in such a way that the computer reports errors. The strange part is that the operation is to repeteadly copy a sheet to another workbook after it has updated the sheet using a database.

In my opinion, it shouldn't provide that much grief and memory cry outs. By other words, my suspicion is that Excel saves a lot of undo's or something like that and i wonder how i can clear the buffer so it runs smoothly.

johnske
10-04-2007, 05:43 AM
??? where's your code??? if you mean to clear the clipboard - it depends on exactly how your copy/paste is being done, but Application.CutCopyMode = Falseusually suffices

chamster
10-04-2007, 10:19 PM
...if you mean to clear the clipboard - it depends on exactly how your copy/paste is being done, but Application.CutCopyMode = Falseusually suffices

Wait a minute, now. I just remembered that somebody told me once that Excel (or was it all of the Office application) had a multiple clipboards, hence capable to store many different chunks of "thingies". I thought he was yanking my chain but perhaps i was mistaken.

I'll try that right away. Thanks.

< 10 hopeful minutes later >

I'm sorry but it didn't work. I'll prepare the code and post it so you can see for yourself what kind of dummy approach i've used.

chamster
10-04-2007, 11:38 PM
Here it is. First, a few things if you're going to run it.

Run the MultiMain ().
On my computer, the problem occurs before April (see direct window) producing a total of 58 sheets or so.
You need to have a file called "Summaries.xls" open. You can create it yourself.
It is the same page copied over and over again. In the real version, a different page is created.Does the error occur on your computer as well? Remedy?

P.S.
This erropr is of such complicated nature that i'll be willig to offer a small thank you amount. Nothing big, really. Still, something for reading the code i believe somebody should get.

Charlize
10-05-2007, 12:00 AM
You could try to save the workbook after 5 copy operations. According to this kb from Microsoft : http://support.microsoft.com/?kbid=210684

johnske
10-05-2007, 12:16 AM
can't open or extract your file. getting an 'unknown compression method' message

johnske
10-05-2007, 12:20 AM
...had a multiple clipboards, hence capable to store many different chunks of "thingies". I thought he was yanking my chain but perhaps i was mistaken... true, excel 2000 has 12, later versions have more - but still limited, not infinite :)

chamster
10-05-2007, 01:19 AM
You could try to save the workbook after 5 copy operations. According to this kb from Microsoft : http://support.microsoft.com/?kbid=210684

I'm saving every 2 iterations, actually, so it shouldn't be it. As i execute a for-loop, the program starts to complain about #7 (memory) after about 60 iterations. If i go, say, 40 iterations, then exit Excel and continue for that point, it works for another 40+ iterations. As long as i restart Excel, that is.

chamster
10-05-2007, 01:20 AM
can't open or extract your file. getting an 'unknown compression method' message

That's a surprise. I used WinZip 9.0 to put it in, maximum compression. That was the only way to get it below the limit. If you give me your e-mail i can send it to you.

Charlize
10-05-2007, 01:26 AM
I'm saving every 2 iterations, actually, so it shouldn't be it. As i execute a for-loop, the program starts to complain about #7 (memory) after about 60 iterations. If i go, say, 40 iterations, then exit Excel and continue for that point, it works for another 40+ iterations. As long as i restart Excel, that is.What about closing the workbook you're copying to (in coding) and reopening it to continue the copying ?

johnske
10-05-2007, 03:31 AM
if it's really a memory problem (in this case i suspect it's not, the repeated saves would only be compounding the problem) just closing the workbooks won't clear the buffer, you have to actually quit excel to do that.

it may help you to read this (http://www.decisionmodels.com/memlimitsc.htm) chamster

chamster
10-05-2007, 05:45 AM
What about closing the workbook you're copying to (in coding) and reopening it to continue the copying ?

I've tried that too. To no avail, sadly...

johnske
10-05-2007, 08:11 AM
had a quick look at your file and yes, am getting that error too. it's too late to look for why, but someone'll probably get it for you before i come back.

it's also excrutiatingly slow with all the 'saves', move them outside the loop and save once just before End Sub

just a couple of FYIs, in visual basic you must explicitly state each variable type or it will be declared as type Variant by default (the slowest and most memory-intensive type). so statements such as Private rName, mName, yName As String means rName and mName are declared as type Variant and only yName is declared as type string. if you need for them all to be string you need to usePrivate rName As String, mName As String, yName As String

also, type Integer is converted to type Long by visual basic so it's generally conceded that type Integer is really a 'non-type' and you may as well just declare integer variables as Long

chamster
10-05-2007, 02:48 PM
had a quick look at your file and yes, am getting that error too. it's too late to look for why, but someone'll probably get it for you before i come back.

Hehe, i wouldn't be so sure, my friend from Down Under. It seems you're the only one able to come with something on this one. I hope i'm mistaken but it sure looks bad for me... :mkay


it's also excrutiatingly slow with all the 'saves', move them outside the loop and save once just before End Sub

I was told to save regularly to save the memory. I'll be more than happy to move it.


statements such as Private rName, mName, yName As String means rName and mName are declared as type Variant and only yName is declared as type string

Ah, didn't realise that. There will be a lot of typing of "as this/ as that" on Monday, hehe. :thumb


also, type Integer is converted to type Long by visual basic so it's generally conceded that type Integer is really a 'non-type' and you may as well just declare integer variables as Long

This is a surprise. I've read in the documentation somewhere that Byte was 1 byte large, Integer 2 byte large and Long 3 or 4. That seems to be supported also by the fact that i got errors when i'd reached 2^16 while there were no errors in the same situation for declaration as Long.

Looking forward to see if you can kill the memory issue. :banghead:

unmarkedhelicopter
10-06-2007, 02:01 AM
This is a surprise. I've read in the documentation somewhere that Byte was 1 byte large, Integer 2 byte large and Long 3 or 4. That seems to be supported also by the fact that i got errors when i'd reached 2^16 while there were no errors in the same situation for declaration as Long. As Johnske says it is considered "non-optimal" to use integers, they do only occupy 2 bytes but every time they are used they are converted to longs (conversion time loss) and then then occupy 2 bytes for the integer and 4 for the long (so it actually, in some wierd circumstances, take up more memory. plus all the pickup and drop out of these temporary longs 'may' cause memory leaks (though this is unproven). Either way it's not ideal to use integers and the documentaion "should" point this out and tell people to discard integers, but ...

chamster
10-06-2007, 02:15 AM
This was very informative, if yet strange. Well, given that i know it now, i'll switch to Long all the way. Is something similar valid for other data types as well?

Bob Phillips
10-06-2007, 03:29 AM
As Johnske says it is considered "non-optimal" to use integers, they do only occupy 2 bytes but every time they are used they are converted to longs (conversion time loss) and then then occupy 2 bytes for the integer and 4 for the long (so it actually, in some wierd circumstances, take up more memory. plus all the pickup and drop out of these temporary longs 'may' cause memory leaks (though this is unproven). Either way it's not ideal to use integers and the documentaion "should" point this out and tell people to discard integers, but ...

I don't know about the memory leaks, never heard that claim or any evidence to such, but there are some places where you have to use Integer, Long doesn't work. For instance, if an API declaration uses Integer, you have to pass an integer, not a long.

Other than that, I agree in principle with what has been said, it is just more efficient to use Long.

johnske
10-06-2007, 03:41 AM
it appears that this issue may have been resolved... the workbook is too large to post so i've emailed a copy to chamster first for testing on his machine. if ok, i will try and reduce the size of the workbook and post it here...

chamster
10-06-2007, 07:17 AM
First of all - cautious thanks to you. Cautious because i need to see it flow with my own eyes, hehe. Nevertheless, you're not exactly a rookie so if you say so, there's a really good chance the sucker is down. : pray2:

Now, to the other matter. In my fury about the above mentioned sucker, i offered a limited monetary compensation (or an equivalent thereof). That offer is of course still valid provided that:
a) the issue is actually solved, killed, hung and then killed again,
b) you wish to receive such a compensation the exact extent of which we can discuss more privately.

I'll take a look first thing on Monday as you've sent the file to my work address, i guess.

johnske
10-06-2007, 09:16 PM
the technique you used in your ExportData procedure viz: wbSrc.Worksheets("Summary").Copy After:=.Worksheets(1)
in
With wbDst
wbSrc.Worksheets("Summary").Copy After:=.Worksheets(1)
.Worksheets(2).Name = nameToBe
.Worksheets(2).Move After:=.Worksheets(endOfSummaries + 1)
End With
seemed to be reaching some sort of inbuilt limit and was raising an error at about sheet t1060 (~60 sheets). by erasing your error handling and using a simple On Error Resume Next just to see what happened, i found the code continued running, but then appeared to be in an infinite loop and was first adding, then deleting sheets. tweaking the code, i found it then started to do some pretty weird things - at one stage it was even acting quite illegally and putting a number of sheets with the same name in the workbook!!

i then decided on a rewrite using a more conventional approach, i.e. explicitly adding sheets, then doing a copy/paste using

With Workbooks("Summaries.xls")
.Sheets.Add after:=.Sheets(.Sheets.Count)
.Sheets(.Sheets.Count).Name = "t" & .Sheets.Count + 1000
Workbooks(docName).Worksheets("Summary").Cells.Copy .Sheets(.Sheets.Count).Range("A1")
End With

this allowed me to add and copy up to around sheet t1209 (~209 sheets), and i'd then get an Excel out of memory error message (in Office 2000) but this appeared to be (possibly) partly dependent on running background processes because a couple of times it actually completed the task (at sheet t1235 ~ 235 sheets), however it would quite possibly complete the task in (your) office 2003

looking further, i saw you were activating each sheet and screen updating was being left turned on, as a consequence, each individual sheet was being painted and an image of each individual sheet (and its embedded chart) was thus being retained in memory. so by turning off screen updating (and also not activating each sheet) the code then ran to completion every time (6-7 times) it was run in office 2000. however, note that you may still have a problem with sheets with more data on them.

try the attached, i've deleted a lot of your code modules to upload it, it's also been heavily modified, and so may not do exactly what you want it to and you'll probably need to tweak it.

BTW, the method you're using is inherently memory intensive and i would suggest you look at other ways of accomplishing the task i.e. do you really need ~235 similar sheets? can your data not be condensed to a lesser number of sheets, or perhaps analyzed differently?

chamster
10-08-2007, 03:46 AM
It seems that the error persists but gets delayed as we only copy the text and leave the the charts and formatting.

What i noticed is twosome. Perhaps this can help us break this one.

1.

There seems to be some "links" as i open Summaries.xls without having the computational workbook open. I clicked Edit->Links and there it is something about a linkage. Could that be causing problems?

This leads automatically to the question how to disable linkage and simply copy the contents as they are. Special paste comes to mind...



2.

Special paste has two applications, which i discovered after some careful reading. The one is for a Range while the other is for a Worksheet. This far, i believe, we were working as if it was used for Ranges.

Could it be that the error lies in the Format-property? Perhaps it should be specified that the format is not "Text", not "HTML", not "Bitmap" and not "Microsoft Word 8.0 Document Object" but... Right - but what?!

I've been Googling the heck out of my internet but i haven't found a list of all the possible formats one can specify. Help, please.

chamster
10-08-2007, 03:50 AM
BTW, the method you're using is inherently memory intensive and i would suggest you look at other ways of accomplishing the task i.e. do you really need ~235 similar sheets? can your data not be condensed to a lesser number of sheets, or perhaps analyzed differently?

I'm not at the liberty of setting up the approach. Some stuff about a boss, communicational problems and other stuff that i should not elaborate on in the open media that can be read by anybody. You're an experienced man, i'm sure you can figure out what i mean. :dunno

johnske
10-08-2007, 06:14 PM
the embedded charts are the main source of your problems. when you copy a chart to another workbook, Excel automatically updates the data source so that it points back to the original workbook and it's then linked to that data, if you want it to point to the data in the new workbook you have to programmatically change the data source of the copied charts.

in addition, these charts are impacting on the memory. as an example, if you let the code below run until Excel gives a 'memory low' type message in the attached workbooks SummariesWithChart and SummariesSansChart, you'll find that you'll get the memory message in SummariesWithChart after about 200 add sheet/copy/pastes whereas when we remove the chart and just leave your data and formatting (as in SummariesSansChart) and run the code you wont get a memory message until after nearly fourteen thousand (13683) add sheet/copy/pastes (and nearly twenty thousand add sheet/copy/pastes if sheet1 is completely empty) i.e. all these charts have a very serious impact and should not be needed. N.B, this is with Office 2000 of course, you may get more with 2003...

NOTE: If, after a few thousand sheets, you get bored, just hold down the ESC key to terminate the procedure.

Mind you, this is NOT how data should be organized, Excel is fundamentally an analysis (calculating) tool. Yes, it can also be used as a database - but within reason. When there is a large amount of data you should be storing it in a database and pulling out only what's needed for Excel to analyze it (or, alternatively using a number of workbooks for data storage).

Option Explicit

Sub ExportData()
Application.DisplayAlerts = False
With ThisWorkbook
Do
.Sheets.Add after:=.Sheets(.Sheets.Count)
.Sheets(.Sheets.Count).Name = "t" & .Sheets.Count
.Worksheets("Sheet1").Cells.Copy .Sheets(.Sheets.Count).Range("A1")
Loop
End With
Application.DisplayAlerts = True
End Sub

chamster
10-08-2007, 10:22 PM
in addition, these charts are impacting on the memory. as an example, if you let the code below run until Excel gives a 'memory low' type message in the attached workbooks SummariesWithChart and SummariesSansChart, you'll find that you'll get the memory message in SummariesWithChart after about 200 add sheet/copy/pastes whereas when we remove the chart and just leave your data and formatting (as in SummariesSansChart) and run the code you wont get a memory message until after nearly fourteen thousand (13683) add sheet/copy/pastes (and nearly twenty thousand add sheet/copy/pastes if sheet1 is completely empty) i.e. all these charts have a very serious impact and should not be needed.

What i find very peculiar is the memory usage. I get that charts will use more memory but i've opened workbooks with many more charts in them (and a lot of other "waste" as well) and they open fine. It seems to me that Excel does some "helping", perhaps stores a whole workbook for undo-operations or something like that. It's definitely not the limitation of number of charts that can be put into the memory.

By the way, you're using "sans" as if it means "without". Does it?


Mind you, this is NOT how data should be organized, Excel is fundamentally an analysis (calculating) tool. Yes, it can also be used as a database - but within reason. When there is a large amount of data you should be storing it in a database and pulling out only what's needed for Excel to analyze it.

That is what i'm doing. The problem is that the analysis is rather complicated so there will be a huge bunch of operations. Something that would be very simplier if i could copy those darn sheets. Well, if life deals you lemons you make a lemonade. If life deals you complicated computations, you make a lemonade and change the approach. :yes


alternatively using a number of workbooks for data storage).

Yes, i think i'll be forced to bend over that way, hehe. Thanks for helping/trying.

johnske
10-08-2007, 11:07 PM
yes, "sans" means "without".

thing is that you don't need a chart on any sheets you're not actually looking at, they're just taking up memory then (and i don't think you'd be examining every single chart either). one thing you could do is create a new chart on the active sheet whenever a sheet is activated (using a Workbook_SheetActivate event) and destroying it when you deactivate the sheet (using a Workbook_SheetDeactivate event) :)

chamster
10-09-2007, 12:32 AM
Oh, that's a good idea. Didn't know there was such a thing. Muchos thankos!

As to the other thing - yes, i (or my boss) will be looking at all the separate charts. I know, who wants to browse through 300+ sheets?! Is it at all possible to get an image of anything that way?! Well, some people (not me) do feel that way... I think THIS is a matter that VBAX-forum won't be able to help me with, hehe.

johnske
10-09-2007, 01:02 AM
oh yes, there's lots of events that can be used to trigger code, have a look here (http://xlvba.3.forumer.com/index.php?showtopic=203&st=0&#entry223) for a short discussion and a list of the most common events

Paul_Hossler
10-09-2007, 06:17 AM
1. A little out of the box, but if the boss only wants to look at the charts, maybe you could generate a chart, copy it to a chart only WB as a picture (no data therefore), delete it from the original, and loop for remaining charts?

2. Also, you might try cleaning the VBA using Ron Bovey's CodeCleaner. I've found that it will clear up a lot of Excel-wierdness :-)

http://www.appspro.com/Utilities/CodeCleaner.htm
About the VBA Code Cleaner

During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents to text files, deleting the components and then importing the components back from the text files.


Paul