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.

 

[Norie]

Monir

Why don't you tell us what you actually want to do?

 

[monirg]

Norie;

1)

Monir
Why don't you tell us what you actually want to do?

I will.
It's a valid question, and I've no doubt by doing so you and other experts in the Forum would be kind enough to help in identifying the conflict causing the error and come up with a brilliant solution(s).
Needless to say, remote debugging of a code is not always easy!!

2) My reasons (or excuses!!) for not providing "too much" overall details at this time are:
a. providing a detailed written description of what I'm trying to do and how it is implemented in VBA would not only be boring for most of you, since it would include some tech aspects of an aero phenomenon that some of you might not be fully familiar with, but also and more importantly it would be a distraction and really confusing to the readers;
b. the description has to be accurate and precise and would take considerable time to compile, otherwise it would add to the confusion;
c. you, as an expert in the field, most likely would modify the entire procedure/code and provide a robust (almost bug-free!) efficient version, which no doubt would work perfectly and as desired, but would look gibberish to me and I'd end up using the procedure as a blackbox;
d. by posting a specific question (in the OP), I thought (wrongly as it appears now) that all I needed help on was to solve the run-time error '1004' in Sub UpdateNow() {OP, WorkShhets statement highlighted by VBA}.

3) I'm convinced now based on your collective helpful replies that the culprit here is not necessarily Sub UpdateNow(), despite its run-time error, but rather one of the w/s events which (among other things) blocks/unblocks certain cells and protects/unprotects the sheet with starting blocked/unblocked cells as the case maybe; depending on the desired procedural scenario for a particular run.

4) That said, I'm currently re-examining the events codes, which would take me some time to complete following the review of my tech notes.
Failing to identify the actual cause of the error, I'll provide additional info and post the w/s events procedures for your evaluation.

Kind regards.

 

[Norie]

Monir

I think it might be preferable to have more information rather than less

I don't think we expect you to write and post a full treatise on your 'aero' phenomenon, it might not be relevant, but then again it could be.

Perhaps just a broad explanation of it and how Excel is involved may help.

So far the suggestions made on what you've posted and/or what can be implied from that.

So we could be making 'shot in the dark' suggestions.

Anyway you seem to have come to some sort of conclusion - the problem might be with some event code you are also using.

That wouldn't surprise me at all, setting up an OnTime schedule that could trigger an event just sounds as though it could be problematic.

If somebody was to find a solution that involves a wee bit of a rewrite then I don't see why you couldn't politely ask for an explanation of it.

 

[xenou]

If we can back up a moment to post # 1:

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

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.

You say "it" produces an error. Is it stopping on a single line? Which line?


Looking again, another thought is this line:

 If [COLOR="Blue"]Range("j15")[/COLOR] = "" Then Exit Sub

The Range is unqualified. Should it have a worksheet qualifier:

If [COLOR="Blue"]Worksheets("SheetX").Range("J15")[/COLOR] = "" Then Exit Sub

Another thought is you are scheduling update now pretty tight if there is a lot of other code being generated. Computers are fast as in milliseconds, but you are scheduling this macro every 1000 of them ... that could be choking (not sure, as I've never tried this kind of thing myself)

 

[monirg]

Norie;

Points well taken! Will follow your suggestions.

Regards

 

[monirg]

xenou;

1)

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

xenou wrote (post # 24):
You say "it" produces an error. Is it stopping on a single line? Which line?

The procedure stops with the above run-time error and the WorkSheets statement is highlighted.

2)

xenou wrote:
If Range("j15") = "" Then Exit Sub
The Range is unqualified. Should it have a worksheet qualifier:
If Worksheets("SheetX").Range("J15") = "" Then Exit Sub

I don't think so! You don't need a ref to the active w/s.
Further, I had tried it earlier with no luck!

3)

xenou wrote:
Another thought is you are scheduling update now pretty tight if there is a lot of other code being generated. Computers are fast as in milliseconds, but you are scheduling this macro every 1000 of them ... that could be choking (not sure, as I've never tried this kind of thing myself)

May try later a smaller time interval, but for now 1 sec appears to be adequate for the intended purpose.

4) I'm reasonably convinced as we speak that the Worksheets run-time error in Sub UpdateNow() {Item 1 above} is a bit misleading!
Based on your collective suggestions and my debugging so far, the conflict has to be in one of the w/s events codes, and somehow the error manifests itself this way. It might be even the result of a "situation" error rather than a "procedure" error.
I'm not sure if the above makes any sense!!

Regards.

 

[xenou]

Based on your collective suggestions and my debugging so far, the conflict has to be in one of the w/s events codes, and somehow the error manifests itself this way. It might be even the result of a "situation" error rather than a "procedure" error.
I'm not sure if the above makes any sense!!

I'm afraid it doesn't make sense. You are suggesting that the error is not on the line that errors. Have you tried unprotecting the sheet before you make a change in cell F1 to see if that is the problem? It should be as simple as:

Worksheets("Burrill-et-al").UnProtect Password:= "SomePassword"
Worksheets("Burrill-et-al").Range("F1").Value = Format(Now, "hh:mm:ss")

I am surprised that you have not yet tried this as it is clearly a possible cause of the problem. Note: I am sympathetic to your desire to review the whole chain of change events, but the immediate issue with this thread is just the error in this one procedure. That the cell is locked is at least one likely cause. Hence, I think a good precaution (or a good try) is to unprotect the sheet before making such a change - if it doesn't work it doesn't work. Shouldn't we at least see if it does before ruling it out? Dunno - it's clearly complicated what you're doing. If that doesn't help I'm out of ideas then.