Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Excel memory usage
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Excel memory usage
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 "Kevin" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA & Excel memory usage
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 memory usage | Excel Discussion (Misc queries) | |||
XL 2007 memory usage | Excel Discussion (Misc queries) | |||
Monitor memory usage | Links and Linking in Excel | |||
Monitor memory usage | Excel Discussion (Misc queries) | |||
Expanding memory usage Excel 2003 | Excel Discussion (Misc queries) |