Mocking doesn't work with objects other than Excel.Application

[dan-ignat]

Rubberduck version information

Rubberduck version [2.5.92.6353]
Operating System: [Microsoft Windows NT 10.0.19045.0, x64]
Host Product: [Microsoft Office x64]
Host Version: [16.0.18324.20194]
Host Executable: [EXCEL.EXE]

Description
I was able to successfully use the mocking example of Excel.Application described in the Quick Start here:
https://github.com/rubberduck-vba/Rubberduck/wiki/VBA-Moq-Mocking-Framework#quick-start

But as soon as I tried mocking a different Excel object, like Excel.Range, Excel.Font, Excel.Interior, I got the following error when executing e.g. Mocks.Mock("Excel.Range"):

Run-time error '-2146233086 (80131502)':

The supplied Excel.Range was not found. The class may not be registered or could not be located with the available metadata. Parameter name: ProgId

Same error for Excel.Font and Excel.Interior.

Can any Excel objects other than Excel.Application be currently mocked?

To Reproduce
Steps to reproduce the behavior:

1. Just try a simple:
   Dim Mock As Rubberduck.ComMock: Set Mock = Mocks.Mock("Excel.Range")
2. See error

Expected behavior
I'm expecting to be able to mock most Excel objects, not just Excel.Application.

Screenshots

Logfile

Additional context

[retailcoder]

Yeah I kind of got a little too excited there, this was always going to be highly experimental stuff 😅

Honestly I fixed some merge conflicts and fixed a unit test or two; the branch had been collecting dust for a long time and I should probably have tested it a bit more, uh, deeply. This PR simply had to be merged, but yeah if it survives pre-release it'll probably release as an experimental feature.

I was preparing a blog article about the feature and toying with it a bit more ...and yikes, indeed it's much more limited than I thought (although I'm sure @bclothier told me many times).

I'm going to continue to play with it to try and see whether there's one realistic use case that works (no luck so far), and I'm sure there's several ways to enhance the API to add whatever is missing to make it work.

FWIW I was able to get a mock worksheet with Mocks.Mock("ExcelWorksheet") and setup MockApp.ActiveSheet to return it, but the SetupChildMock that is intended for this is throwing, and as far as I could tell the properties aren't setup so the mocks don't retain any state, which... destroys their entire purpose. Could be just the ProgId used to identify the type, but I find it weird that the Application mock is clearly a proxy type, but the Worksheet mock is just a plain Worksheet:

Other than these technicalities, I'm finding that the sheer amount of methods and properties that yield a Variant or an Object is... not making this any easier... but I'd like to see how far this feature can be taken.

[dan-ignat]

You got excited? When I read the blog post, I nearly spurted coffee out my nose and fell out of my chair. 🙂

Mocking would be an absolute game-changer for VBA development. I follow good OO design and organize code into many small collaborating classes, and try to abstract most of them away from dealing directly with Ranges whenever possible. But some code somewhere must eventually deal with them, and if we could use mocks to test that code, it would drastically increase VBA testability.

Anyway, please keep up the great work. I just started using Rubberduck yesterday and it's an amazingly helpful add-in. I can never again go back to the plain vanilla VBA Editor.

[retailcoder]

But some code somewhere must eventually deal with them, and if we could use mocks to test that code, it would drastically increase VBA testability.

Indeed! In fact I was ambitiously hoping for it to make stubs entirely redundant. I've yet to try mocking user code (VBA classes); I'm aware of some serious limitations with it, but if it works at all it's still a massive game changer, especially in a project that has stable interfaces. I'll include my findings in the upcoming blog post.

I follow good OO design and organize code into many small collaborating classes, [...] I just started using Rubberduck yesterday

Oh wow, you're in for a ride! Make sure you check out @Folder annotations, I'm sure your projects crave it!

I can never again go back to the plain vanilla VBA Editor.

We can never read this kind of feedback too many times, thank you so much!

[dan-ignat]

Indeed! In fact I was ambitiously hoping for it to make stubs entirely redundant. I've yet to try mocking user code (VBA classes); I'm aware of some serious limitations with it, but if it works at all it's still a massive game changer, especially in a project that has stable interfaces. I'll include my findings in the upcoming blog post.

Agreed, that would be great as well. I think I tried mocking one of my user classes, and also got an error. I think it was the same one I got for the Excel classes above. I didn't tinker with it too much, as I thought maybe I was using it wrong.

Oh wow, you're in for a ride! Make sure you check out @Folder annotations, I'm sure your projects crave it!

Yes, I love the folders. Truth be told, I already had a similar workaround. I'm from Java land, so used to having packages. So I prefix all my class names with a package prefix that's an acronym of the actual package. E.g. a lang.string package would become an ls_ prefix for all classes in that package. I know, I hate Hungarian notation too. But given VBA's requirement that all module names in a project be unique, this was the only way I could think of to avoid having to disambiguate names in some other, contrived way (Color, Color2, etc.). The prefixes were also helping me keep the class list organized by package. Now the Rubberduck folders make it cleaner, in that I don't have to look through the whole list all the time, and can create a folder per package, while still keeping the prefixes for uniqueness.

We can never read this kind of feedback too many times, thank you so much!

You're most welcome! I know how much work something like Rubberduck must take, and truly appreciate all the time and effort you guys have all put in. I'm sure there are many other users who feel the same.

[dan-ignat]

Another idea... If mocking real Excel or user classes doesn't work, but there's a way to mock just a general class like Object, and tell the mock to expect whatever calls to whatever methods are needed for that test (even if those methods don't exist on the generic object), that would be great too.

Although I guess that wouldn't be a great solution, as then you lose the type safety of passing the mocked object around to procedures that expect its actual type.