Version problem?

[stuart_bisset]

I have 2 problems which I think are related but have no proof. Let me
explain. The firm I work for operates a number of offices across
scotland and has over 300 staff. There are various different versions
of excel running from 97 up to 2003 and also a number of different
service packs are installed so whilst I'm on 2000 SP-3 some of my
colleagues are on 2000 SR-1. You get the idea.

Lately I have been building models for various parties around the firm
and whilst they work perfectly on my machine the macros often don't
work on other machines.

eg Problem 1 - a macro compares a date to the values in a range of
cells. On some machines the empty cell is being read as an empty
string (ie "") and causing a type mismatch error (due to string being
compared to number) whilst on my machine the empty cell is being read
as zero and it works (number being compared to number).

Relevant code (dDeadlineStart contains a date)...

Dim DeadLineStart As Long
DeadLineStart = Range("dDeadlineStart").Value
If Format(Cells(xloop, yloop).Value, "0") >= DeadLineStart

the error happens at the IF statement

Problem 2 - Clicking on a cancel button on a dialog box I created
caused an error then crashed excel. Clicking on the cancel button of a
different dialog box (which I also created) caused the same error and
again crashed excel. Unfortunately I didn't note the error.

Relevant code...

Private Sub cmdCancel()
Unload frmDeleteUnit
End Sub

I am becoming fed up of creating models which operate perfectly on my
machine but not on others. I know its unhelpful not to have the error
for the 2nd problem but if any of you can shed any light on why this
might be happening I'd be grateful.

Thanks in advance

Stuart

 

[Bob Phillips]

No great insights into the problem, other than it would seem okay, and
advising to always develop on the least version deployed as a fail-safe
technique.

But ...

If Format(Cells(xloop, yloop).Value, "0") >= DeadLineStart

is a problem. The format will evaluate to a string, but you are comparing to
a Long, could be problemmatical.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

For problem #1, some machines would be machines with xl97 I would think.
This was a problem in xl97.

Solution is to recognize it and program to take care of it.

If you know your cells will be blanks or contain numbers

Dim DeadLineStart As Long
DeadLineStart = Range("dDeadlineStart").Value
If clng(Format(Cells(xloop, yloop).Value, "0")) >= DeadLineStart

if you don't know
Dim sStr as String
Dim DeadLineStart As Long
DeadLineStart = Range("dDeadlineStart").Value
sStr = Format(Cells(xloop, yloop).Value, "0")
if isnumeric(sStr) then
If cln(sStr) >= DeadLineStart Then

I suspect you second problem is probably xl97 machines as well. However
unloading a userform should be pretty generic - however, if you have chained
the showing of userforms, that might be more of a problem.

As Bob said, for the greatest chances of success, you should do your
development on xl97 (the lowest version which will use the code).

 

[stuart_bisset]

Tom/Bob

Thanks for your insights. I like the idea of testing if the cell is
numeric before comparing it to the date. That aught to work a treat.

However your assumption that problem #2 was probably on an xl97 machine
was incorrect (apologies for not specifying that in my original query).
It was actually on an xl 2000 sr-1 machine whilst I am on xl 2000
sp-3. Unless someone advises me otherwise I would have thought that
the service packs would make no difference. So either (a) the service
packs do make a difference and could cause this sort of error or (b)
they don't make a difference and the error is caused by some other
unknown factor. I'd prefer it to be the former as that's easiest to
solve.

Let me know if you have anything you can add to this, as you can tell
I'm pretty much stumped!!!

Kind regards

Stuart