Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bottlenecks in an Excel VBA application
Hi everyone,
I need to evaluate what parts of an Excel Application are slower than others so that I can work on them to make the overall performance better. The application was built over a 5 year period by someone I don't know and is now in Excel 2000. Is there a tool (an application) that can easily identify the lines of code in an Excel application that cause (or may cause) a performance problem. I'm looking for a tool like Golden that can be used to evaluate an Oracle application or query. If that does not exist, is there another way to pinpoint the parts of an application that should be optimized or checked? The application contains about 24000 lines of VBA code in 22 modules and uses many Excel files with different extensions (.xls, .xlt, .xla). Thank you for your help. -- Jac Tremblay |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bottlenecks in an Excel VBA application
Hi Harald,
What you say is right and I am exactly that person. But I am not an MVP nor an expert and I thought that a tool could speed up my work. Do you (or anyone) know of Internet sites where would be listed the dos and don'ts in good Excel VBA development? Thank you again for your concern. "Harald Staff" wrote: Hi Jac I think you need a skilled person for this. An experienced Excel developer should be able to do this within reasonable time -assuming the code is "somewhat standard" and not spaghetti. Best wishes Harald "Jac Tremblay" skrev i melding ... Hi everyone, I need to evaluate what parts of an Excel Application are slower than others so that I can work on them to make the overall performance better. The application was built over a 5 year period by someone I don't know and is now in Excel 2000. Is there a tool (an application) that can easily identify the lines of code in an Excel application that cause (or may cause) a performance problem. I'm looking for a tool like Golden that can be used to evaluate an Oracle application or query. If that does not exist, is there another way to pinpoint the parts of an application that should be optimized or checked? The application contains about 24000 lines of VBA code in 22 modules and uses many Excel files with different extensions (.xls, .xlt, .xla). Thank you for your help. -- Jac Tremblay |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bottlenecks in an Excel VBA application
It's not the easy way - it's the hard way, but...
I have speed testing routines on my website. -- Rob van Gelder - http://www.vangelder.co.nz/excel "Jac Tremblay" wrote in message ... Hi everyone, I need to evaluate what parts of an Excel Application are slower than others so that I can work on them to make the overall performance better. The application was built over a 5 year period by someone I don't know and is now in Excel 2000. Is there a tool (an application) that can easily identify the lines of code in an Excel application that cause (or may cause) a performance problem. I'm looking for a tool like Golden that can be used to evaluate an Oracle application or query. If that does not exist, is there another way to pinpoint the parts of an application that should be optimized or checked? The application contains about 24000 lines of VBA code in 22 modules and uses many Excel files with different extensions (.xls, .xlt, .xla). Thank you for your help. -- Jac Tremblay |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bottlenecks in an Excel VBA application
"Jac Tremblay" skrev i melding
... Hi Harald, What you say is right and I am exactly that person. But I am not an MVP nor an expert and I thought that a tool could speed up my work. Hi again Jack Apologies <g. I don't know of any place where those are collected. These are the things I look for first, maybe what you already do: - Calculation turned off when entering into cells ? - Screenupdating turned off while code does things to sheets ? - Any unneeded Select or Activate actions ? (Do a Search for those words and see if there are any and what they do if so) - Any undeclared or lousy declared memory eating variables or collections ? - Efficient object coding ? ("Set Ws = ThisWorkbook.Sheets(1)" instead of looking for and activating Workbook and Sheet1 again and again by code) - Any meaningless tests ? (Like all cells in a range instead of all cells with numeric constants ?) - Any inefficient tests ? (Test for the least likely first. Say we want to find all swedish male persons in the world by testing everybody. This if male then if swedish then here the inner test runs billions of times but this if swedish then if male then here the inner test runs only 9 million times) Also, for a start I place MsgBoxes all over the code to see which parts that runs fast and which that I have to wait for. I can't imagine how a program could spot these things. But lots of clever people are in here, so if someone can do it, it would be fantastic. Best wishes Harald |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bottlenecks in an Excel VBA application
Thank you again, Harald.
Your help is very appreciated but remains the hard way to do the job. I have over 30 modules of code in different workbooks, worksheets, addins and templates and over 25000 lines of code. The code is well built and the application works fine but it was developed over a 5 year period and some optimization is now needed for performance over the network. I sure will use your comments on good VBA programming and check for all those possible problems you wrote about. Thank you again. "Harald Staff" wrote: "Jac Tremblay" skrev i melding ... Hi Harald, What you say is right and I am exactly that person. But I am not an MVP nor an expert and I thought that a tool could speed up my work. Hi again Jack Apologies <g. I don't know of any place where those are collected. These are the things I look for first, maybe what you already do: - Calculation turned off when entering into cells ? - Screenupdating turned off while code does things to sheets ? - Any unneeded Select or Activate actions ? (Do a Search for those words and see if there are any and what they do if so) - Any undeclared or lousy declared memory eating variables or collections ? - Efficient object coding ? ("Set Ws = ThisWorkbook.Sheets(1)" instead of looking for and activating Workbook and Sheet1 again and again by code) - Any meaningless tests ? (Like all cells in a range instead of all cells with numeric constants ?) - Any inefficient tests ? (Test for the least likely first. Say we want to find all swedish male persons in the world by testing everybody. This if male then if swedish then here the inner test runs billions of times but this if swedish then if male then here the inner test runs only 9 million times) Also, for a start I place MsgBoxes all over the code to see which parts that runs fast and which that I have to wait for. I can't imagine how a program could spot these things. But lots of clever people are in here, so if someone can do it, it would be fantastic. Best wishes Harald |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bottlenecks in an Excel VBA application
I wish that I had the magic tool to save you the hours of work that
you'd like to avoid. Here are a few links that cover some issues. Chip Pearson http://www.cpearson.com/excel/optimize.htm Charles Williams http://www.decisionmodels.com/optspeed.htm Optimizing for Size and Speed http://www.microsoft.com/officedev/a...pg/013/013.htm In this article we'll disclose the ideal methods for maximizing loading performance and minimizing file size http://archive.baarns.com/excel/develop/vbaperfm.asp HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- Hi everyone, I need to evaluate what parts of an Excel Application are slower than others so that I can work on them to make the overall performance better. The application was built over a 5 year period by someone I don't know and is now in Excel 2000. Is there a tool (an application) that can easily identify the lines of code in an Excel application that cause (or may cause) a performance problem. I'm looking for a tool like Golden that can be used to evaluate an Oracle application or query. If that does not exist, is there another way to pinpoint the parts of an application that should be optimized or checked? The application contains about 24000 lines of VBA code in 22 modules and uses many Excel files with different extensions (.xls, .xlt, .xla). Thank you for your help. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bottlenecks in an Excel VBA application
What you describe is a profiler:
http://www.google.com/search?hl=en&l...BA&btnG=Search -- Regards, Tom Ogilvy "Jac Tremblay" wrote in message ... Thank you again, Harald. Your help is very appreciated but remains the hard way to do the job. I have over 30 modules of code in different workbooks, worksheets, addins and templates and over 25000 lines of code. The code is well built and the application works fine but it was developed over a 5 year period and some optimization is now needed for performance over the network. I sure will use your comments on good VBA programming and check for all those possible problems you wrote about. Thank you again. "Harald Staff" wrote: "Jac Tremblay" skrev i melding ... Hi Harald, What you say is right and I am exactly that person. But I am not an MVP nor an expert and I thought that a tool could speed up my work. Hi again Jack Apologies <g. I don't know of any place where those are collected. These are the things I look for first, maybe what you already do: - Calculation turned off when entering into cells ? - Screenupdating turned off while code does things to sheets ? - Any unneeded Select or Activate actions ? (Do a Search for those words and see if there are any and what they do if so) - Any undeclared or lousy declared memory eating variables or collections ? - Efficient object coding ? ("Set Ws = ThisWorkbook.Sheets(1)" instead of looking for and activating Workbook and Sheet1 again and again by code) - Any meaningless tests ? (Like all cells in a range instead of all cells with numeric constants ?) - Any inefficient tests ? (Test for the least likely first. Say we want to find all swedish male persons in the world by testing everybody. This if male then if swedish then here the inner test runs billions of times but this if swedish then if male then here the inner test runs only 9 million times) Also, for a start I place MsgBoxes all over the code to see which parts that runs fast and which that I have to wait for. I can't imagine how a program could spot these things. But lots of clever people are in here, so if someone can do it, it would be fantastic. Best wishes Harald |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bottlenecks in an Excel VBA application
Thank you Tom,
That is exactly what I was looking for. "Tom Ogilvy" wrote: What you describe is a profiler: http://www.google.com/search?hl=en&l...BA&btnG=Search -- Regards, Tom Ogilvy "Jac Tremblay" wrote in message ... Thank you again, Harald. Your help is very appreciated but remains the hard way to do the job. I have over 30 modules of code in different workbooks, worksheets, addins and templates and over 25000 lines of code. The code is well built and the application works fine but it was developed over a 5 year period and some optimization is now needed for performance over the network. I sure will use your comments on good VBA programming and check for all those possible problems you wrote about. Thank you again. "Harald Staff" wrote: "Jac Tremblay" skrev i melding ... Hi Harald, What you say is right and I am exactly that person. But I am not an MVP nor an expert and I thought that a tool could speed up my work. Hi again Jack Apologies <g. I don't know of any place where those are collected. These are the things I look for first, maybe what you already do: - Calculation turned off when entering into cells ? - Screenupdating turned off while code does things to sheets ? - Any unneeded Select or Activate actions ? (Do a Search for those words and see if there are any and what they do if so) - Any undeclared or lousy declared memory eating variables or collections ? - Efficient object coding ? ("Set Ws = ThisWorkbook.Sheets(1)" instead of looking for and activating Workbook and Sheet1 again and again by code) - Any meaningless tests ? (Like all cells in a range instead of all cells with numeric constants ?) - Any inefficient tests ? (Test for the least likely first. Say we want to find all swedish male persons in the world by testing everybody. This if male then if swedish then here the inner test runs billions of times but this if swedish then if male then here the inner test runs only 9 million times) Also, for a start I place MsgBoxes all over the code to see which parts that runs fast and which that I have to wait for. I can't imagine how a program could spot these things. But lots of clever people are in here, so if someone can do it, it would be fantastic. Best wishes Harald |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bottlenecks in an Excel VBA application
Hi Rob,
Thank you for the reference to your site. That will surely help me when I get more time to check it out. What I have seen so far is very interesting. "Rob van Gelder" wrote: It's not the easy way - it's the hard way, but... I have speed testing routines on my website. -- Rob van Gelder - http://www.vangelder.co.nz/excel "Jac Tremblay" wrote in message ... Hi everyone, I need to evaluate what parts of an Excel Application are slower than others so that I can work on them to make the overall performance better. The application was built over a 5 year period by someone I don't know and is now in Excel 2000. Is there a tool (an application) that can easily identify the lines of code in an Excel application that cause (or may cause) a performance problem. I'm looking for a tool like Golden that can be used to evaluate an Oracle application or query. If that does not exist, is there another way to pinpoint the parts of an application that should be optimized or checked? The application contains about 24000 lines of VBA code in 22 modules and uses many Excel files with different extensions (.xls, .xlt, .xla). Thank you for your help. -- Jac Tremblay |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bottlenecks in an Excel VBA application
Hi Paul,
Your comment is very pertinent and interesting. I found many informations that will help me. Thank you very much. " wrote: I wish that I had the magic tool to save you the hours of work that you'd like to avoid. Here are a few links that cover some issues. Chip Pearson http://www.cpearson.com/excel/optimize.htm Charles Williams http://www.decisionmodels.com/optspeed.htm Optimizing for Size and Speed http://www.microsoft.com/officedev/a...pg/013/013.htm In this article we'll disclose the ideal methods for maximizing loading performance and minimizing file size http://archive.baarns.com/excel/develop/vbaperfm.asp HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- Hi everyone, I need to evaluate what parts of an Excel Application are slower than others so that I can work on them to make the overall performance better. The application was built over a 5 year period by someone I don't know and is now in Excel 2000. Is there a tool (an application) that can easily identify the lines of code in an Excel application that cause (or may cause) a performance problem. I'm looking for a tool like Golden that can be used to evaluate an Oracle application or query. If that does not exist, is there another way to pinpoint the parts of an application that should be optimized or checked? The application contains about 24000 lines of VBA code in 22 modules and uses many Excel files with different extensions (.xls, .xlt, .xla). Thank you for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Application | Excel Discussion (Misc queries) | |||
Application/vnd.ms-excel | Excel Discussion (Misc queries) | |||
macro to close excel application other than application.quit | Excel Programming | |||
Excel Application needs a DLL | Excel Programming | |||
application.quit will not shut off application | Excel Programming |