Solved

VB Out of Memory - MS Excel

Posted on 2000-03-28
10
403 Views
Last Modified: 2010-05-02
I have a VB app which imports data into excel and formats it e.g font, col width etc. I am creating upto 20 excel spreadsheets each containing 10 sheets. The Problem is after sucessfully creating 4 spreadsheets I get an out of memory problem and I have to reboot. I have tracked the problem down to all my 16bit selectors being used (Norton Utilities System Doctor). I thought these would be constantly recycled. I can recreate the problem in VBA in excel. e.g
For i = 1 To 100
  ActiveSheet.PageSetup.LeftMargin = 100
End If

Each iteration consumes 2 selectors that are never recovered. Is this an MS bug or is there a workaround. Many Thanks Steve
0
Comment
Question by:steveowen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 2

Expert Comment

by:nunga
ID: 2664432
Hi steve

For your info, I tried your code in Excel 97 and it worked fine on my computer.
0
 
LVL 4

Expert Comment

by:gcs001
ID: 2664639
What software are you running, i.e. operating system, version, Excel version, VB version?
0
 
LVL 1

Expert Comment

by:j3877
ID: 2664669
(I tried ur code on Excel 2k, but it's fine.)


This problem is a M$ bug - memory in Windows isn't properly cleared (ever noticed that after a while on, your computer gets really slow?) There is only one solution - make your code as efficient as possible, and hope your end users have lots of memory.


Send me your sys spec, the spec of your end users, the code (or the part that's annoying u) & i'll see if I can help u further, but I doubt it...
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:steveowen
ID: 2664734
OS is win 95 and VB is version 5 sp3. I am using Excel 97

With 64MB I get about 6000 16bit selectors free (out of possible 8152) after OS has loaded. If each iteration takes 2 selectors.

Change code to
For i = 1 To 1000
  ActiveSheet.PageSetup.LeftMargin = 10
End If  

Setting loop to 1000 will lock up you PC !!
0
 
LVL 22

Expert Comment

by:ture
ID: 2664820
steveowen,

PageSetup in VBA is slow and EATS system resources. This is a well known problem.

Avoid using it extensively. One way to solve it is to use the XL4 macros to handle page setups.

This works to set Left, Right, Top and Bottom margins to 2 inches:

ExecuteExcel4Macro "PAGE.SETUP(,,2,2,2,2,2)"

There are other things you can set with this old macro command, find an old Excel 4 Function Reference manual to read more about it.

Ture Magnusson
Karlstad, Sweden
0
 

Author Comment

by:steveowen
ID: 2668289
I am looking for a solution, I will try Ture's solution of :-

ExecuteExcel4Macro "PAGE.SETUP(,,2,2,2,2,2)"
0
 
LVL 22

Expert Comment

by:ture
ID: 2668352
Steve,

Further information about the PAGE.SETUP function is available at:

http://www.turedata.se/exex/pagesetup1.gif
http://www.turedata.se/exex/pagesetup2.gif
http://www.turedata.se/exex/pagesetup3.gif

/Ture
0
 
LVL 22

Expert Comment

by:ture
ID: 2668356
Ouch! Those GIF images are BIG (about 700 kB each)! I'll try to reduce their file size a bit...
0
 
LVL 22

Accepted Solution

by:
ture earned 200 total points
ID: 2668404
0
 
LVL 1

Expert Comment

by:j3877
ID: 2672876
ok... I'm beat... he's good @ this!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

690 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