Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Equivalent VBA Excel code

  1. #1

    Equivalent VBA Excel code

    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]

    Kind regards<br /><br />Marcel

  2. #2
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10

    Re: Equivalent VBA Excel code

    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
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  3. #3
    thinBasic MVPs
    Join Date
    May 2007
    Location
    UK
    Posts
    1,427
    Rep Power
    159

    Re: Equivalent VBA Excel code

    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
    Home Desktop : Windows 7 - Intel Pentium (D) - 3.0 Ghz - 2GB - Geforce 6800GS
    Home Laptop : WinXP Pro SP3 - Intel Centrino Duo - 1.73 Ghz - 2 GB - Intel GMA 950
    Home Laptop : Windows 10 - Intel(R) Core(TM) i5-4210U CPU @ 1.70GHz, 2401 Mhz, 2 Core(s), 4 Logical Processor(s) - 4 GB - Intel HD 4400
    Work Desktop : Windows 10 - Intel I7 - 4 Ghz - 8GB - Quadro Fx 370

  4. #4

    Re: Equivalent VBA Excel code

    Quote Originally Posted by 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
    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.
    Kind regards<br /><br />Marcel

  5. #5
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10

    Re: Equivalent VBA Excel code

    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
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  6. #6

    Re: Equivalent VBA Excel code

    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
    http://www.thinbasic.com

  7. #7
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10

    Re: Equivalent VBA Excel code

    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
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  8. #8

    Re: Equivalent VBA Excel code

    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
    http://www.thinbasic.com

  9. #9
    thinBasic author ErosOlmi's Avatar
    Join Date
    Sep 2004
    Location
    Milan - Italy
    Age
    57
    Posts
    8,777
    Rep Power
    10

    Re: Equivalent VBA Excel code

    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
    www.thinbasic.com | www.thinbasic.com/community/ | help.thinbasic.com
    Windows 10 Pro for Workstations 64bit - 32 GB - Intel(R) Xeon(R) W-10855M CPU @ 2.80GHz - NVIDIA Quadro RTX 3000

  10. #10

    Re: Equivalent VBA Excel code

    Perfect.

    Ciao,
    Roberto
    http://www.thinbasic.com

Page 1 of 2 12 LastLast

Similar Threads

  1. MS Excel as 3D engine :D
    By ErosOlmi in forum Development
    Replies: 3
    Last Post: 13-03-2008, 01:09

Members who have read this thread: 0

There are no members to list at the moment.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •