PDA

View Full Version : EXCEL COMPATIBILITY DRAMA



t64
09-05-2007, 12:15 AM
hi all.

In short, I've developed many advanced VBA solutions using Excel 2003, but am not facing a problem. They don't seem to work on machines running Excel 2000! Lots of Library Conflicts and things like the dtpicker (date time picker) dissapear. It's bad enough that machines running the same Excel 2003 have problems sometimes, and we need to program references lookup/adding.

Is there a solution to all this? Is there a way to compile everything to one working file/library/code for it to work on any Excel version?

Jan Karel Pieterse
09-05-2007, 01:11 AM
Yes: write it on the lowest common denominator: a plain vanilla Office 2000 installation (preferably an Office version WITOUT Access) on e.g. Win 2000. That will improve your chances that your references stay put.

If loaded on a later Office version, all Office refs are updated automatically.

Another thing to try to adhere to is to avoid ActiveX controls that are external to native Excel (The date time picker is a good example, AFAIK it belongs to either Access or even a VB6 installation!). That prevents your reference hell from happening in the first place.

rory
09-05-2007, 01:41 AM
I think it's VB6 rather than Access as I don't have it on my work machine. The Calendar control is from Access as I recall.

t64
09-05-2007, 02:28 AM
thanks guys.. i think that sounds logical to me. I'll use a build of win 2000 & excel 2000 to develop things (lots of editing needed now!)

on the same topic.. is there a dtpicker in excel 2000? what would u use?

cheers.

rory
09-05-2007, 03:31 AM
I'd use a text box, personally. It's just as quick to type a date in as it is to pick from a DTPicker control. You may want to split up into Day, Month and Year boxes to ensure the dates are what you think they are if international formats may be an issue (you can use a combobox for months).

Jan Karel Pieterse
09-05-2007, 03:50 AM
I agree: keep it simple. The alternative is to grow your own on a userform.

tpoynton
09-05-2007, 05:07 PM
i modified this a little to suit my needs - I couldnt find dtpicker on my computer or I would have done the same thing...I stumbled on this in my quest to find a userform-based method of picking date and time (I just have them enter the date in a text box, then validate it using:


If IsDate(TextBox_date.Value) = True Then
strDate = TextBox_date.Value
Else
MsgBox "The date is not entered correctly. Please enter as month/day/year (e.g., " & Date & ")"
Exit Sub
End If


here's a start on a 'growing your own' userform....
http://www.dailydoseofexcel.com/archives/2007/08/03/time-picker/

t64
09-06-2007, 01:13 AM
Great.
Well, I've written a neat peice using 3 comboboxes (for a date), If anyone is interested, let me know.


What about the following commands in Excel 2000?

- UCASE(variable)
- CSTR(variable)
- VAL(variable)

rory
09-06-2007, 04:30 AM
What about them? (They all work, if that's the question)

t64
09-07-2007, 04:20 AM
Yes that was the question :)
Do you know what library makes them work, so that I could add references? By default, they don't work with me..

rory
09-07-2007, 04:55 AM
VBA. If they are not working, then you probably have a different missing reference. Check Tools-References for anything checked that starts with 'MISSING:' It's the only cause I've ever come across for built-in VBA functions not working.