Excel crash when typing open parenthesis

Question

Here's one I don't understand.

Given this class module (stripped down to the bare minimum necessary to reproduce the crash):

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "TestCrashClass"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Public Function Init() As TestCrashClass
Attribute Init.VB_UserMemId = 0
    Dim tcc As New TestCrashClass
    Set Init = tcc
End Function

Public Property Get Data() As String
    Data = "test data"
End Property

Can anyone tell me why Excel totally craps out when I type in this code:

Sub MakeExcelCrash()
    With TestCrashClass(

At this point, I this lovely message:

Even if I type in a full procedure without the offending parentheses and then try to add them later, I get the same crash.

The only way I can get Excel not to crash is to copy/paste a set of () from somewhere else to this line of code.

 Sub MakeExcelCrash()
     With TestCrashClass()
         Debug.Print .Data
     End With
 End Sub

If the Init() method has a parameter—even an optional one—it won't crash when the opening paren is typed.

I'm more curious about why this happens than ways around it; it doesn't actually come up that often in my code and when it does I can fix it with a change in approach, but I'm really frustrated that I don't know what's causing these crashes. So maybe someone who knows more about the inner working of VBA can explain it to me?

I'd guess that making a method which creates an instance of your class the default method is creating an infinite loop. Possibly there is some evaluation of the class's code going on when you type that line... What is the purpose of that method ? — Tim Williams, Feb 06 '17 at 19:51
I think since you made `Init` the default member, the VBE will try to give you an Intellisense drop-down based off of that - as soon as it tries to do that it enters the infinite loop (because the return value from `Init` is yet another instance of the same class, also with that same default member and so on and so on...) and Excel crashes. — Tim Williams, Feb 06 '17 at 20:03
@TimWilliams Then why wouldn't it do the same if the method in question has a parameter? I have plenty of such methods that return an instance of the same class and they all function without incident... as long as the method takes a parameter. — Patrick Wynne, Feb 06 '17 at 20:14
I have no idea - maybe others here who are more familiar with the inner workings of the VBE will chime in... — Tim Williams, Feb 06 '17 at 20:24
Given the lines are redundant anyway, I assume the same problem occurs if you omit to `Dim tcc As New TestCrashClass` and change `Set Init = tcc` to `Set Init = tcc`? — ThunderFrame, Feb 06 '17 at 20:35
Is the intent to get a singleton `TestCrashClass`, or to have `Init` run when you use the default instance? — Comintern, Feb 06 '17 at 20:57
Make the default member late-bound, and *that* problem goes away. `Public Function Init() As Object` — ThunderFrame, Feb 06 '17 at 21:14

score 13 · Accepted Answer · answered Feb 06 '17 at 20:52

You don't even need the With block. Any attempt to type ( after the class name takes Excel down.

The problem is that you have the VB_PredeclaredId set to true and the default member is trying to return itself. When you attach a debugger to the dying Excel instance, you can see that the underlying issue is a stack overflow:

Unhandled exception at 0x0F06EC84 (VBE7.DLL) in EXCEL.EXE: 0xC00000FD: Stack overflow (parameters: 0x00000001, 0x00212FFC).

When you type With TestCrashClass(, what happens is that VBA starts looking for an indexer on the default property, because Init() doesn't have any properties. For example, consider a Collection. You can use the default property's (Item) indexer like this:

Dim x As Collection
Set x = New Collection
x.Add 42
Debug.Print x(1)   '<--indexed access via default member.

This is exactly equivalent to Debug.Print x.Items(1). This is where you start running into problems. Init() doesn't have parameters, so VBA starts drilling down through the default members to find the first one that has an indexer so IntelliSense can display the parameter list. It starts doing this:

x.[default].[default].[default].[default].[default]...

In your case, it's creating an infinite loop because [default] returns x. The same thing happens in the Collection code above (except it finds one):

Throw in the fact that you have a default instance, and the end result is something like this:

Private Sub Class_Initialize()
    Class_Initialize
End Sub

OK, that makes sense. Thanks for the details. – Patrick Wynne Feb 08 '17 at 18:19 — Patrick Wynne, Feb 08 '17 at 18:19

score 11 · Answer 2 · answered Feb 06 '17 at 20:50

As @TimWilliams points out, having a default member that returns an instance of the same class (or a class loop eg. ParentClass.ChildClass.ParentClass.ChildClass... where ParentClass and ChildClass both have default members), and when used in certain syntax cases, such as a With block, will cause VBE to try and resolve the default member.

The first parenthesis makes VBE assume there must be a method, indexed get or array index that will take an argument, so it sets off to resolve the ultimate target member.

So the incomplete line, with a cursor located after the parenthesis:

With TestCrashClass(

Is effectively the same as:

With TestCrashClass.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init.Init '....You're inquisitive scrolling this far over, but you get the point.

At some point, your system or VBE runs out of resources and exits with the grace and poise of a thermonuclear group-hug.

+1 for improvising with a copy/pasta of a parentheses pair.

I have to admit I did scroll all the way over to the right, just to see how far you'd take it. Thanks! — Patrick Wynne, Feb 08 '17 at 18:21

score -4 · Answer 3 · answered Feb 06 '17 at 19:52

-4

Sounds like some sort of corruption. I've had Excel behave irrationally like this before, normally in large projects, and the only way to get around it is to drag all of your classes etc into a new project.

I suspect it happens because Excel doesn't truly delete classes, modules, worksheets etc that have been removed. You can tell this because of the file size.

There is no Compact and Repair functionality, as in Access, as far as i'm aware

answered Feb 06 '17 at 19:52

Jiminy Cricket

1,377
2
15
24

Same thing happens with a completely new project as well. – Patrick Wynne Feb 06 '17 at 20:21

Excel crash when typing open parenthesis

3 Answers3