Errors when switching sheets/workbooks

[AirforceMook]

Hello there,

I have a worksheet in 2003 that uses a one second timer to update (Application.OnTime) various information.

When I switch to a new tab or move to a new excel sheet, with this still running in the background, I immediately get the "end/debug" box. My thought is that this is because the new tab/book does not contain whatever information the script is attempting to locate. I'm trying to find a way for the script to function normally w/o trying to access anything but its own contents.

I've tried setting a variable for the activework book and sheets, using:

wb.Sheets("Status").Cells(i,4).Value = blah blah blah

My thought is that it would then reference the old workbook and sheets, but it doesn't seem to have any effect. No matter what I do, the error persists.

Any help?

 

[AlphaFrog]

This part should work...
wb.Sheets("Status").Cells(i,4).Value
...even if the workbook or worksheet is not currently active.

The error may be in the blah blah blah part or i may be set to = 0

 

[AirforceMook]

The line itself (without wb) works. It's something about passing the workbook that's the problem.

I have the following code:

dim wb As Workbook
-------------------
Sub OneSecond()
 
      if (IsNull(wb)) then
          wb = ThisWorkbook
      end if
 
      ... other stuff
 
End Sub

In another sub, when I reference "wb" I get the error "Object variable or with block variable not set."

I've dim'd it on what would be considered global scope (above all subs/functions), and no errors exist otherwise. Not sure what's up...

 

[Norie]

Could it be a problem with the code being run every second?

 

[AirforceMook]

Well, yes and no. The OneSecond function just resets itself and calls my "process" sub, which does all the work.

The error I'm getting is down below in the process sub. It's the first time I reference "wb" If I remove the "wb" from that line then the error just occurs on the very next instance of it, so it's definitely a problem with "wb" being empty (even though I just tried to assign it up above).

Not sure if it matters, but all of this code is in a module, not in the workbook portion. I was having problems before where some stuff wasn't being found unless it was in a module. Not sure why.

 

[Norie]

What exactly is being reset?

Have you stepped through the code line but line?

Might need to do that a few times.

Can you post the rest of the code?

 

[Andrew Poulsom]

This:

wb = ThisWorkbook

should be:

Set wb = ThisWorkbook

 

[AirforceMook]

Dim highlight
Dim wb as Workbook
-------------------
 
Sub OneSecond()
 
     If (IsNull(wb)) then
          set wb = ThisWorkbook
     End If
 
     Application.OnTime now() + TimeSerial(0,0,1), "OneSecond", schedule:=True
     process
End Sub
 
Sub Process
 
     Application.ScreenUpdating = False
 
     Dim sel as Range
     Set sel = selection
 
     Dim difference,time
     highlight = "GREY"
     For i = 6 to 20
 
          If (Len(wb.Sheets("Status").Cells(i,4).Value) = 0 then)

That's exactly where the problem lies. That WB right there. If I remove "wb" then it just occurs at the next instance of it.

UPDATE: It IS set to use "SET wb", I typo'd up above.

 

[Norie]

Why are you using IsNull?

If you want to check if a variable is set use Is Nothing:

If wb Is Nothing Then
    Set wb = ThisWorkbook
End If

 

[AlphaFrog]

wb will never be Null. The Set wb = ThisWorkbook line is not executed.

Try this...

     If wb Is Nothing Then
          Set wb = ThisWorkbook
     End If

Also, ThisWorkbook is the workbook that contains the macro code. You wouldn't need to set a variable as ThisWorkbook as you could always reference ThisWorkbook.