fbpx
Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1

TOPIC:

VBA translation, C# Linq extension method and Switch speed 06 May 2021 00:17 #18336

  • ic2


  • Posts: 995
  • 1 In my Excel reader, this will also read empty rows. E.g. when I have 300 rows, select 295 rows and delete these, this will still be 300 while I would say that it is not a UsedRange
    oRange:=oWorkSheet:UsedRange
    nRowCount :=oRange:Rows.Count

    Now in this site there's a well illustrated sample of how to move to the last row:
    www.wallstreetmojo.com/vba-row-count/

    Basically it is, in VBA:

    No_Of_Rows = Range("A1").End(xlDown)

    But when when I type this in X#, End autocompletes but contrary to the picture in the website, opening a bracket does not show me the 3 Direction As XlDirection options XlToLeft,xlToRight and XlUp. When I try this nevertheless:

    ni:=oRange.End(xlDown)

    I get -with a . as well as with a : -


    Error XS0103 The name 'xlDown' does not exist in the current context
    and
    Error XS0118 'oRange' is a variable but is used like a type

    What should I do differently?

    2 I also used ILSpy to convert this method to find empty rows:

    public static bool IsDataRowEmpty(this DataRow row)
    {
    return row == null || row.ItemArray.All(i => i is DBNull);
    }
    XLSpy translates this to:

    Public Static Method IsDataRowEmpty(Self row As DataRow ) As Logic
    Return row?:ItemArray:All({i As Object => i Astype DBNull}) ?? True

    But I get this error, on the two question marks:

    XS9002 Parser: unexpected input ??

    What is wrong here? And why does ILSpy give an non working translation?

    Finally, following the discussion I had about the advantages of Switch I changed:

    Do Case
    Case aFieldType[ni]=="C"
    odb:Fieldput(ni,cValue)
    (..etc)
    ENDCASE

    to

    cFieldType:=alltrim(aFieldType[ni])
    Switch (cFieldType)
    Case "C"

    I used my stopwatch; on the same sheet with DO CASE took 30 seconds to read and with the Switch statement it took 31 seconds. So much for speed gains with new statements :lol:

    Dick

    Please Log in or Create an account to join the conversation.

    Last edit: by ic2.

    VBA translation, C# Linq extension method and Switch speed 06 May 2021 08:13 #18344

    • robert
    • robert's Avatar


  • Posts: 2222
  • Dick,

    ic2 wrote: I used my stopwatch; on the same sheet with DO CASE took 30 seconds to read and with the Switch statement it took 31 seconds. So much for speed gains with new statements :lol:


    I do not think the DO CASE or SWITCH are consuming the time. In this case it is the communication between the 2 processes that makes things slow.

    W.r.t. the other messages:

    Error XS0118 'oRange' is a variable but is used like a type

    If you change the DOT to a COLON then this error should disappear. Or you enable the new compiler option "AllowDot".

    Error XS0103 The name 'xlDown' does not exist in the current context

    xlDown is a member of the XlDirection enum in the .Net type library for Excel. So you need to prefix this with
    XlDirection.xlDown

    What is wrong here? And why does ILSpy give an non working translation?

    ILSpy is not giving the translation. That is the X# language plugin that we (Fabrice) wrote. Apparently the plugin has a mistake.
    return row == null || row.ItemArray.All(i => i is DBNull);

    If you do not like one liners (like with the => operator) then this is indeed a bad example.
    Try this:
    IF row == NULL
       RETURN TRUE
    ENDIF
    FOREACH item AS OBJECT in row:ItemArray
         IF .not. (item IS DbNull )
               RETURN FALSE
         ENDIF
    NEXT
    RETURN  TRUE

    But this should work too:
    IF row == NULL
       RETURN TRUE
    ENDIF
    RETURN row:ItemArray:All( { item => item IS DbNull })
    And when I decompile that with the latest XSharp language plugin then I see that this is translated into a one liner like this:
    RETURN row?:ItemArray:All({item AS Object => item IS DBNull}) DEFAULT TRUE

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    VBA translation, C# Linq extension method and Switch speed 06 May 2021 16:54 #18352

    • ic2


  • Posts: 995
  • Hello Robert,

    Thanks for (Again) a fast reply.

    robert wrote: I do not think the DO CASE or SWITCH are consuming the time. In this case it is the communication between the 2 processes that makes things slow.


    No doubt, but Switch instead DO CASE didn't makeit faster either. Even a bit slower it seems.

    Error XS0118 'oRange' is a variable but is used like a type

    robert wrote: If you change the DOT to a COLON then this error should disappear. Or you enable the new compiler option "AllowDot".

    xlDown is a member of the XlDirection enum in the .Net type library for Excel. So you need to prefix this with

    XlDirection.xlDown


    The second works, that's a bit the problem of solution found elsewhere which were (all) displayed without the prefix. It doesn't make sense that I expected it to work without a prefix too, I admit.

    But the first one does not work. I already wrote that I tried it with a colon and with a dot but the forum turned it into a smiley. See picture. I don't have the AllowDot option, it's 2.8 and I usually wait a few month before installing an upgrade.



    Any idea?

    robert wrote: ILSpy is not giving the translation. That is the X# language plugin that we (Fabrice) wrote. Apparently the plugin has a mistake.

    There's an X# update of ILSpy I noticed. Maybe it works all right there.

    robert wrote:

    return row == null || row.ItemArray.All(i => i is DBNull);

    If you do not like one liners (like with the => operator) then this is indeed a bad example.

    First, it's a misunderstanding that I don't like one liners. On the contrary, the less code, the better, although I also prefer to understand the code and one liners are often less clear (I only meant to say in the other thread that the =? operator is not like a big step forward).

    But second, your code does not work. The (item IS DbNull) returns false on the actual values in the DataRow Itemarray element and these show in the debugger as "". I eventually used a C# function which I call from X#, and this works:

    public bool IsDataRowEmpty(DataRow dr)
    // Checks if a DataRow of a datagrid and/or all it's columns are empty 6-5-2021
    {
    bool lEmpty=(dr == null || dr.ItemArray.All(x => string.IsNullOrEmpty(x.ToString())));
    return lEmpty;
    }


    Dick
    Attachments:

    Please Log in or Create an account to join the conversation.

    Last edit: by ic2.

    VBA translation, C# Linq extension method and Switch speed 06 May 2021 17:23 #18354

    • robert
    • robert's Avatar


  • Posts: 2222
  • ic2 wrote: Hello Robert,

    Error XS0118 'oRange' is a variable but is used like a type

    robert wrote: If you change the DOT to a COLON then this error should disappear. Or you enable the new compiler option "AllowDot".

    xlDown is a member of the XlDirection enum in the .Net type library for Excel. So you need to prefix this with

    XlDirection.xlDown


    The second works, that's a bit the problem of solution found elsewhere which were (all) displayed without the prefix. It doesn't make sense that I expected it to work without a prefix too, I admit.

    But the first one does not work. I already wrote that I tried it with a colon and with a dot but the forum turned it into a smiley. See picture. I don't have the AllowDot option, it's 2.8 and I usually wait a few month before installing an upgrade.



    Any idea?


    In the error message you can see that End is followed by a SQUARE bracket. This means that this is an (indexed) property. So the code should be:
    oRange:End[ XlDirection.xlDown ]

    Maybe we need to update the help file to explain that a "non-invocable member" is a field (instance variable) or a property ?

    Robert
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    Last edit: by robert.

    VBA translation, C# Linq extension method and Switch speed 06 May 2021 23:16 #18359

    • ic2


  • Posts: 995
  • Hello Robert,

    Maybe we need to update the help file to explain that a "non-invocable member" is a field (instance variable) or a property ?


    Indeed!
    I'll add it to my documentation so it will be included in the help text I promised to send you. Soon I hope!

    Dick

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1