Stepping through code gives different results than running it!

[Simon White]

Hi,

I have some very simple VBA6 code, which gives an error
when run. But when I step through it, it runs fine
without giving the error!

More specifically, the code is trying to un-protect a
worksheet, then refresh a DataQuery on the worksheet, then
protect the worksheet again.

The code is:

------------------------------------------------
Public Sub refreshAll()

Dim timeSheet As Worksheet

Dim protectionDrawingObjectsState As Boolean
Dim protectionContentsState As Boolean
Dim protectionScenariosState As Boolean
Dim protectionUIState As Boolean

On Error GoTo ErrHandler

// Break here
Set timeSheet = ActiveWorkbook.Sheets("Timesheet")

' Temporarily unprotect the time sheet
protectionDrawingObjectsState =
timeSheet.ProtectDrawingObjects
protectionContentsState = timeSheet.ProtectContents
protectionScenariosState = timeSheet.ProtectScenarios
protectionUIState = timeSheet.ProtectionMode
timeSheet.Unprotect (PROTECTION_PASSWORD)

' Refresh all the query tables
ActiveWorkbook.refreshAll

' Restore protection and exit
GoSub RestoreProtection
Exit Sub


RestoreProtection:
Call timeSheet.Protect(PROTECTION_PASSWORD,
protectionDrawingObjectsState, protectionContentsState,
protectionScenariosState, protectionUIState)
Return

ErrHandler:
GoSub RestoreProtection
MsgBox Err.Description
Exit Sub

End Sub
------------------------------------------------

There is only one data query in the workbook, and it is on
the Timesheet sheet.

If I break at the top, and then step through it (with
Shift F8), it works fine - the data query gets refreshed,
and the sheet returns to its protected state.

If I break at the top, then run (with F5), it gives the
following error:

"The cell or chart you are trying to change is protected
and therefore read-only. To modify a protected cell or
chart, first remove the protection ... etc."

Please help - I don't understand why, if there is a
problem, I don't get the error while stepping through.

Thanks for any advice,
Simon.

 

[microsoft]

Well, I found the trouble, if anyone else is getting the same problem.

The problem was, my data table query was set to "enable background refresh".
So it was un-protecting the sheet, then triggering a refresh, but then
protecting the sheet again before it had a chance to actually finish
refreshing it!

That's why it was working ok when stepping through - it had more time to do
it!

(Very pleased with myself! ... )

 

[Howie]

I'm not very familiar with the DataQuery / Refresh thing,
but have often found that when something works during
single-step, but not with a normal run, that a DoEvents is
required. Gives Excel the opportunity to do it's thing
during user code execution. Perhaps a DoEvents before and
after the refresh. Just an idea.