This VBA code crashes my Excel

[6diegodiego9]

Just write this code in a new module and launch a debug - compile:

Option Explicit

    Private Type seq
        Count As Long
        'rng As Range
    End Type
Private Type xlTable
    seq As seq
    rng As Range
End Type

Sub REALE()
    Dim dbl As Double: dbl = Sheet1.Range("ABC").Value2
   
    Dim xlTable As xlTable
    Set xlTable.seq.rng = xlTable.rng.Offset(0, 4)
End Sub

It crashes my Excel on two different PCs.

I know that there are atleast three errors in this code: the named range "ABC" doesn't exist, xlTable.seq.rng doesn't exist and xlTable.rng is not defined.

Anyway, what's the cause of the crash?

 

[GWteB]

You have a variable declaration and an assignment of that same variable as a one-liner at the start of your procedure. The non-existence of the rng variable results in a compile error. If an attempt is made to run the code despite that, the run-time error and the compile-time error fight for precedence. Neither wins, Excel crashes.

 

[Domenic]

xlTable.seq.rng

In the above, rng is not a member of seq. You probably wanted to do xlTable.rng instead, but as @GWteB has already mentioned, you can't assign it a non-existing variable.

 

[6diegodiego9]

Wait wait, I never tried to "run the code", I just try to "debug - compile" it to spot the errors and on the (second) compile Excel crashes.

The first Compile correctly highlights an error:

while the second consecutive Compile instead than highlighting the (same) error, just stay hang for some second with this screen ("Compiling...") then Excel crashes:

Is it a compiler bug? what triggers it?

 

[offthelip]

I am running Excel 2010 and I get exactly the same results as you, excel crashes on the second compile!!

 

[6diegodiego9]

Excel for Office 365 MSO (16.0.11328.20492) 32-bit here

 

[GWteB]

@6diegodiego9 & @offthelip you're both right. I've looked into it a little further out of interest and have to revise my previous comment in part.

The crash is not caused by a (possible) run-time error and a compile error at the same time. It's just a (or another) bug in the compiler.
I don't pretend to be exhaustive, but Excel crashes anyway when the conditions mentioned below are met:

1. Option Explicit is used - AND
2. the very first VBA method/function within the procedure is on the same line of code as which a variable is declared - AND
3. an attempt is made to assign a Range object to an undeclared variable using a pre-declared variable (vartype Range) using one of the following properties: Cells, Cells(), Offset(), Range(), Resize().

Translated to VBA code:

Option Explicit                     ' #1

Public SomeRange As Range           ' #3.1

Sub ExcelCrash()
    Dim a
    Dim b: MsgBox "Bye"             ' #2
    '
    '
    Set r = SomeRange.Cells(1, 1)   ' #3.2
    '
End Sub

 

[offthelip]

I tried deleting the option explicit but Excel still crashed with my 2010. However I agree this is another bug in the compiler and it obviously it isn't much of a problem because the code also obviously wrong. Compiler bugs are much more of problem when they are apparent on legal code.

 

[GWteB]

I second that.