[Excel Dna]... Insert a simple row

[Oscar Padilla Paco]

Greetings to all.

Excuse my very elementary questions about Excel-Dna, I have a column that contains consecutive numerical values, but a few values are not there; so I'm trying to make a function that inserts the missing intermediate values, by means of entire rows. Could someone suggest to me how to make a reference to the cell and do the row inserts, because the code I use in VS 2019 using C # .Net. The code is the following:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using ExcelDna.Integration;

using ExcelApp = Microsoft.Office.Interop.Excel;

namespace FYXLTools

{

    public class FYAddIn 

    {

        [ExcelFunction(Name = "CompleteRows", Description = "Complete rows", HelpTopic = "Nothing", Category = "FYXLFunctions")]

        public static string CompleteRows([ExcelArgument(Description = @"Current Range")] string Text)

        {

            var xlApp = (ExcelApp.Application)ExcelDnaUtil.Application;

            var xlSelection = (ExcelApp.Range)xlApp.Selection;

            xlSelection.EntireRow.Insert(); // <-- When executing the function, it indicates this line as an error 

            return xlSelection.Address; 

        }

    }

}

My frustration is that I can't even insert a simple line, I know that to finish my function I must use other structures and controls, but not being able to reference a simple cell fills me with negative energies.

Thank you very much for your time.

Oscar Padilla

[Govert van Drimmelen]

Hi Oscar,

 

Excel does not allow you to make changes to the sheet from a worksheet function call.

So when you try to manipulate the sheet with the Range.Insert() call, it fails.

 

If you change from a function to a macro (an ExcelCommand instead of an ExcelFunction, returning ‘void’) then your code will work.

I have assigned a shortcut to the macro, but you could call if from a ribbon button or the Alt+F8 run dialog too. Macros in Excel-DNA add-ins are always hidden, so you would need to types the name into the Alt+F8 dialog to run it.

 

The code I tried looks like this:

 

        [ExcelCommand(ShortCut="^R")] // Shortcut is Ctrl+Shift+R

        public static void CompleteRows()

        {

            var xlApp = (ExcelApp.Application)ExcelDnaUtil.Application;

            var xlSelection = (ExcelApp.Range)xlApp.Selection;

            xlSelection.EntireRow.Insert();

        }

 

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/CAK9YKZK9jr2OU3bD%2BLg%3DhdnsvV_pV1_dW9VUahcnZKV23o2ESg%40mail.gmail.com.

[Oscar Padilla Paco]

Thank you very much, I didn't know you had to use ExcelCommand (I never read about it). Now it works perfectly, and as you say it is hidden, the keyboard shortcut ctrl + r does not work.

Where can I learn more about this? How can I access the excel objects? How can I send a simple messagebox?

Thanks a lot,

Oscar Padilla

[Govert van Drimmelen]

Hi Oscar,

 

Ctrl+Shift+R should work.

 

Inside a macro, you can access the Excel COM object model just like you would from VBA.

The only important part is to use the Application object that you get back from ExcelDnaUtil.Application, as you did already in your code.

Here is the object model reference:

https://docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model

If you have trouble converting from VBA to C#, you’re welcome to write back.

 

You can also run the code from a Ribbon extension – I have a small video and tutorial about that on YouTube:

https://youtu.be/2oBQaQFgQow

 

To show a message box, easiest is to add a reference to System.Windows.Forms in your project, then use

MessageBox.Show(…)

 

-Govert

--

You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/CAK9YKZLTbX4q_0UVOJwiQgdggMZx0mdmqC1ffYOAoEE4DT7f6Q%40mail.gmail.com.

[Oscar Padilla Paco]

Hi Govert

Thanks for the tips for using Excel-DNA. Everything you told me has been very useful. Now I am implementing it using the Excel ribbon, following the tutorial and video. I started a new project in C #, but in its execution the Tab was not displayed, I did many things to solve including the installation and uninstallation of Office; But finally I downloaded the example you put in the tutorial and I saw that the difference is <ComVisible (True)> and that if that line is suppressed, the Tab is not displayed on the Excel ribbon, as if its default value was False. Your example is based on VB, I'm doing it now in C # because VS won't let me do it in VB (it must be something from my configuration, I'll solve that later). Please could you tell me how I do <ComVisible (True)> but in C #. I am trying to use Excel-DNA for my needs at home.

I have experience in application development in Excel VBA, using SQL Server Express with Microsoft Enterprise Library Data Access Application Block. My interest in using Excel-DNA is to use it as I did before making desktop applications using VSTO with VB.NET and Windows Forms, to discard the limited use of the VBA ListBox control and instead use the DataGridView control which is much more advanced. I suppose that with Excel-DNA I can do it much better and faster, I still don't know how to use it.

Thanks for your support.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/01cc01d6c28c%24f38862e0%24da9928a0%24%40icon.co.za.

[Oscar Padilla Paco]

Hi Govert

Sorry about the [ComVisible (true)] thing, I just saw that it goes after the Namespace section and before the class definition.

using System.Runtime.InteropServices;

using System.Windows.Forms;

using ExcelDna.Integration.CustomUI;

namespace FirstExcelDNARibbon

{

     [ComVisible (true)] // <- Here goes it

     public class XLFYRibbonTools: ExcelRibbon

     {

         public void OnInsertRowPressed (IRibbonControl control)

         {

             MessageBox.Show ("Hello from control ..." + control.Id);

         }

     }

}

Thank you

[Govert van Drimmelen]

Hi Oscar,

 

There is a C# ribbon sample here: https://github.com/Excel-DNA/Samples/tree/master/Ribbon

 

For the ComVisible, you probably have an explicit attribute like

    [assembly:ComVisible(false)]

In your Properties\AssemblyInfo.cs file.

That makes the default false for all classes.

 

You can set it to true on the class in C# like this:

 

    [ComVisible(true)]

    public class RibbonController : ExcelRibbon

 

Good luck with your add-in :-)

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/CAK9YKZ%2B0%2B6H6-pyxiXPqEuMK9V0pqHJ1uSX--nAf97Ujo72nWg%40mail.gmail.com.

[Oscar Padilla Paco]

Thanks so much.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/02f401d6c357%241244fb10%2436cef130%24%40icon.co.za.