Functionally program Excel code

Question

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
            | _ ->  ()

These two statements contradict each other: how to return the range to Excel and the formula must return a unit — John Palmer
– John Palmer, Commented Oct 30, 2013 at 5:07
FYI, in your working code, the formula value you create with let formula = ... isn't actually used. If you add --warnon:1182 to the Other Flags setting in the Build tab of your project properties, the F# compiler will warn you about unused values (which is extremely useful for avoiding small mistakes). — Jack P.
– Jack P., Commented Oct 30, 2013 at 12:34
1. John- The first formula works and returns a unit. I call this routine from a main routine with a match statement wherein it expects a unit returned. I know the second formula doesn't make sense, but that's what I'm asking for help with! :) 2. Jack- good catch on that line. I removed it. :) — Uziel
– Uziel, Commented Oct 31, 2013 at 4:37

kaefer · Accepted Answer · 2013-10-30 08:35:53Z

1

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() )

answered Oct 30, 2013 at 8:35

kaefer

5,7511 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Uziel Over a year ago

We have systems at work that returns data with spaces on either side of the values. I select the range, click the 'trim' macro, and it iterates through the cells trimming the unnecessary spaces. I'll have to look at your suggestion further this weekend. It's a bit advanced and I must decipher it... Sorry, I'm a novice.

kaefer Over a year ago

Ah, thanks for the explanation. You do have a perfectly fine imperative solution. If you want more functional, look into the functions of the Array2D module. But it will get you only so far, since I think you will need to set the property of each cell individually in any case.

kaefer Over a year ago

@pearpies has it. You could convert the formulas to strings by omitting the box, changing the signature from obj[,] -> obj[,] to obj[,] -> string[,].

kevinykuo · Accepted Answer · 2013-10-30 23:35:20Z

1

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"?

answered Oct 30, 2013 at 23:35

kevinykuo

4,8125 gold badges26 silver badges31 bronze badges

Collectives™ on Stack Overflow

Functionally program Excel code

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related