2

I work with Excel spreadsheets that use the vba Erl() function for error handling. The vba code might look something like this:

   Sub doSomething()
      Dim v as variant
      Dim v1 as Variant

      On Error goto err_Handler

10    v = "Test"

20    v1 = 1/0        ' Generate error

30    Call doMoreStuff()

      Exit Sub

err_Handler:

      if Erl = 20 then 
          Resume Next     ' Ignore this error

      Else
          Msgbox "Looks Like there is a REAL error somewhere.  Desc = " & Err.Description
      End If

End Sub

I am not making a judgement about using Erl in this way. Personally, I never do, but we have many spreadsheets that have this.

However, in O365 testing on 64 bit Excel it seems like this functionality does not work at all. In the above code, the program does not read the line number correctly and so the line Erl = 20 returns False when it should be True and hence the program calls the Msgbox when it should ignore the error.

Has anyone else noticed this? There are apparently dozens of spreadsheets that have this potential issue. Creating a script that would somehow change the code to something else would be a huge undertaking.

Has anyone else had to deal with this problem and can you outline how you addressed this problem?

Thanks very much

C Tauss
  • 88
  • 4
  • `the line Erl = 20 returns False` - then what does Erl return? – GSerg Jul 16 '20 at 19:48
  • It is not consistent. Most of the time it returns 10 (the previous line number) but sometimes it returns 0 (as if the 20 is not there at all). – C Tauss Jul 16 '20 at 20:04
  • I suspect that, since Line numbers in VBA have essentially been deprecated, and since Line Numbers are limited to Integer resolution (32,767) and VBA code can have 65,535 lines, that the function was never implemented for 64-bit Office, hence unreliable results. – Ron Rosenfeld Jul 16 '20 at 20:17
  • You're may be right, but it seems very bad to allow code to be valid and then upgrade and _poof_ it doesn't work anymore. Surprise! I suspect this was just missed and will probably be fixed at some point, but that doesn't help us much now. – C Tauss Jul 16 '20 at 22:26
  • I don't know if it will be fixed. `ERL` is not documented at all for the current version of VBA. You may be better served by eliminating it in the code, especially if you're going to be using 64-bit Office. – Ron Rosenfeld Jul 16 '20 at 23:38
  • 1
    for your example, Erl is working as it should for me. my build is "Microsoft 365 MSO (16.0.13001.20254) 64-bit" – Joe Bourne Jul 17 '20 at 00:05
  • Interesting. Maybe he problem is with spreadsheets that start out as Excel 2013 and then are moved to O365. I've tried forcing a recompile, but that doesn't seem to do it. I wonder if creating a macro to copy the code into new modules in a second workbook stripped of the code might work. Thanks all for your insights. – C Tauss Jul 17 '20 at 02:34
  • It doesn't work in my Win64/Excel32 VBA 7.1.1087 system too. Erl is always 0. – 6diegodiego9 Jan 19 '22 at 16:35

1 Answers1

0

Has anyone else noticed this?

Yes, I can confirm that this issue also exists in the 64 bit version of MS Access, when compiling the database to accde or mde format. Here are my repro instructions (tested with Version 2112, 16.0.14729.20260 C2R).

  1. Create a new empty database with a single module.

  2. Fill the module with the following code:

    Sub Test()
    Dim a As Long
    Dim error_lines As String

    On Error GoTo error_handler

10: a = 1 / 0
20: a = 1 / 0
30: a = 1 / 0
40: a = 1 / 0
50: a = 1 / 0

    Debug.Print error_lines
    Exit Sub

error_handler:
    error_lines = error_lines & Erl() & ","
    Resume Next

End Sub

  3. Execute the code and notice that it prints 10,20,30,40,50, in the immediate window. So far, so good.

  4. Create a compiled accde file with File/Save As...

  5. Open the accde file, hit Ctrl-G to enter the VBA immediate window and execute Test.

  6. Note that the output is now 10,20,0,0,0,

Has anyone else had to deal with this problem and can you outline how you addressed this problem?

The obvious workaround is to use your own error line variable, i.e., replace

10: FrobnicateFoo()
20: ...

error_handler:
    If Erl() = 10 ...

with

    Dim line_nr As Long

    line_nr = 10
    FrobnicateFoo()
    line_nr = 20
    ...

error_handler:
    If line_nr = 10 ...

However, this would be a good opportunity to replace those meaningless line numbers with well-named variables, e.g.

    Dim frobnicating_foo As Boolean

    frobnicating_foo = True
    FrobnicateFoo()
    frobnicating_foo = False
    ...

error_handler:
    If frobnicating_foo Then ...
Heinzi
  • 167,459
  • 57
  • 363
  • 519