none
Powerpivot - Can't insert formula that combines two or more different columns and keep getting message "unhandled exception has occurred in a component in your application...

    Question

  • I am using powerpivot v2 with excel 2010.

    When I go to my excel file's powerpivot and I try to insert a formula in any column, I constantly get the following message:

     

    "unhandled exception has occurred in a component in your application. If you click continue, the application will ignore this error and attempt to continue".

    ************** Exception Text **************

    System.InvalidCastException: Return argument has an invalid type.

       at System.Runtime.Remoting.Proxies.RealProxy.ValidateReturnArg(Object arg, Type paramType)

       at System.Runtime.Remoting.Proxies.RealProxy.PropagateOutParameters(IMessage msg, Object[] outArgs, Object returnValue)

       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

       at Microsoft.Office.Interop.Excel._Application.get_ActiveWorkbook()

       at Microsoft.AnalysisServices.XLHost.Addin.GeminiRibbon.currentClientWindow_InFormulaBarChanged(Object sender, EventArgs e)

       at Microsoft.AnalysisServices.Common.SandboxEditor.formulaBar_Leave(Object sender, EventArgs e)

       at System.EventHandler.Invoke(Object sender, EventArgs e)

       at System.Windows.Forms.ContainerControl.UpdateFocusedControl()

    Does anybody know how to fix this ?


    Wednesday, August 29, 2012 12:36 AM

Answers

  • Please don't install PowerPivot 2010 on Excel 2013 - Excel 2013 actually comes with PowerPivot built in but disabled by default. YOu can enable it by doing:

    • Go to File > Options > Add-Ins.
    • In the Manage box, click COM Add-ins> Go.
    • Check the Microsoft Office PowerPivot for Excel 2013 box, and then click OK. If you have other versions of the PowerPivot add-in installed, those versions are also listed in the COM Add-ins list. Be sure to select the PowerPivot add-in for Excel 2013.

    -- This posting is provided "AS IS" with no warranties, and confers no rights

    Wednesday, September 19, 2012 6:06 PM
  • Hi David,

    I know that you will not like to hear this.

    However, after wasting an entire week, calling MS, and trying everything to fix things, there is only 1 solution.

    You need to uninstall everything and re-install a new image of your PC. This solved my problem and now powerpivot works just fine.

    Regards.

    Thursday, August 01, 2013 6:56 PM
  • Mark,

    I used the Fix It found here: http://support.microsoft.com/kb/2739501

    I would have to ask my IT support person for more specifics, but as I understand the scenario with my particular laptop: the laptop came preloaded with RTM build of 2013.  We used Control Panel to uninstall Office 2013, in order to install Office 2010.  I installed PowerPivot add-in and used it successfully with no issues for several months.  After attempting to install another program and encountering issues with apparent traces left behind of Office 2013 uninstall, I found the link above and used the Fix It to attempt to remove all remaining traces of Office 2013.  While it did appear to find and clean-up quite a bit left behind from the Office 2013 install, I was surprised to find an error upon entering my existing installation of PowerPivot for 2010 and attempting to type new formulas into the formula bar.

    My experience to-date is that an error message stating ""Return Argument Has an Invalid Type" appears anytime I select or modify a formula in the formula bar that would cause the intellisense to appear.  For example, when I first click into the formula bar, I do not get an error...but as soon as I type the first letter, before the intellisense appears, I get the error message.  Throughout the remainder of the formula as I type, just prior to any time when the intellisense drop-down will appear again, I get the same error message again (see details below).

    At one time, although I can't reproduce it now, the error message did state ".Net Framework" in the menu bar of the error pop-up window.  I can't get that to appear again, as it now only says "PowerPivot for Excel".  Based on this, I had attempted to see if I could repair my .NET framework install using the following tool: http://www.microsoft.com/en-us/download/details.aspx?id=30135

    I learned, however, that since I am running Windows 8 which comes with .NET 4.5 built-in to the OS, this tool does not yet cover that version for repair.

    While the overall end-result of my PowerPivot models appear to be just the same, having to click through "Continue" on the error message multiple times each time I enter a new formula is a huge drain on productivity.

    Let me know if you have any additional questions.

    David

    ********************

    System.InvalidCastException: Return argument has an invalid type.
       at System.Runtime.Remoting.Proxies.RealProxy.ValidateReturnArg(Object arg, Type paramType)
       at System.Runtime.Remoting.Proxies.RealProxy.PropagateOutParameters(IMessage msg, Object[] outArgs, Object returnValue)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at Microsoft.Office.Interop.Excel._Application.get_ActiveWorkbook()
       at Microsoft.AnalysisServices.XLHost.Addin.GeminiRibbon.currentClientWindow_InFormulaBarChanged(Object sender, EventArgs e)
       at Microsoft.AnalysisServices.Common.SandboxEditor.formulaBar_Leave(Object sender, EventArgs e)
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.Windows.Forms.ContainerControl.UpdateFocusedControl()


    David Duncan Consultant I.B.I.S., Inc.

    Tuesday, August 06, 2013 5:30 PM

All replies

  • I got the same problem! any solution? Thanks!
    Tuesday, September 04, 2012 11:02 AM
  • Jojx1,

    There is not much info about this issue over the internet.

    THere is some speculation that it has to do with visual studio or Net framework or both.

    However, so far nobody has a definite answer to this problem....definitely a glitch in Powerpivot.

    Tuesday, September 04, 2012 9:39 PM
  • Ok, Thanks ALEXRK.

    I have tried to reinstall the powerpivot plugin, reinstall Office, reinstall .net framework and reparied my SQL Server 2012 installation. But the bug is still there. I cant develop solution in a enviroment where I have to click continue on three error message each time I write a calculation.
    My last hope is to go back to a backup image of my computer...

    Wednesday, September 05, 2012 5:51 AM
  • Hi again.

    I have now got back to a system image from June. The only changes I done to my system since then was to try Office 2013, and uninstalling after a day. Now when I am back,  the problems in PowerPivot is gone :) I suppose that Office 2013 did something to my system and not correct it when uninstalling.

    • Proposed as answer by NGUYEN Yann Wednesday, September 05, 2012 3:41 PM
    • Unproposed as answer by NGUYEN Yann Wednesday, September 05, 2012 3:41 PM
    Wednesday, September 05, 2012 8:30 AM
  • I have been struggling hard with the same problem since a week. I solved it but don't know what is the real problem. I think it is due to Office 2013 with bad PIA Registry keys on Microsoft.Office.Interop.Excel.

    1 - First complete the uninstall of OFFICE 2013:

    http://support.microsoft.com/kb/2739501

    http://answers.microsoft.com/en-us/office/forum/office_home-office_install/unable-to-uninstall-office-365-home-preview/86bd3242-f137-4a0b-ba22-94867d82b7c2

    2 - Delete all Registry Keys refering the following assembly: Microsoft.Office.Interop.Excel, Version=15.0.0.0

    3 - If not enought delete all keys refering Microsoft.Office.Interop.XXXX, Version=15.0.0.0 where XXXX is an Office 2013 program

    Work fine with this for me !

    Wednesday, September 05, 2012 3:55 PM
  • JoJx1,

    It is interesting that you mentioned trying office 2013.

    I also tried excel 2013 and then I had to uninstall it because it was creating too many problems with my excel 2010 versions, which I had to share with other people.

    Perhaps as you indicated, there is a correlation between office 2013 creating this problem. Maybe the 2013 version damaged...microsoft visual studio 2010 tools for office runtime...or...microsoft Net framework 4....which are both required to use powerpivot.


    • Edited by ALEXRK Wednesday, September 05, 2012 6:53 PM
    Wednesday, September 05, 2012 6:50 PM
  • I have the same problem  following an Office 2013 install. 
    Wednesday, September 19, 2012 6:07 AM
  • As Joix1 mentioned above, I reinstalled my PC's image from when I purchased the pc.

    Now, the problem is gone.

    As it was indicated above, this is definitely related to the installation and later removal of excel 2013 beta testing download.

    I hope that microsoft becomes  aware of this, because the excel 2013 beta testing download gave me a lot of problems with my previous excel 2010 files, as well as with inserting formulas that combine two or more columns (which is discussed in this thread)

    Wednesday, September 19, 2012 3:54 PM
  • Please don't install PowerPivot 2010 on Excel 2013 - Excel 2013 actually comes with PowerPivot built in but disabled by default. YOu can enable it by doing:

    • Go to File > Options > Add-Ins.
    • In the Manage box, click COM Add-ins> Go.
    • Check the Microsoft Office PowerPivot for Excel 2013 box, and then click OK. If you have other versions of the PowerPivot add-in installed, those versions are also listed in the COM Add-ins list. Be sure to select the PowerPivot add-in for Excel 2013.

    -- This posting is provided "AS IS" with no warranties, and confers no rights

    Wednesday, September 19, 2012 6:06 PM
  • Hi Chu

    This appears to be a wide spread issue, based on the previous links and threads. Can you give a Microsoft perspective on this problem? Is Microsoft working on a solution? Is there an ETA?

    I also installed Excel 2013, de-installed it and now Powerpivot in Excels 2010 doesn't work.  The suggested " registry" fix didn't work for me. My system restores  don't extend back far enough to be a solution. So I am looking at doing a "factory reset" and then installing a back up - a major pain in butt. I don't want to so this if there is some sort of fix on the horizon. Hence understanding if Microsoft is looking into this issue would be valuable.

    Regards

    Steve 

     


    Sunday, September 23, 2012 9:15 AM
  • Steve:

    When Excel 2013 RTMs, PowerPivot for Excel 2010 will be blocked from being loaded or visible.

    As for your specific problem, I suggest that you reinstall Office 2010 and the PowerPivot 2010 addin.

    Regards,

    Chu


    -- This posting is provided "AS IS" with no warranties, and confers no rights

    Sunday, September 23, 2012 2:51 PM
  • Hi Chu

    Thanks for your email. I deinstalled Office 2013 sometime ago. As ALEXRK and JojX1 also alluded to, Powerpivot become non-functional after removing Office  2013. The first thing I tried was a deinstall of  2010 and Powerpivot and re installing. I tried installing  with both 32 and 64 bit version. When this didn't work I looked into the registry fix outlined above (http://answers.microsoft.com/en-us/office/forum/office_home-office_install/unable-to-uninstall-office-365-home-preview/86bd3242-f137-4a0b-ba22-94867d82b7c2) . This didn't seem to apply as  the registry files mentioned in the fix weren't present. 

    Any other ideas? 

    Regards

    Steve 

    Sunday, September 23, 2012 7:54 PM
  • Hello Steve,

    I had too many problems with the installation of the beta version of excel 2013, not because excel 2013 was a bad product...it was great...(although I hate its esthetics).

    The problem was that excel 2013 was creating too many problems with the excel 2010 files I was sharing with others. Unfortunately, microsoft does not warn or even offer any protection to keep your excel 2010 files as excel 2010. Once you install excel 2013, you are in big trouble.

    So, I was forced to remove excel 2013, which created many problems with my excel 2010.

    Microsoft does not have any ways to truly fix this. So, as I did, you will need to get an image of your pc from before installing excel 2013 to fix it.

    Sunday, September 23, 2012 8:10 PM
  • Hi Alex

    I suspect I will be forced down the same route as you. I wanted to avoid this if at all possible, due to the time and inconvenience. Hence the question for Chu, as to whether Microsoft has an ETA on a fix. The issue was flagged as a problem almost a month ago. So as you indicated, if  there is currently no easy fix, I feel Microsoft should be working on a solution and keep us updated on an ETA.

    This problem is hitting early adopters of Microsoft products, who are normally big advocates  for MS, so I feel  some weight should be put on this.

    Unfortunately I feel you are right when you say - Microsoft does not have a way to fix this problem (other than a reinstall of an image or OS).  This is a big pain when you start looking at multiple machines. 

    Because of this, last week I was forced to abandon a PowerPivot dev project (for a large multinational Silicon Valley company) and run with a competitive solution (Tibco Spotfire Silver). If we had used PowerPivot there would have been sales of Office 2010 for Microsoft. 

    As of next week, I will be in a similar situation with another client.

    This situation highlights there is also a financial benefit to MS to resolve the issue ASAP.

    Regards

    Steve 


    Sunday, September 23, 2012 8:58 PM
  • Hello Steve,

    sorry that it happened to you as well.

    Changing the topic, how do you get involved in developing powerpivot projects for companies ?

    Most of the companies executives are people with big job titles but little knowledge. They don't even know (or know almost nothing) about analytics, powerpivot and dashboards, you have to sell them the idea.

    It is hard work and normally you need an insider to even get a meeting. 

    Let me know.

    Sunday, September 23, 2012 10:24 PM
  • Hi Alex, The business development question is best discussed off this forum. I am happy to do this via email - contact me at Steve@analyse.us
    Sunday, September 23, 2012 10:33 PM
  • Hi Steve,

    I've confirmed this problem, which seems to be related to Click-to-Run installations of Office 2013 followed by an uninstall. We're now tracking this issue in our bug database. We don't have an ETA, but will try to get back with a proposed workaround or fix as we know more.

    Best,

    Mark


    This posting is provided "AS IS" with no warranties, and confers no rights


    • Edited by Mark Sisley Wednesday, September 26, 2012 6:51 PM
    Wednesday, September 26, 2012 6:49 PM
  • Thank you! Deleting Registry keys helped!
    Tuesday, October 23, 2012 11:15 AM
  • I am stuck with the same problem since weeks !!!

    I deleted registry keys and it did not work

    Any solution or patch from Microsoft ?

    It would be highly appreciated.

    If so, I give my email jm.philip@hotmail.fr

    Thanks!

    Tuesday, December 11, 2012 9:22 AM
  • Hello Jean-Marc13,

    If you completely read this thread, you will see that I had to completely re-install a new image of my PC and also re-install excel and powerpivot.

    After re-installation, things are working perfectly fine.

    Sorry about the bad news, but I had no choice. I got tired of wasting time trying to figure out how to repair things, so it is a much faster approach to re-install everything.

    I just hope that microsoft is taking note about all these issues.

    Regards,

    Alex

    Tuesday, December 11, 2012 4:27 PM
  • Dear Alex, Chu, Mark,

    Presently, I cannot afford to re install an image of my PC:

    - 1, The time spent for that operation. Is it an acceptable solution to solve a Microsoft bug? 

    - 2, I updated Windows 7 to Windows 8 and I desinstalled Office 2013 just after. I got this problem before making an image with a clean configuration. In addition, I wanted to  add features to Windows 8 and I got the message "Sorry, ... this key won't work ..". Why ? I updated Windows 7 to Windows 8 on line., ... Another issue that I do not understand.

    Clearly, I made the usual mistake of trying or purchasing Microsoft products too early.

    However this issue in PowerPivot is really a pain in the ass from the begining, because it is precesily the reason I desintalled Office 2013, which is nevertheless a great product: you cannot send an xlsx Excel file with PowerPivot 2013 to someone who has only Office 2010

    Now It's simply impossible to make any calculation in the PowerPivot windows of Office 2010.

    I tried all the suggested options and nothing worked. 

    Could Mark or Chu tell us when a fix is expected to be available in inserted in Microsoft update ?

    I udpate computer every day, desperately and exasperatedly looking for the bug to be fixed, but nothing changes.

    Is it so complicated to fix that issue?

    Thanks again for answers.

    Tuesday, December 18, 2012 1:32 PM
  • To determine if your machine still contains Office 2013 registry keys, run this query from the command line:

    reg query HKLM  /f "Microsoft.Office.Interop.Excel, Version=15.0.0.0" /s

    It should return a sizeable list of key/value pairs.  I pasted the keys in to a file to create a batch file to delete each key.  Note you will need full admin to run this, and manually respond 'Y' to each command.   Once thee keys were deleted PowerPivot returned to normal!


    Monday, January 07, 2013 9:40 PM
  • Mark/Chu - is there a fix for this issue yet?  I now have the same issue after going down the route of uninstalling Microsoft 2013 using the Fix It provided by MS.  PowerPivot provides me with incessant error messages stating "Return argument has an invalid type" with the same details as provided by the original poster.

    David


    David Duncan Consultant I.B.I.S., Inc.

    Thursday, August 01, 2013 12:17 PM
  • Hi David,

    I know that you will not like to hear this.

    However, after wasting an entire week, calling MS, and trying everything to fix things, there is only 1 solution.

    You need to uninstall everything and re-install a new image of your PC. This solved my problem and now powerpivot works just fine.

    Regards.

    Thursday, August 01, 2013 6:56 PM
  • Hi David,

    It was previously thought that this issue only occurred when uninstalling Beta builds of Office 2013. Given this and the fact that jlegelis had provided a workaround, it had taken a lower priority. Given that you've apparently reproduced this issue while uninstalling an RTM build of Office 2013, we're currently re-evaluating what to do about the issue.

    Meanwhile, could you please direct me to the Fix It that you're attempting to use?

    Further, if jlegelis could potentially share the batch script they implemented on this thread, perhaps this can help others workaround the issue more easily.

    Best,

    Mark


    This posting is provided "AS IS" with no warranties, and confers no rights

    Thursday, August 01, 2013 9:24 PM
  • Mark,

    I used the Fix It found here: http://support.microsoft.com/kb/2739501

    I would have to ask my IT support person for more specifics, but as I understand the scenario with my particular laptop: the laptop came preloaded with RTM build of 2013.  We used Control Panel to uninstall Office 2013, in order to install Office 2010.  I installed PowerPivot add-in and used it successfully with no issues for several months.  After attempting to install another program and encountering issues with apparent traces left behind of Office 2013 uninstall, I found the link above and used the Fix It to attempt to remove all remaining traces of Office 2013.  While it did appear to find and clean-up quite a bit left behind from the Office 2013 install, I was surprised to find an error upon entering my existing installation of PowerPivot for 2010 and attempting to type new formulas into the formula bar.

    My experience to-date is that an error message stating ""Return Argument Has an Invalid Type" appears anytime I select or modify a formula in the formula bar that would cause the intellisense to appear.  For example, when I first click into the formula bar, I do not get an error...but as soon as I type the first letter, before the intellisense appears, I get the error message.  Throughout the remainder of the formula as I type, just prior to any time when the intellisense drop-down will appear again, I get the same error message again (see details below).

    At one time, although I can't reproduce it now, the error message did state ".Net Framework" in the menu bar of the error pop-up window.  I can't get that to appear again, as it now only says "PowerPivot for Excel".  Based on this, I had attempted to see if I could repair my .NET framework install using the following tool: http://www.microsoft.com/en-us/download/details.aspx?id=30135

    I learned, however, that since I am running Windows 8 which comes with .NET 4.5 built-in to the OS, this tool does not yet cover that version for repair.

    While the overall end-result of my PowerPivot models appear to be just the same, having to click through "Continue" on the error message multiple times each time I enter a new formula is a huge drain on productivity.

    Let me know if you have any additional questions.

    David

    ********************

    System.InvalidCastException: Return argument has an invalid type.
       at System.Runtime.Remoting.Proxies.RealProxy.ValidateReturnArg(Object arg, Type paramType)
       at System.Runtime.Remoting.Proxies.RealProxy.PropagateOutParameters(IMessage msg, Object[] outArgs, Object returnValue)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at Microsoft.Office.Interop.Excel._Application.get_ActiveWorkbook()
       at Microsoft.AnalysisServices.XLHost.Addin.GeminiRibbon.currentClientWindow_InFormulaBarChanged(Object sender, EventArgs e)
       at Microsoft.AnalysisServices.Common.SandboxEditor.formulaBar_Leave(Object sender, EventArgs e)
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.Windows.Forms.ContainerControl.UpdateFocusedControl()


    David Duncan Consultant I.B.I.S., Inc.

    Tuesday, August 06, 2013 5:30 PM
  • Seems like my detailed response scared everyone away from this thread.  Too bad! :)

    David Duncan

    Wednesday, August 21, 2013 6:23 PM
  • Well, it may be January 2014, but I'm here.  I'm getting this same error issue.  However, I have Windows 7 and MS Office 2010 running PowerPivot for SQL Server 2008 R2.  The error message just started up one day and I haven't been able to get around it.  It eventually 'locks' PowerPivot and I have to manually force it to close.  As this is a company PC and PowerPivot is not technically supported, I don't have the option to reimage the machine.  After all this time, it sure would be nice if Microsoft had an answer.
    Thursday, January 23, 2014 7:42 PM