Hi there,

Wonder if anyone can tell me what might be going on here.

I have a routine that check a column of values row by row, cell by cell for various undesirable conditions which if it finds it ends the sub and highlights the offending cell to allow the user to correct it.

It is a series of doubles which I have dimmed as variant, and it checks to see if its numeric, empty or 0.5.

What has happened is that one data entry person has messed up somewhat and entered the numbers in wrongly and to fix it I just entered a formula in excel to turn any cells that were too big into the right number by dividing by 100, and if the cell was empty to give it and empty string value ("").

What is happening is that when it is evaluation an empty cell, it is throwing an error for the condition 0.5 which is very puzzling indeed since it is an empty string.

If I select the offending cell and clear all, then it solves it and the cell is evaluated correctly.

I cannot see why this happens as if it is a string then why would it be evaluated as .05

Any ideas?

Thanks and regards, Mark

Hi there,

Wonder if anyone can tell me what might be going on here.

I have a routine that check a column of values row by row, cell by
cell for various undesirable conditions which if it finds it ends the
sub and highlights the offending cell to allow the user to correct
it.

It is a series of doubles which I have dimmed as variant, and it
checks to see if its numeric, empty or 0.5.

What has happened is that one data entry person has messed up
somewhat and entered the numbers in wrongly and to fix it I just
entered a formula in excel to turn any cells that were too big into
the right number by dividing by 100, and if the cell was empty to
give it and empty string value ("").

What is happening is that when it is evaluation an empty cell, it is
throwing an error for the condition 0.5 which is very puzzling
indeed since it is an empty string.

If I select the offending cell and clear all, then it solves it and
the cell is evaluated correctly.

I cannot see why this happens as if it is a string then why would it
be evaluated as .05

Any ideas?

Thanks and regards, Mark

Well.., possibly because the var is variant and so VBA interprets "" as
a string and 0.5 as numeric and so should throw a type mismatch error.
This is common when trying to process the empty string with numeric
values. Perhaps return zero?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Gary,

Thanks but if it interprets it as string then why is it saying it is 0.5?


Kind regards, Mark

On Sunday, 3 May 2015 10:13:24 UTC+8, GS wrote:
Hi there,

Wonder if anyone can tell me what might be going on here.

I have a routine that check a column of values row by row, cell by
cell for various undesirable conditions which if it finds it ends the
sub and highlights the offending cell to allow the user to correct
it.

It is a series of doubles which I have dimmed as variant, and it
checks to see if its numeric, empty or 0.5.

What has happened is that one data entry person has messed up
somewhat and entered the numbers in wrongly and to fix it I just
entered a formula in excel to turn any cells that were too big into
the right number by dividing by 100, and if the cell was empty to
give it and empty string value ("").

What is happening is that when it is evaluation an empty cell, it is
throwing an error for the condition 0.5 which is very puzzling
indeed since it is an empty string.

If I select the offending cell and clear all, then it solves it and
the cell is evaluated correctly.

I cannot see why this happens as if it is a string then why would it
be evaluated as .05

Any ideas?

Thanks and regards, Mark

Well.., possibly because the var is variant and so VBA interprets "" as
a string and 0.5 as numeric and so should throw a type mismatch error.
This is common when trying to process the empty string with numeric
values. Perhaps return zero?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks but if it interprets it as string then why is it saying it is
0.5?

VBA interprets the comparison as a type mismatch.
Excel interprets the comparison as TRUE, regardless of the value...

F3: =IF(B3=12,B3,"") returns ""

G3: =F30.5 returns True
G3: =F3100.5 returns True

In the Immediate window:
?""0.5 throws a 'Type msmatch' exception
?[F3]0.5 returns True
?[F3]100.5 returns True

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"Mark Stephens" wrote:
I have a routine that check a column of values row by row,
cell by cell for various undesirable conditions which if
it finds it ends the sub and highlights the offending cell
to allow the user to correct it. It is a series of doubles
which I have dimmed as variant, and it checks to see if its
numeric, empty or 0.5.

What has happened is that one data entry person has messed
up somewhat and entered the numbers in wrongly and to fix
it I just entered a formula in excel to turn any cells that
were too big into the right number by dividing by 100, and
if the cell was empty to give it and empty string value ("").

What is happening is that when it is evaluation an empty cell,
it is throwing an error for the condition 0.5 which is very
puzzling indeed since it is an empty string.
[....]
I cannot see why this happens as if it is a string then why
would it be evaluated as .05[.] Any ideas?

If you had included some snippets of code, it would have eliminated
ambiguities in your description and made things clearer.

On the one hand, you say "it is throwing an error for the condition 0.5".

On the other hand, you ask why "a string [...] would be evaluated as .05"
[sic].

Which is happening?

If you do:

Dim x As Variant
x = ""
Msgbox Typename(x) & vbNewLine & (x 0.5)

that displays True because any text in a Variant variable (subtype String)
is considered larger than a number, just like in Excel.

But Msgbox ("" 0.5) does cause a VBA error due to the mismatch.

I think the inconsistency arises from how VBA wants to emulate Excel when we
use Range(...), which is type Variant. But that is only guess. The fact
is: it is what it is.

You might avoid the problem by doing the following:

x = Empty ' instead of ""

Alternatively, you might avoid the problem by ordering and separating your
tests appropriately, for example:

If x < "" And WorksheetFunction.IsNumber(x) Then
If x tooBig Then x = x / 100
If x 0.5 Then
' .... whatever ....
End If
End If

FYI, I use WorksheetFunction.IsNumber instead of VBA IsNumeric because the
latter does not behave the same way.

If that does not answer your question(s), please post code snippets so we
can better understand the problem.