PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Excel portability-VBForums
Results 1 to 14 of 14

Thread: Excel portability

  1. #1

    Thread Starter
    Conquistador
    Join Date
    Dec 1999
    Location
    Australia
    Posts
    4,527

    Excel portability

    I was wondering what i should do if i wanted to use several versions of excel.

    I'm using 2003, some other people that will be using this program will use 2000 etc

    Any suggestions?

  2. #2
    PowerPoster
    Join Date
    Aug 2001
    Location
    new jersey
    Posts
    2,904
    you'll have to learn how to use late binding --- early binding is faster but is not very portable

  3. #3
    New Member
    Join Date
    Jun 2003
    Posts
    7

    Excel Portability

    Hi

    Yes I am having the same difficulty incorporating Excel - our clients could be using anything from Excel 97 -> Excel 2003..

    Late binding is the only answer but i think it raises other problems because you need to be careful the methods etc you use are compatible across all versions.

    Its a nightmare..

    I'd really appreciate if anyone has any feedback / suggestions / comments on this..

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,227
    There are very few significant changes between versions (up to 2002 anyway).

    There should be a page in the Excel VBA help which says about changes from previous versions.

    To be on the safe side you should develop for the lowest version you expect your users to have, as it is extremely rare for features to be removed from MS apps.

  5. #5

    Thread Starter
    Conquistador
    Join Date
    Dec 1999
    Location
    Australia
    Posts
    4,527
    Isn't that a pity that they don't remove features

    I could use 4 gig more on my computer (and do without clippy )

    i'll look into some late binding, thanks for that

  6. #6
    Evil Genius alex_read's Avatar
    Join Date
    May 2000
    Location
    Espoo, Finland
    Posts
    5,538
    Isn't that a pity that they don't remove features
    I'vealways seen it asbeingthe otherway round -I prefer the earlier editions without all the fancy smart tags etc added & taking up an extra 4gb...

    Anyways, yeah rather than using project menu > add reference > microsoft Excel library etc. You've got to use this way (late binding):

    VB Code:
    1. Dim VariableName as Object
    2. Set VariableName = CreateObject("Excel.Application")

    This'll tell vb to lookup Excel.Application in the registry which'll tell it what the version & pathing is of the Excel Version on the current machine so it can use it.

    Doiing it this way you'll need to add error handling, or this routine incasethe user doesn't have any versions of Excel installed:

    VB Code:
    1. Set VariableName = CreateObject("Excel.Application")
    2. If Not VariableName is nothing Then
    3.     'Okay to run other Excel code
    4. End If

    Please rate this post if it was useful for you!
    Please try to search before creating a new post,
    Please format code using [ code ][ /code ], and
    Post sample code, error details & problem details

  7. #7
    Evil Genius alex_read's Avatar
    Join Date
    May 2000
    Location
    Espoo, Finland
    Posts
    5,538
    & why isn't this posted in the VBA forum too!!

    With the different versions (not including 95), you'll find they all support cells(),ranges(), pivottables - unless you're doing some really complicated stuff you should be alright & not notice incompatibilites in the coding...

    Please rate this post if it was useful for you!
    Please try to search before creating a new post,
    Please format code using [ code ][ /code ], and
    Post sample code, error details & problem details

  8. #8

    Thread Starter
    Conquistador
    Join Date
    Dec 1999
    Location
    Australia
    Posts
    4,527
    lol, i was talking about all the useless crap that the average joe won't use in his life that's in office 2003 now



    also, this isn't a vba question!

    imo anything that interfaces with excel / word etc isn't vba

    vba is scripting inside excel



    thanks for the help

  9. #9
    New Member
    Join Date
    Jun 2003
    Posts
    7

    More late binding...

    Thanks for all the feedback on this - feel more comfortable writing the code now. Also agree with all the comments on all the extra crud that ships with newer versions of Excel etc (might try and code a way to assassinate the office assitant).

    While late binding is the only answer it has two drawbacks..

    1. The IDE cannot reveal the methods etc for the excel object when using late binding, because it doesnt know what the Excel object is until its running and

    2. A lot slower than early binding

    Sigh, better struggle on with it until the next version of Excel arrives...

  10. #10
    New Member
    Join Date
    Jun 2003
    Posts
    7
    Why has this been moved to the VBA forum ?? We're talking VB here and when i wanted to know more about VB -> Excel, I searched the VB forum, not VBA which I think anyone interested in this will do ????

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,227
    1. The IDE cannot reveal the methods etc for the excel object when using late binding, because it doesnt know what the Excel object is until its running
    true.. but you can just switch to late binding when you make an Exe, so you get all the help while you are writing it

    2. A lot slower than early binding
    It's not a major issue, the difference isnt that large.

  12. #12
    Fanatic Member WorkHorse's Avatar
    Join Date
    Jul 2002
    Location
    Where you live.
    Posts
    591
    I do like SI says and use late-binding-style code with a reference to the object library in the IDE so that I have all of the helpful, then remove the OLB reference before compiling so that the code is version independent. I use the earliest possible OLB reference so that the code run frm the IDE should run on any version of the called application (there are some complications with this with applications that have object models that are not backwards compatable, such as MS Access, but for Word, Excel, etc. there should be no backwards compatability issues, and actually very few forwards compatability issues because those application object models have been very stable for years).

    VBA isn't really scripting within Excel. Applications such as MS Outlook support scripting in VBScript. VBA in Excel is just VB within Excel with Excel as a default COM object. VB that interfaces with Excel just declares a COM object of Excel that uses functions from the Office DLL. The only difference in VBA is that Excel is pre-defined as the default COM object without having to GetObject or CerateObject. Code-wise, I think VBA is the same as VB, with the eception that VBA does not require a specific reference or create of the application in which the code is running. Of coursethe controls available in VBA are specfic to that application and don't use the standard controls available in VB. Office applications use Forms 2.0 library instead of the controls used by VB6. But outside of that, VBA can use any other controls, references, and code that can be used in VB.

  13. #13
    Fanatic Member WorkHorse's Avatar
    Join Date
    Jul 2002
    Location
    Where you live.
    Posts
    591
    PS: The time difference between late and early binding should not be significant except in benchmark tests between late and early binding. Early binding (at least in MS Office applications) does not actually compile the called application code, but only some of the the object modle information. When the early-bound object is created and functions of that object are called, it must read them from a DLL that contains both the function code and the object model. With late bound objects it does the same thing but does not have object model structure pre-compiled. This should only affect time when the application object is called (for early binding and late binding) or when the object created (for late binding). So the only loss should be during the creation of the application object, which is normal code should happen infrequently, but in a benchmark test that creates the object millions of times will cause the code to be much slower.

  14. #14
    Evil Genius alex_read's Avatar
    Join Date
    May 2000
    Location
    Espoo, Finland
    Posts
    5,538
    In reality, you would worry about this performance hit if you were writing this for excel 2.0 on an old 386 machine with 8mb ram!

    With all the computers/phones/pda's that have 128, 256+ ram, you won't even notice the difference.

    Please rate this post if it was useful for you!
    Please try to search before creating a new post,
    Please format code using [ code ][ /code ], and
    Post sample code, error details & problem details

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width