VBA5.0 vs VBA6.5

[josecanuseay]

Ok, here’s the situation. I am new to VBA, but not to visual basic. I have been tasked at work to determine why a VBA that is an add-in works fine for Excel2003, but not Excel2007.

The app was originally written for an even older version of Excel and was updated in 2005 just after the release of Excel2003 here at work and before the original programmer jumped ship to another company. Test data in an Excel file is used to generate histograms to determine how the data results are distributed. The VBA generates the histograms. The file extensions are .xls and .xla.

When trying to run it in Excel2007 (which they recently rolled out here at work), it comes up with errors. Upon investigation, there are members being called for classes that are not available in VBA6.5.

I’ve come up with the following conclusions based on things I’ve read in this forum and others:

Excel2003 utilizes VBA5.0 while Excel2007 uses VBA6.5.

VBA5.0 uses class members that are not in 6.5.

VBA5.0 and 6.5 are not interchangeable.

Now that I've probably overstated the obvious, at this juncture, you’re probably wondering ‘what is the point of this post’. I need a way to take the code that was done and convert it to so that it is able to be run on either Excel2003 or 2007. I am looking for any documentation that might allow me to code it, with the proper file extensions, to be run on either version of Excel and VBA.

I realize this has probably been discussed in the past, so if someone knows of any links, that would be helpful.

TIA.

Joe L.

[royUK]

I've just come across a similar situation. This old code was written using DialogSheets and CodeSheets. Despite Excel's "hotfix" which works with versions prior to Excel 2007 I couldn't get it to work,

My conclusion is that the DialogSheets don't work & the code needs updating, possibly you have the same situation