VBA translation, C# Linq extension method and Switch speed

[ic2]

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:
https://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

Dick

[robert]

Dick,

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

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

Code: Select all

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.

Code: Select all

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:

Code: Select all

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:

Code: Select all

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:

Code: Select all

RETURN row?:ItemArray:All({item AS Object => item IS DBNull}) DEFAULT TRUE

Robert

[ic2]

Hello Robert,

Thanks for (Again) a fast reply.

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

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

Code: Select all

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.

oRangeEnd.jpg (36.91 KiB) Viewed 417 times

Any idea?

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.

Code: Select all

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

[robert]

Hello Robert,

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

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

Code: Select all

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.

oRangeEnd.jpg

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:

Code: Select all

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

[ic2]

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