Excel 4 Macro and QueueAsMacro

[Lubos]

Hello,

I am kind of lost in all the async examples I found and can´t figure following out. 

Tagret is to create a form that is called from ribbon button. The form accepts separator and range via refedit control. Because of all the trouble with refedits I tried to go with DIALOG.BOX which seems nice but then it turned out it cannot be called from ribbon. 

Now I have this definition of array for dialog:

let dialog : obj[,] =

        array2D [

            [null; null; null; 283; 83; "Split text in cells"; 2];

            [5; 7; 7; 150; null; "Separator:"; null];

            [6; 125; 5; 150; null; null; null];

            [5; 7; 30; 150; null; "Column range: "; null]; 

            [10; 125; 28; 150; null; null; null];

            [1; 73; 53; 100; 23; "OK"; null];

            [2; 178; 53; 100; 23; "Cancel"; null];

            ]

And macro definition for testing purposes. I found here that queueAsMacro can be used.

let showDlg() = 

    ExcelAsyncUtil.QueueAsMacro(fun _ -> XlCall.Excel(XlCall.xlfDialogBox, Functions.dialog) |> ignore);

The form shows itself as expected but now I don´t know how to get my hands on the input array - its copy with user input should be returned from DIALOG.BOX. 

Do I need TaskCompletionSource or which object to get the array back? Simple example would be great.

Lubos

[Govert van Drimmelen]

Hi Lubos,

 

I think your approach with ExcelAsyncUtil.QueueAsMacro and using DIALOG.BOX is a good one.

 

You won’t be able to get the answer back from the ExcelAsyncUtil.QueueAsMacro call – that execution context is long gone by the time the dialog is shown.

 

I’m expecting the XlCall.Excel call to return either a number (1 since you have only one non-cancel button) or the boolean false (if cancelled). Then if it’s a number, the array should have been modified too. You’ll probably do the checking and initial processing inside the macro context. So more like this:

 

ExcelAsyncUtil.QueueAsMacro(fun _ ->

let result = XlCall.Excel(XlCall.xlfDialogBox, Functions.dialog)

// check result type here and whether ‘dialog’ array has been updated

// either finish processing here, or use another mechanism to schedule further work.

)

 

Write again if you don’t come right.

 

-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 post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Govert van Drimmelen]

Sorry – I think I mistook the documentation at a first reading.

If OK is pressed, the modified array will be returned from the xlfDialogBox call:

 

The dialog box definition table can be an array. If dialog_ref is an array instead of a reference, DIALOG.BOX returns a modified copy of that array, along with the results of the dialog box in the seventh column. (The first item in the seventh column is the position number of the chosen button or >of a triggered item.) This is useful if you want to preserve the original dialog box definition table since DIALOG.BOX does not modify the original array argument. If you cancel the dialog box, or if a dialog box error occurs, DIALOG.BOX returns FALSE instead of an array.

 

 

Also, it wasn’t clear from your question what you are doing now, but ExcelAsyncUtil.QueueAsMacro with the rest of the code should be safe to call from a ribbon handler, as a way to transition to a context where the C API is available.

[Lubos]

Hi, it's ok, I have the Excel4 reference.

All I needed was the idea, that all the work must be done in macro and then start the macro in "fire and forget manner" via ExcelAsyncUtil.QueueAsMacro. It is working now. Thank you very much.

I attach the sample. May be you can tell me if there is a better way to go from R1C1 address to A1 style? Returned array provides refedit address in R1C1 style. My current approach fails for multi area ranges. Not that it matters here but could be useful elsewhere..

Lubos

module SplitString2

open Microsoft.Office.Interop.Excel

open NLog

open System

open ExcelDna.Integration

module Seq =

    let ofCells(rng:Range) =

            seq { for cl in rng.Cells -> cl :?> Range}

module private Functions = 

    let app = ExcelDnaUtil.Application :?> Application

    let excelErrorMap = 

        Map [

            "-2146826288", ExcelError.ExcelErrorNull;

            "-2146826281", ExcelError.ExcelErrorDiv0;

            "-2146826265", ExcelError.ExcelErrorRef;

            "-2146826259", ExcelError.ExcelErrorName;

            "-2146826252", ExcelError.ExcelErrorNum;

            "-2146826246", ExcelError.ExcelErrorNA;

            "-2146826273", ExcelError.ExcelErrorValue

            ]

    let tryIntersect (rng1:Range) (rng2:Range) =

        let rng = rng1.Application.Intersect(rng1,rng2)

        match rng with

        | null -> None

        | _ -> Some rng

    // ==============================================

    // Define dialog for DIALOG.BOX

    // ==============================================    

    (*

    row control definition:

    -----------------------

    Item number 1

    Horizontal position 2

    Vertical position 3

    Item width 4

    Item height 5

    Text 6

    Initial value or result 7

    control types:

    --------------

    Default OK button 1

    Cancel button 2

    OK button 3

    Default Cancel button 4

    Static text 5

    Text edit box 6

    Integer edit box 7

    Number edit box 8

    Formula edit box 9

    Reference edit box 10

    Option button group 11

    Option button 12

    Check box 13

    Group box 14

    List box 15

    Linked list box 16

    Icons 17

    Linked file list box (Windows only) 18

    Linked drive and directory box (Windows only) 19

    Directory text box 20

    Drop-down list box 21

    Drop-down combination edit/list box 22

    Picture button 23

    Help button 24

    *)

    let dialog : obj[,] =

        array2D [

            [null; null; null; 283; 83; "Split cell string"; 2];

            [5; 7; 7; 150; null; "Separator:"; null];

            [6; 125; 5; 150; null; null; ","]; //7th position is separator

            [5; 7; 30; 150; null; "Column: "; null]; 

            [10; 125; 28; 150; null; null; null]; //7th position is range?

            [1; 73; 53; 100; 23; "OK"; null];

            [2; 178; 53; 100; 23; "Cancel"; null];

            ]

    // ==============================================

    // Extract parameters

    // ==============================================    

    let tryRngFromR1C1 str =    

        if str <> String.Empty then

            let a1 = app.ConvertFormula(str, XlReferenceStyle.xlR1C1, XlReferenceStyle.xlA1)

            app.Range(a1)

            |> Some

        else

            None

    let trySep (sep:obj) =

        match sep with

        | :? ExcelEmpty -> None  

        | o -> o.ToString() |> Some

        

    // ==============================================

    // Splitting

    // ============================================== 

    

    let splitStr (sep:string) (cl:Range) =

        let str = cl.Value2.ToString()

        let isXlErr = Map.exists (fun k _ -> k = str) excelErrorMap

        if isXlErr then

            cl.Offset(0,1).Value2 <- cl.Value2

        else

            let arr = str.Split([|sep|], StringSplitOptions.None)

            cl.Offset(0,1).Resize(1,arr.Length).Value2 <- arr

            

    let splitCells sep (rng:Range) =

        let split = sep |> splitStr

        let wsh = rng.Parent :?> Worksheet

        let rCol = rng.Columns.[1] :?> Range

        let col = tryIntersect rCol wsh.UsedRange 

                

        col

        |> Option.map (

            fun r -> 

                r

                |> Seq.ofCells

                |> Seq.iter split)

    let splitCol (arr: obj[,]) =

        let sep = arr.[2,6] |> trySep

        let rng = arr.[4,6].ToString() |> tryRngFromR1C1

        Option.map2 splitCells sep rng |> ignore

   

module Setup =    

    open Functions    

    open NLog

    open MyTypes

    let private lg = LogManager.GetCurrentClassLogger()    

        

    let private split() =

        try

            let res =XlCall.Excel(XlCall.xlfDialogBox, Functions.dialog)

            match res with

            | :? bool as r -> ()

            | :? (obj[,]) as a -> a |> splitCol

            | _ -> ()

        with

            | ex -> ex |> ignore

    let private tryBlock f =

        try

            ExcelAsyncUtil.QueueAsMacro(fun _ -> f())

        with

            | ex -> ex |> ignore

    let splitStrings() = split |> tryBlock

    

 

 

Dne středa 1. srpna 2018 13:33:50 UTC+2 Govert van Drimmelen napsal(a):