Function returning many lines

[Xyloto]

Hi,

I 've just installed ExcelDna and it work perfectly for UDF

I would like to know how to implement a function that returns many lines to show up in my excel?

Best Regards

Xyloto

[Dr DB Karron]

Dear Ismael;

Excel trys to prevent this unless you pre-define an array region on invocation. (control shift return

in the command box )

You will see this opaque syntax {=myUserDefinedFunction()}

The cury braces say the function owns a region highlighted on invocation

and can return values to that region.

I'm working on a linear/matrix function library that is free of that nonsense and

can write down to the sheet below invocation so you can drill down through a matrix

tensor function sheet to sheet.  There must be a way to manage evaluation or just

tell the user to setup an evaluation list to trigger functions in the desired order.

The theory for this limitation in Excel is to control the dependency of cells to functions

so a sheet can be evaluated by a left to right, top to bottom sweep.

Grovert, do I have this correct ?

dr. K 

______________________________________

"Dr D B Karron" <drdbk...@gmail.com>
<kar...@casi.net> skype: drdbkarron

+1 (516) 515-1474 (will find me)

+1 (917) 674-0828 (voice and cell texting, sms)

Xyloto

--
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 http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[ismail hnida]

Hi

Thanks Dr Karron.

Is that means that exceladna does not handle natively this kind of problem?

Best Regards

Ismael

2013/7/30 Dr DB Karron <drdbk...@gmail.com>

[Govert van Drimmelen]

Hi Ismael,

As Dr DB explains, Excel support something called an "Array Formula" which allows a function to return an array of values. This is a standard Excel feature, and example of a built-in function that works like this is the FREQUENCY function, which returns histogram data. To enter an array formula in Excel you select that range where the results will be written, then enter the formula and press Ctrl+Shift+Enter to store the formula as an array formula. Such a formula is displayed with curly brackets {=FREQUENCY(...)}.

Excel-DNA allows you to make functions that return arrays, just like the built-in functions. You just set you return type to be an object[,] or double[,] and fill in the 2D array in your function. When entered as an array formula, the array of values will be returned. So Excel-DNA has built-in support for array to the same extent that Excel supports these.

There are some frustrations with how Excel work in this regard. For example, you need to know in advance how large the result will be, so that you can select and enter the array formula. There are some workarounds, but this involves having the function calculation trigger a macro to do work after the calculation completes. This can be problematic, since Excel's internal dependency structures which allow it to do correct and efficient calculation, no longer reflect hoe one part of the sheet or formula might affect another. If you're just getting started with Excel-DNA and Excel programming, it might be best to avoid such complications.

Regards,

Govert

[ismail hnida]

Hi Govert,

Your response gives  me more motivations to find out a solutions for my problems :D

Actually i had a discussion with many collegues about this issue, one of them told about a complicated solutions that invoke an other thread in excel, the writng process is delegate to this thread, what do you think about this?

I made many research on the internet withoud succes actually.

Best Regards

Xyloto

2013/7/31 Govert van Drimmelen <gov...@icon.co.za>

[Govert van Drimmelen]

Hi Xyloto,

Have you tried the ArrayResizer sample in the Excel-DNA distribution?

-Govert

[Dr DB Karron]

Here are some simple studies that illustrate the difference between

MacroType functions and UDF functions.

These functions list sheets as menu commands and UDF's.

The UDF's call an anonymous Async Macro to make a

list directly below its caller location.

When called from a menu, there is no need to wrap the

deligate in an async macro.

When called from a UDF, it has to be wrapped up

as an async macro and called like it was in a manu.

Now: How can I connect the menu call into the deligate 

and just call the menu code from the UDF asynchronously ?

The UDF requires a return value, the menu can't take a parameter

or return a value.  Show me I'm wrong.

I have not addressed the clobber/no clobber issue of a UDF

writing outside the allowed array bounds of its return cell(s).

I think the safest thing is to push to a new sheet and use it for

scratch space and intermediate results, graphs and such.

Let me know if this works; it is part of a group of studies in

managing Colors, Patterns, Borders, Fonts and Foreground/Background

colors, color palettes, Cell Width and Height, Row and Column fitting 

from UDF's and Menu Commands,

and a collections of the two.

If there is enough demand I will publish this with  Grovert's demo collection,

if he wants to.

    [ExcelCommand ( Name = "PushToNewSheet" , MenuName = "Class1" , MenuText = "PushToNewSheet" )]

    public static void PushToNewSheet ( )

        {

        PushToNewSheetUDF ( "yaya" );

        }

    [ExcelFunction ( Name = "PushToNewSheetUDF" , Category = "Class1" , IsMacroType = true )]

    public static string PushToNewSheetUDF ( [ExcelArgument ( 

        Name = "New Sheet Name" , Description = "sheet to push into" )]  String NewSheet )

        {

        return PushToNewSheet ( NewSheet );

        }

    private static String PushToNewSheet ( String NewSheet )

        {

        ExcelAsyncUtil . QueueAsMacro ( delegate ( )

        {

            bool bingo = ( bool ) XlCall . Excel ( XlCall . xlcWorkbookInsert , 1 );

            if ( bingo == false )

                {

                Debug . WriteLine ( "Workbook Insert Failed" );

                return;

                }

            object [ , ] active_sheet = ( object [ , ] ) XlCall . Excel ( XlCall . xlfGetWorkbook , 3 );

            object [ , ] all_sheets = ( object [ , ] ) XlCall . Excel ( XlCall . xlfGetWorkbook , 1 );

            int new_sheet_name = SortItOut ( all_sheets , active_sheet );

            String OldName =( String ) all_sheets [ 0 , new_sheet_name ];

            String NewName = Uniquify ( all_sheets , NewSheet , 0 );

            XlCall . Excel ( XlCall . xlcWorkbookName , OldName , NewName );

            XlCall . Excel ( XlCall . xlcWorkbookSelect , NewName );

        } );

        return String . Format ( "=PushToNewSheetUDF(\"{0}\")" , NewSheet );

        }

    private static String PushToNewSheetMacro ( String NewSheet )

        {

        bool bingo = ( bool ) XlCall . Excel ( XlCall . xlcWorkbookInsert , 1 );

        if ( bingo == false )

            {

            Debug . WriteLine ( "Workbook Insert Failed" );

            return "PushToNewSheetMacro() Workbook Insert Failed";

            }

        object [ , ] active_sheet = ( object [ , ] ) XlCall . Excel ( XlCall . xlfGetWorkbook , 3 );

        object [ , ] all_sheets = ( object [ , ] ) XlCall . Excel ( XlCall . xlfGetWorkbook , 1 );

        int new_sheet_name = SortItOut ( all_sheets , active_sheet );

        String OldName =( String ) all_sheets [ 0 , new_sheet_name ];

        String NewName = Uniquify ( all_sheets , NewSheet , 0 );

        XlCall . Excel ( XlCall . xlcWorkbookName , OldName , NewName );

        XlCall . Excel ( XlCall . xlcWorkbookSelect , NewName );

        return NewName;

        }

    private static string Uniquify ( object [ , ] a , string p , int depth )

        {

        bool DebugMe = false ;

        int l = a . Length ;

        for ( int i=0 ; i < l ; i++ )

            {

            string ayh = (string)a [ 0 , i ] ;

            if ( DebugMe )

                Debug . WriteLine ( "{0}??endswith??{1}" , ayh , p );

            if ( ayh.EndsWith (  p ) )

                {

                if ( DebugMe )

                    Debug . WriteLine ( "{0}!!endswith!!{1}" , ayh , p );

                string arg = String . Format ( "{0}{1}" , p , depth );

                return Uniquify ( a , arg , depth+1 ) ;

                }

            }

        return p ;

        }

    [ExcelCommand ( Name = "SheetStudy" , MenuName = "Class1" , MenuText = "SheetStudy" )]

    public static void SheetStudy ( )

        {

        bool WorkbookStructureProtected=( bool ) XlCall . Excel ( XlCall . xlfGetWorkbook , 14 );

        bool WorkbookWindowProtected=( bool ) XlCall . Excel ( XlCall . xlfGetWorkbook , 15 );

        XlCall . Excel ( XlCall . xlcWorkbookProtect , false , false );

        object [ , ] active_sheets = (object[,])XlCall . Excel ( XlCall . xlfGetWorkbook , 3 ) ;

        for ( int k=0 ; k <  2 ; k++ )

            {

            bool bingo=( bool ) XlCall . Excel ( XlCall . xlcWorkbookInsert , 1 );

            Debug . WriteLine ( "{0}:{1}" , k , bingo );

            if(bingo==true)

                {

                object [ , ] active_sheets_new = ( object [ , ] ) XlCall . Excel ( XlCall . xlfGetWorkbook , 3 );

                object [ , ] all_sheets_new = ( object [ , ] ) XlCall . Excel ( XlCall . xlfGetWorkbook , 1 );

                int new_sheet_name = SortItOut ( all_sheets_new , active_sheets_new );

                String OldName = (String)all_sheets_new [ 0 , new_sheet_name ];

                String NewName = String.Format("yaya{0:02}", k) ;

                XlCall . Excel ( XlCall . xlcWorkbookName , OldName , NewName );

                XlCall . Excel ( XlCall . xlcWorkbookSelect,NewName ) ;

                //recover sheet id and rename

                }

            }

        }

    private static int SortItOut ( object [ , ] all , object [ , ] hot )

        {

        int eye = all . Length ;

        string ott = ( string ) hot [ 0 , 0 ] ;

        for ( int i = 0 ; i < eye ; i++ )

            {

            string awl = ( string ) all [ 0 , i ] ;

            if ( awl == ott )

                {

                return i ;

                }

            }

        return 0 ;

        }

______________________________________

"Dr D B Karron" <drdbk...@gmail.com>
<kar...@casi.net> skype: drdbkarron

+1 (516) 515-1474 (will find me)

+1 (917) 674-0828 (voice and cell texting, sms)

[ismail hnida]

Hi Govert,

Yes i already test it, but unfortunately i had a message error "You cannot change part of an array" when recalling the same UDF twice.

Any idea about this issue?

Regards

Xyloto

2013/8/12 Dr DB Karron <drdbk...@gmail.com>

[Govert van Drimmelen]

Hi Xyloto,

If your expanded array would crash into another array formula, the ArrayResizer backs off.

One could make the behaviour different, but that seemed the most sensible to me, since it's all-or-nothing when changing an array formula.

Maybe a more concrete example of which functions you put into which cells would help explain it.

Regards,

Govert

[ismail hnida]

Hi Govert,

Please find in attached files my  code and also a screen copy with errors (error in french = You cannot change part of an array)

The first time it work perfectly when i call the function "MakeArrayAnd resize". The second time i call the same function from the same cell i got the error in attached file

Could you have a look on this pb?

Thank you very much for your help

Xyloto

2013/8/13 Govert van Drimmelen <gov...@icon.co.za>

[Govert van Drimmelen]

Ah! I think you need to press Ctrl+Shift+Enter to change an array formula.

-Govert

[Dr DB Karron]

You want to outline where you want the array to go before you fire

the function with control-shift-enter.

Or stay tuned for the code I'm banging on now.

Try this; it should trim the column width after you set the value

at RC.

   private static void FitEntireCell ( ExcelReference rc )

        {

        XlCall . Excel ( XlCall . xlcColumnWidth , 0 , rc , true , 3 );

        }

______________________________________

"Dr D B Karron" <drdbk...@gmail.com>
<kar...@casi.net> skype: drdbkarron

+1 (516) 515-1474 (will find me)

+1 (917) 674-0828 (voice and cell texting, sms)