Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extremely Slow VBA Execution Speed
I have written a large application in VBA which controls
the opening, closing, screen updating, calculation mode, and displaying of workbooks and worksheets while moving data between them and completing multiple calculations. As with most applications, the users have requested more and more features. Well, somewhere along the line I have hit a wall of SLOW. The time to perform one line of code, setting a cell’s value = an array’s element value, has slowed over 200 times. I have place timers within the code to give me some guidance, but I am at a loss. For overall understanding, I have written a test workbook and macro within which I turn off screen updates and calculations, read 20 values into an array, write the values into 20 cells (one at a time, since I cannot push the array into my application), then turn calculations and screen updating back on. If I open this test workbook and execute the macro as the only workbook open, the macro will take .032 seconds to execute on a 733Mhz machine with 256M of RAM and 384M of Virtual RAM using Windows 2000 and Excel 2000 SR-1. If I have the application open and then open this test workbook, the macro will take 7.235 seconds to execute! We took this computer and installed a 2.4Ghz motherboard into it. So, it is running the same software on the same network using the same hard drive (i.e.: same print drivers, virus protection, …). Now, if I open the test workbook and execute the macro as the only workbook open, the macro will take .015 seconds to execute, and if I have the application open and then open this test workbook, the macro will take 4.421 seconds to execute. Yes, it is 40% faster, but … So, we added 1Gig of RAM for a total of 1,280Meg of RAM and Windows recommended 2,762Meg of Virtual Memory which we are running with a Page File Space of 1,500Meg. Now, if I open the test workbook and execute the macro as the only workbook open, the macro will take .015 seconds to execute, and if I have the application open and then open this test workbook, the macro will take 4.453 seconds to execute. Essentially, NO CHANGE or improvement! The time to recalculate the application is almost the same (within timer error) as the time to insert the element value from an array. Could Excel have lost the Application.Calculation = xlCalculationManual switch? I have observed that as the application has grown (and slowed down) the it has become faster to copy and paste values than to read them into an array and then insert the value. This is why we tried the additional RAM, but it had no effect. (Also, inserting an array of data with about 200 elements into a worksheet takes about the same time as entering one value into one cell, approx. 0.314 sec.) Any Ideas of how to regain some of this 200X speed loss? Why didn’t additional RAM help? Thanks in advance, Joe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extremely Slow VBA Execution Speed
Joe,
Without seeing any of your code, one of the things to look at is whether or not you are using the select method on ranges, workbooks, and worksheets. This really slows down code. Range("A1").Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select Selection.Paste can be replaced with: Sheets("Sheet1").Range("A1").Copy _ Destination:= Sheets("Sheet2").Range("A2") There is also a With Object .<whatever End With structure that also speeds things up... hth -- steveB (Remove 'NOSPAM' from email address if contacting me direct) "Joe Adams" wrote in message ... I have written a large application in VBA which controls the opening, closing, screen updating, calculation mode, and displaying of workbooks and worksheets while moving data between them and completing multiple calculations. As with most applications, the users have requested more and more features. Well, somewhere along the line I have hit a wall of SLOW. The time to perform one line of code, setting a cell’s value = an array’s element value, has slowed over 200 times. I have place timers within the code to give me some guidance, but I am at a loss. For overall understanding, I have written a test workbook and macro within which I turn off screen updates and calculations, read 20 values into an array, write the values into 20 cells (one at a time, since I cannot push the array into my application), then turn calculations and screen updating back on. If I open this test workbook and execute the macro as the only workbook open, the macro will take .032 seconds to execute on a 733Mhz machine with 256M of RAM and 384M of Virtual RAM using Windows 2000 and Excel 2000 SR-1. If I have the application open and then open this test workbook, the macro will take 7.235 seconds to execute! We took this computer and installed a 2.4Ghz motherboard into it. So, it is running the same software on the same network using the same hard drive (i.e.: same print drivers, virus protection, …). Now, if I open the test workbook and execute the macro as the only workbook open, the macro will take .015 seconds to execute, and if I have the application open and then open this test workbook, the macro will take 4.421 seconds to execute. Yes, it is 40% faster, but … So, we added 1Gig of RAM for a total of 1,280Meg of RAM and Windows recommended 2,762Meg of Virtual Memory which we are running with a Page File Space of 1,500Meg. Now, if I open the test workbook and execute the macro as the only workbook open, the macro will take .015 seconds to execute, and if I have the application open and then open this test workbook, the macro will take 4.453 seconds to execute. Essentially, NO CHANGE or improvement! The time to recalculate the application is almost the same (within timer error) as the time to insert the element value from an array. Could Excel have lost the Application.Calculation = xlCalculationManual switch? I have observed that as the application has grown (and slowed down) the it has become faster to copy and paste values than to read them into an array and then insert the value. This is why we tried the additional RAM, but it had no effect. (Also, inserting an array of data with about 200 elements into a worksheet takes about the same time as entering one value into one cell, approx. 0.314 sec.) Any Ideas of how to regain some of this 200X speed loss? Why didn’t additional RAM help? Thanks in advance, Joe |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extremely Slow VBA Execution Speed
I am using the
With WorksheetObject .range("aRangeName").value = arrList(23,2) End With construct and I agree it is faster if you are on the good side of the "Wall". I just do not know what causes the "Wall". Thanks for the input, Joe -----Original Message----- Joe, Without seeing any of your code, one of the things to look at is whether or not you are using the select method on ranges, workbooks, and worksheets. This really slows down code. Range("A1").Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select Selection.Paste can be replaced with: Sheets("Sheet1").Range("A1").Copy _ Destination:= Sheets("Sheet2").Range("A2") There is also a With Object .<whatever End With structure that also speeds things up... hth -- steveB (Remove 'NOSPAM' from email address if contacting me direct) "Joe Adams" wrote in message ... I have written a large application in VBA which controls the opening, closing, screen updating, calculation mode, and displaying of workbooks and worksheets while moving data between them and completing multiple calculations. As with most applications, the users have requested more and more features. Well, somewhere along the line I have hit a wall of SLOW. The time to perform one line of code, setting a cell’s value = an array’s element value, has slowed over 200 times. I have place timers within the code to give me some guidance, but I am at a loss. For overall understanding, I have written a test workbook and macro within which I turn off screen updates and calculations, read 20 values into an array, write the values into 20 cells (one at a time, since I cannot push the array into my application), then turn calculations and screen updating back on. If I open this test workbook and execute the macro as the only workbook open, the macro will take .032 seconds to execute on a 733Mhz machine with 256M of RAM and 384M of Virtual RAM using Windows 2000 and Excel 2000 SR-1. If I have the application open and then open this test workbook, the macro will take 7.235 seconds to execute! We took this computer and installed a 2.4Ghz motherboard into it. So, it is running the same software on the same network using the same hard drive (i.e.: same print drivers, virus protection, …). Now, if I open the test workbook and execute the macro as the only workbook open, the macro will take .015 seconds to execute, and if I have the application open and then open this test workbook, the macro will take 4.421 seconds to execute. Yes, it is 40% faster, but … So, we added 1Gig of RAM for a total of 1,280Meg of RAM and Windows recommended 2,762Meg of Virtual Memory which we are running with a Page File Space of 1,500Meg. Now, if I open the test workbook and execute the macro as the only workbook open, the macro will take .015 seconds to execute, and if I have the application open and then open this test workbook, the macro will take 4.453 seconds to execute. Essentially, NO CHANGE or improvement! The time to recalculate the application is almost the same (within timer error) as the time to insert the element value from an array. Could Excel have lost the Application.Calculation = xlCalculationManual switch? I have observed that as the application has grown (and slowed down) the it has become faster to copy and paste values than to read them into an array and then insert the value. This is why we tried the additional RAM, but it had no effect. (Also, inserting an array of data with about 200 elements into a worksheet takes about the same time as entering one value into one cell, approx. 0.314 sec.) Any Ideas of how to regain some of this 200X speed loss? Why didn’t additional RAM help? Thanks in advance, Joe . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extremely Slow VBA Execution Speed
Charles Williams has the best collection of information that I've seen
on speed, bottlenecks, etc. Maybe the answer will reveal itself by perusing his WebSite. http://www.decisionmodels.com/ HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- I have written a large application in VBA which controls the opening, closing, screen updating, calculation mode, and displaying of workbooks and worksheets while moving data between them and completing multiple calculations. As with most applications, the users have requested more and more features. Well, somewhere along the line I have hit a wall of SLOW. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Extremely Slow | Excel Discussion (Misc queries) | |||
Opens extremely slow | Excel Discussion (Misc queries) | |||
Excel extremely slow opening | Setting up and Configuration of Excel | |||
Workbook is now Extremely Slow | Excel Worksheet Functions | |||
MACRO execution speed | Excel Programming |