COM behavior difference in Excel

ruby

hi, I just found some strange behavior of COM object.

► _VERSION
"LuaRT 2.1.0"

excel = sys.COM('Excel.Application', true)

excel.activeSheet:Range("a1:c3").Offset(1, 0).Address

COM error: unknown error

excel.activeSheet:Range("a1:c3").Offset(1).Address
"$A$1"

excel.activeSheet:Range("a1:c3").Offset(1, 1).Address
"$A$1"

excel.activeSheet:Range("a1:c3").Offset(1, nil).Address
"$A$1"

► excel.activeSheet:Range("a1:c3").Offset(2, 1).Address
"$A$2"

so parameter of Offset can not be zero, but can be nil. and result is always single cell

but in office VBA I got this:
Debug.Print 0, ActiveSheet.Range("a1:c3").Offset(1, 0).Address
Debug.Print ActiveSheet.Range("a1:c3").Offset(1).Address
Debug.Print 1, ActiveSheet.Range("a1:c3").Offset(1, 1).Address
Debug.Print 2, ActiveSheet.Range("a1:c3").Offset(2, 1).Address

0 $A$2:$C$4
$A$2:$C$4
1 $B$2:$D$4
2 $B$3:$D$5

so parameter of Offset can be zero (default), and result is offseted area

[Login to see the link]

Samir

Hi ruby,

Thank you for the report I will check this !

Samir

Well, after some inverstigations, you can thank Microsoft and this silly COM implementation.
In fact COM itself does not distinguish “property” vs “method” and it's complex for LuaRT to find if a property expects arguments or not.

When you use :

excel.activeSheet:Range("a1:c3").Offset(1, 1).Address

You call the COM property Address without any arguments, so only the row is shown.
The offset property is in fact called without arguments, so the (1, 1) applies to the returned range which is $A$1

It will be complex to fix that but I will see what can be done !

Samir

Ok I have found a workaround. It will be available in next LuaRT release 👍🏼