?
Solved

Excel VBA - "out of memory", can't load macro

Posted on 2003-03-31
10
Medium Priority
?
1,152 Views
Last Modified: 2008-03-10
Hi everyone,

We are generating excel workbooks up to about 3 MB in size (up to a couple thousand sheets in a workbook).  We have written a fairly straightforward VBA macro that goes through each workbook, identifies worksheets with a particular name suffix, then operates on those worksheets applying some formatting to the cells and doing some simple calculations (i.e. adding, multiplying, dividing, then copying those equations down for a number of rows).  This macro works beautifully for excel workbooks that are smaller than about 2 MB.  However, for workbooks above 2 MB, when we try to import the .bas file for the macro into Excel's VB environment, we get an immediate "Out of memory" error--VBA won't even attempt to open the macro file.

We have 768 MB of RAM on this computer, along with over 40 GB of hard drive space available.  We have set the virtual memory swap files to 300 MB and we do not have any other applications running at the same time.  That's why we don't believe that we are running into a limit of the physical memory of the system.  Does anyone have any suggestions on what the problem might be?  Is there some other "unwritten" memory limit for Excel or VBA???  Any help would be greatly appreciated.

Leah
0
Comment
Question by:leah1
[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
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8241526
Hi Leah,

You managed to put thousands of sheets in a 3MB workbook? I want your autograph :)

* Not much of a help but have you monitored the memory with TaskManager just to see how is the memory being handled?
* Open clipIt, type "limits" and check out Excel's specifications. You'll find most limits you may want to consider.
* Could you post some of the code? Maybe it has something to do with how you're trying to add the code...
* Maybe your application has some memory leaks, that is, you're not destroying the objects properly... that could lead to it (although I think the effect on this would be a little random and not just 'over 3 MB files won't work').

Not much of a help, but it may be a start..

Paulo
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 8241622
I just got done telling someone they need to come up with a different solution because they're using too many worksheets; somewhere between 300 and 350.

Personally, I would never even think about working with a workbook with that many worksheets.
0
 
LVL 13

Expert Comment

by:cri
ID: 8243220
Shot in the dark: How do you add the worksheets ? If you make a copy of a copy of a copy etc. this gives you very long _VBA_ worksheet names (th one you see in the VBA project window). There is a limit for those too.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:leah1
ID: 8245922
Hi everyone,

Thanks for your suggestions.  I've been trying to troubleshoot the problem and it is now looking like the excel file size may not be the problem.  I started breaking up the large workbook into smaller workbooks (around 800 KB) and tried importing the same macro .bas file into each of these smaller workbooks.  It works fine for most of the workbooks, but there is one workbook that still immediately gives the "out of memory" error.  I think maybe there is something weird/corrupt about this particular batch of sheets that makes VBA immediately choke.  I am still in the process of breaking up the workbook that's giving me the problems, although I don't really know what kind of corruption I'm looking for.  It seems strange that there would be one or a few sheets that are causing problems, since all of the sheets in the Excel workbook are generated in the same manner by a stand-alone VB program.  Any ideas???
0
 
LVL 44

Expert Comment

by:bruintje
ID: 8245949
it could be a named range that's hidden somewhere or linked to another sheet that's causing excel trouble
0
 
LVL 22

Expert Comment

by:Dreamboat
ID: 8246367
I've seen corruption come from all kinds of things. Even from people putting cell borders on the cells using the drawing tools. D'oh!
0
 
LVL 13

Expert Comment

by:cri
ID: 8247911
Up to now I had 2 cases (touch wood) of corrupted files in approx. 10 years of using Excel (now stuck at 97 SR-2). One was during troubleshooting a intermittent page fault (cause: VBA function declared as Volatile), the other was saving at the very moment the network had hicups.
0
 
LVL 3

Accepted Solution

by:
randomgurn earned 150 total points
ID: 8252792
Why do you need to import this macro to the workbook?  Can't you just create an XLA containing your macro, then just run it on whichever workbook?
0
 

Author Comment

by:leah1
ID: 8254661
Randomgurn!  Thank you for pointing out a simple but effective approach.  Although I did not convert the macro to an add-in, I did instead try running the macro from a separate workbook, and surprisingly, it works!  I had not thought that this would work, since when I tried to record a macro in the problem workbook, it gave me an immediate "unable to record" message.  However, it IS able to run an existing macro that's stored elsewhere. Thank you!
0
 
LVL 3

Expert Comment

by:randomgurn
ID: 8255699
No worries, glad to help you:)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

801 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