Excel portability

[da_silvy]

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?

[phinds]

you'll have to learn how to use late binding --- early binding is faster but is not very portable

[dessiedoyle]

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

[si_the_geek]

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.

[da_silvy]

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

[alex_read]

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):

Dim VariableName as Object 
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:

Set VariableName = CreateObject("Excel.Application")
If Not VariableName is nothing Then
    'Okay to run other Excel code
End If

[alex_read]

& 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...

[da_silvy]

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

[dessiedoyle]

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

[dessiedoyle]

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 ????

[si_the_geek]

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.

[WorkHorse]

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.

[WorkHorse]

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.

[alex_read]

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.