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.