• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

VB Out of Memory - MS Excel

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
steveowen
Asked:
steveowen
  • 4
  • 2
  • 2
  • +2
1 Solution
 
nungaCommented:
Hi steve

For your info, I tried your code in Excel 97 and it worked fine on my computer.
0
 
gcs001Commented:
What software are you running, i.e. operating system, version, Excel version, VB version?
0
 
j3877Commented:
(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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
steveowenAuthor Commented:
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
 
tureCommented:
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
 
steveowenAuthor Commented:
I am looking for a solution, I will try Ture's solution of :-

ExecuteExcel4Macro "PAGE.SETUP(,,2,2,2,2,2)"
0
 
tureCommented:
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
 
tureCommented:
Ouch! Those GIF images are BIG (about 700 kB each)! I'll try to reduce their file size a bit...
0
 
j3877Commented:
ok... I'm beat... he's good @ this!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now