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
Solved

VB Out of Memory - MS Excel

Posted on 2000-03-28
10
401 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
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…

809 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