On error resume next stopped working

Question

4.56/5 (2 votes)

See more:

In my current role I inherited (I must have been evil in a previous life) some MS Access VBA which has been working for years.

Today it randomly started throwing errors. I tracked it down to a piece of code that uses the old on-error-resume-next trick that VB6 and VBA programmers had to use in the absence of decent error handling.

The minimum code that I can recreate the problem with is...

VB

On Error Resume Next
ws.Range("A1:K18").Replace "", "0"
On Error GoTo 0

Where ws is a worksheet in an Excel workbook (that was added in the Access VBA) and the range is filled with data (no empty cells) (As, if there are empty cells then Replace does not throw up the "I can't find anything to replace" message that I'm trying to avoid)

I've tried all the usual stuff (see below) to no avail. Interesting, MS Excel does not seem to be affected.

The way around this specific problem is obviously to do a Find first and only issue the Replace command if it is necessary. However, I'm conscious that there are hundreds of EUP solutions out in the business and I would like to find a generic fix if possible.

This is Windows 10, Office 365 ProPlus with Access Version 1803 (Build 9126.2351) i.e. VBA Retail 7.1, No Office updates have been applied since it last worked.

What I have tried:

- Checking the IDE,Tools, Options, General, Error Trapping hadn't been changed to "Break on All Errors"
- Tried all combinations of that setting
- Compact and Repaired the database
- Fully decompiled the database in question and recompiled
- applied a gpupdate /force and hard reboot

Posted 6-Feb-19 6:16am

CHill60

Updated 24-Apr-19 9:14am

Add a Solution

Comments

Maciej Los 6-Feb-19 14:23pm

Caroline, years ago i've been in the same position. The reason was very strange, because something happend with modules (i've found unidentified sign/character).
How to walkaroud this? Export and remove all modules (user forms too), then compact and repair database. Inspect files. Finally, import modules and again compact and repair. Let me know if it helps.

CHill60 8-Feb-19 4:31am

Well 47 modules later .. I'm afraid it didn't work :-(
I don't have administrator rights on my machine so couldn't run sfc but the same issue can be reproduced on various machines. Ironically if I just put the absolute bare minimum of code into a new database it doesn't reproduce the error!
I'm fairly confident now that all the other stuff isn't going to suddenly fall apart so I've just released the "see if it's there before attempting to replace" fix.
Strange though.
Thanks for trying
C

Maciej Los 8-Feb-19 5:00am

I've got another idea. Please, check if there's a situation, which has been described by MSDN: "If the calling procedure has an enabled error handler, it is activated to handle the error."
On Error statement (VBA) | Microsoft Docs[^]

CHill60 8-Feb-19 8:21am

So I took out all of the error handling except the bit around my tiny snippet - still didn't work I'm afraid.

Maciej Los 8-Feb-19 8:50am

Well, you've got a hard nut to crack...

I'd call service staff to provide procedure with:
- file system checking,
- HDD area checking and repairing
- system cleaning (remove unnecesarry restore points, )
- etc.
This should help!

If it'll NOT help, there'll be last thing to do:
Create new database. Insert new module and paste your short code snippet. Import all tables and queries. Then import modules (UserForms), but not all in the time. Import one-by-one and after each import, compile code, compact and repair database and finally check if your code snippet is working. There must be something what causes such of strange behaviour.

I'm keeping my fingers for you, Caroline. I hope it helps t resolve your issue.

Cheers
Maciej

CHill60 8-Feb-19 9:12am

Cheers mate. I've taken the pragmatic approach of changing the way the code works to

        If Not r.Find(What:="", After:=ws.Range("A1"), LookIn:=xlValues) Is Nothing Then
            r.Replace "", "0"
        End If

It takes slightly longer but at least it works :-)

Maciej Los 8-Feb-19 9:34am

Wait a moment...
Are you replacing empty string in the cells with "zeros" before import/export?
Have you tried to grab/dump data from Excel via ADODB.Recordset:

sSQL = "SELECT IIF(Field1 IS NULL, "0", Field1) AS Field1, ..." & vbCrLf & _
"FROM [Sheet1]"
Set oRst = New ADODB.Recordset()
oRst.Open(sSql, ...)

'further
ws.Range("A2").CopyFromRecordset(oRst)

CHill60 8-Feb-19 11:16am

To be honest the whole thing is going to be thrown away soon. It's a right mess. It's being replaced with a sql server database :cheering: :-)

Maciej Los 19-Apr-19 4:44am

Caroline, seems you are not interested in finding solution....
I'd suggest to delete this question.

CHill60 23-Apr-19 6:49am

Hi Maciej. I'm still interested in a solution … turns out that the "soon" in about to be thrown away is one of those relative terms. "Not soon enough" is my current feeling! Looks like it will be around for longer than I though

Maciej Los 24-Apr-19 8:11am

Sadly, i must admit that i can't reproduce your issue. Can you share your file?

CHill60 24-Apr-19 8:30am

I'm afraid I can't as it contains confidential information and is also very large.
Do you want to post a solution that summarises all the suggestions you came up with (in case someone else has similar problems) - I know I still have the issue but all of the stuff you came up with was valid so I'll still accept the solution

Maciej Los 24-Apr-19 8:36am

Uhm...
I didn't looked up for posting solution. I've been interested much to resolve your issue.
If you think that my ideas to resolve issue are good, i can post them as a solution.

CHill60 24-Apr-19 8:43am

I think the ideas are very good. In my research I've come across them all … just not all in the same place!

Maciej Los 24-Apr-19 15:17pm

I'd been encouraged ;)
Done!

Maciej Los 6-Feb-19 14:30pm

And one more note: please, check if OS is OK. Run [cmd] as administrator and type sfc /scannnow

CHill60 6-Feb-19 16:07pm

Thanks Maciej. I'll give these a go tomorrow

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2019-04-24T09:14:00

As we agreed with Caroline in the comments to the question, i would like to share my ways i've resolved such of issues...

Method #1 - in case when unidentified signs/characters have been found in one or more modules:

export and remove all modules (UserForms too)
then compact and repair database
inspect files and edit with notepad - if necessary
import modules and compact and repair database again

Method #2 - in case when method #1 won't help

create new database
import tables and queries, then import modules (even UserForms), but not all in the time!
Note: import one-by-one to detect which one is broken... And after each import, compile code, compact and repair database

Method #3 - in case when method #2 won't help:

check file system and file system metadata - using chkdsk C: /f /x command[^]
repair file system integrity - using sfc /scannnow command[^]
clean system and remove unnecesarry restore points, see:

Method #4 - in case when none of above methods help:

repair or reinstall MS Office

Good luck!