Solved

Cleaning HD of consequences of VBA application object errors

Posted on 2013-05-25
6
540 Views
Last Modified: 2013-05-26
I'm learning to create Application interactions between MS Visio and Excel in early binding. When I started  creating application objects (visio, excel) I did not clean up the objects before closing, and Windows7 showed down a lot. Then I learned to clean up application objects before closing, eg in Visio code and for excel.application:
if NOT xlApp is Nothing then Set xlApp = Nothing

if Visio code creates xl.App and throws a bug before xl.App is cleaned up, does this adversely affect Windows7?

ought one to have
if NOT xlApp is Nothing then Set xlApp = Nothing in a beforeClose event in the visio ThisDocument module?

Finally, how does one maintain ones HD / Windows7 when these coding mistakes are made, and xlApp is not killed.

I try running the PC through Windows7 safe mode once or twice, and if that does not help speed up excel/visio, do a system restore to a restore point at which Windows was running faster.

What SHOULD I do?

Are there 'cleaning processes' I should use regularly in this connection?

Thanks
Kelvin
0
Comment
Question by:Kelvin4
6 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 260 total points
Comment Utility
This situation should not cause any lasting issues to your HD after a log-off log-on or a restart of the computer.  What does happen is that during one session you could get a lot of instances of Excel hanging around in the background using up resources.  You should be able to see these if you start Task Manager and look on the Processes tab. You can also kill them from here.  However they will go away after you log off.

The only permanent effect I could imagine is that starting Excel could become slow as it will warn you about lots of files needing recovery when you start anew session.

As you say it is definitely recommended to kill the instances when you are finished with them.  It is best to close any unsaved files, and use the xlApp.Quit method to ensure it goes away before setting to nothing.

You should also use error handling to ensure you do the close even if an error is raised.

For a comprehensive discussion (which may be over the top for your requirements), see this EE article http://rdsrc.us/TVnIgG
0
 
LVL 19

Assisted Solution

by:Ken Butters
Ken Butters earned 80 total points
Comment Utility
I agree with andrewssd3.  In general the items you are referring to are more memory management issues than they are hard drive management issues.

You can however use a tool like http://www.piriform.com/ccleaner to clean up leftover files from various places on your hard drive.
0
 
LVL 24

Assisted Solution

by:lionelmm
lionelmm earned 80 total points
Comment Utility
I have two suggestion 1) Ask that this question be moved to the coding section for experts there and 2) to use tools like CCleaner and glary utilities--these will address ystem issues and not coding issues. Hope that helps
http://www.piriform.com/ccleaner/download
http://www.glarysoft.com/glary-utilities/download/
0
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 80 total points
Comment Utility
"Run in safe mode" and "performing a system restore" are in no way related to your application objects. As said by prior posts, the application objects are bound to the session, in memory, and cannot persist over a reboot or log-off.

IMHO the only issues you can get rid of with a system restore is
a) MRU list (most recently used files)
b) crash/file recovery info for Visio/Excel, as mentioned above.
The latter is usually fixed by starting the corresponding application once without any file, or with the file not closed properly.
0
 

Author Closing Comment

by:Kelvin4
Comment Utility
Thank you all for useful education
Kelvin
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Auto Operation of MS Excel 2013 2 29
changing Hard Drive Space color? 3 30
Manipulate Data Quickly 2 11
Excel 6 16
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now