How to control the Excel VBA Editor Main Window Caption

[stuartt]

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).

 

[Barb Reinhardt]

Have you tried

Application.StatusBar = FALSE

I think that just turns off whatever is in the statusbar though.

 

[Charles Williams]

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

 

[Peter T]

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

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

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).

 

[stuartt]

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.

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

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

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).

 

[Peter T]

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

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.

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

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

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).

 

[Charles Williams]

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

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.

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

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

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).