VBA Help: "On Error Resume Next" ??

[monirg]

Hello;

1) Please examine the following simple macro in a standard module:

Sub UpdateNow()
    If Range("j15") = "" Then Exit Sub
    Worksheets("Burrill-et-al").Range("F1").Value = Format(Now, "hh:mm:ss")  'use any cell on the active w/s
    Application.OnTime Now + TimeValue("00:00:01"), "UpdateNow"
 End Sub

It produces the error:
....."run-time error: '1004'
.....Application-defined or object-defined error"
with reference to the Worksheets statement.

2) By inserting "On Error Resume Next" before the Worksheets statement, there're no errors, and the macro and the entire algorithm appears to be working fine:

Sub UpdateNow()
    On Error Resume Next
    If Range("j15") = "" Then Exit Sub
    Worksheets("Burrill-et-al").Range("F1").Value = Format(Now, "hh:mm:ss")  'use any cell on the active w/s
    Application.OnTime Now + TimeValue("00:00:01"), "UpdateNow"
End Sub

3) My concern is that the "On Error Resume Next" (which I don't often use) maybe telling VBA to ignore errors I didn't mean to ignore and I shouldn't, since I couldn't figure out why the Worksheets statement produced the run-time error in the first place!

Can someone please identify the cause of the problem, and whether it is "generally" safe to use "On Error Resume Next" when the cause of the error is not known (to me) ??

Thank you kindly.

 

[monirg]

Sorry!
Please ignore my 2nd para (protected) of my previous reply (#7). It's irrelevant to this thread or to my OP question.
My apologies!

Regards.

 

[JackDanIce]

Then afraid I can't see what's wrong the code as it works fine on my spreadsheet which, as started, is Excel 2007

 

[monirg]

Rory;

If F1 is locked, how do you expect to change it?

Cell F1 has the NOW() function, and J15 has a formula that returns "" if the condition is false.

Regards.

 

[xenou]

I get a run-time error 1004 trying to change the value in a locked cell.

Sub UpdateNow()
    If Range("j15") = "" Then Exit Sub
    Worksheets("Sheet1").Range("F1").Value = Format(Now, "hh:mm:ss")  
    Application.OnTime Now + TimeValue("00:00:01"), "UpdateNow"
 End Sub

would it be possible to unlock this cell? When you say it works with on error resume next do you mean it really works or just doesn't error? It would run (I think) but there would be nothing happening to the cell you want to update with the time ... I suppose if you prefer you could continuously unlock and lock it again on each run.

 

[Norie]

Monir

How is protection irrelevant when it appears to be what's causing the problem?

As well as the protection have you considered the OnTime could be causing some problems?

Anyway, this worked for me - well it didn't error anyway.

Option Explicit
Sub UpdateNow()
Dim ws As Worksheet
    Set ws = Worksheets("Burrill-et-al")
    
    If ws.Range("j15") <> "" Then
    
        ws.Unprotect
        ws.Range("F1").Value = Format(Now, "hh:mm:ss")  'use any cell on the active w/s
        ws.Protect
        
        Application.OnTime Now + TimeValue("00:00:01"), "UpdateNow"
        
    Else
         Application.OnTime Now + TimeValue("00:00:01"), "UpdateNow", False
         
    End If
         
 End Sub

 

[RoryA]

Rory;

Cell F1 has the NOW() function, and J15 has a formula that returns "" if the condition is false.

Regards.

If F1 has the Now function in it, why are you trying to overwrite it with the value of Now (which you can't do if it's locked anyway)?

 

[Norie]

rory

Good point, I missed that J1 had something in it.

I just thought the OP wanted some sort of timer/clock thing, that's what the code seemed to do when I tried it.

 

[monirg]

xenou;
1)

I get a run-time error 1004 trying to change the value in a locked cell.

But locking cells has no effect when the w/s is unprotected. Correct ??
2)

When you say it works with On Error Resume Next do you mean it really works or just doesn't error?

It really works! No errors, and the macro and the entire algorithm work fine when I use On Error Resume Next.


Norie:
3)

How is protection irrelevant when it appears to be what's causing the problem?

I apologised earlier for the mix-up (reply # 11). The w/s is NOT (intentionally) protected. (plse see Item 6 below)
4) Your code (reply # 15) works fine. But the screen now continuously "flickers" because (I reckon) the use of OnTime in both branches of IF. Also the w/s ends up being protected. (plse see Item 6 below)

5)

If F1 has the Now function in it, why are you trying to overwrite it with the value of Now (which you can't do if it's locked anyway)?

The function/value NOW() in F1 is from the macro Sub UpdateNow(), and not manually entered. Furthermore, I think the value in a locked cell can be overwritten if the sheet is not protected.

--------------------------

6) Based on your collective helpful replies, I'm re-evaluating the problem from a slightly different angle.
There're couple of events on the w/s, and I'm re-examining them to see if somehow there's a direct or indirect conflict. Depending on a desired computational scenario (out of possible 22), one w/s event temporarily "locked" certain cells to avoid unintentionally changing their values for that particular scenario, as well as temporarily "protect" the w/s so that the initially "locked" cells (F1 not included) remain "locked" at the start of the next desired scenario. Some of those cells are in named ranges and are all over the w/s, and I suspect there might be a conflict there somewhere leading indirectly to the Worksheets statement error: '1004' in Sub UpdateNow() reported in the OP.
The above description may sound a bit confusing, and that's because actually it is!

7) Keep in mind that by inserting On Error Resume Next in Sub UpdateNow() the run-time error disappears, and all the scenarios I've tested so far work perfectly. Obviously this is not a reliable solution since I haven't yet identified the cause of the error.

Please allow me some time and I'll report back.

Thank you.

 

[RoryA]

The On Error Resume Next is not a fix, it just masks errors. Is it possible that the error is actually coming from a Change event on that sheet (since you are changing cells)?

 

[monirg]

Rory;

The On Error Resume Next is not a fix, it just masks errors.

I agree, and I'm disperately trying to identify the cause of the error!

Is it possible that the error is actually coming from a Change event on that sheet (since you are changing cells)?

It is very possible, and I'm re-examining all the w/s events.

Regards.