Excel VBA Macro Hangs When Power Query Add-in Activated

Question

I have been running several Excel 2013/32bit VBA macros daily, on scheduled task, without issue.

When I activate the "power query" add-in, and change nothing else, the macros hang with the following error:

    See the end of this message for details on invoking 
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
Microsoft.Mashup.Client.ClientShared.Com.ComWrapperException: Cannot cast null to type 'System.Double'. ---> System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Mashup.Client.ClientShared.Com.ComWrapper.As[T](Object value)
   --- End of inner exception stack trace ---
   at Microsoft.Mashup.Client.ClientShared.Com.ComWrapper.As[T](Object value)
   at Microsoft.Mashup.Client.Excel.Com.ExcelComWrapper.AsIntFromDouble(Object value)
   at Microsoft.Mashup.Client.Excel.Com.Application.get_Build()
   at Microsoft.Mashup.Client.Excel.Com.ApplicationFeatures..ctor(IApplication application)
   at Microsoft.Mashup.Client.Excel.Com.Application..ctor(Object application, Boolean enableEvents)
   at Microsoft.Mashup.Client.Excel.AddIn.TryCreateApplication(Object appObject, IApplication& application)
   at Microsoft.Mashup.Client.Excel.AddIn.Extensibility.IDTExtensibility2.OnConnection(Object application, ext_ConnectMode connectMode, Object addInInst, Array& custom)


************** Loaded Assemblies **************
mscorlib
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1590.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll
----------------------------------------
Microsoft.Mashup.Client.Excel
    Assembly Version: 1.0.0.0
    Win32 Version: 2.35.4399.761
    CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Client.Excel.dll
----------------------------------------
Extensibility
    Assembly Version: 7.0.3300.0
    Win32 Version: 7.00.9466
    CodeBase: file:///C:/Windows/assembly/GAC/Extensibility/7.0.3300.0__b03f5f7f11d50a3a/Extensibility.dll
----------------------------------------
office
    Assembly Version: 15.0.0.0
    Win32 Version: 15.0.4859.1000
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/office/15.0.0.0__71e9bce111e9429c/office.dll
----------------------------------------
Microsoft.Mashup.Client.Windows
    Assembly Version: 1.0.0.0
    Win32 Version: 2.35.4399.761
    CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Client.Windows.DLL
----------------------------------------
Microsoft.Mashup.Document
    Assembly Version: 1.0.0.0
    Win32 Version: 2.35.4399.761
    CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Document.DLL
----------------------------------------
System
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1590.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1590.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Core
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1590.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
----------------------------------------
System.Windows.Forms
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1590.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
Microsoft.MashupEngine
    Assembly Version: 1.0.0.0
    Win32 Version: 2.35.4399.761
    CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.MashupEngine.DLL
----------------------------------------
Microsoft.Mashup.Client.AddinTelemetry
    Assembly Version: 1.0.0.0
    Win32 Version: 2.35.4399.761
    CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Client.AddinTelemetry.DLL
----------------------------------------
Microsoft.Mashup.ProviderShared
    Assembly Version: 1.0.0.0
    Win32 Version: 2.35.4399.761
    CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.ProviderShared.DLL
----------------------------------------
Microsoft.Office.Interop.Excel
    Assembly Version: 15.0.0.0
    Win32 Version: 15.0.4569.1506
    CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.Office.Interop.Excel/15.0.0.0__71e9bce111e9429c/Microsoft.Office.Interop.Excel.dll
----------------------------------------
Microsoft.Mashup.Client.Models
    Assembly Version: 1.0.0.0
    Win32 Version: 2.35.4399.761
    CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.Client.Models.DLL
----------------------------------------
Microsoft.Practices.Unity
    Assembly Version: 2.1.505.0
    Win32 Version: 2.1.505.2
    CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Practices.Unity.DLL
----------------------------------------
System.Configuration
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1590.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
    Assembly Version: 4.0.0.0
    Win32 Version: 4.6.1590.0 built by: NETFXREL2
    CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
Microsoft.Mashup.EventSource
    Assembly Version: 1.0.0.0
    Win32 Version: 2.35.4399.761
    CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20Power%20Query%20for%20Excel/bin/Microsoft.Mashup.EventSource.DLL
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
    <system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

Popup Error

When I remove the add-in "power query" and run the macro, the error does not occur.

How can I have the add-in active?

Does Power Query crash in the same way if you open a blank Excel workbook, or only when you use one of the macros? — Alejandro Lopez-Lago - MSFT

Eugene Eugene · Accepted Answer · 2016-12-13T09:22:50

You need to do the following:

Check if you have latest version of PowerQuery.
Open VBA editor (Alt+F11) and set a breakpoint in the start of your macro. Then execute it step-by-step to locate a line that causes PQ to hang. You can also start your macro in this mode by putting cursor in and pressing F8.
Report an error to Microsoft by clicking "Send a frown", with some of your code, including line that causes hang-up.
You may also write it here, as we might probably advise something (since this seem to be unhandled exception within PowerQuery).

Excel VBA Macro Hangs When Power Query Add-in Activated

1 Answers