Getting Selection.Address equivalent...

Oct 25, 2011 at 7:11 AM
Edited Oct 25, 2011 at 7:13 AM

Hi all,

I have begun converting a VBA Excel add-in to C#, however, as dumb as it may sound, I seem to be having some issues getting the currently selected Range of cells in C#.

What I am effectively trying to do is take what range the user currently has selected when they hit the add-in button, and jam it in a string for later use, I have been using the following code, which compiles fine, but causes Excel to throw an error and shutdown/restart when the button is clicked. (I did this using selection.address in VBA)

Any help would be greatly appreciated!

            //// Get a reference to the current selection
            ExcelReference selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
            ////Get the value of the selection
            Object objSelectionContents = selection.GetValue();

--Tellu
Coordinator
Oct 25, 2011 at 8:43 AM

Hi Tellu,

Are you calling this from a Ribbon button?

  • If not, it works fine for me from a CommandBar button and you should post some more details of how your code is called.
  • If so, there is a small bump to overcome...

When using the XlCall functions (and the ExcelReference type) you are using the Excel C API. Calling the C API from a Ribbon handler directly does not work. You have some options:

  1. You can change your Ribbon handler to use the onAction='RunTagMacro' Tag='MyMacro', and then MyMacro will be running in a context where the C API is safe to call.
  2. You can call the macro with C API call directly from your Ribbon handler, via Application.Run (though late-bound from C# this is a bit tricky.

I would recommend you use the COM API for macros that you invoke from the Ribbon handlers.

  • This is easiest from VB.NET this is easiest, since you can call everything late-bound and basically copy-and-paste from your VBA code.
  • From C# you can also call the COM API late-bound if you are targeting .NET 4. For this you use the 'dynamic' type. There is a sample of this in Excel-DNA Distribution\Samples\CSharp4Dynamic.dna.
  • Otherwise you need an interop assembly. There are some options:
  1.  
    1. My recommendation is to try the NetOffice interop assemblies (http://netoffice.codeplex.com). These are version-independent, well supported and continuously being improved.
    2. You can also use the Excel version-specific Primary Interop Assemblies from Microsoft. Typically you'd use the version corresponding to the oldest version of Excel you want to target. You also need to redistribute or install with your add-in.

I'll investigate the Excel crashing issue, but I think this is one place where I can really inject an exception handler so it might not be so easy.

Please write back if you need more details or help on any of the above points.

Regards,

Govert

Oct 25, 2011 at 10:23 AM

Hi Govert,

Thanks for the speedy response! I am calling using this code from a Toolbar button which I have programatically added in the application, see below

        private static string showForm()
        {
            Form frm = new Form();
            //// Get a reference to the current selection
            ExcelReference selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
            ////Get the value of the selection
            Object objSelectionContents = selection.GetValue();
            frm.Show();

            return "ok";
        }

This function is called when the toolbar button is clicked, as I am looking to insert the current selection (selection.address) into a Label on the form (after putting it into a string etc).
This is being done in Excel 2003, and I've tried placing the code in the Form_Load method, but I still get the "Encountered an unexpected error, send error report....etc etc".
I'm not particularly sure this error is an ExcelDNA issue, I was just curious if there was a simple and easy to implement method to pull in the Cell Range which is selected when the toolbar button is clicked

Thanks!
--Tellu

Coordinator
Oct 25, 2011 at 12:27 PM

Hi Tellu,

Well - here's a little add--in that has a button displaying the contents of the selection. If you remove the MenuXXX parts, you could call it from a toolbar button.

<DnaLibrary Language="C#">
<Reference Name="System.Windows.Forms" />
<![CDATA[
using System;
using System.Windows.Forms;
using ExcelDna.Integration;

public class MyAddIn
{
  [ExcelCommand(MenuName="Selection Test AddIn", MenuText="Show Selection")]
  public static void ShowSelection()
  {
    try
    {
      // Get a reference to the current selection 
      // (fails if no workbook open - xlfSelection returns ExcelError in that case)
      ExcelReference selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
      //Get the value of the selection
      Object objSelectionContents = selection.GetValue();
      MessageBox.Show("OK: " + objSelectionContents.ToString());
    }
    catch (Exception e)
    {
      // For example, when no workbook is open
      MessageBox.Show("Error: " + e.ToString());
    }
  }
}
]]>
</DnaLibrary>

To try this, just paste into a text file called Test.dna, and add a copy of ExcelDna.xll renamed to Test.xll.

Even without the exception handler, this code does not crash my Excel in the case where the cast the ExcelReference fail when no workbook is open. So I'm not sure what the context of your call is that makes your function able to crash Excel. Are you perhaps marking your macro as IsExceptionSafe=true, in which case the expected behaviour is that any unhandled exception will crash the process.

Regards,

Govert

Oct 25, 2011 at 2:12 PM

Aha, I will give this a go Govert, thanks for the reply!

I am constructing my add in through a DLL just for reference.

Thanks

 

--Tellu

Oct 25, 2011 at 2:28 PM
Edited Oct 25, 2011 at 2:40 PM

This is really bugging me now! I copied your function across to my project fine, no VS2010 errors, I rebuild the DLL and the Excel Add-in updates...

Again, I hit the button and the inevitable 'Excel has encountered a problem and needs to close....send error report/dont send', When I hit the debug button on this prompt I get the following error message

http://imageshack.us/photo/my-images/27/errorlq.png/

I tried to then implement your add-in standalone, which returns me the following

http://imageshack.us/photo/my-images/194/govertsaddin.png/

All I want is the selected range ie. "C6:C10" in a string, so I can use it later, unsure if your add-in should be doing that...

Really can't figure out what's causing this, Thanks for your help thus far though Govert!

--Tellu

Coordinator
Oct 26, 2011 at 8:48 AM

Hi Tellu,

The second picture you post is exactly what I expect. You have selected a few cells, so the content of those cells returned from GetValue() is an array of objects. You can inspect the array further to see the contents of each cell, which looks like it will be ExcelEmpty in your case.

If you want the address instead of the value of the selection, you can call xlfReftext with the ExcelReference.

This version shows both the selection address and the contents:

<DnaLibrary Language="C#">
<Reference Name="System.Windows.Forms" />
<![CDATA[
using System;
using System.Windows.Forms;
using ExcelDna.Integration;

public class MyAddIn
{
  [ExcelCommand(MenuName="Selection Test AddIn", MenuText="Show Selection")]
  public static void ShowSelection()
  {
    try
    {
      // Get a reference to the current selection 
      // (fails if no workbook open - xlfSelection returns ExcelError in that case)
      ExcelReference selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
      //Get the value of the selection
      object selectionContents = selection.GetValue();
      string selectionAddress = (string)XlCall.Excel(XlCall.xlfReftext, selection, true);
      MessageBox.Show("OK!\r\nSelection Address: " + selectionAddress + "\r\nSelection Content: " + selectionContents.ToString());
    }
    catch (Exception e)
    {
      // For example, when no workbook is open
      MessageBox.Show("Error!\r\n" + e.ToString());
    }
  }
}
]]>
</DnaLibrary>

I'm really not sure why you get the error in your library. The API behaviour should be exactly the same whether it is dynamically compiled from a .dna file or whether it is precompiled into a .dll. It might be that you are trying to do this from a button on a modal form or something?

Otherwise you might start with a .dll that only has the above code in, check that it work, and then grow the working library until you see what the breaking change is.

Regards,

Govert

Oct 26, 2011 at 9:07 AM

I have since discovered that the use of 'XICall' anywhere crashes Excel, not this specifically.  

Coordinator
Oct 26, 2011 at 9:59 AM
Edited Oct 26, 2011 at 10:07 AM

It's strange, since the .dna file that works just compiles a library on the fly and loads it the same way your library is loaded.

If you have a copy of ExcelDna.Integration.dll in your output directory you should delete it and check again. Where you reference the ExcelDna.Integration.dll in your project you can set the property Copy Local: False - this prevents the rebuild from making a copy to your output directory. You never need to distribute this file, there is a packed copy inside the .xll file.

Otherwise it is very unusual that the add-in loads without errors but crashes when running.

Can you confirm that a simple .dll that contains only the code above displays the menu correctly, but crashes your Excel when you press the button?

-Govert

Oct 26, 2011 at 10:06 AM
Edited Oct 26, 2011 at 11:37 AM

Aha,

I ran the add-in on a virtual machine which uses Windows 7 and Excel 2010, instead of Excel crashing when hitting that function, I managed to catch the exception and got the below error message:

 

Maybe you recognise this? It seems that XlCall is causing me problems in the version of Excel my add-in needs to function in (2003). As well as throwing an error in 2010

Thanks

 

**Edit, I also tried what you recommended above in Excel 2003, and the Excel crash still persists**

Coordinator
Oct 26, 2011 at 10:27 AM
Edited Oct 26, 2011 at 10:30 AM

Hi Tellu,

Are you calling this from a modal form or from a thread that is not the main Excel thread?

That won't work.

-Govert

Oct 26, 2011 at 10:34 AM

Hi Govert,

The form I am using isn't Modal, and I am not calling it from the form,  I'm calling it from a toolbar button  which I added

--Tellu

Coordinator
Oct 26, 2011 at 10:44 AM

Ah - OK, I was just worried because it is called from the KPIRegistryForm.ShowSelection() method.

  1. Can you confirm that if you run the <DnaLibrary ....> code I pasted above directly from a .dna file (with matching .xll) that it works fine from the menu, and does what you expect?
  2. Can you make a new library project that contains nothing but the code class, and put that together as an add-in, and check whether it works?

-Govert

Oct 26, 2011 at 10:59 AM

Govert,

1. If I run the <DnaLibrary ...> code, it works as expected, the results are copied to the MessageBox which is displayed.

2. It also works when I place it in a seperate library by itself....

Once I can get Excel to stop crashing on XlCall, I can pretty much finish my project! So I've been on the edge of my seat for the past couple of days! :)

Coordinator
Oct 26, 2011 at 11:09 AM
Edited Oct 26, 2011 at 11:09 AM

OK, so now you have a library where the XlCall calls work. Now you can add your own code into the working library bit by bit, and see what is the change that makes it stop working.

-Govert

Oct 26, 2011 at 11:40 AM

I'm pretty sure it's because I was trying to call it from a Toolbar with a button that I had added and this was causing problems, is it possible/is there an example of someone specifying a toolbar/button in the [ExcelCommand(...)] code?

No problems if not, I can just workaround!

Thanks for your help Govert, much appreciated!

Coordinator
Oct 26, 2011 at 12:46 PM

In my Excel 2007, once I have the ShowSelection() macro working from the menu I can also add it to the Quick Access Toolbar as a button, and it works fine like that.

How are you assigning the macro to your Toolbar button? If you are handling some event, you will have to invoke your macro with a call to Application.Run("ShowSelection").

To call this late-bound from C# you'd have something like:

object app = ExcelDnaUtil.Application; 
app.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, app, new object[]{"ShowSelection"});

So I think if you can explain a bit more about your toolbar code, that's should make thing more clear.

-Govert

Oct 26, 2011 at 2:48 PM

Govert,

After playing around for a good few hours I finally have it functional and on the brink of completion! So thanks for your help!

One more question....(Last one I promise!), I'm pulling values from a cell in date format '01-Jan-11' - '01/01/2011' and get value returns a double, I specifically need the actual cell date value, is this possible? (Converting the double back to a date breaks something else..that isn't a date)

Thanks!

 

--Tellu

Oct 26, 2011 at 3:11 PM

Everything is a double internally and the api returns a double regardless of the cell formatting. I think one of the examples comes with a conversion – though at this minute I can’t find it.

Ian

Ian Murphy

San Agustinalde, 1 esc.dcha. 6º dcha.,48200,Durango,Bizkaia

ian@integra-xp.com

946 21 52 65

www.integra-xp.com

946 20 00 41


En cumplimiento de la Ley Orgánica 15/1999, de Protección de Datos de Carácter Personal, se le informa que sus datos personales, facilitados por Ud., están incluidos en un fichero con finalidad de gestionar la relación comercial que nos une, así como de informarles de los productos o servicios de nuestra empresa, por correo, fax o correo electrónico, que pudiera serle de interés, cuyo responsable es INTEGRA EXPERIENCE, S.L. - SAN AGUSTINALDE, 1 ESC.DCHA. 6º DCHA, 48200, DURANGO, BIZKAIA. Podrá ejercitar sus derechos de acceso, rectificación, cancelación u oposición remitiendo una solicitud por escrito a la dirección anteriormente citada, acompañando un documento para su identificación.

De: Telluria [email removed]
Enviado el: miércoles, 26 de octubre de 2011 16:49
Para: Ian Murphy
Asunto: Re: Getting Selection.Address equivalent... [exceldna:277081]

From: Telluria

Govert,

After playing around for a good few hours I finally have it functional and on the brink of completion! So thanks for your help!

One more question....(Last one I promise!), I'm pulling values from a cell in date format '01-Jan-11' - '01/01/2011' and get value returns a double, I specifically need the actual cell date value, is this possible? (Converting the double back to a date breaks something else..that isn't a date)

Thanks!

--Tellu

Coordinator
Oct 26, 2011 at 3:57 PM
You can convert by
DateTime theDate = DateTime.FromOADate(theValue);

-Govert
Coordinator
Oct 26, 2011 at 4:02 PM
Edited Oct 27, 2011 at 8:49 AM
For checking whether a cell actually has a date in, this thread has all I know: http://groups.google.com/group/exceldna/browse_thread/thread/d1bfecc4f2436d7d
-Govert
Oct 27, 2011 at 8:47 AM

Come to some 'sort' of solution.

Cheers for the help guys, much appreciated.

--Tellu

Coordinator
Oct 27, 2011 at 8:52 AM

Hi Tellu,

I'm glad it's working now.

I wonder if you could explain a bit about what caused the initial problems you had, causing the C API calls to crash Excel...? And then what changes you made to fix it.

Thanks,

Govert

Oct 27, 2011 at 10:52 AM
Edited Oct 27, 2011 at 10:54 AM

Hi,

I'm having a problem somewhat similar to this, I can't seem to use XlCall from a form button without it crashing, I am trying to retrieve the current workbook object but this crashes Excel in the way Telluria described.

Thanks