Too many macros for XP?

[CLR]

Hi All..........
I have written an Excel program that imports another Excel file and massages
it and eventually Autofilters the data out into 16 separate sheets. The
entire program was written on a machine with WindowsXP, and Excel97. Each
individual macro runs fine when run individually. The first few macros
import and massage the data......the last dozen or so are identical except
for certain numbers that direct the extraction of data relative to different
departments.

When I try to run them all in succession, the program crashes on the last
two, giving an "Out of memory" error message. The problem occurs whether
the macros are sequenced by hand or by another macro. The machine has 512MB
RAM, no other programs are running, and the entire program runs fine on
another machine using WindowsMe with only 160MB RAM.........but crashes
every time on WindowsXP. Five different XP machines act the same way, some
on the network and some not. All are maintained by the company MIS section
so are completely up to date with all appropriate updates.

I feel there is some sort of "setting" that is restricting XP to not allow
it to process only so much in a row........I am not an OS person, so am only
feeling my way along. Anyone have any similar experience, or have an idea
what might be the problem?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Pegasus \(MVP\)]

Hi All..........
I have written an Excel program that imports another Excel file and massages
it and eventually Autofilters the data out into 16 separate sheets. The
entire program was written on a machine with WindowsXP, and Excel97. Each
individual macro runs fine when run individually. The first few macros
import and massage the data......the last dozen or so are identical except
for certain numbers that direct the extraction of data relative to different
departments.

When I try to run them all in succession, the program crashes on the last
two, giving an "Out of memory" error message. The problem occurs whether
the macros are sequenced by hand or by another macro. The machine has 512MB
RAM, no other programs are running, and the entire program runs fine on
another machine using WindowsMe with only 160MB RAM.........but crashes
every time on WindowsXP. Five different XP machines act the same way, some
on the network and some not. All are maintained by the company MIS section
so are completely up to date with all appropriate updates.

I feel there is some sort of "setting" that is restricting XP to not allow
it to process only so much in a row........I am not an OS person, so am only
feeling my way along. Anyone have any similar experience, or have an idea
what might be the problem?

TIA
Vaya con Dios,
Chuck, CABGx3

It's now February 2007. Excel 97 was written more than ten years
ago when Win2000/XP did not even exist. I recommend you test
your macro on a current version of Excel. It might also be a good
idea to cross-post your question to an Excel newsgroup.

 

[Newbie Coder]

When you run the macros, how many MS Excel shells are open when you run the
final 2?

Out-of-memory is because you're trying to pull too much data.

Also, you wrote these in VBA. So, can you paste some of the code in from the
final 2 macros?

One last question for now: What service pack are you running for MS Office
97?

 

[CLR]

The version selection of Windows/Excel is made by the company MIS section,
not by us individuals. They have about 1000 computers and most are still on
Excel 97, so I must write my code in that version. FWIW, I do have Excel
2000 and Excel 2002 also on that same machine with WindowsXP and they all
act the same way, bringing up the "Out of memory" error at the same place in
the sequencing of the macros........to reiterate, the problem appears to be
an OS problem rather than an Excel problem.....the exact same program,
running off the exact same jump drive works fine on an Me machine (with
Excel97) with only 160MB RAM.

Any other ideas please?

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Thanks very much for your time and interest..............

I am only using one Excel shell after the first macro retrieves the data
into the program.

As I said, the program runs fine on an Me machine with only 160MB RAM, but
will not run on an XP machine with 1GB RAM, so RAM does not really appear to
be what's wrong.....now maybe XP just handles it differently than Me. The
file I'm importing is only about 1.5MB and the entire file once totally run
is only a little over 3MB.....too much data does not really appear to be the
problem, in spite of the "Out of memory" error message.

The last dozen or so macros are all the same, only changing the Department
numbers to retrieve their data from the database.
Here's one....it's crude, but works..........
Sub Filter2765()
'the 2765 (dept number) is the only number that gets changed from one macro
to another
Sheets("Q5PLAN2").Select 'this is the massaged database
Rows("10:10").Select 'this is the header row
Application.CutCopyMode = False
Selection.Copy
Sheets("2765").Select
Rows("10:10").Select
ActiveSheet.Paste
'============================================================
Sheets("Q5Plan2").Select
Range("A11").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=2765 'Worksheets("All
WO's").Range("c2").Value
Range("A5").Select
'This section copies the Autofiltered range over to the respective TAB
Dim rng As Range
Dim rng2 As Range
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
'Error message pop's up if no drawing found

If rng2 Is Nothing Then
Worksheets("2765").Select
Range("A11:AJ65000").Clear
Worksheets("Q5Plan2").Select
Range("A7").Select
Selection.AutoFilter
Worksheets("2765").Select

'turn screen on briefly to allow MsgBox appearance
Application.ScreenUpdating = True
Application.ScreenUpdating = False
MsgBox "No PartNumbers for that Scheduler-Code available"
Range("A5").Select

Else
Worksheets("2765").Select
Range("A11:a65000").Clear
Worksheets("Q5Plan2").Select
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy
Destination:=Worksheets("2765").Range("A11")
ActiveSheet.ShowAllData
Selection.AutoFilter
Range("A11").Select
Worksheets("2765").Select
'ActiveSheet.ShowAllData
Application.ScreenUpdating = True
Range("A5").Select

End If
'=============================================================
Columns("A:BQ").Select
Columns("A:BQ").EntireColumn.AutoFit
Call AddTopFormulas
Call Goal
Range("F11").Select
ActiveWindow.FreezePanes = True
Range("A11").Select
End Sub

Don't know for sure which Service Pack is installed for Office97.....I
believe it to be SP2, as the MIS dept usually keeps things pretty well up to
date. That machine is at my workplace and I won't be able to tell for sure
until Monday. The Me machine which runs the program fine is only SP1.

Again, thanks for any insights you can offer.........

Vaya con Dios,
Chuck, CABGx3

 

[mikeyhsd]

is your page file restricted in size.
sometimes the OS does not know the real difference between real memory and virtual memory.

(e-mail address removed)@sport.rr.com

Hi All..........
I have written an Excel program that imports another Excel file and massages
it and eventually Autofilters the data out into 16 separate sheets. The
entire program was written on a machine with WindowsXP, and Excel97. Each
individual macro runs fine when run individually. The first few macros
import and massage the data......the last dozen or so are identical except
for certain numbers that direct the extraction of data relative to different
departments.

When I try to run them all in succession, the program crashes on the last
two, giving an "Out of memory" error message. The problem occurs whether
the macros are sequenced by hand or by another macro. The machine has 512MB
RAM, no other programs are running, and the entire program runs fine on
another machine using WindowsMe with only 160MB RAM.........but crashes
every time on WindowsXP. Five different XP machines act the same way, some
on the network and some not. All are maintained by the company MIS section
so are completely up to date with all appropriate updates.

I feel there is some sort of "setting" that is restricting XP to not allow
it to process only so much in a row........I am not an OS person, so am only
feeling my way along. Anyone have any similar experience, or have an idea
what might be the problem?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Thanks for answering, but I am at a loss. I do not know what a "page file" is, nor how to check or restrict/unrestrict it's size. Could you please elaborate on the procedure?

Vaya con Dios,
Chuck, CABGx3


is your page file restricted in size.
sometimes the OS does not know the real difference between real memory and virtual memory.

(e-mail address removed)@sport.rr.com

Hi All..........
I have written an Excel program that imports another Excel file and massages
it and eventually Autofilters the data out into 16 separate sheets. The
entire program was written on a machine with WindowsXP, and Excel97. Each
individual macro runs fine when run individually. The first few macros
import and massage the data......the last dozen or so are identical except
for certain numbers that direct the extraction of data relative to different
departments.

When I try to run them all in succession, the program crashes on the last
two, giving an "Out of memory" error message. The problem occurs whether
the macros are sequenced by hand or by another macro. The machine has 512MB
RAM, no other programs are running, and the entire program runs fine on
another machine using WindowsMe with only 160MB RAM.........but crashes
every time on WindowsXP. Five different XP machines act the same way, some
on the network and some not. All are maintained by the company MIS section
so are completely up to date with all appropriate updates.

I feel there is some sort of "setting" that is restricting XP to not allow
it to process only so much in a row........I am not an OS person, so am only
feeling my way along. Anyone have any similar experience, or have an idea
what might be the problem?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[mikeyhsd]

I use WINDOWS KEY + Pause/Break to get System Properties then Advanced tab and under Settings button under Performance section then Advanced tab again and should tell you how large the page file is. if you click on the CHANGE button there it will show which drive it is on and how much space is allocated.

(e-mail address removed)@sport.rr.com

Thanks for answering, but I am at a loss. I do not know what a "page file" is, nor how to check or restrict/unrestrict it's size. Could you please elaborate on the procedure?

Vaya con Dios,
Chuck, CABGx3


is your page file restricted in size.
sometimes the OS does not know the real difference between real memory and virtual memory.

(e-mail address removed)@sport.rr.com

Hi All..........
I have written an Excel program that imports another Excel file and massages
it and eventually Autofilters the data out into 16 separate sheets. The
entire program was written on a machine with WindowsXP, and Excel97. Each
individual macro runs fine when run individually. The first few macros
import and massage the data......the last dozen or so are identical except
for certain numbers that direct the extraction of data relative to different
departments.

When I try to run them all in succession, the program crashes on the last
two, giving an "Out of memory" error message. The problem occurs whether
the macros are sequenced by hand or by another macro. The machine has 512MB
RAM, no other programs are running, and the entire program runs fine on
another machine using WindowsMe with only 160MB RAM.........but crashes
every time on WindowsXP. Five different XP machines act the same way, some
on the network and some not. All are maintained by the company MIS section
so are completely up to date with all appropriate updates.

I feel there is some sort of "setting" that is restricting XP to not allow
it to process only so much in a row........I am not an OS person, so am only
feeling my way along. Anyone have any similar experience, or have an idea
what might be the problem?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Many Thank-you's kind Sir. I will try that Monday when I get to work if I can do it without Administrator rights, .....if not, then I will ask MIS to do it......BTW, other than this, do you know if XPpro can limit the amount of memory that Excel can use, or the number of lines of VBA code that can be run at one time?

Vaya con Dios,
Chuck, CABGx3


I use WINDOWS KEY + Pause/Break to get System Properties then Advanced tab and under Settings button under Performance section then Advanced tab again and should tell you how large the page file is. if you click on the CHANGE button there it will show which drive it is on and how much space is allocated.

(e-mail address removed)@sport.rr.com

Thanks for answering, but I am at a loss. I do not know what a "page file" is, nor how to check or restrict/unrestrict it's size. Could you please elaborate on the procedure?

Vaya con Dios,
Chuck, CABGx3


is your page file restricted in size.
sometimes the OS does not know the real difference between real memory and virtual memory.

(e-mail address removed)@sport.rr.com

Hi All..........
I have written an Excel program that imports another Excel file and massages
it and eventually Autofilters the data out into 16 separate sheets. The
entire program was written on a machine with WindowsXP, and Excel97. Each
individual macro runs fine when run individually. The first few macros
import and massage the data......the last dozen or so are identical except
for certain numbers that direct the extraction of data relative to different
departments.

When I try to run them all in succession, the program crashes on the last
two, giving an "Out of memory" error message. The problem occurs whether
the macros are sequenced by hand or by another macro. The machine has 512MB
RAM, no other programs are running, and the entire program runs fine on
another machine using WindowsMe with only 160MB RAM.........but crashes
every time on WindowsXP. Five different XP machines act the same way, some
on the network and some not. All are maintained by the company MIS section
so are completely up to date with all appropriate updates.

I feel there is some sort of "setting" that is restricting XP to not allow
it to process only so much in a row........I am not an OS person, so am only
feeling my way along. Anyone have any similar experience, or have an idea
what might be the problem?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

To prove there was not a bad spot in the code, I took the two macros at the
end of the string where the program crashed, and moved them away up forward
so they would be processed early...........the program processed them just
fine, and still failed in XP when it got to the point where the old macros
were.....failing on the new macro that is now in that position.....The
problem is not the code.......the code runs fine in Me

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Hi mikeyhsd..........
I just got to work and tried your suggestion but my system would not let me
change it. I clicked the Custom button and entered a larger number than the
765MB that was there but the SET button was disabled and when I left the
window, all reverted back to the default 765MB..........I will try to get one
of the MIS guys to help me.

Thanks again for your suggestion.

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

PROBLEM SOLVED!!!!!!

Jim Cone came up with a suggestion that did the trick.....

http://support.microsoft.com/kb/313935/en-us
"Office Programs Stop Responding After You Upgrade to Windows XP"

Many thanks Jim, and to all the others who gave of their time....

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Follow-up note:
The Compatibility adjustment procedure worked fine in my case on Excel 97
and on Excel 2002 for running my program on a WinXP machine. HOWEVER, it
would NOT work on Excel 2000-SP3. In fact, it disabled Excel 2000-SP3 so it
would not even work at all.......this on 2 different WinXP machines..just a
heads-up for anyone trying the Compatibility adjustment.

Vaya con Dios,
Chuck, CABGx3