Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BOLD font slows VBA performance in Excel 2003
In investigating slow performance in one of my projects (excel 2003) I've found something interesting which maybe one of you may be able to comment on. A simple worksheet_change event to change the interior color, font color and highlight bold a cell when its value is changed. with target.cells ..interior.color = RGB (...colour codes etc...) ..font.color = vbBlue ..font.bold = True end with Sometimes running the code and it returned control within a second, other times it went away for seconds (count to five!!) before returning control. Other times first 1 - 5 changes are fast and OK, then they slow down, then the next batch would be OK again. Spent hours trying to analyse and improve performance in the code In the end I started commenting out lines one at a time until I saw an improvement. What do you know, commenting out the .bold line suddenly put everything back to lightening speed and consistently too. I then messed around with italic and underline settings and both retained a lightening fast passing of control back to the user. Final control was to put the .bold line back; what do you know, wait increased to 3+ seconds again. I'm sure at one time I also saw the BOLD toolbar icon "flicker" too but not expected given I had screen updating set to false. Suffice it to say I'm currently showing changed fields with background color, font colour and underline set at the moment. Any ideas. Possibly a bug in Excel 2003 by chance? Regards Julian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
BOLD font slows VBA performance in Excel 2003
How about a complete guess????
Maybe it's caused by excel going off to the printer driver to see how that cell should be displayed. If you change to a different printer driver (install a different one--even if you don't have the physical hardware), maybe you could test it out. And if there's a difference, maybe going to your printer's site and getting a new driver would help. Juwi_uk wrote: In investigating slow performance in one of my projects (excel 2003) I've found something interesting which maybe one of you may be able to comment on. A simple worksheet_change event to change the interior color, font color and highlight bold a cell when its value is changed. with target.cells .interior.color = RGB (...colour codes etc...) .font.color = vbBlue .font.bold = True end with Sometimes running the code and it returned control within a second, other times it went away for seconds (count to five!!) before returning control. Other times first 1 - 5 changes are fast and OK, then they slow down, then the next batch would be OK again. Spent hours trying to analyse and improve performance in the code In the end I started commenting out lines one at a time until I saw an improvement. What do you know, commenting out the .bold line suddenly put everything back to lightening speed and consistently too. I then messed around with italic and underline settings and both retained a lightening fast passing of control back to the user. Final control was to put the .bold line back; what do you know, wait increased to 3+ seconds again. I'm sure at one time I also saw the BOLD toolbar icon "flicker" too but not expected given I had screen updating set to false. Suffice it to say I'm currently showing changed fields with background color, font colour and underline set at the moment. Any ideas. Possibly a bug in Excel 2003 by chance? Regards Julian -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
BOLD font slows VBA performance in Excel 2003
If I recall correctly, someone else posted a similar problem.
When they switched from a network printer to a local printer the problem went away. Maybe you can do that, well, if it applies. Dave Peterson wrote: How about a complete guess???? Maybe it's caused by excel going off to the printer driver to see how that cell should be displayed. If you change to a different printer driver (install a different one--even if you don't have the physical hardware), maybe you could test it out. And if there's a difference, maybe going to your printer's site and getting a new driver would help. Juwi_uk wrote: In investigating slow performance in one of my projects (excel 2003) I've found something interesting which maybe one of you may be able to comment on. A simple worksheet_change event to change the interior color, font color and highlight bold a cell when its value is changed. with target.cells .interior.color = RGB (...colour codes etc...) .font.color = vbBlue .font.bold = True end with Sometimes running the code and it returned control within a second, other times it went away for seconds (count to five!!) before returning control. Other times first 1 - 5 changes are fast and OK, then they slow down, then the next batch would be OK again. Spent hours trying to analyse and improve performance in the code In the end I started commenting out lines one at a time until I saw an improvement. What do you know, commenting out the .bold line suddenly put everything back to lightening speed and consistently too. I then messed around with italic and underline settings and both retained a lightening fast passing of control back to the user. Final control was to put the .bold line back; what do you know, wait increased to 3+ seconds again. I'm sure at one time I also saw the BOLD toolbar icon "flicker" too but not expected given I had screen updating set to false. Suffice it to say I'm currently showing changed fields with background color, font colour and underline set at the moment. Any ideas. Possibly a bug in Excel 2003 by chance? Regards Julian -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
BOLD font slows VBA performance in Excel 2003
Dave,
Many thanks for the tip; I'll need to try that and report back. Interestingly enough I was working offline on my business laptop at the time so network printers (my default printer is a network printer) may be causing the problem as you say. It's interesting though that it does affect for example choosing italic, underline etc just bold. I would have thought the first 2 would alos ned to check the printer driver too. Regards Julian "Dave Peterson" wrote: If I recall correctly, someone else posted a similar problem. When they switched from a network printer to a local printer the problem went away. Maybe you can do that, well, if it applies. Dave Peterson wrote: How about a complete guess???? Maybe it's caused by excel going off to the printer driver to see how that cell should be displayed. If you change to a different printer driver (install a different one--even if you don't have the physical hardware), maybe you could test it out. And if there's a difference, maybe going to your printer's site and getting a new driver would help. Juwi_uk wrote: In investigating slow performance in one of my projects (excel 2003) I've found something interesting which maybe one of you may be able to comment on. A simple worksheet_change event to change the interior color, font color and highlight bold a cell when its value is changed. with target.cells .interior.color = RGB (...colour codes etc...) .font.color = vbBlue .font.bold = True end with Sometimes running the code and it returned control within a second, other times it went away for seconds (count to five!!) before returning control. Other times first 1 - 5 changes are fast and OK, then they slow down, then the next batch would be OK again. Spent hours trying to analyse and improve performance in the code In the end I started commenting out lines one at a time until I saw an improvement. What do you know, commenting out the .bold line suddenly put everything back to lightening speed and consistently too. I then messed around with italic and underline settings and both retained a lightening fast passing of control back to the user. Final control was to put the .bold line back; what do you know, wait increased to 3+ seconds again. I'm sure at one time I also saw the BOLD toolbar icon "flicker" too but not expected given I had screen updating set to false. Suffice it to say I'm currently showing changed fields with background color, font colour and underline set at the moment. Any ideas. Possibly a bug in Excel 2003 by chance? Regards Julian -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
BOLD font slows VBA performance in Excel 2003
I try not to think when I can test <vbg.
Please post back with your results. It should be interesting and google will have it for the next victim, er, person. Juwi_uk wrote: Dave, Many thanks for the tip; I'll need to try that and report back. Interestingly enough I was working offline on my business laptop at the time so network printers (my default printer is a network printer) may be causing the problem as you say. It's interesting though that it does affect for example choosing italic, underline etc just bold. I would have thought the first 2 would alos ned to check the printer driver too. Regards Julian "Dave Peterson" wrote: If I recall correctly, someone else posted a similar problem. When they switched from a network printer to a local printer the problem went away. Maybe you can do that, well, if it applies. Dave Peterson wrote: How about a complete guess???? Maybe it's caused by excel going off to the printer driver to see how that cell should be displayed. If you change to a different printer driver (install a different one--even if you don't have the physical hardware), maybe you could test it out. And if there's a difference, maybe going to your printer's site and getting a new driver would help. Juwi_uk wrote: In investigating slow performance in one of my projects (excel 2003) I've found something interesting which maybe one of you may be able to comment on. A simple worksheet_change event to change the interior color, font color and highlight bold a cell when its value is changed. with target.cells .interior.color = RGB (...colour codes etc...) .font.color = vbBlue .font.bold = True end with Sometimes running the code and it returned control within a second, other times it went away for seconds (count to five!!) before returning control. Other times first 1 - 5 changes are fast and OK, then they slow down, then the next batch would be OK again. Spent hours trying to analyse and improve performance in the code In the end I started commenting out lines one at a time until I saw an improvement. What do you know, commenting out the .bold line suddenly put everything back to lightening speed and consistently too. I then messed around with italic and underline settings and both retained a lightening fast passing of control back to the user. Final control was to put the .bold line back; what do you know, wait increased to 3+ seconds again. I'm sure at one time I also saw the BOLD toolbar icon "flicker" too but not expected given I had screen updating set to false. Suffice it to say I'm currently showing changed fields with background color, font colour and underline set at the moment. Any ideas. Possibly a bug in Excel 2003 by chance? Regards Julian -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bold Font | Excel Discussion (Misc queries) | |||
Scrolling performance with filter in Excel 2003 | Excel Discussion (Misc queries) | |||
how do you create an importance-performance chart in excel 2003? | Charts and Charting in Excel | |||
How to show only bold font ? | Excel Worksheet Functions | |||
Excel 2003 performance problems | Excel Discussion (Misc queries) |