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...
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