How to control the Excel VBA Editor Main Window Caption
Excel updates the Caption of the VBA Editor Main Window every time a Macro
runs, adding the text [running]. When the macro is finished, the text is removed. This causes problems when there are 1000s of calls to macros per second, and I would like to be able to disable this feature (it still occurs when the Main Window is hidden). |
How to control the Excel VBA Editor Main Window Caption
Have you tried
Application.StatusBar = FALSE I think that just turns off whatever is in the statusbar though. -- HTH, Barb Reinhardt "stuartt" wrote: Excel updates the Caption of the VBA Editor Main Window every time a Macro runs, adding the text [running]. When the macro is finished, the text is removed. This causes problems when there are 1000s of calls to macros per second, and I would like to be able to disable this feature (it still occurs when the Main Window is hidden). |
How to control the Excel VBA Editor Main Window Caption
This is a known bug in all Excel versions through Excel2007 SP1.
I have not found a way of completely eliminating this problem in all circumstances, but this may help: - if you are using VBA UDF's (most common cause of 1000's of calls) then initiating calculation from VBA stops this happening (create an Application.Calculate sub which is called every time someone presses F9 etc by using ONKEY). This will not help if you are using Automatic calculation mode. - closing all VBE windows and minimising the VBE helps a bit. - using Windows API calls to block the Caption refresh does not seem to help. - if you close all VBE windows, save, close Excel and then reopen Excel without opening up the VBE the time taken by the VBE caption refresh will be somewhat reduced. - if you convert all your VBA to compiled VB6 the VBE caption will not be refreshed. regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "stuartt" wrote in message ... Excel updates the Caption of the VBA Editor Main Window every time a Macro runs, adding the text [running]. When the macro is finished, the text is removed. This causes problems when there are 1000s of calls to macros per second, and I would like to be able to disable this feature (it still occurs when the Main Window is hidden). |
How to control the Excel VBA Editor Main Window Caption
Hi Charles,
I've also noticed this, the caption even updates if the VBE has never been opened in the session of a given instance. However I've not found a fast enough way of testing if updates occur on each call from a cell formula to a UDF, or merely on a single calculate event that may trigger many calls. If the latter the effect would be trivial. Regards, Peter T "Charles Williams" wrote in message ... This is a known bug in all Excel versions through Excel2007 SP1. I have not found a way of completely eliminating this problem in all circumstances, but this may help: - if you are using VBA UDF's (most common cause of 1000's of calls) then initiating calculation from VBA stops this happening (create an Application.Calculate sub which is called every time someone presses F9 etc by using ONKEY). This will not help if you are using Automatic calculation mode. - closing all VBE windows and minimising the VBE helps a bit. - using Windows API calls to block the Caption refresh does not seem to help. - if you close all VBE windows, save, close Excel and then reopen Excel without opening up the VBE the time taken by the VBE caption refresh will be somewhat reduced. - if you convert all your VBA to compiled VB6 the VBE caption will not be refreshed. regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "stuartt" wrote in message ... Excel updates the Caption of the VBA Editor Main Window every time a Macro runs, adding the text [running]. When the macro is finished, the text is removed. This causes problems when there are 1000s of calls to macros per second, and I would like to be able to disable this feature (it still occurs when the Main Window is hidden). |
How to control the Excel VBA Editor Main Window Caption
Hi, thanks for the suggestions Charles. Just a little further inforamtion, I
found this problem whilst investigating an increase in the percentage CPU utilization of the explorer.exe process. Explorer has a Shell Hook that is triggered by the windows redraw events, presumably so that it can update the buttons on the task bar with the correct window caption text. Monitoring explorer with spy++ I found that excel was triggering thousands of messages that were being processed by explorer. So this issue can definitely be responsible for serious system degradation. Stuart. "Peter T" wrote: Hi Charles, I've also noticed this, the caption even updates if the VBE has never been opened in the session of a given instance. However I've not found a fast enough way of testing if updates occur on each call from a cell formula to a UDF, or merely on a single calculate event that may trigger many calls. If the latter the effect would be trivial. Regards, Peter T "Charles Williams" wrote in message ... This is a known bug in all Excel versions through Excel2007 SP1. I have not found a way of completely eliminating this problem in all circumstances, but this may help: - if you are using VBA UDF's (most common cause of 1000's of calls) then initiating calculation from VBA stops this happening (create an Application.Calculate sub which is called every time someone presses F9 etc by using ONKEY). This will not help if you are using Automatic calculation mode. - closing all VBE windows and minimising the VBE helps a bit. - using Windows API calls to block the Caption refresh does not seem to help. - if you close all VBE windows, save, close Excel and then reopen Excel without opening up the VBE the time taken by the VBE caption refresh will be somewhat reduced. - if you convert all your VBA to compiled VB6 the VBE caption will not be refreshed. regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "stuartt" wrote in message ... Excel updates the Caption of the VBA Editor Main Window every time a Macro runs, adding the text [running]. When the macro is finished, the text is removed. This causes problems when there are 1000s of calls to macros per second, and I would like to be able to disable this feature (it still occurs when the Main Window is hidden). |
How to control the Excel VBA Editor Main Window Caption
Hi Stuart,
Monitoring explorer with spy++ I found that excel was triggering thousands of messages I see what you mean and answers what I wondered previously! Looks like each call to a UDF triggers about 5 messages to the VBE window. Strangely, though not problematic, even editing a cell triggers a number of events to the VBE window. Spy++ doesn't seem able to monitor messages to the VBE window when the VBE is closed, which is not surprising (what is surprising is that the window even exists while the VBE is closed). As I mentioned previously the window is updated when closed as can verified by be verified by other API calls. Regards, Peter T "stuartt" wrote in message ... Hi, thanks for the suggestions Charles. Just a little further inforamtion, I found this problem whilst investigating an increase in the percentage CPU utilization of the explorer.exe process. Explorer has a Shell Hook that is triggered by the windows redraw events, presumably so that it can update the buttons on the task bar with the correct window caption text. Monitoring explorer with spy++ I found that excel was triggering thousands of messages that were being processed by explorer. So this issue can definitely be responsible for serious system degradation. Stuart. "Peter T" wrote: Hi Charles, I've also noticed this, the caption even updates if the VBE has never been opened in the session of a given instance. However I've not found a fast enough way of testing if updates occur on each call from a cell formula to a UDF, or merely on a single calculate event that may trigger many calls. If the latter the effect would be trivial. Regards, Peter T "Charles Williams" wrote in message ... This is a known bug in all Excel versions through Excel2007 SP1. I have not found a way of completely eliminating this problem in all circumstances, but this may help: - if you are using VBA UDF's (most common cause of 1000's of calls) then initiating calculation from VBA stops this happening (create an Application.Calculate sub which is called every time someone presses F9 etc by using ONKEY). This will not help if you are using Automatic calculation mode. - closing all VBE windows and minimising the VBE helps a bit. - using Windows API calls to block the Caption refresh does not seem to help. - if you close all VBE windows, save, close Excel and then reopen Excel without opening up the VBE the time taken by the VBE caption refresh will be somewhat reduced. - if you convert all your VBA to compiled VB6 the VBE caption will not be refreshed. regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "stuartt" wrote in message ... Excel updates the Caption of the VBA Editor Main Window every time a Macro runs, adding the text [running]. When the macro is finished, the text is removed. This causes problems when there are 1000s of calls to macros per second, and I would like to be able to disable this feature (it still occurs when the Main Window is hidden). |
How to control the Excel VBA Editor Main Window Caption
See
http://www.decisionmodels.com/calcsecretsj.htm for timing comparisons (91 seconds reduced to .3 seconds using Application.Calculate rather than F9) Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "stuartt" wrote in message ... Hi, thanks for the suggestions Charles. Just a little further inforamtion, I found this problem whilst investigating an increase in the percentage CPU utilization of the explorer.exe process. Explorer has a Shell Hook that is triggered by the windows redraw events, presumably so that it can update the buttons on the task bar with the correct window caption text. Monitoring explorer with spy++ I found that excel was triggering thousands of messages that were being processed by explorer. So this issue can definitely be responsible for serious system degradation. Stuart. "Peter T" wrote: Hi Charles, I've also noticed this, the caption even updates if the VBE has never been opened in the session of a given instance. However I've not found a fast enough way of testing if updates occur on each call from a cell formula to a UDF, or merely on a single calculate event that may trigger many calls. If the latter the effect would be trivial. Regards, Peter T "Charles Williams" wrote in message ... This is a known bug in all Excel versions through Excel2007 SP1. I have not found a way of completely eliminating this problem in all circumstances, but this may help: - if you are using VBA UDF's (most common cause of 1000's of calls) then initiating calculation from VBA stops this happening (create an Application.Calculate sub which is called every time someone presses F9 etc by using ONKEY). This will not help if you are using Automatic calculation mode. - closing all VBE windows and minimising the VBE helps a bit. - using Windows API calls to block the Caption refresh does not seem to help. - if you close all VBE windows, save, close Excel and then reopen Excel without opening up the VBE the time taken by the VBE caption refresh will be somewhat reduced. - if you convert all your VBA to compiled VB6 the VBE caption will not be refreshed. regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "stuartt" wrote in message ... Excel updates the Caption of the VBA Editor Main Window every time a Macro runs, adding the text [running]. When the macro is finished, the text is removed. This causes problems when there are 1000s of calls to macros per second, and I would like to be able to disable this feature (it still occurs when the Main Window is hidden). |
All times are GMT +1. The time now is 06:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com