Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
identical macros run fast and slow
I have written two identical Macros using Visual Basic. The only difference
in the code are the variables that are inputted. the code contains directions that cause lines to be hidden or cells to be erased based upon either the color or content of the cells. One macro runs fast but the other runs very slow. Ive contacted IBM and they say it is not the processor. When viewing the code in the debug mode the selection.clearcontents seems to cause a pause in the execution but only in the one macro. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
identical macros run fast and slow
If you're hiding rows or columns, then excel may be trying to determine where
those little dotted lines go (the ones you see after you do File|print preview). If you delete/hide/show lots of rows, this can slow your macro down. As a test, try this on the slow version. Open your workbook tools|options|view tab|uncheck Page Breaks Then run your macro. If it works fast, then maybe you found the problem. === Saved from a previous post... There are some other settings that can make your code work faster: Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ActiveSheet.DisplayPageBreaks = False The first stops the screen flickering when you select different sheets (but it's even better to not select sheets/ranges/objects). The second & third line turns calculation to manual (and remembers the current setting). You'll want to turn it back to what it was before you started at the end: Application.calculation = calcmode And the last tells excel to not worry about where to show those dotted lines (where page breaks would be). This is useful if your macro inserts/deletes rows or columns. markwattwood wrote: I have written two identical Macros using Visual Basic. The only difference in the code are the variables that are inputted. the code contains directions that cause lines to be hidden or cells to be erased based upon either the color or content of the cells. One macro runs fast but the other runs very slow. Ive contacted IBM and they say it is not the processor. When viewing the code in the debug mode the selection.clearcontents seems to cause a pause in the execution but only in the one macro. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
identical macros run fast and slow
"Dave Peterson" wrote: If you're hiding rows or columns, then excel may be trying to determine where those little dotted lines go (the ones you see after you do File|print preview). If you delete/hide/show lots of rows, this can slow your macro down. As a test, try this on the slow version. Open your workbook tools|options|view tab|uncheck Page Breaks Then run your macro. If it works fast, then maybe you found the problem. === Saved from a previous post... There are some other settings that can make your code work faster: Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ActiveSheet.DisplayPageBreaks = False The first stops the screen flickering when you select different sheets (but it's even better to not select sheets/ranges/objects). The second & third line turns calculation to manual (and remembers the current setting). You'll want to turn it back to what it was before you started at the end: Application.calculation = calcmode And the last tells excel to not worry about where to show those dotted lines (where page breaks would be). This is useful if your macro inserts/deletes rows or columns. markwattwood wrote: I have written two identical Macros using Visual Basic. The only difference in the code are the variables that are inputted. the code contains directions that cause lines to be hidden or cells to be erased based upon either the color or content of the cells. One macro runs fast but the other runs very slow. Ive contacted IBM and they say it is not the processor. When viewing the code in the debug mode the selection.clearcontents seems to cause a pause in the execution but only in the one macro. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
identical macros run fast and slow
I checked the view bar and you are right. the page breaks is not checked in
one, but in the slow one it is in grey and will not uncheck. Ive unprotected the sheet . All other settings are identical to the fast macro. "Dave Peterson" wrote: If you're hiding rows or columns, then excel may be trying to determine where those little dotted lines go (the ones you see after you do File|print preview). If you delete/hide/show lots of rows, this can slow your macro down. As a test, try this on the slow version. Open your workbook tools|options|view tab|uncheck Page Breaks Then run your macro. If it works fast, then maybe you found the problem. === Saved from a previous post... There are some other settings that can make your code work faster: Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ActiveSheet.DisplayPageBreaks = False The first stops the screen flickering when you select different sheets (but it's even better to not select sheets/ranges/objects). The second & third line turns calculation to manual (and remembers the current setting). You'll want to turn it back to what it was before you started at the end: Application.calculation = calcmode And the last tells excel to not worry about where to show those dotted lines (where page breaks would be). This is useful if your macro inserts/deletes rows or columns. markwattwood wrote: I have written two identical Macros using Visual Basic. The only difference in the code are the variables that are inputted. the code contains directions that cause lines to be hidden or cells to be erased based upon either the color or content of the cells. One macro runs fast but the other runs very slow. Ive contacted IBM and they say it is not the processor. When viewing the code in the debug mode the selection.clearcontents seems to cause a pause in the execution but only in the one macro. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
identical macros run fast and slow
It sounds like you're viewing the window in Page Break Preview mode.
View|Normal And with that added code, it should work quicker. markwattwood wrote: I checked the view bar and you are right. the page breaks is not checked in one, but in the slow one it is in grey and will not uncheck. Ive unprotected the sheet . All other settings are identical to the fast macro. "Dave Peterson" wrote: If you're hiding rows or columns, then excel may be trying to determine where those little dotted lines go (the ones you see after you do File|print preview). If you delete/hide/show lots of rows, this can slow your macro down. As a test, try this on the slow version. Open your workbook tools|options|view tab|uncheck Page Breaks Then run your macro. If it works fast, then maybe you found the problem. === Saved from a previous post... There are some other settings that can make your code work faster: Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ActiveSheet.DisplayPageBreaks = False The first stops the screen flickering when you select different sheets (but it's even better to not select sheets/ranges/objects). The second & third line turns calculation to manual (and remembers the current setting). You'll want to turn it back to what it was before you started at the end: Application.calculation = calcmode And the last tells excel to not worry about where to show those dotted lines (where page breaks would be). This is useful if your macro inserts/deletes rows or columns. markwattwood wrote: I have written two identical Macros using Visual Basic. The only difference in the code are the variables that are inputted. the code contains directions that cause lines to be hidden or cells to be erased based upon either the color or content of the cells. One macro runs fast but the other runs very slow. Ive contacted IBM and they say it is not the processor. When viewing the code in the debug mode the selection.clearcontents seems to cause a pause in the execution but only in the one macro. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
identical macros run fast and slow
right again
thanks "Dave Peterson" wrote: It sounds like you're viewing the window in Page Break Preview mode. View|Normal And with that added code, it should work quicker. markwattwood wrote: I checked the view bar and you are right. the page breaks is not checked in one, but in the slow one it is in grey and will not uncheck. Ive unprotected the sheet . All other settings are identical to the fast macro. "Dave Peterson" wrote: If you're hiding rows or columns, then excel may be trying to determine where those little dotted lines go (the ones you see after you do File|print preview). If you delete/hide/show lots of rows, this can slow your macro down. As a test, try this on the slow version. Open your workbook tools|options|view tab|uncheck Page Breaks Then run your macro. If it works fast, then maybe you found the problem. === Saved from a previous post... There are some other settings that can make your code work faster: Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ActiveSheet.DisplayPageBreaks = False The first stops the screen flickering when you select different sheets (but it's even better to not select sheets/ranges/objects). The second & third line turns calculation to manual (and remembers the current setting). You'll want to turn it back to what it was before you started at the end: Application.calculation = calcmode And the last tells excel to not worry about where to show those dotted lines (where page breaks would be). This is useful if your macro inserts/deletes rows or columns. markwattwood wrote: I have written two identical Macros using Visual Basic. The only difference in the code are the variables that are inputted. the code contains directions that cause lines to be hidden or cells to be erased based upon either the color or content of the cells. One macro runs fast but the other runs very slow. Ive contacted IBM and they say it is not the processor. When viewing the code in the debug mode the selection.clearcontents seems to cause a pause in the execution but only in the one macro. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros Running Sometimes Slow Sometimes Fast | Excel Discussion (Misc queries) | |||
Runs fast then slow | Excel Programming | |||
Searches slow for some Fast for others? | Excel Worksheet Functions | |||
Why macro slow on one, fast on another? | Excel Programming | |||
It was SLOW, now is FAST! | Excel Programming |