Solved

VB Out of Memory - MS Excel

Posted on 2000-03-28
10
398 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
change vba from autofit to 13.5 width? 4 28
Paint/Redraw window while dragging 16 69
Run code from text file in vb 1 57
VB 6 error 5 in windows 10 but not in XP 7 46
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.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

863 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

24 Experts available now in Live!

Get 1:1 Help Now