will more ram help

[jerry12302]

I have a large excel application with many sheets that processes a large amount of data and is painfully slow running macros. Even moving the cellpointer beyond column AA, for example, is slow, you press the right arrow and wait 2 or 3 seconds before it moves.

I have 1 gig of Ram (2 512s) currently with a couple of slots available. I could install 2 more gig and bring it to 3 gig, but I'm just wondering if that will help speed up the macros and navigating in this spreadsheet, or if it's another issue?

It's a Dell Dimension 4700 running Windows XP Professional Service Pack 2 (build 2600)

Processor:
2.80 gigahertz Intel Pentium 4
16 kilobyte primary memory cache
1024 kilobyte secondary memory cache

 

[MorganO]

From my experience, more RAM always helps in memory based operations (such as manipulating data) but the benefit you get is usually only in factors of a few percentage points improvement. Doubling RAM doesn't necessarily cause your macros to complete twice as fast.

If might be more useful to reveiw the macro code to ensure you are using the most effiecient methods for manipulating the data. These are things that have helped me run macro's faster:

- Turning off screenupdating during spreadsheet writes
- Manipulating data within the code vs on the sheet
- Avoiding 'selecting' cells prior to writing to them

Take care.

Owen

 

[jerry12302]

Thanks Owen,

I do turn off screen updating, and I never select cells when writing data or manipulating data. I try to manipulate intermediate data in code as much as possible without writing to cells, but sometimes I think it's necessary.

I do a lot of filtering, to produce unique record sets, for example, and sorting, which takes some time.

I can get another gig of memory for $50, but I wanted to hear that I would see $50 worth of benefit in terms of increased performance. Sounds like maybe, maybe not.

 

[SydneyGeek]

You'll get more benefit from turning calculation to manual, especially if you're filtering, because filtering / unhiding generates a recalc.
You can always turn calculation back to automatic when the code finishes, so...

Application.Calculation = xlCalcationManual
...code in here
Application.Calculation=xlCalculationAutomatic

Denis

 

[mrhartley]

I would say that memory is very cheap these days (is in the UK anyway) so an upgrade to 2gb might be worth it; Not for Excel alone but for other apps as a whole. 3gb would be a waste of money, for Win XP at least.

You can try bringing up Task Manager (using ctrl alt delete) on the processes tab, you can order processes by mem usage. Anything listed there taking up shed loads of memory? Is always something to look out for good house keeping anyway.

No open Excel, see how much memory that takes, now open your large workbook, see how much more memory that takes, run your macro's see how much memory that takes. You might find the incremental increase in mem usage not as harsh as you thought, if so, more memory may not help, but you never know.

Now I may be wrong but I expect the power of Excel comes from the CPU, it is after all responsible for the math behind the computer. It maybe this that needs looking into.

A new core 2 duo machine might be a better bet but perhaps a more costly upgrade, depends on how much you use such spreadsheets.

I am not suggesting you upgrade for Excel but perhaps you can convince someone in the office to take your machine if you can convince someone you are a power user and need something better than standard.

If you are lucky enough to get a new machine, if it comes with Vista, you will need 2gb as standard, any less and you wont see it's full power potential.
---
I had a similar problem, working with 60mb files (which is still small by some standards) running macro's took ages, always had to work with auto calc switched off as did the end user too which is not a good thing and I managed to convince the corporate IT department for a memory upgrade, they took my XP machine from the 'Corporate Standard' 512 to 1gb - Wow I thought when I recovered from that micracle to see that my machine performed a couple of seconds quicker on the 60meg file. What I really needed to was decent machine, not a 512 upgrade.