Unit Testing fails with COM Exception

[ghost]

Hello!

Thanks for all of your hard work on Rubberduck! I've been using it for a few months now and it is very helpful!

I've inherited a project involving VBA code in an Excel spreadsheet, with the goal of improving it. I am wanting to setting up Unit Tests, but am running into problems.

I set up a test, and it works once or twice, but eventually it fails to run. The message in the Test Explorer is:

Test raised and error. Sorry, we couldn't find . Is it possible it was moved, renamed, or deleted?

I dig into the log and see the following error message:

2017-06-16 15:52:43.2126;ERROR-2.0.14.18332;Rubberduck.UnitTesting.TestEngine;Unexpected COM exception while running tests.;System.Runtime.InteropServices.COMException (0x800A03EC): Sorry, we couldn't find . Is it possible it was moved, renamed or deleted?
   at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)
   at CallSite.Target(Closure , CallSite , ComObject , Object )
   at Rubberduck.VBEditor.Application.ExcelApp.Run(Object declaration) in C:\Users\electassy\Documents\Rubberduck-next\Rubberduck.VBEEditor\Application\ExcelApp.cs:line 17
   at Rubberduck.UnitTesting.TestEngine.Run(IEnumerable`1 members) in C:\Users\electassy\Documents\Rubberduck-next\RetailCoder.VBE\UnitTesting\TestEngine.cs:line 130

I never got tests to work when using Late binding for the tests. I switched the tests to Early binding. I have four tests set up. If I comment all but one of the tests, I can sometimes get one to work, but as soon as I uncomment another one, I encounter this problem.

RubberduckLog.txt

[ghost]

Should mention that I am using Excel 2013 on Windows 7.

Also, here is another log file; with the log level set to Trace.

1) Quit Excel 2) deleted the previous log file 3) Opened my VBA project in Excel 4) Successfully ran two tests, twice. 5) Uncommented another unit test, 6) Got COM exception error as written in the OP.

RubberduckLog.txt

[ghost]

Also would like to note that I have a empty test set up in a different test module, that test appears to run successfully each time.

[retailcoder]

If you call the test method from the immediate pane, does VBA fire any error?

[ghost]

I have not tried it from the Immediate pane, but I can step through the test function just fine (when using early binding.)

[ghost]

If I try running the unit test in the Immediate pane, I get a VBA error:

Run-time error '91': Object variable or With block variable not set

When I click on Debug, i see that code execution is paused on the TestFail: error handling line

TestFail:
    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description

I saw the following error a few times in my trials to get unit tests working, and I finally captured it. Note that this error references the file name and module name, but it looks like it is not parsed correctly:

2017-06-19 07:56:53.4985;ERROR-2.0.14.18332;Rubberduck.UnitTesting.TestEngine;Unexpected COM exception while running tests.;System.Runtime.InteropServices.COMException (0x800A03EC): Sorry, we couldn't find GUACP_Matrix_Rev_20.7.8.xlsm - [m_ReadWriteYaskawaTests (k. Is it possible it was moved, renamed or deleted?
   at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)
   at CallSite.Target(Closure , CallSite , ComObject , Object )
   at Rubberduck.VBEditor.Application.ExcelApp.Run(Object declaration) in C:\Users\electassy\Documents\Rubberduck-next\Rubberduck.VBEEditor\Application\ExcelApp.cs:line 17
   at Rubberduck.UnitTesting.TestEngine.Run(IEnumerable`1 members) in C:\Users\electassy\Documents\Rubberduck-next\RetailCoder.VBE\UnitTesting\TestEngine.cs:line 130

[retailcoder]

Nah, it's correctly parsed; the problem seems to be with the whitespaces in the filename - a bug that we have already fixed IIRC. Try saving a copy with a shorter filename without spaces; that annoyance will definitely be fixed next release.

[ghost]

I think that I am experiencing two different issues here.

I opened this issue with an issue where all my unit tests silently fail and I get the following message in the Text Explorer for each test: 'Test raised an error. Sorry, we couldn't find . Is it possible it was moved, renamed, or deleted?'

I now think that is a separate issue that I added to #3076.

If i do what I wrote in #3076 to get this and other unit tests working, I still remain with one that fails with a slightly different error message: 'Sorry, we couldn't find GUACP_Matrix_Rev_20.7.8.xlsm - [m_ReadWriteYaskawaTests (k. Is it possible it was moved, renamed or deleted?'

@retailcoder I don't mean to be a jerk, but are you sure that it is parsing the module name correctly? I ask because that error message does not have a trailing bracket, and that "(k" is odd.

Either way, I am experiencing this on build of the current next branch.

[retailcoder]

Tests are discovered before they're run, and test discovery happens after a successful parse. If there was a parsing issue the test engine wouldn't even try to run any of the tests. The host file having spaces is causing this problem; the partial file name you're seeing in the stack trace is meant to be the entire complete fully-qualified name of the macro (in Excel at least), but there's a bug that's messing up that string when the filename contains spaces. That said we parse code, not file names.

So that bug is still present.. thanks for this thread!

[ghost]

I do not think it is the same as the previously fixed issue. Related, but not the same.

First, the file name does not have any spaces. It has underscores.

Second, if I shorten the module names from m_ReadWriteYaskawaTestsor m_ZipArchiveFunctionsTests to m_RWYT or m_ZAFT, the unit tests run successfully. They fail if I change the module names back to the full versions.

And they successfully run with shorter module names, even with spaces in the file path and underscores in the file name.
Z:\VM Shared Files\GUACP Matrix Improvements\GUACP_Matrix_Rev_20.7.8.xlsm

[MDoerner]

I have a vague idea what could be going on here.

@shadowofsilicon Could you try whether it works when you try it with the original name but in the topmost directory of your drive?

[retailcoder]

Yeah that's Application.Run having a stupid short maximum length for the macro name to run - it would work with just the macro name, but then you'd run into problems with running RD tests with more than 1 workbook opened, or if you have Module1.Test1 and Module2.Test1; that's why we're fully-qualifying the names, including the workbook name (even VBProject1.Module1.Test1 isn't unambiguous).

[ghost]

Moved the file to C:\GUACP_Matrix_Rev_20.7.8.xlsm and after I cajole the tests into running with my workaround in #3076 I get the following error:

2017-06-19 14:47:53.3059;ERROR-2.0.14.16836;Rubberduck.UnitTesting.TestEngine;Unexpected COM exception while running tests.;System.Runtime.InteropServices.COMException (0x800A03EC): Sorry, we couldn't find GUACP_Matrix_Rev_20.7.8.xlsm - [z_ZipArchiveFunctionsTestk. Is it possible it was moved, renamed or deleted?
   at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)
   at CallSite.Target(Closure , CallSite , ComObject , Object )
   at Rubberduck.VBEditor.Application.ExcelApp.Run(Object declaration) in C:\Users\electassy\Documents\Rubberduck\Rubberduck.VBEEditor\Application\ExcelApp.cs:line 17
   at Rubberduck.UnitTesting.TestEngine.Run(IEnumerable`1 members) in C:\Users\electassy\Documents\Rubberduck\RetailCoder.VBE\UnitTesting\TestEngine.cs:line 130

I see a trailing k where i would expect a trailing ] in the module name in that error.

[MDoerner]

@shadowofsilicon Thank you for testing this.

That basically rules out the argument length issue. I will investigate further.

[retailcoder]

@shadowofsilicon Is there anything after that K? z_ZipArchiveFunctionsTestk looks very much like it was trimmed to fit some maximum length; what is the expected string?

[ghost]

The name of the test module is z_ZipArchiveFunctionTests

In the debug log, I would expect the string to be [z_ZipArchiveFunctionTests].

To me, it looks like s] has been truncated and k has been concatenated.

But I can't say for sure what it should be as I am not familiar with the code for Rubberduck. I don't know where the bracket(s) surrounding the module name come from. It could just be from concatenation to generate the error message, but I would sure think that the code for that would look wrong, therefore I assume that there should be a trailing bracket.

[MDoerner]

This k is really strange. However, that part of the error message seems to be part of the original COMException thrown by the interop library.

@shadowofsilicon Just to be 100% sure about the argument length issue: What is the name of your longest test method in the test module? Well, the length would be enough information. Oh, and could you also tell us the length of the name of the project the test module is contained in?

[ghost]

Longest test method name is Test_CreateArchive which is 18 characters long.

The VBA project name is GUACP_Matrix which is 12 characters long.

[MDoerner]

OK, I can reproduce the issue when using the same file name, module name and test name.

However, the error message I get is even stranger:

2017-06-20 00:58:22.2466;ERROR-2.0.14.1576;Rubberduck.UnitTesting.TestEngine;Unexpected COM exception while running tests.;System.Runtime.InteropServices.COMException (0x800A03EC): Sorry, we couldn't find . Is it possible it was moved, renamed or deleted?
   at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)
   at CallSite.Target(Closure , CallSite , ComObject , Object )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at CallSite.Target(Closure , CallSite , Application , Object )
   at System.Dynamic.UpdateDelegates.UpdateAndExecuteVoid2[T0,T1](CallSite site, T0 arg0, T1 arg1)
   at Rubberduck.VBEditor.Application.ExcelApp.Run(Object declaration) in H:\Repositories\Rubberduck\Rubberduck.VBEEditor\Application\ExcelApp.cs:line 17
   at Rubberduck.UnitTesting.TestEngine.Run(IEnumerable`1 members) in H:\Repositories\Rubberduck\RetailCoder.VBE\UnitTesting\TestEngine.cs:line 130

[ghost]

See #3076

I get the same error message but the call stack is different

2017-06-19 14:51:49.8331;ERROR-2.0.14.16836;Rubberduck.UnitTesting.TestEngine;Unexpected COM exception while running tests.;System.Runtime.InteropServices.COMException (0x800A03EC): Sorry, we couldn't find . Is it possible it was moved, renamed or deleted?
   at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)
   at CallSite.Target(Closure , CallSite , ComObject , Object )
   at Rubberduck.VBEditor.Application.ExcelApp.Run(Object declaration) in C:\Users\electassy\Documents\Rubberduck\Rubberduck.VBEEditor\Application\ExcelApp.cs:line 17
   at Rubberduck.UnitTesting.TestEngine.Run(IEnumerable`1 members) in C:\Users\electassy\Documents\Rubberduck\RetailCoder.VBE\UnitTesting\TestEngine.cs:line 130

[MDoerner]

Let me explain what is going on, here. We are using Excel's Application.Run method to run the unit tests. In order to do this, we have to pass in the fully qualified name of the test. Otherwise the call to Application.Run could be ambiguous. The qualified name consists of the procedure name, the module name, the project name, and the name of the workbook (without the path). We fall back to omitting the workbook if the maximum argument length for Application.Run (255 characters) would be to small otherwise.

The problem originates in a bug in how the workbook name is discovered. To get this, we look at the caption of the VBE's main window. Unfortunately, for some reason the corresponding method in Microsoft's interop library returns gibberish after character 99 of the caption whenever the caption exceeds 99 characters.

[PeterMTaylor]

So in a nutshell, total length of qualified name is ( procedure name, the module name, the project name, and the name of the workbook (without the path)) must not exceed over 99 characters worst case, likely under 99 and best case on average is under 99 characters.

[MDoerner]

@PeterMTaylor Not really. Neither the procedure name nor the project name matter for this problem. What matters is the workbook name and the name of the module. Moreover, the limit for their combined length is significantly smaller.

Open up the VBE in Excel and open any module. What you see in the caption of the VBE window is what may not exceed 99 characters in order to avoid the bug.

The limit on the fully qualified name of the test itself is 255 characters. This restriction is due to the restrictions of Applications.Run in Excel. (We ommit the workbook in case we exceed the limit otherwise.)

[ThunderFrame]

@PeterMTaylor Here's a contrived example of a fully constructed, fully qualified path:

'WorkbookWithReallyLongName.xlsm'!VBAProjectWithReallyLongName.TestModuleWithReallyLongName.TestMethodWithReallyLongName

As contrived as that example is, it's still only 120 characters, but some users do like descriptive file names like Sales Report (with totals) For Engineering Department Team B Version 84.12c PROD Final (USE THIS COPY - DO NOT DELETE).xlsm

Add in some descriptive project, module and method names, and you're knocking on the door of 255 awfully fast.

Interestingly, that filename, at 118 characters, only shows the first 117 characters as the Window caption in VBE.

We can omit the filename and the project name, if the project is active, as Application.Run defaults to the active project. That affords much longer module and method names, but the 255 character limit can still be breached: VBA/VBE allows module names of up to 31 characters, but method names can be up to 255 characters. The method can be called without module qualification, but only if it is known to be unique - otherwise, it requires qualification.

[daFreeMan]

Hearkens back to his early days using DOS...

Would it be possible to get the 8.3 file name for the workbook, or has that been completely abandoned in Win8+?

I'm running Win7 at the office right now and I can get an 8.3 with dir /x and for the weaponized-ThunderFrame-exceedingly-long-file-name™ it gives me SALESR~1.XLS. Clipping all workbook names down to a manageable 12 characters might just make it worthwhile.

[ThunderFrame]

VBE is unaware of the 8.3 name, just as Application.Workbooks isn't indexed by 8.3 name.

[ThunderFrame]

So it seems that VBIDE's Window.Caption property will only ever return up to 98 characters, even if the caption of the window is longer than 98 characters, and all of those characters can be read with Win32 API calls.

But in the actual/visible MainWindow caption, as soon as the length of the filename exceeds 117 characters, the filename is truncated. In the codepane window, as soon as the length of the filename exceeds 127 characters, the filename is truncated.

i.e. Even though the VBIDE Window.Caption only returns the first 98 characters, for longer names, even if we're able to read the actual window caption using Win32, that is insufficient to reliably determine the actual name of the file.

[Inarion]

Please forgive my ignorance, as I have no clue about C#, but is there a reason you're not using the Workbook's .Name/.Path/.FullName properties to determine the file's name and location in the filesystem? They are available even through Excel's COM server.

[ThunderFrame]

That would be an Excel-specific solution - Rubberduck allows for multiple VBA hosts.

So, how to get the host document name?

It could be host specific:

Excel: Application.ThisWorkbook.Name Word: Application.MacroContainer.Name Access: Application.CodeDB.Name

But I don't think PowerPoint has a useful member?

[Inarion]

For PP I just found Application.ActivePresentation or Application.Presentation(i) - this is not exactly the equivalent of ThisWorkbook in Excel, but should still be able to provide the necessary information.

Edit: PowerPoint Presentation objects do seem to provide the same properties Name, Path and FullName as Workbooks do.

[ThunderFrame]

@Inarion The problem is that Rubberduck exists in the context of the VBE, so it needs to be able to discover the document name given only a VBProject. As I pointed out earlier, Excel offers Application.ThisWorkbook and Application.ActiveWorkbook, but while Application.ThisWorkbook returns the workbook of the selected/running project, Application.ActiveWorkbook isn't necessarily the same workbook as the selected/running project.

So we need:

• Application.ThisWorkbook and not Application.ActiveWorkbook
• Application.MacroContainer and not Application.ActiveDocument

PowerPoint's Application.ActivePresentation isn't necessarily going to return the correct presentation, and I doubt it would work with Masters or Addins.

[Inarion]

I see, this is unfortunate indeed...

What about Application.VBE.ActiveVBProject? From my quick testing, its FileName property seems to return (approximately*) the same value as Application.ThisWorkbook.FullName in an Excel context. Further testing shows that besides .xlsx it also works on

• Excel .xlam

• Word .docx and .dotm

• PowerPoint .pptx and .potm

• Access .mdb and .accdb

(tested with Excel/Word/PowerPoint 2010 and Access 2013)

*there is a discrepancy in how paths to files are resolved which are located on network drives mounted as local drives: Application.VBE.ActiveVBProject.FileName returns the network path (e.g. \server\folderAboveMountPoint\folderBelowMountPoint\subfolder\filename.pptx) whereas Application.ActivePresentation.FullName returns the local path (e.g. Y:\folderBelowMountPoint\subfolder\filename.pptx)

EDIT: The only catch is that this only works if the file has been saved, but I guess that's true for about anything we could do with RD...

[ThunderFrame]

Rubberduck starts with a reference to VBE, so we don't need to retrieve it again (and in any case, Application.VBE might be blocked by the user's trustt settings.

But that isn't the issue. VBProject.FileName is reliable, but only for files that have already been saved at least once. For a file that hasn't ever been saved (eg. a new workbook), VbProject.FileName throws an exception. We can try/catch the exception, but then what do you do if there is more than one new/unsaved workbook open? How do you tell them apart?

BTW - The VBProject.Saved property doesn't help here - it doesn't tell you if the file has been saved. Saved really means IsNotDirty.

[Inarion]

So it seems that the whole Window.Caption issue only arises for cases where the other (more reliable) methods fail? And only in cases where the respective file has a really long name (and/or) long module names will there be a real problem? A prioritized list might look like this:

1. For already saved files utilize VBProject.FileName (trying to read that property then also tells you whether or not the file has been saved)
2. For unsaved files in Excel/Word/Access use Application.ThisWorkbook / Application.MacroContainer / Application.CodeDB
3. For unsaved files in PowerPoint use the Window.Caption and try to verify its correctness via comparing it to the names of open presentations
4. If 3. doesn't return exactly one presentation, ask the user to save the presentation first (not elegant, but if it helps prevent an error maybe it's worth it?)

It seems to me that there would be a rather small amount of cases where circumstances force you to rely on the Window.Caption (PowerPoint, unsaved), so that in the vast majority of cases you could go routes known to work more reliably. It does, however, mean a staggered approach rather than a universal solution.

[rubberduck203]

It doesn't seem absurd to me to ask the user to save if this occurs. Assuming we can tell that's the issue where the exception is thrown.

[MDoerner]

Using Application.ThisWorkbook or similar functions in other hosts does not work. This function returns the workbook of the code calling Appliction.Run. However, we are calling Application.Run via the interop library from the managed side and there is no workbook. So, trying to get the name via Application.ThisWorkbook results in a COM exception. Anyway, we would never get the right name from this method since we are not executing inside th project whose tests we want to run.

In principle, we could ask for the name of workbooks via the Name, Path and FullName properties. However, to do this we would have to find them in workbooks collection, which requires us to know the name.

[MDoerner]

I agree that we could try to get the FileName of the project first.

I would suggest to generally fall back to the window caption whenever we cannot get the document name from the FileName. The result would then need some further validation. (Actually, the code needs only a minor tweak if we want to throw the result away in case we get something strange.)

I also agree that asking the user to save the project might be OK as a last resort.

[gbalcom]

I hope this is the right place to put this, if it isn't please move where needed.

I think I have a similar problem. I'm late binding, but it's not seeming to find the Rubber duck assert classs for unit testing.

I'm running Access 2013, rd version 2.1.0.2382. Thanks! RubberduckLog.txt

Here is my test module:

TestModule_clsProduct.txt

[bbarney213]

I am having a similar issue to the original issue ghost was having. My unit tests all fail with Test raised an error. Sorry, we couldn't find C:Users\b.barney1\Documents\Test.xlsx. Is it possible it was moved, renamed, or deleted?. After reading all of the comments I tried creating a completely fresh file, saved to my desktop, no long names or anything ('C:\Users\b.barney1\Desktop\Test.xlsx') I added a test module using 'Add Test Module With Stubs' and created a simple test case that should pass.

In the original project I was working in, the path was also pointing to ..\Documents\ so I assume that this is a similar issue with resolving the full path of the project.

RubberduckLog.txt

[bclothier]

Closing this because this no longer applies - the issue came about due to the limitations of Application.Run but we are no longer using it -- ref #3778

Tested with an excel workbook:

C:\Users\USER~1\DOCUME~1\SQLSER~1\VULNER~1\MORELO~1\EVENLO~1\MOREBL~1\AExeeedingLongWorkbookabcdefghijklmnopqrstuvwyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ=$.xlsx

aka:

C:\Users\User\Documents\SQL Server Management Studio\Vulnerability Assessment Reports\More longer\even longer folder name because blah blah blah blah blah blah blah blah blah blah blah\more blah blah blah blah blah blah blah\AExeeedingLongWorkbookabcdefghijklmnopqrstuvwyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ=$.xlsx

with test module: TestModule123456789012345678901 with a method: TestMethod1234567890abcdefghijklmopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWYXZ

and the test runs.