CODEWITHSUNDEEP
excelf#add-inexcel-dna
Uziel
Uziel

Reputation: 349

Functionally program Excel code

I am rewriting my Excel VBA add-in with F# and Excel-DNA. Formula1 below works. It iterates through the currently selected cells and applies trim to each cells value.

Formula2 is my failed attempt at applying Functional and F# concepts. I'm unsure how to return the range to Excel. The formula must return a unit.

Could someone help me?

Formula1 (Works):

let Trim (rng:Range) = 
    for cell in rng.Cells do
        let cel = cell :?> Range       
        if not (cel :? ExcelEmpty) then
            cel.Formula <- cel.Formula.ToString().Trim()

Formula2 (Does not work):

let Trim2 (values:obj[,]) =  
    values
    |> Seq.cast<obj>
    |> Seq.map( fun x -> x.ToString().Trim() )

Some have asked about the reason for returning a unit or the calling function. That is below.

type public MyRibbon() =
    inherit ExcelRibbon()
    override this.GetCustomUI(ribbonId) =
    "bunch of xml here"

member this.OnButtonPressed (control:IRibbonControl) =
        let app = ExcelDnaUtil.Application :?> Application
        let rng =  app.Selection :?> Range
        let rng2 =  app.Selection :?> obj
        match control.Id with
            | "AddIfError" ->  RibFuncs.RangeFuncs.AddIfError app rng
            | "Trim" ->  RibFuncs.RangeFuncs.Trim rng
                         |> ignore
            | _ ->  ()

Upvotes: 2

Views: 460

Answers (2)

kevinykuo
kevinykuo

Reputation: 4762

Try

let Trim2 (values:obj[,]) =  
    values
    |> Array2D.map(fun x-> box(x.ToString().Trim()))

Where are you getting the Range type in your "Formula 1"?

Upvotes: 1

kaefer
kaefer

Reputation: 5741

Not sure what you're trying to accomplish there. Why would a formula need trimming, as opposed to e.g. a value?

The other snag is that I've been doing late binding exclusively to avoid Interop library dependencies. Here's an implementation of the dynamic operators for late binding.

let usedRange = workSheet?UsedRange
usedRange?Formula
|> box |> function
| :? (obj[,]) as a -> a
| o -> Array2D.createBased 1 1 1 1 o    // Single cell sheet
|> Array2D.iteri (fun i j formula ->
    let cell = usedRange?Cells(i, j)
    cell?Formula <- (string formula).Trim() )

Upvotes: 1

Related Questions