Differentiate Zero from Null 1

[FractalWalk]

I know this should be easy but I can't get anything to work. I have an Excel macro that needs to End if the active cell is null but run otherwise.

My problem is that everything I try reads a 0 (zero) as null/empty and ends the script. I need a 0 to still trigger the code and only a true null to end it.

Here are different methods I have tried that all failed

If IsNull(ActiveCell) then End
If IsEmpty(ActiveCell) then End
If ActiveCell.Value = Empty then End

I've also tried identifying if it is 0 and if so skipping over the line with the end command but then I get the opposite issue: it reads nulls as 0's.

What cell property or VBA function can I use to distinguish between a 0 and null in a cell?

 

[combo]

Try formula testing (ActiveCell.Formula = Empty)

combo

 

[strongm]

These are the values for the various cell error condition, of which #NULL! is one.

xlErrDiv0 2007 #DIV/0!
xlErrNA 2042 #N/A
xlErrName 2029 #NAME?
xlErrNull 2000 #NULL!
xlErrNum 2036 #NUM!
xlErrRef 2023 #REF!
xlErrValue 2015 #VALUE!

SO we can check for #NULL! as follows:

If IsError(ActiveCell) And (CVErr(ActiveCell)=CVErr(xlErrNull)) Then ' do your stuff...

 

[FractalWalk]

Try formula testing (ActiveCell.Formula = Empty)"

Perfect! Thank you.

 

[strongm]

So not null then ...

 

[TonyJollans]

A cell cannot be null

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[strongm]

#NULL! works well enough as a null cell for me ...

 

[TonyJollans]

A null cell is a contradiction in terms.

It is possible for a UDF to return a #NULL! error - which is an entirely different thing. AFAIK, built-in functions cannot return it other than by referencing (directly or indirectly) cells containing UDFs that have returned it. I'm not good enough with Excel to categorically state that, however, and would be interested to see an example that proves me wrong.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[strongm]

Stick this in A1;

=CELL("address", B1 C1)

 

[TonyJollans]

Thank you. I didn't realise you could enter an intersection so easily ([blue][tt]=B1 C1[/tt][/blue] is sufficient). The cell is still not null, however, it just contains a reference to something that is null and, indicates that as the reason for its failure to resolve.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[xlbo]

To be honest Tony - I've always gone off your FAQ on this subject:

faq707-3710

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[strongm]

>(=B1 C1 is sufficient)
Indeed. I was just trying to be a little more explicit about what wasa going on, and possibly failing

Oh, I agree that a cell cannot physically be null, but it can happily represent null just as various languages represent it (e.g. VBA's vbNull isn't actually null, and Null is just a variant with a specific value). The fact that Excel's programmers chose to call this an error condition and display it as #NULL! rather than deciding it isn't an error and displaying it as, say, [NULL] is a purely philosophical decision...

 

[TonyJollans]

Confusion does abound

I just went and re-read my FAQ and I wouldn't really change it much if I wrote it again.

What a cell displays is distinct from what it contains - #NULL! is an error condition, as is #REF!; both mean that the cell contents cannot be resolved, and neither inherently gives the cell any properties or contents.

The real problem, as we all know, is terminology. I'm just more of a pedant than most

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[fumei]

I'm just more of a pedant than most "

Really? I do not think I would actually agree with that.

However, I most certainly would agree with: "The real problem, as we all know, is terminology."

I as strongly in favour of clear and explicit use of terminology, as you well know. Part of the problem is with a certain fuzziness on the part of Microsoft (we are talking VBA here). To give them credit, for the most part, they are not too bad actually. However, there are some areas that seem a wee wonky.

On the other hand, I think a greater wonkiness comes from assumptions that people make. One of the best is the example you posted:

"What a cell displays is distinct from what it contains"

THAT is where some people get conceptually lost, as there is the assumption that the above statement is not correct. There is the assumption that what is displayed IS the contents.

Let us not forget how many times Skip has sent people off to his Dates FAQ! The confusion between strings (that look like "dates"), and actual Date data types will never go away.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[TonyJollans]

Oh, I can be a pedant! Maybe not always (I let the reference to vbNull pass), and I do try to explain myself, but being precise - where necessary - is part of the programmer's art.

Were it not for Kernighan & Ritchie, and Codd & Date, NUL would probably have remained a rather obscure control character - well, maybe a little more than that, but they gave it a life, and look at it now!

The misuse (abuse) of dates continues unabated. Just as an aside, and an example, in one new registry entry in Office 2010 there are two dates in two different formats - a file date/time (units of 100 nanoseconds since 1601), and a Unix date (minutes since 1970) - it makes Excel look easy

I guess I'm going a bit OT now - time to stop.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website