Using ExcelDNA to run class

Feb 8, 2013 at 7:30 AM
I am having a tough time figuring out problem to my solution.I am using ExcelDNa from CodePlex downloaded everything.referred to the samples
I hv created a new class library project in VS 2010 and my code is as follows
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ExcelDna.Integration;
using Excel = Microsoft.Office.Interop.Excel;



namespace ClassLibrary1
{
public class Class1
{


    [ExcelFunction(Description = "My first Excel-DNA function")]

    public static string MyFirstFunction(string name)
    {

        return "Hello " + name;

    }

    public static void  CreateArray()
    {
        dynamic app = ExcelDnaUtil.Application;
        dynamic wb = app.ActiveWorkbook;
        dynamic ws = app.ActiveSheet;
        ws.Name = "New name";
        dynamic ws1 = app.ActiveWorkbook.Worksheets("New name");


        dynamic range = ws1.Range("A1:B2");
        range.Value = new object[,] { { 1, "Hello" }, { 2, "Goodbye" } };
        ws1.Cells(2, 5).Value = DateTime.Now;


    }

 }
}  

MyFirstFunction runs fine in excel sheet but I cannot figure out the way to run CreateArray


Please Help

Thank you all
Coordinator
Feb 8, 2013 at 8:37 AM
Hi Rohit,

There are a couple of ways to run a macro like your CreateArray:
  • Press Alt+F8 to display the 'Macro' dialog, then type in your macro name - the 'Run' button should light up and allow you to run the macro. (Excel makes all macros defined in .xll add-in hidden, so the name is not listed in the dialog box.)
  • Add an attribute to your code to add a menu button for your macro:
    [ExcelCommand(MenuName="My Macros", MenuText="Run CreateArray")] 
    public static void CreateArray() {...} 
(Under Excel 2007 and later, this will add the menu under an Add-Ins tab on the ribbon.)
  • Add a shortcut key for your command - now pressing Ctrl+m should run your macro:
    [ExcelCommand(ShortCut="^m")] 
    public static void CreateArray() {...} 
  • Run your macro from VBA by calling
    Application.Run("CreateArray") 
  • Add a custom ribbon or command bar - see the Excel-DNA samples in Distribution\Samples\Ribbon.
Regards,
Govert
Jan 1, 2014 at 3:51 AM
Hi Govert,

Do you have any guidance regarding running macros from F#? The F# compiler states that the "ExcelCommand" attribute is not valid on class members. Specifically, the error states "This attribute is not valid for use on this language element."

Example code is here:
namespace TestFSExcelAddIn

open ExcelDna.Integration
open System.Windows.Forms

type TestClass() = class
    [<ExcelCommand(MenuName="Test Add-In", MenuText="Say Hello World")>]
    static member sayHelloWorld2 = MessageBox.Show("Say 'Hello World'")
end
Best,

Tom
Coordinator
Jan 1, 2014 at 7:00 AM
Hi Tom,

I think your F# code is defining a property called sayHelloWorld2. For a method you would need a parameter-list, perhaps empty. Could you try this:
type TestClass() = class
    [<ExcelCommand(MenuName="Test Add-In", MenuText="Say Hello World")>]
    static member sayHelloWorld2 () = MessageBox.Show("Say 'Hello World'")
end
-Govert