Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing memory, speed: Arrays vs. Cells, etc...
I've written 2 fairly large Excel programs (each having over 1000 lines of
code and several forms) and I'm concerned about optimizing memory/speed as these programs continue to grow. I have some specific questions 1) Is it better to store temporary data within arrays in the code or to send data to worksheet cells as much as possible? 2) Is it better to store text within the program code or to gather it from some separate workbook containing the text? The text I'm talking about comes from comments in my code that will become headings to rows and columns and comments in those headings. 3) I tend to provide extensive documentation comments within my code for future programmers and myself. Do i pay a price in memory or speed for doing this? 4) I've stayed away from producing Excel charts in some cases because I guessed they would degrade speed/memory usage. Without those charts the workbooks generated by my programs are typically 500K in size. Am I being too cautious? Our company uses fairly modern computers with, for example, 1GB RAm typically and the programs I'm making use Excel 2003 to drive AutoCAD, which means both those programs are running at the same time when the programs are being used. Thanks for any advice, -Tony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing memory, speed: Arrays vs. Cells, etc...
Answers interspersed
I've written 2 fairly large Excel programs (each having over 1000 lines of code and several forms) and I'm concerned about optimizing memory/speed as these programs continue to grow. I have some specific questions 1) Is it better to store temporary data within arrays in the code or to send data to worksheet cells as much as possible? There is a high overhead time for sending data to/from VBA and Excel, and memory useage is probably higher on an Excel sheet because of the number of properties each cell has. so keep temporary data in arrays. 2) Is it better to store text within the program code or to gather it from some separate workbook containing the text? The text I'm talking about comes from comments in my code that will become headings to rows and columns and comments in those headings. Store text and formats as named ranges on a sheet in the workbook (.xls or ..xla) that contains the code. These can include comments. Then copy the named ranges to the workbook you are generating. This simplifies maintenance etc. 3) I tend to provide extensive documentation comments within my code for future programmers and myself. Do i pay a price in memory or speed for doing this? No. (except that the .xls /.xla containing the code will be slightly larger so will open slightly slower) 4) I've stayed away from producing Excel charts in some cases because I guessed they would degrade speed/memory usage. Without those charts the workbooks generated by my programs are typically 500K in size. Am I being too cautious? Bit of an open-ended question, but generating up to say 10 charts I doubt that you would notice the difference. Generating Charts is fast but you can hit Excel internal memory limits. Our company uses fairly modern computers with, for example, 1GB RAm typically and the programs I'm making use Excel 2003 to drive AutoCAD, which means both those programs are running at the same time when the programs are being used. I am not an autocad user so dont know how much memory it needs: suggest you check using Task Manager. In any case I assume that autocad could be paged out if neccessary bevause it wont be executing at the same time? I assume you are turning off screenupdating and calculation at start then restoring at end. regards Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing memory, speed: Arrays vs. Cells, etc...
Thank you for your detailed, and very helpful response.
AutoCAD and Excel displays are alternately turned on and off at different times during my program's execution depending on which needs to be visible (Excel needs to visible when the forms are used, for example, but not when it's switching sheets). But both programs need to be on throughout execution so that means their both using memory. Not sure what "paging out" is or how I'd do it? Thanks again. Tony "Charles Williams" wrote: Answers interspersed I've written 2 fairly large Excel programs (each having over 1000 lines of code and several forms) and I'm concerned about optimizing memory/speed as these programs continue to grow. I have some specific questions 1) Is it better to store temporary data within arrays in the code or to send data to worksheet cells as much as possible? There is a high overhead time for sending data to/from VBA and Excel, and memory useage is probably higher on an Excel sheet because of the number of properties each cell has. so keep temporary data in arrays. 2) Is it better to store text within the program code or to gather it from some separate workbook containing the text? The text I'm talking about comes from comments in my code that will become headings to rows and columns and comments in those headings. Store text and formats as named ranges on a sheet in the workbook (.xls or ..xla) that contains the code. These can include comments. Then copy the named ranges to the workbook you are generating. This simplifies maintenance etc. 3) I tend to provide extensive documentation comments within my code for future programmers and myself. Do i pay a price in memory or speed for doing this? No. (except that the .xls /.xla containing the code will be slightly larger so will open slightly slower) 4) I've stayed away from producing Excel charts in some cases because I guessed they would degrade speed/memory usage. Without those charts the workbooks generated by my programs are typically 500K in size. Am I being too cautious? Bit of an open-ended question, but generating up to say 10 charts I doubt that you would notice the difference. Generating Charts is fast but you can hit Excel internal memory limits. Our company uses fairly modern computers with, for example, 1GB RAm typically and the programs I'm making use Excel 2003 to drive AutoCAD, which means both those programs are running at the same time when the programs are being used. I am not an autocad user so dont know how much memory it needs: suggest you check using Task Manager. In any case I assume that autocad could be paged out if neccessary bevause it wont be executing at the same time? I assume you are turning off screenupdating and calculation at start then restoring at end. regards Charles ______________________ Decision Models FastExcel 2.2 Beta now available www.DecisionModels.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct, arrays affecting speed | Excel Worksheet Functions | |||
Repost: Speed of inserting pictures and Excel memory | Excel Programming | |||
Excel memory and speed when inserting pictures ??? | Excel Programming | |||
Optimizing a macro for speed- find and replace | Excel Programming | |||
Optimizing a macro for speed- find and replace | Excel Programming |