Equivalent VBA Excel code

[marcel]

Hi,

After studying the COM Excel example I was able to reassembling a tiny part of the VBA cell addressing.

[code=thinbasic]'Excel code
'for i = 1 to 20
' for n = 1 to 5
' ActiveSheet.Cells(i, n) = "Hello"
' next n
'next i

'thinBasic equivalent code
for i = 1 to 20 'Rows
for n = 1 to 5 'Column
vParam(3) = i
vParam(2) = n
vParam(1) = "Hello"
COM_Execute(pXlSheet, "Cells", %TB_DISPATCH_PROPERTYPUT, %NUMBER_OF_PARAMETERS, vParam, 0)
next
next[/code]

It is a bit long-winded but with some do-it-yourself repackaging into a function it could be something like:

[code=thinbasic]
for i = 1 to 20 'Rows
for n = 1 to 5 'Column
Cells(pXlSheet, i, n, "hello")
next
next[/code]

[ErosOlmi]

Marcel,

there are some reasons for that:

• COM module is quite new
• so far there was not so much interest in using it so for us it was not worth to dedicate too much time but as soon as there is interest, like the one you showed, we will dedicate more time
• developed functions are actually 1 to 1 connected with COM interface so nothing is hidden like in VBA. You may know better than me but the simple line ["ActiveSheet.Cells(i, n) = "Hello"] in reality is hiding a lot of complexity of the COM interface like late binding of methods and VARIANT data conversions.
  Of course from a user point of view VBA code is much better because more clear and simple to manage.

That said, we will try to go into that direction but some other steps need to be done in the main thinBasic engine.
Be sure we will try.

Ciao
Eros

[Michael Clease]

Marcel you could make a subroutine.

call it with
[code=thinbasic]Cells(pXlSheet, i, n, "hello")[/code]

Heres the subroutine

[code=thinbasic]
SUB Cells(pXlSheet AS DWORD, i AS LONG, n AS LONG , vParam1 as VARIANT)

vParam( 3 ) = i
vParam( 2 ) = n
vParam( 1 ) = vParam1
COM_EXECUTE( pXlSheet, "Cells", %TB_DISPATCH_PROPERTYPUT, %NUMBER_OF_PARAMETERS, vParam( 1 ), 0 )

END SUB
[/code]

Hope that helps

Mike

** Update using a VARIANT

[marcel]

Marcel,

there are some reasons for that:

• COM module is quite new
• so far there was not so much interest in using it so for us it was not worth to dedicate too much time but as soon as there is interest, like the one you showed, we will dedicate more time
• developed functions are actually 1 to 1 connected with COM interface so nothing is hidden like in VBA. You may know better than me but the simple line ["ActiveSheet.Cells(i, n) = "Hello"] in reality is hiding a lot of complexity of the COM interface like late binding of methods and VARIANT data conversions.
  Of course from a user point of view VBA code is much better because more clear and simple to manage.

That said, we will try to go into that direction but some other steps need to be done in the main thinBasic engine.
Be sure we will try.

Ciao
Eros

Eros,

My comment "long-winded" should not be interpreted as criticism to COM in thinBasic, in the contrary. I'm more than happy that the COM module is present in the way it is.

It took me some time to investigate how the parameters are used and must be used and compared it with Excel VBA and made an example. I thought it may be serve some other user time who want to pick up COM. My other "do-it-yourself" text is a tip that the thinBasic user can make a wrapper around it for a more compact construction. I should have written it down with more care than I did.

I know how much time it cost to get COM implemented. A nice talk about COM it is here: http://www.codeproject.com/com/com_in_c1.asp

Added -----------

@Abraxas: I was writing this part while you were posting.

[ErosOlmi]

Marcel,

I didn't intend it as negative, be sure but instead the other way round.
It is our usual behave to give more info about things are in one way or the other so I just wanted to give you and others reading our forum more info about COM module. We know our limits and our strengths
We also like critics when genuine like yours, so please, do not hesitate to tell us what you think about anything. We will be happy to listen to you and do our best to improve thinBasic and thinBasic related material.

So, I have to thank you about your code compare. Your first karma point is on the way, stupid me I didn't make before.
Ciao
Eros

[RobertoBianchi]

A step forward could be add some frequently used functions as standard API, for example the Cells() functions could be implemented internally (I mean in the COM module) like Cells(pXlSheet AS DWORD, i AS LONG, n AS LONG , vParam1 as VARIANT).
When the core will be ready these APIs could be re-used by the interpreter in order to handle the VB likes code ActiveSheet.Cells(i, n) = "Hello" or (better form I think) pXlSheet.Cells(i, n, "Hello")
What do you think?

Cheers,
Roberto

[ErosOlmi]

Roberto,

I have some idea on how to natively implement COM interface.
My idea is to add a new variable type called OBJECT, like in Power Basic. So instead of using DWORD to store COM handle we can use OBJECT type.
During the script, when a variable of type OBJECT is encountered, we can start a link between Core engine and COM module. Core engine can scan the script and when finished pass all the info to COM module.

Another idea is to include COM module into Core thinBasic engine in order to be able to directly parse and link with COM object.

In any way the final result is to be able to parse pXlSheet.Cells(i, n) = "Hello" directly without the need to allocate variants or indicate if a method or a LET/SET property. Exactly the same done by VBS engine. What do you think?

Maybe worth to open a new thread for this discussion.

Ciao
Eros

[RobertoBianchi]

Eros,

a new variable type OBJECT is absolutely the right choice.

without the need to allocate variants

Not sure, a COM calls to Office OLE it must be done using variant, so if you don't allocate a variant at this level, a conversion to variant should be done before the call to the dispatch method.
The LET and SET should be detected by the core and therefore call the appropriate API like COM_ExcelSetCells(pXlSheet AS DWORD, i AS LONG, n AS LONG , vParam1 as VARIANT) and COM_ExcelGetCells(pXlSheet AS DWORD, i AS LONG, n AS LONG , vParam1 as VARIANT), two API are necessary only if specializtion is required.

Ciao,
Roberto

[ErosOlmi]

Of course COM deals with variants. By "not allocating any variant" I mean the user has not bother with variants (unless needed) but just pass number or strings. Than engine should take care about it.

Now user has to create a variant array to pass parameters. I would like to avoid this and pass that work to the engine.
[code=thinbasic]
for i = 1 to 20 '---Rows
for n = 1 to 5 '---Column
pXlSheet.Cells(i, n) = "hello"
next
next
[/code]
In this way no need to create variant from the user point of view. Of course internally we have to do.

Another thing we have to consider is to wrap any COM interface into a more strong error checking situation because it is very easy to generate GPF in case of wrong parameter. So a TRY/CATCH of something else wrapping COM call must be considered.

Ciao
Eros

[RobertoBianchi]

Perfect.

Ciao,
Roberto