RE: I wan't put a button on a sheet in excel. Then I want to call some F# when the button is pressed. Any ideas?

[Stuart Davies]

Hi,  

I want to put a button on a sheet in excel. Then I want to call some F# library when the button is pressed. 

I have created a F# dna project and I am calling functions.

How can I get the button to call some FSharp code? Does anyone have some example F# code?

Stuart

[Govert van Drimmelen]

Hi Stuart,

You can try this:

* In Visual Studio, create a new F# Library project.

* In the project, open the Package Manager Console, and type do "Install-Package Excel-DNA"

* In the Library1.fs file, put the following code:

    module fsExcelSample

    open ExcelDna.Integration

    let sayhello () = "Hello from F#"

    [<ExcelCommand(MenuName="&FSharp Macros", MenuText="&Put some Values")>]

    let putStuff () = 

        let refs = ExcelReference(0,0)

        let v = System.DateTime.Now.ToString()

        refs.SetValue(v) |> ignore

        let refs2 = ExcelReference(1,1)

        let v2 = 42

        refs2.SetValue(v2) |> ignore

* In the project Properties page, set up the Debug information:

  - Start external program: C:\Program Files (x86)\Microsoft Office\Office15\Excel.EXE

  - Command-line arguments: ...insert the full path to the -AddIn.xll in the bin\Debug directory...

* Press F5 to compile and run Excel. You should see no error message.

* Check that the add-in works: 

  - Try the formula =sayHello()

  - Check that the macro got added under the Add-Ins tab.

* Now insert a button on your sheet. 

  - The Excel Developer tab must be visible - go to File->Options and make it visible if it isn't already.

  - Select "Developer, Insert -> Button (Form Control)" and drag out a rectangle to add a button to the sheet.

  - When you have to assign a macro, type in the macro name "putStuff" (the "New" button should change to "Edit" if you have it right, though pressing it will just give an error).

  - Check that pressing your button does work and pastes the data as expected.

Let us know whether you get it to work ...

Regards,

Govert