Erl() function not working in 64 bit O365 Excel VBA

Question

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

Answer 1

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