How to Insert a NULL Value?

[dplum]

When I insert a double quote ("") into a cell as a Null value this causes problems when creating formulas that use the cell.

I've searched many posts to do with Null values but can only find information on checking for a Null value, like ISBLANK().

But, I've not been able to find any information on how to write a formula like
=IF(ISBLANK(A1), "",A1/B1)
and substitute the double quote ("") with a value that is Truly Null?

 

[Kenneth Hobson]

Why not just check for the value being "" rather than empty?

Of course your formula will show division by 0 error if B1 is empty or 0.

I guess you could use a UDF like this but a simple If would do just as well.

'=If(ISE(A1),"",A1) or =If(A1="","",A1)
Function ISE(cell As Range) As Boolean
  If cell.Value = "" Then
    ISE = True
    Else: ISE = False
  End If
End Function

 

[dplum]

I'll ask my question another way...

I've learned that VBA includes a Null value in the variable vbNullString

I've tested running a macro to insert a null value into a cell using vbNullString and it works... that is, it inserts a null value as follows...
Sub InsertNull_IfDivisiorZero()
If B1 = "" Then
ActiveCell.Value = vbNullString
Else
ActiveCell.Value = A1 / B1
End If
End Sub

However, when I create a UDF as follows...
Function NullValue()
ActiveCell.Value = vbNullString
End Function

and include this UDF in a cell formula as follows...
=IF(B1="",NullValue(),A1/B1)

the result is #VALUE! error with the explanation...
A value used in the formaul is of a wrong data type.

AND SO, I ask my question again...
"How can a Null Value be inserted into a cell in a formula?"

 

[Scott Huish]

Not sure what this is accomplishing, but your Function is not written correctly:

Function NullValue() As String
NullValue = vbNullString
End Function

WHy not just check to see if you are trying to divide by a non-zero number?

=IF(B1,A1/B1,"")

 

[pgc01]

Hi

Several points

1 -

Your UDF is wrong. You cannot assign values to cells in a UDF. Your statement is not allowed.
If you want to return the null string then use Hotpepper's :

Function NullValue()
    NullValue = vbNullString
End Function

Now you could use:

=IF(B1="",NullValue(),A1/B1)

This is, however, equivalent to:

=IF(B1="","",A1/B1)

no need for the UDF, and does not solve your problem (b1 can be 0).

2 - "I've learned that VBA includes a Null value in the variable vbNullString"

No, vbNullString is not the vba Null value, it's a constant equivalent to an empty string. You cannot write a null value to a cell. You use an empty string instead, like in the previous point.

3 -

ISBLANK() tests if a cell is empty. As far as I can see in your examples your cells have formulas and so will never be empty and so you have no use ffor ISBLANK() in this case.

Note that COUNTBLANK(), has a different criterion: it counts not only empty cell but also cells with empty strings.

4 -

In your formula you don't want to perform the division if either

- the cell is empty
- the cell has a null string
- the cell has the value 0 (zero)

use:

=IF(N(B1),A1/B1,"")

It works for the 3 cases.

 

[Kenneth Hobson]

Another option that will catch other problems:
=IF(ISERROR(A1/B1),"",A1/B1)

 

[dplum]

I thank everyone for their input. However, the responses have not addressed my main need, which is to insert a NULL VALUE that is something other than "".

Perhaps by posting a couple of files that you can examine you'll better understand what I'm trying to do.

At http://dplum.com/excel you will find a PDF file that can be reviewed (if you'd prefer to not open an xls file). Or you can examine the xls file from which the PDF was created.

Please note:
1. The Batting Average field is calulated using =IF(C6=0,"",D6/C6) to avoid a Divide by Zero Error.
2. The IF statement in the Comment field (to show Avg > .350 as a "Slugger!") results in 2 errors... cells F11, F13... these cells display "Slugger!" even though they have a Double Quote ("") Null Value in them.
3. To fix this error I need to use the AND() function to also check that the cell is Not = ""

So... if a TRUE Null Value could be placed in these cells rather than the "" the error would not happen. IS THERE A WAY TO DO THIS?

 

[Scott Huish]

No, you can't have nothing in a cell and a formula.

But this should be a very easy fix.

In F6:
=IF(N(E6)>0.35,"Slugger!","")

Copy down.

 

[dplum]

Hotpepper,
Thanks for your feedback.

 

[Fazza]

Hi, Dennis.

Seems like you have sorted out the question.

I wanted to mention that a null (from dataset) can be written into a cell using Excel's database type functionality - so a query table, or an ADO recordset copied to the worksheet.

Regards, Fazza