detecting zero length vs null vs. long conversions

[vba_php]

I know I've asked something similar to this before you guys, but Excel is seriously annoying me right now. Can anyone tell me why this code is printing EVERY single row in a range of 50000 lines in column 'E'!? At the bottom of this post is an image of the sheet I'm working with. I don't see anything wrong with it myself.

Please Login or Register  to view this content.

[Phuocam]

Try this:

Please Login or Register  to view this content.

[vba_php]

Thanks I will try it when I get home but can I ask you why exactly does mine not work?

[hrlngrv]

Not IsNull(r) ?

I believe you want Not IsEmpty(r.Value) .

From VBA Help with emphasis added: The Null value indicates that the Variant contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized. It is also not the same as a zero-length string (""), which is sometimes referred to as a null string.

Gettin' picky: if Trim(r) <> "" then Len(r) <> 0, meaning you don't need both. For that matter, IsNumeric(Left(r, 1)) should be sufficient to check that the 1st char in r.Value is a decimal numeral.

[vba_php]

Thanks I appreciate it and based on the help section of the health article you posted for me to look at, it's talking about variables and that's not really relevant in this case because I am checking the actual worksheet value in the cells I'm not checking a variable in code. But regardless, yes you are right and that I want to check empty. And the only reason that I have nested three functions inside the check for the outermost function called isnumeric,is because I've run into issues with this language a million times over 20 years and it's proven to me time and time again that it's completely unreliable in terms of what functions do what and what functions to return what types of objects.

And it's because of that reality that I write code in such a way that I don't take a chance on whether VBA can decipher what I want as a return so if I want to string returned and the value I'm checking is technically a numeric value even though it's left aligned in the cell and formatted as a string, I will still use the string conversion function.

[Rick Rothstein]

Try this:

Please Login or Register  to view this content.

I don't think you need the Len test as I believe [!0-9] cannot be the empty string. You can also test r.value directly by using a wild card. If I am correct, this should do the same thing as our code line above...

Please Login or Register  to view this content.

Edit Note: Just added the Trim function call in response to hrlngrv comment in Message #8.

[hrlngrv]

OK, but another thing I missed before is that if you need to call Trim first, then you should also call Trim inside the IsNumeric call.

I've been writing VBA code since the mid-1990s, before that XLM and Lotus 1-2-3 macros in addition to other languages. I haven't come across the type of inconsistency you mention. Something like the following has always worked for me AS LONG AS all spaces are ASCII spaces rather than HTML nonbreaking spaces or other exotica.

Please Login or Register  to view this content.

If the source of the values are cell values, always best to ensure you're not dealing with an error value.

Note that for blank cells, LTrim$(x) returns "".

The only thing I know of which would screw this up would be characters which appear to be spaces which aren't ASCII spaces at the beginning of cell values. That's a data cleansing exercise.

[hrlngrv]

Presumably the Trim call is there for a reason.

[vba_php]

hey guys,

is there a difference between r and r.value?? I've never noticed any difference whatsoever. not when reading range values anyway.

[Rick Rothstein]

The default property when not specified is Value, so with r declared as a Range variable, r and r.value are the same when the situation you are using it in expects a value.

[hrlngrv]

. . . comment in Message #8.

These message numbers depend on the type of message threading one's using. Using the type I've selected, my message appears as #4.