VBA & Excel memory usage

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I've written a pretty extensive app under Excel, using a
lot of VB code. The problem is now that it is very slow
and uses a lot of memory.

I have most of the cose in two large modules. Would
breaking it into smaller chunks help?

Are there good techniques for generically making a
programm lighter weight and more efficient? Now that it's
working, I want to to tune the app to make is as effieicnt
and fast as I can, short of a re-write or move to another
platform/language (which may be the longer-term solution).

Thanks

Kevin
 
Kevin..

without giving us any examples.. it's a bit hard to guess for us here.

you give no indication of your skill level, no indication whether u use
a lot of controls/userforms of a general idea of what your code does.

in general VBA shouldn't use a lot of memory,
(compared to excel when you have a sheet with 20.000 odd rows and 50
columns...

but if you pump the cell's values of that sheet into an array..
then yes that WOULD take some memory :)

Memory:
do you make extensive use of variables?
are these object variables (little mem) or arrays(lot of mem)

Speed:

a lot of loops tends to make it slow, whereas sometimes same results
can be achieved with 1 line of code using an(array) worksheetfunction.

how often do you find the method .SELECT or .ACTIVATE in your code?
experienced VBA programmers would maybe find it 10 times in 1 large
module (maybe even 0).

If you find it 10 times in 1 procedure you could probably achieve
spectacular performance results by using object variables.

So please.. tell us (part of) what your app is trying to achieve, and
post one or two procedures which you know to be slow.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
how often do you find the method .SELECT or .ACTIVATE in your code?
experienced VBA programmers would maybe find it 10 times in 1 large
module (maybe even 0).

If you find it 10 times in 1 procedure you could probably achieve
spectacular performance results by using object variables

I am new to VBA and in getting close to finishing my Project. I saw this
thread and was woundering about it.

I use SELECT or .ACTIVATE A lot and I dont really understand how to use an
Array I also dont quite know how I could do better with object variables.
Could you give an example.
Thanks
 
Back
Top