Bug when using interfaces on larger projects

Question

For larger VBA projects (40,000+ lines of code) I cannot properly use interfaces because the Application (I mainly use Excel) will crash quite often. Apparently, this is because the code cannot remain compiled (from my understanding VBA code gets compiled to P-code which is later interpreted). I mainly get this behavior when the VBA Project is password protected.

The Debug/Compile menu is almost never "greyed out" when I open the hosting document:

This article describes the same behavior. Go to section 2.3

For example:
IClass interface:

Option Explicit

Public Property Get SomeProperty() As Double
End Property

Class1:

Option Explicit

Implements IClass

Private Property Get IClass_SomeProperty() As Double
    IClass_SomeProperty = 0
End Property

Code in standard module:

Option Explicit

Sub TestInterface()
    Dim obj As IClass
    
    Set obj = New Class1
    Debug.Print obj.SomeProperty 'Crashes here on large projects only
End Sub

As expected the Debug.Print obj.SomeProperty line works fine and prints 0 in the Immediate window if the project is small. However, on a large project the application crashes when this line is called. The IClass_SomeProperty is not reached (logging to a file clarifies this).

As in the above-mentioned article, there are ways to temporarily avoid the issue:

1. Recompiling solves the issue (not always) but the crash could occur on the very next time the document is opened, or it might keep things going for a few days (presuming file is opened every day)
2. When option 1 is not working, deactivating all Implements statements in the entire project using the Find/Replace window, then compiling and then reactivating the statements back. Again, this can work for a few hours or a few days but inevitably a crash will occur in the near future

Since the VBA Project is password protected and because many people are using these VBA-capable documents (Workbooks in my case), applying the temporary fixes does not help at all.

The only way that I've found to avoid the crashes and still get the benefits of the interfaces is to use conditional compilation. Basically, I use interfaces only for development and then switch to late-binding for production. Obviously, this comes with a lot of headaches.

The example above becomes:
Class1:

Option Explicit

#Const USE_INTERFACES = True

#If USE_INTERFACES Then
Implements IClass
#End If

Private Property Get IClass_SomeProperty() As Double
    IClass_SomeProperty = Me.SomeProperty
End Property

Public Property Get SomeProperty() As Double
    SomeProperty = 0
End Property

Notice that all interface methods must be duplicated and made public so that late-binding is an option.

Code in standard module:

Option Explicit

#Const USE_INTERFACES = True

Sub TestInterface()
    #If USE_INTERFACES Then
        Dim obj As IClass
    #Else
        Dim obj As Object
    #End If
    
    Set obj = New Class1
    Debug.Print obj.SomeProperty
End Sub

When developing new features, I follow these steps:

1. turn #Const USE_INTERFACES = True for all occurrences using Find/Replace
2. add new features
3. turn #Const USE_INTERFACES = False so that code runs on late-binding and doesn't crash

---

I've been experiencing this bug for at least 3 years. I would obviously avoid the conditional compilation workaround if I could.

Is there a way to keep the VBA Project compiled (let's say running a procedure when opening the document) without access to the VBA project object model? It is not an option for me to have the Trust access to the VBA project object model turned on.

I appreciate this bug is not easy to re-create unless you happen to have a large VBA project at hand.

EDIT 1

A nice point raised by @PEH in the comments: this issue is applicable for both xlsm and xlsb files (Excel).

Speiss

I had a similar issue with Excel when my project .xlam started to exceed 1,5 Mb. It would crash during compilation or if it compiled it would throw errors where it worked before and then crash during debugging (classes sometimes would also get corrupted, but other files formats never solved the issue). I did not realize it has to do with interfaces. My solution was to pull the project apart into multiple files. Currently my core project is around 14.000 lines and it relies on up to 6 other files that have another 12.000 lines together and it works. I figured it is just a VBA limitation. – Area

@VictorK I figured out this bug when I was beyond 40,000 lines but I had crashes before. I don't have time to test this but I belive my issues started around 25-30k lines. – Speiss

@CristianBuse If I were to guess I was at about 20k when I had issues, but maybe it is also affected by amount of classes/forms/controls on forms/reference counts, etc. Now I try to stay under 1mb - I have not had any issues anymore. But splitting project into multiple files might not work for everyone. Fortunately my users only need core functionality and I selectively load other files (with classes implementing interfaces in the core) based on their needs - fortunately Interfaces make it possible. It would be amazing if someone knows of a solution to this problem. – Area

You mentioned the LoC but I'm wondering, how many VBComponents do your VBA project have? The reason I ask about this is because there is a defined limitation of 1,000 modules (including document modules) for an Access VBA projects which may cause this problem. It's not clear from the documentation whether this limitation is Access-only or applies to any VBA projects. However, the 1,000 modules limit is not a hard code; you'd be allowed to add more modules but then you'd get undefined behaviors. Is this the case here? – Deuterogamy

@Deuterogamy I only have 116 componenents (138 if counting the worksheets). I had less than 80 when the issues started. Thanks for the info. Good to know there is a limit – Speiss

I wonder if splitting across several addins would solve this, I don't know if the p-code buffer that seems to be getting too full is causing issues when in the document streams or when loaded into the interpreter memory. I know that makes it less portable but I'm developing a package manager that runs using addins per package and it would be great to know if this limit will impact 40k lines over 10 .xlam files or just 40k in one file – Pacha

Thanks @Pacha ! I would be curious too to figure this out. However, I am not working on that big project anymore and also I already refactored to not use interfaces about a year ago. I could of course use an older version, but I don't think I will have time to look into this for at least a few months given my current circumstances. – Speiss

@CristianBuse glad you are keeping busy I hope in a good way. Also curious how the zero interface approach was working for you. Was there a particular style that worked well under those constraints so it stopped being an annoyance, like less OOP more procedural or data orientated. Or did it put you off large scale VBA? Basically was this workaround you outline feasible in practice or too soul destroying to pursue and took away from the satisfaction of the design aspects of programming? – Pacha

Thanks @Pacha ! Yeah, busy in a good way - second baby (and last :) ). I already had (as mentioned in the question) a conditional compilation constant that was switching to late binding which obviously implied that the classes also had the exact same methods as the interfaces i.e. there was some duplication already. I eventually removed the interfaces and replaced the conditional compilation with some runtime If TypeOf ... and proceeded to use early binding which of course bloated the code. So, no style in particular but rather went for something that took the minimum amount of time. – Speiss

@Pacha So, definitely soul destroying and unsatisfactory. – Speiss

@CristianBuse wow congrats 🎉 (on baby not soul destruction). Thanks for clarifying on the long term findings, sounds less than ideal. Hopefully an approach with addins can be scalable though we'll have to wait and see. Or maybe automatically compile tB DLLs as a build step in the package manager... – Pacha

Answer 1

I don't have a 40K-liner project to test it, but it is known that the VBE debugger's edit-and-continue feature can corrupt the internal storage streams, resulting in "ghost breakpoints".

The VBE itself will corrupt a VBA project, given enough time and enough debugger sessions (and the right amount of pixie dust) - with or without interfaces involved.

I've been coding against interfaces in VBA ever since I've realized that VBA had that capability, and the only problem I've found that was directly caused by Implements statements, was that if you make a document module (e.g. Sheet1, or ThisWorkbook), implement an interface, then you're 100% going to corrupt your project and crash Excel. But with normal user classes? Nope, never had a problem with those.

I would definitely consider edit-and-continue the primary suspect for any stream corruption happening in a VBA project - if the internal ITypeInfo gets corrupted when the type Implements an interface, then that would be because of the bug with edit-and-continue, and the Implements statement "causing it" is really just a symptom.

Project size is also an important factor: 40K LoC is indeed a very large VBA project, and large VBA projects have a tendency to more easily get corrupted too. This can usually be mitigated by regularly removing+exporting all code files and re-importing them back into the project (which forces a "clean rebuild" of the internal storage, I'm guessing) - if your very large project is under source control, then this should be happening regularly.

The VBE does not want you to write OOP. It's actively fighting it: it wants you to cram as much code as possible into as few modules as possible, and since its navigation tooling objectively sucks, you don't get a "find all implementations" command to quickly locate the concrete implementations for your interfaces, so yes the VBE's "go to definition" takes you to an empty method stub - guess what, "go to definition" in Microsoft Visual Studio 2019 does exactly the same (it does give you "go to implementation" too though).

Writing OOP in VBA without Rubberduck to assist with navigation (and everything else) isn't something I would recommend, but then irony has it that Rubberduck doesn't perform very well with very large legacy code bases with lots of late binding (implicit or not).

If project size is related to the problem, then using conditional compilation is actually making things worse, by making the project even larger, and then making Rubberduck fail to "see" pretty much half of the code (late bound member calls can't be resolved, so we lose track of what's being used where), ...which essentially cripples it.

I don't have a solution, only a hunch that edit-and-continue is very likely behind this, since it rewrites chunks of p-code on the fly, and that is already known to be causing problems. If my hunch is correct, then regularly exporting & reimporting all code files should help keep the corruption at bay (and then it makes it easy to put the project under source control, should anything ever become irreparably broken). Avoid code-behind in document modules as much as possible when you do this, and whatever you do NEVER make a worksheet/document module Implements any interface. Rubberduck has tooling to quickly & easily export/import multiple code files at once to/from a given folder.

Geordie

Thanks for all these useful tips. Unfortunately I do export/re-import all modules regularly and I don't use Implements inside document-modules. I also rebuild in a new file from time to time. After a clean re-build, the corruption happens with just regular Excel use without editing the code (the Compile button is not greyed out when this happens). I will need to test for the 64K module limit in the next few days (thanks for that tip in that other channel). – Speiss

In my experience VBA is prone to corruption when: 1) The code uses interfaces, which make references to classes, and is ran from the Macros menu, not from the IDE. Opening the editor and initiating the code from there resolves interface issues for some reason. Try running your code from the ribbon/Macros without opening the IDE. Save after each run and close the file. Reopen and repeat 3 times. On 3rd run it should crash. – Tittivate

2) Circular interfaces are an obvious one, the code crashes immediately, but there is some kind of middle ground: Two different classes A and B implement two interfaces each. Class A implements Interface1 and Interface 2. Class B implements Interface1 and Interface3, ie. they have Interface1 in common. Interface2 makes references to class B. (If Class B implemented Interface2 it would make a direct circular interface, which is not the case here, but something happens indirectly and causes corruption later on...) – Tittivate

Answer 2

I too jumped into using interfaces (years ago) because I thought they were proper COM (and they are) but I too hit problems. So, I stopped using them.

Apart from anything else, if you double click on a method with normal code you jump to the code implementation but with interfaces you jump to an empty method (which is unsatisfactory).

If you really have a class, Foo, that expresses different behaviours worthy enough to break into a separate interface IBar then why not break IBar into an actual separate class Bar and then set an instance of Bar to be a public property of Foo?

That's just a suggestion. I know of no other VBA fix and it is extremely unlikely Microsoft will fix this now.

You could always migrate your code to VisualBasic.Net if you want to preserve you class/interface design.

I'd be delighted if someone actually solves this.

Jambeau

Thanks for your suggestions. Unfortunately I cannot migrate to .Net. Rather than ditching interfaces entirely I still prefer using conditional compilation like mentioned in the question. – Speiss

Answer 3

In my experience, it is the strongly typed arguments in the interfaces that cause the problem in big projects, not the presence of Implements itself.

I think in most cases you could get away with using late binding in interfaces only. To use your trick conditionally you could do something like this:

Interface ISerializable:

#Const USE_LATE_BINDING_IN_INTERFACES = True

#If USE_LATE_BINDING_IN_INTERFACES Then
    Public Sub Save(ByVal oWorksheet As Object)
        '
    End Sub
    
    Public Sub Load(ByVal oWorksheet As Object)
        '
    End Sub
#Else
    Public Sub Save(ByVal oWorksheet As Serializer)
        '
    End Sub
    
    Public Sub Load(ByVal oWorksheet As Serializer)
        '
    End Sub
#End If

Inside a class which implements the interface:

Public Sub Save(ByVal oWorksheet As Serializer)
    'Do something
End Sub

Public Sub Load(ByVal oWorksheet As Serializer)
    'Do something
End Sub

#If USE_LATE_BINDING_IN_INTERFACES Then
    Private Sub ISerializable_Save(ByVal oWorksheet As Object)
        Save oWorksheet
    End Sub
    
    Private Sub ISerializable_Load(ByVal oWorksheet As Object)
        Load oWorksheet
    End Sub
#Else
    Private Sub ISerializable_Save(ByVal oWorksheet As Serializer)
        Save oWorksheet
    End Sub
    
    Private Sub ISerializable_Load(ByVal oWorksheet As Serializer)
        Load oWorksheet
    End Sub
#End If

The public methods will cast from Object to Serializer anyway.
Everywhere else you should be able refer to Serializer class by it's name.
There is something with interfaces which make references to other classes...

Tittivate

What is the purpose and the effect of changing the argument types in the interface? The problem is caused by the presence of the word Implements, not by the type of the argument in an interface's method. – Cupcake

I have no idea, but in my projects it helped. If I keep early binding references in interfaces I can reliably corrupt my files within 3 saves (run code, save file, close, reopen and repeat). When I change to "Object" the project seems to stay healthy... – Tittivate

Ah, so you are saying that the bug is actually caused by strongly typed arguments in interfaces rather than the presence of Implements? – Cupcake

After days of testing, this seems to be the case in my project. Probably it's both, but I'm no longer seeing any issues, using Implements with late binding – Tittivate