Solved

VB Out of Memory - MS Excel

Posted on 2000-03-28
10
400 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

810 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