leah1
asked on
Excel VBA - "out of memory", can't load macro
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
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
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.
Personally, I would never even think about working with a workbook with that many worksheets.
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.
ASKER
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???
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???
it could be a named range that's hidden somewhere or linked to another sheet that's causing excel trouble
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!
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
No worries, glad to help you:)
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