Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.ScreenUpdating broken
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.ScreenUpdating broken
The following sample code does NOT turn off screenupdating:
Sub tstUpdate() Application.ScreenUpdating = False Cells(1, 1) = "ABCDE" Application.ScreenUpdating = True End Sub 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 "Dave Unger" wrote in message oups.com... | 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 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.ScreenUpdating broken
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 Jim Rech wrote: The following sample code does NOT turn off screenupdating: Sub tstUpdate() Application.ScreenUpdating = False Cells(1, 1) = "ABCDE" Application.ScreenUpdating = True End Sub 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.ScreenUpdating broken
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" wrote in message oups.com... 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 Jim Rech wrote: The following sample code does NOT turn off screenupdating: Sub tstUpdate() Application.ScreenUpdating = False Cells(1, 1) = "ABCDE" Application.ScreenUpdating = True End Sub 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.ScreenUpdating broken
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 wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.ScreenUpdating broken
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 "Dave Unger" wrote in message ups.com... 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to set Application.ScreenUpdating = False for Gen use | Excel Discussion (Misc queries) | |||
PLEASE PLEASE HELP Application.Screenupdating | Excel Programming | |||
Application.ScreenUpdating MsgBox | Excel Programming | |||
SendKeys and Application.ScreenUpdating | Excel Programming | |||
Application.ScreenUpdating = False | Excel Programming |