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.

 

[lynnnow]

Try these things first:

1. Make sure you have the name of the sheet spelled correctly.
2. Make sure you have no missing references.

 

[monirg]

The references are correct, and the w/s name is correct (copy/paste).
Not only it is the ONLY w/s in the w/b, but also if the sheet's name was misspelled, I'd get the run-time error: "Subscript is out of range" (or something to that effect) instead of error: '1004'.

Regards.

 

[shg]

it is the ONLY w/s in the w/b

If the active sheet were a chart sheet rather than a worksheet, you'd get a 1004 error on this line:

If Range("j15") = "" Then Exit Sub

 

[monirg]

There are NO charts in the workbook, and the ONLY w/s is the active sheet!

Regards.

 

[JackDanIce]

This worked fine for me when J15 is empty or not empty. I'm using Excel 2007:

Sub UpdateNow()
    If IsEmpty(Range("J15")) Then Exit Sub
    Sheets(1).Range("F1").Value = Format(Now, "hh:mm:ss")
    Application.OnTime Now + TimeValue("00:00:01"), "UpdateNow"
End Sub

If your sheet is the only sheet in the book, you don't need to define it in the code, this should also work:

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

 

[monirg]

JackDanIce;

IsEmpty w & w/o w/s reference made no difference (XL 2003). Same '1004' error unless I use "On Error Resume Next".

If it is not too much trouble, could you please try your code in XL 2007 with protected w/s and protected w/b and let me know if you got a run-time error ??
(cells F1 and J15 are locked and hidden)

Thank you.

 

[JackDanIce]

Why didn't you say anything about protected before? Why don't you unprotect your workbook and worksheet before running the test on the blank cell and then reprotecting it etc?

 

[RoryA]

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

 

[JackDanIce]

My sentiments exactly Rory!