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

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

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" <(E-Mail Removed)> wrote in message
news:9F5353D2-92BD-4C3D-9BBB-(E-Mail Removed)...
> 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).

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:9F5353D2-92BD-4C3D-9BBB-(E-Mail Removed)...
> > 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).
>
>

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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:9F5353D2-92BD-4C3D-9BBB-(E-Mail Removed)...
> > > 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).
> >
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:37CEA827-352D-4B37-9C70-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:9F5353D2-92BD-4C3D-9BBB-(E-Mail Removed)...
> > > > 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).
> > >
> > >
> >
> >
> >

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" <(E-Mail Removed)> wrote in message
news:37CEA827-352D-4B37-9C70-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> > news:9F5353D2-92BD-4C3D-9BBB-(E-Mail Removed)...
>> > > 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).
>> >
>> >
>>
>>
>>