What versions of Office VBA support class modules.

[Andy]

I need to write an Excel add in. I have office 2000 on my machine which
seems to support OO constructs like classes ( class modules) in VBA.
However I need my add in to be widely compatible with other versions of
office including Office 97 and possibly, though less likely Office 95.

I would like to use class modules as I am an OO programmer by instinct
however I am worried that if I use class modules my add in will not be
backward compatible.

Can anyone advise on this or point me at the relevant MSDN doucument that
explains compatibility issues with different versions of Office and VBA.

TIA

Andy

 

[Tushar Mehta]

The big difference between XL97 and XL2000 was the transition from VB5
to VB6.

As far as 95 goes, I don't remember what it did and did not support.
Hech, I have a hard enough time with 97. The quickest way to find out
if it supports custom classes is to use one to create one!

In general, I would develop my app on the oldest platform it has to
support. Otherwise, backward compatibility may come back to haunt you
in ways -- and at times -- you cannot even begin to imagine.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Howard Kaikow]

Excel 97 does support classes, but the best way to avoid some of the
annoying differences between VB 5 and VB 6 is to write the code in VB 6,
using, as needed, early or late binding. See
http://www.standards.com/VB/UsingVisualBasic6.html.

There are differences between the Excel 97 VBA (based on VBA 5) and the
other versions (based on VBA 6). Using VB 6 with classes and "clever"
programming will avoid some of the problems, not to mention compiled code
will typically run faster..

Excel VBA code that works in an earlier version does not necessarily work
correctly in later versions. The answers to these mysteries are found by
enrolling in a course at the College of Hard Knocks (and keeping one's
fingers crossed).

Excel 95 does support a much weaker version of VBA.
IMHO, folkes still using Excel 95 should have already upgraded.
Rather than hamper the coding for later versions of Excel.
I actually feel this way about Excel 97, but the roadblocks are not as
severe, so I can live with it.

 

[Tushar Mehta]

Excel 97 does support classes, but the best way to avoid some of the
annoying differences between VB 5 and VB 6 is to write the code in VB 6,
using, as needed, early or late binding. See

Quite the contrary. Writing something for VB6 and discovering that it
doesn't work with VB5 leaves one up the proverbial creek without the
equally proverbial paddle.

If one uses Join or Split or, as I did recently,
dim withevents x as commandbarbutton
or any of the myriad of other VB6 / XL2000+ enhancements (e.g., the
Range object's Sort method) and then tries to use the code with XL97,
one is in big trouble. No, that is not correct. One is f**ked.

Early binding and late binding have nothing to do with it.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Howard Kaikow]

Quite the contrary. Writing something for VB6 and discovering that it
doesn't work with VB5 leaves one up the proverbial creek without the
equally proverbial paddle.

Nope.

When you code in VB 6, Office 97, doesn't know the difference since the code
is in a DLL, not in the VBA project.

Early binding and late binding have nothing to do with it.

Yes it does, if one has to use some fetures that are not supported in all
versions.

I've done the above a number of times for Word and Excel.
For example,
http://www.standards.com/Sorting/SortPerformanceComparison-Description.html
uses a VB 6 DLL and the code uses non-VB 5 statements such as Replace.

 

[Tushar Mehta]

Ah, VB6 itself, not the VBA based on VB6. We are drifting way beyond
the OP's question of class module support in versions of XL VBA.
Coding in VB (rather than VBA) means one is playing in a different
ballpark altogether with its own advantages and disadvantages.



--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Howard Kaikow]

Yes, I suggested VB 6 because that eliminates a number of significant
constraints in coding for all the Excel versions, and it offers a speed
improvement.