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.


'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

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



for i = 1 to 20 'Rows
for n = 1 to 5 'Column
Cells(pXlSheet, i, n, "hello")
next
next

[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

Cells(pXlSheet, i, n, "hello")

Heres the subroutine



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


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 :D 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.


for i = 1 to 20 '---Rows
for n = 1 to 5 '---Column
pXlSheet.Cells(i, n) = "hello"
next
next

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

[marcel]

Hello Eros and Roberto,

I followed your discussion and imho I would not implement Office dependent issues such as COM_ExcelSetCells(). COM, yes but if the user knows the object model of the programs such as Word or Excel the user must reference the right object, method or property. If you use COM_ExcelSetCells() strategie you must implement the whole object model.

I would create a more abstract way of calling the object made available via COM, just as you do now.

I do not know how thinBasic is build internally but if you can use a dotted like construction without making it an object oriented language would be a bennefit.

Just like


dim pWdAppl as P
dim pWdDocs as P
dim pWActDoc as P

pWdAppl = ole.create("word.application")
pWdDocs = pWdAppl.documents
pWdActDoc = pWdDocs.Add()
pWdActDoc.name = "mydoctwo"

In this example the names of the objects and properties are exact the same as used in the COM object that is called. It doesn't have to be part of thinbasic. You can map them 1:1 to the COM object. If you make a mistake the property or object is not found. Methods can be handled as functions() such as the Add() method. Properties just as they are and handle collections like array's.


pWdActDoc = pWDocs(2)

It is just an idea.

[ErosOlmi]

Marcel,

your suggestion is exactly what we would like to implement. We do not want to rebuild any COM model but let thinBasic engine understand what user wants using a dot notation. I suggested to have a new variable type called OBJCT because the first element in the dotted notation must be a pointer to a COM interface. Having a DWORD to store such a pointer will not let engine understand that script is dealing with a COM interface already referenced.

Currently the biggest problem is that COM module is a separated module developed in C while thinBasic core engine is written is Power Basic so we need to understand what is better to do.

Ciao
Eros

[marcel]

Hello Eros,

That is indeed a difficult decision. I know nothing about PowerBasic but when it has to deal with COM I would probable do it in C.

-----------
Added: probable