VBA code to close and re-start Excel to work around memory leak.
Randy Swanson
I have not been able to solve my memory leak issue (Posted on Jan 28th: “How can
memory be freed when deleting multiple Objects (Images)?”) so I am now looking
for a work-around.
My current idea is this: Write VBA code to identify when available memory is
getting low (see http://msdn.microsoft.com/library/techart/smartalerts.htm) .
When low memory is detected then this VBA would initiate VBA code contained in
another application (Word?) that would close down Excel and all files, then
restart Excel and reopen the files (lost memory is regained when the Excel
Application shuts down). Does anyone know how this could be done? I tried
setting up a macro in Word, that could be started from Excel, to perform this
task but was not successful. I was able to place a hyperlink in a Word file and
was able to click on it to open Excel; however, when the Word macro recorder was
on my left mouse was not functional. Any ideas or solutions would be
appreciated?
Rgds
Randy Swanson
Beaverton Oregon USA
Tom Ogilvy
There are known problems with Excel zoomed at other than 100%, especially
with objects on the worksheet.
Regards,
Tom Ogilvy
Randy Swanson <randall....@intel.com> wrote in message
news:0bbc01c08e5c$1803c6d0$19ef2ecf@tkmsftngxa01...
Randy Swanson
Thanks for your reply. This very well could be the source of my problem,
because I adjust my zoom to make the picture fit the full size of the screen;
independent of the screen resolution or task bar settings. I will defenitly try
altering my code to keep the zoom at 100% to see what happens. If the memory
leak goes away I will feed that info back to the "Bug Report" I filed with Micro-
Soft.
For my application it is important that the screen be adjusted to the maximum
size to fit the picture, so even if this is the cause of my problem I will most
likely still need the work-around solution. There might be hope that if the
images are imported and deleted at 100% screen size, with no memory leak, then it
may not matter what the screen size is while being viewed, that would be great!
I will let you know what I find out when I make it back to work.
Any idea on the work-around?
rgds
Randy
-----Original Message-----
Try leaving the zoom at 100% and see if your problem goes away.
There are known problems with Excel zoomed at other than 100%, especially
with objects on the worksheet.
Regards,
Tom Ogilvy
Randy Swanson <randall....@intel.com> wrote in message
news:0bbc01c08e5c$1803c6d0$19ef2ecf@tkmsftngxa01...
I have not been able to solve my memory leak issue (Posted on Jan 28th:
“How can
memory be freed when deleting multiple Objects (Images)?”) so I am now
looking
for a work-around.
My current idea is this: Write VBA code to identify when available memory
is
getting low (see http://msdn.microsoft.com/library/techart/smartalerts.htm)
..
When low memory is detected then this VBA would initiate VBA code contained
in
another application (Word?) that would close down Excel and all files, then
restart Excel and reopen the files (lost memory is regained when the Excel
Application shuts down). Does anyone know how this could be done? I
tried
setting up a macro in Word, that could be started from Excel, to perform
this
task but was not successful. I was able to place a hyperlink in a Word file
and
was able to click on it to open Excel; however, when the Word macro recorder
was
on my left mouse was not functional. Any ideas or solutions would be
appreciated?
Rgds
Randy Swanson
Beaverton Oregon USA
.
Randy Swanson
With the screen size set to 100%, I still had a memory leak.
Any suggestions on VBA code that would initiate VBA code contained
in another application (Word?) that would close down Excel and all files, then
restart Excel and reopen the files (lost memory is regained when the Excel
Application shuts down)?
rgds
Randy Swanson
Beaverton Oregon USA
-----Original Message-----
Tom,
Regards,
Tom Ogilvy
"How can
memory be freed when deleting multiple Objects (Images)?") so I am now
looking
for a work-around.
My current idea is this: Write VBA code to identify when available memory
is
getting low (see http://msdn.microsoft.com/library/techart/smartalerts.htm)
...
When low memory is detected then this VBA would initiate VBA code contained
in
another application (Word?) that would close down Excel and all files, then
restart Excel and reopen the files (lost memory is regained when the Excel
Application shuts down). Does anyone know how this could be done? I
tried
setting up a macro in Word, that could be started from Excel, to perform
this
task but was not successful. I was able to place a hyperlink in a Word file
and
was able to click on it to open Excel; however, when the Word macro recorder
was
on my left mouse was not functional. Any ideas or solutions would be
appreciated?
Rgds
Randy Swanson
Beaverton Oregon USA
..
.
Tom Ogilvy
pending a return code from Word indicating that control is returned to
Excel. Trying to shut down Excel at that point will probably result in a
lock up with Word waiting for Excel to shut down and Excel not shutting down
or even accepting the instruction because it is already actively engaged
waiting on word. If you used the API to kill it, I am sure this would
corrupt memory and the OS would either die or kill both processes.
I have loaded images in a worksheet and haven't had all these problems. Put
an image control on your sheet and load your images from files instead of
importing worksheets. Or, You turn on the recorder and to insert=>Object
and select file.
Sub LoadPictures()
With Application.FileSearch
.NewSearch
.LookIn = "D:\"
.SearchSubFolders = False
.FileName = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For j = 1 To 20
For i = 1 To .FoundFiles.Count
sName = .FoundFiles(i)
sExt = UCase(Right(sName, 3))
If sExt = "JPG" Or sExt = "GIF" Then
Set Pic = ActiveSheet.Pictures.Insert(sName)
With Pic
.Top = Range("A2").Top
.Left = Range("A2").Left
End With
Pic.Delete
End If
Next i
Next j
Else
MsgBox "There were no files found."
End If
End With
End Sub
I ran this 4 times with allocated memory in the system monitor and saw no
real change in memory used. It loaded 80 picture per run, so it loaded 320
pictures. Total of 4 unique picturs 3 jpg, 1 gif
jpgs:
110K
117K
535K
Gif was 24K
This was in Win98 2nd Edition.
Regards,
Tom Ogilvy
Randy Swanson <randall....@intel.com> wrote in message
news:0f9901c08ec5$03b9e5b0$b1e62ecf@tkmsftngxa04...
Randy Swanson
Thank you so much for writing such a cool macro to test for the Memory Leak. I
modified it to run .TIF images in my test. I had to run it twice because it was
so fun to watch the 1st time. It really shows the memory leak that I am trying
to fix/work around. In fact it demonstrates is better than the manual process
that I documented at the bottom of my earlier post (Jan 28th:"How can memory be
freed when deleting multiple Objects (Images)?").
Thank you for being specific in your message to include that you ran this macro
using only 4 unique images (which were small in file size); this is why you did
not see the leak:
>I ran this 4 times with allocated memory in the system monitor and saw
>no real change in memory used. It loaded 80 picture per run, so it
>loaded 320 pictures. Total of 4 unique pictures 3 jpg, 1 gif
>jpgs:110K, 117K, 535K, Gif was 24K
Your leak was probably less than 1 Meg. I have found that memory consumption
only occurs the first time a unique image is loaded. Somehow even if you make
many copies of an image and name them different, it will still not use memory.
Even if the images are deleted and reloaded the memory usage will not increase,
only when the next unique image has been loaded. This could be viewed as a
feature for most applications, but when I want to view a hundred or more unique
images that is when memory runs out and Excel shuts down.
Try repeating your test with several unique large TIF or BMP images. And watch
your memory usage graph go up while the images are loaded the 1st time, no change
to memory usage will be seen as they are then reloaded subsequent times, and the
Memory usage will not come down until you exit Excel. When I ran your Macro I
used 12 TIF images (1,202 KB/each). My memory went from 89K to 109K and did not
come down until I exited Excel. When you run the macro with these large files
you can gain a sense of appreciation for this Memory leak "Feature", after the
images take ~1.5 second each to load the 1st time, then all following imports of
the image in almost instant.
I just have to find out how I can turn off, using VBA, this cool feature that
Microsoft has built into Excel. If I can't then there has to be some feasible
work around. Other than a message to tell the user "Close down Excel NOW before
you get a visit from Dr. Watson."
I will forward your macro onto Micro Soft for the "Bug Report" that has been
filed: (case number, SRX010130600241).
Rgds
Randy Swanson
Beaverton, Oregon. USA
End Sub
jpgs:
110K
117K
535K
Gif was 24K
Regards,
Tom Ogilvy
-----Original Message-----
Tom,
Regards,
Tom Ogilvy
....
When low memory is detected then this VBA would initiate VBA code contained
in
another application (Word?) that would close down Excel and all files, then
restart Excel and reopen the files (lost memory is regained when the Excel
Application shuts down). Does anyone know how this could be done? I
tried
setting up a macro in Word, that could be started from Excel, to perform
this
task but was not successful. I was able to place a hyperlink in a Word file
and
was able to click on it to open Excel; however, when the Word macro recorder
was
on my left mouse was not functional. Any ideas or solutions would be
appreciated?
Rgds
Randy Swanson
Beaverton Oregon USA
...
..
.
Peltier
Is the leak at 100% as much as at variable zoom? If so, then this won't
help, but if it's better behaved at 100%, why not resize the picture to
fit the zoom, rather than zooming to fit the picture?
Regarding the use of Word... What if you start up an instance of Word
when you start your Excel macro, and open the Word doc that has a macro
to restart Excel? So it's already going. When Excel gets gummed up,
have your Excel macro write something in the Word document, then quit
Excel. Have the Word macro start when something gets written in it,
like a worksheet_change event in Excel (there's gotta be something like
that in Word, right?). So the Word macro checks whether there's an
instance of Excel running; if there is, wait 2 seconds and then check
again. When there's no Excel instance running, restart Excel with your
macro, and the process repeats.
Sure it's a kludgey workaround, but I guess so is the way Excel hoards
your memory without returning it when the pictures are removed. The
better thing to do is probably something directly in VB. I hear you can
get a beginner's edition fairly cheap.
- Jon
Randy Swanson
I just keep learning more and more how Excel uses memory with images.
Yes, after reading your message I varied Zoom size, ran Tom's cool macro, and the
larger the Zoom the more memory consumed (tested with TIF images). 12% zoom
consumed 11K, 100% consumed 20K, 189% consumed 40K. I have ran out of time to
see if I can benifet from this. I will play with it tomorow.
I like your aproach to the work around. A lot of this stuff you mention I have
not been exposed to before so could give greater detail?
gota go
Randy
- Jon
.
Peltier
As far as running Excel from Word, you're going to have to look up some
sites that describe it. I have very little direct experience, just
things I've copied from other folks. Microsoft's developer network has
some articles (look up "Automation" on their search site), and I would
think that Chip Pearson's site, and probably John Walkenbach's would
have examples you can hack.
How many of your pictures must be in view at one time? I still am
thinking that you could use one or more modeless forms; this is built in
to XL2k, and not hard to do in XL97. See Stephen Bullen's site,
www.bmsltd.co.uk, and look for an excel file download for modeless
forms, but not the floating window one (not manual enough). He also has
one called FormFun, which shows how to do things like hide the menu bar.
Randy Swanson
Thank's for the references, I will give them a try.
>How many of your pictures must be in view at one time?
Only one that goes the full hight of the screen.
>I still am thinking that you could use one or more modeless forms; this is built
>in to XL2k, and not hard to do in XL97.
All my code is based off of displaying the images in my current fashion,
changing over would take several weeks. If the main program was successfuly
transfered to this new mode of displaying images then I would still have to
extract images from over 1700 individule Excel files and then rename the 1700
images to fit the new formate. Time that I just don't have.
A co-worker will have a HTML version, a duplacation my program using HTML, that
will be ready to compair shortly and I need to spend my time on features that
Excel does well, such as summarization in graphs of the data collected from the
main program.
If the memory leak comes up as a issue during the compairison that I will explain
that it is nothing that a couple of 128 MB RAM modules ($86/ea) wouldn't solve:)
This hardware fix may be the most economical solution. Then have the text
warning to "Shut down and restart Excel NOW to prevent a system crash" as a
backup measure.
Who knows, maybe Microsoft will have a solution for me, that would be nice.
rgds
Randy
- Jon
.