Application.ScreenUpdating broken

[Dave Unger]

Hello,

Suddenly, I can't turn off ScreenUpdating (Excel 97). If I set it to
False,
it will show False in the Watch window, Debug.print will also show it
as False,
yet when I slide my cursor over the command line, it shows the value =
True.
It's always worked until now, and the problem seems to coincide with
some
experimentation with Automation, running Excel from Word. I've tried
loading Excel
without any Add-Ins or Personal.xls, to no avail.

The following sample code does NOT turn off screenupdating:

Sub tstUpdate()

Application.ScreenUpdating = False
Cells(1, 1) = "ABCDE"
Application.ScreenUpdating = True

End Sub

But this code works as expected:

Sub tstUpdate()

Dim xlApp As Object, MySht As Object

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Add
Set MySht = xlApp.Activesheet

xlApp.ScreenUpdating = False
MySht.Cells(1, 1) = "ABCD"
xlApp.ScreenUpdating = True

xlApp.Quit
Set xlApp = Nothing

End Sub

Any help appreciated.

Regards,

Dave

 

[Jim Rech]

The following sample code does NOT turn off screenupdating:

How can you possibly tell? That code run instanteously. You mean your eyes
can detect the appearance of the text before screen updating is turned on?
Or do you mean you are stepping throught the code in debugging mode? When
debugging screen updating is automatically turned on. That does not mean
that it's on running at full speed.


--
Jim
| Hello,
|
| Suddenly, I can't turn off ScreenUpdating (Excel 97). If I set it to
| False,
| it will show False in the Watch window, Debug.print will also show it
| as False,
| yet when I slide my cursor over the command line, it shows the value =
| True.
| It's always worked until now, and the problem seems to coincide with
| some
| experimentation with Automation, running Excel from Word. I've tried
| loading Excel
| without any Add-Ins or Personal.xls, to no avail.
|
| The following sample code does NOT turn off screenupdating:
|
| Sub tstUpdate()
|
| Application.ScreenUpdating = False
| Cells(1, 1) = "ABCDE"
| Application.ScreenUpdating = True
|
| End Sub
|
| But this code works as expected:
|
| Sub tstUpdate()
|
| Dim xlApp As Object, MySht As Object
|
| Set xlApp = CreateObject("Excel.Application")
| xlApp.Visible = True
| xlApp.Workbooks.Add
| Set MySht = xlApp.Activesheet
|
| xlApp.ScreenUpdating = False
| MySht.Cells(1, 1) = "ABCD"
| xlApp.ScreenUpdating = True
|
| xlApp.Quit
| Set xlApp = Nothing
|
| End Sub
|
| Any help appreciated.
|
| Regards,
|
| Dave
|

 

[Dave Unger]

Jim,

I'm quite surprised that I can't find any documentation that alerts
one to this - either in the online Help, or any other VBA book I've
read to date.

However, you're absolutely right - if I add a message box to pause
execution in the 1st code sample, I can see that ScreenUpdating does
get turned off. In the past, I must have added the ScreenUpdating
lines after I had de-bugged the code.

I guess what confused me is the fact that in the 2nd code sample,
ScreenUpdating does indeed get turned off when stepping through it in
debug mode. Not sure why that would be.

Anyway, thanks for your help,

Regards

DaveU

 

[NickHK]

Dave,
It makes sense to me really, although I've not seen it documented:
When stepping through Excel VBA code, it would be somewhat pointless if
..ScreenUpdating=false, because there would be no way to see what you are
doing. I would imagine the VBA IDE set it to True as soon as you enter break
mode. If you code does not affect the Excel UI, you see no difference, but
if it does, you see the changes.
However, when executing the code from outside (VB6, Word VBA etc), Excel
only respond to the automation requests and has not context/reason as to
why.

NickHK

 

[Dave Unger]

Hi Nick,

Thanks for that explanation. Now that you've pointed it out, it makes
sense to me, too. I do feel a bit foolish for not realizing that
ScreenUpdating was working b4 starting this thread, and wasting
everyones' time on something so trivial. I've made significant
advances since starting with VBA, but am still very much in learning
mode. And I can and do get 'de-railed' every once in a while. Thanks
for your patience and help

regards,

DaveU

 

[NickHK]

Dave,
I wouldn't call it an explanation as it's speculation on my part, but it
seems to fit.
As with most aspects that people claim are "broken", that normally is not
the case.
It is working correctly for settings/environment in affect...you just have
to know and understand what these are.

As for feeling foolish, I would use much stronger words for some of my own
shortcomings.

NickHK